Excel破译：绿色文本格式时间在平均时长计算中的困扰

1

职场实例

如下图所示：

A列为某项工具的4个测试时长的数据，我们想要在C2单元格内计算一下A2:A5区域时间时长数据的平均时长。

如果我们直接在C2单元格输入函数公式：

=SUM(A2:A5)/4

即用SUM函数求得A列数据的总时长后除以时间个数，欲获取平均时长。由于A列的时间记录是文本内容，因此Excel无法直接识别和计算，故返回不正确值。

2

解题思路

当计算“平均时长”时，遇到文本格式的时间，该如何破局呢？

下面我们来介绍一下具体的解决方案。

首先，我们将C2单元格的格式设置为“时间”格式。

接着输入函数公式：

={"0时","0时0分"}&A2:A5

使用字符串{"0时","0时0分"}与A2:A5单元格内容连接，变成4行2列的内存数组，我们选中公式后按下F9键可查看数组内各个元素：

{"0时1分10秒","0时0分1分10秒";"0时50秒","0时0分50秒";"0时2分1秒","0时0分2分1秒";"0时25秒","0时0分25秒"}

Excel将“0时0分0秒”格式的文本字符串识别为真正的时间；将“0时0秒”、“0时0分”、“0分0秒”等格式的字符串仍然识别为文本。

我们继续完善C2单元格的函数公式为：

=TEXT({"0时","0时0分"}&A2:A5,"h:m:s;;;!0")

TEXT函数的第2参数使用"h:m:s;;;!0"，将时间样式的字符串转换为“h:m:s”格式，非时间样式的文本字符串强制的显示为“0”，我们选中公式后按下F9键可查看数组内各个元素：

{"0:1:10","0";"0","0:0:50";"0:2:1","0";"0","0:0:25"}

因为TEXT函数计算出的结果仍然为文本格式，所以我们要运用经典的“减负运算”，加上两个负号，即负数的负数为正数，通过减负运算将文本结果转换为时间序列值。

我们继续完善C2单元格的函数公式为：

=--TEXT({"0时","0时0分"}&A2:A5,"h:m:s;;;!0")

我们选中公式后按下F9键可查看数组内各个元素：

{0.000810185185185185,0;0,0.000578703703703704;0.00140046296296296,0;0,0.000289351851851852}

最后使用SUM函数的求和结果除以所有时间的个数，即可得到平均用时：

=SUM(--TEXT({"0时","0时0分"}&A2:A5,"h:m:s;;;!0"))/4

公式以Ctrl+Shift+回车键三键结束公式。