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

26759761@qq.com

1、作业统一贴公式(需要过程的直接录屏).
2、跟帖不要重复占楼,要修改直接在原楼层编辑
3、评分标准:答案正确+15/+15,个别优秀的+1-10分.不贴公式直接上附件的,评分标准上,经验BB各扣5分.即+10/+10

滴答滴

1、
=SUM(--TEXT(TEXT(B3:B9,"[>=10];!0;!0;!0"),"[<=20];!0"))
=SUM(--TEXT(B3:B9,"[<10]!0;[>20]!0;0.00;!0"))
=SUMIFS(B3:B9,B3:B9,">=10",B3:B9,"<=20")
=SUM(IF((B3:B9>=10)*(B3:B9<=20),B3:B9))
2、
=SUBSTITUTE(TEXT(C14,0&REPT("!,00",LEN(C14)/2.1)),",0",",")
3、
=IF(C22*D22,TEXT(C22,"[>.35417]迟到;")&TEXT(D22,"[<.75]早退;"),"漏刷卡")
=IF(C22*D22,IF(C22>17/48,"迟到",)&IF(D22<3/4,"早退",),"漏刷卡")

下面两个公式是一种参数调制的思路，要求下班打开时间不得早于12:00:00，也基本符合实际情况
=TEXT(TEXT(PRODUCT(INT(C22:D22/{0.177084,0.249999}))-2,"0;漏刷卡;早退"),"[=4]迟到;[=2]迟到早退;")
=CHOOSE(PRODUCT(INT(C22:D22/{0.177084,0.249999}))+1,"漏刷卡",,"早退","","迟到早退",,"迟到")
4、
=MID(TEXT("."&C30&1,"[dbnum1]"),3,4)
5、
=TEXT(MID(C39,FIND("/",C39)+4,4)&"/"&MID(C39,FIND("/",C39)-2,5),"ddd")
=TEXT("13/"&MID(C43,FIND("/",C43)-2,5),"ddd")
之前看成求星期几了，原来是求月份，求月份的话上面的ddd换成mmm，然后可以下面这样写
=text(mid(c39,find("/",c39)-2,2)&-1,"mmm")
=text(mid(c39,find("/",c39)-2,2)*30,"mmm")
=text(text(mid(c39,find("/",c39)-2,2),"0/1"),"mmm")
关于首位定位还可以
=text(mid(c39,min(find({0,1},c39&0,4)),2),"mmm")

XMergeD

1

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

复制代码

1. =SUM(--TEXT(B3:B9,"[<10]!0;[>20]!0;G/通用格式;!0"))

复制代码

如果只限于题目数据可以把通用格式改成0.00
2

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

复制代码

3

1. =IF(C22*D22,"漏刷卡",TEXT(C22,"[>0.354167]迟到;")&TEXT(D22,"[<0.75]早退;"))

复制代码

4

1. =TEXT(C30,"[DBnum1]0")

复制代码

1. =NUMBERSTRING(C30,3)

复制代码

5

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

复制代码

hsl215

第一题

1. =SUMPRODUCT(--TEXT(B3:B9,"[<10]\0;[>20]\0;#.##;\0"))
   
2. =SUM(--TEXT(B3:B9,"[<10]\0;[>20]\0;#.##;\0"))
   
3. =SUM(IF((B3:B9>=10)*(B3:B9<=20),B3:B9))

复制代码

第二题

1. =SUBSTITUTE(TEXT(C14,"#"&REPT("\,##",LEN(C14)/2.1)),",0",",")

复制代码

第三题

1. =TEXT(TEXT(TEXT(SUM(COUNTIF(C22,{"<=8:30";">8:30"})*{7;4},COUNTIF(D22,{">=18:0";"<18:0"})*{2;1}),"[<5]漏刷卡;[<6]迟到早退;#"),"[<7]迟到;[<8]漏刷卡;#"),"[<9]早退;")
   
2. =TEXT(TEXT(IF(C23*D23,IF(C23>17/48,IF(D23<3/4,5,6),IF(D23<3/4,8,9))),"[<6]迟到早退;[<8]迟到;#;漏刷卡"),"[<9]早退;")
   
3. =TEXT(-(C22>17/48)*D22,";迟到")&TEXT(-(D22<3/4)*C22*D22,";早退")&TEXT(C22*D22,";;漏刷卡")
   
4. =TEXT(-(C23>17/48)*D23,";迟到")&TEXT(-1^(D23<3/4)*C23*D23,";早退;漏刷卡")
   
5. =IF(C22*D22,IF(C22>17/48,"迟到",)&IF(D22<3/4,"早退",),"漏刷卡")

复制代码

第四题

1. =TEXT(C30,"[dbnum1]#")
   
2. =TEXT(C30,"[dbnum1]0")

复制代码

第五题

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

复制代码

取巧公式

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

复制代码

ANDY0393

第一题：数组公式

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

复制代码

第二题：

1. =SUBSTITUTE(TEXT(C14,"#"&REPT("!,00",LEN(C14)/2-(MOD(LEN(C14),2)=0))),",0",",")
   
2. =SUBSTITUTE(TEXT(C14,"#"&REPT("!,00",LEN(C14)/2.1)),",0",",")

复制代码

第三题：

1. =IF(COUNTBLANK(C22:D22),"漏刷卡",TEXT(C22,"[>"&8.5/24&"]迟到;")&TEXT(D22,"[<"&18/24&"]早退;"))

复制代码

第四题：

1. =NUMBERSTRING(C30,3)
   
2. =TEXT(NUMBERSTRING(C30,3),";;;@")这样写过于无赖了的，充个数吧

复制代码

第五题：

1. =TEXT("2014/"&MID(C39,FIND("/",C39)-3,6),"MMM")

复制代码

sinowonder

1、{=SUM(--TEXT(TEXT(B3:B9,"[>=10]0.00;;!0;!0"),"[<=20]0.00;;!0;!0"))}
2、=SUBSTITUTE(TEXT(C14,"#"&REPT("!,00",LEN(C14)/2.1)),",0",",")
3、=IF(C22*D22,TEXT(C22,"[>0.354167]迟到;;;")&TEXT(D22,"[<0.75]早退;;;"),"漏刷卡")
4、=LEFT(TEXT(C30&"-1-1","[dbnum1]e年m月d日"),4)
5、=TEXT(MID(C39,FIND("/",C39)-2,2)&"-1","[$-809]MMM")

注：更好公式见附件，已修改。

ldxhzy

第1题 ：   D3数组公式：
         =SUM(--TEXT(B3:B9,"[<10]\0;[>20]\0;0.00;\0"))

第2题：   E14公式：（下拉）
        =IF(LEFT(SUBSTITUTE(SUBSTITUTE(TEXT(C14,REPT("00"&"\,",9)&"00"),"00,",""),",0",","),1)="0",MID(SUBSTITUTE(SUBSTITUTE(TEXT(C14,REPT("00"&"\,",9)&"00"),"00,",""),",0",","),2,99),SUBSTITUTE(SUBSTITUTE(TEXT(C14,REPT("00"&"\,",9)&"00"),"00,",""),",0",","))

第3题：

第4题：

第5题 ：G39数组公式：（下拉）
=TEXT(SUM(IFERROR(DATE(RIGHT(MID(C39,ROW(1:99),10),4),MID(MID(C39,ROW(1:99),10),1,2),MID(MID(C39,ROW(1:99),10),4,2)),0)),"[$-809]mmm")

满坛皆为吾师

题一

1. =SUM(--TEXT(B3:B9,"[<10]!0;[>20]!0;0.00;!0"))
   
2. =SUM(IF((B3:B9>=10)*(B3:B9<=20),B3:B9))
   
3. =SUM(SUMIF(B3:B9,{">=10",">20"})*{1,-1})

复制代码

题二

1. =SUBSTITUTE(TEXT(C14,"0"&REPT("!,##",(LEN(C14)-1)/2)),",0",",")

复制代码

题三

1. =TEXT(TEXT((3^(C22<0.35486)+2^(D22>=0.75))*(LEN(C22&D22)>9)-2,"0;漏打卡;迟到早退"),"[=3]"""";[=1]迟到;早退")
   
2. =TEXT(COUNTBLANK(C22:D22),"漏打卡;;"&TEXT(C22-"8:30","迟到;;")&TEXT(D22-"18:",";早退;"))

复制代码

题四
不能扩展

1. =TEXT(MID(C39,FIND("/",C39)-2,2)&-1,"mmm")      *28 *29 等

复制代码

可以就本题形成完整日期格式

1. =TEXT(LOOKUP(9^9,--("13/"&MID(C39&"!",ROW($1:$99),6))),"mmm")
   
2. =TEXT(MID(C39,FIND("/",C39)+4,4)&"/"&MID(C39,FIND("/",C39)-2,5),"mmm")

复制代码

yqnh

e11:yqnh上交第六课作业，请学委老师批改指点
一、=SUM(--(TEXT(B3:B9,"[>20]!0;[>=10]0.00;!0;!0")))数组
二、=MID(SUBSTITUTE(TEXT(C14,REPT("!,00",ROUNDUP(LEN(C14)/2,0))),",0",","),2,99)
三、=IF(C22*D22,TEXT(C22-17/48,"迟到;;;")&TEXT(D22-3/4,";早退;;"),"漏刷卡")
四、=TEXT(C30,"[DBNUM1]0")
五、=TEXT(MID(C39,FIND("/",C39)-2,2)&"-1","mmm")

一杯清荼

题1

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

复制代码

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

复制代码

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

复制代码

题2

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

复制代码

题3

1. =IF(C23*D23,TEXT(C23,"[>"&17/48&"]迟到;")&TEXT(D23,"[<"&3/4&"]早退;"),"漏刷卡")

复制代码

1. =TEXT(TYPE(C23/D23)-16,";"&TEXT((C23>17/48)*1,"迟到;;")&TEXT((D23<3/4)*1,"早退;;")&";漏打卡")

复制代码

题4

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

复制代码

1. =TEXT(C30&-1,"[dbnum1]e")

复制代码

题5

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

复制代码

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

复制代码