Excel中CountIf()处理含波浪号字符串结果不一致问题咨询

DevOps 小满

2026-6-23

Excel中COUNTIFS处理含波浪号字符串的异常解析

问题背景

在Excel中，波浪号（~）是通配符转义字符，通常将其替换为双波浪号（~~）可让MATCH()和COUNTIF()正常搜索含波浪号的字符串。但测试发现：

	B列字符串	C列公式	D列结果	E列公式	F列结果	G列公式	H列结果	I列公式	J列结果
4	{FK*BD6Qc)9j~aHa	`=MATCH($B4, $B:$B, 0)`	#N/A!	`=MATCH(SUBSTITUTE($B4, "~", "~~"), $B:$B, 0)`	4	`=COUNTIFS($B:$B, $B4)`	0	`=COUNTIFS($B:$B, SUBSTITUTE($B4, "~", "~~"))`	1
5	;.D~n[sg9#?$}z3` \| `=MATCH($B5, $B:$B, 0)` \| #N/A! \| `=MATCH(SUBSTITUTE($B5, "~", "~~"), $B:$B, 0)` \| 5 \| `=COUNTIFS($B:$B, $B5)` \| 0 \| `=COUNTIFS($B:$B, SUBSTITUTE($B5, "~", "~~"))` \| 1 \|
6	JFxa7V9."Ap~/Q2g	`=MATCH($B6, $B:$B, 0)`	#N/A!	`=MATCH(SUBSTITUTE($B6, "~", "~~"), $B:$B, 0)`	6	`=COUNTIFS($B:$B, $B6)`	1	`=COUNTIFS($B:$B, SUBSTITUTE($B6, "~", "~~"))`	0
7	dP4%5>by{Bw#Vt~D	`=MATCH($B7, $B:$B, 0)`	#N/A!	`=MATCH(SUBSTITUTE($B7, "~", "~~"), $B:$B, 0)`	7	`=COUNTIFS($B:$B, $B7)`	1	`=COUNTIFS($B:$B, SUBSTITUTE($B7, "~", "~~"))`	0

这不是Excel Bug，而是MATCH()和COUNTIFS()的通配符解析逻辑差异导致：

MATCH()（精确匹配模式，match_type=0）：
会将~、*、?均视为特殊字符，无论~后跟随的是什么内容，都需要转义（~→~~、*→~*、?→~?）才能精确匹配实际字符。
COUNTIFS()：
仅将*、?视为通配符，~仅在跟随*、?或~时才需要转义；若~后是普通字符，会直接被当作普通字符处理，无需转义。同时，COUNTIFS()默认启用通配符模式，未转义的*、?会触发模糊匹配。

结合测试数据具体说明：

行4、5：字符串含*/?通配符，未转义时COUNTIFS()将*/?当作通配符模糊匹配，无法命中原字符串（原字符串的*/?是实际字符），返回0；转义~后，通配符*/?刚好匹配到原字符串中的实际*/?，返回1。
行6、7：字符串仅含~且~后为普通字符，未转义时COUNTIFS()直接精确匹配原字符串，返回1；转义~为~~后，条件与原字符串的~不匹配，返回0。

两类字符串会出现此类异常：

根据字符串是否含通配符，分场景处理：

字符串不含*/?：直接使用原字符串作为条件，无需转义~
```
=COUNTIFS($B:$B, $B6)
```
字符串含*/?：同时转义所有特殊字符（~→~~、*→~*、?→~?），确保所有字符被当作普通字符匹配
```
=COUNTIFS($B:$B, SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($B4, "~", "~~"), "*", "~*"), "?", "~?"))
```
通用解决方案（适配所有场景）：使用SUMPRODUCT()替代COUNTIFS()，利用精确比较逻辑，无需处理转义
```
=SUMPRODUCT(--($B:$B=$B4))
```
注：SUMPRODUCT()会逐单元格精确对比，不受通配符规则影响，适合处理含特殊字符的计数需求

内容的提问来源于stack exchange，提问作者NewSites