最重要的文本函数text的进阶用法1

1.分数等级判断

如下图所示，根据右表的标准对左表的成绩判断等级。昨天也做过类似的题目，但是只有3个条件，今天这个题目有4个条件。在C2单元格中输入公式=TEXT(B2,"[>=90]优;[>=80]良;"&TEXT(B2,"[>=60]中;差"))，向下填充。

这里是text的嵌套用法，和if函数的嵌套在作用上是一样的，但是要注意它的写法，用&连接起来。实际上就是将里边的text函数的结果作为外边的text的第3个条件。

如果有更多的条件判断，多嵌套几个text也能搞定。

第2种解法，在D2单元格中输入公式=TEXT(0&B2-60,"[>=30]优;[>=20]良;中;差")，向下填充。这个text函数的第2参数有4部分代码，前面的3部分是对数字设置格式，最后的差是对文本设置格式。

第1参数为0&B2-60，这个的作用是将小于60分的转成文本，那么对应的格式就是差。以40分为例说明，0&40-60就是0&-20，最后的结果是"0-20"，这样就变成文本。如果是90，那么0&90-60的结果就是"030"，但是text会将"030"转成30，然后再对其设置格式。

第1参数减去60，那么第2参数的每个等级也要减去60。这种方法比较奇特，充分运用了text函数的特性和数学的思维方式，但是它只能对4个条件进行判断。如果是更多的条件判断，就要text的嵌套或者是其他方法来完成。

2.提取单个数字并求和

如下图所示，A列是一些字符串，有数字字母和汉字，求每个单元格的数字之和。以A12单元格为例说明，求2+5+0+3+4的和。在B11单元格中输入公式=SUM(--TEXT(MID(A11,ROW($1:$20),1),"0;;0;!0"))，按ctrl+shift+enter三键，向下填充。

这里用的是数组用法，先用mid将每个字符一一提取出来，作为text的第1参数，然后对其设置格式。这里的关键是text的第2参数，"0;;0;!0"有4部分，正数显示它本身，负数显示为空，0显示为0，文本强制显示为0。

由于提取出来的都是1位，不可能有负数的，而文本通过!0强制显示为0，这样就将文本转为0。由于text得出的结果是文本，直接用sum求和是0，所以用--转为数字，再求和。

3.计算2个日期时间相差几天几小时几分钟

如下图所示，有起始日期的时间和结束日期的时间，计算它们相差多少天多少小时多少分钟。在C17单元格中输入公式=TEXT(B17-A17,"d天")&TEXT(MOD(B17-A17,1),"h小时m分钟")，向下填充。

先用结束日期减去起始日期，得到一个数字，这个数字就是它们相差的天数，整数部分就是整天，小数部分就是不满一天的天数，将其化成小时数和分钟数。

TEXT(B17-A17,"d天")这部分就是整天数，B17-A17可能是小数，为什么不用int取整呢？原因就是第2参数的d，它只会取整天数。

TEXT(MOD(B17-A17,1),"h小时m分钟")这部分就是将不满1天的时间转成小时和分钟数。MOD(B17-A17,1)是取2个日期的差的小数部分。

如果对你有所帮助或启发，请打赏或分享一下，你的支持就是我最大的动力！

关注解锁更多函数的用法