当前位置: 首页 > 办公技巧 > 正文

excel办公技巧查询(一对多查询只会vlookup就out了,Excel全部3种查询方式你需要知道)

  • 叁碗诸角 叁碗诸角
  • 办公技巧
  • 2023-08-14 12:29:43
  • 0

使用Excel的朋友都知道,经常会碰到的一个操作那就是数据查询。如果是简单的数据查询,我们使用VLOOKUP、lookup或者其他的一些基本查询函数就可以实现。但是工作中我们会发现有一种查询经常会出现,但也是非常麻烦的一种,那就是一对多查询。

今天我们就来详细学习一下Excel中全部4种一对多查询方法。


案例:通过部门名称,查找出该部门下面的所有成员

说明:如上图,我们需要根据部门名称,查找出销售二部对应的所有成员。下面我们就来学习一下最快捷的4种方式。


方法一:vlookup函数一对多查询

讲解:vlookup函数实现一对多查询时,需要操作的难点在于需要做一个辅助列,计算对应部门的数量,通过数量来匹配对应的人名。

1.1 辅助函数为:

=COUNTIF(B$2:B2,G$4)

函数解析:如上图,作用在于计算我们每一个部门出现的次数,销售二部前面对应都促使为1-4。

1.2 查询函数为:

IFERROR(VLOOKUP(ROW(A1),A:D,3,0),"")

函数解析:做出辅助列函数后,我们就可以通过前面的序号进行查询数据。row(A1)=1的作用在于返回当前行数,往下拖动函数就会出现1、2、3、4...等等。这样就实现了通过辅助列的序号来查询数据。


二、index small if row混合函数一对多查询

讲解:这里用到了index small if row混合函数搭配使用查询的方式,对于新手来讲可能会比较蒙,下面我们来详细进行一些函数解析:

查询函数为:

{INDEX(B:B,SMALL(IF($A$2:$A$11=F$4,ROW($2:$11),4^8),ROW(F1)))&""}

函数解析:

1、index(B:B,xx):这个函数为返回B列从上往下的第几个值。有两个参数,B:B为我们需要查询的位置,第二参数为需要查询值的位置。

2、SMALL(A,B):small函数代表的是取出期间数字中的最小的一个值。A,B为对应的参数,可以为任意个。

3、IF($A$2:$A$11=F$4,ROW($2:$11),4^8)

代表党查询的区域有查询的值的时候,返回当前值所在行的值。否则返回0,为了消除0的影响,所以我们最后用连接“”来取消。


三、VBA自定义Nlookup函数进行一对多查询

讲解:通过自定义函数,我们可以查找出姓名为王五当月全部销售数据。

查询函数:

=NLOOKUP(K4,B1:F15,5,-1)

函数解析:

这是VBA自定义的函数,所以功能上面我们第四参数设置的是-1,来进行全部查找。代码如下:


四、数据透视表一对多查询数据

讲解:这种方法主要是通过数据透视表刷选的方式来进行的,功能和数据筛选是一样的。如果是数据比较少的情况下,我们可以直接点击筛选特定的关键词就可以实现这种操作。

现在你掌握了如何进行数据一对多查询了吗?


最新文章