Excel2010新增函数中的多面手AGGREGATE
作者：Excel123 文章来源：www.excel123.cn 点击数： 更新时间： 10:47:46
&&& AGGREGATE是Excel 2010中新增的函数之一，它可以返回列表或数据库中的合计。 AGGREGATE不仅可以实现诸如SUM、AVERAGE、COUNT、LARGE等19个函数的功能，而且还可以忽略隐藏行、错误值、空值等。如果区域中包含错误值，SUM等函数将返回错误，这时用 AGGREGATE函数就非常方便了。其引用形式的语法为：
&&& AGGREGATE(function_num, options, ref1, [ref2], …)
&&& 其中第一个参数“function_num&”为一个介于1到19之间的数字，指定要使用的函数，见下表。
计算平均值
计算参数中数字的个数
计算区域中非空单元格的个数
返回参数中的最大值
返回参数中的最小值
返回所有参数的乘积
基于样本估算标准偏差
基于整个样本总体计算标准偏差
基于样本估算方差
计算基于样本总体的方差
返回给定数值的中值
返回数组或区域中出现频率最多的数值
返回数据集中第k个最大值
返回数据集中的第k个最小值
PERCENTILE.INC
返回区域中数值的第K(0≤k≤1)个百分点的值
QUARTILE.INC
返回数据集的四分位数（包含0和1）
PERCENTILE.EXC
返回区域中数值的第K（0&k&1)个百分点的值
QUARTILE.EXC
返回数据集的四分位数(不包括0和1）
&&& 第二个参数“options”为为一个0至7之间的数字，指定要忽略的项目。在单元格中输入该函数的第二个参数时，Excel会自动给出该参数的选择列表，如图。
&&& 第三个参数“ref1”为区域引用或一个数组，第四个可选参数“[ref2]”为某些函数必需的第二个参数。
&&& AGGREGATE函数与SUBTOTAL函数有点类似，当SUBTOTAL函数的第一个参数为“101-111”之间的数字时，就会忽略隐藏值，但SUBTOTAL函数不能忽略错误值。
&&& 例如在下图的A2:F12区域是由公式返回的动态数据区域，其中某些单元格可能会返回错误值，要对这些包含错误值的区域求和，在Excel 2010中使用AGGREGATE函数更为简洁。
&&& 如果在G10单元格中输入公式：
&&& =SUM(B10:F10)
&&& 公式返回错误“#N/A”。而公式：
&&& =AGGREGATE(9,6,B10:F10)
&&& 会忽略错误值而返回B10:F10区域的合计值。
&&& 值得注意的是，AGGREGATE函数只能忽略隐藏行，而不能忽略隐藏列。
上一篇文章：
下一篇文章：
相关教程：
　网友评论：（评论内容只代表网友观点，与本站立场无关！）
::发表评论::
评论内容：
请遵守及中华人民共和国其他各项有关法律法规。
严禁发表危害国家安全、损害国家利益、破坏民族团结、破坏国家宗教政策、破坏社会稳定、侮辱、诽谤、教唆、淫秽等内容的评论 。
用户需对自己在使用本站服务过程中的行为承担法律责任（直接或间接导致的）。
评论需要审核通过后才可见，本站管理员有权保留或删除评论内容。
评论内容只代表网友个人观点，与本网站立场无关。
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
Excel技巧天地 E-mail:查看: 313604|回复: 276
VLOOKUP进阶——你可能所不知道的VLOOKUP用法
阅读权限100
在线时间 小时
本帖已被收录到知识树中，索引项：
本帖最后由 看见星光 于
13:07 编辑
=====================写在前面====================
VLOOKUP函数，号称函数家族中的大众情人，是最简单最实用的函数之一，也是我们最常用的三个函数之一（其余两个是霸主级的SUM和IF），关于它的每一寸肌肤（男生）每一块肌肉（女生），想必均都了如指掌，熟捻于心了。但是，您是否真正了解他的心呢？
因为这句反问，勾起伤心往事的，男生请走开，女生拥抱安慰，哈哈。
接下来我们有三个问题。第一个，VLOOKUP能否查询符合条件的多个数值？就像经典数组套路INDEX+SMALL+IF那样？第二个，VLOOKUP能否进行条件求和？就像SUMIF那样？第三个，VLOOKUP第一参数，能否支持数组？
这三个问题，想必很多新人都会这样回答——不行……吧……？我以前也这么认为。但，这是真的吗？——这当然不是真的。
这个帖子，咱们希望通过四个例子，扩展大家对于VLOOKUP函数的理解，更新大家对于VLOOKUP固定而死板的印象。
例子三，。
帖子进了知识树，所以看帖子的人总还是会有吧，所以我还是更新下例题附件吧：
(24.47 KB, 下载次数: 11160)
00:09 上传
点击文件名下载附件
再增加一个附件：入门VLOOKUP（年前给同事培训做的一个小东西，仅供练习消遣，真正的入门贴，还是看知识树哈）
(124.65 KB, 下载次数: 10234)
13:04 上传
点击文件名下载附件
有些朋友看不明白多维数组部分，可以抹黑公式，按F9查看计算过程，如果实在茫无头绪，不妨置之不理，只要能看懂辅助列部分，相信便是有所收获，呵呵，对吧？（我每次发呵呵，都忍不住想起胡剑么么哒，晕）。
第一个参数搞成数组，似乎第三个数组就无效了&
阅读权限100
在线时间 小时
本帖最后由 看见星光 于
12:43 编辑
第一节：VLOOKUP按指定次数重复数据
如下图，有这样一道题，要求按照B列的指定次数，重复C列的班级名称，结果放入E列。
1.jpg (28.64 KB, 下载次数: 3798)
11:19 上传
看到这道题，好学的表亲们，大概会立刻想起祝老师的某个基础操作教程动画，函数了得的亲们，会立刻想起某个多维数组套路。但咱们这里只想VLOOKUP。
如果用VLOOKUP，这题怎么做？很简单。只需要两步。
第一步，A2输入公式：=A1+B2，向下填充第二步，E2输入公式：=IFERROR(VLOOKUP(ROW(A1),A:C,3,0),E3)&&&，向下拖动。然后……结果……
2.jpg (35.93 KB, 下载次数: 3604)
11:20 上传
我们结合两个公式，解释下其中过程。
第一个公式：A1+B2，是计算相关次数的累计值，比较好理解。第二个公式：=IFERROR(VLOOKUP(ROW(A1),A:C,3,0),E3)&&&，看起来是常用的VLOOKUP套路，但其实有两个很有意思的地方。
其一，VLOOKUP的查找值——ROW(A1)。在公式的下拉过程中，通过查找1，2，3，4，5，（电脑配音，12345，上~山~打~老~虎）……来返回结果。
其二，屏蔽VLOOKUP错误值的方式。如果VLOOKUP查找不到相关数值，比如此例中的1和2，通常会返回错误值#N/A，而我们通过IFERROR，使它返回公式所在单元格的下一个单元格的值。比如，我们在E2输入公式，VLOOKUP函数的错误值则返回E3，公式向下拖动，E3的错误值返回E4……如此类推，直至VLOOKUP函数返回正确值——则之前通过IFERROR函数判断为错误值的单元格，自然统一更新为相应的正确值（……脑海里播放多米诺骨牌从依次跌倒到依次站起的画面）。然后再进行新一轮循环判断、数据更正。
最后的&””，是函数里常用的屏蔽零值的技法，以便在VLOOKUP公式下拉过界时，返回的零值显示为空白。
以上两个公式，除了VLOOKUP(ROW(A1)……)的技巧外，还利用了函数的另外一个技巧，我们姑且称之为上下其手。何谓上下其手？简而言之，便是拿公式所在单元格的上下单元格结果为己用。第一个公式，=A1+B2,是上手，拿公式所在单元格的上一个单元格的值为己用。很多人比较熟悉常用，已经很了解了。第二个公式，=IFERROR(VLOOKUP(ROW(A1),A:C,3,0),E3)&&&，是下手，拿公式所在单元格的下一个单元格的值为己用。大家用的可能就比较少了。因为少用，所以才显得比较有意思。大家有闲时，不妨多想下，兴许别有收获哈。
==========我是似水温柔的分割线==========
通过辅助列的方式，我们实现了按指定次数重复数据。下面咱们要做的，便是丢掉辅助列，直接用一个公式得出结果。即，我们需要把A列累计次数求和的数据，放入VLOOKUP公式的查找范围中，以便直接得出所需要的结果。
我们可以使用这样的公式：SUMIF(OFFSET($B$2,,,ROW($1:$4),),&&&&)这是一个累计求和的多维数组套路，类似的套路还有MMULT、INDIRECT、SUBTOTAL等。这个公式，是通过OFFSET函数，制作多维求和统计范围，比如B2:B2,B2:B3,B2:B4……最后使用SUMIF进行求和。
我们把这一段放入VLOOKUP函数中：E2=IFERROR(VLOOKUP(ROW(A1),IF({1,0},SUMIF(OFFSET($B$2,,,ROW($1:$4),),&&&&),$C$2:$C$5),2,0),E3)&&&如此，这个公式便正式写完了。
当然，如果用LOOKUP，公式可以简洁：=LOOKUP(ROW(A1),SUMIF(OFFSET($B$1,,,ROW($1:$5),),&&&&)+1,$C$2:$C$6)&&&
真是晕菜了，这一节，我发了N次，一直提示我有不良信息，我开始以为是上下其手，但最后发现……是上山打~老~虎。。好吧，爱~老~虎油，不让说。老~虎也不行。
唉，老~虎，老~虎，你炽热地发光, 照得夜晚的森林灿烂辉煌（可怜的布莱克）……
阅读权限100
在线时间 小时
本帖最后由 看见星光 于
12:41 编辑
第二节：VLOOKUP查询符合条件的多个结果。
通过第一节的内容，我们初步认识了VLOOKUP(ROW(A1),……)的技巧。这一节，我们需要利用这个技巧，回答开篇所提到的第一个问题。VLOOKUP能否查询符合条件的多个数值？就像经典数组套路INDEX+SMALL+IF那样？
3.jpg (45.77 KB, 下载次数: 3761)
11:25 上传
如上图，我们需要提取C列符合F1班级的姓名，放入E4:E15。
通常我们会使用INDEX+SMALL+IF的数组套路：E4=INDEX(C:C,SMALL(IF($B$1:$B$15=F$1,ROW($1:$15),4^8),ROW(A1)))&&&
如果使用VLOOKUP，我们应该怎么做？其实也简单。我们还是如第一节那般，先采用辅助列的方式。A2=COUNTIF(B$2:B2,F$1)向下填充。E4=IFERROR(VLOOKUP(ROW(A1),A:C,3,0),&&)向下填充。
结果……如下：
4.jpg (48.12 KB, 下载次数: 3603)
11:26 上传
这里，咱们依然利用了VLOOKUP(ROW(A1)……)的技巧。第一个公式：=COUNTIF(B$2:B2,F$1)我们使用COUNTIF函数，配合相对引用的原理，统计班级的累计重复次数。第二个公式：=IFERROR(VLOOKUP(ROW(A1),A:C,3,0),&&)我们通过VLOOKUP查询ROW(a1)（1，2，3，4，5，上山打老……），来返回与之相对应的C列姓名结果，最后外套IFERROR函数，屏蔽VLOOKUP查询不到结果而返回的错误值，使之返回空白。
在数据量大时，我们使用INDEX+SMALL数组查询数据，难免卡机，此时不妨使用VLOOKUP+辅助列的方式，当然，辅助列我们不能再使用低效函数COUNTIF了，我们可以使用这样的公式：=(B2=$F$1)+A1（感谢老师指正错误之处哈）
==========我是往事如烟的分割线==========
理解了辅助列的意义，加深了VLOOKUP(ROW(A1),……)技巧的理解，我们下面要做的，依然是丢掉辅助列，把辅助列的内容，放到公式中，直接使用一个公式得出结果。
我们依然可以使用OFFSET对COUNTIF的统计范围进行多维引用，比如：=COUNTIF(OFFSET(B$2,,,ROW($1:$14)),F$1)这个公式的意思，是使用COUNTIF对B2:B2,B2:B3,B2:B4……直至B2:B15的范围内，分别统计F1数值的重复次数，得出来的结果，自然是和辅助列是一致的。
我们将这一段公式，放入VLOOKUP函数公式中：=IFERROR(VLOOKUP(ROW(A1),IF({1,0},COUNTIF(OFFSET($B$2,,,ROW($1:$14)),$F$1),$C$2:$C$15),2,0),&&)如此，这个公式也便正式写完了。
==========我是如烟往事的分割线==========
当然，如果您确实了解透彻了VLOOKUP的心，关于VLOOKUP查询符合条件的多个数值，我们其实也可以写成这样：
=IFERROR(VLOOKUP($F$1,OFFSET($B$1:$C$1,SMALL(IF($B$1:$B$15=$F$1,ROW($1:$15)-1),ROW(1:1)),,15),2,0),&&)或者这样：=IFERROR(VLOOKUP($F$1,INDIRECT(&b&&SMALL(IF($B$1:$B$15=$F$1,ROW($1:$15)),ROW(1:1))&&:c15&),2,0),&&)
我们结合第二个函数套路来稍微解释下此中过程。
SMALL(IF($B$1:$B$15=$F$1,ROW($1:$15)),ROW(1:1))IF函数判断B1:B15的值，是否等于F1，并返回相对应的行数序号ROW(1:15)，或者FALSE。(为什么将IF的假值留白，而不是像许多学友那样习惯性的输入4^8之类？因为这里没有必要撒，逻辑值天生就比数值大不是……)SMALL函数，按IF函数的结果，在公式下拉的过程中，依次从小到大取数，即ROW(1:1),ROW(2:2)，取得最小值，第二小值……。
INDIRECT函数，搭配SMALL所取得的结果，完成对VLOOKUP查找范围从大到小的限定。比如此例中的INDIRECT(“B”&13&”:C15”),INDIRECT(“B”&14&”:C15”)……。由于VLOOKUP天生只取首个匹配结果，所以咱们通过查找范围的精确限定，便可以使它依次取得所有符合条件的结果……
最后外套IFERROR函数，屏蔽错误值，使之返回空白。......
好啦，现在，咱们可以很清楚的知道，关于VLOOKUP无法提取符合条件多个数值的说法，是不正确的。呵呵。（我每次发呵呵，都会想起胡剑么么哒，唉）
耶…COUNTIF不是易失性函數，然後MAX還是遍歷，對於運算效率的幫助應該不會很大&
阅读权限100
在线时间 小时
本帖最后由 看见星光 于
12:54 编辑
第三节：VLOOKUP条件求和以及T/N+IF{1}技巧建立内存数组的一个应用小例。
这一节，我们来回答开篇所提到的第二个以及第三个问题：VLOOKUP能否进行条件求和？就像SUMIF那样？VLOOKUP第一个参数能否支持数组引用？
如下图，有这样一道题，需要在E1，求出A列存在的D3:D6班级的成绩之和。
5.jpg (34.4 KB, 下载次数: 3623)
11:31 上传
解这道题的方法有很多种，我们通常使用SUMIF：数组：=SUM(SUMIF(A1:B5,D4:D6,B1))或者：数组：=SUM((A2:A5=TRANSPOSE(D4:D6))*B2:B5)
如果用VLOOKUP，又怎么做呢？我们可以写成这样：E1=SUM(VLOOKUP(T(IF({1},D4:D6)),A:B,2,0))这个公式不需要按数组三键。
我们来简单了解下这个公式。重点是VLOOKUP的查找值，T(IF({1},D4:D6))。我们知道D4:D6，是需要进行查找统计的班级名称，那么为什么要在其外套T和IF函数？或者，我们反过来想，为什么不套T和IF函数，VLOOKUP就只对查询范围的第一个数值（金庸班）进行查询呢？我们可以这么简单的理解。T/N+IF组合，是让VLOOKUP函数的第一参数，接受数组形式，因此返回相应的内存数组。如此，VLOOKUP方能对每一个查找值进行查询统计。具体解释参见小翟斑竹的贴子：
如果为了避免错误值的问题，比如D4:D6出现了查询范围不曾出现的班级名称：天仙班，公式可以修改为：数组：=SUM(IFERROR(VLOOKUP(T(IF({1},D4:D6)),A:B,2,0),0))
=========我是温暖恰春的分割线=========
6.jpg (33.16 KB, 下载次数: 3641)
11:32 上传
再看一道题。如上图，对A列存在的D4:D6的班级进行求和，班级重复的只计算一次，答案是305。
我们通常使用这样的数组公式：=SUM(SUMIFS(B:B,A:A,D4:D6)/COUNTIF(A:A,D4:D6))或者：=SUM(N(INDIRECT(&b&&MATCH(D4:D6,A1:A9,))))
其实我们也可以使用VLOOKUP：=SUM(VLOOKUP(T(IF({1},D4:D6)),A:B,2,0))依然由于VLOOKUP天生就只取首个匹配的结果的缘故，所以咱们也就不需要对重复数据进行二次处理。如果要屏蔽错误值，依然要增加IFERROR：数组=SUM(IFERROR(VLOOKUP(T(IF({1},D4:D6)),A:B,2,0),0))
=========我是恰春温暖的分割线=========
综合以上两个问题，咱们不难发现，在条件求和方面，VLOOKUP和SUMIF还是有所不同的。如果未加以处理，VLOOKUP只对第一次出现的数据进行计算，这是它的短处，当然，未必不是它的长处。如果未加以处理，SUMIF会对所有数据进行求和，不论重复与否，这是它的长处，当然，未必不是它的短处。
=========我只是分割线=========
T/N+IF{1}技巧建立内存数组的一个应用小例
QQ图片02.jpg (38.68 KB, 下载次数: 3473)
22:30 上传
如图，判定D列姓名的相对累计重复次数（中文名和英文名如果是同一个人的名字，则同样视为重复）
这道题如果用辅助列，会很简单。直接把名字统一转换为中文或者英文，再使用COUNTIF进行重复次数计算。比如E2公式：=IFERROR(VLOOKUP(D2,A:B,2,0),D2)，下拉后将名字统一更换为中文；再使用公式=COUNTIF($E$2:E2,E2),下拉后便可得出正确结果。
但如果不用辅助列呢？
如果我们继续之前的解题思路，将查询的名字，统一更换为中文或者英文，再进行重复次数的计算，我们依然可以使用VLOOKUP函数。
比如公式：F2=SUM(N(IFERROR(VLOOKUP(T(IF({1},$D$2:D2)),A:B,2,0),$D$2:D2)=IF(CODE(D2)&91,VLOOKUP(D2,A:B,2,),D2)))
IFERROR(VLOOKUP(T(IF({1},$D$2:D2)),A:B,2,0),$D$2:D2)，是在公式下拉的过程中，将第二行到公式所在行的D列姓名，统一转换为中文，并以可以计算的内存数组的形式保存相关值。IF(CODE(D2)&91,VLOOKUP(D2,A:B,2,),D2),是将D列需要判定重复次数的值，统一转化为中文。SUM(N……)是统计第一个公式的内存数组值等于第二个公式返回值的次数，即相关名字的重复次数。.....
不说了，慢慢读
不明白T+IF
阅读权限100
在线时间 小时
本帖最后由 看见星光 于
15:20 编辑
第四节：VLOOKUP在字符串提取中的使用小例。
我们依然用题来说事哈。
下面这道题，我们需要提取A列单元格内第一个数值，结果如B列。
7.jpg (29.2 KB, 下载次数: 3540)
11:37 上传
我们通常使用数组公式：B2=MID(A2,MATCH(1=1,ISNUMBER(-MID(A2,ROW($1:$99),1)),),1)上面这个公式，通过ISNUMBER和MID组合，来判断单元格内每一个字符是否是数值，再通过MATCH函数，对首个数值的位置进行定位，最后通过MID函数来取值。
如果我们用VLOOKUP来处理呢？我们可以写成这样：数组：=VLOOKUP(,MID(A2,ROW($1:$99),1)*{0,1},2,)这个公式，依然利用MID函数，把单元格内的字符拆成个体，分别乘以0和1，如此则产生两列数据，一列由MID(A2,ROW($1:$99),1)*0得来，另外一列由MID(A2,ROW($1:$99),1)*1得来。我们知道文本*0，是错误值，数值*0，结果为0。于是当我们利用VLOOKUP，查找第一列的0值，得出来的结果，便是首个0值所对应的数值——即我们所需要的结果。
这个技巧，并不仅仅局限于提取首个数字的使用，比如一个稍微复杂的示例：
1.jpg (43.19 KB, 下载次数: 3680)
10:55 上传
如上图。数据区域是一些数据，有的人名后有电话号码，有的人名后没有电话号码，现在要求把没有电话号码的人名增补电话号码，增补的电话号码从哪来呢？向下数，从距离最近的拥有电话号码的人名那儿来，（关系再远，比如表大爷，毕竟也是一家人不是？）
结果如C列。
我们可以使用这样的公式：
=B3&IF(COUNT(--MID(B3,ROW($1:$52),11)),,VLOOKUP(,MID(PHONETIC(B3:$B$36),ROW($1:$201),11)*{0,1},2,0))
IF(COUNT(--MID(B3,ROW($1:$52),11))，是判断单元格内是否有电话号码。
VLOOKUP(,MID(PHONETIC(B3:$B$36),ROW($1:$201),11)*{0,1},2,0))
PHONETIC函数，将数据区域捏合为一个数据，MID函数，从中提取手机号码，最后通过VLOOKUP(,数据*{0,1},2,)的技巧，将MID的提取结果，分别乘以0和1，如此前所言，文本乘0，为错误值，数值乘0，结果为0，最后通过VLOOKUP来取得首个匹配结果，便是距离最近的手机号码。最后有B3黏合提取的电话号码。
后记：这篇帖子，只是分享思路和技巧，并不是建议每类问题用vlookup去解决。术业有专攻，每个函数，均有长处和短处，而且，数据应该适应函数，而不是函数来适应数据，不管什么时候，数据录入的规范性，都是最重要的哈。
再后记：第一次写这类分享文，从早上9点钟动笔时的信心满满，到中午11.30草草结束时的垂头丧气，这中间的过程，真他妈的苦。如果不是忌惮旁边MM的心理承受能力，俺真想砸桌子骂脏话。妹的，俺果然还是适合讲故事，不适合玩技术分析……嗯，只希望这篇破烂东西，可以开拓大家对于VLOOKUP函数的视野，拓展下思维方式，嗯，祝安。。。
读你千遍也不厌倦❀❀❀
阅读权限30
在线时间 小时
前排旺位招租！！！
多谢楼主，先顶再看！！
阅读权限95
在线时间 小时
看见星光 发表于
第四节：VLOOKUP在字符串提取中的使用小例。
我们依然用题来说事哈。
下面这道题，我们需要提取A列单元格 ...
用LOOKUP可以写成=LOOKUP(10,--MID(A1,ROW($1:$100),1))复制代码
如果是提取单元格中第一个出现的数字的话，这个函数是不准确的。&
阅读权限100
在线时间 小时
本帖最后由 看见星光 于
16:57 编辑
满坛皆为吾师 发表于
用LOOKUP可以写成
LOOKUP应该是最强大的函数之一，不管是数组运转还是技巧方面，VLOOKUP能做的，它通常也能做到，而且很多时候都是更简洁。不过我们最先接触到的通常是VLOOKUP……我有时候觉得先学了LOOKUP的人，总比先学VLOOKUP的函数思维方式要前进几步。。
可惜我花昨天花没了，稍后补你撒，首回有奖。
阅读权限95
在线时间 小时
看见星光 发表于
LOOKUP应该是最强大的函数之一，不管是数组运转还是技巧方面，VLOOKUP能做的，它通常也能做到，而且很多时 ...
星光大侠,Vlookup是我最喜欢的函数,没有之一...除了sum以外这是我接触的第二个函数,感触深刻.
函数没有喜不喜欢，只有合不合用&
阅读权限20
在线时间 小时
谢谢分享。
玩命加载中，请稍候
玩命加载中，请稍候
Powered by
本论坛言论纯属发表者个人意见，任何违反国家相关法律的言论，本站将协助国家相关部门追究发言者责任！ & & 本站特聘法律顾问：徐怀玉律师 李志群律师函数 | Excel中SUMIF函数的10个高级用法！超级经典~ - 简书
函数 | Excel中SUMIF函数的10个高级用法！超级经典~
SUMIF函数可以对范围中符合指定条件的值求和，该函数拥有十分强大的条件求和功能，在工作中有极其广泛的应用，在之前的文章中，罂粟姐姐也给大家介绍了该函数的一些经典应用场景。但是，今天，罂粟姐姐将带大家深入了解SUMIF函数，因为它不仅有漂亮的外表，还有丰富的内涵。（当然，最主要原因是之前有小伙伴表示没看懂，这一次再看不懂就……就……就给姐姐发红包，手把手教！）1、单字段单条件求和计算“杨过”总销售额，公式：SUMIF(D2:D16,"杨过",E2:E16)
单字段单条件求和
2、单字段多条件求和计算“杨过”和“杨逍”两个人的总销售额，共有两种方法。方法1：借助SUMIF进行数学运算，杨过总销售额+杨逍总销售额。公式：SUMIF(D2:D16,"杨过",E2:E16)+SUMIF(D2:D16,"杨逍",E2:E16)
方法2：借助SUM和SUMIF实现单字段多条件求和，SUM（杨过销售额，杨逍销售额）。公式：SUM(SUMIF(D2:D16,{"杨过","杨逍"},E2:E16))
注意：SUMIF(D2:D16,{"杨过","杨逍"},E2:E16)是一个数组公式，指代的结果是{杨过销售额,杨逍销售额}，再用SUM公式求和，即可得到杨过与杨逍的总销售额。3、包含日期的单字段多条件求和计算8月20日至9月20日总销售额，有两种方法。方法1：借助SUM和SUMIF实现单字段多条件求和，SUM（8月20日以后的销售额，9月20日之前的销售额）。公式：SUM(SUMIF(A2:A16,{"&=","&"},E2:E16)*{1,-1})。相当于=SUMIF(A2:A16,"&=",E2:E16)-SUMIF(A2:A16,"&",E2:E16)，即8月20日至9月20日的销售额=8月20日以上的销售额-9月20日以后的销售额。
方法2：借助SUMIFS实现多条件求和，同时满足8月20日以后和9月20日之前两个条件的销售额。公式：SUMIFS(E2:E16,A2:A16,"&=",A2:A16,"&=")
sumifs多条件求和
注意：SUMIFS函数是 Excel 2007及以后版本的新增函数，不能在 Excel 2003 中使用，但是可以使用SUMIF函数的普通公式来实现（如方法1）。4、模糊条件求和SUMIF语法中criteria参数中支持使用通配符(包括问号“?”和星号“*”)。关于通配符的介绍，请移步姐姐之前的文章：。计算姓“杨”销售员的总销售额，公式：SUMIF(D2:D16,"杨*",E2:E16)。
模糊条件求和
5、多列区域求和计算各月借方和贷方合计。公式：SUMIF($B$2:$I$2,J$2,$B3:$I3)。
多列区域求和
注意：选中单元格，按F4，则单元格绝对引用，再按一次F4，则单元格相对引用锁定行，再按一次F4，则单元格相对引用锁定列，尽量避免一个个输入“$”符号。6、错列求和杨过的销售总额。公式：SUMIF(A1:C16,"杨过",B1:D16)。
注意：查找条件为销售员“杨过”，条件区域为销售员，求和区域比查找区域往右偏移一列，意思是统计销售员右一列的数据，即各销售员的销售额。7、错行求和计算销售员总销售额。方法1公式：SUMIF(A1:A8,A2,B1:B8)，与基本用法一致。公式2公式：SUMIF(B1:B8,"*",B2:B9)。
注意：查找条件为“*”，说明查找的是0个或多个字符，求和区域比查找区域往下偏移一行，意思是统计0个或多个字符下一行单元格的数据，即为各销售员的销售额。8、查找引用一说到查找引用，可能很多小伙伴会说，查找引用不是应该是VLOOKUP函数或者INDEX+MATCH函数吗？关SUMIF函数什么事儿呢？它只是一个求和函数而已。的确，在绝大多数时候，查找引用不需要SUMIF，但是当求和区域符合条件的数值只有一个时，求和得到的结果就是数值本身，因此可以借助SUMIF来实现查找引用。现需匹配郑州、长沙、武汉、合肥4个省会城市的销售量、单价、销售额、利润额。公式：SUMIF($A$2:$A$16,$G2,B$2:E$16)。
注意：当原始数据表中有两个及以上郑州、长沙等省会城市名称时，销售量、单价、销售额、利润额等结果均为求和以后的结果。9、排除错误值求和计算全国销售总量。由于销售量中有不同类型的错误值，所以不能用SUM直接求和，可以使用SUMIF函数实现排除错误值求和。公式：SUMIF(B2:B16,"&9e307",B2:B16)
排除错误值求和
注意：9e07是科学记数法表示的9*10^307，是接近Excel允许键入的最大数值9.99E+307的一个数。在这里表示对小于最大值9e07的数据进行求和，也就是对“数值”单元格进行求和。使用9e07不是规定，不是原则，是大家约定俗成的用法。当然结合案例，也可以使用“&500”或者“&1000”等任意大于最大值253的值。
替换9e07结果仍然保持不变
10、跨表条件求和在一文中姐姐介绍过跨表求和，较为基础的求和方式，今天再给大家介绍如何在跨表的基础上还能满足一定的条件进行求和。
如果只有1月这一个表，则合计公式为：SUMIF('1月'!$A:$A,$A2,'1月'!$B:$B)
实现跨表条件求和需要借助INDIRECT函数（对文本描述的单元格引用，也就是说INDIRECT的括号里的参数是一个字符串（文本）描述的文本形式，INDIRECT取得这个引用。）最终公式为：=SUMPRODUCT(SUMIF(INDIRECT(ROW($1:$3)&"月!$A:$A"),$A2,INDIRECT(ROW($1:$3)&"月!$B:$B")))
跨表求和公式分析
好啦，SUMIF函数的10大经典用法就介绍完了，教程写的好辛苦啊！写得这么详细，有些知识百度都出来，希望能够对大家有用，更重要的是为大家提供一种解决问题的思路。记得刚开始学函数的时候，有个朋友跟我说：Excel函数很讲究逻辑的，一般女孩子逻辑差，学不好。可是姐姐我偏不信，义无反顾的踏上了这条不归路，希望能够得到大家的鼓励，给姐姐继续走下去的力量。
人人都知道Excel重要，可是却不是人人都“熟练”使用Excel，罂粟姐姐这本书完全以实践应用为指导，围绕案例层层展开；循序渐进，以数据分析的6大步骤为主线，每一个知识点都是精华；除了Excel数据分析外，还涉及SPSS案例分析、统计分析图表、统计分析报告等相关知识，全面覆盖，重点突出；并且站在职场“老人”带“新人”的角度把数据分析各个环节的注意事项解释得清清楚楚，通俗易懂。
之前罂粟姐姐推过一篇文章Excel中SUMIF函数的10个高级用法！超级经典~，有小伙伴回复说：“还是SUMPRODUCT函数好用”。 的确，刚入门学习EXCEL的时候，最先接触到的条件统计函数就是SUMIF、SUMIFS、COUNTIF、COUNTIFS等，非常简单，容易...
我们首先看到的是1级竞技场，也就是最初级的水平。在这个层级当中的需求呢，通常是一些最基本的统计需求，例如求和、统计个数、求平均值、最大值、最小值等等；有时候还需要在统计的基础上对结果的精度做一些调整，例如四舍五入并保留几位小数，或者直接保留整数结果等等。 那么在这里需要用...
上次给大家分享了《2017年最全的excel函数大全（2）——web函数》，这次分享给大家查找和引用函数（上）。 ADDRESS 函数 含义 你可以使用 ADDRESS 函数，根据指定行号和列号获得工作表中的某个单元格的地址。例如，ADDRESS(2,3) 返回 $C$2。...
Excel中，sumif函数算是一个非常实用、也非常强大的条件求和函数，运用好它，可以帮助我们解决非常多的统计问题。然而，这个函数近些年来有一种逐渐被淘汰取代的趋势。因为从Excel2007开始，微软新增了sumifs函数，而且经过这些年的发展，新增的sumifs函数越来越...
今天把会计常用的Excel公式进行一次整理，共16个，希望对做会计工作的同学们有用。 1、完成率公式 如下图所示，要求根据B的实际和C列的预算数，计算完成率。 E2: =IF(C3&0,2-B3/C3,B3/C3) 2、同比增长率公式 如下图所示，B列是本年累计，C列是去年...
第十二章 吕君心和章一泓 吕冰心被裴家铭吕君心送进家附近医院时，已近午夜。急诊没有骨科，医生做了清创处理并简单固定吕冰心的手臂，配了止痛片让她一早到骨科就诊。 凌晨四点左右，吕冰心醒过来睡意全无，翻来覆去倒把身旁的妹妹吵醒了。 “手臂疼得厉害？”吕君心关心地问。 “有一点。...
站在30岁的人生边上，有些许莫名的惶恐，时而觉得岁月荏苒、少年已老，时而觉得一无所有、一事无成。 最近刚好看了一部关于路遥先生的纪录片，怀着对路遥先生的崇敬与好奇，读了这本据说影响了一代代年轻人的路遥先生的《人生》。原以为该书是讲述某个大小人物或曲折或辉煌的一生，不想竟是一...
今天有人来给我们量了多高，以后就能穿新校服啦，真高兴
本实践之路是根据《财富自由之路》所倡导的方法所设计。该体系按照top down的方式逐一设计和打磨。 之前财务自由之路分享（1,、2、3、4）文章涵盖了理论篇，接下来我们将进入到实践篇。举一目而纲目并举，所以首先要解决的是价值观的理解。“获得内心平静的秘密，藏于我们对核心价...
内存泄漏问题一直是项目开发中的一大问题，本文力求帮助从事过一段时间工作的iOS开发者快速寻找App中的内存泄漏问题。部分内容比较基础，大神可无视勿喷。 一、从AFNet说起 对于iOS开发者，网络请求类AFNetWorking是再熟悉不过了，对于AFNetWorking的使...}