Excel基于单元格当前值锁定/允许输入的实现问题

问题原因 #

• 直接用=B5="REQ"做当前单元格（如B5）的数据验证公式会报错，核心原因是Excel数据验证规则禁止公式直接引用设置验证的单元格本身——这会形成循环引用：验证逻辑需要在用户输入前判断条件，但引用自身的话无法确定初始状态，触发Excel的循环引用错误。
• 跨单元格验证（如J16引用B5）能正常工作，是因为公式引用的是其他单元格，不存在循环引用，Excel可以正常计算条件是否成立。

实现方法 #

方法一：直接复用原VLOOKUP逻辑作为验证条件 #

跳过依赖当前单元格的REQ标记，直接把原判断逻辑嵌入数据验证，和标记公式保持一致：

1. 选中需要设置验证的单元格（如B5）
2. 打开「数据验证」对话框，选择「自定义」类型
3. 在公式栏输入：

   =NOT(ISNA(VLOOKUP($A5,Sheet1!$A$5:$DB$76,COLUMN(B5),FALSE))) AND VLOOKUP($A5,Sheet1!$A$5:$DB$76,COLUMN(B5),FALSE)="x"
   

   用COLUMN(B5)替代原公式里的C$1，自动适配当前单元格的列索引，确保验证逻辑和标记逻辑完全匹配。
4. 设置出错警告，提示用户仅能在REQ标记的单元格输入内容。

方法二：辅助列+间接引用（适配已有的标记公式） #

如果已经用原公式生成了大量REQ/BLOCK标记，可借助辅助列避开循环引用：

1. 在同一行的空白列（如Z列）保留原标记公式，比如Z5单元格：

   =IF(ISNA(VLOOKUP($A5,Sheet1!$A$5:$DB$76,B$1,FALSE )), "", IF(VLOOKUP($A5,Sheet1!$A$5:$DB$76,B$1,FALSE)="x","REQ","BLOCK"))
   

2. 选中B5，打开数据验证选择「自定义」，输入公式：

   =Z5="REQ"
   

   通过引用辅助列的标记结果，规避循环引用问题。

批量设置技巧 #

选中所有需要验证的目标区域，再设置上述公式，Excel会自动调整相对引用（如$A5、COLUMN(B5)），批量完成验证规则的应用。

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