基于现有Color map精确匹配实现单元格条件格式设置

问题场景 #

拥有Color Map表格（大量值对应特定填充色的单元格），以及包含这些值的目标表格，需批量将目标表中每个值对应的单元格，应用Color Map里的匹配填充色，避免手动逐个设置条件格式，无需VBA或外部工具。

可行解决方案 #

方案一：查找全部+格式刷（手动批量操作） #

适合需要快速落地、能接受重复操作的场景：

• 选中Color Map表中某值对应的颜色单元格，点击「格式刷」
• 切换到目标表，按Ctrl+F打开查找框，输入该值，点击「查找全部」
• 按Ctrl+A选中所有查找结果，点击格式刷完成格式应用
• 重复上述步骤，覆盖所有Color Map中的值

方案二：宏表函数+条件格式（半自动化） #

利用Excel内置宏表函数实现格式匹配，无需编写VBA代码：

1. 规范Color Map结构：
   确保Color Map在ColorMap工作表中，A列为匹配值，B列为对应填充色的单元格（已预先设置好颜色）。
2. 定义名称获取颜色代码：
   点击「公式」→「定义名称」，设置：
   • 名称：MatchFillColor
   • 引用位置：=GET.CELL(38,INDEX(ColorMap!$B:$B,MATCH(INDIRECT("RC",FALSE),ColorMap!$A:$A,0)))
     （GET.CELL(38,x)返回单元格填充色代码，INDEX+MATCH定位对应颜色单元格）
3. 设置条件格式：
   • 选中目标表需格式化的区域，点击「条件格式」→「新建规则」→「使用公式确定要设置格式的单元格」
   • 输入公式：=MatchFillColor<>GET.CELL(38,INDIRECT("RC",FALSE))
   • 点击「格式」→「填充」，手动选择Color Map中对应值的颜色（相比方案一，此步骤无需重复查找，效率更高）

方案三：Power Query生成带格式的目标表（全自动化） #

通过Power Query加载数据并批量设置格式，直接输出带颜色的目标表：

1. 加载数据到Power Query：
   将目标表和Color Map分别加载到Power Query编辑器（「数据」→「从表格/区域」）。
2. 合并查询匹配颜色：
   在目标表查询中，点击「合并查询」，选择Color Map查询，以值列为匹配键，仅保留匹配结果。
3. 批量设置填充色：
   展开合并后的Color Map颜色列，选中目标表的值列，点击「格式」→「填充颜色」→「自定义条件格式」，通过M代码批量映射颜色（示例代码如下）：

   = Table.AddColumn(合并后的表, "填充色代码", each 
       if [值列] = "D" then "#FF0000" 
       else if [值列] = "A" then "#00FF00" 
       else "#FFFFFF"
     )
   

   将自定义列的颜色代码应用到值列的填充色后，加载回Excel即可。

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