先看数据源，是12个月的销售记录，分别存放在不同的工作表里。
现在，咱们要汇总出每个客户在各个月份的业务金额。
接下来咱们以Excel 2016为例，说说具体的操作步骤：
依次单击【数据】→【获取数据】→【自文件】→【从工作簿】，找到存放数据的文件，双击导入。
按下图步骤加载到数据查询编辑器。
单击Data列，右键→删除其他列，然后按下图步骤展开数据。
将日期转换为月份名称，删除暂时用不到的订单号所在列。
单击日期所在列，单击【转换】选项卡下的【透视列】，值列选择“金额”，最后将文件上载到工作表中，OK了。
此方法一劳永逸，如果数据源发生了变化，只要保存一下，然后在汇总表中右键刷新，就可以获取最新的汇总结果了。
今天给大家分享一个很常用也很实用的函数：SUMPRODUCT。众所周知，条件求和与计数是表格使用者最经常碰到的两个问题，而该函数不但集合了条件求和与计数两大功能于一身；还可用于复杂场景下的排名处理，甚至听说有人靠它一个函数就打下了Excel半壁江山……于是便不可不学了。
先来看基础语法，SUMPRODUCT的官方语法说明是在给定的几组数组（array）中，将数组间对应的元素相乘，并返回乘积之和。语法格式如下：
=SUMPRODUCT（array1,array2,array3, …）
——SUM是求和的意思，PRODUCT是相乘的意思，参数之间相乘之后再求和，你看，SUMPRODUCT确实人如其名了。
看我手，歪、图、斯瑞……总结起来，SUMPRODUCT函数具有以下三个特点：
1> 它本身默认执行数组运算。
2> 它会将参数中非数值型的数组元素作为0处理。
3> 参数必须有相同的尺寸，否则返回错误值。
看完了SUMPRODUCT的简历，想必很多朋友是雾里看花，仅仅对它有个模糊的认知，它的这些特点是啥意思？它到底能够胜任什么样子的工作？其实并不了然。
打个响指，我举几个例子。
如上图所示的数据表，C列是商品单价，D列是销售数量，现在需要在C9单元格计算销售总额。
C9输入以下公式，即可得出结果11620.60
=SUMPRODUCT(C3:C7, D3:D7)
这便是一个简单的SUMPRODUCT函数了。它的运算过程是：C3:C7和D3:D7两个区域数组内的元素分别相乘，也就是C3*D3,C4*D4,C5*D5……直至C7*D7
等于先将每个商品的销售金额计算出来，最后汇总求和。
由于SUMPRODUCT函数第一个特点，本身是支持数组间运算的，所以虽然该公式执行了多项运算，但并不需要按数组三键<Ctrl+Shift+Enter>结束公式输入。
有的朋友说啦，公式也可以写成这样：
=SUMPRODUCT(C3:C7*D3:D7)
或者使用以下数组公式，也是可以的。
=SUM(C3:C7*D3:D7)
那么这三个公式之间有什么区别呢？
首先，大部分情况下，SUMPRODUCT函数都不需要数组三键结束公式输入即可执行数组运算，而SUM函数是需要的。
其次，就要说到SUMPRODUCT函数另一个非常重要的特点了。
……
我们将上面的表稍做改动，将“钢笔”的销售数量更改为：暂未统计。同样需要在C9单元格计算销售总额。
这时候，如果使用公式：
=SUMPRODUCT(C3:C7*D3:D7)
或者数组公式：
=SUM(C3:C7*D3:D7)
都将返回错误值#VALUE!
返回错误值的原因在于D4单元格“暂未统计”为文本值，文本值是无法直接参与数学运算的，于是C4*D4返回错误值#VALUE!，进而造成整个公式的结果返回错误值。
而使用以下公式就没有这方面的困扰，会直接返回正确结果：
=SUMPRODUCT(C3:C7,D3:D7)
这便是SUMPRODUCT函数的第二个特点：将非数值型的数组元素作为0处理。
以该示例来说，D4单元格的值“暂未统计”为文本，并非数值，SUMPRODUCT将其主动视为零，于是C4*D4，结果亦为零，其余数组元素照常计算，得出11385.60的结果。
需要特别说明的是，SUMPRODUCT将非数值型的数组元素作为0处理，所谓的非数值型数组元素，包含逻辑值、文本，但并不包含错误值，如果数组元素中包含错误值，该公式亦返回错误值，比如该示例的第一条公式。
……
说完了SUMPRODUCT函数的两个特点，我们就再来聊聊它的第三个特点：数组参数必须有相同的尺寸，否则返回错误值。
我们依然用上述图片的例题为例，继续计算商品的销售总额。如果我们在C9输入公式：
=SUMPRODUCT(C3:C7,D3:D6)
结果会是怎么样的呢？
错误值：#VALUE!
为什么？
细心的你肯定已经注意到了，两个区域数组，C3:C7明显显比D3:D6多了一个元素，C3和D3结对子，C4和D4结对子……那么C7和谁结对子呢？女人们都嫁了，结果剩下一个光棍，这日子没法过了！一个萝卜一个坑，只有萝卜没有坑，这不是要萝卜死吗？
——于是SUMPRODUCT就不高兴了，它给你一个错误值#VALUE!，明确告诉你，和谐时代幸福岁月，日子不能这么过。
这就是SUMPRODUCT函数的第三个特点：数组参数必须有相同的尺寸，否则返回错误值。
下面是一道练习题，你看看，能用SUMPRODUCT函数做出来吗？
假设下面这张图，是某个公司工资发放的部分记录表（数据纯属虚拟，如有雷同，那是穿越）。A列是工资发放的时间，B列是员工所属的部门，C列是员工姓名，D列是相关员工领取的工资金额。
——那么，问题和广告都来了：
1
员工西门庆领取了几次工资？
这是一个单条件计数的问题,通常我们使用COUNTIF函数,但如果使用SUMPRODUCT函数，一般写成这样：
=SUMPRODUCT((C2:C13="西门庆")*1)
先判断C2:C13的值是否等于”西门庆”，相等则返回TRUE，不等则返回FALSE，由此建立一个有逻辑值构成的内存数组。
上文已经说过，SUMPRODUCT有一个特性，它会将非数值型的数组元素作为0处理，逻辑值自然是属于非数值型的数组元素，为了避免SUMPRODUCT函数把逻辑值视为0，造成统计错误，我们使用*1的方式，把逻辑值转化为数值，TRUE转化为1，FALSE转化为0，最后统计求和。
2
员工西门庆领取了多少工资？
这是一个单条件求和的问题,通常我们使用SUMIF函数,如果使用SUMPRODUCT函数，我们可以写成这样：
=SUMPRODUCT((C2:C13="西门庆")*D2:D13)
依然首先判断C2:C13的值是否等于”西门庆”，得到逻辑值FALSE或TRUE，再和D2:D13的值对应相乘。TRUE乘以数值，得到数值本身。FALSE乘以数值返回0。最后统计求和得出结果。
看完了上面两个问题，有些朋友可能会在心里想，貌似SUMPRODUCT能干的事，SUMIF和COUNTIF也能做到，而且做的更好，那么还要SUMPRODUCT干啥嘞？
乡亲们呐，话不能这么说，SUMPRODUCT可是上得厅堂下得厨房，对工作环境不挑不拣，它对参数类型没有啥特别要求，COUNTIF和SUMIF就不同了，他俩要求个别参数，必须是区域（Range型），不支持数组，比如下面这两个问题，COUNTIF和SUMIF就要绕了。
3
二月份外交部发放了几次工资？总额是多少？
第1个问题，二月份外交部发放了几次工资？
这是一个多条件计数的问题。
第一个条件，发放工资的时间必须是二月份；第二个条件，发放工资的部门必须是外交部。
如果使用多条件计数函数COUNTIFS，判断发放工资的时间是否属于六月份，会简单问题复杂化。而使用SUMPRODUCT函数，咱们可以把公式写成这样：
=SUMPRODUCT((MONTH(A2:A13)=2)*(B2:B13="外交部"))
……
第2个问题，统计二月份外交部发放了多少工资？
这是一个常见的多条件求和问题。
如果使用SUMIFS函数，判断发放工资的时间是否属于六月份，也会简单问题复杂化。
SUMPRODUCT跃然而至：
=SUMPRODUCT((MONTH(A2:A13)=6)*(B2:B13="外交部"),D2:D13)
或者：
=SUMPRODUCT((MONTH(A2:A13)=6)*(B2:B13="外交部")*D2:D13)
打个响指，关于这两个形式的SUMPRODUCT函数的区别，咱们上文已有详细说明——你还记得吗？
上面这个公式可以说是SUMPRODUCT多条件求和的典型用法啦，可以归纳为:
=SUMPRODUCT((条件一)*(条件二)……,求和区域)
4
二月份外交部和步兵部合计发放了多少工资？
解决了上面的问题，相信大家已经晓得如何计算二月份外交部发放多少工资了，那么二月份外交部和步兵部合计发了多少工资，又当怎么计算呢
我们经常见有些性格朴素的表亲们把公式写成这样：
=SUMPRODUCT((MONTH(A2:A13)=2)*(B2:B13="外交部")*D2:D13)+SUMPRODUCT((MONTH(A2:A13)=2)*(B2:B13="步兵部")*D2:D13)
这些表亲们估计心想，不就是计算两个部门吗？甭说两个，二十个咱也能算，一个加一个，一直加到二十个，世上无难事，只怕有心人嘛，一砖加一砖，长城就建成了，一泡加一泡，长江就奔流了……
呃……公式写的那么长，先不谈计算速度，首先它累手啊，万一写错了，又要修改，那也是麻烦他妈哭麻烦——麻烦死了。
其实我们可以写成这样：
=SUMPRODUCT((MONTH(A2:A13)=6)*(B2:B13={"外交部","步兵部"})*D2:D13)
5
排名应用
认识了SUMPRODUCT函数在条件计数和求和方面的用法，最后，咱们再来看一个它在排名上的使用方法。
如上图所示，某个月某个公司某些人领了某些工资，然后呢，他们想看看自己的工资，在部门内的排名情况，比如说步兵部的鲁智深都是老员工了，非常想知道自个工资在各自部门排几号。
当然啦，不排不知道，一排就傻掉。
SUMPRODUCT是这么解决这个问题的，D2输入公式向下复制：
=SUMPRODUCT(($A$2:$A$9=A2)*(C2<$C$2:$C$9))+1
（思考，为什么公式的最后+1，而不是直接写成如下：）
=SUMPRODUCT(($A$2:$A$9=A2)*(C2<=$C$2:$C$9))
结束语
唠唠叨叨说了这么多，眼睛都说酸麻了，是到了该结束的时候啦。
最后，请思考两个小问题：
第1个问题：下面SUMPRODUCT函数有几个参数？
=SUMPRODUCT((MONTH(A2:A13)=6)*(B2:B13="财务部")*D2:D13)
下面这个SUMPRODUCT函数又有几个参数？
=SUMPRODUCT((MONTH(A2:A13)=6)*(B2:B13="财务部")，D2:D13)
第二个问题：
SUMPRODUCT为什么有时候比SUMIF/COUNTIF计算速度慢？
……
出处：https://mp.weixin.qq.com/s/0VZMQtCqN-Ez1FdR8i4J1g
分享一个数据查询有关的内容，先看数据源：
左侧是员工信息表，右侧是查询区域，希望在G4输入任意姓名或者任意部门，就能在右侧提取出符合条件的全部记录。
要实现这样的数据提取效果，其实很简单，接下来咱们就看看具体的操作步骤。
步骤1
在G4单元格输入要查询的内容，比如 销售部。
步骤2
在第一行数据的左侧，本例是A4单元格，输入以下公式，向下拖动：
=(C4=G$4)+(B4=G$4)+A3
公式是什么意思呢？
如果C4单元格中的部门等于G4要查询的部门，或者是B4单元格中的姓名等于G4单元格要查询的姓名，就在上一个单元格的基础上加上1，否则仍然是上一个单元格中的内容。
观察公式下拉后的效果，可以看到，只要C列的部门名称等于G4单元格中的部门，得到的结果正好是一串递增的序号1……2……3……
这些序号干嘛用呢？不着急，往下看。
步骤3
在I4单元格输入公式，向右向下复制，就可以得到查询结果了：
=IFERROR(VLOOKUP(ROW(A1),$A:$E,COLUMN(B1),0),"")
这个公式又是啥意思呢？
这里面的主角是VLOOKUP函数，要查询的内容是ROW(A1)，ROW的作用是返回参数所在的行号，这里得到的结果就是A1的行号——1，当公式向下复制时，会依次变成ROW(A2)、ROW(A3)……，结果就是从1开始的递增序号1、2、3……。
也就是说，在不同行中VLOOKUP函数的查找内容是不一样的，在第四行时，查找的内容是1，公式到了第五行，查询的内容就是2了。
再看看VLOOKUP函数查询的区域是哪里呢？$A:$E ，这个写法表示A~E列的整列区域，并且使用了绝对引用。
说到这里，有小伙伴们已经知道咱们前面用公式得到的那些序号是干嘛用的了，对，就是协助VLOOKUP查询用的。
前面的序号特点是每遇到一个符合条件的记录，序号就加上1，而VLOOKUP要查询的内容就是序号1、2、3……
再来看COLUMN(B1)是干啥用的呢？她的作用和ROW函数类似，是返回参数的列号的，COLUMN(B1)，得到的就是B1的列号2。公式向右复制时，会依次变成COLUMN(C1)、COLUMN(D1)……，结果就是从2开始的递增序号2、3、4……
得到的序号再给VLOOKUP函数当小三，不对不对，是当成第三参数，用来指定返回查询区域中哪一列的内容。
当公式在I列时，返回查询区域第二列的内容，公式向右复制到了J列，那就是返回查询区域第三列的内容了，以此类推。
小伙伴可能会说了，A列还有那么多重复的序号啊，没关系，因为VLOOKUP函数有个特点，就是如果有多个符合条件的记录，只会返回第一条。所以在I4单元格查询1的时候，得到序号1对应的姓名兰花，在I5单元格查询2的时候，得到序号2对应的姓名枣花……
最外面的这个IFERROR是干啥用的呢？ 她啊，是用来给VLOOKUP屏蔽错误值的。
因为VLOOKUP每一行中的查询序号都不一样，当公式一直下拉时，这个序号就会不断增加，当查询的序号在A列没有出现时，说明左侧记录中没有这么多符合条件的内容了，公式会返回错误值。所以，咱们使用IFERROR函数把错误值变成了空文本。
图片
练手文件在此，你也试试吧：
链接：https://www.aliyundrive.com/s/H3ZRUvxawRL
实例需求：数据保存在A列中，需要将其中的日期和金额分别提取至B列和C列。其中日期也两种不同格式，金额可能包含小数点、千分符和货币标识。
示例代码如下：
Sub RegExp_Date_Num()
Dim Res()
Dim objRegEx As Object
Dim objMH As Object
Dim j As Integer
Set objRegEx = CreateObject("vbscript.regexp")
objRegEx.Pattern = "(\d{4}-\d{2}-\d{2}|\d{4}.\d{2}.\d{2}).*?(([A-Z]{3})*\d+[\d.,]*元)"
objRegEx.Global = True
For i = 2 To Cells(Rows.Count, "A").End(xlUp).Row
form = Cells(i, "A")
Set objMH = objRegEx.Execute(form)
If objMH.Count > 0 Then
Cells(i, 2) = CStr(objMH(0).submatches(0))
Cells(i, 3) = CStr(objMH(0).submatches(1))
End If
Next
Set objRegEx = Nothing
Set objMH = Nothing
End Sub
【代码解析】
第6行代码使用后期绑定创建正则对象。
第7行代码指定正则匹配字符串，
如果直接使用[\d.,]+匹配金额中的数字，则会匹配到单个小数点或者逗号，例如数据为：旧的一年结束,元旦将要来临，此模式可以将匹配到,元。由此可见组织一个严谨的正则表达式需要考虑很多情况。
第8行代码设置为全局搜索模式。
第9行代码第16行代码循环处理工作表中的数据。
第11行代码执行正则匹配，返回结果为MatchCollection集合对象。
第13行和第14行代码分别将日期和金额写入工作表中
在Excel中制作倒计时牌，先来看效果：
在日常工作中，经常会有一些倒计时的应用，例如常见的距高考还有n天、距项目结束还有n天等。使用Excel中的日期函数结合按指定时间刷新的VBA代码，即可制作出倒计时牌。
步骤1 假设假期结束日期为2021年12月27日0时，在C2单元格输入以下公式，得到剩余的整数天数。
=INT("2021-12-27"-NOW())&"天"
步骤2 设置D2单元格自定义格式为：
hh小时mm分ss秒
然后在D2单元格中输入以下公式：
="2021-12-27"-NOW()
虽然NOW函数属于易失性函数，但是如果在工作表中没有执行能够引发重新计算的操作，公式结果并不能自动实时刷新，因此需要添加定时刷新的VBA代码。
步骤3 按<Alt+F11>组合键打开VBE编辑器，依次单击【插入】→【模块】命令，在【工程资源管理器】中单击选中刚刚插入的“模块1”，在右侧的代码窗口中输入以下代码。
Sub Macro1()
Application.OnTime Now + TimeValue("00:00:01"),"Macro1"
Calculate
End Sub
Private Sub workbook_open()
Macro1
End Sub
代码中的”00:00:01″，表示刷新时间为1秒，实际使用时可根据需要设置。例如要设置刷新时间为1分钟，可将此部分修改为”00:01:00″。
步骤4 在【工程资源管理器】中单击选中“ThisWorkbook”，在右侧的代码窗口中输入以下代码，然后按F5键，即可在单元格中实现倒计时效果。
Private Sub workbook_open()
Call Macro1
End Sub
最后将文件保存为Excel启用宏的工作簿，即xlsm格式。
再次打开文件时，如果出现如图 所示的安全警告，记得要点击【启用内容】按钮哦。
制作完成之后，你就可以打开Excel，看着时间一点点的减少，心里对某个人的思念却越来越强烈……
今天和大家一起分享一个图表制作的技巧，先来看看效果：
这个图表里，其实有三个数据系列，分别是一深一浅两个颜色的条形图，再就是大大的圆圈儿，其实是用散点图模拟出来的。
先来看数据源：
各车间不同月份的优质率指标：
首先建立一个下拉菜单。
单击H2，【数据】 →【数据验证】→ 设置【序列】来源为B21:F21单元格区域的车间名称。
接下来要使用公式来构建图表数据源了。
如下图所示，B2公式为：
=HLOOKUP(H$2,$21:$33,ROW(A2),0)
也就是根据H2单元格中的部门，从21至33行这个范围中，返回不同月份的指标数据。
B列的数据，在图表中就是深色的条形图数据系列。
在C2输入以下公式，得到浅色的条形图数据系列：
=1-B2
辅助列的意思其实就是把产品指标看作是1的话，去掉优质率部分，剩余的那些了。
接下来就是要构建散点图的x轴和y轴数据了。
x轴，咱们先用等式等于B列的优质率。
再用0.5 1.5 2.5……这样递增的数据得到y轴数据。
y轴数据的尾数设置成0.5，生成图表后，散点图的位置能恰好位于各个条形的中间。
选中A1:C13单元格区域，插入堆积条形图。
单击图例项，按Delete键删除。然后双击横坐标轴标签，设置最大值为1。
下面的步骤是添加散点图的数据系列。
右键单击图表，【选择数据】，在【选择数据源】对话框中单击【添加】按钮，在【编辑数据系列】对话框中输入系列名称，例如“标签”。
再添加散点图的数据系列。
右键单击条形图，【更改系列图表类型】，在弹出的对话框中将“标签”系列的图表类型设置为散点图。
接下来给散点图选择数据来源。
右键单击图表→【选择数据】，在【选择数据源】对话框中选中【标签】系列，再单击【编辑】按钮，在【编辑数据系列】对话框中，选择x轴系列值为D2:D13区域的数据，y轴选择E2:E13区域的数据。
双击图表右侧的次要垂直轴标签，设置最大值为12。
从【设置坐标轴格式】窗格底部，设置标签位置为“无”
此时的图表已经有了大致的效果，但是默认的黄蓝配色实在不咋样：
对图表进行简单的美化。单击选中蓝色的系列，设置填充颜色：
同样的方法，再选中黄色的系列，设置填充颜色，可以设置成和前一个系列相同色系，只是稍淡一些，这样看起来比较顺眼。
现在的条形图看起来很瘦弱，粗壮一些会更好看。
散点系列也要再大一些。
单击选中散点系列，分别设置标记类型、填充颜色以及边框颜色和线型。
右键单击散点系列，添加数据标签。
右键单击数据标签，设置数据标签格式。
标签选择“单元格中的值”，然后选择D列的x轴数据。
设置标签位置为“居中”。
此时由于标签的字体颜色是黑色的，和散点图的大圆圈儿在一起看不清了。
咱们要设置一下标签的字体和颜色。
在I2单元格输入公式，生成一个动态更新的图表标题。
=H2&”上年度产品优质率”
单击图表标题，编辑栏中输入等号，再单击I2单元格，按回车。
至此，咱们的图表就制作完成了，一起来看看效果吧：
有小伙伴可能注意到了，条形图中的月份和实际的数据源中的顺序是相反的，如何设置成顺序一样呢？
今天的练手文件在此：
https://pan.baidu.com/s/1KqVjN3i_cPWW-EESKg7ObQ
提取码: v3yd
先来看一个表格的展示效果。
这样查看数据是不是非常方便啊，炫酷又方便哦。
有好多小伙伴肯定还不知道这是怎么设置的，其实这个就是久负盛名的分类汇总功能，今天咱们就学习一下。
在日常的工作中，常常需要对数据进行快速分类，并以一定的方式来汇总项目。
而分类汇总这个功能就能够帮助我们实现这样的要求。
比如下面的一个数据：
这样的数据源算是十分规整的，而我们的要求仅仅是把这个数据，按照级别汇总各级别的人数，同时还要对补贴项目求和。
对分类汇总有所了解的小伙伴可能已经迫不及待了：
全选数据区域， 点开【数据】【分类汇总】~
STOP！
别急别急，分类汇总最重要的一步【排序】还没有做哦。
对，就是排序，分类的参照级别如果不经过排序，直接分类汇总的后果就乱套了：
这样的结果，显然不符合日常工作中的要求。~
首先要对需要汇总的参照列【级别】进行排序，排序处理后，相同类别的内容都会集合到一起。
然后再进行分类汇总，就妥妥的了。
注意这里勾选了上【级别】，目的是为了计算每个级别的人数~
点击【确定】按钮 即可初步完成了。
点击汇总单元格，里面有自动生成的汇总公式。
每个级别的补贴等内容都完成了求和汇总，但是我们看B列的每个级别的计数还是显示为0的。
这里额外插一下关于SUBTOTAL这个函数的一些小说明，更多内容看官们自行脑补哦~
相信这个时候，有些看官已经知道了， SUBTOTAL第一参数使用3就是计数，如果第一参数选择9，就是求和计算了。
怎么把计算级别个数的公式批量修改为计数呢？可以这样操作~
选中【级别】所在的B列，按Ctrl + H，调出替换对话框。
点击左侧的加号或是减号或者是左上角的数字，就可以分级显示啦。
AGGREGATE函数用法与SUBTOTAL函数类似，但在功能上比SUBTOTAL函数更加强大，不仅可以实现诸如SUM、AVERAGE、COUNT、LARGE、MAX等19个函数的功能，而且还可以忽略隐藏行、错误值、空值等，并且支持常量数组。
该函数的第一参数是1到19之间的数字，用于指定要使用的汇总方式：
第二参数是介于0到7之间的数字，指定在计算区域内要忽略哪些类型的值：
接下来咱们就说说这个函数的一些典型用法：
这个函数的强大之处就是在于2参可以指定参数来忽略错误值直接统计
如下图，蓝色区域中包含有不同的错误值，现在要对这几个不连续的区域求和。
公式为：
=AGGREGATE(9,6,A3:A7,C3:C4,D6:F7)
如下图，在筛选后的数据区域中包含有错误值，如何对可见单元格进行统计呢？
公式为：
=AGGREGATE(9,7,B6:B18)
第一参数使用9，表示求和，第二参数使用7，表示忽略隐藏行和错误值。
如下图，A3:B14单元格区域中是筛选后的的数据，要分别统计在可见区域和所有数据的最大、最小、平均、总和、计数和中位数。
只要一个公式就够了：
=AGGREGATE({4;5;1;9;3;12},{5,0},B4:B14)
注意是区域数组公式，先选取c17:d22区域，然后在编辑栏写上公式，最后按ctrl+shift+enter三键录入。
=AGGREGATE(9,3,A4:A$18)*2-AGGREGATE(9,7,A4:A$18)
除了向下求和的方向外还有隐藏和错误值，这是subtotal+sum(if)都无法实现的统计效果
(录入方法是选取区域定位空值后编辑栏写完公式ctrl+enter批量填充)
这个函数提早五年就实现了2016才有的maxifs和minifs函数的统计效果，而且不需要三键。
如下图，要计算1车间对应的最小值，公式为：
=AGGREGATE(15,6,B4:B15/(A4:A15="1车间"),1)
公式中的第一参数使用15，表示使用SMALL函数，第二参数使用6，表示忽略错误值。要统计的区域是B4:B15/(A4:A15=”1车间”)
A4:A15=”1车间”部分，先对比A列的车间是不是等于指定的条件。如果A4:A15单元格区域中等于”1车间”，就返回逻辑值TRUE，否则返回逻辑值FALSE。然后再用B4:B15除以这组内存数组，结果为：
{70;69;87;77;55;46;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;19;47}
最后，AGGREGATE函数忽略里面的错误值，得到第一个最小值。
如果要计算1车间对应的第三个最小值，只需要将最后的1，变成3就好了。
如果要计算1车间对应的最大值，咱们可以修改一下第一参数，使用14，就是第k个最大值了。
如果想要一对多查询，很多人想到的是INDEX+SAMLL+IF函数的三键客组合。其实，用aggregate函数替代也是能实现的。
如下图，要提取出二车间的所有工号，可以使用以下公式：
=IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW($3:$12)/(A$3:A$12=D$3),ROW(A1))),"")
这个公式的思路和第五个公式基本相同。
如下图，B列多人的考核情况被写到同一个单元格内，要统计其中的最大值。公式为：
=AGGREGATE(14,6,--MID(B4,ROW($1:$50),COLUMN(A:AZ)),1)
公式中的MID(B4,ROW($1:$50),COLUMN(A:AZ))部分，使用MID函数，依次从第1~50个字符处开始，各提取长度为1~50的字符串，得到一个巨长的内存数组。再使用两个负号，把内存数组中的文本变成错误值，数值仍然是其本身的值。
最后使用AGGREGATE函数，忽略内存数组中的错误值，计算出其中的第一个最小值。
如下图所示，要同时统计1车间对应的最大和最小值。
先同时选中F4:G4单元格，编辑栏输入以下公式，按Ctrl+Shift+回车。
=AGGREGATE({16,15},6,B4:B15/(A4:A15=E4),1)
AGGREGATE第一参数使用常量数组{16,15}，表示分别使用最大值和最小值的计算规则。
最终的结果也是一个内存数组，所以要同时选中两个单元格输入。
这个函数的特性在于第一参数为14~19时，可以使用第四参数，此时的第四参数是支持数组的，因此就能玩出各种应用，来替代不能直接忽略错误值的SMALL、LARGE等函数。
今天的练习文件在此：
链接: https://pan.baidu.com/s/1hOJgByy-IdALhGoHBNLgRw
提取码: mu5n
下面这个Word表格是一份电子版的员工信息表，要求每个员工据实填写：
这个表格中包含有姓名、性别、籍贯、学历以及政治面貌等等必填项。
姓名、性别、籍贯、学历这几项在填写时不会有啥问题，二逼青年也知道自己的姓名和性别的。在政治面貌这一栏中就会出问题了，填写结果往往是五花八门。
既然是电子版的信息表，咱们可以给他设定一个输入的下拉选项，避免出现什么贫农、地主、反动派这样的信息：
单击需要填写政治面貌的单元格，【开发工具】【下拉列表内容控件】
接下来就要设置控件属性了：
然后添加需要在下拉列表中显示的内容：
OK，完工～～
单击单元格就会出现“请在下拉列表中选择一项”的提示，再单击单元格右侧的按钮，就可以选择内容了。
光说不练假把式，动手试试吧。
咋了，你家Word上找不到【开发工具】？
不急，在这里：
用数据有效性，能够帮助我们对输入的内容进行检测，不符合要求的数据不允许录入。今天老祝就和大家再分享几个数据有效性（也叫数据验证）的实用技巧，点滴积累，也能提高效率。
如下图所示，是某人的三胎育儿计划表，从幼儿园到结婚计划预算180万元，要求各分项预算之和不能超过总预算。
选中B2:B7单元格区域，数据→数据验证→自定义，输入以下公式。
=SUM($B$2:$B$7)<=$D$2
设置完成后，B列各分项之和超过D2单元格的预算，就会弹出错误提示。
如下图所示，是某地三胎意愿调查表，D列的配偶姓名填写时，要求C列的婚否一项中必须为“是”，否则禁止录入。
选中D2:D6单元格区域，数据→数据验证→自定义，输入以下公式。
=C2=”是”
如下图所示，是某人的工作计划表，B列的拟定日期填写时，要求不能录入周末日期。
选中B2:B6单元格区域，数据→数据验证→自定义，输入以下公式。
=WEEKDAY(B2,2)<6
WEEKDAY(B2,2) ，根据B2单元格的日期，返回对应的星期。第二参数使用2，用数字1~7来表示周一到周日。WEEKDAY(B2,2)<6，就是限定录入日期小于周六了。
如下图所示，要根据A列的对照表，在D列生成下拉菜单，要求能随着A列数据的增减，下拉菜单中的内容也会自动调整。
选中要输入内容的D2:D10单元格区域，数据→数据验证→序列，输入以下公式。
=OFFSET($A$2,0,0,COUNTA($A:$A)-1)
公式表示以A2作为基点，向下偏移0行，向右偏移0列，新引用的行数为COUNTA函数统计到的A列非空单元格个数，结果-1，是因为A1是表头，计数要去掉。
这样就是A列有多少个非空单元格，下拉菜单中就显示多少行。
如下图所示，A、B列是客户城市和县区的对照表，在D列已经生成一级下拉菜单，要求在E列生成二级下拉菜单，要求能随着D列所选不同的一级菜单，E列下拉菜单中的内容也会自动调整。
选中要输入内容的E2:E6单元格区域，数据→数据验证→序列，输入以下公式。
=OFFSET($B$1,MATCH($D2,$A$2:$A$16,0),0,COUNTIF($A:$A,$D2))
公式表示以B1为基点，以MATCH函数得到的城市首次出现的位置作为向下偏移的行数。
向右偏移的列数为0。
新引用的行数为COUNTIF($A:$A,$D2)的计算结果。
COUNTIF($A:$A,$D2)的作用是，根据D列以及菜单中的城市名在A列统计有多少个与之相同的城市个数。有多少个城市名，OFFSET函数就引用多少行。
ExcelHome
