Google Sheet(谷歌表格) 设置联动数据验证功能

因为工作中使用 Google Doc 全家桶,在使用 Google Sheet 过程中,有时要使用到数据验证功能,例如

经常还会有这样的场景需求:有两个下拉选择框,根据第一个选择框的内容,第二个选择框自动更新可选的内容列表:

这时如果直接设置数据验证时,Primary 可以选择 Primary 1 到 Primary 3,但 Second 无法根据 Primary 的选择去过滤非关联的数据。

这时我们可以这么做

实现思路:根据用户选择的 Primary 数据,动态生成一列新数据,然后 Second 的数据验证源设置为这一列数据

在 _shelf 表 E1 输入公式:

=arrayformula(if(MainSheet!A2=A1,A2:A4,if(MainSheet!A2=B1,B2:B5,if('MainSheet'!A2=C1,C2:C3,""))))

然后在 MainSheet 表格中,设置 Second 的数据验证源为 E1:E100 即可实现数据联动功能了。

具体 Demo 请访问此处查看。

此外,更多的场景是需要设置多个 Primary,例如在设置一系列商品所属一级分类,然后二级分类根据一级分类过滤。

这时我们可以这么做

增加一个临时文件表。

实现思路:在临时表中根据用户选择的 Primary 数据,动态生成一行 Second 数据,然后设置 Second 的数据源为对应的一行动态数据。

1. 在 _temp 表 A1 输入公式:

=transpose(arrayformula(if(MainSheet!$A2='_shelf'!$A$1,'_shelf'!$A$2:$A$100,if(MainSheet!$A2='_shelf'!$B$1,'_shelf'!$B$2:$B$100,if(MainSheet!$A2='_shelf'!$C$1,'_shelf'!$C$2:$C$100,"")))))

2. 选择 A1 项,然后复制,然后选择 A2 到 AXX(根据实际情况添加),然后粘帖,这时 A1 到 AXX 都会自动识别为公式。

3.在 MainSheet 中选中 B2,设置数据验证源为 _temp 的 1 列,这时就已经实现根据 primary 来过滤 second 了,但是,这还不够,难道需要在 MainSheet 表中一项项的设置 second 的数据验证源?

当然不用,感谢 AD:MD 提供的脚本,我们可以批量设置 second 的数据验证源分别为 _temp 表中对应的行。

最后,搞定 🙂

留下评论