如何基于单元格与数据验证列表反向校验？批量填充非选中值

当然有超便捷的公式能搞定这个需求！而且还能完美满足反向校验+无重复的要求，我分两种常用场景给你说明：

直接在C1单元格输入下面的公式，它会自动溢出填充到C2和C3，完全匹配你描述的所有对应关系：

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

• 为什么这个公式好用？
  • 它会自动筛选掉A1选中的选项，剩下的三个值严格按照a→b→c→d的原始顺序填充到C1-C3，正好符合你要的规则：选a就出b/c/d，选b就出a/c/d，以此类推；
  • 天生自带校验属性：C1-C3的内容肯定和A1不重复，而且三个单元格之间也绝对不会有重复值——因为都是从原始的唯一选项列表里筛选出来的；
  • 不用逐个单元格输入公式，一次操作搞定三个位置，效率拉满！

如果你的Excel没有动态数组功能（比如2019及更早版本），可以用以下数组公式，每个单元格分别输入：

• C1单元格（输入完成后，按 Ctrl+Shift+Enter 组合键确认，不是单纯按回车哦）：

  =INDEX({"a","b","c","d"},SMALL(IF({"a","b","c","d"}<>A1,ROW($1:$4)),1))
  

• C2单元格（同样按Ctrl+Shift+Enter确认）：

  =INDEX({"a","b","c","d"},SMALL(IF({"a","b","c","d"}<>A1,ROW($1:$4)),2))
  

• C3单元格（同样按Ctrl+Shift+Enter确认）：

  =INDEX({"a","b","c","d"},SMALL(IF({"a","b","c","d"}<>A1,ROW($1:$4)),3))
  

• 原理说明：先用IF函数找出所有不等于A1的选项位置，再用SMALL按顺序提取第1、2、3个有效位置，最后用INDEX返回对应的值，同样能保证无重复和反向校验的要求。

如果想进一步防止手动修改C1-C3导致不符合规则，可以给C1-C3设置数据验证：

1. 选中C1到C3的单元格区域；
2. 打开「数据验证」对话框，允许类型选择「自定义」；
3. 输入校验公式：

   =AND(C1<>$A$1,COUNTIF($C$1:$C$3,C1)=1)
   

4. 可以设置出错警告，提示用户输入内容不符合规则。
   这个公式会同时检查两个核心条件：当前单元格内容不等于A1，且在C1-C3区域内只出现一次，完美实现反向校验+无重复的双重保障。

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