还在用老方法对带单位的数字求和？这个方法帮你一步搞定！

每天一点小技能

职场打怪不得怂

编按：说到对带单位的数字进行求和等问题时，我们似乎已经掌握了一些方法……可是，有时那些方法还是会显得繁琐甚至无用，例如：面对数字所带单位长度和名称均不同等各种状况，我们有没有更简单有效的处理方法？今天，小E带来三种解决此类问题的方法，保你一学就会……

带单位数字的计算问题很让人头疼，其根源在于数据源的不规范性。

今天的文章不是讨论表格的使用规范，也不是操作技巧的教程。今天的案例将通过分析几种常见问题，由浅入深的对带单位数字的计算问题进行一次梳理，让大家能够举一反三。不仅知其然，更要知其所以然。

1

数字后面的单位都是统一的

如下图所示，每个人的销售额后面都有一个“元”字。

要对这样的一列数字求和，比较常用的是SUMPRODUCT和SUBSTITUTE组合，公式为：

= SUMPRODUCT (1* B2:B14 (B2:B14,"元",""))&"元"

思考：

这个公式的原理比较简单，首先是SUBSTITUTE(B2:B14,"元","")这一部分。关于SUBSTITUTE的用法，之前有专门的教程，这个函数的功能是把单元格内指定的字符换成另一个内容，有点类似于查找替换功能。

思路解析：

①平时使用函数的时候，第一参数是一个单元格。在本例中第一参数使用的是数据区域，目的是把B2:B14这个范围内的每个单元格中的“元”字替换为空，也就是清除单位。

②由于SUBSTITUTE的结果是文本格式，不能直接求和，所以在前面用1*将结果转为数字。1*也可以写成--，也就是两个减号，利用负负得正来实现文本转数字的效果。

③1*B2:B14 (B2:B14,"元","")得到的是一组数字，要对一组数字求和需要用到SUMPRODUCT函数。如果用SUM函数的话，需要同时按Ctrl、shift和回车键才行。

④最后在求和结果后连接一个“元”字保持整体一致。

思路扩展：

如果单位是两个字的话，方法也是一样的，例如数量单位都是“公斤”，则求和公式对应修改为=SUMPRODUCT(1*SUBSTITUTE(C2:C14,"公斤",""))&"公斤"即可。如下图：

2

单位不一致，但是单位的长度一致

这种情况比较少见，例如每个业务员需要领取不同包装方式和数量的赠品答谢客户，现在需要对赠品数量（可以理解为份数，不管是一盒还是一包都算作一份包装下的赠品）进行汇总，具体数据如图所示。

这种情况一般用公式=SUMPRODUCT(LEFT(D2:D14,LEN(D2:D14)-1)*1)解决，与第一种情况的区别在于将SUBSTITUTE这部分改为LEFT-LEN组合，LEFT函数的作用是从单元格数据的最左边开始提取指定字数的内容。

思路解析：

①在本例中，难点是如何确定数字的位数，因此借助了LEN函数辅助。LEN函数的功能是统计单元格内数据的字数，因为单位都是一个字，所以数字的位数就是整体内容的字数减1。公式中的LEFT(D2:D14,LEN(D2:D14)-1)就是来提取数字的。

②同样，LEFT函数得到的也是文本，需要处理后才能求和。这和第一类问题的原理完全一样，不再赘述。

3

单位不统一，字数也不一致

这种情况一般是针对同一行的数据进行计算，例如下面这个例子。

思考：

金额=单价*数量，这个公式原本很简单，但是因为数量中存在着字数不等的单位，就需要先将数量中的数字提取出来后才能计算金额。

思路解析：

①上面的图中用到的公式是=B2*-LOOKUP(1,-LEFT(C2,ROW($1:$9)))。它看似与前两类状况是一样的“带单位数字的计算”的问题，但是有本质上的区别。

②前两类是数组计算，后面这类问题的本质却是找出对单元格内的数字的提取方法，进而再去计算。就本例而言，还有一个更为常见的公式套路：=B2*LEFT(C2,LEN(C2)*2-LENB(C2))

关于如何从单元格提取数字，之前有一篇很详细的教程，本文就不再赘述。

小结：

还是要再次强调数据源的规范性！不论何种情况，规范的数据源是高效工作的前提。有些视觉效果可以用自定义格式去实现，例如统一添加单位“元”。

总之，一个单元格不要出现两种属性的内容，数字和单位分开存放才是最合理的。