1、首先打开excel表格,在其中输入需要求平均值的时间数据。
2、点击“开始”选项卡中的“查找和选择”,点击其中的“替换”。
3、然后在替换对话框中的“查找内容”中输入“分钟”,在“替换为”中输入“:”符号,点击全部替换。
4、再继续将“查找内容”换成“秒”,在“替换为”中空置不输入,点击全部替换。
5、然后在空白单元格中输入平均数的求值公式:=AVERAGE(A1:A10)。
6、点击回车即可得到平均时间的数据了。
准备工具/材料:装有windows 10的电脑一台,Microsoft Office 家庭和学生版 2016 excel软件。
在EXCEL中,求时间的平均值的办法如下:
1、首先,在excel表中输入员工上班的时间,计算第2到第4天的时间平均值。
2、然后选择F2单元格,并单击上面的“fx”按钮插入该功能。
3、单击并选择对话框中的“平均值”平均值函数来插入函数。
4、单击打开功能参数中的数字1框,选择B2:D2单元格区域。
5、单击“确定”按钮,在F2单元格中生成从2到4的上班时间平均值。
本回答被网友采纳excel中,时间本身应该是一个日期用整数、时间用小数这样来记录的一个数值型的值,也就是说,你应该可以直接通过AVERAGEA()函数来计算平均值的,平均值求完了,右键单元格,菜单选设置单元格格式,修改为时间格式即可。
如果你这个本身数据源不是excel标准的日期时间的显示格式,而是字符串,我这做了一个字符串处理的工具,根据你这个截图做的,B列下拉公式就可以了,结果在D2格子,对B列求平均,抹除秒的小数,如果不想抹掉小数,那你自己编辑下C2格子,改成AVERAGEA(B:B)就可以了。
B1贴公式:IF(ISNUMBER(FIND("分",A1,1)),VALUE(LEFT(A1,FIND("分",A1,1)-1))*60+VALUE(SUBSTITUTE(MID(A1,IFERROR(FIND("分钟",A1,1)+2,FIND("分",A1,1)+1),3),"秒","")),123)
C2贴公式:=INT(AVERAGEA(B:B))
D2贴公式:=IF(C2>=60,INT(C2/60)&"分钟"&MOD(C2,60)&"秒",C2&"秒")
大神,这个是怎么转换成秒的呀
怎么从2分15秒变成135的格式呢
追答这个你看下我B1填写的公式,我估计你这个是个字符串形式的值,所以做的字符串切分,也就是用find识别了一下“分钟”“秒”这两个关键字,然后抓取到相应位置的值,把分钟乘以60。。。。。
我看你截图里没有小时,所以没做小时的识别。
IF(ISNUMBER(FIND("分",A1,1)),VALUE(LEFT(A1,FIND("分",A1,1)-1))*60+VALUE(SUBSTITUTE(MID(A1,IFERROR(FIND("分钟",A1,1)+2,FIND("分",A1,1)+1),3),"秒","")),VALUE(SUBSTITUTE(A1,"秒","")))
B1格子的公式,上面那个好像有句没用的忘记删了。。。。
分解的话,首先if(ISNUMBER(FIND("分",A1,1)),带分钟的处理,只有秒的处理),判断了一下有没“分”
然后,分钟数值提取:VALUE(LEFT(A1,FIND("分",A1,1)-1))*60
+
有分钟的情况下的秒值的提取:VALUE(SUBSTITUTE(MID(A1,IFERROR(FIND("分钟",A1,1)+2,FIND("分",A1,1)+1),3),"秒",""))
最后一段,只有秒的时候,直接删掉秒,把格式转换为数值就行了:VALUE(SUBSTITUTE(A1,"秒",""))
大体就是这样,用find定位关键字,left或者mid把数值部分切分处理,SUBSTITUTE删掉秒这个中文字,value转换格式为数值,然后60*分+秒,就是这样。
你的这张图上面看不到公式呢
😊
追答公式我不是贴在文字里了嘛。。。图片上是公式运行的效果。。。。。
如果你非要看下带公式的图片。。。。。
不好意思,我还是入门级的,,,对这些不太懂,AVERAGEA(B:B)这个吗,你说的用find识别的公式是哪一个呢
追答AVERAGEA(B:B)是写到C1格子的,用途是把B列我计算好的秒数,求平均值。
find是写到上面B1格子里面那一串公式中用到的,find本身功能是查找一个关键字在一个字符串里第一次出现的位置,中英文都算一个字符,格式:find(关键字,被查找的字符串,查找起始的查找位置)
上面那些都是公式套公式的,最开始的判断,if(ISNUMBER(FIND("分",A1,1)),带分钟的处理,只有秒的处理)
,这是三个公式,FIND查“分”在A1中的位置,ISNUMBER判断find返回的结果,如果不是数值就说明没查到,if是判断公式,格式是if(判断条件,条件为TRUE时执行,条件为FALSE时执行)。
字数写不下,这部分看图。
整个公式就是这个意思。
说实话,这个的话,如果是入门级不建议去看这些公式,先直接按大家给的写好的公式使用就好,如果想学的话,可以通过excel的帮助,查看你需要用到的公式的说明和范例,然后自己尝试从单一公式开始使用。另外就是可能需要一点编程的基本概念和能力。。。。。
先睡了。。。明天要上班的说。。。。
A13=INT((SUM(IF(ISERROR(LEFT(A1:A12,FIND("分钟",A1:A12)-1)),0,--LEFT(A1:A12,FIND("分钟",A1:A12)-1)))*60+SUM(--(0&SUBSTITUTE(MID(A1:A12,IF(ISERROR(FIND("分钟",A1:A12)),1,FIND("分钟",A1:A12)+2),2),"秒",""))))/3600)&"小时"&INT(MOD((SUM(IF(ISERROR(LEFT(A1:A12,FIND("分钟",A1:A12)-1)),0,--LEFT(A1:A12,FIND("分钟",A1:A12)-1)))*60+SUM(--(0&SUBSTITUTE(MID(A1:A12,IF(ISERROR(FIND("分钟",A1:A12)),1,FIND("分钟",A1:A12)+2),2),"秒","")))),3600)/60)&"分钟"&MOD(SUM(IF(ISERROR(LEFT(A1:A12,FIND("分钟",A1:A12)-1)),0,--LEFT(A1:A12,FIND("分钟",A1:A12)-1)))*60+SUM(--(0&SUBSTITUTE(MID(A1:A12,IF(ISERROR(FIND("分钟",A1:A12)),1,FIND("分钟",A1:A12)+2),2),"秒",""))),60)&"秒"
【数组公式,公式输完后,光标放在公式编辑栏同时按下CTRL+SHIFT+回车键,使数组公式生效】
追问这个公式适用我这种格式的求值吗
追答不好意思没看清楚问题,我这是求和公式。求平均值是:
=INT((SUM(IF(ISERROR(LEFT(A1:A12,FIND("分钟",A1:A12)-1)),0,--LEFT(A1:A12,FIND("分钟",A1:A12)-1)))*60++SUM(--(0&SUBSTITUTE(MID(A1:A12,IF(ISERROR(FIND("分钟",A1:A12)),1,FIND("分钟",A1:A12)+2),2),"秒",""))))/COUNTA(A1:A12)/60)&"分钟"&MOD((SUM(IF(ISERROR(LEFT(A1:A12,FIND("分钟",A1:A12)-1)),0,--LEFT(A1:A12,FIND("分钟",A1:A12)-1)))*60++SUM(--(0&SUBSTITUTE(MID(A1:A12,IF(ISERROR(FIND("分钟",A1:A12)),1,FIND("分钟",A1:A12)+2),2),"秒",""))))/COUNTA(A1:A12),60)&"秒"
或者=INT((SUM(IF(ISERROR(LEFT(A1:A12,FIND("分钟",A1:A12)-1)),0,--LEFT(A1:A12,FIND("分钟",A1:A12)-1)))*60++SUM(--(0&SUBSTITUTE(MID(A1:A12,IF(ISERROR(FIND("分钟",A1:A12)),1,FIND("分钟",A1:A12)+2),2),"秒",""))))/COUNTA(A1:A12)/60)&"分钟"&ROUNDUP(MOD((SUM(IF(ISERROR(LEFT(A1:A12,FIND("分钟",A1:A12)-1)),0,--LEFT(A1:A12,FIND("分钟",A1:A12)-1)))*60++SUM(--(0&SUBSTITUTE(MID(A1:A12,IF(ISERROR(FIND("分钟",A1:A12)),1,FIND("分钟",A1:A12)+2),2),"秒",""))))/COUNTA(A1:A12),60),0)&"秒"
全是数组公式,第一个平均时间的秒有小数,第二个四舍五入无小数。
天啊,这个公式好长
我还是入门级的,对这个复杂的公式看不懂,这可以直接套用吗,怎么验证得出的数据是否正确呢
追答B1=IF(ISERROR(FIND("分钟",A1)),0,1)*LOOKUP(99^9,--LEFT(A1,{1,2,3}))
C1=IF(ISERROR(LOOKUP(99^9,--RIGHT(SUBSTITUTE(A1,"秒",""),{1,2,3}))),0,LOOKUP(99^9,--RIGHT(SUBSTITUTE(A1,"秒",""),{1,2,3})))
两公式向下复制
B13=SUM(B1:B12)向右复制
B14=INT(B13/12)
C14=(MOD(B13/12,1)*60+C13/12)
C15=ROUNDUP((MOD(B13/12,1)*60+C13/12),0)
这样就是用另一种公式验证了结果。其实这是增加辅助列计算平均时间。还不放心就手工计算吧。
是啊,但是现在格式不统一,做起来很麻烦,而且我要做的文档数据量很大
追答如果确保计算区域中无空白单元格,下面的公式应该可以达到目的,只是太长了。
=TEXT((SUMPRODUCT((LEN(A1:A29)>3)*(--SUBSTITUTE(SUBSTITUTE(A1:A29,"分钟",":"),"秒","")))*24/1440+SUMPRODUCT((LEN(A1:A29)<4)*(--SUBSTITUTE(SUBSTITUTE("0分钟"&A1:A29,"分钟",":"),"秒","")))/60)/COUNTA(A1:A29),"m分钟s秒")
不行,显示不出来