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

chrisfang

第一次出题，不足之处多多包涵。

总结帖见21楼

[此贴子已经被作者于2008-7-23 13:22:01编辑过]

zldccmx

看来这道考题不容易哦，先占位，慢慢研究！

 

QUOTE:

好像没有收到这位朋友的答案——chrisfang

[此贴子已经被chrisfang于2008-7-21 15:13:35编辑过]

willin2000

答案已发送至chrisfang版主, 2种方法 *^__^*  :

优化原来的思路公式长172.

新思路的最新长度163个字符.

QUOTE:

1，218字符： =TEXT(SUM((TEXT(LARGE((A$2:A$16=A2)*SUBSTITUTE(B$2:B$16&"秒","点","时"),COUNTIF(A:A,A2)^{1,0}),"h点m分s")=B2)*{3,1},TEXT(SUBSTITUTE(B2&"秒","点","时"),{"[m]","s"})/24/60^{1,3}),"[>2]!"&A2&"第一次-h点m.00分;[>1]!"&A2&"第二次-h点m.00分;")

2，209字符： =TEXT(LEFT(SUM((TEXT(LARGE((A$2:A$16=A2)*SUBSTITUTE(B$2:B$16&"秒","点","时"),COUNTIF(A:A,A2)^{1,0}),"h点m分s")=B2)*{20,3}))+SUM(TEXT(SUBSTITUTE(B2&"秒","点","时"),{"[m]","s"})/24/60^{1,3}),"[>1]!"&A2&"第aaa次-h点m.00分;")

3，195字符： =TEXT(MIN(IF(TEXT(QUARTILE(IF(A$2:A$16=A2,1*SUBSTITUTE(B$2:B$16&"秒","点","时")),{0,4}),"h点m分s")=B2,{2,3}))+SUM(TEXT(SUBSTITUTE(B2&"秒","点","时"),{"[m]","s"})/24/60^{1,3}),"[>1]!"&A2&"第aaa次-h点m.00分;")

4，163字符： =TEXT(MIN(IF(TEXT(QUARTILE(IF(A$2:A$16=A2,1*SUBSTITUTE(B$2:B$16&"秒","点","时")),{0,4}),"h点m分s")=B2,{2,3}))+SUBSTITUTE(B2&"秒","点","时")/60,"[>1]!"&A2&"第aaa次-m点s.00分;")

5，172字符： =TEXT(MIN(IF(TEXT(LARGE((A$2:A$16=A2)*SUBSTITUTE(B$2:B$16&"秒","点","时"),COUNTIF(A:A,A2)^{1,0}),"h点m分s")=B2,{2,3}))+SUBSTITUTE(B2&"秒","点","时")/60,"[>1]!"&A2&"第aaa次-m点s.00分;")

第4个公式163个字符是所有答题者中最短的公式，实在很精彩，其中所运用到的大部分技巧细节与我的底牌都十分相似 ——chrisfang

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

samsoncsr

短的想不出，481个字符,答案已发送至chrisfang版主。

QUOTE:

481个字符：

=IF(INDEX(B:B,MATCH(MIN(IF(A$2:A$16=A2,--TEXT(SUBSTITUTE(B$2:B$16,"点","时")&"秒","h:m:s"),"")),--TEXT(SUBSTITUTE(B$2:B$16,"点","时")&"秒","h:m:s"),)+1)=B2,A2&"第一次-"&LEFT(B2,FIND("分",B2)-1)&"."&TEXT(-LOOKUP(,-RIGHT(B2,ROW($1:$9)))*5/3,"00")&"分",IF(INDEX(B:B,MATCH(MAX(IF(A$2:A$16=A2,--TEXT(SUBSTITUTE(B$2:B$16,"点","时")&"秒","h:m:s"),"")),--TEXT(SUBSTITUTE(B$2:B$16,"点","时")&"秒","h:m:s"),)+1)=B2,A2&"第二次-"&LEFT(B2,FIND("分",B2)-1)&"."&TEXT(-LOOKUP(,-RIGHT(B2,ROW($1:$9)))*5/3,"00")&"分",""))

老老实实一步一个脚印，小米加步枪也能打赢小日本。不过“--TEXT(SUBSTITUTE(B$2:B$16,"点","时")&"秒","h:m:s")”的做法实在是让人感觉缴获了飞机坦克却又拿去大炼钢铁造成步枪的味道——chrisfang

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

gouweicao78

[em06]只搞到222字符。先发送占位吧。——2008-7-1

目前做到187字符，(*^__^*) 嘻嘻……——2008-7-17

综合前两个解法，第3个188字。

终于想出willin2000兄的163字的解法了，呵呵。2008-7-20 3：00

QUOTE:

1，222个字符：

=TEXT(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"),)+1+SUBSTITUTE(B2&"秒","点","时")/60,"[<4]!"&A2&"第aaa次-m点s.00分;")

2，187个字符：

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

答帖中使用计数方式求取最大最小值的人有不少，但能精简到这个程度的也只有草兄。中间将两部分直接相加解决了不少问题，不过如果题目最后显示结果所要求的字符串前缀再多些，此类公式的短处就要显露出来了。

187个字符的长度激发了不少人的斗志，无论如何都应该感谢草兄。——chrisfang

谢谢，如果“字符串前缀再多些”的话，就是我综合第1、2个解法之后的188字：

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

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

Luckyguy2008

218字符，已发给版主，请批阅，谢谢！

QUOTE:

218个字符：

=TEXT(ABS(SUM((TEXT(LARGE(SUBSTITUTE(SUBSTITUTE(B$2:B$16,"点",":"),"分",":")*(A$2:A$16=A2),COUNTIF(A:A,A2)^{0,1}),"h点m分s")=B2)*{2,-1})),"!"&A2&"第[DBNum1]0次-"""&LEFT(B2,FIND("分",B2)-1)&TEXT(RIGHTB(B2,2)/60,".00分")&""";;")

用“large(...,countif(a:a,a2)^{0,1})”来同时求取最大最小值的做法思路还是蛮别致的，不过没有QUARTILE函数效率高。“ABS(....*{2,-1})”的构思也挺巧妙的。——chrisfang

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

hyc2222

晕,居然用了400多个字符[em06]

先占个位

QUOTE:

454个字符：

=IF((SUM(($A$2:$A$16=$A2)*(TEXT(SUBSTITUTE($B$2:$B$16,"点","时")&"秒","hh:mm:ss")<TEXT(SUBSTITUTE($B2,"点","时")&"秒","hh:mm:ss")))+1)=1,A2&"第一次-"&MID(B2,1,FIND("分",B2)-1)&"."&ROUND(MID(B2,FIND("分",B2)+1,2)/60*100,0)&"分",IF((SUM(($A$2:$A$16=$A2)*(TEXT(SUBSTITUTE($B$2:$B$16,"点","时")&"秒","hh:mm:ss")<TEXT(SUBSTITUTE($B2,"点","时")&"秒","hh:mm:ss")))+1)=COUNTIF($A$2:$A$16,A2),A2&"第二次-"&MID(B2,1,FIND("分",B2)-1)&"."&ROUND(MID(B2,FIND("分",B2)+1,2)/60*100,0)&"分",""))

时间转换结果有错误，“6点48分5”的转换结果应该是“6点48.08分”而不是“6点48.8分”。错误解答不得分，谢谢参与。——chrisfang
 

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

lbzcc

发帖占位，公式太长

向大家学习

 

QUOTE:

1，441个字符：

=IF(MAX(($A$2:$A$16=A2)*(SUBSTITUTE($B$2:$B$16,"点","时")&"秒"))=TIMEVALUE(SUBSTITUTE(B2,"点","时")&"秒"),A2&"第二次－"&TEXT(SUBSTITUTE(B2,"点","时")&"秒","H"&"点"&"m")&TEXT(SECOND(SUBSTITUTE(B2,"点","时")&"秒")/60,".00分"),IF(LARGE(($A$2:$A$16=A2)*(SUBSTITUTE($B$2:$B$16,"点","时")&"秒"),COUNTIF($A$2:$A$16,A2))=TIMEVALUE(SUBSTITUTE(B2,"点","时")&"秒"),A2&"第一次－"&TEXT(SUBSTITUTE(B2,"点","时")&"秒","H"&"点"&"m")&TEXT(SECOND(SUBSTITUTE(B2,"点","时")&"秒")/60,".00分"),""))

2，362字符：

=CHOOSE(MMULT(COLUMN($A:$B),N(LARGE(($A$2:$A$16=A2)*(SUBSTITUTE($B$2:$B$16,"点","时")&"秒"),IF(COUNTIF($A$2:$A$16,A2)=1,2,COUNTIF($A$2:$A$16,A2))^(ROW($1:$2)-1))=TIMEVALUE(SUBSTITUTE(B2,"点","时")&"秒")))+1,"",A2&"第二次－"&LEFT(B2,FIND("分",B2)-1)&TEXT(RIGHT(B2,(LEN(B2)-FIND("分",B2)))/60,".00分"),A2&"第一次－"&LEFT(B2,FIND("分",B2)-1)&TEXT(MID(B2,FIND("分",B2)+1,2)/60,".00分"))

两个解答公式都没有做对，没有满足题目说明中的第5点“如果当天此员工只有一次打卡记录，需要显示结果为‘第一次’”。不能得分，谢谢参与。——chrisfang
 

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

HHAAMM

非常非常丑陋的，就算参与一下（自己都感到不好意思）

 

题目要求数据多方控制，凭偶的水平，几经思考，也没找到简化的思路

（现有公式，偶自己最多只能去掉一二十个字符，反正也不达标，所以也就不简化了）

 

[em04]

QUOTE:

594个字符：

=IF(TIMEVALUE(SUBSTITUTE(SUBSTITUTE(B2,"点",":"),"分",":"))=MIN(IF($A$2:$A$16=A2,TIMEVALUE(SUBSTITUTE(SUBSTITUTE($B$2:$B$16,"点",":"),"分",":")),9)),A2&"第一次-"&LEFT(B2,FIND("点",B2))&TEXT(MID(B2,FIND("点",B2)+1,FIND("分",B2)-FIND("点",B2)-1)+RIGHT(B2,LEN(B2)-FIND("分",B2))/60,"0.00")&"分","")&IF(COUNTIF($A$2:$A$16,A2)=1,"",IF(TIMEVALUE(SUBSTITUTE(SUBSTITUTE(B2,"点",":"),"分",":"))=MAX(($A$2:$A$16=A2)*(TIMEVALUE(SUBSTITUTE(SUBSTITUTE($B$2:$B$16,"点",":"),"分",":")))),A2&"第二次-"&LEFT(B2,FIND("点",B2))&TEXT(MID(B2,FIND("点",B2)+1,FIND("分",B2)-FIND("点",B2)-1)+RIGHT(B2,LEN(B2)-FIND("分",B2))/60,"0.00")&"分",""))

中规中矩，题目要求什么，公式就给什么，朴实的不偷一点懒。做函数公式很多时候就是这样，只要能够照着题意写，还是可以达到目标。

不过在IF函数的逻辑判断层次上还是可以整理一下更合理的条理，可以参考11楼的做法——chrisfang

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

sunya_0529

精简字符到203个啦~~

=TEXT(ABS((B2=LARGE((A2=$A$2:$A$16)*$B$2:$B$16,COUNTIF($A$2:$A$16,A2)))*2-(B2=MAX((A2=$A$2:$A$16)*$B$2:$B$16))),"[dbnum1]!"&A2&"第#次-"&SUBSTITUTE(TEXT(B2,"h点m")&TEXT(SECOND(B2)/60,".00分"),"0","!0")&";;;")

QUOTE:

1，216个字符：

=TEXT(--TEXT(B2,"[="&LARGE((A2=$A$2:$A$16)*$B$2:$B$16,COUNTIF($A$2:$A$16,A2))&"]1;[="&MAX((A2=$A$2:$A$16)*$B$2:$B$16)&"]2;-1"),"[dbnum1]!"&A2&"第#次-"&SUBSTITUTE(TEXT(B2,"h点m")&TEXT(SECOND(B2)/60,".00分"),"0","!0")&";")

2，203个字符：

=TEXT(ABS((B2=LARGE((A2=$A$2:$A$16)*$B$2:$B$16,COUNTIF($A$2:$A$16,A2)))*2-(B2=MAX((A2=$A$2:$A$16)*$B$2:$B$16))),"[dbnum1]!"&A2&"第#次-"&SUBSTITUTE(TEXT(B2,"h点m")&TEXT(SECOND(B2)/60,".00分"),"0","!0")&";;;")

这位朋友自己修改了题目中的B列数据（见附件），这样做显然不符合题目要求。不能得分。

其次，正式竞赛区不允许在回帖中直接给出解答，再次重申此规定。

——chrisfang

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