|
继续送书！今天送3本《Excel 跟卢子一起学 早做完，不加班》，从留言区随机抽奖。 全套总共90个案例，超级全面。坚持看完，你的水平肯定会更上一层楼。如果点赞数超过100个，明天继续分享。 第一批：花了1年时间整理的90个公式，学完你就能碾压99%的同事 第二批：第二批！花了1年时间整理的90个公式，学完你就能碾压99%的同事 第三批：Excel中最牛的查找函数是VLOOKUP、LOOKUP，还是SUMIFS，谁最厉害？ 1.计算文本表达式的和 在统计数据的时候忘记输入=号，如何计算这些文本表达式的结果呢？ STEP 01 单击B2单元格，再单击“公式”选项卡的“定义名称”图标，在弹出的“新建名称”对话框，如果名称：表达式，引用位置为下面的公式，单击“确定”按钮。
STEP 02 在B2单元格输入公式，并向下复制。 原理分析EVALUATE属于宏表函数。宏表函数是早期低版本Excel中使用的，现在已由VBA顶替它的功能。但仍可以在工作表中使用，不过要特别注意的是：不能直接在单元格中、只能在“定义的名称”中使用。 EVALUATE函数语法如下：
对以文字表示的一个公式或表达式求值，并返回结果。 在使用宏表函数或者VBA的时候，必须将工作簿另存为：Excel 启用宏的工作簿，否则功能会失效。 补充说明，在WPS表格中，可以直接使用EVALUATE得到结果。
2.对带颜色的项目进行求和我们在使用Excel时，会遇到这样的情况：一个工作表中某些单元格填充为某种颜色，要求将填充了某种颜色的单元格进行快速求和。现在保价金额被分别填充成红色跟绿色背景色，如何分别对这两种背景色进行求和？ STEP 01 单击G2单元格，再单击“公式”选项卡的“定义名称”图标，在弹出的“新建名称”对话框，如果名称：颜色，引用位置为下面的公式，单击“确定”按钮。
STEP 02 在G2单元格输入公式，并向下复制。
STEP 03 将颜色产生的数字依次填入H2跟H3。 STEP 04 在I2单元格输入公式，并向下复制。
STEP 05 将工作簿另存为：Excel 启用宏的工作簿。 原理分析SUMIF函数虽然可以进行条件求和，但不知直接对颜色进行条件求和。需要借助宏表函数GET.CELL获取背景色对应的数字，然后才能求和。 GET.CELL函数中的参数“63”的意思是：单元格填充颜色(背景)编码数字。 3.对产品进行统计并引用自定义数字格式产品从不同国家购买，因此使用的货币格式不一样，如果对产品进行条件求和， 并引用B列原有的货币格式？ STEP 01 单击E2单元格，再单击“公式”选项卡的“定义名称”图标，在弹出的“新建名称”对话框，如果名称：格式，引用位置为下面的公式，单击“确定”按钮。
STEP 02 在E2单元格输入公式，并向下复制。
STEP 03 将工作簿另存为：Excel 启用宏的工作簿。 原理分析在名称“格式”中，使用INDEX函数与MATCH函数配合，查询D2在A列对应的值，并返回B列的单元格引用。使用GET.CELL函数，取得INDEX返回单元格的数字格式。 使用TEXT函数将SUMIF函数求得的和返回为“格式”的格式，“格式”是指原理B列对应的格式。 GET.CELL函数中的参数“7”的意思是：用于返回单元格的数字格式。 4.动态统计金额这是一份每天出差花费清单，经常要在总金额上面插入行。用SUM函数直接统计有时不会对新增加的金额进行统计，该如何处理？ STEP 01 单击C18单元格，再单击“公式” 选项卡的“定义名称”图标，在弹出的“新建名称”对话框，如果名称：上一行，引用位置为下面的公式，单击“确定”按钮。
STEP 02 在C18单元格输入公式。
原理分析这个“上一行”的引用为相对引用，每插入一行引用位置就会动态变化，所以插入行也会自动汇总进去。 有人试过用SUM(区域)就能自动扩展区域，统计正确就认为任何情况下都可以，但实际上某些情况下还是不会自动扩展区域。几年前我吃过一个亏，有一天验证金额的时候，发现问题，金额最后一行没有统计，也就是少统计一个产品的金额。 小心驶得万年船，宁愿麻烦一点，也要保证数据的准确性。 5.包含单位的金额求和这是一份每天出差花费清单，在输入金额的时候在后面输入单位，导致用SUM函数直接求和得不到正确答案，怎么样才能让含有单位的金额可以求和呢？ 输入公式，按Ctrl+Shift+Enter三键结束。
原理分析因为包含单位元，需要将单位去除掉才能求和。用SUBSTITUTE函数将元替换成空文本，也就是只提取数字。 SUBSTITUTE函数属于文本函数，所以得到的数字也属于文本， 这里叫做文本数字。数字有两种类型，一种是文本数字，一种是真正的数字，就是数值。数值可以直接求和，而文本不能求和。如账簿上的数字跟墙上的数字是不同，前者我们可以用这些数字进行各种分析，后者只能当欣赏用。 那有什么办法还原数字的本质呢？ 把文本型转换成数值型，有专用的转换函数 VALUE。 =VALUE("25")，它的结果就是一个数值。 =VALUE("25")=25，它的结果就是TRUE了。 在函数或公式中，运算过程会自动把文本转换为数值(一个隐含过程)，再与数值进行运 算，负值运算(-)也是一种运算，能把文本转换成数值： -"25"=-25 还记得负负得正吧？ -(-"25")=-(-25)=25 简写为： --"25"=25 --可以把文本转换为数值，但它不是标准的转换方式，是借用负运算的隐含功能。 6.含姓名求总金额金额跟姓名混合在一起，这样的金额又该如何统计呢？ 输入公式，按Ctrl+Shift+Enter三键结束。
原理分析因为姓名跟金额的字符数都不确定，所以不能直接提取金额出来。不过可以利用汉字是双字节，数字是单字节的特点来提取金额。 金额在右边可以用RIGHT函数，汉字的个数就是，总字节减去总字符数，而数字的个数，就是总字符数减去汉字的字符数，也就是：
提取出来的数字都是文本数字，不能够直接求和，需要要--将文本数字转换成真正的数字才可以求和。 当然转换成数字也可以用1*、0+、/1、-0等方法，只要让文本数字运算即可。 7.含错误值求总数量数量是从别的地方引用过来，导致有部分数据是错误值，如何避开这些错误值进行求和呢？
原原理分析Excel允许的最大数值是15位，9E+307是比最大数值还大的值，条件<9E+307就是包含所有数字，这样就可以排除错误值求和。数量中包含逻辑值、文本这些也照样可以用这种方法， 因为最大的数字都比逻辑值、文本值、错误值还小。 8.根据姓氏统计产量明细表记录了每个操作人员的产量，如何根据姓氏统计产量？比如张，就是姓名第一个字是张的所有人员。
原理分析D2&"*"就是以D2开头的作为条件统计，*是通配符，代表全部。 9.统计销量前5名的和明细表记录着每个人的销售量，如何统计销量前5名的和？ 输入公式，按Ctrl+Shift+Enter三键结束。
原理分析最大值用MAX函数，前几大用LARGE函数，函数语法如下：
比如第2大，N就是2。现在要前5名，也就是ROW(1:5)。 作者：卢子，清华畅销书作者，《Excel效率手册 早做完，不加班》系列丛书创始人，个人公众号：Excel不加班（ID：Excelbujiaban） |
|
|
