[201401函中]E组第六讲作业提交贴

十五的月亮

2.4题没做出，等开贴
1.=SUMIFS(B3:B9,B3:B9,">=10",B3:B9,"<=20")
   =SUM(SUMIF(B3:B9,{">=10",">20"})*{1,-1})
   =SUM(TEXT(B3:B9,"[>=10]0.00;!0;!0;!0")-TEXT(B3:B9,"[>20]0.00;!0;!0;!0"))
2、=TEXT(C14,"#"&REPT("!,#0",INT(LEN(C14)/2.1)))
3.=IF(OR(C23="",D23=""),"漏刷卡",TEXT(C23,"[>0.354166]迟到;")&TEXT(D23,"[<0.75]早退;"))
4.=NUMBERSTRING(C30,3)
  辅助：=TEXT(MID(C30,COLUMN(INDIRECT("1:"&LEN(C30))),1),"[DBNum1]G/通用格式")
           =CONCATENATE(H30,I30,J30,K30)
5.=TEXT(MID(C39,FIND("/",C39)-2,5),"mmm")

瑾瑄

1.=SUM(--TEXT(B3:B9,"[>20]!0;[<10]\0;0.00;!0"))
2.=SUBSTITUTE(TEXT(C14,0&REPT("!,00",(LEN(C14)-1)/2)),",0",",")
3.=IF(C22*D22,TEXT(C22,"[>.35417]迟到;")&TEXT(D22,"[<.75]早退;"),"漏刷卡")
4.=TEXT(C30,"[DBNum1][$-804]#或0")
5.=TEXT(MID(C39,FIND("/",C39)+4,4)&"-"&MID(C39,FIND("/",C39)-2,2),"mmm")

76868850

1、=SUM(TEXT(B3:B9,"[<10]!0;[>20]!0;#.00;!0")*1)数组公式
2、=MID(SUBSTITUTE(TEXT(C14,REPT("!,00",ROUNDUP(LEN(C14)/2,0))),",0",","),2,100)
3、=TEXT(C22,"[<8:30]迟到;[<18:00]早退;;漏刷卡")
4、=TEXT(C30,"[DBNUM1]0")
5、=TEXT(MID(C39,FIND("/",C39)-2,10)*1,"mmm;@")

meililin

第1题

1. =SUM(--TEXT(B3:B9,"[>=10]#.00;!0;!0;!0"))-SUM(--TEXT(B3:B9,"[>=20]#.00;!0;!0;!0"))

复制代码

第2题

1. =MID(SUBSTITUTE(TEXT(C14,REPT("!,00",ROUND(LEN(C14)/2,))),",0",","),2,20)

复制代码

第3题

1. =IF(ISERROR(FIND("漏刷卡",TEXT(C22,"[>0.35417]迟到;[=0]漏刷卡; ")&TEXT(D22,"[>0.75] ;[=0]漏刷卡;早退 "))),TEXT(C22,"[>0.35417]迟到;[=0]漏刷卡; ")&TEXT(D22,"[>0.75] ;[=0]漏刷卡;早退 "),"漏刷卡")

复制代码

chh0926

1
=SUMIFS(B3:B9,B3:B9,">="&10,B3:B9,"<="&20)
2
=SUBSTITUTE(TEXT(C14,"#"&REPT("!,00",INT(LEN(C14)/2.1))),",0",",")
3
=IF(C23*D23=0,"漏打卡",TEXT(C23,"[>=0.35416]迟到;"""";""""")&TEXT(D23,"[<0.75]早退;"""";"""""))
4
=TEXT(C30,"[DBNum1]0")
5
=TEXT(MID(C39,FIND("/",C39)-2,5),"mmm")

天龙九部

来晚了

1. SUM(SUMIF(B3:B9,{">=10",">=20"})*{1,-1})

复制代码

1. MID(SUBSTITUTE(TEXT(C14,REPT("!,00",(1+LEN(C14))/2)),",0",","),2,99)

复制代码

1. IF(OR(C22=0,D22=0),"漏打卡",TEXT(0.354166666666667-C22,";迟到;")&TEXT(D22-0.75,";早退;"))

复制代码

1. NUMBERSTRING(C30,3)

复制代码

1. TEXT(--MID(C39,FIND("/",C39)-2,2)&"-1","mmm")

复制代码

tt89004808

1. 1.=SUMIFS(B3:B9,B3:B9,">=10",B3:B9,"<=20")
   
2. 2.=TEXT(C14,"#"&REPT("!,00",INT(LEN(C14)/2.1)))
   
3. 3.=TEXT(C22,"[>"&TIME(8,30,0)&"]迟到;!"&TEXT(C22,"[=""]漏刷卡;! "))&TEXT(D22,"[>"&TIME(8,30,0)&"]早退;!"&TEXT(D22,"[=""]漏刷卡;! "))
   
4. 4.=TEXT(C30,"[Dbnum1]0")
   
5. 5.=TEXT(DATE(2013,MID(C39,SEARCH("/",C39)-2,2),1),"mmm")

复制代码

xiaox0124

1. =SUM(TEXT(B3:B9,"[<10]!0;[>20]!0;#.00;!0")*1)   三键
   
2. =MID(SUBSTITUTE(TEXT(C14,REPT("!,00",ROUNDUP(LEN(C14)/2,0))),",0",","),2,100)
   
3. 不会
   
4. 不会
   
5. =TEXT(MID(C39,FIND("/",C39,1)-2,2)&"-1","mmm")

复制代码