请问Excel中这个列怎么求和

发布网友发布时间：2024-04-10 00:46

我来回答

共4个回答

热心网友时间：2024-04-17 13:49

利用2003版本excel操作如下：
1.选取数据→分列→分隔符号为字符“分”

2.该列被分为两列，第一列为分，第二列为秒。手工将1分钟以下的时间由E列移到F列

同样的，对F列用字符“秒”分列
3.分别对E.F列求和,再换算以下F列的结果就口以啦~~~

热心网友时间：2024-04-17 13:53

如果数据在A1：A10，如果不是请用实际数据区域替换公式中的 A1:A10

=SUM(TIME(HOUR(IF(LEN(SUBSTITUTE(A1:A10,"分",""))<LEN(A1:A10),"0:"&SUBSTITUTE(SUBSTITUTE(A1:A10,"秒",""),"分",":"),"0:0:"&SUBSTITUTE(A1:A10,"秒",""))),MINUTE(IF(LEN(SUBSTITUTE(A1:A10,"分",""))<LEN(A1:A10),"0:"&SUBSTITUTE(SUBSTITUTE(A1:A10,"秒",""),"分",":"),"0:0:"&SUBSTITUTE(A1:A10,"秒",""))),SECOND(IF(LEN(SUBSTITUTE(A1:A10,"分",""))<LEN(A1:A10),"0:"&SUBSTITUTE(SUBSTITUTE(A1:A10,"秒",""),"分",":"),"0:0:"&SUBSTITUTE(A1:A10,"秒","")))))

Ctrl+Shift+回车结束！

设置输入公式的单元格格式为时间即可以时间格式显示。

试了一下前面朋友的回答如果如下：

热心网友时间：2024-04-17 13:51

假设是在e16求和则e16单元格中输入：{=TEXT(SUM(--(IF(ISNUMBER(FIND("分",E1:E10)),"0时","0时0分")&E1:E10)),"h小时mm分ss秒")}

注意：大括号是ctrl+shift+回车最后输入的！

热心网友时间：2024-04-17 13:46

=TEXT(SUM(--SUBSTITUTE(SUBSTITUTE(IF(ISERR(FIND("分",E1:E20)),"0:0:","0:")&A1:A6,"分",":"),"秒",)),"[mm]分s秒")数组公式