Excel：如何按文本匹配百分比高亮单元格？

核心思路 #

针对企业名称的变体（标点、连接词、后缀差异），先统一文本格式，再通过计算核心内容的匹配比例，结合条件格式实现按阈值（如50%、75%）高亮相似单元格。

步骤1：预处理文本（可选但推荐） #

新建一列（比如B列），统一清理名称中的干扰项，公式如下：

=SUBSTITUTE(TRIM(CLEAN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,",",""),".",""),"LLC",""),"Inc",""))),"and","&")

这个公式会：

• 去除逗号、句号等标点
• 删除常见后缀（LLC、Inc）
• 将"and"统一替换为"&"
• 清理多余空格

步骤2：设置条件格式高亮相似单元格 #

选中A列（或预处理后的B列），按以下操作设置条件格式：

1. 点击「条件格式」→「新建规则」→「使用公式确定要设置格式的单元格」
2. 输入对应阈值的公式（以75%相似度为例，假设预处理列是B列，当前行是B2）：

=MAX(IFERROR(COUNT(FILTERXML("<t><s>"&SUBSTITUTE(B$2," ","</s><s>")&"</s></t>","//s[contains('"&SUBSTITUTE(B2," ","|")&"',.)]"))/MAX(COUNTA(FILTERXML("<t><s>"&SUBSTITUTE(B$2," ","</s><s>")&"</s></t>","//s")),COUNTA(FILTERXML("<t><s>"&SUBSTITUTE(B2," ","</s><s>")&"</s></t>","//s"))),0))>=0.75

3. 选择高亮样式（比如填充黄色），点击确定。

调整匹配阈值 #

• 要设置50%匹配度，把公式末尾的0.75改成0.5即可
• 若只关注核心合伙人姓名，可在预处理公式中再去掉连接符：=SUBSTITUTE(原预处理公式,"&","")，只保留姓名部分计算相似度

注意事项 #

• 旧版Excel需按Ctrl+Shift+Enter输入数组公式，新版Excel自动支持动态数组
• 数千行数据可能导致公式计算较慢，建议先完成预处理列，再基于预处理列设置条件格式

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