Excel 怎么用通配符做模糊查找和统计
统计含某个词的条目不用一条条筛。星号、问号、波浪号这三个符号怎么用,条件怎么放进单元格再引用,一次说清。
表里几百行,只想统计含某个词的条目,一条条筛太慢。通配符就是为这件事准备的:它写在条件里,让文本按包含、开头、结尾、固定字数去匹配,配合统计函数一次算出结果。
入口在哪
- 通配符没有开关,是直接写在条件里的三个符号:星号代表任意多个字符,问号代表一个字符,波浪号用来取消通配。
- 常用在查找替换、COUNTIF 与 COUNTIFS、SUMIF 与 SUMIFS、AVERAGEIF、SEARCH,以及 VLOOKUP 的近似匹配模式。
- 它们只对文本有效。纯数字、日期、错误值不会按通配符匹配,得先转成文本。
具体步骤
- 先把要找的规律说清楚:是包含某词、以某词开头、以某词结尾,还是固定几个字。
- 包含写成两侧加星号,开头写成词后加星号,结尾写成词前加星号。
- 固定字数用问号,比如三位编号就写三个问号,前面加一个字母再补一个字母。
- 真要找星号或问号本身,就在它前面加波浪号,这个符号的作用是取消通配。
- 统计个数用 COUNTIF,求和用 SUMIF。把条件写进一个空白单元格,公式里引用这个单元格,就不用在公式里写引号。
- 查找替换里要勾选使用通配符,不勾的时候星号只是普通字符。
- 要排除某些内容,先把包含的算出来,再用总数减掉,比写反向条件稳。
几个坑
- 忘记转义。要找带星号的编号却没加波浪号,结果等于匹配全表。
- 数字没转文本。订单号、长编号常被存成数值或科学计数,通配符匹配不上,先分列或改成文本格式。
- 全角符号。中文输入法下打出的星号和问号是另一套字符,看着一样却不起作用。
- VLOOKUP 的匹配方式。第四个参数决定按精确还是近似匹配,写成精确时通配符不生效。
- FIND 不支持通配符,同样的需求要用 SEARCH。
- 尾随空格。单元格末尾多一个空格,包含匹配就会漏,先用 TRIM 清一遍再统计。
- 把表头算进去了。统计区域的起点选错,结果会多一条。
怎么验证
- 造五行小样本,里面故意放星号、问号、纯数字各一行,逐个条件试,看返回的数字是不是预期的。
- 用筛选里的包含做交叉验证,筛出来的条数应该和公式结果一致。
- 换一个已知答案的区间再算一遍,两次都能对上,再套到全表。
- 把结果和手工数的行数对一遍,差一条通常是表头或空白行被算进去了。
常见问题
为什么 COUNTIF 统计出来是 0
先看两件事:条件里的引号是不是英文半角,以及被统计的那一列是不是文本。数字型数据即使显示成文本的样子也匹配不上。另外从别处粘过来的词可能夹了看不见的字符,重新手打一遍再试。
通配符能不能用在 SUMIFS 里
可以,条件区域和条件成对写就行,写法和 COUNTIFS 一致。要注意求和区域必须和条件区域同尺寸,错开一行就会算出错误结果,而且它不会报错,只会安静地算错。
中文关键词为什么匹配不到
常见原因是词里夹了全角空格或换行,肉眼看不出来。把条件单元格的内容重新打一遍再试;仍然不匹配,就检查被查的那一列是不是有多余的前后空格,用 TRIM 处理后再统计。
一句话总结:星号管多个字符、问号管一个字符、波浪号负责转义,先把条件放进单元格,再在五行小样本上验证过才套全表。