打开APP
userphoto
未登录

开通VIP,畅享免费电子书等14项超值服

开通VIP
一篇文章带你全面掌握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单元格输入如下公式。

=LOOKUP(G2,A1:A11,D1:D11)

公式在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单元格输入如下公式。

=INDEX(D1:D11,MATCH(G2,A1:A11,0))

公式中先用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返回的是一个数组。

{FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE}

我们可以选中公式中的这一部分,然后按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,其余全是错误值:

{#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;0;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/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)+N(A1)

公式很简单,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单元格的公式则为:

=IFERROR(INDEX($E$1:$E$11,MATCH(ROW(A1),$A$1:$A$11,0)),'')

如下图所示。注意公式中的两个绝对引用方式不能变。

图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键显示结果为:

{FALSE;FALSE;FALSE;FALSE;FALSE;6;FALSE;8;FALSE;FALSE;FALSE}

发现只有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单元格输入如下公式即可。

=INDEX(A1:A11,MATCH(G2,D1:D11,0))

有了前文查找的基础,相信大家对于这个公式应该很容易理解。

图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”的过程。

Sub FINDNAME() Dim arr(1 To 11) As String For i = 1 To 11 If (Range('B' & i).Value = '高三6' _ And Range('C' & i).Value < 6 _ And (Range('E' & i) = 7 _ Or Range('E' & i) = 3)) _ Then Count = Count + 1 arr(Count) = Range('D' & i).Value End If Next i For j = 1 To Count Range('G' & j).Value = arr(j) Next jEnd Sub

最后运行此过程即可在G列列举出所有满足条件的结果。

图18

更多关于VBA的查询方法就不再赘述。

三、总结

以上列举了很多日常办公中经常遇到的查询问题,并详细讨论了每一种问题的可行方案,不管是用公式还是用VBA代码,往往查询方案不止一种,笔者也是尽量按由易到难的顺序进行探讨各种解决方案。

这里要强调的是,一方面,公式并不是越长就越好,就好比在进行“一对多查询”时IF+SMALL+INDEX的方法虽然一步到位解决了问题,但是尤其对于初学者而言时很难一下就理解透彻的,因此INDEX+MATCH组合查询方案是最好的选择。

另一方面,我们为了叙述方便,在文中将查询操作分为了好几类,但实际上查询操作往往是复杂的、多变的,需要结合多种方法综合施策。希望大家能在以后的日常办公中多多实践,尝试各种查询方法,做到熟能生巧、举一反三。

本站仅提供存储服务,所有内容均由用户发布,如发现有害或侵权内容,请点击举报
打开APP,阅读全文并永久保存 查看更多类似文章
猜你喜欢
类似文章
【热】打开小程序,算一算2024你的财运
Vlookup函数详解,教你真正认识Excel中的函数
VLOOKUP函数详解(精确查找)
逆向查询那些事儿
利用INDEX跟MATCH函数进行多条件组合查询和数据多向查找
vlookup常用套路合集(10.1加餐)
学会LOOKUP的牛掰套路,都挺好
更多类似文章 >>
生活服务
热点新闻
分享 收藏 导长图 关注 下载文章
绑定账号成功
后续可登录账号畅享VIP特权!
如果VIP功能使用有故障,
可点击这里联系客服!

联系客服