别找了，你要的Excel求和问题妙解都在这里了

继续送书！今天送3本《Excel 跟卢子一起学 早做完，不加班》，从留言区随机抽奖。

全套总共90个案例，超级全面。坚持看完，你的水平肯定会更上一层楼。如果点赞数超过100个，明天继续分享。

第一批：花了1年时间整理的90个公式，学完你就能碾压99%的同事

第二批：第二批！花了1年时间整理的90个公式，学完你就能碾压99%的同事

第三批：Excel中最牛的查找函数是VLOOKUP、LOOKUP，还是SUMIFS，谁最厉害？

1.计算文本表达式的和

在统计数据的时候忘记输入=号，如何计算这些文本表达式的结果呢？

STEP01 单击B2单元格，再单击“公式”选项卡的“定义名称”图标，在弹出的“新建名称”对话框，如果名称：表达式，引用位置为下面的公式，单击“确定”按钮。

=EVALUATE(A2)

STEP 02 在B2单元格输入公式，并向下复制。

原理分析

EVALUATE属于宏表函数。宏表函数是早期低版本Excel中使用的，现在已由VBA顶替它的功能。但仍可以在工作表中使用，不过要特别注意的是：不能直接在单元格中、只能在“定义的名称”中使用。

EVALUATE函数语法如下：

EVALUATE(表达式)

对以文字表示的一个公式或表达式求值，并返回结果。

在使用宏表函数或者VBA的时候，必须将工作簿另存为：Excel 启用宏的工作簿，否则功能会失效。

补充说明，在WPS表格中，可以直接使用EVALUATE得到结果。

=EVALUATE(A2)

2.对带颜色的项目进行求和

我们在使用Excel时，会遇到这样的情况：一个工作表中某些单元格填充为某种颜色，要求将填充了某种颜色的单元格进行快速求和。现在保价金额被分别填充成红色跟绿色背景色，如何分别对这两种背景色进行求和？

STEP 01 单击G2单元格，再单击“公式”选项卡的“定义名称”图标，在弹出的“新建名称”对话框，如果名称：颜色，引用位置为下面的公式，单击“确定”按钮。

=GET.CELL(63,D2)

STEP 02 在G2单元格输入公式，并向下复制。

=颜色

STEP 03 将颜色产生的数字依次填入H2跟H3。

STEP 04 在I2单元格输入公式，并向下复制。

=SUMIF(G:G,H2,D:D)

STEP 05 将工作簿另存为：Excel 启用宏的工作簿。

原理分析

SUMIF函数虽然可以进行条件求和，但不知直接对颜色进行条件求和。需要借助宏表函数GET.CELL获取背景色对应的数字，然后才能求和。

GET.CELL函数中的参数“63”的意思是：单元格填充颜色(背景)编码数字。

3.对产品进行统计并引用自定义数字格式

产品从不同国家购买，因此使用的货币格式不一样，如果对产品进行条件求和， 并引用B列原有的货币格式？

STEP 01 单击E2单元格，再单击“公式”选项卡的“定义名称”图标，在弹出的“新建名称”对话框，如果名称：格式，引用位置为下面的公式，单击“确定”按钮。

=GET.CELL(7,INDEX(Sheet1!$B:$B,MATCH(Sheet1!$D2,Sheet1!$A:$A,0)))

STEP 02 在E2单元格输入公式，并向下复制。

=TEXT(SUMIF(A:A,D2,B:B),格式)

STEP 03 将工作簿另存为：Excel 启用宏的工作簿。

原理分析

在名称“格式”中，使用INDEX函数与MATCH函数配合，查询D2在A列对应的值，并返回B列的单元格引用。使用GET.CELL函数，取得INDEX返回单元格的数字格式。

使用TEXT函数将SUMIF函数求得的和返回为“格式”的格式，“格式”是指原理B列对应的格式。

GET.CELL函数中的参数“7”的意思是：用于返回单元格的数字格式。

4.动态统计金额

这是一份每天出差花费清单，经常要在总金额上面插入行。用SUM函数直接统计有时不会对新增加的金额进行统计，该如何处理？

STEP 01 单击C18单元格，再单击“公式” 选项卡的“定义名称”图标，在弹出的“新建名称”对话框，如果名称：上一行，引用位置为下面的公式，单击“确定”按钮。

=C17

STEP 02 在C18单元格输入公式。

=SUM(C2:上一行) 原理分析

这个“上一行”的引用为相对引用，每插入一行引用位置就会动态变化，所以插入行也会自动汇总进去。

有人试过用SUM(区域)就能自动扩展区域，统计正确就认为任何情况下都可以，但实际上某些情况下还是不会自动扩展区域。几年前我吃过一个亏，有一天验证金额的时候，发现问题，金额最后一行没有统计，也就是少统计一个产品的金额。

小心驶得万年船，宁愿麻烦一点，也要保证数据的准确性。

5.包含单位的金额求和

这是一份每天出差花费清单，在输入金额的时候在后面输入单位，导致用SUM函数直接求和得不到正确答案，怎么样才能让含有单位的金额可以求和呢？

输入公式，按Ctrl+Shift+Enter三键结束。

=SUM(--SUBSTITUTE(C2:C17,"元",""))

原理分析

因为包含单位元，需要将单位去除掉才能求和。用SUBSTITUTE函数将元替换成空文本，也就是只提取数字。

SUBSTITUTE函数属于文本函数，所以得到的数字也属于文本， 这里叫做文本数字。数字有两种类型，一种是文本数字，一种是真正的数字，就是数值。数值可以直接求和，而文本不能求和。如账簿上的数字跟墙上的数字是不同，前者我们可以用这些数字进行各种分析，后者只能当欣赏用。

那有什么办法还原数字的本质呢？

把文本型转换成数值型，有专用的转换函数 VALUE。

=VALUE("25")，它的结果就是一个数值。

=VALUE("25")=25，它的结果就是TRUE了。

在函数或公式中，运算过程会自动把文本转换为数值(一个隐含过程)，再与数值进行运 算，负值运算(-)也是一种运算，能把文本转换成数值：

-"25"=-25

还记得负负得正吧？

-(-"25")=-(-25)=25

简写为：

--"25"=25

--可以把文本转换为数值，但它不是标准的转换方式，是借用负运算的隐含功能。

6.含姓名求总金额

金额跟姓名混合在一起，这样的金额又该如何统计呢？

输入公式，按Ctrl+Shift+Enter三键结束。

=SUM(--RIGHT(A2:A14,2*LEN(A2:A14)-LENB(A2:A14)))

原理分析

因为姓名跟金额的字符数都不确定，所以不能直接提取金额出来。不过可以利用汉字是双字节，数字是单字节的特点来提取金额。

金额在右边可以用RIGHT函数，汉字的个数就是，总字节减去总字符数，而数字的个数，就是总字符数减去汉字的字符数，也就是：

=LEN(A2)-文字数=LEN(A2)-(LENB(A2)-LEN(A2))=2*LEN(A2)-LENB(A2)

提取出来的数字都是文本数字，不能够直接求和，需要要--将文本数字转换成真正的数字才可以求和。

当然转换成数字也可以用1*、0+、/1、-0等方法，只要让文本数字运算即可。

7.含错误值求总数量

数量是从别的地方引用过来，导致有部分数据是错误值，如何避开这些错误值进行求和呢？

=SUMIF(C:C,"<9E+307")

原原理分析

Excel允许的最大数值是15位，9E+307是比最大数值还大的值，条件<9E+307就是包含所有数字，这样就可以排除错误值求和。数量中包含逻辑值、文本这些也照样可以用这种方法，因为最大的数字都比逻辑值、文本值、错误值还小。

8.根据姓氏统计产量

明细表记录了每个操作人员的产量，如何根据姓氏统计产量？比如张，就是姓名第一个字是张的所有人员。

=SUMIF(A:A,D2&"*",B:B)

原理分析

D2&"*"就是以D2开头的作为条件统计，*是通配符，代表全部。

9.统计销量前5名的和

明细表记录着每个人的销售量，如何统计销量前5名的和？

输入公式，按Ctrl+Shift+Enter三键结束。

=SUM(LARGE(B2:B17,ROW(1:5)))

原理分析

最大值用MAX函数，前几大用LARGE函数，函数语法如下：

LARGE(区域,N)

比如第2大，N就是2。现在要前5名，也就是ROW(1:5)。

作者：卢子，清华畅销书作者，《Excel效率手册 早做完，不加班》系列丛书创始人，个人公众号：Excel不加班（ID：Excelbujiaban）