[开_136]工时合计

delete_007

题目说明：
   1、工作时长由d(天)和h(小时)组成，d是整数且0<=d<=999;h是0.5的整数倍且0<h<8。
   2、8h=1d，即求和时，满8小时进位为1天。求每位员工的合计工时。
   3、如果工时合计为0d0h，合计列不显示；如果区域无数据，合计列也不显示；如模拟结果所示。
   4、限函数公式完成。不允许定义名称、辅助列、VBA。
   5、公式长度（含等号）小于250字符 奖励技术分1分，小于190字符奖励技术分2分。
   6、数据形式说明：d在h前面，即如果同时有天数和小时数，数据形式为 *d*h ，无小时数为 *d，无天数为 *h
   7、答题截止日期：2012-11-4




该贴已经同步到 delete_007的微博

hjj0451

几种方法都未能取得简洁的效果，先列出来，看能不能找到更好的办法：
法一，复数法，226：

1. =TEXT(INT(IMREAL(IMPRODUCT(IMSUM(SUBSTITUTE(TEXT(SUBSTITUTE(0&B2:F2,"h",),"0+0")&"i","d","+0")),"8-i"))/8),"0\d;;")&SUBSTITUTE(MOD(IMAGINARY(IMSUM(SUBSTITUTE(TEXT(SUBSTITUTE(0&B2:F2,"h",),"0+0.0")&"i","d","+0"))),8)&"h","0h",)

复制代码

法二，分数法，271:

1. =SUBSTITUTE(TEXT(INT(SUM(COUNTIF(B2:F2,"*.5h")/80+SUBSTITUTE(TEXT(SUBSTITUTE(SUBSTITUTE(0&B2:F2,".5",),"h",),"0 0")&"/8","d"," 0"))),"0\d;;")&MOD(SUM(8*SUBSTITUTE(TEXT(SUBSTITUTE(SUBSTITUTE(0&B2:F2,".5",),"h",),"0 0")&"/8","d"," 0"))+COUNTIF(B2:F2,"*.5h")/2,8)&"h","0h",)

复制代码

                         185：

1. =SUBSTITUTE(SUBSTITUTE(TEXT(SUM(MID(SUBSTITUTE(TEXT(SUBSTITUTE(0&B2:F2,"h",),"0   0.0"),"d","  0"),{1;5},{4;9})/{1;8})-1%,"#\d0/8;"),"/8",RIGHT(".5h",2--1^COUNTIF(B2:F2,"*.5h"))),"0h",)

复制代码

"*.5h"应该可以改为"*.*"省1字符，如果原始数据没有1.0h这样的格式的话。
法三，直接法，190：

1. =SUBSTITUTE(TEXT(INT(SUM(ISNUMBER(MATCH("*d"&ROW(A:A)/{1,2}&{"d*","h*"},"d"&B2:F2,))*ROW(A:A)/{1,16})),"0\d;;")&MOD(SUM(ISNUMBER(FIND("d"&ROW(A:A)/2&"h","d"&B2:F2))*ROW(A:A))/2,8)&"h","0h",)

复制代码

用MATCH在重复值处理时存在问题：
还是用FIND，211：

1. =SUBSTITUTE(TEXT(INT(SUM((1-ISERR(FIND(0&ROW(A:A)&"d",0&B2:F2))+ISNUMBER(FIND("d"&ROW(A:A)/2&"h","d"&B2:F2))/16)*ROW(A:A))),"0\d;;")&MOD(SUM(ISNUMBER(FIND("d"&ROW(a:a)/2&"h","d"&B2:F2))*ROW(a:a))/2,8)&"h","0h",)

复制代码

试一下文本分段处理法，198：

1. =SUBSTITUTE(TEXT(INT(SUM(MID(SUBSTITUTE(TEXT(SUBSTITUTE(0&B2:F2,"h",),"0     0"),"d","    0"),{1;7},6)/{1;8})),"0\d;;")&MOD(SUM(ISNUMBER(FIND("d"&ROW(A:A)/2&"h","d"&B2:F2))*ROW(A:A))/2,8)&"h","0h",)

复制代码

8个" "，前半部分长度同复数法。
法四，时间法，165:

1. =SUBSTITUTE(TEXT(SUM(TEXT(SUM(ISNUMBER(MATCH("*d"&ROW(A:A)/{1,2}&{"d*","h*"},"d"&B2:F2,))*ROW(A:A)/{6,96}/240),{"[m]","s.00"})%/{14.4,6480}),"[m]\ds.0\h;;"),"0.0h",)

复制代码

还差od没有处理掉，同时MATCH在重复值处理时也存在问题:

1. =SUM(TEXT(SUM(TEXT(SUM(--("0:"&SUBSTITUTE(TEXT(SUBSTITUTE(B2:F2,"h",),"0\:0.0"),"d",":0"))),{"[m]","s.0"})/{6,48}/240),{"[m]","s.0"})%/{14.4,6480})

复制代码

超出嵌套，需要自定义格式[m]\ds.0\h;;。
做的好纠结，可能走弯路了。

Zaezhong

我先占个楼,長長的裹腳布

1. =SUBSTITUTE(SUBSTITUTE(TEXT(SUM(-TEXT(SUBSTITUTE(SUBSTITUTE({" ";"0d"}&SUBSTITUTE(B2:F2,".5",),"d"," "),"h","/8"),"G/通用格式;;0;\0"))-COUNTIF(B2:F2,"*.*")/16+1%,";#\d0/8;"),"/8",IF(-1^COUNTIF(B2:F2,"*.*")>0,"h",".5h")),"0h",)

复制代码

1. =SUBSTITUTE(TEXT(SUM(MID(SUBSTITUTE("d"&B2:F2,"d",".000000",2-ISERR(FIND("d",B2:F2))),{2;6},LEN(B2:F2&0))/{1;8})-1%,"#\d"&IF(-1^COUNTIF(B2:F2,"*.*")<0,0.5,"#")&"/8\d;"),"/8d","h")

复制代码

wangg913

附件不上了
1、

1. =SUBSTITUTE(TEXT(SUM(MID(SUBSTITUTE("d"&B2:F2,"d",".000000",2-ISERR(FIND("d",B2:F2))),{2;6},1+LEN(B2:F2))/{1;8})-1%,"#\d"&IF(-1^COUNTIF(B2:F2,"*.*")-1,0.5,"#")&"\h/8\d;;"),"/8d",)

复制代码

2、

1. =SUBSTITUTE(TEXT(SUM(COUNTIF(B2:F2,{"","","*d"}&ROW($1:$999)/{1,2,2}&{"d*","h","h"})*ROW($1:$999)/{1,16,16})-1%,"#\d"&IF(-1^COUNTIF(B2:F2,"*.*")-1,0.5,"#")&"\h/8\d;;"),"/8d",)
   

复制代码

简化一下，使用ROW(A:A) ，有点慢，呵。

1. =SUBSTITUTE(TEXT(SUM(COUNTIF(B2:F2,{"","","*d"}&ROW(A:A)/{1,2,2}&{"d*","h","h"})*ROW(A:A)/{1,16,16})-1%,"#\d"&IF(-1^COUNTIF(B2:F2,"*.*")-1,0.5,"#")&"\h/8\d;;"),"/8d",)
   

复制代码

1. 
   
2. =SUBSTITUTE(TEXT(SUM(COUNTIF(B2:F2,{"","","*d"}&ROW(A:A)/10&{"d*","h","h"})*ROW(A:A)/{10,81,81}),"#\d"&IF(-1^COUNTIF(B2:F2,"*.*")<0,0.5,"#")&"\h/8\d;;"),"/8d",)

复制代码

3、

1. =SUBSTITUTE(TEXT(SUM(SUBSTITUTE(LEFT(0&B2:F2,FIND({"d";"h"},B2:F2&"dh")),LEFT(B2:F2,FIND("d",B2:F2&"d")),)/{1;8})-1%,"#\d"&IF(-1^COUNTIF(B2:F2,"*.*")<0,0.5,"#")&"\h/8\d;;"),"/8d",)

复制代码

4、

1. 
   
2. =SUBSTITUTE(TEXT(SUM(12.5*TEXT(MID(SUBSTITUTE(SUBSTITUTE(B2:F2,"d","0/125  "),"h","%"),{1;9},8),"\0 @"))-1%,"#\d"&IF(-1^COUNTIF(B2:F2,"*.*")-1,0.5,"#")&"\h/8\d;;"),"/8d",)

复制代码

简化一下：

1. 
   
2. =SUBSTITUTE(TEXT(SUM(12.4*TEXT(MID(SUBSTITUTE(SUBSTITUTE(B2:F2,"d","0/124  "),"h","%"),{1;9},8),"\0 @")),"#\d"&IF(-1^COUNTIF(B2:F2,"*.*")-1,0.5,"#")&"\h/8\d;;"),"/8d",)
   

复制代码

ff8feng

{:soso_e141:}先来个长的，有时间再考虑精简
=SUBSTITUTE(INT(SUM((0&TRIM(LEFT(RIGHT(SUBSTITUTE(SUBSTITUTE("0d"&B2:F2,"d","     "),"h",),{10;5}),5)))/{1;8}))&"d","0d",)&SUBSTITUTE(MOD(SUM(--(0&TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE("0d"&B2:F2,"d","     "),"h",),5)))),8)&"h","0h",)

xgg2001

1. =TEXT(INT(SUM(MID(RIGHT(10^11&SUBSTITUTE(B2:F2,"d",".000"),11),{1;8},{7;3})/{1;8})),"0\d;;")&TEXT(MOD(SUM(--SUBSTITUTE(SUBSTITUTE(0&B2:F2,"d",128),"h",)),8),"G/通用格式\h;;")
   
2. 
   

复制代码

5blessyou

这一题应该算是做竞赛题以来相对简单的题目，为什么说这个题目简单呢，因为拿1分非常容易
先上1个无耻的公式吧！轻拍啊，161字符

1. =SUBSTITUTE(TEXT(SUM(COUNTIF(B2:F2,{"","*d",""}&ROW(A:A)/2&{"d*","h","h"})/{1,8,8}/2*ROW(A:A))-1%,"#\d"&IF(-1^COUNTIF(B2:F2,"*.*")+1,"#",0.5)&"/8\d;"),"/8d","h")

复制代码

text函数变幻莫测啊，毕竟大家电脑不是神机，不使用ROW(A:A)吧，公式为171字符，如果去掉1998前面的$为169字符

1. =SUBSTITUTE(TEXT(SUM(COUNTIF(B2:F2,{"","*d",""}&ROW($1:$1998)/2&{"d*","h","h"})/{1,8,8}/2*ROW($1:$1998))-1%,"#\d"&IF(-1^COUNTIF(B2:F2,"*.*")+1,"#",0.5)&"/8\d;"),"/8d","h")

复制代码

利用两个TEXT合并是比较简单的思路，也是本人比较喜欢用的思路，不存在什么严谨和进位的问题，验证也简单，第一个TEXT算天数d，第二个TEXT算小时h，公式195字符

1. =TEXT(INT(SUM(COUNTIF(B2:F2,{"","*d",""}&ROW($1:$1998)/2&{"d*","h","h"})/{1,8,8}/2*ROW($1:$1998))),"0\d;;")&TEXT(MOD(SUM(COUNTIF(B2:F2,{"*d",""}&ROW($1:$15)/2&"h")/2*ROW($1:$15)),8),"G/通用格式\h;;")

复制代码

利用两个TEXT的公式思路好拓展，不存在什么0.5倍的问题，0.1倍也是可以轻松实现，只是计算量扩大了不少，这里就不写了，再上一个可以应对h在前d在后的公式,183字符

1. =SUBSTITUTE(TEXT(SUM(COUNTIF(B2:F2,{"","*h","*d",""}&ROW($1:$1998)/2&{"d*","d","h","h*"})/{1,1,8,8}/2*ROW($1:$1998))-1%,"#\d"&IF(-1^COUNTIF(B2:F2,"*.*")+1,"#",0.5)&"/8\d;"),"/8d","h")

复制代码

CheryBTL

水平有限，想了一周只能想出这个擦边球，243字符，有空再努力精简吧：

1. =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXT(SUM((0&SUBSTITUTE(MID(B2:F2,(FIND("d",B2:F2&"d")<=LEN(B2:F2))*FIND("d",B2:F2&"d")+1,9),"h",))/8+TEXT(LEFT(B2:F2,FIND("d",B2:F2&"d")-1),"0;;0;!0")),"[<1]00/80;0!d00/80"),"00/80",),"5/80",".5h"),"0/80","h")

复制代码

熊小华

1. =TEXT(INT(SUM(MID(SUBSTITUTE(TEXT(SUBSTITUTE("0h"&B2:F2,"h",),"0      0.0"),"d","      0"),{1;7},6)/{1;8})),"0!d;;")&TEXT(MOD(SUM(MID(SUBSTITUTE(TEXT(SUBSTITUTE("0h"&B2:F2,"h",),"0      0.0"),"d","      0"),{1;7},6)/{1;8}),1)*8,"G/通用格式!h;;")

复制代码

纯凑数。241字

洛梅

尝试了，做不出来，得努力去学习了{:soso_e127:}