Excel中如何用SUBSTITUTE函数将天、时、分、秒转换成小时？

作者：阿硕来源：部落窝教育发布时间：2021-12-08 09:37:19点击：21470

分享到:

收藏收藏人气：0人

版权说明：原创作品，禁止转载。

编按：

有一组关于时长的数据，我想把它们统一转换成以小时为单位，该如何做？

案例数据如下图，其中，A列中的数据就是关于时长的数据记录。从数据的格式方面来看，并不是所有数据都按照“XX天XX小时XX分钟XX秒”这种比较规范的格式来录入的，数据“混搭”比较严重。

文本, 信件
描述已自动生成

今天，让我们一起来学习一下此类问题的处理方法。

首先，我们先来分析一下时间的换算关系。以A2中的数据为例，11天21小时16分钟52秒如果想换算成小时的话，应该是这样的一个换算方式：11*24+21*1+16/60+52/3600。具体的逻辑为：在天数方面，一共是11天，每天有24小时，所以11天对应的小时数为11*24=264小时；在小时方面，一共是21小时，所以用小时数乘1就行了，也就是21*1=21小时；在分钟方面，一共是16分钟，而一小时有60分钟，所以用分钟数除以60就可以将分钟转换为小时，即16分钟＝16/60＝0.26666667小时；在秒方面，一共是52秒，而一小时有3600秒，所以我们用秒数除以3600就可以将秒转换成小时，即52秒＝52/3600=0.01444444小时。最后，我们再计算一下总和，就可以换算成小时数了。

为了便于大家理解，阿硕对本例中每一个时长的运算逻辑做了整理，如下图所示。在这里有一点请大家注意，由于小时数乘以1还是等于其自身，所以在下表以及后续的操作中，我们对小时不做乘1的处理。

好了，讲完了时间换算的逻辑，就可以得出解决今天这个问题的思路了：先将原始数据转换成运算表达式（使用SUBSTITUTE函数），然后再对这个运算表达式进行求和（使用EVALUATE函数）。

下面，就让我们一起来操作吧！

1.使用SUBSTITUTE函数替换“天”

我们在B2中输入“=SUBSTITUTE(A2,"天","*24+")”，然后向下复制填充公式，得到的结果如下图所示。可以看到，A2中的“11天”变成了“11*24+”，这就为天数的转换做好了准备。小伙伴们在这里一定要注意一点，那就是在24后面还要有一个加号（“+”）。因为如果不写这个加号的话，我们构造的表达式在后续的运算中就会出问题啦！

2.使用SUBSTITUTE函数替换“小时”

将天数替换完成之后，我们再来替换小时。这时，我们使用函数嵌套来进行操作。我们使用上一步骤中的SUBSTITUTE函数，作为本步骤中SUBSTITUTE函数的第一参数。

我们将B2中的公式修改为“=SUBSTITUTE(SUBSTITUTE(A2,"天","*24+"),"小时","+")”，然后向下复制填充公式，得到的结果如下图所示。可以看到，A2中的“21小时”变成了“21+”，这就为小时的转换做好了准备。（注：将“天”替换成“*1+”也是可以的哦~）

3.使用SUBSTITUTE函数替换“分钟”

将小时替换完成之后，我们再来替换分钟。我们使用SUBSTITUTE函数嵌套来进行操作。我们将B2中的公式修改为“=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"天","*24+"),"小时","+"),"分钟","/60+")”，然后向下复制填充公式，得到结果的如下。可以看到，A2中的“16分钟”变成了“16/60+”，这就为分钟的转换做好了准备。

4.使用SUBSTITUTE函数替换“秒”

将分钟替换完成之后，我们再来替换秒，我们继续使用SUBSTITUTE函数嵌套。由于“秒”是我们最后一个要替换的时间单位，所以在直觉上，我们的第一想法是只要将“秒”替换成“/3600”就行了，不需要再额外写一个加号。我们把B2中的公式修改为“=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"天","*24+"),"小时","+"),"分钟","/60+"),"秒","/3600")”，然后向下复制填充公式，得到的结果如下图所示。

此时，我们再观察一下数据，可以发现，B6、B9、B10、B11单元格均以加号作为结尾，对于这样的逻辑表达式，如果直接进行运算的话，是会产生问题的。看来直觉不是太可靠，我们还需要再改进一下函数。

该如何改进呢？首先，我们在“3600”后面加上一个加号试试。我们将B2中的公式修改为“=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"天","*24+"),"小时","+"),"分钟","/60+"),"秒","/3600+")”，得到的结果如下图所示。

由上图可以看到，所有的逻辑表达式的最后一个字符都变成加号了。不过大家别紧张，我们再微调一下函数，就可以轻松搞定！我们知道，任何数据加上0之后，数据还是等于其本身，所以，我们再通过连接运算符（“&”），在数据后面连接上一个0，这样可以既保证运算结果不变，又能够完美地解决了数据以加号作为结尾的问题。我们将B2中公式修改为“=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"天","*24+"),"小时","+"),"分钟","/60+"),"秒","/3600+")&0”，得到的结果如下图所示。

5.使用EVALUATE函数计算时间运算表达式

在前述4步中，我们已经构造了一个关于时间转换的逻辑表达式。想要对这个表达式求和的话，我们要用到一个宏表函数——EVALUATE函数。

EVALUATE是常用的宏表函数，它的作用是对以文本表示的一个公式或表达式求值，并返回结果。它的语法结构比较简单——只需要把要计算的表达式放在EVALUATE后面的括号内，就行了，如下：

=EVALUATE(formula_text)。

其中，formula_text是一个以文本形式表示的表达式。

在使用宏表函数时，有一点要注意：宏表函数只能通过定义名称来使用。下面，让我们来学习具体操作。

在进行定义名称的操作之前，请大家先将B2中的公式等号（“=”）之后内容复制一下（即“SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"天","*24+"),"小时","+"),"分钟","/60+"),"秒","/3600+")&0”）。这是因为，在接下来我们定义名称的时候，在EVALUATE函数前面也会有等号，如果此时把SUBSTITUTE嵌套函数前面的等号也复制过去的话，就会出现两个等号了，还要再额外进行删除操作。

复制完公式之后，我们按Ctrl+F3，则会弹出名称管理器，如下图所示。

点击“新建”，弹出“新建名称”对话框，如下图所示。

接下来，我们来对“名称”和“引用位置”这两处进行修改。我们将“名称”修改为“时间转换”；在“引用位置”后面的输入框中，输入“=evaluate()”，然后将刚才我们复制的那一长串不包含等号、层层嵌套的SUBSTITUTE函数粘贴到EVALUATE函数的括号中，如下图所示。（注：由于函数太长，所以截图不全，感兴趣的小伙伴可以拖动鼠标进行查看）