Excel模糊查找全攻略：SEARCH、COUNTIF与FILTER函数实战应用-北京尧图网络

1. 从一次数据清洗的“翻车”经历说起上周市场部的同事发来一份近万行的客户信息表让我帮忙筛选出所有来自“北京”、“上海”、“广州”、“深圳”这四个城市的潜在客户。听起来很简单对吧我第一反应就是用Excel的筛选功能在“城市”列里勾选这四个选项。结果筛选出来只有寥寥几百条。直觉告诉我数据量对不上。仔细一看原始数据我差点没背过气去。原来录入数据的同事“风格”非常自由。同一个“北京”在表格里可能呈现为“北京市”、“北京朝阳区”、“客户位于北京”甚至是“北京总部”。用精确匹配的筛选自然就把后面这些“不标准”的数据全都漏掉了。这让我想起无数个类似的场景在一堆产品描述里找出所有包含“升级版”或“Pro”字样的SKU在用户反馈中快速定位提到“登录失败”或“支付问题”的条目或者仅仅是检查一列邮箱地址是否都来自“company.com”这个域名。这些需求的本质都不是精确匹配而是模糊查找——判断一个单元格里的文本是否包含了我们关心的某些特定字符或字符串。这恰恰是Excel数据处理中最高频、最基础却也最容易让人头疼的操作之一。很多人会卡在第一步如何告诉Excel去“寻找”和“判断”今天我们就抛开那些华而不实的复杂功能深入聊聊Excel里判断单元格是否包含特定字符的几种核心方法。你会发现掌握它们你处理文本数据的效率会提升一个量级。2. 基础但强大的查找函数FIND与SEARCH当我们需要判断单元格是否包含某些字符时最直接的武器就是查找函数。Excel提供了两个孪生兄弟FIND和SEARCH。它们的功能高度相似都是在一个文本字符串中查找另一个文本字符串并返回后者在前者中首次出现的位置一个数字。如果找不到则返回错误值#VALUE!。这个“返回位置”的特性就是我们用来做“是否包含”判断的关键。因为只要函数返回了一个数字而不是错误就证明找到了即“包含”。2.1 FIND函数严格区分大小写的侦察兵FIND函数的语法很简单FIND(要查找的文本, 在哪找, [从第几个字符开始找])。其中“从第几个字符开始找”是可选参数默认为1从第一个字符开始。它的最大特点是区分英文大小写。例如FIND(apple, I have an Apple.)会返回#VALUE!错误因为句子中的“Apple”首字母是大写A。FIND(Apple, I have an Apple.)则会返回数字12表示“Apple”这个字符串从原句的第12个字符开始。在实际工作中这个特性是把双刃剑。如果你的数据来源规范大小写统一比如全部大写的产品代码那么使用FIND可以避免误判更加精确。但面对用户自由输入的文字比如文章开头提到的城市名混录用FIND判断“北京”就会漏掉“北京市”因为“市”这个字符的存在改变了查找结果。2.2 SEARCH函数不区分大小写的灵活助手SEARCH函数的语法和FIND完全一致SEARCH(要查找的文本, 在哪找, [从第几个字符开始找])。它的核心优势在于不区分英文大小写并且支持使用通配符。不区分大小写SEARCH(apple, I have an Apple.)会成功返回数字12无视大小写差异。支持通配符这是SEARCH函数一个极其强大的功能。通配符主要有两个问号?代表任意单个字符。SEARCH(张?三, A1)可以找到“张三”、“张三”、“张老三”等。星号*代表任意多个字符包括零个。SEARCH(*北京*, A1)这就是我们解决开头那个问题的钥匙它意味着在A1单元格里只要文本中任意位置出现了“北京”二字无论前后有什么其他内容都能被找到。注意FIND函数不支持通配符。如果你在FIND的参数中使用了?或*Excel会老老实实地去查找问号或星号字符本身。2.3 将查找结果转化为“是/否”判断知道了FIND和SEARCH会返回位置或错误我们如何将其变成直观的“包含”或“不包含”呢答案是结合ISNUMBER函数。ISNUMBER函数如其名用来判断一个值是否为数字。是数字则返回TRUE否则返回FALSE。所以一个完整的判断公式通常是这样的ISNUMBER(SEARCH(“北京”, A1))这个公式的含义是在A1单元格里查找“北京”不区分大小写支持通配符。如果找到了SEARCH返回一个数字位置ISNUMBER对这个数字的判断结果为TRUE我们解读为“包含”。如果没找到SEARCH返回错误值ISNUMBER对错误值的判断结果为FALSE我们解读为“不包含”。你可以把SEARCH换成FIND以适应区分大小写的场景。实操心得在绝大多数中文环境或大小写不敏感的数据清洗场景中SEARCH函数的适用性远高于FIND。我个人的习惯是除非明确需要区分大小写如验证密码复杂度、处理严格编码否则一律优先使用SEARCH配合ISNUMBER的组合拳它的容错率更高。3. 信息函数双雄COUNTIF与COUNTIFS的模糊匹配妙用如果说FIND/SEARCH是精准的“字符串手术刀”那么COUNTIF和COUNTIFS就是高效的“区域扫描仪”。它们原本用于按条件计数但其条件参数支持通配符的特性让我们可以非常优雅地对一个区域进行“是否包含”的批量判断。3.1 COUNTIF函数单条件区域扫描COUNTIF的语法是COUNTIF(要检查的区域, 条件)。关键在于这个“条件”。当我们需要判断“是否包含”时可以这样写条件*关键词*。双引号内的星号就是通配符表示“任意字符”。例如我们想判断A列每个单元格是否包含“故障”二字可以在B2单元格输入公式并向下填充COUNTIF(A2, *故障*) 0这个公式的解读是在A2这个“区域”虽然只有一个单元格里计算满足条件“包含‘故障’二字”的单元格数量。如果数量大于0结果就是TRUE包含否则为FALSE。你可能会问为什么不直接用COUNTIF(A2, *故障*)然后看结果是不是大于0呢因为COUNTIF直接返回的是数字0, 1, 2...。对于单个单元格的判断结果只能是0不包含或1包含。所以0这个比较是为了将其转化为逻辑值TRUE/FALSE更直观。你也可以省略0直接用COUNTIF(A2, *故障*)然后通过条件格式将大于0的单元格高亮效果一样。为什么用COUNTIF它的优势在于简洁特别是当“条件”本身很复杂或者已经是存储在另一个单元格里的值时。比如你的关键词在D1单元格公式可以写为COUNTIF(A2, *D1*) 0。用连接符动态构建条件非常灵活。3.2 COUNTIFS函数多条件“与”关系扫描COUNTIFS是COUNTIF的复数版本用于多条件计数所有条件必须同时满足。语法是COUNTIFS(区域1, 条件1, 区域2, 条件2, ...)。我们可以利用它来判断一个单元格是否同时包含多个关键词。这在实际工作中非常有用。场景筛选出用户反馈中既提到“卡顿”又提到“发热”的严重问题记录。假设反馈内容在A列我们可以在B列建立辅助列输入公式COUNTIFS(A2, *卡顿*, A2, *发热*) 0这个公式的意思是在A2单元格作为区域1里查找包含“卡顿”并且在A2单元格也作为区域2里查找包含“发热”的记录数量。只有两个条件同时满足计数才为1公式返回TRUE。踩坑提醒这里有一个极其常见的误区。很多人会想当然地写成COUNTIFS(A2, *卡顿*发热*)希望用一个条件匹配“卡顿...发热”这样的字符串。这是行不通的。COUNTIFS的每个条件参数是独立的它无法在一个条件字符串里处理两个间隔不确定的关键词。你必须像上面那样将每个关键词作为单独的条件参数并重复引用同一个检查区域。3.3 对比SEARCH与COUNTIF如何选择特性SEARCHISNUMBERCOUNTIF核心功能查找文本位置转化为逻辑判断按条件计数利用计数结果判断通配符SEARCH支持FIND不支持支持判断单个单元格最直接、最标准的用法可以但语法稍显“绕”判断区域中任意单元格需结合数组公式或SUMPRODUCT较复杂天然支持直接指定区域即可动态关键词可直接引用单元格如SEARCH(D1, A2)需用连接符构建条件如*D1*多关键词“与”判断需嵌套多个SEARCH并用AND连接公式长使用COUNTIFS结构清晰多关键词“或”判断需嵌套多个SEARCH并用OR连接条件用{*A*,*B*}数组或相加多个COUNTIF选择建议如果你只是对一列数据做简单的“是否包含某个词”的判断两者皆可SEARCH更函数化COUNTIF更易读。如果你的关键词是动态变化的比如来自另一个单元格SEARCH更简洁。如果你需要判断“是否同时包含A和B”用COUNTIFS。如果你需要判断“是否包含A或B或C”用COUNTIF配合数组条件SUM(COUNTIF(A2, {*A*,*B*,*C*}))0会更方便。4. 条件格式让“包含”结果一目了然很多时候我们不仅需要知道是否包含更需要让这些单元格自己“跳出来”。这时候条件格式就是最好的可视化工具。它允许你基于公式的计算结果自动为单元格设置字体、颜色、边框等格式。实战场景高亮显示所有包含“紧急”或“尽快”字样的任务项。操作步骤如下选中你需要应用格式的数据区域例如A2:A100。点击【开始】选项卡下的【条件格式】-【新建规则】。在规则类型中选择“使用公式确定要设置格式的单元格”。在“为符合此公式的值设置格式”框中输入我们的判断公式。对于“或”关系我们需要用OR函数组合多个SEARCH判断OR(ISNUMBER(SEARCH(紧急, A2)), ISNUMBER(SEARCH(尽快, A2)))关键点公式中引用的单元格这里是A2必须是你选中区域左上角的单元格。Excel会把这个公式相对应用到整个区域。点击【格式】按钮设置你想要的突出显示格式比如填充红色背景。点击【确定】。完成以上步骤后A2:A100区域内只要单元格内容包含“紧急”或“尽快”就会自动变成红色背景一目了然。条件格式公式的进阶用法与关系将OR替换为AND。例如高亮同时包含“北京”和“客户”的单元格AND(ISNUMBER(SEARCH(北京, A2)), ISNUMBER(SEARCH(客户, A2)))。排除特定内容高亮不包含某些词的单元格。例如高亮所有没有写“已完成”的任务ISNUMBER(SEARCH(已完成, A2))FALSE或者更简洁的NOT(ISNUMBER(SEARCH(已完成, A2)))。结合其他函数条件格式的公式可以非常复杂。例如你可以结合LEN和SEARCH只高亮“company.com”出现在字符串末尾的邮箱即验证邮箱域名RIGHT(A2, LEN(company.com))company.com。这里虽然没有直接用“包含”但思路是相通的都是基于文本判断。重要提示在条件格式中使用涉及文本查找的公式时务必注意公式的相对引用和绝对引用。如果你的判断需要始终针对某一固定列比如总是判断B列是否包含C1单元格的关键词那么公式应该类似于ISNUMBER(SEARCH($C$1, B2))。$C$1是绝对引用锁定关键词单元格B2是相对引用会随着条件格式应用的行而变化。5. 借助FILTER与XLOOKUP进行动态筛选与查找在最新版本的Office 365和Excel 2021中微软引入了两个革命性的函数FILTER和XLOOKUP。它们本身不是专门的“包含”判断函数但结合我们前面学的文本判断逻辑能实现极其强大的动态数据提取功能。5.1 FILTER函数根据“包含”条件筛选出整行数据FILTER函数可以根据一个或多个条件直接从数组或区域中筛选出符合条件的所有记录。语法是FILTER(要返回的数据区域, 条件1, [如果为空则返回])。假设我们有一个数据表A列是产品IDB列是产品描述。现在我们需要筛选出所有产品描述中包含“无线”二字的产品信息。传统做法可能是用辅助列筛选。而用FILTER只需一个公式FILTER(A:B, ISNUMBER(SEARCH(无线, B:B)), 未找到)这个公式解读如下A:B这是我们要返回的结果区域即筛选后显示A列和B列的所有数据。ISNUMBER(SEARCH(无线, B:B))这是筛选条件。它对B列的每一行进行判断看是否包含“无线”。SEARCH函数在这里作用于一个数组整个B列会返回一个由数字和错误值组成的数组。ISNUMBER将其转换为由TRUE和FALSE组成的逻辑数组。未找到可选参数。如果所有行都不满足条件即没有产品包含“无线”则在这个单元格显示“未找到”避免返回#CALC!错误。按下回车所有包含“无线”的产品行会瞬间被提取出来形成一个动态数组。当源数据更新时这个结果也会自动更新。多条件“与”筛选筛选描述中同时包含“无线”和“蓝牙”的产品。FILTER(A:B, (ISNUMBER(SEARCH(无线, B:B))) * (ISNUMBER(SEARCH(蓝牙, B:B))), 未找到)注意这里将两个条件用乘号*连接。在Excel的逻辑运算中TRUE相当于1FALSE相当于0。两个条件相乘只有都为TRUE1*11时结果才为TRUE非零值被视为TRUE实现了“与”逻辑。多条件“或”筛选筛选描述中包含“无线”或“蓝牙”的产品。FILTER(A:B, (ISNUMBER(SEARCH(无线, B:B))) (ISNUMBER(SEARCH(蓝牙, B:B))), 未找到)用加号连接只要有一个条件为TRUE1相加结果就大于0被视为TRUE。5.2 XLOOKUP函数进行“包含”式的模糊查找XLOOKUP通常用于精确查找但通过结合SEARCH等函数可以实现近似“包含即找到”的效果。不过它通常只返回第一个匹配项。假设我们有一个“关键词-分类”对照表在Sheet2的A列是关键词如“退款”、“投诉”、“表扬”B列是对应的分类如“售后”、“客诉”、“好评”。现在我们想在Sheet1的反馈内容A列中查找是否包含这些关键词并返回对应的分类。我们可以使用一个数组公式配合XLOOKUPXLOOKUP(TRUE, ISNUMBER(SEARCH(Sheet2!$A$2:$A$10, A2)), Sheet2!$B$2:$B$10, 未分类)这个公式需要按CtrlShiftEnter旧版本数组公式或在Office 365中直接回车其原理是SEARCH(Sheet2!$A$2:$A$10, A2)用A2单元格的内容去依次搜索对照表A2:A10中的每一个关键词。这会返回一个数组包含每个关键词在A2中的位置数字或错误值。ISNUMBER(...)将上述数组转化为TRUE/FALSE数组。XLOOKUP(TRUE, ...)在得到的TRUE/FALSE数组中查找第一个TRUE值。找到后返回对照表B列Sheet2!$B$2:$B$10中对应位置的值即分类。如果找不到任何TRUE即不包含任何关键词则返回“未分类”。这种方法的价值与局限它非常适合进行多关键词的归类比如对用户反馈进行自动打标签。但需要注意的是SEARCH在数组中的顺序很重要XLOOKUP会返回第一个匹配到的关键词对应的分类。因此你需要把优先级高的关键词或更具体的关键词放在对照表的前面。例如“登录失败”应该放在“失败”前面否则所有包含“失败”的反馈包括“登录失败”、“支付失败”都会先被归类到“失败”这个更宽泛的类别下。6. 处理复杂逻辑与常见陷阱掌握了基本方法后现实中的数据往往更“脏”需求也更复杂。这一部分我们来探讨一些进阶场景和必须绕开的坑。6.1 判断包含多个关键词中的任意一个“或”逻辑我们之前用COUNTIF配合数组{*A*,*B*}简单提过。这里详细展开几种方法。方法一SUM COUNTIF 数组公式SUM(COUNTIF(A2, {*故障*, *错误*, *bug*})) 0这个公式会分别计算A2包含“故障”、“错误”、“bug”的次数0或1然后求和。如果和大于0说明至少包含一个返回TRUE。这是非常简洁的写法。方法二SUMPRODUCT ISNUMBER SEARCHSUMPRODUCT(--ISNUMBER(SEARCH({故障,错误,bug}, A2))) 0原理类似。SEARCH返回数组ISNUMBER转化为逻辑数组--将逻辑值转化为1/0SUMPRODUCT求和。大于0则为真。方法三用OR连接多个判断最直观但冗长OR(ISNUMBER(SEARCH(故障,A2)), ISNUMBER(SEARCH(错误,A2)), ISNUMBER(SEARCH(bug,A2)))当关键词很多时这个公式会非常长。选择建议如果关键词数量固定且不多比如3-5个方法一最优雅。如果关键词列表本身在一个单元格区域里如D1:D10则可以用方法二的变体SUMPRODUCT(--ISNUMBER(SEARCH($D$1:$D$10, A2))) 0这是一个强大的动态公式修改D1:D10区域的关键词判断逻辑自动更新。6.2 判断同时包含多个关键词“与”逻辑除了之前提到的COUNTIFS和FILTER中的乘法逻辑也可以用AND连接多个SEARCH判断。AND(ISNUMBER(SEARCH(北京, A2)), ISNUMBER(SEARCH(客户, A2)))同样关键词多时会很冗长。此时如果关键词在区域E1:E3中可以使用一个数组公式需三键结束AND(ISNUMBER(SEARCH($E$1:$E$3, A2)))这个公式会检查A2是否包含E1、E2、E3中的所有字符串。6.3 避开“通配符”字符本身带来的误判这是一个极易踩坑的地方。如果你的关键词本身包含Excel的通配符字符即星号*、问号?或波浪线~直接使用SEARCH或COUNTIF会出错。*和?会被解释为通配符。~是转义字符。例如你想判断单元格是否包含字符串“文件*.txt”。如果你写ISNUMBER(SEARCH(文件*.txt, A2))Excel会把*当作通配符可能匹配到“文件123.txt”、“文件abc.txt”等这并非你的本意。解决方案在通配符前加上波浪线~进行转义。你需要搜索的字符串应写为文件~*.txt。所以公式应为ISNUMBER(SEARCH(文件~*.txt, A2))对于COUNTIF函数条件参数是文本字符串同样需要转义COUNTIF(A2, *文件~*.txt*) 0实操心得在编写涉及用户自由输入内容或文件名的判断逻辑时养成先检查关键词是否包含*、?、~的习惯。一个健壮的公式应该能处理这种情况。你可以用SUBSTITUTE函数批量替换ISNUMBER(SEARCH(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(关键词, ~, ~~), *, ~*), ?, ~?), A2))。这个嵌套将关键词中的~先替换成~~再把*和?分别替换成~*和~?确保它们被当作普通字符处理。6.4 处理大小写、空格与不可见字符大小写如前所述用SEARCH忽略大小写用FIND区分大小写。根据需求选择。空格用户输入经常有多余空格。SEARCH(关键词, A2)是严格匹配“关键词”这三个连续字符。如果单元格里是“关键词”中间有空格就找不到。一个常见的清理步骤是先用TRIM函数去除首尾空格再用SUBSTITUTE函数去掉所有中间空格ISNUMBER(SEARCH(关键词, SUBSTITUTE(TRIM(A2), , )))。但注意这也会把合法的词组空格去掉需谨慎。不可见字符从网页或系统导出的数据常包含换行符CHAR(10)、制表符CHAR(9)等。它们会影响查找。可以用CLEAN函数移除大部分非打印字符ISNUMBER(SEARCH(关键词, CLEAN(A2)))。对于顽固字符有时需要结合CODE和SUBSTITUTE函数进行针对性清理。7. 综合案例构建一个智能反馈分类器让我们把所有知识串联起来解决一个实际问题自动化处理客服反馈工单。场景你有一张反馈表A列是工单IDB列是用户反馈的原始文本。你需要根据反馈内容自动在C列标记其“问题类型”类型规则如下包含“登录”、“密码”、“账号” - 标记为“账户问题”包含“支付”、“扣款”、“退款” - 标记为“支付问题”包含“卡顿”、“闪退”、“崩溃” - 标记为“性能问题”包含“建议”、“希望”、“期待” - 标记为“功能建议”以上都不包含 - 标记为“其他问题”同时如果反馈中同时包含“紧急”或“尽快”则在D列标记“加急”。步骤一建立关键词对照表在另一个工作表如Sheet2中建立规则问题类型关键词1关键词2关键词3账户问题登录密码账号支付问题支付扣款退款性能问题卡顿闪退崩溃功能建议建议希望期待步骤二使用IFSUMPRODUCT进行多条件分类C列公式在C2单元格输入以下公式并向下填充IF(SUMPRODUCT(--ISNUMBER(SEARCH(Sheet2!$B$2:$D$2, $B2))), Sheet2!$A$2, IF(SUMPRODUCT(--ISNUMBER(SEARCH(Sheet2!$B$3:$D$3, $B2))), Sheet2!$A$3, IF(SUMPRODUCT(--ISNUMBER(SEARCH(Sheet2!$B$4:$D$4, $B2))), Sheet2!$A$4, IF(SUMPRODUCT(--ISNUMBER(SEARCH(Sheet2!$B$5:$D$5, $B2))), Sheet2!$A$5, 其他问题))))公式拆解SUMPRODUCT(--ISNUMBER(SEARCH(Sheet2!$B$2:$D$2, $B2)))判断B2单元格的文本是否包含Sheet2中B2:D2这个区域即“账户问题”的三个关键词中的任意一个。如果包含SUMPRODUCT结果大于0在IF函数中视为TRUE。如果为TRUE则返回Sheet2!$A$2即“账户问题”。如果为FALSE则进入下一个IF判断是否为“支付问题”以此类推。如果所有类型都不匹配则返回“其他问题”。步骤三标记加急反馈D列公式在D2单元格输入公式并向下填充IF(OR(ISNUMBER(SEARCH(紧急, $B2)), ISNUMBER(SEARCH(尽快, $B2))), 加急, )步骤四应用条件格式高亮加急工单选中A到D列的数据区域设置条件格式公式为$D2加急并设置一个醒目的填充色。通过这个案例你将文本判断、逻辑函数、数组运算和条件格式结合了起来构建了一个虽然基础但非常实用的自动化数据处理流程。当新的反馈录入B列时C列和D列会自动完成分类和标记加急项也会自动高亮极大地提升了工作效率。判断单元格是否包含某些字符这个看似简单的需求背后是Excel文本处理逻辑的基石。从基础的SEARCH/FIND到面向区域的COUNTIF再到动态数组函数FILTER以及条件格式的可视化应用每一层工具都对应着不同的场景和效率需求。真正掌握它们的关键不在于死记硬背函数语法而在于理解“查找位置”、“条件计数”、“逻辑判断”这些核心概念是如何串联的。下次当你面对杂乱的数据时不妨先问自己我是要找出它们数出它们标记它们还是提取它们想清楚了终点选择通往那里的函数路径就会清晰很多。

Excel模糊查找全攻略：SEARCH、COUNTIF与FILTER函数实战应用

相关推荐

终极跨平台资源下载神器：15分钟搞定视频号、抖音、小红书内容保存

Unlock Music：浏览器中一键解密加密音乐文件，重获音乐自由

手机号查询QQ号终极指南：3分钟快速找回失联社交账号

更多资讯

Labelme启动报错No Qt bindings解决指南

用 MarkdownInstance 泛型为 Astro.glob() 读取的 Markdown 文件声明强类型

VS Code+Anaconda+PyTorch环境搭建全攻略：从零到跑通第一个神经网络

微信小程序点餐系统毕业设计：Java后端与数据库实战

用LLM自动生成性能测试报告：Prompt工程与数据流水线实战

菲涅尔波带片设计指南：从衍射原理到微纳加工实战

今日更新

CMOS设计三大物理效应：体效应、沟道长度调制与亚阈值导电

微信小程序在线阅读毕设全解析：Java后端+MySQL+避坑实战

Android音频问题五层分析法：从App到Codec的系统化诊断

本周精选

CMOS设计三大物理效应：体效应、沟道长度调制与亚阈值导电

微信小程序在线阅读毕设全解析：Java后端+MySQL+避坑实战

Android音频问题五层分析法：从App到Codec的系统化诊断

本月热门

我发现了一个新思路：用 Remotion + Claude Code 像写代码一样自动化生成短视频

Windows下 Codex 中 Chrome 和 Computer Use 插件不可用问题排查及解决参考方式：TaoToken 统一 Key 配置与验证

2026 大模型集体涨价：用 Python 做企业 Token 成本测算与选型避坑（附配置）

读完文章，想聊聊您的网站？