Excel 中文本型数字如何正确求和?

理想是美好的,现实是残酷的。而文本型数字是这句话的绝佳印证。Excel 提供的各类求和类函数,例如 SUMSUMIFSUMPRODUCT 函数不会统计区域中的文本型数字。

虽然「数字」本应该是数字类型,但是实际环境下,难免会出现不是数字的「数字」。如果数据量较少,我们可以根据 Excel 自带的提示功能,很容易知道数据里有文本型数字,可以快速转换成数字类型。

但是数据量很大时,例如几千几万行,其中的部分文本型数字,无法快速分辨。如果我们直接用 SUM 类函数求和,将导致错误的结果。

今天给大家介绍一类求和公式,可以正确的对文本型数字求和。

公式

对于文本型数字,使用 SUMPRODUCT 函数,加上对求和区域的一点小处理,即可正确求和。以下 5 种公式均可正确求和,可按照自己的喜好选择。

=SUMPRODUCT(VALUE(求和区域))
=SUMPRODUCT((求和区域)*1)
=SUMPRODUCT((求和区域)+0)
=SUMPRODUCT((求和区域)-0)
=SUMPRODUCT(--(求和区域))

 

公式原理

SUM 类函数虽然无法转换文本型数字,但是文本型数字直接与数字四则运算时,将自动转换成正确的数字格式。

SUMPRODUCT 函数的原理简单的可以认为,对提供的参数展开,逐一处理后再求和。这里的「处理」正是上述提供的公式中的 VALUE 函数、乘以 1、加减零、求负运算等。

这些运算可以在不改变原值的情况下,可以对每一个参数进行四则运算。结合上述特性,四则运算时,文本型数字自动转换成标准数字格式。所以最后得到的和是包含文本型数字的正确的和。

值得一提的是,上述提供的第一个公式中的 VALUE 函数,是获得提供的参数的值。针对文本型数字,可以获取标准数字。这一过程也可以认为是一次「四则运算」处理。

转载注明:文章转载自「懒人Excel - www.lanrenexcel.com

您可能还喜欢...

订阅评论
提醒
2 评论
最新
最旧 最多投票
内联反馈
查看所有评论
柒与騹
3 年 前

直接分列不就行了吗

2
0
希望看到您的想法,请您发表评论x