★ FEATURED ARTICLE Excel 里那三个通配符我见过太多人用了好几年都没真正搞明白。有人用*做查找替换结果把整张表都改乱了有人写COUNTIF统计带星号的产品编号怎么算都是错的还有人压根不知道~是干什么的看到公式里出现个波浪号以为是手滑打错了。这三个符号——星号、问号、波浪号——看着不起眼但它们决定了你在查找、筛选、条件统计、数据清洗时到底是精准命中还是全军覆没。这篇内容就是把*、?、~的匹配原理掰开揉碎讲清楚配上可以直接抄作业的实战场景和踩坑记录不管你是刚接触 Excel 的新手还是天天跟公式打交道的老手都能从中找到自己漏掉的那块拼图。1. 三个符号各自管什么先把职责边界划清楚很多人把通配符当成一个笼统的概念觉得反正就是模糊匹配结果用的时候凭感觉来出了问题也不知道从哪查。实际上*、?、~三者的职责完全不同一个是管数量不限一个是管数量固定一个是管转义还原。把这三条边界刻在脑子里后面所有场景都是在这三条线上做组合。1.1 星号*代表零个或多个任意字符*的核心语义是匹配任意长度的任意字符序列包括空。注意包括空这三个字这是很多人忽略的关键点。也就是说A*既能匹配A也能匹配ABC、A123、A 后面跟一万个字符。它不要求星号位置后面必须有内容。这个特性在实际使用中会带来一个非常隐蔽的后果当你用*做包含判断时它天然就是包含语义。比如COUNTIF(A:A,*苹果*)统计的是单元格里任意位置出现苹果的个数前后有没有其他字符都无所谓。但反过来如果你想匹配以苹果结尾写*苹果是对的想匹配以苹果开头写苹果*也是对的。问题出在有人写*苹果*却以为自己写的是精确等于苹果——这就是典型的语义混淆。我个人的经验是看到*先问自己一句我到底要不要它匹配空。如果要放心用如果不要就得配合其他条件把空值排除掉比如用COUNTIFS加一个条件或者干脆改用SEARCH配合ISNUMBER来判断。1.2 问号?代表恰好一个任意字符?的语义是匹配且仅匹配一个任意字符。一个问号对应一个字符位置两个问号对应两个字符位置以此类推。它和*最大的区别在于数量确定性。这个确定性在什么场景下最值钱固定长度编码的校验和提取。比如产品编号规则是两位字母 四位数字你想筛选出所有符合这个格式的编号写??####这里#在 Excel 通配符体系里不生效实际要用??[0-9][0-9][0-9][0-9]配合支持正则的函数或者用?配合LEN做长度校验。更常见的用法是??-??这种匹配两字符-两字符的短横线格式。?还有一个容易被低估的用途排除空单元格。因为?至少要求一个字符所以COUNTIF(A:A,?)统计的是非空且长度为1及以上的单元格数量——注意它统计的是文本型内容纯数字单元格在部分函数里行为会有差异这个后面踩坑章节会细说。1.3 波浪号~不是通配符是通配符的刹车~严格来说不是通配符它是转义符作用是让紧跟其后的*、?、~失去特殊含义变回普通字符。这是三者里最容易被忽视、但一旦缺位就会出大问题的角色。举个最典型的例子你的数据里有一列产品型号叫A*01、A*02星号是型号的一部分不是通配符。这时候如果你直接写COUNTIF(A:A,A*01)Excel 会把*当成任意字符结果A12301、Axyz01全被算进去。正确写法是COUNTIF(A:A,A~*01)用~把星号摁住让它只匹配字面意义上的星号。同理问号要匹配字面问号写~?波浪号本身要匹配字面波浪号写~~。这个规则简单但实战中漏写的概率极高因为大多数人的数据里星号不常见一旦遇到就懵了。符号语义匹配示例不匹配示例*零个或多个任意字符A*匹配A、AB、A123无几乎全能匹配?恰好一个任意字符A?匹配AB、A1A、ABC~转义其后符号A~*匹配A*AB、A1232. 匹配原理拆解Excel 到底是怎么对的理解了职责边界还得知道 Excel 内部是怎么执行匹配的否则遇到明明公式没写错但结果就是不对的情况你连排查方向都没有。这一节讲的是匹配引擎的行为逻辑偏原理但每一条都直接对应实战中的坑。2.1 通配符只在文本比较语境下生效这是第一条铁律通配符不是万能的它只在函数或功能明确支持文本模式匹配时才起作用。COUNTIF、SUMIF、COUNTIFS、SUMIFS、AVERAGEIF、AVERAGEIFS、SEARCH、MATCH、VLOOKUP第四参数为 FALSE 或省略时的部分场景、HLOOKUP、XLOOKUP匹配模式选通配符、查找替换对话框、筛选器里的文本筛选——这些支持。而FIND不支持通配符它是精确查找SUBSTITUTE不支持大部分数组公式里的直接等号比较也不支持。为什么这个区分重要因为很多人写A1*苹果*想判断 A1 是否包含苹果结果永远返回 FALSE——等号比较是精确匹配星号就是星号本身。正确做法是COUNTIF(A1,*苹果*)0或者ISNUMBER(SEARCH(苹果,A1))。这个坑我见过至少几十次几乎每个新手都会踩一遍。2.2 匹配是整串匹配而非子串搜索COUNTIF这类条件函数的匹配逻辑是拿整个单元格内容和模式做整串比对不是在单元格里找子串。所以COUNTIF(A:A,苹果)只统计内容恰好等于苹果的单元格COUNTIF(A:A,*苹果*)才统计包含苹果的。这一点和SEARCH函数不同——SEARCH(苹果,A1)是子串搜索找到就返回位置。理解这个差异能帮你选对工具要整串符合某模式用条件函数要串里含有某片段用 SEARCH 或配合*片段*。两者在简单场景下结果一样但在边界场景比如空值、纯数字、前后空格下行为会分叉。2.3 大小写敏感性条件函数不敏感SEARCH 不敏感FIND 敏感COUNTIF、SUMIF、SEARCH在匹配时不区分大小写FIND区分。这意味着COUNTIF(A:A,apple)会把Apple、APPLE都算进去。如果你的业务要求区分大小写比如某些编码体系里大小写代表不同含义条件函数就靠不住了得换FIND配合SUMPRODUCT或者用EXACT做精确比对。这个特性没有好坏只有适不适合。大多数日常统计场景不区分大小写反而更方便但做编码校验、密码类比对时就必须警惕。2.4 数字与文本的隐式转换会干扰匹配Excel 在匹配时会做隐式类型转换这带来一个非常隐蔽的坑纯数字单元格在COUNTIF里用文本模式匹配时行为可能和你的预期不一致。比如 A 列是数字123你写COUNTIF(A:A,1*)有的版本会把它算进去把数字转成文本123再匹配有的场景下又不会。更稳妥的做法是先用TEXT函数把数字转成文本再匹配或者明确知道自己在做什么。我个人的处理习惯是凡是涉及通配符的统计先确认目标列的数据类型是否统一。如果一列里既有文本又有数字先做清洗别指望通配符帮你兜底。3. 查找替换与筛选里的通配符最容易翻车的地方查找替换对话框和筛选器是通配符使用频率最高的两个入口也是翻车率最高的地方。原因很简单这两个功能是批量操作一旦匹配范围错了影响的是整张表而且很多操作不可逆替换之后原值就没了。这一节把这两个场景的匹配逻辑和防翻车手法讲透。3.1 查找替换对话框默认开启通配符且没有预览在查找替换对话框里点选项你会看到一个使用通配符的勾选框默认是勾上的。这意味着你输入*就是通配符输入?就是单字符通配。很多人不知道这个默认状态想查找字面星号却查不到或者想查找字面问号结果匹配了一大片。更麻烦的是查找替换没有实时预览匹配结果的功能至少常规版本没有你点全部替换之前根本不知道会改掉多少单元格。我的做法是先点查找全部看结果列表里有多少条、都是什么内容确认无误再替换。这个习惯帮我避免过好几次批量事故。如果要查找字面星号输入~*查找字面问号输入~?查找字面波浪号输入~~。这个转义规则和公式里完全一致记一套就够。3.2 筛选器里的文本筛选模式写法和公式一致自动筛选里的文本筛选→包含/开头是/结尾是这些选项底层用的就是通配符逻辑。你选包含输入框里填的内容会被自动包上*内容*选开头是包上内容*。但如果你选自定义筛选就可以手写模式这时候?和~都能用上。这里有个细节值得注意筛选器对空值的处理。用包含筛选时空单元格通常不会被匹配因为空里没有内容可包含但用等于配合*时空单元格可能被算进去因为*匹配空。这个差异在做数据核对时会导致数量对不上排查时优先怀疑空值。3.3 一个真实踩坑替换把型号里的星号全干掉了早些年我处理一批产品数据型号列里有X*200、X*300这种带星号的编码。当时要做的是把型号里的X统一替换成Y我直接在查找框输入X替换框输入Y点全部替换。结果型号变成了Y*200——看起来没问题问题在于同一张表里还有一列备注里面写着详见X*200规格书也被一起替换了而且更糟的是有些备注里写的是X 系列X 后面跟空格替换后变成Y 系列语义全变了。这次教训让我养成了一个习惯批量替换前先选中目标列再执行替换而不是全表替换。Excel 的查找替换默认作用范围是整个工作表选中区域后才会限定范围。这个操作只多花两秒钟但能避免大量返工。4. 条件统计函数里的通配符实战COUNTIF 家族的正确打开方式COUNTIF、SUMIF、COUNTIFS、SUMIFS、AVERAGEIF这一家族是通配符的主战场。日常工作中统计包含某关键词的订单数汇总某类前缀的金额这类需求全靠它们。但这一家族的参数结构有个容易搞混的地方条件区域和条件本身是分开的两个参数通配符写在条件参数里不是区域里。这一节按场景拆开讲。4.1 单条件包含统计*关键词*的标准写法统计 A 列中包含退货的单元格数量COUNTIF(A:A,*退货*)这里*退货*是条件参数前后星号保证退货出现在任意位置都能命中。如果只写退货就变成精确等于退货包含申请退货退货处理中的都不算。对应的求和写法SUMIF(A:A,*退货*,B:B)意思是 A 列包含退货的行把 B 列对应值加起来。注意SUMIF的参数顺序是条件区域、条件、求和区域和COUNTIF的区域、条件不同别搞混。4.2 多条件组合COUNTIFS 里每个条件都能带通配符COUNTIFS支持多组区域-条件配对每一组条件都可以独立使用通配符COUNTIFS(A:A,*退货*,B:B,2024-01-01,C:C,??-??)这个公式统计的是A 列包含退货、B 列日期大于 2024-01-01、C 列符合两字符-两字符格式的记录数。三个条件各自独立匹配全部满足才计数。这里有个实战技巧条件里的比较运算符和通配符可以混用但要注意引号包裹。2024-01-01整体是一个字符串条件*退货*也是两者在语法上地位相同。如果你要拼接动态条件比如日期来自某个单元格写法是D1注意拼接后整体仍然是字符串通配符部分同理。4.3 排除特定模式用配合通配符想统计不包含某关键词的数量写法是COUNTIF(A:A,*测试*)表示不等于后面跟通配符模式整体语义是不匹配该模式的计数。这个写法在数据清洗时特别有用比如排除所有测试数据、排除所有内部单号。但要注意一个边界配合通配符时空单元格的行为。COUNTIF(A:A,*测试*)通常会把空单元格也算进去因为空不包含测试如果你不想统计空值得再加一个条件COUNTIFS(A:A,*测试*,A:A,)这个细节在核对总数时经常导致差几个数排查时优先看空值。4.4 精确匹配含通配符字符的字面值前面提过数据里如果有字面星号条件要写~*。完整示例如下COUNTIF(A:A,A~*01)统计 A 列中恰好等于A*01的单元格数。如果漏写~写成A*01那么A12301、Axyz01、A 01都会被算进去结果虚高。同理统计包含字面问号的内容COUNTIF(A:A,*~?*)这个模式的意思是任意内容 字面问号 任意内容即包含问号的单元格。5. 查找函数里的通配符VLOOKUP、MATCH、XLOOKUP 的匹配模式查找类函数对通配符的支持情况参差不齐用错了不会报错只会返回错误结果这是最危险的。这一节把常用查找函数的通配符行为逐个说清楚。5.1 VLOOKUP第四参数决定一切VLOOKUP的第四个参数range_lookup控制匹配方式FALSE或0精确匹配支持通配符TRUE或1或省略近似匹配不支持通配符走的是二分查找逻辑很多人以为精确匹配就是完全相等其实在VLOOKUP里FALSE模式下是支持通配符的。比如VLOOKUP(*苹果*,A:B,2,FALSE)这个公式会在 A 列里找第一个包含苹果的单元格返回对应 B 列的值。注意它返回的是第一个匹配项不是全部这是VLOOKUP的固有局限。这里有个坑VLOOKUP用通配符时如果找不到匹配会返回#N/A而不是空。如果你的数据里可能没有匹配项外面要套IFERROR。5.2 MATCH第三参数为 0 时支持通配符MATCH的第三个参数match_type为0时是精确匹配支持通配符MATCH(*苹果*,A:A,0)返回第一个包含苹果的位置。这个用法常和INDEX配合做反向查找INDEX(B:B,MATCH(*苹果*,A:A,0))效果类似VLOOKUP但方向灵活。同样注意只返回第一个匹配。5.3 XLOOKUP需要显式开启通配符模式XLOOKUP的第五个参数match_mode控制匹配模式0精确匹配默认不支持通配符2通配符匹配支持*、?、~-1精确匹配或下一个较小项1精确匹配或下一个较大项也就是说用XLOOKUP做通配符查找必须显式写2XLOOKUP(*苹果*,A:A,B:B,,2)这个设计比VLOOKUP更明确但也更容易忘——默认的0不支持通配符忘了写2就会返回#N/A而且不报语法错误排查起来费劲。函数通配符支持条件典型写法VLOOKUP第四参数为FALSE/0VLOOKUP(*关键词*,A:B,2,FALSE)MATCH第三参数为0MATCH(*关键词*,A:A,0)XLOOKUP第五参数为2XLOOKUP(*关键词*,A:A,B:B,,2)SEARCH原生支持SEARCH(关键词,A1)FIND不支持无6. 踩坑实录那些年通配符给我挖的坑原理讲完了实战也过了一遍但真正让人长记性的永远是踩过的坑。这一节挑几个我印象最深的翻车现场把排查链路完整还原你遇到类似问题时可以照着这个思路走。6.1 坑一COUNTIF统计数字列结果总是差几个现象A 列是订单号有纯数字的也有带字母的我用COUNTIF(A:A,1*)统计以 1 开头的订单结果和手工核对差了十几个。排查过程先怀疑是空值问题加了条件差异缩小但没消失。然后把 A 列复制到新表用ISTEXT和ISNUMBER逐行判断发现纯数字单元格在COUNTIF的文本模式匹配下行为不稳定——部分版本会把数字123转成文本123参与匹配部分版本直接跳过。根因COUNTIF对数字单元格做文本模式匹配时存在隐式转换转换规则依赖版本和具体内容不可靠。修复方案先用辅助列把 A 列统一转成文本TEXT(A1,)再对辅助列做COUNTIF。或者改用SUMPRODUCT配合LEFT做前缀判断SUMPRODUCT(--(LEFT(A1:A1000,1)1))这个写法不依赖通配符对数字和文本一视同仁结果稳定。6.2 坑二VLOOKUP通配符匹配到了错误的第一项现象用VLOOKUP(*苹果*,A:B,2,FALSE)查价格返回的却是苹果醋的价格而我要的是苹果本身。排查过程检查数据发现 A 列里苹果醋排在苹果前面VLOOKUP返回第一个匹配项自然命中了苹果醋。根因VLOOKUP通配符模式只返回第一个匹配无法指定最精确的那个。修复方案如果业务要求优先精确匹配其次模糊匹配得用两层判断IFERROR(VLOOKUP(苹果,A:B,2,FALSE),VLOOKUP(*苹果*,A:B,2,FALSE))先试精确匹配失败再走通配符。这个模式在处理标准名称 变体名称混排的数据时特别管用。6.3 坑三替换操作把转义符本身吃掉了现象数据里有一列备注写着折扣~10%我想把~替换成-在查找框输入~替换框输入-结果提示未找到。排查过程一开始以为是编码问题后来想起~是转义符在查找框里单独一个~会被解释为转义下一个字符但后面没有字符所以匹配失败。根因查找框里的~需要写成~~才能匹配字面波浪号。修复方案查找框输入~~替换框输入-正常替换。这个规则和公式里完全一致但查找替换对话框不会给你任何提示只能靠记忆。6.4 坑四SEARCH和COUNTIF结果对不上现象同一批数据用COUNTIF(A:A,*关键词*)统计出 50 条用SUMPRODUCT(--ISNUMBER(SEARCH(关键词,A1:A1000)))统计出 48 条。排查过程把两个公式的结果逐行比对发现差异出在两行内容上——这两行的关键词前后有不可见字符从网页复制来的零宽空格。COUNTIF的通配符匹配对这类字符不敏感因为*能吞掉它们而SEARCH是精确子串搜索零宽空格插在关键词中间就搜不到了。根因数据源不干净混入了不可见字符两种匹配机制对脏数据的容忍度不同。修复方案先用CLEAN函数清洗数据CLEAN(A1)再做统计。或者统一用COUNTIF的通配符模式它对脏数据的容忍度更高。这个案例说明统计结果对不上时先怀疑数据源再怀疑公式。7. 进阶技巧把通配符和数组、动态数组结合起来基础用法掌握之后通配符还能和现代 Excel 的动态数组函数组合出更强的能力。这一节讲几个进阶玩法适合已经能熟练使用基础公式的读者。7.1 FILTER 通配符动态筛选包含关键词的记录FILTER函数本身的条件参数不直接支持通配符但可以配合ISNUMBER(SEARCH(...))实现FILTER(A2:C100,ISNUMBER(SEARCH(关键词,A2:A100)),无匹配)这个公式返回 A 列包含关键词的所有行。SEARCH找不到时返回错误ISNUMBER把错误转成 FALSE找到时返回位置数字转成 TRUE正好作为FILTER的筛选条件。如果要支持多个关键词的或关系用连接多个ISNUMBER(SEARCH(...))且关系用*连接。这个模式比COUNTIF灵活得多因为FILTER能返回完整记录不只是计数。7.2 通配符 动态数组做批量提取假设 A 列是混合文本你想提取所有符合字母数字格式的片段可以配合TEXTSPLIT、TEXTAFTER等新函数。不过这类需求已经超出通配符的能力边界通配符擅长的是判断是否符合模式不擅长从串里抠出符合模式的片段。抠片段要用正则类思路Excel 原生支持有限通常得靠MIDFIND组合或者 VBA。这里给一个实用边界提醒通配符是匹配判断工具不是内容提取工具。想清楚这一点能帮你少走很多弯路——需要提取时老老实实用文本函数组合别硬套通配符。7.3 用通配符做数据校验的辅助列一个很实用的模式用COUNTIF配合通配符做格式校验辅助列。比如校验产品编号是否符合两位字母四位数字IF(COUNTIF(A1,??[0-9][0-9][0-9][0-9])0,格式正确,格式异常)注意这里[0-9]在 Excel 通配符体系里不生效Excel 通配符只支持*、?、~不支持字符类所以这个写法实际不成立。正确的做法是用LEN配合ISNUMBER分段校验IF(AND(LEN(A1)6,ISNUMBER(VALUE(MID(A1,3,4))),NOT(ISNUMBER(VALUE(LEFT(A1,2)))),NOT(ISNUMBER(VALUE(RIGHT(A1,2))))),格式正确,格式异常)这个例子说明一个重要的认知Excel 通配符的能力边界比正则表达式窄得多它只有*、?、~三个符号没有字符类、没有量词、没有分组。遇到复杂模式校验要么用多个条件组合要么上 VBA 或 Power Query。认清这个边界能避免在错误的方向上死磕。8. 一份可以直接抄的通配符速查与操作清单最后把前面散落各处的规则和操作整理成一份清单日常用的时候对着查比翻文档快。8.1 符号速查表需求写法说明包含某关键词*关键词*前后星号以某内容开头关键词*后星号以某内容结尾*关键词前星号恰好 N 个字符?重复 N 次如???匹配三字符匹配字面星号~*波浪号转义匹配字面问号~?波浪号转义匹配字面波浪号~~双波浪号不包含某关键词*关键词*配合8.2 操作前必查清单确认目标列数据类型是否统一文本/数字混排先清洗确认公式或功能是否支持通配符FIND不支持VLOOKUP精确模式支持批量替换前先查找全部预览并限定作用区域数据里含字面*、?、~时条件里必须加~转义统计结果对不上时优先排查空值和不可见字符XLOOKUP用通配符必须显式写第五参数28.3 几个我反复用到的组合公式COUNTIF(A:A,*D1*)动态关键词统计D1 里填关键词公式自动拼接通配符模式。这个写法比硬编码关键词灵活改关键词不用改公式。SUMIFS(C:C,A:A,*D1*,B:B,E1)多条件动态汇总关键词和日期下限都来自单元格引用。IF(COUNTIF(A1,*~**)0,含星号,不含)判断单元格是否含字面星号。注意模式*~**的拆解第一个*是通配符~*是字面星号最后一个*是通配符整体意思是任意内容 字面星号 任意内容。通配符这东西规则就那么几条但组合起来能覆盖日常工作中绝大部分的模糊匹配需求。真正拉开差距的不是记住规则而是知道每条规则在什么场景下会失效、失效时怎么排查。上面那些踩坑记录和边界提醒才是我觉得比符号表更值钱的部分。下次再遇到统计结果对不上、查找返回错项、替换改乱数据的情况先回到匹配原理那一节对一遍大概率能自己定位到问题。
阅读完成 · 觉得有帮助？
