经常是不是因为记不住公式或者筛选条件太多而没办法做，今天我们来学习下Excel的高级筛选，帮您搞定所有您想筛选的内容

一、提取满足条件的部分数值：

例如上例，我们想直接筛选提取出地区是广州的所有人员的姓名和工龄：首先我们可以新建一个地方放置我们想提取的内容，列表区域选择我们原始数据的所有区域，条件区域就选择我们所要提取的条件即可，需要注意的是新的表格的表头需要和原始的表头一致，操作如下：

二、提取包含某值

例如地区有河南新乡、河南郑州，我们需要筛选只要包含河南的所有数值，操作如下：

三、多条件筛选，很多情况下，我们需要筛选满足多个条件，如本例中筛选出广东所有合格的人员，操作如下：

四、并列筛选：筛选出地区是四川和广东的所有人员，操作如下

五、模糊筛选：筛选出只要带某个字或词的所有情况，例如本例中筛选出所有满足带有广字的人员

六、比较筛选：筛选出满足条件的两值相比A值大于B值的，如业绩大于任务的：

七、复合筛选：

八、条件筛选：

比如下面这个表格，有2016年和2017年的进货日期，只有数量和单价，没有各项目的小计。如果要筛选出2017年来项目形成一张报表来进行求和，直接使用SUMPRODUCT是不能成功的。有人说可以选择区域啊，那么如果筛选出2016的不是又要更改求和区域吗？其实EXCEL提供了一个处理筛选的函数。请看分析。

数据源示例

首先：利用SUBTOTAL参数102，对隐藏单元格进行计数。SUBTOTAL(102,OFFSET(A1,ROW(1:13),))，得到一个{0;0;0;0;0;0;1;1;1;1;1;1;0}的内存数组，从这里就可以看到，当所在行筛选隐藏后，函数返回结果为0，而显示的行则会返回1，然后就可以利用SUMPRODUCT一一对应乘积求和了。

完整公式：SUMPRODUCT(SUBTOTAL(102,OFFSET(A1,ROW(1:13),)),C2:C14,D2:D14)

效果示例

（辅助列是本例效果示例，并没有采用辅助列求和）

在HR同事电脑中，经常看到海量的Excel表格，员工基本信息、提成计算、考勤统计、合同管理....看来再完备的HR系统也取代不了Excel表格的作用。一周前，交给小助理木炭一个任务，尽可能多的收集HR工作中的Excel公式。进行了整理编排，于是有了这篇本平台史上最全HR的Excel公式+数据分析技巧集。

一、员工信息表公式

1、计算性别(F列)

=IF(MOD(MID(E3,17,1),2),"男","女")

2、出生年月(G列)

=TEXT(MID(E3,7,8),"0-00-00")

3、年龄公式(H列)

=DATEDIF(G3,TODAY(),"y")

4、退休日期 (I列)

=TEXT(EDATE(G3,12*(5*(F3="男")+55)),"yyyy/mm/dd aaaa")

5、籍贯(M列)

=VLOOKUP(LEFT(E3,6)*1,地址库!E:F,2,)

注：附带示例中有地址库代码表

6、社会工龄(T列)

=DATEDIF(S3,NOW(),"y")

7、公司工龄(W列)

=DATEDIF(V3,NOW(),"y")&"年"&DATEDIF(V3,NOW(),"ym")&"月"&DATEDIF(V3,NOW(),"md")&"天"

8、合同续签日期(Y列)

=DATE(YEAR(V3)+LEFTB(X3,2),MONTH(V3),DAY(V3))-1

9、合同到期日期(Z列)

=TEXT(EDATE(V3,LEFTB(X3,2)*12)-TODAY(),"[<0]过期0天;[<30]即将到期0天;还早")

10、工龄工资(AA列)

=MIN(700,DATEDIF($V3,NOW(),"y")*50)

11、生肖(AB列)

=MID("猴鸡狗猪鼠牛虎兔龙蛇马羊",MOD(MID(E3,7,4),12)+1,1)

二、员工考勤表公式

1、本月工作日天数(AG列)

=NETWORKDAYS(B$5,DATE(YEAR(N$4),MONTH(N$4)+1,),)

2、调休天数公式(AI列)

=COUNTIF(B9:AE9,"调")

3、扣钱公式(AO列)

婚丧扣10块，病假扣20元，事假扣30元，矿工扣50元

=SUM((B9:AE9={"事";"旷";"病";"丧";"婚"})*{30;50;20;10;10})

四、员工数据分析公式

1、本科学历人数

=COUNTIF(D:D,"本科")

2、办公室本科学历人数

=COUNTIFS(A:A,"办公室",D:D,"本科")

3、30~40岁总人数

=COUNTIFS(F:F,">=30",F:F,"<40")

五、其他公式

1、提成比率计算

=VLOOKUP(B3,$C$12:$E$21,3)

2、个人所得税计算

假如A2中是应税工资，则计算个税公式为：

=5*MAX(A2*{0.6,2,4,5,6,7,9}%-{21,91,251,376,761,1346,3016},)

3、工资条公式

=CHOOSE(MOD(ROW(A3),3)+1,工资数据源!A$1,OFFSET(工资数据源!A$1,INT(ROW(A3)/3),,),"")

注：

A3:标题行的行数+2,如果标题行在第3行，则A3改为A5

工资数据源!A$1:工资表的标题行的第一列位置

4、Countif函数统计身份证号码出错的解决方法

由于Excel中数字只能识别15位内的，在Countif统计时也只会统计前15位，所以很容易出错。不过只需要用 &"*" 转换为文本型即可正确统计。

=Countif(A:A,A2&"*")

六、利用数据透视表完成数据分析

1、各部门人数占比

统计每个部门占总人数的百分比

2、各个年龄段人数和占比

公司员工各个年龄段的人数和占比各是多少呢？

3、各个部门各年龄段占比

分部门统计本部门各个年龄段的占比情况

4、各部门学历统计

各部门大专、本科、硕士和博士各有多少人呢？

5、按年份统计各部门入职人数

每年各部门入职人数情况

附：HR工作中常用分析公式

1.【新进员工比率】＝已转正员工数/在职总人数

2.【补充员工比率】＝为离职缺口补充的人数/在职总人数

3.【离职率】（主动离职率/淘汰率＝离职人数/在职总人数=离职人数/(期初人数+录用人数)×100％

4.【异动率】＝异动人数/在职总人数

5.【人事费用率】＝（人均人工成本*总人数）/同期销售收入总数

6.【招聘达成率】=（报到人数+待报到人数）/（计划增补人数+临时增补人数）

7.【人员编制管控率】=每月编制人数/在职人数

8.【人员流动率】=（员工进入率+离职率）/2

9.【离职率】=离职人数/（（期初人数+期末人数）/2）

10.【员工进入率】=报到人数/期初人数

11.【关键人才流失率】=一定周期内流失的关键人才数/公司关键人才总数

12.【工资增加率】=（本期员工平均工资—上期员工平均工资）/上期员工平均工资

13.【人力资源培训完成率】=周期内人力资源培训次数/计划总次数

14.【部门员工出勤情况】=部门员工出勤人数/部门员工总数

15.【薪酬总量控制的有效性】=一定周期内实际发放的薪酬总额/计划预算总额

16.【人才引进完成率】=一定周期实际引进人才总数/计划引进人才总数

17.【录用比】=录用人数/应聘人数*100%

18.【员工增加率】 =（本期员工数—上期员工数）/上期员工数

【好东西记得分享哦！！！】

【好东西记得分享哦！！！】

【好东西记得分享哦！！！】

Excel中的自动筛选很多人都会用，单经常遇到多条件或复杂条件筛选时无从下手的情况，其实强大的Excel早就为你准备了N个贴心的高级筛选功能，只是你不知道而已……

一、两条件同时满足下的数据筛选。

目的：筛选业务员为“张明”且商品为“手机”的相关信息。

方法：

【数据】-【高级】，打开高级筛选对话框。选择方式：【在原有区域显示筛选结果】。选定【列表区域】和【条件区域】。【确定】。

备注：

需要提前准备好“条件”。当不需要筛选时可以单击【清除】命令来恢复数据。

二、两条件任意满足其一下的数据筛选。

目的：筛选业务员为“张明”或“文强”的相关信息。

方法：

【数据】-【高级】，打开高级筛选对话框。选择方式：【在原有区域显示筛选结果】。选定【列表区域】和【条件区域】。【确定】。

备注：

多条件筛选满足其中一条件时，将条件放在不同的行上即可。

三、按日期区间筛选数据。

目的：筛选12月13日至16日的相关数据。

方法：

【数据】-【高级】，打开高级筛选对话框。选择方式：【在原有区域显示筛选结果】。选定【列表区域】和【条件区域】。【确定】。

备注：

提前录入日期范围。

四、按数值区间筛选数据。

目的：筛选出金额大于9000小于30000的相关数据。

方法：

【数据】-【高级】，打开高级筛选对话框。选择方式：【在原有区域显示筛选结果】。选定【列表区域】和【条件区域】。【确定】。

备注：

提前录入金额范围。

五、按混合条件筛选数据。

所谓的混合条件，就是既有“且”关系又有“或”关系的复杂条件。比如你现在要筛选出业务员为“夏丽”和“周宗”所销售的“笔记本”和“手机”的相关数据。

方法：

【数据】-【高级】，打开高级筛选对话框。选择方式：【在原有区域显示筛选结果】。选定【列表区域】和【条件区域】。【确定】。

备注：

“且”关系的条件放在同一行上，“或”关系的条件放在不同行上即可。

六、按模糊条件筛选数据。

目的：筛选出业务员为“李”姓的相关数据。

方法：

【数据】-【高级】，打开高级筛选对话框。选择方式：【在原有区域显示筛选结果】。选定【列表区域】和【条件区域】。【确定】。

七、按精准条件完全匹配筛选。

目的：筛选出品牌为“小米”的相关记录。

一般方法：

从筛选的结果中我们可以看出不仅筛选除了品牌为“小米”的记录，还筛选出了“小米”相关的记录。但这并不是我们所需要的记录。那么我们就要用到精准筛选。

正确方法：

方法：

【数据】-【高级】，打开高级筛选对话框。选择方式：【在原有区域显示筛选结果】。选定【列表区域】和【条件区域】。【确定】。

备注：

我们可以与前面的条件区域相比，只是在“小米”的前面多了一个“=”（等号）。但筛选的结果截然不同。

八、按自定义条件筛选数据。

自定义条件，就是你想按什么规则就自己写相应的条件，只要你能想到的都可以，比如，我们筛选日销售额大于平均销售额的数据。

方法：

自定义条件：=G3>AVERAGE(G3:G10)【数据】-【高级】，打开高级筛选对话框。选择方式：【在原有区域显示筛选结果】。选定【列表区域】和【条件区域】。【确定】。

九、提取不重复记录。

方法：

【数据】-【高级】，打开高级筛选对话框。选择方式：【在原有区域显示筛选结果】。选定【列表区域】和【条件区域】。【确定】。

十、提取两表相同部分。

方法：

【数据】-【高级】，打开高级筛选对话框。选择方式：【在原有区域显示筛选结果】。选定【列表区域】和【条件区域】。【确定】。

Excel中的技巧非常的多，全部掌握的可能性不大，但是对于常用且实用的技巧，作为职场的我们必须掌握哦！

一、创建下拉菜单。

方法：

1、选定目标单元格。

2、【数据】-【数据验证】，选择【允许】中的【序列】、单击【来源】右侧的箭头，选择需要在下拉菜单中显示的内容，并单击箭头返回，【确定】。

解读：

1、此方法实用于内容较多的情况，如果内容较少，也可以在来源中输入，如：男,女。

2、输入内容时分隔符必须为英文逗号哦！

二、直接生成下拉菜单。

方法：

在目标单元格中按快捷键：Alt+↓（向下箭头）即可。

解读：

此方法使用于已经录入数据且新录入的数据与前面的数据重复的情况。

三、按单元格颜色筛选。

方法：

在目标单元格右键-【筛选】-【按所选单元格的颜色筛选】。

四、按单元格值快速筛选。

方法：

在目标单元格右键-【筛选】-【按所选单元格的值筛选】。

五、快速填充工作日。

方法：

1、拖动起始日期所在单元格的填充柄。

2、单击右下角的箭头，选择【填充工作日】。

六、输入1、2显示男、女。

方法：

1、选定目标单元格。

2、快捷键Ctrl+1打开【设置单元格格式】对话框，选择【分类】中的【自定义】，并在【类型】中输入：[=1]"男";[=2]"女";，【确定】。

3、输入数字1，显示男，输入数字2，显示女。

解读：

此处的“男、女”具有共性，可以用其他需要的字段替换哦！根据实际情况灵活应用即可。

结束语：

Excel中最常用的并不是高大上的函数，而是常用的技巧，所以希望通过对本文的学习，大家对常用技巧有所掌握，以此来提高工作效率哦！学习中遇到任何问题可以在留言区留言讨论哦！

最近重庆地区的天气简直热出天际！再加上烦人的数据分析，喝再多的降火茶，也难消心中怒火。今天帮帮给大家分享一个excel数据筛选技巧，为你带来一丝清凉（méi yǒu luǎn yòng）！

大家请看范例表格，我们要快速筛选出工资大于800，补贴大于等于500的人，应该怎么做？

我们选中G2单元格，输入公式=(C2>800)*(D2>=500)。大家注意G1单元格，这里的要求是不能与筛选数据区域“列标题”一样，空白都可以。

【如果大家喜欢帮帮，请点击文章末尾下方的推广广告，在里面找到并关注我，我给表亲们准备了更多更实用的办公软件技巧，期待您的到来】

找到数据选项卡中的排序和筛选，选择高级。在高级筛选中，列表区域和条件区域用复选快捷框分别选取，大家注意红色细线区域。

大家看，我们想要的结果直接显示出来了^^，是不是很简单呢？

同样的，我们利用列标题来书写函数，H1单元格留空。

重复刚才的操作，选取列表和条件区域。

大家看，留白和列标题的方式也能解决我们的问题。这里我再强调一个知识点，对于B列的部门，某些领导对分公司，部门有排序偏好的，怎么对文本进行排序？

进入EXCEL选项设置，找到高级选项中的常规，点击编辑自定义列表。

自定义序列设置中，选择添加，按照大家的需求编辑好顺序，用逗号隔开，点击确定。

然后在排序功能中，去掉标题，降序排列。

大家看，文本排序的方法就是这么简单。小伙伴们有没有感受到一点清凉呢^^

今天的教学就到这里拉，如果觉得今天的内容有帮到大家，请点点文末后面的推广广告，我给大家准备了好多实用的小技巧视频，期待你的到来。拜拜，下课。（吃瓜去了）

1、取绝对值

=ABS(数字)

2、取整

=INT(数字)

3、四舍五入

=ROUND(数字,小数位数)

二、判断公式

1、把公式产生的错误值显示为空

公式：C2

=IFERROR(A2/B2,"")

说明：如果是错误值则显示为空，否则正常显示。

2、IF多条件判断返回值

公式：C2

=IF(AND(A2<500,B2="未到期"),"补款","")

说明：两个条件同时成立用AND,任一个成立用OR函数。

三、统计公式

1、统计两个表格重复的内容

公式:B2

=COUNTIF(Sheet15!A:A,A2)

说明：如果返回值大于0说明在另一个表中存在，0则不存在。

2、统计不重复的总人数

公式：C2

=SUMPRODUCT(1/COUNTIF(A2:A8,A2:A8))

说明:用COUNTIF统计出每人的出现次数，用1除的方式把出现次数变成分母，然后相加。

四、求和公式

1、隔列求和

公式：H3

=SUMIF($A$2:$G$2,H$2,A3:G3)

或

=SUMPRODUCT((MOD(COLUMN(B3:G3),2)=0)*B3:G3)

说明：如果标题行没有规则用第2个公式

2、单条件求和

公式：F2

=SUMIF(A:A,E2,C:C)

说明：SUMIF函数的基本用法

3、单条件模糊求和

公式：详见下图

说明：如果需要进行模糊求和，就需要掌握通配符的使用，其中星号是表示任意多个字符，如"*A*"就表示a前和后有任意多个字符，即包含A。

4、多条件模糊求和

公式：C11

=SUMIFS(C2:C7,A2:A7,A11&"*",B2:B7,B11)

说明：在sumifs中可以使用通配符*

5、多表相同位置求和

公式：b2

=SUM(Sheet1:Sheet19!B2)

说明：在表中间删除或添加表后，公式结果会自动更新。

6、按日期和产品求和

公式：F2

=SUMPRODUCT((MONTH($A$2:$A$25)=F$1)*($B$2:$B$25=$E2)*$C$2:$C$25)

说明：SUMPRODUCT可以完成多条件求和

五、查找与引用公式

1、单条件查找公式

公式1：C11

=VLOOKUP(B11,B3:F7,4,FALSE)

说明：查找是VLOOKUP最擅长的，基本用法

2、双向查找公式

公式：

=INDEX(C3:H7,MATCH(B10,B3:B7,0),MATCH(C10,C2:H2,0))

说明：利用MATCH函数查找位置，用INDEX函数取值

3、查找最后一条符合条件的记录。

公式：详见下图

说明：0/(条件)可以把不符合条件的变成错误值，而lookup可以忽略错误值

4、多条件查找

公式:详见下图

说明:公式原理同上一个公式

5、指定区域最后一个非空值查找

公式;详见下图

说明：略

6、按数字区域间取对应的值

公式：详见下图

公式说明：VLOOKUP和LOOKUP函数都可以按区间取值，一定要注意，销售量列的数字一定要升序排列。

六、字符串处理公式

1、多单元格字符串合并

公式：c2

=PHONETIC(A2:A7)

说明：Phonetic函数只能对字符型内容合并，数字不可以。

2、截取除后3位之外的部分

公式：

=LEFT(D1,LEN(D1)-3)

说明：LEN计算出总长度,LEFT从左边截总长度-3个

3、截取-前的部分

公式:B2

=Left(A1,FIND("-",A1)-1)

说明：用FIND函数查找位置，用LEFT截取。

4、截取字符串中任一段的公式

公式:B1

=TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",20)),20,20))

说明:公式是利用强插N个空字符的方式进行截取

5、字符串查找

公式：B2

=IF(COUNT(FIND("河南",A2))=0,"否","是")

说明: FIND查找成功，返回字符的位置，否则返回错误值，而COUNT可以统计出数字的个数，这里可以用来判断查找是否成功。

6、字符串查找一对多

公式：B2

=IF(COUNT(FIND({"辽宁","黑龙江","吉林"},A2))=0,"其他","东北")

说明：设置FIND第一个参数为常量数组，用COUNT函数统计FIND查找结果

七、日期计算公式

1、两日期相隔的年、月、天数计算

A1是开始日期（2011-12-1），B1是结束日期(2013-6-10)。计算：

相隔多少天？=datedif(A1,B1,"d") 结果：557

相隔多少月? =datedif(A1,B1,"m") 结果：18

相隔多少年? =datedif(A1,B1,"Y") 结果：1

不考虑年相隔多少月？=datedif(A1,B1,"Ym") 结果：6

不考虑年相隔多少天？=datedif(A1,B1,"YD") 结果：192

不考虑年月相隔多少天？=datedif(A1,B1,"MD") 结果：9

datedif函数第3个参数说明：

"Y" 时间段中的整年数。

"M" 时间段中的整月数。

"D" 时间段中的天数。

"MD" 天数的差。忽略日期中的月和年。

"YM" 月数的差。忽略日期中的日和年。

"YD" 天数的差。忽略日期中的年。

2、扣除周末天数的工作日天数

公式：C2

=NETWORKDAYS.INTL(IF(B2

说明：返回两个日期之间的所有工作日数，使用参数指示哪些天是周末，以及有多少天是周末。周末和任何指定为假期的日期不被视为工作日

我们都知道excel函数公式很强大，运用好了对我们制表很有帮助，但是excel函数公式实在是太多了，根本记不住，下面跟大家分享一些常常会用到的函数公式。

一、对于数字的处理：

1、取绝对值

=ABS(数字)

2、取整

=INT(数字)

3、四舍五入

=ROUND(数字,小数位数)

二、统计公式：

1、统计两个表格重复的内容

公式:B2

=COUNTIF(Sheet15!A:A,A2)

说明：如果返回值大于0说明在另一个表中存在，0则不存在。

2、统计不重复的总人数

公式：C2

=SUMPRODUCT(1/COUNTIF(A2:A8,A2:A8))

说明:用COUNTIF统计出每人的出现次数，用1除的方式把出现次数变成分母，然后相加。

在完成excel表格统计之后，以免后期不小心修改数据，我们可以将excel表格转换成pdf格式。高版本的office可以直接将excel另存为pdf，低版本或者想要批量转换的可以用迅捷pdf转换器来完成转换。

三、求和公式

1、隔列求和

公式：H3

=SUMIF($A$2:$G$2,H$2,A3:G3)

或

=SUMPRODUCT((MOD(COLUMN(B3:G3),2)=0)*B3:G3)

说明：如果标题行没有规则用第2个公式

2、单条件求和

公式：F2

=SUMIF(A:A,E2,C:C)

说明：SUMIF函数的基本用法

3、单条件模糊求和

公式：详见下图

说明：如果需要进行模糊求和，就需要掌握通配符的使用，其中星号是表示任意多个字符，如"*A*"就表示a前和后有任意多个字符，即包含A

4、多条件模糊求和

公式：C11

=SUMIFS(C2:C7,A2:A7,A11&"*",B2:B7,B11)

说明：在sumifs中可以使用通配符*

5、多表相同位置求和

公式：b2

=SUM(Sheet1:Sheet19!B2)

说明：在表中间删除或添加表后，公式结果会自动更新。

6、按日期和产品求和

公式：F2

=SUMPRODUCT((MONTH($A$2:$A$25)=F$1)*($B$2:$B$25=$E2)*$C$2:$C$25)

说明：SUMPRODUCT可以完成多条件求和

好啦，以上就是比较常用的excel公式啦，当然，excel函数公式远远不止这些，但是以上公式如果能熟练应用也会非常提高工作效率哦。

Excel中数据的筛选是一项非常强大的功能，其常用的数据筛选功能有【自动筛选】和【高级筛选】，其功能也是非常的强大。今天我们主要来学习一下“自动筛选”功能。对于数据量非常庞大的表格有绝对性的实用性哦！

一、通过筛选排序。

方法：

1、选定标题行。

2、【数据】-【排序】。

3、单击需要排序的列标题右下角的箭头，选择【升序】或【降序】即可。

二、按颜色筛选。

方法：

1、选定标题行，【数据】-【筛选】。

2、单击任意标题行右下角的箭头-【按颜色筛选】并选取颜色。

三、包含指定值得筛选。

目的：筛选销售地区中有“海”的所有信息。

方法：

1、选定标题行，【数据】-【筛选】。

2、单击目标列的右下角箭头，在搜索框中输入关键字“海”并【确定】。

四、筛选以指定值开头的数据。

目的：筛选出姓“小”的所有销售员信息。

方法：

1、选定标题行，【数据】-【筛选】。

2、单击目标列的右下角箭头，在搜索框中输入关键字“小*”并【确定】。

解读：

关键字中的星号（*）为通配符，*可以匹配任意长度的字符。

五、筛选以指定值结尾的数据。

目的：筛选销售地区中以“海”结束的数据。

方法：

1、选定标题行，【数据】-【筛选】。

2、单击目标列的右下角箭头，在搜索框中输入关键字“*海”并【确定】。

六、筛选指定长度的数据。

目的：筛选销售地区中地名为2位数或3位数的信息。

方法：

1、选定标题行，【数据】-【筛选】。

2、单击目标列的右下角箭头，在搜索框中输入关键字“??”或“???”并【确定】。

解读：

关键字中的问号（？）为通配符，只能匹配一个字符。??就是两个字符，???就是三个字符。在录入关键字是问号必须为英文状态。

七、精准筛选。

目的：快速筛选第一季度销售额为66或67的所有数据。

方法：

1、选定标题行，【数据】-【筛选】。

2、单击目标列的右下角箭头，在搜索框中输入关键字“66”或“67”并【确定】。

八、范围查询。

目的：筛选第一季度销售额大于等于30小于等于60的销售记录。

方法：

1、选定标题行，【数据】-【筛选】。

2、单击目标列的右下角箭头，【数字筛选】-【介于】，输入范围值，并【确定】。

结束语：

经过学习，数据筛选没有问题了，但是如果查阅原来的数据了？除了【全选】之外，有么有其它的方法呢？欢迎大家在留言区留言讨论或发表自己的看法和观点哦！

小编有话说：有很多小伙伴告诉小编想学习万金油公式，今天就分享给大家啦！估计还有很多小伙伴不知道啥是万金油公式吧，其实就是INDEX-SMALL-IF-ROW啦，这个公式套路可以解决Excel一对多查找筛选等难题，今天给大家分享的只是其中一个，先来学一下吧，有兴趣的话，再继续给大家推送。

那么，这个公式又要怎么用呢？不妨先看看下面这个效果图：

这个例子就是一个典型的一对多查找，查找条件是部门，在数据源内每个部门对应的都是多个数据，万金油公式最主要的用途就是用来解决一对多查找等一些相对复杂的问题。上面动画中的公式为：

=IFERROR(INDEX($A$2:$D$21,SMALL(IF($C$2:$C$21=$F$2,ROW($1:$20),99),ROW(A1)),MATCH(F$3,$A$1:$D$1,0)),"")

看到这个公式，或许很多朋友都会惊叹：这么长的公式，看不懂哇！

今天就和大家一同破解这个看不懂但又很强悍的公式套路，耐心往下看哦……

上面这个公式一共用了六个函数：IFERROR、INDEX、SMALL、IF、ROW和MATCH，其中的IFERROR和MATCH是本例中辅助性的两个函数，其余的四个INDEX-SMALL-IF-ROW就是万金油公式啦。

因此我们先来学习这个核心部分的原理：

F4单元格的公式为：

=INDEX($A$2:$A$21,SMALL(IF($C$2:$C$21=$F$2,ROW($1:$20),99),ROW(A1)))

先从INDEX说起，这个函数基本功能是给出一个区域，然后根据对应的行列位置返回查找结果，上图中INDEX查找的数据区域就是姓名所在的区域$A$2:$A$21。

INDEX函数的基本结构是：INDEX（查找区域，第几行，第几列），如果区域是单行或者单列的话，后面两个参数可以省略一个。通俗点说，你拿着电影票去找座位，整个大厅的座位就是区域，第几排第几座就是公式中的后面两个参数，通过这种方式可以准确找到目标位置。

在上面这个例子里，区域是在一列，所以我们只需要确定每个数据在第几行就行。

明白这一点的话，我们的重点就该放到INDEX的第二个参数了：

SMALL(IF($C$2:$C$21=$F$2,ROW($1:$20),99),ROW(A1))

注意看上面这个图，销售部一共有四条记录，分别在数据区域的第5、8、9和16行（数据区域是从第二行开始）。

因此我们希望公式下拉的时候，INDEX的第二个参数分别是5、8、9和16这四个数字（这一点一定要想明白）。

注意，接下来我们即将接触到万金油最核心的部分，请保持高度集中的注意力……

SMALL函数的基本结构：SMALL（一组数，第几小的数）

建议自己模拟个简单的数据来充分理解这个函数，方法如下：

在A列输入一些数字，公式的意思是这列数字中最小的一个，结果是2，很好理解对不对，将公式的第二个参数改成2，再看看结果：

倒数第二小的是4。

如果希望继续得到第三小的数，该怎么做我想大家都能想到，但是会有个问题，我们只能手动修改第二参数，并不能通过下拉来实现这个参数的变化，如果要想可以下拉的话，第二参数就需要用到ROW函数，也就是这样修改：

ROW函数非常简单，得到的就是参数的行号，通过这个公式，我们就把A列的数据从小到大排了个序，觉得有意思吗？

回到我们的万金油公式，5、8、9和16这四个数字代表什么意思还记得吧，我们需要用SMALL函数依次得到这四个数字，思路是通过判断C列是否与F2一致，如果一样得到行号，如果不一样，就得到一个比最大行号还大的数字（目的是为了防止被查找到）：

要实现这个目的，就需要IF函数的介入，于是就有了：

IF($C$2:$C$21=$F$2,ROW($1:$20),99)，用这一段来作为SMALL的第一个参数。

关于这段IF，就比较容易理解了，我们可以借助F9来看看这段公式的结果：

因为我们的数据就20个，所以IF的第三个参数使用99就足够了，如果数据量比较大的话，可以用9^9，表示9的9次方，反正足够大就行。

搞清楚这个IF的话，再来看这段SMALL(IF($C$2:$C$21=$F$2,ROW($1:$20),99),ROW(A1))是不是就没那么晕了。

关于SMALL这部分，一定要明白是随着公式下拉的时候，逐个得到我们希望得到的那几个数字，然后用这些数字作为INDEX的第二参数，就可以得到最终需要的结果。

万金油的核心就是INDEX、SMALL、IF和ROW，请大家务必反复琢磨，把这部分原理搞清楚。还有非常重要的一点需要强调，万金油公式是一个数组公式，因此需要我们按着Ctrl和shift再回车。

至于一开始的公式，考虑到要查找多列的内容，所以INDEX的数据区域用的$A$2:$D$21，多列的时候，就需要提供列位置才能找到目标值，因此用MATCH(F$3,$A$1:$D$1,0)来确定数据在第几列。

每个部门的数据都不一样多，我们需要将公式多向下拉几行，这时候就会产生一些错误值，在公式的最外层使用IFERROR函数屏蔽了错误值，使得查询结果看起来非常干净。

今天只是使用了一对多查找这样一个例子来解释万金油公式的原理，实际上万金油的套路还有很多，大家喜欢的话以后继续分享相关的实例，当然，如果看完本文的话能够自己去解读一些复杂的公式就更好了。

＊＊＊＊部落窝教育－excel万金油函数公式解读＊＊＊＊

原创：老菜鸟/部落窝教育（未经同意，请勿转载）