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

绵花糖

A组长：绵花糖
一 1.=IFERROR(VLOOKUP(LEFT(B3,6),表1!A:B,2),"无此人")
   2.=IF(B4="无此人",B4,TEXT(MID(B3,7,8),"0000年00月00日"))
   3.=IF(B4="无此人",B4,IF(ISODD(MID(B3,17,1)),"男","女"))
   4.=IF(B4="无此人",B4,DATEDIF(TEXT(MID(B3,7,8),"0000-00-00"),TODAY(),"Y"))
   5.=IF(B4="无此人",B4,MID("鼠牛虎兔龙蛇马羊猴鸡狗猪",MOD(MID(B3,7,4)-2007,12),1))
   6.=IF(B4="无此人",B4,DATE(IF(--MID(B3,11,4)>(--TEXT(NOW(),"MDD")),0,1)+YEAR(NOW()),MID(B3,11,2),MID     (B3,13,2))-TODAY())
二.=SUM(IF(ISERROR(表1!B2:B3528),,LEN(表1!B2:B3528)))   三键结束
三.=MAX((ROW(A1)-1)*10,1)&"-"&MIN(ROW(A1)*10,100)
四.=RIGHT(A27,FIND("-",A27)-1)
五.=SUM(IFERROR(--表1!A2:A3528,))   三键结束

today0427

A05:tody0427
第一题
1  =IF(ISERROR(VLOOKUP(LEFT(B3,6),表1!A:B,2,FALSE)),"无此人",VLOOKUP(LEFT(B3,6),表1!A:B,2,FALSE))
2  =TEXT(MID(B3,7,8),"00000000")
3  =IF(MOD(RIGHT(B3,2),2)<>0,"男","女")
4  =DATEDIF(B5,NOW(),"y")  这个错了，但是不知道怎么错的，烦请指点一下
5  =VLOOKUP(MOD(--LEFT(B5,4),12),H3:I14,2,0)
6  =IF(DATEDIF(TODAY(),TEXT(B5,"mm/dd"),"d")<0,DATEDIF(TODAY(),DATE(MONTH(B5)+12,DAY(B5),"d"),DATEDIF(TODAY(),TEXT(B5,"mm/dd"),"d")))   这个也不知道错哪儿了
第二题
这个不会  =SUM(LEN(表1!B2:B3
=IF(10*ROW(A1)-10<1,"1",10*ROW(A1)-10)&"-"&IF(10*ROW(A1)>100,"100",10*ROW(A1))528))
第四题
=MID(A27,FIND("-",A27)+1,LEN(A27))
=RIGHT(A27,FIND("-",A27)-1)
第五题
不会 不知道错哪儿了  =SUM(--表1!A2:A3528)

jzkwf

C组长:jzkwf
第一题

1. =IFERROR(VLOOKUP(LEFT(B3,6),表1!A:B,2,0),"无此人")
   
2. =IF(B4="无此人","无此人",--TEXT(MID(B3,7,8),"0-00-00"))
   
3. =IF(B4="无此人","无此人",IF(MOD(MID(B3,17,1),2),"男","女"))
   
4. =IF(B4="无此人","无此人",YEAR(NOW())-YEAR(B5))
   
5. =IF(B4="无此人","无此人",MID("鸡狗猪鼠牛虎兔龙蛇马羊猴",MOD(YEAR(B5),12),1))
   
6. =IF(B4="无此人","无此人",IF(TODAY()>--(MONTH(B5)&-DAY(B5)),(YEAR(NOW())+1&-MONTH(B5)&-DAY(B5))-TODAY(),(MONTH(B5)&-DAY(B5))-TODAY()))

复制代码

第二题

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

复制代码

第三题

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

复制代码

第四题

1. =REPLACE(A27,1,FIND("-",A27),"")
   
2. =RIGHT(A27,LEN(A27)-FIND("-",A27))
   
3. =MID(A27,FIND("-",A27)+1,9)
   
4. =ABS(RIGHT(A27,3))
   
5. =ABS(MID(A27,4,9))

复制代码

第五题

1. =SUM(IFERROR((表1!A:A*1),0))

复制代码

回答完毕！