[开_136]工时合计

hjj0451

lishengping666 发表于 2012-11-4 15:19
结合FUGB求和的方法与我上面使用的格式与替换方法 142 个字符通过测试：
=SUBSTITUTE(SUBSTITUTE(TEXT(S ...

公式错误，如下面的原数据：

原始数据用随机数测试，=TEXT(RANDBETWEEN(0,999),"0\d;;")&RANDBETWEEN(1,15)/2&"h"测试。

lishengping666

hjj0451 发表于 2012-11-4 16:36
公式错误，如下面的原数据：

原始数据用随机数测试，=TEXT(RANDBETWEEN(0,999),"0\d;;")&RANDBETWEEN( ...

老师说得对！
如果仅就题目的几组数据来看，142个字符是可以通过测试的（我已测试过）。
您上面的数据有错主要是0.5h ，用我的公式表示成了.5h(把0省略了)，个人觉得用.5h与0.5h表达的意思是一致的（也符合题目要求），因为实务中也常用.#表示0.#（比如计算器中）

hjj0451

由于楼主有事，受其委托，由我进行总结。
各楼答案、正误及评分建议如下：




答案及评分建议.rar (171.06 KB, 下载次数: 125)

2012-11-5 10:11 上传

点击文件名下载附件

如有异议，请短信联系或跟贴告知。

hjj0451

136期竞赛题－工时合计答案解析

　　本题技巧良多，是个门槛低、发掘深度大的好的竞赛题，要想写精需要一些技巧。本题涵盖的知识面不算广，但就求和及带分数格式这两个题点来说确实是练习文本函数的好题目。
　　从公式结构形式上看，有两类方法：
　　一：TEXT处理再替换的一步到位法
　　二：分别求出*d及*h的合并法
　　后者长度上没有优势，但比较容易读懂，这就是本题的门槛。下面以第一类方法为主线结合各楼层的解答进行题目解析。

一：text先处理再替换的一步到位法
　　主要用到text的带分数形式，各楼层的公式的差别主要在求和的方法及在带分数格式设置的基础上替换的不同。


第一步：求和
　　对***d*.*h以d为单位进行求和，1d=8h。这是用0或空格占位取数字的技巧，近似一种拼凑技术，结合原始数据位数来凑位,补几个0补几个空格。也许第一次由于占位位数不够导致取数出现越位或取不足的现象，但抓住最短位数及最长位数多尝试几次就能找到办法。
　　注意事项：原始数据***d*.*h最大长度为8，注意不要丢掉了最高位，同时也不要丢掉了小数部分.5。
　　
　　由于存在小数，不能直接转换为带分数来求和。比如，如果把.5先替换掉，采用公式：

1. =SUM(--SUBSTITUTE(TEXT(SUBSTITUTE(SUBSTITUTE(0&B2:F2,".5",),"h",),"0 0")&"/8","d"," 0"))

复制代码

　　这个是很常用的带分数求和的形式，这种方法也能做出，但长度上处于劣势，如我在2楼的第2个公式。
　　要想公式长度达到要求，本题的核心思路是需要把d和h前的数字分别取出来进行求和。

　　法一：　

1. =SUM(MID(SUBSTITUTE(TEXT(SUBSTITUTE(0&B2:F2,"h",),"0   0.0"),"d","  0"),{1;5},{4;9})/{1;8})

复制代码

　　如2楼，先把h去掉再设置格式为"0   0.0"，避免先取到h。d前的数字最长为3位，最短1位，设定"0   0.0"中间3个空格，"  0"2个空格，从第1个字符取4个数得d,从第5个字符开始取9个字符得h。
　　
　　法二：

1. =SUM(MID(SUBSTITUTE("d"&B2:F2,"d",".000000",2-ISERR(FIND("d",B2:F2))),{2;6},LEN(B2:F2&0))/{1;8})

复制代码

　　如3、4楼。用"d"&B2:F2避免先取到h，如果原始数据含d则替换第2个d反之替换第1个d。原始数据***d*.*h最大长度为8,考虑到空单元格，取LEN(B2:F2&0)个字符，现要多取1个字符取9个字符，d前最多3个字符，因此最少要6个0。
　　
　　法三：

1. =SUM(COUNTIF(B2:F2,{"","","*d"}&ROW($1:$999)/{1,2,2}&{"d*","h","h"})*ROW($1:$999)/{1,16,16})
   
2. =SUM(COUNTIF(B2:F2,{"","*d",""}&ROW(A:A)/2&{"d*","h","h"})/{1,8,8}/2*ROW(A:A))
   
3. =SUM((1-ISERR(FIND("d"&ROW(A:A)&"d","d"&B2:F2))+ISNUMBER(FIND("d"&ROW(A:A)/2&"h","d"&B2:F2))/16)*ROW(A:A)))

复制代码

　　如4楼、7楼，主要是利用d及h两边的字符分隔为依据进行COUNTIF模糊匹配及FIND查询求和。
　　
　　法四：　

1. =SUM(SUBSTITUTE(LEFT(0&B2:F2,FIND({"d";"h"},B2:F2&"dh")),LEFT(B2:F2,FIND("d",B2:F2&"d")),)/{1;8})

复制代码

　　4楼，先取得xdyh中的x、xdy，再把xd替换掉从而得到x、y。
　　
　　法五：　

1. =SUM(12.5*TEXT(MID(SUBSTITUTE(SUBSTITUTE(B2:F2,"d","0/125  "),"h","%"),{1;9},8),"\0 @"))

复制代码

　　4楼，把d替换为0/125，相当于d前的数字/12.5，h替换为%相当于h前的数字/100，再统一乘以12.5相当于d前的数字还原、h前的数字/8。2个空格是因为d前的数字为1-3位，要统一取8位。
　　text格式含义为%的百分数数值原样显示，/125的文本分数显示为"0 分数"的带分数形式。形如8%会被text识别为数字0.08、形如8/125会被text识别为文本，需要变成带分数才能转化为数字。
　　=SUM(12.4*TEXT(MID(SUBSTITUTE(SUBSTITUTE(B2:F2,"d","0/124  "),"h","%"),{1;9},8),"\0 @"))这是附带对-1%的简化，求和原理同上。
　　
　　法六：　

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

复制代码

　　3楼，取负的和。{" ";"0d"}&的技巧形同平时对不规则时间的求和{0,"0时"}&......。{" ";"0d"}&后面的substitute部分得到的是可以被TEXT转换的数字和不能转换的文本，用TEXT的参数"G/通用格式;;0;\0"把文本变为0再求和。
　　
　　法七：

1. =SUM(MID(RIGHT(10^11&SUBSTITUTE(B2:F2,"d",".000"),11),{1;8},{7;3})/{1;8}))
   
2. =SUM(LEFT(RIGHT(SUBSTITUTE("d"&B2:F2,"d",".0000000"),{8;15}),7)/{8;1})

复制代码

　　见6楼和15楼。这是本题较精巧的求和方法。d前的数字最长3位，全长最长８位。把d替换为".000"的形式右取一定的长度，再左取一定的位数。
　　
　　法八：

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

复制代码

　　12楼，这种方法比前面复杂。统一把d与h间补足最少3位数字，100&移位后统一取3位得到d、h之前的数字，可以分步解析下。
　　
　　法九：　

1. =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"))

复制代码

　　8楼，这是较直观的分两部分求*h/8+*d。

第二步：转换为带分数
　　在第一步求出的和的基础上，利用text把其转换为带分数，大致分为两类方法。
　　法一：以8为分母的带分数
　　-1%防止进位(见下表示例)，一些公式在求和的内部做了相当于-1%的简化处理。转换为带分数后，还要判断是否存在.5。　

*h	*h/8	(*h/8-1%)*8	text舍	&".5"
0.5	0.0625	0.4200	0	0.5
1.5	0.1875	1.4200	1	1.5
2.5	0.3125	2.4200	2	2.5
3.5	0.4375	3.4200	3	3.5

　　这里text格式参数形式又有2种处理方法：
　　一种是减1%后直接转换为"#\d0/8\h;"的带分数形式，再把"/8"替换为".5"或者""，通过countif判断原始数据是否存在奇数个.5，来判断是把/8替换为.5还是空，如2楼、3楼的第一个公式。
　　这种格式需要注意的是当d前的数字求和的结果为0时，通过"#\d0/8"的格式设置，d是不会显示的。
　　另外一种是直接在TEXT第2参数里进行判定设置，其形式为TEXT第2参数形式为"#\d"&IF(-1^COUNTIF(B2:F2,"*.*")<0,0.5,"#")&"/8\d;"，如原数据含奇数个.5，text格式参数为"#\d0.5/8\d;"，否则为"#\d#/8\d;"，再把/8d替换为h。
　　如3楼、4楼、7楼、
　　text(3.75,"0 0.5/8")是可以的，".5"部分相当于强制显示了。而text(3.75,"0 0.0/8")是TEXT带分数格式所不允许的。
　　需要注意的是不能写成"#\d"&IF(-1^COUNTIF(B2:F2,"*.*")<0,0.5,"#")&"/8\h;"这样的形式，会把形如24d的误转化为24h。
　　法二：以80为分母的带分数
　　text格式参数设置为"#\d00/80\h"，和法1的text结果比较，就是分母和分子都扩大了10倍。如55/80、50/80之于5.5/8、5/8。此时带分数分子里已经包含了是否存在.5的信息，只需直接进行后面替换，把0/80替换掉，把5/80替换为.5。省去了IF(-1^COUNTIF(B2:F2,"*.*")<0.....部分的判断。
　　如8楼、12楼、13楼、15楼。

3d1.5h为例:
3 1/8	=TEXT(3.1875-1%,"0 0/8")	=1.5/8=0.1875
3 15/80	=TEXT(3.1875,"0 00/80")

第三步：替换
　　这部分比较简单，前面的思路就决定了这里的出路，不再分析此处替换的差别。



二：两部分分开处理再文本合并&法
　　这类方法一般字符较长，比较直观，其核心框架为int(sum(d)+sum(h)/8)&mod(sum(h),8)。
　　有三大类的方法：
　　法一：
　　复数法：
　　把d替换为+,h替换为i,取复数和实部和虚部，求和部分为：

1. ＝IMREAL(IMPRODUCT(IMSUM(SUBSTITUTE(TEXT(SUBSTITUTE(0&B2:F2,"h",),"0+0")&"i","d","+0")),"8-i"))/8

复制代码

　　求余为：　

1. ＝MOD(IMAGINARY(IMSUM(SUBSTITUTE(TEXT(SUBSTITUTE(0&B2:F2,"h",),"0+0.0")&"i","d","+0"))),8)

复制代码

　　法二：
　　替换求和法：
　　如5楼、6楼、7楼、9楼、11楼、14楼、16楼、17楼、18楼。求和部分同前面，只是加个INT取整。以6楼以xgg2001为代表，2部分都很精简，的确是采用&法能做到的极致，其mod求余部分的"和"：

1. =SUM(--SUBSTITUTE(SUBSTITUTE(0&B2:F2,"d",128),"h",))

复制代码

　　这是间接求余的极妙思路。128、1280、12800、…都能被8整除，能被8整除的最小的10^n是1000，把adbh替换为a128b(a*10000+128*10+b)，a最少升了3位，扩大了1000、10000、...倍，都可被8整除。结果还是mod(b,8)。此处最少要替换为一个三位数以保证d前的数字至少扩大足够的倍数(1000)，最小的能被8整除的三位数是128，当然替换为200、1200等也没问题，但要保证替换后的求和部分不超过1073741816而让mod(,8)出错。
　　汇总一下，求h部分的和的方法有：
　　类型1----5楼、14楼、18楼：
　　＝SUM(--(0&TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE("0d"&B2:F2,"d","     "),"h",),5))))
　　＝SUM(--RIGHT(SUBSTITUTE(SUBSTITUTE("d"&B2:F2,"h",),"d","000"),3))
　　＝SUM(--RIGHT(SUBSTITUTE(SUBSTITUTE(0&B2:F2,"h",""),"d",0)*10,2))
　　类型2----6楼：
　　＝SUM(--SUBSTITUTE(SUBSTITUTE(0&B2:F2,"d",128),"h",))
　　类型3----7楼、11楼、16楼、17楼：
　　＝SUM(COUNTIF(B2:F2,{"*d",""}&ROW($1:$15)/2&"h")/2*ROW($1:$15))
　　=SUM(ISNUMBER(FIND("d"&ROW($1:$15)/2&"h","d"&B2:F2))*ROW($1:$15)/2)
　　＝SUM(ISNUMBER(SEARCH("*d"&ROW($1:$16)/2&"h","d"&B2:F2))*ROW($1:$16)/2)

　　法三：
　　时间法：
　　先把h的和乘以7.5转换为60进位，取得{"[m]","s.0"}后再把s.0除以7.5还原，最后变成"[m]\ds.0\h;;"的形式：　

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;;"会看到*d*h。
　　由于03超过嵌套了，我在2楼没有继续处理。只能在2007及更高版本采用此法。



几个结果错误公式：
5楼：
参考6楼、15楼。
9楼：
改为=TEXT(INT(SUM(MID(SUBSTITUTE(TEXT(SUBSTITUTE(0&B2:F2,"h",),"0     0"),"d","    0"),{1;7},6)/{1;8})),"0!d;;")&TEXT(MOD(SUM(MID(SUBSTITUTE(TEXT(SUBSTITUTE(0&B2:F2,"h",),"0     0.0"),"d","    0"),{1;7},6)/{1;8}),1)*8,"G/通用格式!h;;")
13楼：
问题出在TEXT格式参数设置上，改为=SUBSTITUTE(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),"#\d00/80\h"),"0/80",),"5/80",".5"),"0h",)，我2003，没有验证。
16楼，
改为=TEXT(SUM(ISNUMBER(FIND("d"&ROW($1:$999)&"d","d"&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",)
18楼，
改为=TEXT(INT(SUM(MID(SUBSTITUTE(SUBSTITUTE(IF(FIND("d",B2:F2&"d")>LEN(B2:F2),"d"&B2:F2,B2:F2),"h",),"d","  00"),{1;4},{3;9})/{1;8})),"0!d;;")&SUBSTITUTE(MOD(SUM(--RIGHT(SUBSTITUTE(SUBSTITUTE("d"&B2:F2,"h",),"d","000"),3)),8)&"h","0h",)

hjj0451

1. 为什么上面"#\d0/8\h;"不能写成"0\d0/8\h;"？
   
2. 为什么上面"#\d"&IF(-1^COUNTIF(B2:F2,"*.*")<0,0.5,"#")&"/8\d;"不能写成"#\d"&IF(-1^COUNTIF(B2:F2,"*.*")<0,0.5,"#")&"/8\h;"？

复制代码

针对上面两个问题，有E友问下面的问题：
1：TEXT()带分数格式，比如TEXT(0.8125,"[>]#!d#/80!d;")=65/80d ，请问该如何理解？
2：TEXT(0.8125,"[>]#!d#/80!d;") #是占位符，是否代表前面0.8125的一个数字呢，我如果把/80拿去为什么=d1d 呢？

答：
1：这其实是带分数和真分数的格式差别。
#!d#/80!d 比如text(3.75,"# 0/4")=3 3/4，是3.75的带分数形式；如果0.75，则为真分数text(0.75,"#/4")=3/4。
TEXT(0.8125,"[>]#!d#/80!d;")=TEXT(0.8125,"#/80!d")=65/80d，真分数部分65/80加上d，这个d是后面的那个d而不是第一个d，65/80是真分数，只强制显示最后一个d。只有带分数形如1 65/80、0 65/80的格式才会同时显示前后两个d。

至于"#\d0/8\h;"为何不能写成"0\d0/8\h;"？因为写成"0\d0/8\h;"的格式后，第1个0永远占位显示，其结果永远是带分数，d和h将永远同时显示。而不能在必要时显示为真分数，以让d不显示而只显示h。

2：TEXT(0.8125,"##;")=1，TEXT(0.8125,"!d##!d;")=d1d，这里没有什么分数格式问题，只是很普通的TEXT用法，四舍五入取整，两个d都会同时显示。

我在这里特意把这个问题提出来供新人解读。

带分数与真分数的分数格式差别.rar (3.77 KB, 下载次数: 36)

2012-11-6 08:27 上传

点击文件名下载附件

fugb-2010

15楼公式中求和部分模拟结果

carlyucy

高人太多。。。看不懂了都。。。

hust01

=IF(TRUNC(MMULT(IF(SEARCH("d",B2:F9&"0000000000d")<10,(--LEFT(B2:F9,SEARCH("d",B2:F9)-1)*8),0)+IF(RIGHT(B2:F9,1)="h",IF(SEARCH("d",B2:F9&"d")>SEARCH("h",B2:F9&"d"),--LEFT(B2:F9,SEARCH("h",B2:F9)-1),--MID(B2:F9,SEARCH("d",B2:F9)+1,SEARCH("h",B2:F9)-SEARCH("d",B2:F9)-1)),),{1;1;1;1;1})/8)>0,TRUNC(MMULT(IF(SEARCH("d",B2:F9&"0000000000d")<10,(--LEFT(B2:F9,SEARCH("d",B2:F9)-1)*8),0)+IF(RIGHT(B2:F9,1)="h",IF(SEARCH("d",B2:F9&"d")>SEARCH("h",B2:F9&"d"),--LEFT(B2:F9,SEARCH("h",B2:F9)-1),--MID(B2:F9,SEARCH("d",B2:F9)+1,SEARCH("h",B2:F9)-SEARCH("d",B2:F9)-1)),),{1;1;1;1;1})/8)&"d","")&IF(MOD(MMULT(IF(SEARCH("d",B2:F9&"0000000000d")<10,(--LEFT(B2:F9,SEARCH("d",B2:F9)-1)*8),0)+IF(RIGHT(B2:F9,1)="h",IF(SEARCH("d",B2:F9&"d")>SEARCH("h",B2:F9&"d"),--LEFT(B2:F9,SEARCH("h",B2:F9)-1),--MID(B2:F9,SEARCH("d",B2:F9)+1,SEARCH("h",B2:F9)-SEARCH("d",B2:F9)-1)),),{1;1;1;1;1}),8)>0,MOD(MMULT(IF(SEARCH("d",B2:F9&"0000000000d")<10,(--LEFT(B2:F9,SEARCH("d",B2:F9)-1)*8),0)+IF(RIGHT(B2:F9,1)="h",IF(SEARCH("d",B2:F9&"d")>SEARCH("h",B2:F9&"d"),--LEFT(B2:F9,SEARCH("h",B2:F9)-1),--MID(B2:F9,SEARCH("d",B2:F9)+1,SEARCH("h",B2:F9)-SEARCH("d",B2:F9)-1)),),{1;1;1;1;1}),8)&"h","")
不知道够不够长

laowuwyx

忽然间发现,我已严重落伍,赶不上咯.