[开_136]工时合计

11005046

1. =TEXT(INT(SUM(COUNTIF(B2:F2,TEXT(ROW($1:$1999)-1,"0!d;;")&TEXT(COLUMN(A:P)/2-0.5,"G/通用格式!h;;"))*(ROW($1:$1999)-1+(COLUMN(A:P)/2-0.5)/8))),"0!d;;")&TEXT(MOD(SUM(ISNUMBER(SEARCH("*d"&ROW($1:$16)/2&"h","d"&B2:F2))*ROW($1:$16)/2)/8,1)*8,"G/通用格式!h;;")

复制代码

超长公式

一嘟噜钥匙

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXT(SUM((0&MID(100&SUBSTITUTE(B2:F2,"d","d00"),FIND({"d";"h"},SUBSTITUTE(B2:F2,"d","d00")&"000dh"),3))*{8;1})/8,"#\d00/80\h;;"),"0/80",),"5/80",".5"),"d0h","d")

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXT(SUM(--SUBSTITUTE(TEXT("0"&SUBSTITUTE(SUBSTITUTE(B2:F2,"h","0"),".50","5"),"0 00")&"/80","d"," 0")),"#\d00/80\h;;"),"0/80",),"5/80",".5"),"d0h","d")

lishengping666

20121022工时合计.zip (6.14 KB, 下载次数: 16)

2012-11-1 16:45 上传

点击文件名下载附件

如下183个字符，通过测试:
'=SUBSTITUTE(SUBSTITUTE(TEXT(SUM(IFERROR(LEFT(B2:F2,FIND("d",B2:F2)-1),)+REPLACE(SUBSTITUTE(B2:F2&0,"h0",),1,IFERROR(FIND("d",B2:F2),),)/8),"[>]#\d#/80\d;"),"5/80d",".5h"),"0/80d","h")

titi012

=TEXT(INT(SUM(IF(ISERR(FIND("d",B2:F2)),0,LEFT(B2:F2,FIND("d",B2:F2)-1))+RIGHT(SUBSTITUTE(SUBSTITUTE(0&B2:F2,"h",""),"d",0)*10,2)/80)),"0\d;;")&TEXT(MOD(SUM(--RIGHT(SUBSTITUTE(SUBSTITUTE(0&B2:F2,"h",""),"d",0)*10,2)),80)/10,"g/通用格式\h;;")
公式很长,要换思路,有空再想想,

fugb-2010

G2=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXT(SUM(LEFT(RIGHT(SUBSTITUTE("d"&B2:F2,"d",".0000000"),{8;15}),7)/{8;1}),"#!d00/80!d;;"),"0/80d","h"),"5/80d",".5h"),"0h",)

解题思路:
⑴、B2:F2每个单元前加"d"字符,并以".0000000"字符串替换之形成新字符串
⑵、对数据⑴字符串倒数提取并分列以d型式进行求和
    数据⑵=SUM(LEFT(RIGHT(SUBSTITUTE(数据⑴,"d",".0000000"),{8;15}),7)/{8;1})
⑶、对数据⑵以"00d00/80d"形式显示
    数据⑶=TEXT(数据⑵,"#!d00/80!d;;")
⑷、"0/80d"替换主"h"、"5/80d"替换为".5h"、"0h"替换为空白
    数据⑷=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXT(数据⑶,"#!d00/80!d;;"),"0/80d","h"),"5/80d",".5h"),"0h",)

swyrlbq

笨方法，248字符：

=TEXT(SUM(ISNUMBER(FIND(0&ROW($1:$999)&"d",0&B2:F2))*ROW($1:$999))+INT(SUM(ISNUMBER(FIND("d"&ROW($1:$15)/2&"h","d"&B2:F2))*ROW($1:$15)/2)/8),"[=] ;0!d")&SUBSTITUTE(MOD(SUM(ISNUMBER(FIND("d"&ROW($1:$15)/2&"h","d"&B2:F2))*ROW($1:$15)/2),8)&"h","0h",)

sharprain

1. =TEXT(INT(SUM(COUNTIF(B2:F2,ROW(A:A)&"d*")*ROW(A:A))+SUM(ISNUMBER(FIND("d"&ROW($1:$15)/2&"h","d"&B2:F2))*ROW($1:$15)/16)),"0!d;;")&TEXT(MOD(SUM(ISNUMBER(FIND("d"&ROW($1:$15)/2&"h","d"&B2:F2))*ROW($1:$15)/2),8),"G/通用格式!h;;")

复制代码

题目倒是难，难点主要是字数限制。写了个223字节的可以得到结果。

lsj128568

昨天才看到，有事了没时间想了
=TEXT(SUM(MMULT(--MID(SUBSTITUTE(SUBSTITUTE(IF(FIND("d",B2:F2&"d")>LEN(B2:F2),"d"&B2:F2,B2:F2),"h",),"d","  00"),{1;4},{3;9}),ROW($1:$5)^0)/{1;8}),"0!d;;;")&SUBSTITUTE(TEXT(MOD(SUM(--RIGHT(SUBSTITUTE(SUBSTITUTE("d"&B2:F2,"h",),"d","000"),3)),8),"#.0!h;;;"),".0",)

hjj0451

fugb-2010 发表于 2012-11-2 07:10
G2=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXT(SUM(LEFT(RIGHT(SUBSTITUTE("d"&B2:F2,"d",".0000000"),{8;15}) ...

学习FUGB的求和及替换技巧，分母用80保留了.5的信息，很精彩。
还可简化一点，151：
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXT(SUM(LEFT(RIGHT(SUBSTITUTE("d"&B2:F2,"d",".0000000"),{8;15}),7)/{8;1}),"#!d00/80\h"),"0/80",),"5/80",".5"),"0h",)

150以内估计还有可能。

lishengping666

hjj0451 发表于 2012-11-4 13:54
学习FUGB的求和及替换技巧，分母用80保留了.5的信息，很精彩。
还可简化一点，151：
=SUBSTITUTE(SUBST ...

结合FUGB求和的方法与我上面使用的格式与替换方法 142 个字符通过测试：
=SUBSTITUTE(SUBSTITUTE(TEXT(SUM(LEFT(RIGHT(SUBSTITUTE("d"&B2:F2,"d",".0000000"),{8;15}),7)/{8;1}),"[>]#\d#/80\d;"),"5/80d",".5h"),"0/80d","h")

hjj0451老师给我送鲜花的理由是" /80"(在此谢谢老师送的鲜花)
不过个人觉得，我上面公式中使用的text( ,"[>]#\d#/80\d;")比较简洁，可以节省不少的字符（在FUGB老师的方法上少替换一次"0h"）。