[60期]各种格式的时间直接求总和

wshcw · 发表于 2010-5-4 00:07

将下列各种格式的时间求总和
8分
1时3秒
12秒
0时00分22秒
12分05秒
17秒
2分3秒
竞赛目标:
测试一下大家对函数的灵活运用和变通能力,巩固函数的基本应用技法.
答题必须同时具备以下要求:
1、公式通用,完全正确,并且公式长度<80字,得财富10分；公式长度<75字,得技术1分；精彩答案得技术2分。
2、公式不能使用辅助列，也不能整列引用(只能引用A1:A8),不得定义名称,公式结果统一为数值型(单元格格式为时间格式)。
3、为了公平合理，公式最好改为03版适用公式，公式采用07版函数则不评技术分，只适当给财富分鼓励。
其它说明:
1、为减轻审贴的工作量，回复时，每人只能占用一层楼，只在固定的楼层编辑修改，勿反复回复。
2、回复终止日期至5月22日（之后回复者不评分）
3、答题的E友，请把附件也一并发上来，以便更快地检查公式的正误和通用性。
补充:
1、不规范时间不包括分跟秒超过60,时间超过24小时的这种情况。
2、文本时间没有小数点的情况（如：不会有2.6秒,6.9分2秒等情况出现)
3、注意了:在求和区域中输入文本型的"9时7分"，"1时03分5秒"和文本、数值型的"2:15:02"都要测试一下,以免出现公式不通用而出错的情况.
附:参考贴子
[结贴在6楼]文本时间直接求和,公式长度不超过100字

[ 本帖最后由 wshcw 于 2010-6-1 16:46 编辑 ]

杏花雨V翟 · 发表于 2010-5-4 14:24

不规范时间包括分跟秒超过60的这种情况吗
比如999秒
999分99秒
或者超过24小时之类的

===========================================
不包括.最后记得把附件发上来.

===wshcw===
===========================================

对不起版主了，目前只能作出来120以上的，再想想
=SUM(RIGHTB(LEFT(TEXT(A1:A8,"h时m分s秒")&0,FIND({"时","分","秒"},TEXT(A1:A8,"h时m分s秒")&"0时分秒")-1),2)/{24,1440,86400})

[ 本帖最后由杏花雨V翟于 2010-5-12 14:19 编辑 ]

wddn · 发表于 2010-5-4 23:07

先占位吧：
C1=SUM((MIDB(10&A1:A8,FINDB({"时","分","秒"},A1:A8&"钟时分秒"),2)&0)/4/60^{1,2,3},A1:A8)

朋友给的公式，我觉得是正确的请一并审核：
=SUM(--TEXT(SUBSTITUTE(TEXT({0,"0时"}&A1:A8,"@"),"时","时0分"),"H:M:S;;0;\0"))

[ 本帖最后由 wddn 于 2010-5-22 23:12 编辑 ]

donghan · 发表于 2010-5-7 11:32

版主的题目真难呀，怎么也简化不到要求的字数
数组公式：
=SUM(RIGHTB(LEFT(A1:A8&"00",FIND({"时","分","秒"},A1:A8&"00时分秒")-1),2)/24/60^{0,1,2},A1:A8)

[ 本帖最后由 donghan 于 2010-5-7 11:34 编辑 ]

liuguansky · 发表于 2010-5-9 22:29

答题的时候最开心了。
=SUM(IFERROR(--({"0时","0时0分",""}&SUBSTITUTE(A1:A8,"时","时0分")),))
64字符，不过用了IFERROR
哈哈，没规定要用03版本啊，哈哈

============================================
A6输入文本型的"9时7分"试试,似乎只有98字的那个正确.

===wshcw===
============================================
不好意思，暂更新为=SUM(IFERROR(--({"0时","0时0分",""}&SUBSTITUTE(IFERROR(--A1:A8,A1:A8),"时","时0分")),))
81个字符。再想。《嘿嘿，错了，还有大头兄陪着。》
大理版主改作业真是用心啊。什么时候大理版主去讲个函数课啊，一定去听下。
=SUM(-IFERROR(-(0&{"时","时0分",0}&SUBSTITUTE(IFERROR(--A1:A8,A1:A8),"时","时0分")),))少一个字符。
===========================================================
为了公平合理，公式最好改为03版适用公式，公式采用07版
函数则不评技术分，只适当给财富分鼓励。

===wshcw===
===========================================================
=SUM(--TEXT(0&{"时","时0分",0}&SUBSTITUTE(TEXT(A1:A8,"0.0000%;;0;@"),"时","时0分"),"0.0000%;;0;!0"))完全没有办法。94字符，感觉减不了了。

[ 本帖最后由 liuguansky 于 2010-5-14 03:19 编辑 ]

wangg913 · 发表于 2010-5-10 09:23

=SUM(--TEXT(SUBSTITUTE({"0时","0时",0}&A1:A8,"时","时0分",{3,1,1}),"h:m:s;;0;!0"))
长度77，超了。
=====================================================================
既然不对，就改改。
=SUM(--TEXT(SUBSTITUTE(TEXT({"0时",0}&A1:A8,"@"),"时","时0分"),"h:m:s;;0;!0"))
不知这回对不对？
=SUM(--TEXT(SUBSTITUTE({"0时","0时",0}&TEXT(A1:A8,"@"),{"","时","时"},"时0分"),"h:m:s;;0;!0"))
=SUM(--TEXT(SUBSTITUTE(TEXT(SUBSTITUTE({"0时",0}&TEXT(A1:A8,"@"),"时","时0分"),"@"),"0分",),"h:m:s;;0;!0"))
======================================================================
建议将所有可能的数据类型，一一列出,并将数据格式改为文本型。
比如：
1分1秒
1分
1时
1时4秒
9秒

6时7分
5时4分3秒
6:00:02
===================================================================

[ 本帖最后由 wangg913 于 2010-5-11 11:58 编辑 ]

wjzwang · 发表于 2010-5-10 14:34

=SUM(MMULT(--TEXT(MIDB(0&A1:A8,FINDB({"时","分","秒"},0&A1:A8&"00时分秒",1)-2,2),"0;;0;!0"),60^{2;1;0}))/86400+SUM(A1:A8)
115字符,超出范围了,参与一下

lgcmeli · 发表于 2010-5-11 16:28

先给个第一稿 138个字符包括=号，数组公式

=SUM(--SUBSTITUTE(IF((FIND("时",A1:A8&"时")-LEN(A1:A8)=1)*(LEN(A1:A8)<7)*(A1:A8<>""),"0时",)&A1:A8,IF(ISERR(FIND("分",A1:A8)),"时","做"),"时0分"))

再来个第2搞 128个字符包括=号，数组公式

=SUM(--SUBSTITUTE(IF((ISERR(FIND("时",A1:A8)))*(LEN(A1:A8)<7)*(A1:A8<>""),"0时",)&A1:A8,IF(ISERR(FIND("分",A1:A8)),"时","做"),"时0分"))

再减少几个，第3搞 124字符
=SUM(--SUBSTITUTE(IF((ISERR(FIND("时",A1:A8)))*(LEN(A1:A8)<7)*(A1:A8<>""),"0时",)&A1:A8,IF(ISERR(FIND("分",A1:A8)),"时"),"时0分"))

[ 本帖最后由 lgcmeli 于 2010-5-12 17:39 编辑 ]

xiongkehua2008 · 发表于 2010-5-12 16:25

(繁體版的系統)先發一個保存起來,再想想看怎麼簡化一下(75個字符,真是不知該從哪里下手了,或許出發點的思路就錯了...)

=SUM(RIGHTB(LEFT(A1:A8&"00時0分0秒",FIND({"時","分","秒"},A1:A8&"00時0分0秒")-1),2)*60^{2,1,0}/86400,A1:A8)

[公式字符長度:98]

黃版:我的公式是在我的繁體版電腦中寫的,你下載我的附件測試一下看看...小時數應是可以正常加上的!只是公式的長度太長(75個字符真想不到什麼辦法了)!

===================================================
公式错了,小时数没加上.要按一楼提示多验证.

===wshcw===
===================================================

[ 本帖最后由 xiongkehua2008 于 2010-5-14 15:01 编辑 ]

东方之珠0 · 发表于 2010-5-16 16:54

=SUM(--TEXT(SUBSTITUTE(TEXT({0,"0时"}&A1:A8,"@"),"时","时0分"),"H:M:S;;0;!0"))

		自动登录	找回密码
密码			免费注册

[Excel 函数与公式] [60期]各种格式的时间直接求总和

本帖子中包含更多资源

评分

本帖子中包含更多资源

评分

本帖子中包含更多资源

评分

本帖子中包含更多资源

评分

本帖子中包含更多资源

评分

本帖子中包含更多资源

评分

本帖子中包含更多资源

本帖子中包含更多资源

评分