如何实现Excel多级下拉菜单?
发布时间:2023-08-16 11:32:08
多级下拉菜单这个问题,在Excel里面并不容易实现,关键问题在于数据有效性验证环节需要对数据源的动态过滤。如果使用了它,那么Excel表格在某些方面上可能会产质的飞跃,至少可以起到如下作用:
实现数据的全局参照验证,确保数据关联和一致。
人机动态交互效果更好。
输入更加智能,减少了使用人的输入量。
数据更加规整,为后期数据的分析提供了极大的便利。
在此,以二级菜单为例,我们来看需要达到的效果。
当我们选择了一级菜单后,根据选择的一级菜单项目自动加载二级菜单内容。在此,我们先不考虑使用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里面,极力推荐使用超级表来管理数据。实际上,超级表的好处非常多,包括超强的可扩展性,超高的智能化、自动化程度等等。只要使用习惯了,那么很难再切回去使用区域模式了。关于超级表的优势,在此先不展开说了。
猜你喜欢
- 剪贴画边框是剪贴画的外围框线,效果就如同我们生活中的相框。它起到一个美化并与文档中其他对象区分的作用,学成之后还可以将多幅图片设置成同样的边
- Excel是许多人都会用到的办公软件,一些企业还会在Excel表格中录入员工的个人信息。不过,在Excel表格中录入身份证号码时也会出现一个
- 在Excel表格中处理各种数据时我们经常会使用各种函数公式对数据进行快速计算,比如我们最常使用的sum求和等等。但是有的小伙伴发现自己在使用
- 以常用的求和函数SUM为例,它的语法是“SUM(number1,number2,......)”。其中“SUM”称为函数名称,一个函数只有唯
- Excel中经常需要使用到表单控件进行控件归类,表单控件具体该如何插入呢?下面是由小编分享的excel2003表单控件的插入方法,以供大家阅
- 有win10用户反映自己想更改更新内容的存储位置,但是操作之后发现无法更改储存位置,系统还出现提示错误代码0x80070002,这该怎么办?
- Word图表怎么设置自动生成目录?Word文章中图片很多,想要给图片排序,该怎么办呢?下面我们就来看看Word图表中生成目录的教程,需要的朋
- excel跳过空单元格这个知识点,真的非常实用,但是知晓此技巧的童鞋不多。我们直接用一个案例来讲解,会更直观。下面截图所示,A列是费用列表,
- 电脑屏幕如果没使用的话,就会自动息屏,那有时候我们正在浏览器观看视频的话,系统有可能检测不到我们正在观看视频,然后自动息屏了,这种情况确实挺
- 在制作数据比较庞大的表格的过程中,经常会出现制作表格下方的时候看不到表头,面对这类型的情况小编给出一下经验,下面小编马上就告诉大家excel
- 在使用word2003的过程中,新手常常会因为操作命令过于分散,无法牢记每个命令的位置,从而影响工作效率。事实上用户可以通过在菜单栏上创建新
- 如果同时打开多个工作簿并进行了操作,退出Excel时会出现一个对话框,上面有三个按钮:“是”、“否”、“取消”,如果退出时都不保
- 长时间使用电脑后,难免会出现一些错误。有些用户在使用Win10系统时无法正常启动文件程序,提示错误码0xc000142。那么如何解决呢?以下
- 害人之心不可有,防人之心不可无。任何时候,必要的保密工作还是要做的。对于Word而言,我们可以对文档进行加密,完了之后只有知道正确的密码才能
- 问:如下图所示,在Excel工作表中有两列,分别是使用人和付款人,要筛选出使用人和付款人不是同一人的数据,如何实现?答:可以使用Excel的
- Excel是一款大家所熟知的办公软件,其功能强大对我们的工作有着很大的帮助。那么大家知道最新的Excel2019中的条件格式改如何设置么?还
- 论文的封面和目录不加页码,摘要单独设置页码(一般是罗马数字页码),正文(包括参考文献、致谢)要求阿拉伯数字页码。如果要将这三种不同的页面设置
- 在Word2013文档中,用户可以借助“键入时自动套用格式”功能,在直接输入数字的时候自动生成编号。用户首先需要启用自动编号列表自动套用选项
- Excel中怎么添加标题行?有很多小伙伴使用excel时还是不懂在Excel中如何添加标题行,那么和小编一起来学习一下吧Excel中怎么添加
- 这篇文章主要介绍了excel表格高级筛选怎么操作?excel表格高级筛选不同用法使用教程的相关资料,需要的朋友可以参考下本文详细内容介绍ex