继续送书！今天送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）
