如何创建条件格式规则高亮匹配多前缀的单元格？

方法1：用辅助区域存前缀（推荐，方便后续修改） #

• 找个空白区域（比如新建一个隐藏工作表Sheet2），把各组前缀分别列出来：
  • A组：Sheet2!A1:A4 填入 109441、109221、108417、45897
  • B组：Sheet2!B1:B3 填入 2451、291877、200000
  • C组：Sheet2!C1:C1 填入 49187
• 选中要高亮的账号单元格区域（比如当前表的A2:A100）
• 打开「条件格式」→「新建规则」→「使用公式确定要设置格式的单元格」
• 输入A组匹配公式：=SUMPRODUCT(--(LEFT(A1,LEN(Sheet2!$A$1:$A$4))=Sheet2!$A$1:$A$4))>0
  • 逻辑：提取账号对应前缀长度的字符，和辅助区域的每个前缀逐一对比，只要有一个匹配就触发格式
• 设置高亮格式（比如填充黄色），点确定
• 重复上述步骤创建B、C组规则，公式替换为对应区域：
  • B组：=SUMPRODUCT(--(LEFT(A1,LEN(Sheet2!$B$1:$B$3))=Sheet2!$B$1:$B$3))>0
  • C组：=SUMPRODUCT(--(LEFT(A1,LEN(Sheet2!$C$1:$C$1))=Sheet2!$C$1:$C$1))>0

方法2：直接在公式里写前缀（无需辅助区域） #

如果不想额外存前缀，直接把前缀数组写进公式：

• 选中目标区域，新建条件格式规则，选「使用公式确定要设置格式的单元格」
• A组公式：=SUMPRODUCT(--(LEFT(A1,LEN({"109441","109221","108417","45897"}))={"109441","109221","108417","45897"}))>0
• B组公式：=SUMPRODUCT(--(LEFT(A1,LEN({"2451","291877","200000"}))={"2451","291877","200000"}))>0
• C组公式：=SUMPRODUCT(--(LEFT(A1,LEN("49187"))="49187"))>0
• 分别设置对应高亮格式即可

关键注意点 #

• 公式里的A1是你选中区域的第一个单元格，要保持相对引用（别加$），这样格式会自动适配整个区域
• 如果账号不在A列，把A1换成对应列的第一个单元格（比如B1）
• 方法1的好处是后续要修改前缀时，直接改辅助区域就行，不用动公式

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