Excel数据验证不接受相对行引用问题求助

问题原因 #

Excel数据验证的公式解析逻辑和普通单元格公式存在差异：

• 普通单元格公式复制到下方时，相对引用（如$D2）会自动调整行号，因为公式会相对于自身所在单元格更新引用。
• 但数据验证的公式是以应用验证的第一个单元格为基准来解析相对引用的，不会随目标单元格的行号自动调整。直接使用$D2时，验证规则会固定引用第一个单元格对应的D行，而非当前行的D列，因此触发报错。

可行解决方案 #

针对Sharepoint版Microsoft 365 Excel，无需特殊权限，可通过以下两种方式修改公式：

方案1：适配原OFFSET逻辑的修改 #

将原公式中的$D2替换为INDEX($D:$D,ROW())，修改后的公式如下：

=OFFSET($I$2,1,MATCH(INDEX($D:$D,ROW()),$I$2:$O$2,0)-1,COUNTA(OFFSET($I$2,1,MATCH(INDEX($D:$D,ROW()),$I$2:$O$2,0)-1,20)),1)

核心改动说明：

• ROW()会返回当前设置验证的单元格所在行号
• INDEX($D:$D,ROW())能动态获取当前行D列的单元格值，实现每行验证规则对应同行的"Object Type"

方案2：更简洁的动态数组写法（适配365版本） #

利用Excel 365的动态数组功能，用FILTER函数替代OFFSET逻辑，公式更简洁清晰：

=FILTER($I$3:$O$22,$I$2:$O$2=INDEX($D:$D,ROW()))

该公式会自动筛选出与当前行D列值匹配的列中所有非空内容，无需手动计算数据行数。

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