如何对带单位的数字进行求和
作者：老菜鸟来源：部落窝教育发布时间：2021-01-27 10:51:23点击：8491
编按：
工作中，我们常常需要对Excel中的各种数据进行统计求和，常见的各种求和操作显然已经难不倒我们。可是当突然面对带单位的数字时，我们还是会有种难以下手的感觉……如何对带单位的数字进行求和等计算？如果数字所带单位长度和名称均不同，又该如何处理呢？今天，小E将带来三种解决此类问题的方法，保你一学就会！
带单位数字的计算问题很让人头疼，其根源在于数据源的不规范性。
今天的文章不是讨论表格的使用规范，也不是操作技巧的教程。今天的案例将通过分析几种常见问题，由浅入深的对带单位数字的计算问题进行一次梳理，让大家能够举一反三。不仅知其然，更要知其所以然。
第一类情况：数字后面的单位都是统一的。
如下图所示，每个人的销售额后面都有一个“元”字。
要对这样的一列数字求和，比较常用的是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,"公斤",""))&"公斤"即可。如下图：
第二类情况：单位不一致，但是单位的长度一致。
思考：
这种情况比较少见，例如每个业务员需要领取不同包装方式和数量的赠品答谢客户，现在需要对赠品数量（可以理解为份数，不管是一盒还是一包都算作一份包装下的赠品）进行汇总，具体数据如图所示。
这种情况一般用公式=SUMPRODUCT(LEFT(D2:D14,LEN(D2:D14)-1)*1)解决，与第一种情况的区别在于将SUBSTITUTE这部分改为LEFT-LEN组合，LEFT函数的作用是从单元格数据的最左边开始提取指定字数的内容。
思路解析：
① 在本例中，难点是如何确定数字的位数，因此借助了LEN函数辅助。LEN函数的功能是统计单元格内数据的字数，因为单位都是一个字，所以数字的位数就是整体内容的字数减1。公式中的LEFT(D2:D14,LEN(D2:D14)-1)就是来提取数字的。
② 同样，LEFT函数得到的也是文本，需要处理后才能求和。这和第一类问题的原理完全一样，不再赘述。
第三类情况：单位不统一，字数也不一致。
这种情况一般是针对同一行的数据进行计算，例如下面这个例子。
思考：
金额=单价*数量，这个公式原本很简单，但是因为数量中存在着字数不等的单位，就需要先将数量中的数字提取出来后才能计算金额。
思路解析：
① 上面的图中用到的公式是=B2*-LOOKUP(1,-LEFT(C2,ROW($1:$9)))。它看似与前两类状况是一样的“带单位数字的计算”的问题，但是有本质上的区别。
② 前两类是数组计算，后面这类问题的本质却是找出对单元格内的数字的提取方法，进而再去计算。就本例而言，还有一个更为常见的公式套路：=B2*LEFT(C2,LEN(C2)*2-LENB(C2))
关于如何从单元格提取数字，之前有一篇很详细的教程，本文就不再赘述。
小结：
老菜鸟还是要再次强调数据源的规范性！不论何种情况，规范的数据源是高效工作的前提。有些视觉效果可以用自定义格式去实现，例如统一添加单位“元”。
总之，一个单元格不要出现两种属性的内容，数字和单位分开存放才是最合理的。
本文配套的练习课件请加入QQ群：902294808下载。
做Excel高手，快速提升工作效率，部落窝教育《一周Excel直通车》视频和《Excel极速贯通班》直播课全心为你！
扫下方二维码关注公众号，可随时随地学习Excel：
相关推荐：
版权申明：
文本作者老菜鸟；同时部落窝教育享有专有使用权。若需转载请联系部落窝教育。