|
学数组也有段时间，但卢子始终在数组的门外徘徊。听到Ctrl+Shift+Enter三键犹如老鼠看到猫，但心中又存在幻想，希望有朝一日神人出现或者天降宝典，那时卢子就能够轻松学好数组。但始终没等到这一天，后来卢子借用玩游戏的时间来学习数组公式，学了很久，忽然有一天发觉会了好多，真是意外的惊喜。 像玩游戏一样玩SUM函数。SUM函数看似简单，实则蕴含高深用法。什么是高手？高手就是能将最平常的函数变幻出神奇莫测的功能，解决掉你想都不敢想的问题。 1.闲聊SUM函数Q：如果我们要求1-10的和怎么办？ A：可以通过辅助列，在A1-A10分别输入1-10，然后用SUM函数求和，话说这个99%的人都会，如果你属于那1%的群体，那恭喜你，你可以关闭这篇文章，该干嘛干嘛去。
不过如果数据庞大的话，如输入1-10000，用这办法恐怕行不通，你光输入就得输入到哭。那这时就可以通过ROW函数来生成1-N这样的序号。
这时习惯了普通公式的人，一回车就会发现问题，怎么才1呢？ 刚开始学习数组公式的时候，卢子就经常犯这种低级错误。记住了，数组公式需要按Ctrl+Shift+Enter三键结束才可以，否则出错。三键一按，立马返回50005000，可以用数学速算法验证下，答案是正确的。
这个公式虽好，但假如有某个人误操作你的表格，将其中两行删除，这时你就傻眼了，公式自己会变。 看来这个公式并不完美，仍需改善，这时INDIRECT函数就派上用场
不管你怎么删除行，效果始终不变。 加上INDIRECT函数就相当于引用一个固定的1:10000，而不用借助单元格引用，直接用ROW(1:10000)其实还是要借助单元格才行实现。 现在不讨论这种万一的情况，继续聊新的话题。 Q：如果要求1-10奇数跟偶数又该如何？ A：其实我们可以利用MOD（数据,2），如果是奇数就是1，偶数就是0这个特点来处理。 奇数：
偶数：
Q：如果要求1-10的最大，小5个数的和要怎么做呢？ A：前几大可以用LARGE函数，前几小可以用SMALL函数。 前5大的和：
前5小的和：
LARGE跟SMALL函数的语法其实一样，学一个就行。一起来看看LARGE函数的语法：
求A列最大跟第2大的数字，最大就是N是1，第2大就是N是2，依次类推。 2.求和问题一次全搞定SUM函数谁都知道可以求和，但他还可以取代SUMIF、SUMIFS函数实现条件求和，你知道吗？当初就是被这一用法深深吸引，觉得SUM函数神奇，卢子才不知不觉喜欢上数组。闲话少说，进入主题。 某学校各人员成绩，现在需要统计各学部的总成绩？ 正常的话用SUMIF函数就可以搞定，SUMIF就是条件求和的意思。
其实我们可以将SUMIF拆分成两个函数SUM+IF，先进行条件判断，然后再求和。 以G2的学部财经为例，需要判断的条件就是E列学部是不是等于财经，如果是就返回成绩，不是就显示0。选择区域在K2:K16输入公式，然后按Ctrl+Shift+Enter结束。可以清楚得看到所有符合财经的都返回本身的成绩，不是财经的都返回0。
IF函数的区域判断跟单元格单独判断是一样的，其实这个公式等同于下面公式下拉的结果。唯一的差别在于，一个是选择区域按三键结束，一个是下拉获取结果而已。
到这一步已经完成了90%的工作，只需在最外面嵌套个SUM函数进行求和即可。
后面的所有数组公式都是需要按三键结束，在这里强调一下，为了方便说明，不再重复。 照着这个思路，看看SUM函数如何取代SUMIFS函数。现在增加了一个条件性别，变成多条件求和。 我们都知道在普通公式里面AND就表示并且的意思，按常规想法已经是这样设置公式
输入公式后的结果让人大跌眼镜，居然全部都是0。 这时用常规公式，发现并没有异常，怎么回事呢？
这时有“独孤九剑”之称的F9键就派上用场。 在编辑栏选择AND部分，这时就会出现抹黑现象，然后F9键，得到FALSE，只有一个结果，也就是说只要有任何一个条件不满足就是FALSE，这样一来肯定有问题。
必须得到15个结果才行。那有没有其他函数可以取代AND函数，答案是肯定的，那就是*。
现在用F9键同样对条件进行测试。
得到这样的结果：
也就是说同时满足两个条件的返回1，否则返回0。1就是TRUE所以返回D列对应值，0就是FALSE所以返回0。如果对F9键还不熟练，没关系，继续回到单元格内进行测试，跟我们的判断一致。 现在只需在最外面嵌套SUM函数即可。
通过*知道可以将各个条件连接起来，其实求和区域也可以看成一个条件，将公式变成：
这样SUM函数条件求和的通用公式就出来了。
SUM函数不仅仅可以实现条件求和，还可以进行各种各样的求和，下面通过一些例子来见识下。 数量是用VLOOKUP函数查询引用过来，找不到对应值就显示#N/A，如果直接求和的话出错，该如何处理？
解决方案： 01 加一个条件来判断数量是不是数字，就可以搞定。
ISNUMBER函数只有一个参数，作用就是判定单元格是不是数字，如果是返回TRUE，否则返回FALSE。 IS类函数还有好多个，语法都差不多，有兴趣的可以了解下。将鼠标放在函数上面就有出现一些说明。
02 借助高版本函数进行容错，再求和会更简单。
这两个函数的用法前面说过，这里就不再重复解释。 统计各季度的销售额。
SUM函数不能直接用*，但FIND函数也可以实现查找季度这2个字符是不是存在，如果存在就返回字符所在位置，否则返回错误值。 ISNUMBER函数判断是否是数字，也就是包含季度，如果是返回B列的销售额。 当然这里只是为了说明用法，因为数据源本身就很有规律，每个季度都等于3个月的合计，所以也可以采取取巧法。
截止到目前SUM函数仅仅是取代SUMIF跟SUMIFS，还不能真正体现出价值，后面看看他如何实现别人做不到的事儿。 含有单位的人员销售清单，直接求和得不出正确答案，怎么才能让含单位的销售额可以求和呢？
帮助提到，SUM函数会自动忽略文本，600元这种就是文本，不属于数字。最简单的做法就是将元替换成空，然后自定义单元格格式G/通用格式"元"。很多人就是搞不明白一格一属性的道理，才会造成汇总数据困难重重。正确的做法应该将元写在字段名那里变成销售额（元），这样别人一看便知道。废话了这么多，进入正题，别见怪，只是有感而发而已。 刚才提到了替换这个词，函数中也有属于自己的替换函数，SUBSTITUTE函数。
SUBSTITUTE第四参数为可选，那就先别管他，其他参数可以理解为：
单元格的元是多余的，需要替换成空，空可以用""表示，替换成空后直接求和，可以吗，不验证猜一下？
这个是数组公式，用法也跟前面差不多，目测应该可以汇总。 但实际SUBSTITUTE这个函数属于文本函数，所以替换得到的数字，也属于文本，在这里叫做文本数字。数字有两种类型，一种是文本数字，一种是真正的数字，即数值。数值是可以求和，而文本不能求和。如：账簿上的数字跟墙上的数字是不同的，前者我们可以用这些数字进行各种分析，后者只能当欣赏用。那有什么办法还原数字的本质呢？ 利用VALUE函数可以将文本型转换成数值型。
但一般情况下不会用这一种，而是通过运算转换。 一起来了解“减负”运算。 在函数或公式中，运算过程会自动把文本转换为数值(一个隐含过程)，再与数值进行运算，负值运算(-)也是一种运算，能把文本转换成数值：
还记得负负得正吧？
简写为：
--可以把文本转换为数值，但它不是标准的转换方式，是借用负运算的隐含功能。 其实负负运算称为减负运算更好，减去数字的负担，还原数字的本质。
将这一部分用F9键抹黑，得到：
这样就能够求和了。 综上，最终的数组公式为：
如果对字符提取三兄弟有印象的话，还可以这样设置公式，因为都是包含元，也可以利用总字符数-1这个特点来做
最后再来一个高段黑的，录入金额的时候，居然把姓名也写在一起。
看到这个，卢子都头大，奉劝大家一句，别把Excel当Word使用，否则后果很严重。 不过即使困难重重，SUM函数也能一一拿下，没有求不了和的数据，只有求不了和的人。 我们知道LEN函数可以统计字符数，其实他还有一个兄弟叫LENB函数，他是统计字节数。汉字2字节，数字1字节。利用这个特点我们可以得出： 汉字的个数：
数字的个数：
最终公式：
3.SUM函数其实也能计数SUM函数能实现各种各样求和那也是情理之中，如果说SUM函数能取代COUNTIF、COUNTIFS函数实现条件计数，也许很多人会觉得天方夜谭。SUM函数强大得难以想象，很多人掌握的知识只是冰山一角。
提取姓名中的第一个字符也就是姓氏，可以用LEFT函数。
第二参数省略不写的话，默认就是提取1位，所以可简化为：
结合前面SUM+IF的用法可用：
让符合条件的显示1，然后嵌套SUM进行求和。 统计各学部男女的人数。
利用SUM+IF的做法，依样画葫芦也能做到，但这里不做说明。大家是否还记得SUM函数求和的通用公式：
其实省略掉求和区域就是条件计数的通用公式：
有了这个通用公式，条件计数so easy！
计数还有一个经典的问题，就是不重复计数。统计不重复学部的个数。
计算区域不重复个数的经典公式，需要好好理解。
如果区域很多的话，可以改小，这样便于理解。 观察
F9键抹黑
Esc键返回
F9键抹黑
Esc键返回，在单元格按三建结束看到结果：4。 分析 F9键观察有时并不太直观，回到工作表中继续看看。
=COUNTIF(E2:E16,E2:E16)是多单元格数组，等同于=COUNTIF(E$2:E$16,E2)下拉的结果，也就是统计每个单元格本身出现的次数，如1。 =1/COUNTIF(E2:E16,E2:E16)是多单元格数组，等同于=1/ COUNTIF(E$2:E$16,E2)下拉的结果，也就是1/每个单元格本身出现的次数，为了让数据更直观转换成分数形式，如2。 出现4次就变成1/4，出现3次就变成1/3。1/3+1/3+1/3=3*(1/3)=1，1/N+…+1/N=N*(1/N)=1，不管出现几次，相加都等于1。 最后将这些相加就是得到不重复的数量，如3。 解读公式的一些习惯： 1. 把区域改小，这样便于查看，如A1:A1000改成A1:A9。 2. F9键配合Ctrl+Z或者Esc键不断地看运算过程再返回，重复到理解为止。 3. 输入公式回到单元格查看运算过程，这种相对比较直观。 4. 分析。 第2，3点可选，看你对公式的熟练程度，如果不熟练选择3，熟练的话选择2。 学好SUM函数不过是为了打开数组之门，知道数组的一些基础用法，要学好数组还需要更多的知识支撑才可以。 推荐：每个Excel学得好的人都有一段不为人知的往事，我有故事，你想听吗 每个人都是从不懂到懂，多花点时间和精力，用心学习，你也可以学得很好。尝试从学习中找到乐趣，培养自己的兴趣爱好，这样会学得更快。 在学习Excel的过程中，你有没发现什么有趣的事儿？
作者：卢子，清华畅销书作者，《Excel效率手册 早做完，不加班》系列丛书创始人，个人公众号：Excel不加班（ID：Excelbujiaban） |
|
|
