[第37期]考勤情况统计[已总结]

shaowu459

 想了好多天，想不到办法解决了，只能发个复杂的了……

发帖占位……

QUOTE:

454个字符：

=IF(--SUBSTITUTE(SUBSTITUTE(B2,"点",":"),"分",":")=MIN(IF($A$2:$A$16=A2,--SUBSTITUTE(SUBSTITUTE($B$2:$B$16,"点",":"),"分",":"),24)),A2&"第一次-"&LEFT(B2,FIND("分",B2)-1)&"."&RIGHT(TEXT(--REPLACE(B2,1,FIND("分",B2),"")/60,".00"),2)&"分",IF(--SUBSTITUTE(SUBSTITUTE(B2,"点",":"),"分",":")=MAX(IF($A$2:$A$16=A2,--SUBSTITUTE(SUBSTITUTE($B$2:$B$16,"点",":"),"分",":"),0)),A2&"第二次-"&LEFT(B2,FIND("分",B2)-1)&"."&RIGHT(TEXT(--REPLACE(B2,1,FIND("分",B2),"")/60,".00"),2)&"分",""))

作为金牌优秀会员实在不该写出如此苍白的公式，你答的帖子可比不少人看过的帖子都要多了。不过整个公式还是条理分明，与题目的表面意思完全切合。希望继续努力！你可以做到更好——chrisfang
 

[此贴子已经被chrisfang于2008-7-22 15:17:41编辑过]

syz105729913

285个字符，方法有点笨，先发帖占个位[em06]

QUOTE:

1，285个字符：

=IF(MIN(IF(A$2:A$16=A2,--SUBSTITUTE(B$2:B$16&"秒","点","时")))=--SUBSTITUTE(B2&"秒","点","时"),A2&TEXT(SUBSTITUTE(B2&"秒","点","时")/60,"第一次-m点S.00分"),IF(MAX((A$2:A$16=A2)*--SUBSTITUTE(B$2:B$16&"秒","点","时"))=--SUBSTITUTE(B2&"秒","点","时"),A2&TEXT(SUBSTITUTE(B2&"秒","点","时")/60,"第二次-m点S.00分"),""))

2，224个字符：

=TEXT(SUBSTITUTE(B2&"秒","点","时")/60,CHOOSE(MATCH(1,FREQUENCY(1,(SMALL(IF(A$2:A$16=A2,-SUBSTITUTE(B$2:B$16&"秒","点","时")),IF({1,0},1,COUNTIF(A:A,A2)))=-SUBSTITUTE(B2&"秒","点","时"))*1),),"!"&A2&"第二","!"&A2&"第一",";")&"次-m点S.00分")

第一个解答用到了本题的两个基本考点，“SUBSTITUTE(B2&"秒","点","时")”和“TEXT(SUBSTITUTE(B2&"秒","点","时")/60,"第二次-m点S.00分")”，只要这两个做对，即使最普通的公式结构也能达到290字符的第二档线，这也是设置这条分数线的初衷。

第二个公式没有做对，不符合题目说明中的第5点。因此给2分。——chrisfang

[此贴子已经被chrisfang于2008-7-22 15:16:42编辑过]

micohui

水平有限,只能做到247个字符的,盼精彩答案.

QUOTE:

1，332个字符：

=CHOOSE(TEXT(SUM((--SUBSTITUTE(SUBSTITUTE(B$2:B$16,"点",":"),"分",":")<--SUBSTITUTE(SUBSTITUTE(B2,"点",":"),"分",":"))*(A$2:A$16=A2))+1,"[=1]1;[="&COUNTIF(A:A,A2)&"]2;3"),A2&"第一次-"&LEFT(B2,FIND("分",B2)-1)&TEXT(RIGHT(B2,LEN(B2)-FIND("分",B2))/60,".00分"),A2&"第二次-"&LEFT(B2,FIND("分",B2)-1)&TEXT(RIGHT(B2,LEN(B2)-FIND("分",B2))/60,".00分"),"")

2，247个字符：

=CHOOSE(TEXT(SUM((-SUBSTITUTE(B$2:B$16&"秒","点","时")>=-SUBSTITUTE(B2&"秒","点","时"))*(A$2:A$16=A2)),"[=1]1;[="&COUNTIF(A:A,A2)&"]2;3"),A2&"第一次-"&TEXT(SUBSTITUTE(B2&"秒","点","时")/60,"m点s.00分"),A2&"第二次-"&TEXT(SUBSTITUTE(B2&"秒","点","时")/60,"m点s.00分"),"")

公式思路与gouweicao版主的类似，第二个公式对比第一个公式的简化内容就是我在楼上所提到的两个基本考点，大幅度缩短了公式长度。——chrisfang

[此贴子已经被chrisfang于2008-7-22 15:15:31编辑过]

xcd

答案已发,请查收,期待gouweicao78版主187字符的公式

QUOTE:

1，212字符(EXCEL2007 兼容模式下)

=TEXT(MODE((B2=TEXT(QUARTILE(IF(A:A=A2,--SUBSTITUTE(SUBSTITUTE(B:B,"点",":"),"分",":")),{0,4}),"h点m分s"))*{1,2},1,2),"!"&A2&"[Dbnum1]第0次-"&SUBSTITUTE(LEFT(B2,FIND("分",B2)-1)&TEXT(RIGHTB(B2,2)/60,".00分!;!;"),0,"!0"))

2，187字符(EXCEL2007 兼容模式下)

=LOOKUP("々",IF({0,1,1},IF(B2=TEXT(QUARTILE(IF(A:A=A2,--SUBSTITUTE(SUBSTITUTE(B:B,"点",":"),"分",":")),{0,4,0}),"h点m分s"),A2&{1,"第二次-","第一次-"}&LEFT(B2,FIND("分",B2)-1)&TEXT(RIGHTB(B2,2)/60,".00分")),""))

由于在2007兼容模式下，数组公式可以引用整列如A:A作为参数，因此可以比2003下节省几个单元格引用的相关字符。这样做完全符合题目要求，不过我们在讨论这里的公式时还是以2003的为统一标准。
  

Lookup的做法曾经在我编题目的时候在脑子中一闪而过，感觉可以有不错的解法，但没有具体去实践。而xcd版主使用Lookup做出了很不错的解答。但也许是题目220个字符的最底线实在是比较宽松，因此xcd版主缺少进一步优化的动力。
  

第2个公式如果在2003版本下应该是207个字符：
  

=LOOKUP("々",IF({0,1,1},IF(B2=TEXT(QUARTILE(IF(A$2:A$16=A2,--SUBSTITUTE(SUBSTITUTE(B$2:B$16,"点",":"),"分",":")),{0,4,0}),"h点m分s"),A2&{1,"第二次-","第一次-"}&LEFT(B2,FIND("分",B2)-1)&TEXT(RIGHTB(B2,2)/60,".00分")),""))

如果依照此思路根据楼上的楼上所提到的基本考点稍微优化一下可缩短到184个字符：
  

=LOOKUP("々",IF({0,1,1},IF(B2=TEXT(QUARTILE(IF(A$2:A$16=A2,--SUBSTITUTE(B$2:B$16&"秒","点","时")),{0,4,0}),"h点m分s"),A2&{1,"第二次-","第一次-"}&TEXT(SUBSTITUTE(B2&"秒","点","时")/60,"m点s.00分")),""))
  

如果进一步优化，稍微改一下结构，可以缩短到180个字符：
  

=LOOKUP("々",IF({0,1,1},IF(B2=TEXT(QUARTILE(IF(A$2:A$16=A2,--SUBSTITUTE(B$2:B$16&"秒","点","时")),{0,4,0}),"h点m分s"),A2&TEXT({0,3,2}+SUBSTITUTE(B2&"秒","点","时")/60,"第aaa次-m点s.00分")),""))
  

——chrisfang

[此贴子已经被chrisfang于2008-7-22 17:48:54编辑过]

nocrying

占位，答案已发至版主邮箱，敬请查收，谢谢！

QUOTE:

457个字符：

=IF(--SUBSTITUTE(SUBSTITUTE(B2,"分",":"),"点",":")=MIN(IF($A$2:$A$16=A2,--SUBSTITUTE(SUBSTITUTE($B$2:$B$16,"分",":"),"点",":"))),A2&"第一次-"&REPLACE(B2,FIND("分",B2),3,"."&TEXT(ROUND(--RIGHT(B2,LEN(B2)-FIND("分",B2))*5/3,0),"00")&"分"),IF(--SUBSTITUTE(SUBSTITUTE(B2,"分",":"),"点",":")=MAX(IF($A$2:$A$16=A2,--SUBSTITUTE(SUBSTITUTE($B$2:$B$16,"分",":"),"点",":"))),A2&"第二次-"&REPLACE(B2,FIND("分",B2),3,"."&TEXT(ROUND(--RIGHT(B2,LEN(B2)-FIND("分",B2))*5/3,0),"00")&"分"),""))

和11楼差不多的解法。——chrisfang

[此贴子已经被chrisfang于2008-7-22 16:01:09编辑过]

galaxy123456

答案已发!占位!

QUOTE:

279字符：

=CHOOSE(MATCH(B2,TEXT(CHOOSE({1,2,3},MIN(IF(A$2:A$16=A2,--SUBSTITUTE(B$2:B$16&"秒","点","时"))),MAX(IF(A$2:A$16=A2,--SUBSTITUTE(B$2:B$16&"秒","点","时"))),B2),"H点M分s"),),A2&"第一次-"&TEXT(SUBSTITUTE(B2&"秒","点","时")/60,"m点s.00分"),A2&"第二次-"&TEXT(SUBSTITUTE(B2&"秒","点","时")/60,"m点s.00分"),"")

连续使用两个choose函数替代了if函数进行三种情况的判断，公式原理在实质上与12楼类似，两个题目所预设的基本步点都踩对了。

——chrisfang

[此贴子已经被chrisfang于2008-7-22 16:17:10编辑过]

yoka

答案已发,占位!

QUOTE:

1，248个字符：

=CHOOSE(TEXT(SUM((--SUBSTITUTE(B$2:B$16&"秒","点","时")<=--SUBSTITUTE(B2&"秒","点","时"))*(A$2:A$16=A2)),"[<2];[="&COUNTIF(A:A,A2)&"]2;3"),A2&"第一次-"&TEXT(SUBSTITUTE(B2&"秒","点","时")/60,"m点s.00分"),A2&"第二次-"&TEXT(SUBSTITUTE(B2&"秒","点","时")/60,"m点s.00分"),"")

2，243个字符：

=TRIM(MID(SUBSTITUTE(A2&"第一1"&A2&"第二1",1,TEXT(SUBSTITUTE(B2&"秒","点","时")/60,"次-m点s.00分")&REPT(" ",40)),MAX(TEXT(SUM((--SUBSTITUTE(B$2:B$16&"秒","点","时")<--SUBSTITUTE(B2&"秒","点","时"))*(A$2:A$16=A2)),"[<1];[="&COUNTIF(A:A,A2)-1&"]1;2")*40,1),40))

3，218个字符：

=TRIM(LEFT(TEXT(SUM((A$2:A$16=A2)*(-SUBSTITUTE(B$2:B$16&"秒","点","时")<=-SUBSTITUTE(B2&"秒","点","时"))),"[="&COUNTIF(A:A,A2)&"]!"&A2&"第一;"&"[=1]!"&A2&"第二;"&REPT("  ",9))&TEXT(SUBSTITUTE(B2&"秒","点","时")/60,"次-m点s.00分"),14))

4，212个字符：

=LEFT(TEXT(SUM((A$2:A$16=A2)*(-SUBSTITUTE(B$2:B$16&"秒","点","时")<=-SUBSTITUTE(B2&"秒","点","时"))),"[="&COUNTIF(A:A,A2)&"]!"&A2&"第一;"&"[=1]!"&A2&"第二;"&REPT("  ",9))&TEXT(SUBSTITUTE(B2&"秒","点","时")/60,"次-m点s.00分"),14)

第3、第4两个公式中添加空格字符的部分构思还是比较巧妙的，虽然题目没有明确，但还是该以第3个公式218个字符的结果为准，第4个公式毕竟生成的结果只是显示效果。

依照此思路，还可以优化为195个字符：

=TRIM(LEFT(TEXT(MAX((B2=TEXT(QUARTILE(IF(A$2:A$16=A2,--SUBSTITUTE(B$2:B$16&"秒","点","时")),{0,4}),"h点m分s"))*{9,3}),"!"&A2&"第aaa次-;;"&REPT("  ",9))&TEXT(SUBSTITUTE(B2&"秒","点","时")/60,"m点s.00分"),14))

——chrisfang
 

[此贴子已经被chrisfang于2008-7-22 17:00:07编辑过]

baeta

780字，新手答题，见笑见笑。邮件已发送

QUOTE:

780个字符：

=IF(ABS(TIME(LEFT(B2,FIND("点",B2)-1),SUBSTITUTE(MID(B2,FIND("点",B2)+1,2),"分",""),MID(B2,FIND("分",B2)+1,2))-MIN(TIME(LEFT(B$2:B$16,FIND("点",B$2:B$16)-1),SUBSTITUTE(MID(B$2:B$16,FIND("点",B$2:B$16)+1,2),"分",""),MID(B$2:B$16,FIND("分",B$2:B$16)+1,2))+1-(A$2:A$16=A2)))<0.00001,A2&"第一次-"&LEFT(B2,FIND("点",B2)-1)&"点"&FIXED(SUBSTITUTE(MID(B2,FIND("点",B2)+1,2),"分","")+MID(B2,FIND("分",B2)+1,2)/60,2)&"分",IF(TIME(LEFT(B2,FIND("点",B2)-1),SUBSTITUTE(MID(B2,FIND("点",B2)+1,2),"分",""),MID(B2,FIND("分",B2)+1,2))=MAX((TIME(LEFT(B$2:B$16,FIND("点",B$2:B$16)-1),SUBSTITUTE(MID(B$2:B$16,FIND("点",B$2:B$16)+1,2),"分",""),MID(B$2:B$16,FIND("分",B$2:B$16)+1,2)))*(A$2:A$16=A2)),A2&"第二次-"&LEFT(B2,FIND("点",B2)-1)&"点"&FIXED(SUBSTITUTE(MID(B2,FIND("点",B2)+1,2),"分","")+MID(B2,FIND("分",B2)+1,2)/60,2)&"分",""))

很喜欢你坚持排除困难的精神，虽然是答帖里面最长的公式，但出题的目的就是要让大家看看，一些简单的小技巧究竟可以产生多大的差别——chrisfang
 

[此贴子已经被chrisfang于2008-7-22 17:05:07编辑过]

huisiming

答案已发送,占位,209个字符.

QUOTE:

215字符：

=TRIM(LEFT(TEXT(SUM((-SUBSTITUTE(B$2:B$16&"秒","点","时")>=-SUBSTITUTE(B2&"秒","点","时"))*(A$2:A$16=A2)),"[=1]!"&A2&"第一;[="&COUNTIF(A:A,A2)&"]!"&A2&"第二;"&REPT(" ",40))&TEXT(SUBSTITUTE(B2&"秒","点","时")/60,"次-m点s.00分"),14))

思路与17楼yoka版主的基本一致，同样也可以如17楼评分说明中的方法进行简化。——chrisfang
 

[此贴子已经被chrisfang于2008-7-22 17:09:03编辑过]

水星钓鱼

占位吧，本来不想发的，看micohui老大都才做到332个字符，我也厚着脸皮发出来吧。

QUOTE:

294字符：

=IF(OR(B2=TEXT(QUARTILE(IF(A$2:A$16=A2,--SUBSTITUTE(B$2:B$16&"秒","点","时")),{0;4}),"h点m分s")),A2&TEXT((B2=TEXT(QUARTILE(IF(A$2:A$16=A2,--SUBSTITUTE(B$2:B$16&"秒","点","时")),{0;4}),"h点m分s"))*{1;-1},"第一次-;;第二次-")&LEFT(B2,FIND("点",B2))&TEXT(TEXT("0时"&MID(B2,FIND("点",B2)+1,5)&"秒","")/60,".00分"),"")

抱歉，超过答题时间，不予评分

公式的思路比较别致，关键就是第一步就先用了一个判断，不过这个判断部分重复出现了两次，因此造成效率较低。

想到了使用QUARTILE函数，但没有找到比较理想的使用方式。否则公式再稍微整理一下可以在250个字符以下。

此外，标红部分的{1;-1}实在属于多余，直接*1也可以得到答案。这部分也挺别致：用了数组运算，却其实没用上数组。也许之前有更好的思路，只是到后来用了前面这个判断条件，因此数组的作用就被废掉了。 ——chrisfang
 

[此贴子已经被chrisfang于2008-7-22 17:36:11编辑过]