二维码 购物车
部落窝在线教育欢迎您!

八大查找函数公式,轻松搞定数据中的多条件查找

 

作者:花花来源:部落窝教育发布时间:2021-09-27 15:13:25点击:16639

分享到:
0
收藏    收藏人气:0人
版权说明: 原创作品,禁止转载。

编按:

在表格中常见的单条件查找目标值相信大家都会,用过VLOOKUP函数的小伙伴可以说是很轻松就能解决单条件查找的问题,但是当单条件变成多条件的时,你还会用函数查找吗?今天小编整理了常见的二维数据中多条件查找的公式大全送给大家。

 

Hello,大家好!这里是部落窝教育,我是花花,很高兴和你一起学习Excel教程,今天我们来学习函数解决多条件查找的8种方法,相信总有一种适合你的。下图效果是当条件①和条件②变化时,会自动在左边的数据区域中查找出业绩。

 

 

这是一份随机模拟的20211~6月销售人员业绩数据表格,如下图:

 

 

我们现在需要设置公式,通过条件①和条件②查找出对应业绩数据。第一眼看到这种问题,你的反应是使用什么函数来解决?我们来个小互动,想到VLOOKUP函数的小伙伴在评论区留言,想到其他函数的聊一聊是为什么。

 

在讲解公式之前,我们先在I3K3单元格分别输入“姓名”和“月份”,选中I4单元格,点击【数据】选项卡下的“数据验证功能”。在弹出来的对话框中,在允许的下拉列表中选择序列。在来源文本框中输入=$C$3:$G$3,单击确定按钮。K4单元格中的月份操作相同,不同的只是需要在“来源”文本框中输入=$B$4:$B$9。返回工作表中,单击设置了数据验证单元格,在单元格右侧会出现一个下拉按钮,单击该按钮可选择指定序列内容。这个很简单,我们就不多赘述啦,现在正式进入多条件查找公式讲解部分。

 

方法1.  VLOOKUP+MATCH多条件查找


K6单元格中输入公式=VLOOKUP(K4,B:G,MATCH(I4,B3:G3,0),0)

 

公式语法解释

VLOOKUP函数语法:(查找值,查找区域,返回值的列数,精确查找或模糊查找)

K4单元格就是对应条件②的月份值,查找区域为B:G列,查找区域中的列数使用MATCH函数进行判断。

 

MATCH函数的语法:(查找对象,指定查找的范围或者数组,查找方式)

MATCH的对象就是条件①的姓名,查找范围就是B3:G3,查找方式选择0为精确查找。

 

因为VLOOKUP第三参数返回的列数是根据条件①的姓名动态变化而变化,所以嵌套MATCH函数对条件①姓名进行查找。

 

 

方法2.  HLOOKUP+MATCH多条件查找

 

K6单元格中输入公式=HLOOKUP(I4,3:9,MATCH(K4,B3:B9,0),0)

 

公式语法解释

HLOOKUP函数语法:(查找值,查找范围,返回值所在的行数,查找模式)

MATCH函数语法前面解释过了,这里就不再重复解释了。重点讲一下HLOOKUPVLOOKUP函数的区别,从名称表面上看只有首字母的差异,VVertical的第一个字母,单词意思是垂直方向,所以VLOOKUP函数代表着垂直方向查找,HHorizontal的第一个字母,单词意思水平方向,HLOOKUP就表示水平方向查找。

 

 

方法3.  INDEX+MATCH+MATCH多条件查找

 

K6单元格中输入公式=INDEX(B3:G9,MATCH(K4,B3:B9,0),MATCH(I4,B3:G3,0))

 

公式语法解释

INDEX函数语法:(查找区域或数组常量,返回值所在区域的行号,返回值所在区域的列号)

 

因为INDEX函数中第二参数返回是行号,第三参数返回的是列号,行号和列号都是变量,这时我们可以使用MATCH函数分别对行号条件①“姓名”和条件②“月份”进行查找。

 

 

方法4.  SUMPRODUCT多条件查找

 

K6单元格中输入公式=SUMPRODUCT((B4:B9=K4)*(C3:G3=I4)*C4:G9)

 

公式语法解释

SUMPRODUCT函数语法:((条件1=条件区域1)*(条件2=条件区域2)*……*(求和区域))

该函数的语法结构很有规律,也便于记忆理解,所以基本不需要怎么解释相信大家看到语法的时候就懂了。

 

 

方法5.  SUMIF+OFFSET+MATCH多条件查找

 

K6单元格中输入公式=SUMIF(B:B,K4,OFFSET(B:B,0,MATCH(I4,C3:G3,0)))

 

公式语法解释

SUMIF函数语法:(条件区域,指定的条件,需要求和的区域)

OFFSET函数语法:(起始单元格,移动的行数,移动的列数,高度,宽度)

 

这个方法从外观上看上去嵌套了三个函数,实际底层逻辑只是通过SUMIF条件求和的原理搭配OFFSETMATCH函数进行动态查找。

 

 

方法6.  DSUM多条件查找

 

前面介绍的五种方法基本都使用了函数嵌套才完成了多条件查找,DSUM函数对多条件查找就不需要嵌套那么麻烦了,在K6单元格中输入公式=DSUM(B3:G9,I4,K3:K4)即可。

 

公式语法解释

DSUM(数据区域,求和的列数,条件区域)

需要注意的是在选择第三参数时,必须包含选择区域字段标题,不可以像别的函数一样只选择一个单元格。

 

 

方法7.  XLOOKUP多条件查找(Office 365专属函数)

 

K6单元格中输入公式=XLOOKUP(K4,B4:B9,XLOOKUP(I4,C3:G3,C4:G9),0)

 

公式语法解释:

XLOOKUP函数语法:(要查找的值,查找的区域,返回的区域)

注意这个函数只在Office 365版本的Excel中才有的哦

 

 

方法8.  INDEX+FILTER+MATCH多条件查找

 

FILTER也是Office 365专属函数,INDEXMATCH这两个函数前面几种方法已经出现很多次了,相信大家一定不陌生了,在条件查找中MATCH函数具有“名配角”的美称。

 

介绍一下FILTER函数,该函数语法结构:(数据源,筛选条件,容错值)

 

 

FILTER函数在单条件查找的时候还可以动态溢出查找结果,自动扩展填充查找值,这个在之前的文章里面有专门介绍过该函数。

 

 

以上就是今天跟大家分享的多条件查找的方法,感谢大家耐心看完,希望大家能够喜欢~

 

编后语:

成功不是将来才有的,而是从决定去做的那一刻起,持续累积而成。再长的路,一步步也能走完,再短的路,不迈开双脚也无法到达。

 

本文配套的练习课件请加入QQ群:902294808下载。

Excel高手,快速提升工作效率,部落窝教育《一周Excel直通车》视频和《Excel极速贯通班》直播课全心为你!

扫下方二维码关注公众号,可随时随地学习Excel

IMG_256

相关推荐:

将excel中的数据按照指定数量进行重复,非得用VBA才能实现吗?

如何在特定位置批量插入空行等12种实用办公技巧

4种删除excel重复值的小妙招,速收藏

Power Query的数据替换技巧比Excel函数更万能!

版权申明:

本文作者花花;同时部落窝教育享有专有使用权。若需转载请联系部落窝教育。