首页
学习
活动
专区
工具
TVP
发布

数据快速批量查找,这些函数公式你得记牢了!

在Excel中进行数据的批量查找,经常会用到一些函数公式,下面技巧妹和大家分享4个数据查找示例:

1、多行多列连续性查找

如下图表格所示,根据姓名进行相应的数据查找,在J2单元格中输入公式:

=VLOOKUP($I2,$B$1:$G$11,COLUMN(B1),0),往右往下填充公式即可。

公式说明:利用VLOOKUP+COLUMN函数组合,第3个参数COLUMN(B1)返回查找区域中的指定列数,公式向右填充时,会自动变成COLUMN(C1)、COLUMN(D1)、COLUMN(E1),即变成了3、4、5。

2、多行多列不连续性查找

根据工号进行数据查找,而且查找项不连续,在J2单元格中输入公式:

=VLOOKUP($I2,$A$1:$G$11,MATCH(J$1,$A$1:$G$1,0),0),往右往下填充公式即可。

公式说明:利用VLOOKUP+MATCH函数组合。MATCH函数公式返回查找项在标题行中对应的列号,结果作为VLOOKUP函数的第3个参数,公式中注意单元格的引用方式。

3、双条件批量查询

如下图表格所示,员工存在重名,为了精准查找,现在需要根据员工的姓名和岗位查找员工的对应月工资标准,在I2单元格中输入公式:

=VLOOKUP($I2&$J2,IF(,$B$1:$B$11&$D$1:$D$11,$G$1:$G$11),2,0),

按Ctrl+Shift+Enter组合键输入公式,并向下填充。

公式说明:利用VLOOKUP+IF函数组合。第1个参数用&符号进行查找值合并,

IF(,$B$1:$B$11&$D$1:$D$11,$G$1:$G$11)则是重新构建一个新的查找区域。

这里也可以利用INDEX+MATCH函数组合,在I2单元格输入公式:

=INDEX($G$1:$G$11,MATCH($I2&$J2,$B$1:$B$11&$D$1:$D$11,0)),

按Ctrl+Shift+Enter组合键输入公式,并向下填充。

公式说明:MATCH($I2&$J2,$B$1:$B$11&$D$1:$D$11,0)返回指定值在查找区域中所在的行位置,作为INDEX函数公式的第2个参数。

4、一对多查询

如果我们查询某一部门对应的所有员工月工资标准呢?比如我们要查询人力资源部门对应的所有月工资标准,在I2单元格中输入公式:

=IFERROR(INDEX($G$1:$G$11,SMALL(IF($C$1:$C$11=$I$2,ROW($1:$11)),ROW(A1))),""),按Ctrl+Shift+Enter组合键输入公式,并向下填充。

公式说明:这里利用INDEX+SMALL+IF 这个万金油函数公式。

IF($C$1:$C$11=$I$2,ROW($1:$11)),若C1:C11中的的值等于I2,返回C列中对应的行号,否则返回逻辑值FALSE;

SMALL(IF($C$1:$C$11=$I$2,ROW($1:$11)),ROW(A1)),对IF函数公式的计算结果进行取数,随着公式的向下填充,提取第1、2、3……n个最小值,依次得到符合条件的行号;

INDEX函数则以SMALL函数公式返回的行号作为索引值,返回G列中对应的月工资标准;

最后用IFERROR函数规避错误值,使之显示为空。

  • 发表于:
  • 原文链接https://kuaibao.qq.com/s/20191128A05MOW00?refer=cp_1026
  • 腾讯「腾讯云开发者社区」是腾讯内容开放平台帐号(企鹅号)传播渠道之一,根据《腾讯内容开放平台服务协议》转载发布内容。
  • 如有侵权,请联系 cloudcommunity@tencent.com 删除。

扫码

添加站长 进交流群

领取专属 10元无门槛券

私享最新 技术干货

扫码加入开发者社群
领券