Excel如何基于内置Data Validation列表实现大小写敏感高亮

不需要额外在其他工作表创建列表区域，以下两种方案可直接实现你的需求： #

方案1：硬编码列表常量（无需启用宏，适合普通xlsx文件） #

直接将数据验证里的选项写成数组常量放到条件格式公式中即可，以A列（校验范围A2:A10）为例：

1. 选中需要设置条件格式的A2:A10区域
2. 新建规则，选择「使用公式确定要设置格式的单元格」，输入如下公式：

=AND(NOT(ISBLANK(B2)),NOT(ISNUMBER(MATCH(TRUE,EXACT(A2,{"USA","AUS","CAN","GBR","PRI"}),0))))

3. 设置填充色为红色即可生效。

公式说明： #

• NOT(ISBLANK(B2))适配你的第三条规则：同行B列为空时不触发校验，允许A列留空
• EXACT()函数保证大小写敏感，只有全大写的匹配项才会判定为合法，符合第二条要求
• 大括号{}内直接填入你在数据验证中输入的所有选项，用逗号分隔、双引号包裹即可

优缺点： #

• 优点：无需启用宏，普通xlsx文件即可使用，配置逻辑简单
• 缺点：后续修改数据验证的选项时，需要同步修改条件格式公式里的数组内容，无法自动同步

方案2：宏表函数读取内置验证列表（自动同步修改，适配多列校验） #

如果希望后续修改数据验证选项时条件格式自动同步，无需手动改公式，可以用GET.CELL宏表函数直接读取单元格内置的验证列表：

1. 选中你需要校验的所有数据区域的首个单元格（比如A2），点击「公式」选项卡→「定义名称」
   • 名称填写当前列验证列表
   • 引用位置填写=GET.CELL(17,INDIRECT(ADDRESS(ROW(),COLUMN())))
   • 点击确定保存
2. 回到条件格式设置，输入如下公式：

=AND(NOT(ISBLANK(B2)),A2<>"",NOT(ISNUMBER(MATCH(TRUE,EXACT(A2,TRIM(MID(SUBSTITUTE(当前列验证列表,",",REPT(" ",99)),ROW($1:$100)*99-98,99))),0))))

3. 设置红色填充即可生效。

注意事项： #

• 该方案使用了宏表函数，需要将文件保存为.xlsm启用宏的格式，打开文件时需要允许宏运行才能正常校验
• 如果有多列需要校验，只需要把条件格式的应用范围扩展到对应列即可，无需额外修改公式

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