今天来上一个Excel大招，绝对的大招！——多级下拉菜单。
多级下拉菜单这个问题，在Excel里面并不容易实现，关键问题在于数据有效性验证环节需要对数据源的动态过滤。如果使用了它，那么Excel表格在某些方面上可能会产质的飞跃，至少可以起到如下作用：
- 实现数据的全局参照验证，确保数据关联和一致。
- 人机动态交互效果更好。
- 输入更加智能，减少了使用人的输入量。
- 数据更加规整，为后期数据的分析提供了极大的便利。
在此，以二级菜单为例，我们来看需要达到的效果。
当我们选择了一级菜单后，根据选择的一级菜单项目自动加载二级菜单内容。在此，我们先不考虑使用VBA来实现这个问题。
image
image
以下，我们就来看看一个二级下拉菜单是如何实现的：
1、我们先建立两个区域，一个“销售区域”，一个“门店信息”，如下图。然后选择对应的数据区域按“Ctrl+T”，将这几个区域分别转化成超级表。然后把销售区这个数据表名称改为“销售区”，把门店这个数据表名称改为“门店信息”以便在后面引用。这里我就不一步步的去演示了。最终效果如下，形成了两个超级表区域。在此，我为了演示方便，全部超级表放在了一个工作表里面了，但在实际使用场景中是需要按工作表来做数据表的。
image
2、现在我们来建立一级菜单。具体数据验证操作步骤，就不啰嗦了。关键还是数据源的问题。这里，我们是引用销售区这个超级表的区域。因为这里是引用超级表，涉及结构化引用的问题，这里需要使用INDIRECT()这个函数。而里面的“销售区”，引用的是“销售区”这个超级表。这一步很简单，并不复杂。如果销售区这个表有很多列，那就需要使用超级表的结构化引用，可以参照第三步的那种语法方式。
image
3、同样的方式在二级菜单列开始建立数据验证。这里其它的都不是问题，比较关键的是序列的来源这里了。具体的函数用到了Offset、Match、和countif函数的嵌套。在这里，具体的公式为：
=OFFSET(INDIRECT("门店信息[[#标题],[门店]]"),MATCH(H3,INDIRECT("门店信息[[#数据],[销售区]]"),0),0,COUNTIF(INDIRECT("门店信息[[#数据],[销售区]]"),H3)）
大体意思就是使用offset函数来获取区域，但这部分区域却是有条件的，这个条件就是只获取一级菜单选定的，对应的内容。
这里，需要特别说明的是，如果没有采用超级表时，那么indirect函数这部分，就需要使用区域了，不能再使用表结构化引用。以上的数据源公式，如果用区域来表示，那么就是：
=OFFSET($D$2,MATCH(H3,$E$3:$E$7,0),0,COUNTIF(($E$3:$E$7),H3))
对比以上两种写法，区域化引用看上去更简洁，而结构化引用似乎更要繁杂，可能大家会认为为什么还用结构化引用这种方式呢？答案是，这种结构化引用方式具有更强的适应性和扩展性，不受区域引用这种绝对或者相对的单元格区域，“$E$3:$E$7”以及区域命名这种方式引用无法自动扩展区域。关于超级表的结构化引用，这个是题外话，在此就不展开说了。
image
以上就是全部过程。无论几级菜单都可以此类推来制作。
写在最后：
1、目前，纵观全网，关于多级菜单的制作问题，都是使用以下这种列式表结构引用的方式来制作的。这种数据管理方式存在很大的问题，因为按照这种方式来做数据源的话，随着数据的增加，表会向横向和纵向两个方向扩展，表格会极具的膨胀和混乱。用专业的来说就是有违“三范式”。如果用这种方式来管理数据，那么就是灾难。所以，一定要使用标准的关系数据，任何时候都绝对不推荐以下这种处理方法。
image
2、在EXCEL里面，极力推荐使用超级表来管理数据。实际上，超级表的好处非常多，包括超强的可扩展性，超高的智能化、自动化程度等等。只要使用习惯了，那么很难再切回去使用区域模式了。关于超级表的优势，在此先不展开说了。
3、如果还有其它更好办法，请不吝赐教！
创作不易，转载请注明来源！
网友评论