一篇文章带你全面掌握Excel中的各种数据查询知识与技巧
数据查询是Excel数据处理中的一项核心业务，也是日常办公中使用频率非常高的一项操作。数据查询业务需求多，而且具有较强的技巧性，因此它也是职场必学的一门技能。
本篇文章从最基本的数据查询的概念讲起，逐一介绍数据查询的各种应用场景，及其相应的查询方法，并深入分析每种查询方法的特点和注意事项，
一、概念的界定
本文所说的“查询”与普通的“查找”不用。“查找”就是简单的匹配问题，而“查询”则是根据条件去匹配结果，是间接的、更高级的查找。举个例子，比如在下表中需要查找“赵月琴”老师有哪几场监考，可以按CTRL+F键，调出【查找与替换】窗口，点击【查找全部】即可找出所有包含关键字“赵月琴”的单元格。
图1
比如在下表中需要根据考号，查找对应学生的姓名信息，可以使用VLOOKUP函数查询。这里查找的不是考号，而是与考号相关联的姓名信息！
图2
二、查询的分类
我们可以根据查询的条件与结果，将查询操作分为不同的类别。
根据是否精确匹配条件，可将查询操作分为精确查询与模糊查询；根据条件与结果数目，可将查询操作分为一（条件）对一（结果）查询、多对一查询、一对多查询、多对多查询；根据数据搜索方向，可将查询操作分为正向查询与反向查询。
以上分类主要针对使用函数与公式进行查询的操作，除此之外还可以用Excel VBA进行查询。
图3
1. 精确查询与模糊查询
（1）精确查询：精确匹配查询条件，返回一个或多个结果。
图2所示用VLOOKUP函数根据学生考号，查询对应姓名，这样的查询即为精确查询。能够进行精确查询的函数或公式非常多，最常用的是LOOK系列函和INDEX+MATCH函数组合，甚至是IF+SMALL+INDEX等函数组合也能做到。
（2）模糊查询：根据条件进行模糊匹配，返回一个或多个结果。
一般采用通配符？和*进行模糊查询。比如“马*”可以匹配所有以“马”开头的字符串，比如“马娅娅”、“马学松”等；“*三*”可以匹配所有包含“三”的字符串，比如“高三7班”、“初三2班”等。
如下表所示，我们可以根据“马*”查找第一个姓马的学生对应的班级。
图4
2. 多（单）条件与多（单）结果查询
（1）一对一查询：根据一个条件查询出唯一的结果。
这种查询方式是日常工作中所见最多的，但这种查询要求查询条件在对应查询区域是唯一的，比如身份证号、学号等均可作为查询条件。我们结合实例来看看常见的一对一查询函数或公式有哪些。
查询任务：根据下图所示的考场安排表，查找G2单元格对应考号的学生姓名。
方法1：LOOKUP（查询值，查询区域，返回值区域）
LOOKUP函数是最简单，但同时也是最强大的查询函数，上式是其最简单的一种用法。我们在H2单元格输入如下公式。
公式在A1:A11区域查询G2单元格对应值，发现在第5行，因此返回D1:D11区域的第5行单元格的值，为“陈衍林”。
图5
方法2：VLOOKUP（查询值，查询区域，返回查询区域第几列值，0）
用VLOOKUP函数进行查询需注意，查询值必须在对应查询区域的第1列（即由前到后查询），而且第3个参数对应的数值表示的不是工作表的第几列，而是对应查询区域的第几列。
如下图所示，在H2单元格输入如下公式。
=VLOOKUP(G2,A1:E11,4,0)
公式非常容易理解，在A1:E11区域查找G2单元格对应的值，发现在第5行，因此返回此区域第4列第5行单元格的值，为“陈衍林”，采用的是精确匹配模式。
图6
方法3：INDEX（返回值区域，MATCH（查询值，查询区域，0））
INDEX+MATCH是查询操作中的“黄金组合”,可完成各种各样的查询，功能十分强大！
如下图所示在H2单元格输入如下公式。
公式中先用MATCH函数查询G2单元格对应的考号在A1:A11这一列的第几行，采用的是精确匹配模式，发现在第5行。然后用INDEX函数返回D1:D11这一列数据第5行对应单元格的值，为“陈衍林”。
图7
（2）多对一查询：即查询同时满足多个条件的的数据，并返回唯一的结果，俗称“多条件查询”。
查询任务：根据下图所示的考场安排表，查找考场为“高三6”同时座位号为7的学生姓名。
方法1：LOOKUP（1，0/（条件1*条件2*...*条件n），返回值区域）
这个公式俗称多条件查询的万金油公式，可以满足任意多个条件的查询（自然也可用作一对一查询）。这个公式很多初学者朋友很不理解，下面我们结合实例来具体讲一下。
如下图所示，我们在I2单元格输入如下公式。
=LOOKUP(1,0/((G2=B1:B11)*(H2=C1:C11)),D1:D11)
图8
LOOKUP函数有一个特点，就是如果找不到查询值，则返回查询区域中小于或等于查询值的最大值。根据LOOK函数的这个特点，我们用G2=B1:B11返回的是一个数组。
我们可以选中公式中的这一部分，然后按F9显示结果，如下图所示。
图9
同理，H2=C1:C11也返回一个数组，然后两个数组相乘，TRUE和FALSE在计算式会转化为1和0，因此(G2=B1:B11)*(H2=C1:C11)最终返回的结果为：
{0;0;0;0;0;1;0;0;0;0;0}
即只有第6个数据为1，其余全部为0。然后用0去除这个数组，因为0除0会得到错误，0除1为0，因此0/((G2=B1:B11)*(H2=C1:C11))返回的数组只有第6个数据为0，其余全是错误值：
然后在上面这个数组中查询数值1，显然查询不到，因此返回小于等于1的最大值，即0，其所在位置第6行！最后取D1:A11区域的第6行单元格对应的值，为“马娅娅”！
方法2：“VLOOKUP+辅助列”也可进行多条件查找
“VLOOKUP+辅助列”的方法虽然稍显麻烦，但也可进行多条件查询，尤其是对于新手朋友来说，VLOOKUP函数掌握的比较牢，希望借助其解决多条件查询问题。
如下图所示，我们在第一列插入一个辅助列，将C列数据和D列数据用&符号连接成为一个新的字符串。然后在J2单元格输入如下公式。
=VLOOKUP(H2&I2,A1:F11,5,0)
公式其实是在A1:F11区域搜索“高三67”这个字符串（由H2和I2拼接而成的）的位置，发现在第6行，因此返回此区域第5列第6行单元格的值，为“马娅娅”。
图10
（3）一对多查询：查询满足一个条件的多个结果，返回多条记录。
查询任务：查询所有座位号为7的学生姓名。
首先得建立一个辅助列，统计座位号7第几次出现，然后用VLOOKUP或者INDEX+MATCH等都可以查询出多条记录。
建立辅助列是一对多查询的关键，我们在第1列建立辅助列，在A2单元格输入如下公式，并双击向下复制至A11单元格。
公式很简单，D2=$H$2判断D2是否与H2（座位号7）相等，返回TRUE或FALSE，N(A1)返回A1单元格对应的值（为0）。当公式向下复制的时候，$H$2采用绝对引用，不会发生改变，而D2和A1会逐渐变成D3、D4....D11和A1、A2...A10，因此得到的结果即为座位号7第几次出现。
图11
然后，我们就可用VLOOKUP或者INDEX+MATCH等查询结果。以VLOOKUP函数为例，在I2单元格输入如下公式，并向下复制至i11单元格。
=IFERROR(VLOOKUP(ROW(A1),$A$1:$F$11,5,0),'')
注意公式中的查询区域$A$1:$F$11必须采用绝对引用方式，否则在向下复制的过程中就会改变。公式巧妙之处在于不是直接查询座位号7，而是在辅助列查询1、2、3等数字（ROW(A1)的返回值），返回匹配的第一个数据，藉此找到查询区域第5列对应单元格的值。
图12
这里的IFERROR函数是为了屏蔽查询不到产生的错误值。
如果使用INDEX+MATCH组合查询的话，对应I2单元格的公式则为：
如下图所示。注意公式中的两个绝对引用方式不能变。
图13
那如果不建立辅助列，能不能进行一对多查询呢？答案是肯定的！只是需要借助更加复杂的公式组合。如下图所示在H2单元格输入如下公式，然后按CTRL+SHIFT+ENTER组合键确定输入，因为这是一个数组公式。
=IFERROR(INDEX($D$1:$D$11,SMALL(IF($C$1:$C$11=$G$2,ROW($A$1:$A$11)),ROW(A1))),'')
然后双击向下复制至H11单元格即可。
图14
这个公式组合相当巧妙，下面简单做一说明。
首先用IF($C$1:$C$11=$G$2,ROW($A$1:$A$11))逐个判断C1:C11这一列数据是否和G2相等，如果相等则返回对应行号，我们选择这部分公式按F9键显示结果为：
发现只有6和8，其余均为FALSE，表示只有在第6行和第8行找到了座位号7。
然后用SMALL函数查找第1、2、3...最小值，因为数值数据都是小于逻辑值FALSE的，因此SMALL(...,ROW(A1))返回的是第一个最小值，为6，SMALL(...,ROW(A2))返回的是第二个最小值，为8。因此最后用INDEX函数就可查询到对应第6行和第8行数据！
（4）多对多查询：根据多个条件查询多个结果。
这种查询方式是多条件查找和一对多查询的结合，有了前面的基础，对于多对对查询就不难理解了。
查询任务：根据下图所示的考场信息表，查询高三6班考场所有的3班学生。
我们在I2单元格输入如下数组公式，并用CTRL+SHIFT+ENTER键确定输入，然后向下复制至I11单元格。
=IFERROR(INDEX($D$1:$D$11,SMALL(IF(($B$1:$B$11=$G$2)*($E$1:$E$11=$H$2),ROW($A$1:$A$11)),ROW(A1))),'')
大家发现了吗？我们只是将上一个公式的IF条件判断，由单条件变成了多条件而已($B$1:$B$11=$G$2)*($E$1:$E$11=$H$2)，其他的部分是一致的。如下图所示。
图15
我们当然也可以像上面“多对一查询”一样借助辅助列，然后用VLOOKUP函数或者INDEX+MATCH组合进行多对多查询！
3. 正向查询与反向查询
这两种查询方式的区别在于数据搜索的方向，比如根据考号查找姓名属于正向查询，根据姓名查找考号则属于反向查询。截止此处，以上案例均为正向查询！
VLOOKUP函数就是一个典型的默认情况下，只支持正向查询的一个函数，它要求查询值必须在查询区域的第一列。而其他的查询函数则没有这个限制，不管查询值与查询区域谁前谁后，比如LOOKUP函数、INDEX+MATCH函数组合。
下面重点举例说说反向查询。
查询任务：根据姓名查询对应考号信息。
以INDEX+MATCH组合为例，只需要在H2单元格输入如下公式即可。
有了前文查找的基础，相信大家对于这个公式应该很容易理解。
图16
这里强调一点，虽然说VLOOKUP默认不支持反向查询，但是我们却可以通过数据重组的方式，间接地实现反向查询。如下图所示，我们在H2单元格输入如下公式。
=VLOOKUP(G2,IF({1,0},D1:D11,A1:A11),2,0)
公式利用一个IF函数将D1:D11和A1:A11调整了前后顺序，构建出了一个新数组，这个数组姓名在前、考号在后，然后就可以用VLOOKUP函数进行正向查询，得到正确的结果！如下图所示。
图17
4. 使用Excel VBA进行复杂查询
一些更加复杂的查询方式很难通过函数或公式进行查询，此时可以借助VBA，进行自定义查询。比如我们现在需要查询考场为“高三6”，座位号小于6，且班级为7或3的所有学生姓名。
我们按ALT+F11打开VBA代码编辑窗口，新建一个模块，并定义一个名为“FINDNAME”的过程。
最后运行此过程即可在G列列举出所有满足条件的结果。
图18
更多关于VBA的查询方法就不再赘述。
三、总结
以上列举了很多日常办公中经常遇到的查询问题，并详细讨论了每一种问题的可行方案，不管是用公式还是用VBA代码，往往查询方案不止一种，笔者也是尽量按由易到难的顺序进行探讨各种解决方案。
这里要强调的是，一方面，公式并不是越长就越好，就好比在进行“一对多查询”时IF+SMALL+INDEX的方法虽然一步到位解决了问题，但是尤其对于初学者而言时很难一下就理解透彻的，因此INDEX+MATCH组合查询方案是最好的选择。
另一方面，我们为了叙述方便，在文中将查询操作分为了好几类，但实际上查询操作往往是复杂的、多变的，需要结合多种方法综合施策。希望大家能在以后的日常办公中多多实践，尝试各种查询方法，做到熟能生巧、举一反三。