如何针对单元格与数据验证列表反向校验？求指定规则的无重复填充公式

嘿，这个需求我刚好实操过，给你两个靠谱的方案，既能实现和A1的反向校验，还能保证C1-C3的内容完全不重复：

如果你的Excel是旧版本（比如2019及更早），可以给C1、C2、C3分别设置以下公式：

• C1单元格公式：

  =INDEX({"a","b","c","d"},MATCH(TRUE,ISNA(MATCH({"a","b","c","d"},A1,0)),0))
  

  输入完成后需要按 Ctrl+Shift+Enter 确认（这是数组公式的触发方式，新版Excel可能直接回车就行）

• C2单元格公式：

  =INDEX({"a","b","c","d"},MATCH(TRUE,ISNA(MATCH({"a","b","c","d"},$A$1:$C$1,0)),0))
  

• C3单元格公式：

  =INDEX({"a","b","c","d"},MATCH(TRUE,ISNA(MATCH({"a","b","c","d"},$A$1:$C$2,0)),0))
  

公式逻辑说明 #

• {"a","b","c","d"} 是你数据验证里的完整选项列表，和A1的下拉选项完全对应；
• 内层的MATCH会检查每个选项是否等于A1（或已填充的C1/C2），不等于的会返回#N/A；
• ISNA把这些#N/A转换成TRUE，再用外层的MATCH找到第一个TRUE对应的选项，就是我们需要的不重复内容；
• 每往下一个单元格，就会排除前面已经出现过的内容，确保C1-C3完全不重复。

如果用的是支持动态数组的Excel版本，那一步就能搞定：

直接在C1单元格输入以下公式，它会自动溢出填充到C2、C3，不用逐个单元格设置：

=FILTER({"a","b","c","d"},{"a","b","c","d"}<>A1)

公式逻辑说明 #

FILTER函数会直接从完整选项列表里过滤掉和A1相同的内容，剩下的三个选项会自动依次填充到C1、C2、C3里，完全符合你要的逻辑，而且操作更简单。

这两个方案都能完美联动A1的数据验证，只要A1选择下拉列表里的任意选项，C1-C3都会自动更新成剩下的三个不重复内容，完全满足你的需求~

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