EXCEL 几分几秒几几如何算和、平均值?例如:1分18秒222分17秒331分20秒11如何计算平均时间如何计算时间和
来源:学生作业帮助网 编辑:作业帮 时间:2024/06/28 10:15:59
![EXCEL 几分几秒几几如何算和、平均值?例如:1分18秒222分17秒331分20秒11如何计算平均时间如何计算时间和](/uploads/image/z/1621578-66-8.jpg?t=EXCEL+%E5%87%A0%E5%88%86%E5%87%A0%E7%A7%92%E5%87%A0%E5%87%A0%E5%A6%82%E4%BD%95%E7%AE%97%E5%92%8C%E3%80%81%E5%B9%B3%E5%9D%87%E5%80%BC%3F%E4%BE%8B%E5%A6%82%EF%BC%9A1%E5%88%8618%E7%A7%92222%E5%88%8617%E7%A7%92331%E5%88%8620%E7%A7%9211%E5%A6%82%E4%BD%95%E8%AE%A1%E7%AE%97%E5%B9%B3%E5%9D%87%E6%97%B6%E9%97%B4%E5%A6%82%E4%BD%95%E8%AE%A1%E7%AE%97%E6%97%B6%E9%97%B4%E5%92%8C)
EXCEL 几分几秒几几如何算和、平均值?例如:1分18秒222分17秒331分20秒11如何计算平均时间如何计算时间和
EXCEL 几分几秒几几如何算和、平均值?
例如:
1分18秒22
2分17秒33
1分20秒11
如何计算平均时间
如何计算时间和
EXCEL 几分几秒几几如何算和、平均值?例如:1分18秒222分17秒331分20秒11如何计算平均时间如何计算时间和
平均:
=ROUND(AVERAGE(IF(ISERR(TEXT(TIME(0,LEFT(A1:A100,FIND("分",A1:A100)-1),MID(A1:A100,FIND("分",A1:A100)+1,FIND("秒",A1:A100)-FIND("分",A1:A100)-1)),"[ss]")+(RIGHT(A1:A100,LEN(A1:A100)-FIND("秒",A1:A100)))/100),"",TEXT(TIME(0,LEFT(A1:A100,FIND("分",A1:A100)-1),MID(A1:A100,FIND("分",A1:A100)+1,FIND("秒",A1:A100)-FIND("分",A1:A100)-1)),"[ss]")+(RIGHT(A1:A100,LEN(A1:A100)-FIND("秒",A1:A100)))/100)),2)&"秒"
求和:
=ROUND(SUM(IF(ISERR(TEXT(TIME(0,LEFT(A1:A100,FIND("分",A1:A100)-1),MID(A1:A100,FIND("分",A1:A100)+1,FIND("秒",A1:A100)-FIND("分",A1:A100)-1)),"[ss]")+(RIGHT(A1:A100,LEN(A1:A100)-FIND("秒",A1:A100)))/100),"",TEXT(TIME(0,LEFT(A1:A100,FIND("分",A1:A100)-1),MID(A1:A100,FIND("分",A1:A100)+1,FIND("秒",A1:A100)-FIND("分",A1:A100)-1)),"[ss]")+(RIGHT(A1:A100,LEN(A1:A100)-FIND("秒",A1:A100)))/100)),2)&"秒"
以上两个公式随便在空的单元格中输入,前提都是你的数据是在A1:A100,而且,输入这个公式后要同时按CTRL+SHIFT+ENTER才有效.
刚刚求和搞错了