第11关 查询函数
你好，很开心又见到你。通过第10关统计函数的学习，你已经基本掌握统计函数。
今天我们将学习查询函数。学会查询函数，你会发现自己之前的操作有多么慢！而学会之后竟然会那么快！
在日常工作中，我们经常需要查找一些信息，比如员工的身份证号、银行卡号、还有本季度业绩。数据少还好说，当数据很多，一条条找非常费时间，而且很累人。
这时候，Excel中的查找效率之王——vlookup函数就派上用场了。只要是查找、匹配的需求，就找它！搞定vlookup函数，可以说日常的都可以搞定！
请下载案例，我们先体验一下查询函数的快速查找功能吧：
这是一张员工工资的统计表，我们现在需要根据已有的数据核算出每个人的年终奖以及查询出王丽丽的岗位名称、岗位工资、月薪和年终奖：
先来计算年终奖，复制以下函数直接粘贴至【G2】单元格：
=lookup(F2,$J$2:$J$6,$K$2:$K$6)*E2
千万别一个个去找！因为写个查询函数，可以立马搞定！
向下填充，年终奖就都计算出来了，紧接着我们来查找王丽丽的岗位名称、岗位工资、月薪和年终奖，复制以下函数直接粘贴至【J9】单元格：
=index($A$1:$G$39,match($I9,$A$1:$A$39,0),match(J$8,$A$1:$G$1,0))
向右填充，王丽丽的岗位名称、岗位工资、月薪和年终奖就都自动生成了：
有没有觉得查询函数很神奇？下面，我们就来系统地学习查询函数：
1.初识查询函数
查找函数顾名思义就是根据单元格的设置、地址信息等，进行数据查找与引用，返回对应的结果。
接下来，我们就从vlookup开始重点学习查找函数的基本用法。
在工作中但凡涉及需要通过【查找】才能实现的功能，今天学习的查询函数就能为你排忧解难！
“vlookup”
vlookup函数是Excel应用中出现频率最高的函数，没有之一。它的功能就是按列进行查找，最终返回该列所需查询序列所对应的值。它的含义和格式如下：
小tips：vlookup（找谁，在哪找，第几列，0）中的最后1个参数“0”代表【精确查找】，我们本小结都是精确查找，所以格式默认写的是0。
打开下载的附表【2-vlookup函数】，我们来套用vlookup函数计算出大家的【岗位工资】：
右侧是不同岗位对应的岗位工资表，我们需要根据B列每个人不同的岗位名称，匹配出相应的【岗位工资】，函数拆解图如下：
【第一步】 首先我们写上vlookup函数的基本式，注意在英文状态下写出：
=vlookup()。
下面，我们通过4个步骤，带大家写出你的第1个vlookup函数：
=vlookup(B2,$H$2:$I$5,2,0)
【第二步】然后找到张海燕的【岗位名称】对应的单元格【B2】。到现在写出：
=vlookup(B2,)
【第三步】紧接着我们再找到查找的区域【岗位工资表】都框选出来【H2:I5】，因为这个被查询的区域是不变的，所以需要按F4键锁定该区域，写出的公式：
=vlookup(B2,$H$2:$I$5,)
【第四步】 这里我们需要把【I】列的值返回到【D】列，注意，这里的列数是针对上一步选中的【岗位工资表】而言的，所以第几列应该为2，同时，我们是精确查找，所以最后写0。最终的函数公式为
=vlookup(B2,$H$2:$I$5,2,0)
写好公式后，同步下拉，员工的岗位工资就都算出来啦！接下来我们用【入门函数课】学习的【sum函数】，来算出【总工资】。你做出如下的效果了么？
（小tips：总工资=基本工资+岗位工资）
vlookup函数运用范围广，但是书写过程较为繁琐，所以同学在进行书写的时候一定要先理清楚vlookup函数的4个参数，然后再书写函数。还有一点需要注意的是，查找的值在查找区域里必须是唯一的，否则查找的结果会出错。
“hlookup”
说完vlookup函数，我们来说一说hlookup。hlookup和vlookup的功能是一样，只不过知查找方向不同。vlookup是按【列】进行搜索，而hlookup是按【行】进行搜索。它的含义和格式如下：
打开【3-hlookup函数】，我们来套用hlookup函数计算出大家的【岗位工资】，函数拆解图如下：
【第一步】 我们写出hlookup函数的基本式：=hlookup()
【第二步】然后找到张海燕的【岗位名称】对应的单元格：=hlookup(B2,)
【第三步】 找到查找的区域【岗位工资表】都框选出来【H1:L2】，并按F4键锁定该区域：=hlookup(B2,$H$1:$L$2,)
【第四步】 找到对应的行数为第2行，最后补充精确查找0：
=hlookup(B2,$H$1:$L$2,2,0)
我们可以观察到，与刚才的【2-vlookup函数】相比，唯一的不同就是岗位工资表变成了横向的。我们套用刚才的书写顺序，写出如下公式：
=hlookup(B2,$H$1:$L$2,2,0)
书写完成同步下拉，员工的岗位工资就都算出来啦！同样的我们就可以用【sum函数】算出总工资了。你是否做出了如下效果？
大部分时候，我们可以把横向表格转换成竖向表格，再用vlookup函数查找。转置操作：复制表格-选择性粘贴-转置。如下图：
vlookup按列查找，hlookup按行查找，他们都需要【精确查找】匹配值，否则就会报错。而lookup函数可以对一个区间内的数值进行【模糊匹配】。我们可以简化理解为：
“lookup”
怎么理解呢？我们直接点开【4-lookup函数】表格，结合案例同步学习使用lookup函数：
这个公司的年终奖是根据每个人的【年度考核评分】进行【分等级】发放的。也就是说：考核分数在0-59这个阶段的人将拿到0.5个月月薪的年终奖，60-69分的人将拿到1个月的月薪年终奖，以此类推。（小tips：年终奖=月薪x年终奖发放系数）
我们注意到，在年终奖发放系数参考表里，有一个【起步分数】，这里的数值正好是对应【分数】段的最小值。这个【起步分数值】就是lookup函数的对应的参数【起步线区域】。
前面我们提到了lookup函数可以对一个区间内的数值进行模糊匹配。要达到这个效果，它所参考的就是对应区间的最小值，也就是这个题目里的【起步分数】。lookup函数的拆解如下：
我们接下来通过3个步骤，写出如下公式：
=lookup(D2,$I$2:$I$6,$J$2:$J$6)
【第一步】 写好基本的格式，然后找到lookup函数的目标数值：
=lookup(D2)
【第二步】然后填写上锁定好的【起步分数】区域：=lookup(D2,$I$2:$I$6,)
【第三步】输入并锁定【对应结果区域】，也就是年终奖的发放系数：
=lookup(D2,$I$2:$I$6,$J$2:$J$6)
书写完成后向下填充，员工的年终奖系数就都算出来啦！最后我们通过公式“年终奖=年终奖发放系数x月薪”，就可以算出每个人的年终奖啦，你是否完成如下效果？
“match”
lookup函数三兄弟让我们可以对特定的行、列、以及固定范围进行有效查找，那如果我们想要知道一个特定的信息位于第几行第几列该怎么办呢？
其实match函数可以搞定，它的格式如下：
打开【5-match+index函数】表格，我们用match先来查找【王丽丽】所在的行，函数拆解图如下：
具体拆解步骤如下：
先看函数格式：=match(找谁，查找区域，0)
找谁？ 找的是【王丽丽】，所以选中【I2】
查找区域？ 我们要找的王丽丽就藏在【员工姓名】这一列，所以选中员工姓名列下的所有内容，并锁定【$A$1:$A$17】
输入好后向下填充，【查找值】下的思维员工所在【行】就都出来了！接下来，我们再来查找一下岗位名称、岗位工资、月薪和年终奖所在的列，函数拆解图如下：
具体拆解步骤如下：
=match(找谁，查找区域，0)
找谁？ 找的是【岗位名称】，所以选中【I8】
查找区域？ 我们要找的【岗位名称】就藏【第1行】，然后我们选中第一行的所有内容，并锁定【$A$1:$F$1】
输入好后下拉，就完成了！你得出的结果和老师的一样吗？快来对照看看吧：
“index”
这里我们再介绍一种常见的查询函数——index函数。作为前面四种函数的补充。index函数最大的功能就是根据指定的行数和列数，返回指定区域的值。它的格式如下：
接着刚才的例子，我们来找出表格中【引用值】的内容，函数拆解图如下：
例如，=index(B2:E7,4,3)，意思是返回B2:E7区域内第4行第3列的值，也就是D5单元格中的值。
具体拆解步骤如下：
=index(在哪找，第几行，第几列)
在哪找？ 要引用的值就藏在左侧【工资表】里，所以我们选中并锁定左侧的工资表【($A$1:$F$17】
第几行？行数在引用值表格里给出来了，直接选中：【I14】
第几列？同样的，在给出的值中直接选中对应的：【J14】
输入完成，参照下图看看你输入的index函数有没有查找正确吧：
3.组合查询
要应对实际的工作需求，为了提高工作效率，在熟练掌握了五大查询函数的前提下，我们可以尝试把这些函数进行嵌套。常用的组合查询有2种方式：
在实际工作中，查询需求总是更为复杂的，就像我们前面的案例中，直接问“xxx在第几行第几列”这样的问题是极少出现的，更多的情况是直接问你“xxx今年拿到了多少年终奖啊？”这样结果性的问题。
打开【6-组合函数】表格，表格需要我们查找出四名员工的的【岗位名称】、【岗位工资】、【月薪】、【年终奖】。
“vlookup+match”
要查这些信息可以用vlookup函数，可是针对不同的查找条件我们需要写4个vlookup函数，而且里面涉及到的【列】数还要自己一个个去数，非常麻烦，而学会组合查询，我们可以用【vlookup+match】，一次搞定。
秘诀就在于：用match函数自动计算vlookup函数引用第几列：
如上图所示，我们用match函数自动计算vlookup函数引用第几列，=match(I1,$A$1:$F$1,0)，可是下拉却全部显示填充出错：
按照前面match的书写方式我们可以写出函数=match(I1,$A$1:$F$1,0)，可是下拉却全部显示填充出错：
这是为什么呢？因为向下填充时，行标变化了。这里只需要锁定【行】的锁定就可以了：=match(I$1,$A$1:$F$1,0)
接下来我们将写好的match函数公式作为第几列参数，嵌套到vlookup函数中，函数拆解图如下：
你写出来这个函数了么？
=vlookup($H2,$A:$F,MATCH(I$1,$A$1:$F$1,0),0)
快来对照一下输出的结果正不正确吧：
其实呢，要得出上面我们查询的信息，用index函数+match函数也可以完成，而且使用起来更加灵活！
“index+match”
首先我们用match定位值所在的行数和列数：
第几行 =match($H9,$A$1:$A$39,0)
第几列 =match(I$8,$A$1:$F$1,0)
然后，用index函数引用上面match函数查找出来的值：
=index($A$1:$F$39,match($H9,$A$1:$A$39,0),match(I$8,$A$1:$F$1,0))
书写完成，向下向右填充，结果出现，你做出来了吗？
恭喜你已经掌握了【查询函数】的书写方法，别看最后我们输出的函数公式很长很复杂，只要我们牢牢掌握查询函数，然后根据需求一步步书写，就都能书写出来！
下面，我们对今天所学做一个总结。
首先，我们认识了查询函数的分类以及应用：
然后，我们练习使用了5个基础基础函数，其中vlookup最为常用：
最后，我们突破了相对有难度的组合查询，让你学会灵活动态查询：
恭喜你完成了本关的学习，学到这里，你掌握了常用的查询函数，最重要的是，你完成了一个巨大的突破，可以快速查询目标内容，实现质的飞跃，这将稳步提升你的工作效率！
但是，老师要提醒你，在工作中，要记得调用vlookup函数哦，不断实战，才能真正掌握！
4.下关预告
下一关我们将学习的是12关-文本函数，搞定它，以后处理文本类的问题都不是事！
下载表格
第11关-11-50套教师必备excel模板
