Excel 下拉菜单的制作虽说大多数人都会,但是一旦对下拉菜单有进一步的需求,难度系数提高得就不止一星半点。
有关下拉菜单的案例,我写过不少有意思的内容,具体请参见:
- Excel ActiveX 控件到底有啥用?如此美貌的下拉菜单,非它不可
- 制作一个可以用下拉菜单控制的 Excel 动态图表
- Excel中可以按关键字搜索的下拉菜单,两种做法任你选
- Excel–这才是三级联动下拉菜单的正确做法,不同于二级联动菜单
- Excel – 如何始终显示下拉菜单右边的小箭头?
- Excel下拉菜单选一个少一个
- 去除Excel下拉菜单中的空值和重复值
今天再教一个新技巧:当数据源中新增菜单项时,如何让下拉菜单的选项随之自动增加?
案例 :
在下图 1 中的 A 列制作下拉菜单,菜单项内容在 E 列。要求:当 E 列新增内容时,A 列的下拉菜单选项会随之自动增加新选项。效果如下图 2 所示。
![96cb10bd65bbb94b65cbc714a2a0f852.png](https://img-blog.csdnimg.cn/img_convert/96cb10bd65bbb94b65cbc714a2a0f852.png)
![1e14d9409136c97ed328a71060d7dc8e.gif](https://img-blog.csdnimg.cn/img_convert/1e14d9409136c97ed328a71060d7dc8e.gif)
解决方案:
先看一下用普通方式制作下拉菜单,是否能实现案例需求。
1. 选中 A2:A14 区域 --> 选择菜单栏的“数据”-->“数据验证”-->“数据验证”
![136195fb25848a15459ea7327a8af708.png](https://img-blog.csdnimg.cn/img_convert/136195fb25848a15459ea7327a8af708.png)
2. 在弹出的菜单中选择“设置”选项卡,按入如下方式设置 --> 点击“确定”:
- 允许:序列
- 来源:=$E$1:$E$3
![150275496d86e182e93239b7e4c8c2f9.png](https://img-blog.csdnimg.cn/img_convert/150275496d86e182e93239b7e4c8c2f9.png)
现在下拉菜单已经设置好。
![5ea6b932aeef08f13ef9ca4696185201.png](https://img-blog.csdnimg.cn/img_convert/5ea6b932aeef08f13ef9ca4696185201.png)
在 E4 单元格新增“四班”,但是下拉菜单选项并没有增加这个新选项。如此看来,我们得另辟蹊径。
![04c905b1a78c04edce6da7a4d5170640.png](https://img-blog.csdnimg.cn/img_convert/04c905b1a78c04edce6da7a4d5170640.png)
下面就来学习一下正确的设置方法。
1. 选中 A2:A14 区域 --> 选择菜单栏的“数据”-->“数据验证”-->“数据验证”
![64c53c6f40692b7db9e83e0d3717917d.png](https://img-blog.csdnimg.cn/img_convert/64c53c6f40692b7db9e83e0d3717917d.png)
2. 在弹出的菜单中选择“设置”选项卡,按入如下方式设置 --> 点击“确定”:
- 允许:序列
- 来源:=OFFSET($E$1,,,COUNTA($E:$E),)
![2449fa9f0d8f5bb432c69879159c6d12.png](https://img-blog.csdnimg.cn/img_convert/2449fa9f0d8f5bb432c69879159c6d12.png)
公式主要用到了 offset 函数,有关该函数的案例,可参见:
- Excel 二维表查询,不得不学会经典组合公式 offset+match
- Excel – 当offset遇上F9,图表动起来了
- Excel – 用offset函数将一列数据首尾倒置
公式释义:
- OFFSET 函数语法:OFFSET(要偏移的起始坐标单元格, 要偏移多少行, 要偏移多少列, [需要返回的引用的行高], [需要返回的引用的列宽])
- OFFSET($E$1,,,COUNTA($E:$E),):从 E1 单元格开始,引用的行高为 E 列的非空个数;也就是将普通下拉菜单公式中的截止单元格变成了动态的,根据 E 列的内容自动变化。
现在在 E 列增加新单元格,下拉菜单会自动新增选项;反之亦然,删除 E 列中的选项,下拉菜单也会自动减少。
![1e14d9409136c97ed328a71060d7dc8e.gif](https://img-blog.csdnimg.cn/img_convert/1e14d9409136c97ed328a71060d7dc8e.gif)
![12d02b3ccc398cfe68b2c0e143cd2978.png](https://img-blog.csdnimg.cn/img_convert/12d02b3ccc398cfe68b2c0e143cd2978.png)
很多同学会觉得 Excel 单个案例讲解有些碎片化,初学者未必能完全理解和掌握。不少同学都希望有一套完整的图文教学,从最基础的概念开始,一步步由简入繁、从入门到精通,系统化地讲解 Excel 的各个知识点。
现在终于有了,以下专栏,从最基础的操作和概念讲起,用生动、有趣的案例带大家逐一掌握 Excel 的操作技巧、快捷键大全、函数公式、数据透视表、图表、打印技巧等……学完全本,你也能成为 Excel 高手。