最近美女同事向我求助,说遇到了一个excel难题。情况是这样的:最近需要让员工填写一些表格,已有确定的选项,只需点选即可。但这些选项是有层级关系的。所以就按照网上教的教程制作了多级联动菜单让员工进行填写,但表格收上来后,仍发现有很多未按要求进行填写,也就是产生了很多的无效数据。
具体见下方的模拟演示:
注:传统的excel多级联动菜单,是运用数据有效性+INDIRECT函数+名称定义 实现的,制作简单,应用广泛。但其也有一个明显的缺点,就是必须逐级向下点选,若用户逆向向上选择,就可能导致出现无效数据。
传统的excel多级联动菜单,若用户逆向向上选择,就可能导致出现无效数据。
那么有没有简单的办法避免上面的问题产生呢?答案是:有的。
思路是:避免客户向上逆向选择,若下级不为空,则不能重新对上级菜单进行选择。
演示效果如下:
避免客户向上逆向选择,若下级不为空,则不能重新对上级菜单进行选择
1、插入两张表
插入两张表:
一张名称为:目标区,用于建立联动菜单;
另一张表名称为:数据源,提供菜单源。
目标区
数据源
2、定义名称:
名称1:选项:用于二、三级菜单。
=IF(ISBLANK(Sheet1!C8),IF(目标区[#标题] Sheet1!B:B = 目标区[[#标题],[一级菜单]],OFFSET(数据源[[#全部],[一级菜单]],1,,COUNTA(数据源[一级菜单])),OFFSET(数据源,0,MATCH(Sheet1!A8, 数据源[#标题],0)-1,COUNTA(OFFSET(数据源,,MATCH(Sheet1!A8, 数据源[#标题],0)-1,,1)),1)))
定义名称
3、设置数据有效性:
名称2:一级菜单。用于一级菜单选项。
选中B8:B11,设置数据有效性,序列,数据来源:一级菜单。
选中C8:D11,设置数据有效性,序列,数据来源:选项。
设置数据有效性
联系客服