Excel多级下拉菜单制作

今天来上一个Excel大招,绝对的大招!——多级下拉菜单。

多级下拉菜单这个问题,在Excel里面并不容易实现,关键问题在于数据有效性验证环节需要对数据源的动态过滤。如果使用了它,那么Excel表格在某些方面上可能会产质的飞跃,至少可以起到如下作用:

1、实现数据的全局参照验证,确保数据关联和一致。

2、人机动态交互效果更好。

3、输入更加智能,减少了使用人的输入量。

4、数据更加规整,为后期数据的分析提供了极大的便利。

在此,以二级菜单为例,我们来看需要达到的效果。

当我们选择了一级菜单后,根据选择的一级菜单项目自动加载二级菜单内容。在此,我们先不考虑使用VBA来实现这个问题。

以下,我们就来看看一个二级下拉菜单是如何实现的:

1、我们先建立两个区域,一个“销售区域”,一个“门店信息”,如下图。然后选择对应的数据区域按“Ctrl+T”,将这几个区域分别转化成超级表。然后把销售区这个数据表名称改为“销售区”,把门店这个数据表名称改为“门店信息”以便在后面引用。这里我就不一步步的去演示了。最终效果如下,形成了两个超级表区域。在此,我为了演示方便,全部超级表放在了一个工作表里面了,但在实际使用场景中是需要按工作表来做数据表的。

2、现在我们来建立一级菜单。具体数据验证操作步骤,就不啰嗦了。关键还是数据源的问题。这里,我们是引用销售区这个超级表的区域。因为这里是引用超级表,涉及结构化引用的问题,这里需要使用INDIRECT()这个函数。而里面的“销售区”,引用的是“销售区”这个超级表。这一步很简单,并不复杂。如果销售区这个表有很多列,那就需要使用超级表的结构化引用,可以参照第三步的那种语法方式。

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”以及区域命名这种方式引用无法自动扩展区域。关于超级表的结构化引用,这个是题外话,在此就不展开说了。

以上就是全部过程。无论几级菜单都可以此类推来制作。

写在最后:

1、目前,纵观全网,关于多级菜单的制作问题,都是使用以下这种列式表结构引用的方式来制作的。这种数据管理方式存在很大的问题,因为按照这种方式来做数据源的话,随着数据的增加,表会向横向和纵向两个方向扩展,表格会极具的膨胀和混乱。用专业的来说就是有违“三范式”。如果用这种方式来管理数据,那么就是灾难。所以,一定要使用标准的关系数据,任何时候都绝对不推荐以下这种处理方法。

2、在EXCEL里面,极力推荐使用超级表来管理数据。实际上,超级表的好处非常多,包括超强的可扩展性,超高的智能化、自动化程度等等。只要使用习惯了,那么很难再切回去使用区域模式了。关于超级表的优势,在此先不展开说了。

3、如果还有其它更好办法,请不吝赐教!

  • 发表于:
  • 原文链接https://kuaibao.qq.com/s/20191107A050MB00?refer=cp_1026
  • 腾讯「云+社区」是腾讯内容开放平台帐号(企鹅号)传播渠道之一,根据《腾讯内容开放平台服务协议》转载发布内容。

扫码关注云+社区

领取腾讯云代金券