【函中201401】第6讲D组作业上交贴

danny-fei

1、作业统一贴公式(需要过程的直接录屏或者写明步骤).
2、跟帖不要重复占楼,要修改直接在原楼层编辑
3、评分标准:答案正确+15/+15,个别优秀的+1-10分.
4. 作业时间截止：10/7日15：00

木偶fenzi

交作业。。
1

1. =SUM(--(0&TEXT(TEXT(IFERROR(--B3:B9,""),"[>=10]0.00;"),"[<=20]0.00;")))

复制代码

1. =SUM(IFERROR(--TEXT(TEXT(IFERROR(--B3:B9,""),"[>=10]0.00;"),"[<=20]0.00;"),""))

复制代码

1. =SUM(--(0&TEXT(TEXT(TEXT(B3:B9,"0.00;-0.00;0;"),"[>=10]0.00;"),"[<=20]0.00;")))

复制代码

2

1. =SUBSTITUTE(TEXT(C14,"0"&REPT("!,00",INT(LEN(C14)/2.2))),"0,",",")

复制代码

3

1. =IF(OR(C23="",D23=""),"漏刷卡",TEXT(C23,"[>0.35416]迟到;")&TEXT(D23,"[<0.75]早退;"))

复制代码

4

1. =TEXT(DATE(C30,1,1),"[DBNum1]e")

复制代码

5

1. =TEXT(TEXT(SUM(MID(SUBSTITUTE(MID(C39,FIND("/",C39)-2,10),"/",REPT(" ",99)),{199,1,99},99)*(10^{4,2,0})),"0-00-00"),"mmm")

复制代码

1. =TEXT(DATE(--MID(SUBSTITUTE(MID(C39,FIND("/",C39)-2,10),"/",REPT(" ",99)),199,99),--MID(SUBSTITUTE(MID(C39,FIND("/",C39)-2,10),"/",REPT(" ",99)),1,99),--MID(SUBSTITUTE(MID(C39,FIND("/",C39)-2,10),"/",REPT(" ",99)),99,99)),"mmm")

复制代码

yuan1987

1、=SUMIFS(B3:B9,B3:B9,">=10")-SUMIFS(B3:B9,B3:B9,">20")
2、=IF(C22>VALUE("8:30:00"),"迟到",IF(C22="","漏刷卡",""))&IF(AND(D22<VALUE("18:00:00"),D22>0),"早退",IF(D22="","漏刷卡",""))
3、=TEXT(C30,"[dbnum1]0")
4、=CHOOSE(MID(C39,FIND("/",C39)-2,2),"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")
第二题看完视频做，不会哈。

C.Deny

D08:C.Deny上交第六讲作业
1.=SUM(TEXT(B3:B9,"[>20]""0"";[>=10]G/通用格式;""0"";""0""")*1)数组
2.=MID(SUBSTITUTE(TEXT(C14,REPT("\,00",CEILING(LEN(C14)/2,1))),",0",","),2,20)下拉
3.=IF(OR(C22="",D22=""),"漏刷卡",TEXT(C22-"8:30","迟到;;;")&TEXT(D22-"18:00",";早退;;"))下拉
4.=TEXT(C30,"[DBNum1]0")下拉
5.=TEXT(--(RIGHT(MID(C39,FIND("/",C39)-2,10),4)&"/"&LEFT(MID(C39,FIND("/",C39)-2,10),5)),"mmm")下拉

天棋

第一题：=SUM(SUMIF(B3:B9,{">=10",">20"},B3:B9)*{1,-1})
第二题：=SUBSTITUTE(TRIM(SUBSTITUTE(TEXT(C14,REPT(" 00 ",INT((LEN(C14)-1)/2)+1))," 0",""))," ",",")
第三题：=TEXT(IF(OR(C23:D23=""),"漏刷卡",IF((C23>--"8:30")*(D23<--"18:00"),"迟到早退",(C23>--"8:30")+(D23<--"18:00")*2-1)),"早退;;迟到;@")
第四题：=TEXT(C30,"[DBNUM1]0")
第五题：=TEXT(--MID(C39,FIND("/",C39)-2,4),"mmm")

lmze2000

第1题：

1. =SUMIFS(B3:B9,B3:B9,">=10",B3:B9,"<=20")

复制代码

1. =SUM(IFERROR((--TEXT(TEXT(B3:B9,"[>=10]0.00;;;"),"[<=20]0.00;;;")),))

复制代码

第2题

1. =SUBSTITUTE(SUBSTITUTE(TEXT(C14,"#"&REPT("!,??",INT(LEN(C14)/2.1))),",00",""),",0",",")

复制代码

第3题:

1. =IF(COUNTA(C22:D22)=2,TEXT(C22-(51/144),"迟到;;")&TEXT(D22,"[<0.75]早退;"),"漏刷卡")

复制代码

第4题

1. =TEXT(DATE(C30,1,1),"[dbnum1]yyyy")

复制代码

1. =TEXT(DATE(C31,1,1),"[dbnum1]e")

复制代码

1. =RIGHT(TEXT(C30/(1*10^LEN(C30)),"[dbnum1]"),LEN(C30))

复制代码

1. =NUMBERSTRING(C30,3)

复制代码

第5题:

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

复制代码

Tank008

1、=SUMPRODUCT(--TEXT(B3:B9,"[<10]!0;[>20]!0;0.00;!0"))

2、=SUBSTITUTE(TEXT(C14,"#"&REPT("!,00",INT(LEN(C14)/2.1))),",0",",")

3、=IF(OR(C22="",D22=""),"漏打卡",TEXT(C22,"[>0.354]迟到;")&TEXT(D22,"[<0.75]早退;"))

4、=TEXT(LEFT(C30,1),"[DBNum1]G/通用格式")&TEXT(LEFT(RIGHT(C30,3),1),"[DBNum1]G/通用格式")&TEXT(LEFT(RIGHT(C30,2),1),"[DBNum1]G/通用格式")&TEXT(RIGHT(C30,1),"[DBNum1]G/通用格式")

5、=TEXT(DATE(MID(C39,FIND("/",C39)+4,4),MID(C39,FIND("/",C39)-2,2),MID(C39,FIND("/",C39)+1,2)),"mmm")

《渐行渐远》

1. 1=SUM(TEXT(B3:B9,"[>20]!0;[<10]!0;0.00;!0")*1)
   
2. =SUMPRODUCT(TEXT(TEXT(B3:B9,"0.00;;;!0")*1,"[>20]!0;[>=10]0.00;!0;!0")*1)
   
3. =SUM(SUMIFS(B3:B9,B3:B9,">=10",B3:B9,"<20"))
   
4. =SUM(SUMIF(B3:B9,{">=10",">20"})*{1,-1})
   
5. 2.=SUBSTITUTE(TEXT(C14,REPT("#0!,",(LEN(C14)-1)/2)&"#0"),",0",",")
   
6. =SUBSTITUTE(TEXT(C14,"#"&REPT("!,00",INT(LEN(C14)/2.1))),",0",",")
   
7. 3.=IF(C22*D22,TEXT(C22,"[>0.354167]迟到;")&TEXT(D22,"[<0.75]早退;"),"漏刷卡")
   
8. 4.=TEXT(C30,"[dbnum1]0")
   
9. 5.=TEXT(MID(C39,FIND("/",C39)-2,2)*30,"mmm")

复制代码

tgy0451

公式说不明白,我传附件吧.
第三题只会用辅助列,不知道时间在TEXT条件中如何表示;
第四题感觉方法太笨,期待高手指教……

李建军

1，=SUMPRODUCT((IF(ISNUMBER(B3:B9),B3:B9)>=10)*(IF(ISNUMBER(B3:B9),B3:B9)<=20),B3:B9)
2，=SUBSTITUTE(TEXT(C14,"#"&REPT("!,00",INT(LEN(C14)/2.01))),",0",",")
3，=IF(C23*D23,TEXT(C23,"[>0.354166]迟到;")&TEXT(D23,"[<0.75]早退;"),"漏刷卡")
4，=TEXT(C30,"[DBNum1]#[$-804]")
5，=TEXT(DATE("2013",MID(C39,FIND("/",C39)-2,2),1),"mmm")