【201502函初】练习五 提交贴(已开贴)

天天EP

练习五
c05 天天EP 参与学习
一、
1、=IF(MID("10x98765432",MOD(SUM(MID(B3,ROW(INDIRECT("1:"&LEN(B3)-1)),1)*2^(18-ROW(INDIRECT("1:"&LEN(B3)-1)))),11)+1,1)=RIGHT(B3,1),VLOOKUP(LEFT(B3,6),表1!A:B,2,),"无此人")
2、=IF(MID("10x98765432",MOD(SUM(MID(B3,ROW(INDIRECT("1:"&LEN(B3)-1)),1)*2^(18-ROW(INDIRECT("1:"&LEN(B3)-1)))),11)+1,1)=RIGHT(B3,1),TEXT(MID(B3,7,8),"0000-00-00"),"无此人")
3、=IF(MID("10x98765432",MOD(SUM(MID(B3,ROW(INDIRECT("1:"&LEN(B3)-1)),1)*2^(18-ROW(INDIRECT("1:"&LEN(B3)-1)))),11)+1,1)=RIGHT(B3,1),IF(MOD(MID(B3,9,9),2),"男","女"),"无此人")
4、=IF(MID("10x98765432",MOD(SUM(MID(B3,ROW(INDIRECT("1:"&LEN(B3)-1)),1)*2^(18-ROW(INDIRECT("1:"&LEN(B3)-1)))),11)+1,1)=RIGHT(B3,1),DATEDIF(TEXT(MID(B3,7,8),"0000-00-00"),TODAY(),"Y"),"无此人")
5、=IF(MID("10x98765432",MOD(SUM(MID(B3,ROW(INDIRECT("1:"&LEN(B3)-1)),1)*2^(18-ROW(INDIRECT("1:"&LEN(B3)-1)))),11)+1,1)=RIGHT(B3,1),MID("猴鸡狗猪鼠牛虎兔龙蛇马羊",MOD(MID(B3,7,4),12)+1,1),"无此人")
6、=IF(MID("10x98765432",MOD(SUM(MID(B3,ROW(INDIRECT("1:"&LEN(B3)-1)),1)*2^(18-ROW(INDIRECT("1:"&LEN(B3)-1)))),11)+1,1)=RIGHT(B3,1),IFERROR(DATEDIF(TODAY(),YEAR(TODAY())&"-"&MID(B3,11,2)&"-"&MID(B3,13,2),"d"),DATEDIF(TODAY(),YEAR(TODAY())+1&"-"&MID(B3,11,2)&"-"&MID(B3,13,2),"d"))&"天","无此人")
二、=SUM(LEN(IFERROR(表1!B2:B3528,)))
三、=MAX(MIN(ROW(A1)-1,10),0.1)*10&"-"&MAX(MIN(ROW(A1),10),1)*10
    =MEDIAN(0.1,10,ROW(A1)-1)*10&"-"&MEDIAN(1,100,ROW(A1)*10)
四、=--MID(SUBSTITUTE(A27,"-",REPT(" ",99)),100,99)
五、=SUM(IFERROR(--表1!A2:A3528,))

suzhouy2003

201502函初：A10

第一题：='320583195010250618
             =IFERROR(VLOOKUP(--LEFT(B3,6),表1!A:B,2,),"无此人")
             =IF(B4="无此人","无此人",TEXT(MID(B3,7,8),"0000-00-00"))
             =IF(B4="无此人","无此人",TEXT(MOD(MID(B3,17,1),2),"男；；女；"))
             =IF(B4="无此人","无此人",YEAR(NOW())-TEXT(--MID(B3,7,4),"0000"))
             =IFERROR(VLOOKUP(MOD(YEAR(TEXT(B5,"[$-130000]0000"))-1900+1,12),{1,"鼠";2,"牛";3,"虎";4,"兔";5,"龙";6,"蛇";7,"马";8,"羊";9,"猴";10,"鸡";11,"狗";12,"猪"},2,),"无此人")
             =IFERROR(IF(MONTH(B5)-MONTH(TODAY())<0,TEXT(DAYS360(TODAY(),DATE(YEAR(NOW())+1,MONTH(B5),DAY(B5))),"0"),TEXT(DAYS360(TODAY(),DATE(YEAR(NOW()),MONTH(B5),DAY(B5))),"0")),"无此人")
            
第二题：{=SUM(IFERROR(LEN(表1!B:B),0))}
第三题：=MAX(10*ROW(A1)-10,1)&"-"&MIN(10*ROW(A2)-10,100)
第四题：=--RIGHT(A27,LEN(A27)-FIND("-",A27)) 下拉
第五题：{=SUM(IFERROR(--表1!A:A,0))}

gyp0707

1:
2:
3:
4:MID(A27,FIND("-",A27)+1,LEN(A27))[/code]

qiaoqiaodl_001

201502函初A07

1. =IFERROR(VLOOKUP(LEFT(B3,6),表1!A:B,2,),"无此人")

复制代码

1. =IF(ISERROR(VLOOKUP(LEFT(B3,6),表1!A:B,2,)),"无此人",--TEXT(MID(B3,7,6+2*(LEN(B3)=18)),"0年00月00日"))

复制代码

1. =IF(ISERROR(VLOOKUP(LEFT(B3,6),表1!A:B,2,)),"无此人",IF(MOD(MID(B3,15+2*(LEN(B3)=18),1),2),"男","女"))

复制代码

1. =IF(ISERROR(VLOOKUP(LEFT(B3,6),表1!A:B,2,)),"无此人",DATEDIF(--TEXT(MID(B3,7,6+2*(LEN(B3)=18)),"0年00月00日"),NOW(),"Y"))

复制代码

1. =IF(ISERROR(VLOOKUP(LEFT(B3,6),表1!A:B,2,)),"无此人",MID("鼠牛虎兔龙蛇马羊猴鸡狗猪",MOD(MID(B3,7+2*(LEN(B3)=18),2),12)+1,1))

复制代码

1. =IF(ISERROR(VLOOKUP(LEFT(B3,6),表1!A:B,2,)),"无此人",DATEDIF(TODAY(),TEXT(YEAR(NOW())+1&MID(B3,9+2*(LEN(B3)=18),4),"0-00-00"),"d"))

复制代码

1. =LEN(PHONETIC(表1!B2:B3528))

复制代码

1. =CONCATENATE(MAX((ROW(A1)-1)*10,1),"-",MIN(ROW(A1)*10,100))

复制代码

1. =RIGHT(A27,LEN(A27)-(FIND("-",A27)))

复制代码

1. =LEN(PHONETIC(表1!A2:A3528))

复制代码

liangj

B组02号：liangj
第一题：发证地：=IF(ISNA(VLOOKUP(MID(B3,1,6),表1!$A$2:$B$3528,2,0)),"无此人",VLOOKUP(MID(B3,1,6),表1!$A$2:$B$3528,2,0))
出生日期：=IF(ISNA(VLOOKUP(MID(B3,1,6),表1!$A$2:$B$3528,2,0)),"无此人",MID(B3,7,8))
性别：=IF(ISNA(VLOOKUP(MID(B3,1,6),表1!$A$2:$B$3528,2,0)),"无此人",IF(MOD(RIGHT(LEFT(B3,17)),2),"男","女"))
年龄：=IF(ISNA(VLOOKUP(MID(B3,1,6),表1!$A$2:$B$3528,2,0)),"无此人",YEAR(TODAY())-MID(B3,7,4))
生肖：=IF(ISNA(VLOOKUP(MID(B3,1,6),表1!$A$2:$B$3528,2,0)),"无此人",MID("鸡狗猪鼠牛虎兔龙蛇马羊猴",MOD(MID(B3,7,4),12),1))
下一次生日：还未算出（在网上也查不到）。
第二题：=LEN(PHONETIC(表1!B2:B4000))
第三题：=TEXT((ROW(A1)-1)*10,"[=0]1")&"-"&TEXT(ROW(A1)*10,"[>100]1!0!0")
第四题：=MID(A27,FIND("-",A27)+1,9)
第五题：{=SUM(IF(ISNUMBER(--表1!A2:A3528),--表1!A2:A3528))}

ziyuepx

学号：A12

第一题：
出生日期：=MID(B3,7,8)
性别：=IF(MOD(MID(B3,17,1),2)=1,"男","女")
年龄：=DATEDIF(TEXT(B5,"0-00-00"),TODAY(),"y")

第三题：=MAX((ROW(A1)-1)*10,1)&"-"&MIN(ROW(A1)*10,100)
第四题：=MID(A27,FIND("-",A27)+1,10)
其余都不会

vicky4896

C03:vicky4896
第一题：

1. =IFERROR(VLOOKUP(LEFT(B3,6),表1!A:B,2,0),"无此人")

复制代码

1. =IF(B4="无此人","无此人",TEXT(MID(B3,7,8),"0000年00月00日"))

复制代码

1. =IF(B4="无此人","无此人",IF(ISODD(MID(B3,17,1)),"男","女"))

复制代码

1. =IF(B4="无此人","无此人",DATEDIF(B5,TODAY(),"y"))

复制代码

1. =IF(B4="无此人","无此人",MID("猴鸡狗猪鼠牛虎兔龙蛇马羊",MOD(YEAR(B5),12)+1,1))

复制代码

1. =IF(B4="无此人","无此人",DATE(IF(--MID(B3,11,4)>(--TEXT(NOW(),"MDD")),0,1)+YEAR(NOW()),MID(B3,11,2),MID(B3,13,2))-TODAY())

复制代码

第二题：

1. =SUM(IFERROR(LEN(表1!B2:B3528),0))

复制代码

第三题

1. =MAX((ROW(A1)-1)*10,1)&"-"&MIN(ROW(A1)*10,100)

复制代码

第四题

1. =TRIM(RIGHT(SUBSTITUTE(A27,"-",REPT(" ",99)),99))

复制代码

1. =REPLACE(A27,1,FIND("-",A27),)

复制代码

第五题

1. =SUM(IFERROR(--表1!A2:A3528,0))

复制代码

{:321:}大C组格式统一完成，{:351:}

is竝

C02:is竝  
第一题：b3:单元格输入429004199301316061  =IF(B3="429004199301316061","湖北省仙境市","无此人")    =IF(B3="429004199301316061",TEXT(MID(B3,7,8),"0000年00月00日"),"无此人")    =IF(B3="429004199301316061",IF(MOD(MID(B3,17,1),2),"男","女"),"无此人")    =IF(B3="429004199301316061",YEAR(TODAY())-MID(B3,7,4),H7)    =IF(B3="429004199301316061",MID("鼠牛虎兔龙蛇马羊猴鸡狗猪",MOD(MID(B3,11,2)-4,12)+1,1),"无此人")     =IF(B3="429004199301316061",MID("鼠牛虎兔龙蛇马羊猴鸡狗猪",MOD(MID(B3,11,2)-4,12)+1,1),"无此人")     =IF(B3="429004199301316061",(DATE(2015,MID(B3,12,2),MID(B3,14,2))-TODAY())&"天","无此人")   
第二题： =SUM(IFERROR(LEN(表1!B2:B3528),0))
第三题：=MAX(1,(ROW(A1)-1)*10)&"-"&MIN(ROW(A1)*10,100)  
第四题：①=MID(A27,FIND("-",A27)+1,9) ②=RIGHT(A27,LEN(A27)-FIND("-",A27))③=RIGHT(A27,FIND("-",A28)-1)
第五题：=SUM(IFERROR(--表1!A2:A3528,0))

H明悠

C08:H明悠
第一题

1. 发证地：=IFERROR(VLOOKUP(LEFT(B3,6),表1!A:B,2,),"无此人")
   
2. 出生日期：=TEXT(MID(B3,7,8),"0-00-00")
   
3. 性别：=MID("女男",MOD(MID(B3,15,3),2)+1,1)
   
4. 年龄：=DATEDIF(B5,TODAY(),"Y")
   
5. 生肖：=MID("猴鸡狗猪鼠牛虎兔龙蛇马羊",MOD(MID(B3,7,4),12)+1,1)
   
6. 下一次生日：=DATE(IF(--MID(B3,11,4)>(--TEXT(NOW(),"MDD")),0,1)+YEAR(NOW()),MID(B3,11,2),MID(B3,13,2))-TODAY()

复制代码

第二题

1. ={SUM(LEN(表1!B2:B3528))}

复制代码

第三题

1. =MAX(1,(ROW(A1)-1)*10)&"-"&MIN(10,ROW(A1))&0

复制代码

第四题

1. =MID(A27,FIND("-",A27)+1,3)

复制代码

第五题

1. ={SUM(IFERROR(--表1!A2:A3528,0))}

复制代码

夕颜2015-1

A09
第一题：=IF(B3="42900419930136061","湖北省仙桃市","无此人")
            =IF(B3="42900419930136061","1993年1月31日","无此人")
            =IF(B3="42900419930136061","女","无此人")
            =DATEDIF(B5,NOW(),"y")
            =IF(B3="42900419930136061","鸡","无此人")
            =IF(B3="42900419930136061",DATEDIF(TODAY(),"2016年1月31日","yd"),"无此人")

第二题：=SUMPRODUCT(LEN(表1!B$2:表1!B$3521))
第三题：不会
第四题：=MID(A27,FIND("-",A27,1)+1,LEN(A27))
第五题：=SUMPRODUCT(VALUE(表1!A$2:表1!A$3521))