4年Excel沉淀:多条件+交叉+模糊查询,一篇讲透!

我是【桃大喵学习记】，欢迎大家关注哟~，每天为你分享职场办公软件使用技巧干货！

——首发于微信号：桃大喵学习记

很多小伙伴经常私信提问关于Excel数据查询的问题，今天汇总分享一下Excel场景多条件查询+交叉查询+模糊查询常见应用场景，简单实用，一篇讲透！（备注：这篇教程篇幅有点长，建议收藏备用）

如下图所示，现在需要根据【姓名】和【部门】2个条件，查询对应的【考核成绩】信息。

一、XLOOKUP函数公式

功能：XLOOKUP函数是一个查找函数，在某个范围或数组中搜索匹配项，并通过第二个范围或数组返回相应的项，默认情况下使用精准匹配。

万能公式语法：

=XLOOKUP(1,(条件1)*(条件2)*(条件N),返回数组,未找到值,匹配模式,搜索模式)

（备注：多条件同时满足）

=XLOOKUP(1,(条件1)+(条件2)+(条件N),返回数组,未找到值,匹配模式,搜索模式)

（备注：多条件至少一个满足） 

方法：

在目标单元格中输入公式：

=XLOOKUP(1,(B:B=F2)*(C:C=G2),D:D,"")

然后点击回车即可

解读：

①((B:B=F2)*(C:C=G2)：表示要同时满足2个条件，2个条件数组相乘会最终得到一个由1（全满足）和0组成的新数组。

②最终XLOOKUP函数的任务就变成了：在第2参数“查找数组”中查找数字 1 。找到1的那一行，就是同时满足所有条件的行，最后返回D列对应的值，否则返回空。

特别注意：

使用XLOOKUP函数时有一个真正的痛点，当查找值在查找列中出现多次时，XLOOKUP函数永远只返回第一个匹配项（除非5个参数设置成-1，返回最后一个匹配项）。

二、FILTER函数公式

功能：FILTER是基于定义的条件筛选一系列数据的函数，它由数组，包括，空值三个参数所构成。

万能公式语法：

=FILTER(返回数组,条件1*条件2*条件N,"")

（备注：多条件同时满足）

=FILTER(返回数组,条件1+条件2+条件N,"")

（备注：多条件至少一个满足） 

方法：

在目标单元格中输入公式：

=FILTER(D:D,(B:B=F2)*(C:C=G2),"")

然后点击回车即可

解读：

FILTER函数进行多条件查询在Excel365/WPS新版可用，它最大的特点就是简洁、可自动返回多个结果，相对于XLOOKUP函数的功能缺陷，FILTER函数才是真正解决“一对多查找”的函数。

三、INDEX+MATCH法（经典万能，所有Excel/WPS版本通用）

功能：INDEX+MATCH函数组合做为一个万能查找组合，需要先使用MATCH来查询结果所在位置，然后再使用INDEX来返回对应的数据结果，有时能够帮助我们快速解决问题。

万能公式语法：

=INDEX(结果返回区域, MATCH(1,(条件1)*(条件2)*(条件N),0))

（备注：多条件同时满足）

=INDEX(结果返回区域, MATCH(1,(条件1)+(条件2)+(条件N),0))

（备注：多条件至少一个满足）  

方法：

在目标单元格中输入公式：

=INDEX(D:D,MATCH(1,(B:B=F2)*(C:C=G2),0))

然后点击回车即可

解读：

上面公式首先通过MATCH(1,(B:B=F2)*(C:C=G2),0)获取查询值所在行号，然后再通过INDEX函数去结果列里面，找对应这个行的值。

四、DGET函数公式

功能：从数据库提取符合指定条件的单个记录。这个函数是一个数据库函数，它能根据表头字段的名称来精准匹配数据，设置参数时，记得要包含这些表头字段名称。

语法：=DGET(带表头的查询表,返回结果的表头名称,查找条件)

方法：

在目标单元格中输入公式：

=DGET(A1:D8,D1,F1:G2)

然后点击回车即可

解读：

使用DGET函数公式时，选择查询表格时必须包含表格表头信息，设置参数时，必须要包含查询表中的表头字段名称。

第1参数：A1:D8就是带着表头信息的要查询的表格数据区域；

第2参数：D1就是要返回结果列表头名称“考核成绩”；

第3参数：F1:G2就是查询条件，这个查询结果表表头名称必须跟左侧查询表格中的表头名称一致。

所谓交叉查询，其实就是判断条件一个是纵横的，一个是横向的。如下图所示，需要根据左侧表格中的纵向【姓名】和横向【季度】来查找最终的销售额。

一、INDEX+MATCH组合公式

在目标单元格中输入公式：

=INDEX(B2:E6,MATCH("王五",A2:A6,0),MATCH("3季度",B1:E1,0))

然后点击回车即可

解读：

① MATCH(G2,A2:A6,0)：在A2:A6这个纵向区域中查找“王五”，返回其所在行号3。

②MATCH(H2,B1:E1,0)：在 B1:E1 这个横向区域中查找“3季度”，返回其所在列号3。

③INDEX(B2:E6,3,3)：在 B2:E6 这个5行4列的数值区域中，提取第3行与第3列交叉位置的值，结果为300。

二、XLOOKUP函数嵌套公式

在目标单元格中输入公式：

=XLOOKUP("王五",A2:A6,XLOOKUP("3季度",B1:E1,B2:E6,""),"")

然后点击回车即可

解读：

①内层查找：XLOOKUP("3季度",B1:E1,B2:E6,"")——在B1:E1中定位“3季度”所在的列，并返回 B2:E6 中该列的所有数据（即 D2:D6）。

②外层查找：XLOOKUP("王五", A2:A6, 内层结果) —— 在 A2:A6 中定位“王五”所在的行，并从内层返回的列（D2:D6）中取出对应行的数值，得到300

三、FILTER函数嵌套公式

在目标单元格中输入公式：

=FILTER(FILTER(B2:E6,B1:E1="3季度",""),A2:A6="王五","")

然后点击回车即可

解读：

①内层FILTER先横向查询，找出符合条件的数据列。

②外层FILTER再进行纵向查询，最终得到交叉数据。

四、SUMPRODUCT函数公式

在目标单元格中输入公式：

=SUMPRODUCT((A2:A6="王五")*(B1:E1="3季度")*B2:E6)

然后点击回车即可

解读：

这个公式的核心其实就是利用SUMPRODUCT函数进行多条件求和。

①(A2:A6="王五")：判断左侧信息表中的“姓名”是否等于“王五”，结果是一列逻辑值（True / False），本质是一维列数组。

②(B1:E1="3季度")：判断表中“季度”是否等于“3季度”，结果是一行逻辑值，本质是一维行数组。

③B2:E6，是需要统计的销售额区域，这是一个多行多列的二维数组。

最后，这三个式子相乘，SUMPRODUCT函数会自动把逻辑值转为1和0，然后进行逐元素相乘并求和，从而得到目标结果。

SUMPRODUCT函数多条件求和通用万能公式：

=SUMPRODUCT((条件数据区域1=条件1)*(条件数据区域2=条件2)*(条件数据区域N=条件N)*求和区域)

如下图所示，左侧是商品价格明细表格，现在需要根据商品名称关键词，筛选出名称中包含该关键词的商品，并将匹配到的商品名称及其价格查找出来。

一、XLOOKUP函数公式

在目标单元格中输入公式：

=XLOOKUP("*"&F2&"*",C:C,C:D,"未找到",2)

然后点击回车即可

解读：

①查找值："*"&F2&"*"，用通配符*包裹查询关键词（F2的值），实现模糊匹配。

②查找数组：C:C，在C列中搜索与查找值匹配的目标数据。

③返回数组：C:D，匹配成功后，返回对应行的C列和D列数据。

④未找到值："未找到"，若无匹配结果，则显示自定义提示文本 “未找到”。

⑤匹配模式：2，表示通配符匹配（支持 * 和 ?），匹配模式必须选择2，否则无法实现模糊匹配。

二、FILTER函数公式

在目标单元格中输入公式：

=FILTER(C:D,ISNUMBER(FIND(F2,C:C)),"无找到")

然后点击回车即可

解读：

虽然FILTER函数本身不支持直接进行模糊匹配，但我们可以借助FILTER配合FIND函数来实现模糊查找功能。

①第1参数：C:D，就是返回查询结果的数据范围；

②第2参数：查询条件，使用ISNUMBER+FIND组合：

FIND函数在“关键词”F2单元格中查找C:C单元格中的内容。如果找到了，返回找到内容的起始位置。否则，返回一个错误值。

ISNUMBER函数主要用于判断FIND函数的结果是否为数字，如果是数字（表示找到了字符串），返回TRUE，表示满足条件返回结果；如果是错误值（表示未找到字符串），返回FALSE，就是不满足条件，返回指定错误值。

③第3参数：如果数据不满足条件，就返回一个指定信息。

特别提醒：

FIND函数在进行模糊查询时是区分字母大写小的，如果不需要区分大小写可以改成SEARCH函数。

亲爱的小伙伴们：

---

如果你正在为复杂繁琐的WPS表格/Excel操作困扰，希望通过掌握实用技能显著提升工作效率、减少无效加班——你可以考虑下我的WPS表格/Excel系列课程。