Excel 中文本型数字如何正确求和?
理想是美好的,现实是残酷的。而文本型数字是这句话的绝佳印证。Excel 提供的各类求和类函数,例如 SUM、SUMIF、SUMPRODUCT 函数不会统计区域中的文本型数字。
虽然「数字」本应该是数字类型,但是实际环境下,难免会出现不是数字的「数字」。如果数据量较少,我们可以根据 Excel 自带的提示功能,很容易知道数据里有文本型数字,可以快速转换成数字类型。
但是数据量很大时,例如几千几万行,其中的部分文本型数字,无法快速分辨。如果我们直接用 SUM 类函数求和,将导致错误的结果。
今天给大家介绍一类求和公式,可以正确的对文本型数字求和。
公式
对于文本型数字,使用 SUMPRODUCT 函数,加上对求和区域的一点小处理,即可正确求和。以下 5 种公式均可正确求和,可按照自己的喜好选择。
=SUMPRODUCT(VALUE(求和区域))
=SUMPRODUCT((求和区域)*1)
=SUMPRODUCT((求和区域)+0)
=SUMPRODUCT((求和区域)-0)
=SUMPRODUCT(--(求和区域))
公式原理
SUM 类函数虽然无法转换文本型数字,但是文本型数字直接与数字四则运算时,将自动转换成正确的数字格式。
SUMPRODUCT 函数的原理简单的可以认为,对提供的参数展开,逐一处理后再求和。这里的「处理」正是上述提供的公式中的 VALUE 函数、乘以 1、加减零、求负运算等。
这些运算可以在不改变原值的情况下,可以对每一个参数进行四则运算。结合上述特性,四则运算时,文本型数字自动转换成标准数字格式。所以最后得到的和是包含文本型数字的正确的和。
值得一提的是,上述提供的第一个公式中的 VALUE 函数,是获得提供的参数的值。针对文本型数字,可以获取标准数字。这一过程也可以认为是一次「四则运算」处理。
直接分列不就行了吗
行倒是行,具体还是看需求场景,各有利弊