Google Sheets如何实现级联式动态数据验证功能

问题描述 #

在名为Product Types的工作表中存在如下结构的数据集：

在名为Assets的工作表中，A列已设置数据验证规则，仅允许输入或选择Product Types表A列（不含表头）的列表项。需要实现的效果：当Assets表A列完成选项选择后，B列自动生成动态数据验证下拉列表，仅展示Product Types表中与所选值对应表头列下的所有有效值。

效果示例：

• 若Assets表A列选择Laptops，则B列的数据验证选项仅为Product Types表Laptops列下的Dell和Apple
• 若Assets表A列选择Desktops，则B列数据验证仅允许选择Dell和HP

现有一段在普通单元格内可正常返回对应列逗号分隔值的公式，但无法直接在数据验证设置栏中生效，公式如下：

=ARRAYFORMULA(IFERROR(VLOOKUP(A2, TRANSPOSE({'Product Types'!A1:M1; 
 REGEXREPLACE(TRIM(QUERY(IF('Product Types'!A2:M<>"", 'Product Types'!A2:M&",", )
 ,,999^99)), ",$", )}), 2, 0)))

需求是调整该公式或给出其他可行方案，实现上述动态级联数据验证效果。
备注：新手用户遇到Markdown表格编辑时可正常预览、发布后无法渲染的问题。

小提示：Markdown表格无法渲染通常是因为表格和前后的正文之间没有留空行，在表格上下各加一个空行即可正常显示。

可行方案 #

原有公式无法在数据验证中生效的核心原因：Google Sheets数据验证的下拉列表规则无法识别单个单元格内逗号拼接的文本作为选项，仅支持识别一维单元格区域/数组返回值作为下拉选项。以下两种方案均可直接落地：

方案1：动态范围下拉（无需辅助列，新手友好） #

该方案直接返回非空值的动态数组作为下拉选项源，不需要修改现有表结构，操作步骤：

1. 打开Assets工作表，选中B列需要设置下拉规则的所有单元格（例如从B2开始，选中所有后续需要填写资产数据的行）
2. 点击顶部菜单栏「数据」→「数据验证」→「添加规则」
3. 规则类型选择「下拉列表（基于范围）」，在范围输入框中填入以下公式：

=FILTER(INDEX('Product Types'!$A$2:$M,0,MATCH(A2,'Product Types'!$A$1:$M$1,0)),INDEX('Product Types'!$A$2:$M,0,MATCH(A2,'Product Types'!$A$1:$M$1,0))<>"")

4. 将「如果数据无效」选项设置为「拒绝输入」，点击保存即可生效。

• 公式逻辑：先用MATCH定位A2所选值在Product Types表头行对应的列序号，再用INDEX提取该列从第2行开始的所有内容，最后用FILTER剔除空单元格，返回纯有效值的一维数组作为下拉选项源。后续Product Types表在A-M列范围内新增品类、品牌值时，下拉列表会自动同步更新，无需修改规则。

方案2：自定义公式校验（适合不需要下拉提示、仅做输入合法性校验的场景） #

如果不需要显示下拉选项列表，仅需要限制B列输入的内容必须属于对应列的有效值，可以选择「自定义公式为」的规则类型，输入以下公式：

=COUNTIF(INDEX('Product Types'!$A$2:$M,0,MATCH(A2,'Product Types'!$A$1:$M$1,0)),B2)>0

保存后，B列输入不属于对应品类的品牌值时会直接被拒绝。

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