求ARRAYFORMULA实现逐行验证条件下拉选项有效性

一、基于现有选项列（R-HI列）的验证方法 #

用下面的数组公式就能实现逐行自动验证，脚本新增行后会自动生效，完全不用手动复制：

=ARRAYFORMULA(IF(ROW(A:A)=1,"验证结果",IF(ISBLANK(A:A),"",IF(ISNUMBER(MATCH(B:B, INDIRECT("R"&ROW(A:A)&":HI"&ROW(A:A)), 0)),"OK","FALSE"))))

公式细节： #

• IF(ROW(A:A)=1,"验证结果")：给第一行设置验证列的表头
• IF(ISBLANK(A:A),"")：A列主下拉为空时，验证列留空，避免无意义的计算
• INDIRECT("R"&ROW(A:A)&":HI"&ROW(A:A))：动态锁定当前行的R到HI列范围，确保每一行只检查自己对应的选项集合
• MATCH(B:B,...)：检查B列的值是否在当前行的选项范围内，找到就返回数字位置，没找到返回错误；用ISNUMBER判断后，返回OK或FALSE

二、无选项列的替代实现方案 #

如果不想单独保留R-HI这类选项列，可直接从主下拉对应的数据源中验证（假设主选项和子选项的映射关系存在Sheet2中：A列是主选项，B列是对应子选项）：

=ARRAYFORMULA(IF(ROW(A:A)=1,"验证结果",IF(ISBLANK(A:A),"",IF(COUNTIF(FILTER(Sheet2!B:B, Sheet2!A:A=A:A), B:B)>0,"OK","FALSE"))))

公式细节： #

• FILTER(Sheet2!B:B, Sheet2!A:A=A:A)：根据当前行A列的主选项，筛选出所有对应的有效子选项
• COUNTIF(...,B:B)：统计B列值在筛选结果中的出现次数，大于0说明是有效选项，返回OK，否则返回FALSE

内容的提问来源于stack exchange，提问作者Miroslav Daniš