excel怎么设置数据下拉列表框（怎样才能让Excel下拉菜单选项随数据源自动增减）

Excel 下拉菜单的制作虽说大多数人都会，但是一旦对下拉菜单有进一步的需求，难度系数提高得就不止一星半点。

有关下拉菜单的案例，我写过不少有意思的内容，具体请参见：

• Excel ActiveX 控件到底有啥用？如此美貌的下拉菜单，非它不可
• 制作一个可以用下拉菜单控制的 Excel 动态图表
• Excel中可以按关键字搜索的下拉菜单，两种做法任你选
• Excel–这才是三级联动下拉菜单的正确做法，不同于二级联动菜单
• Excel – 如何始终显示下拉菜单右边的小箭头？
• Excel下拉菜单选一个少一个
• 去除Excel下拉菜单中的空值和重复值

今天再教一个新技巧：当数据源中新增菜单项时，如何让下拉菜单的选项随之自动增加？

案例 ：

在下图 1 中的 A 列制作下拉菜单，菜单项内容在 E 列。要求：当 E 列新增内容时，A 列的下拉菜单选项会随之自动增加新选项。效果如下图 2 所示。

解决方案：

先看一下用普通方式制作下拉菜单，是否能实现案例需求。

1. 选中 A2:A14 区域 --> 选择菜单栏的“数据”-->“数据验证”-->“数据验证”

2. 在弹出的菜单中选择“设置”选项卡，按入如下方式设置 --> 点击“确定”：

• 允许：序列
• 来源：=$E$1:$E$3

现在下拉菜单已经设置好。

在 E4 单元格新增“四班”，但是下拉菜单选项并没有增加这个新选项。如此看来，我们得另辟蹊径。

下面就来学习一下正确的设置方法。

1. 选中 A2:A14 区域 --> 选择菜单栏的“数据”-->“数据验证”-->“数据验证”

2. 在弹出的菜单中选择“设置”选项卡，按入如下方式设置 --> 点击“确定”：

• 允许：序列
• 来源：=offset($E$1,,,COUNTA($E:$E),)

公式主要用到了 offset 函数，有关该函数的案例，可参见：

• Excel 二维表查询，不得不学会经典组合公式 offset match
• Excel – 当offset遇上F9，图表动起来了
• Excel – 用offset函数将一列数据首尾倒置

公式释义：

• OFFSET 函数语法：OFFSET(要偏移的起始坐标单元格, 要偏移多少行, 要偏移多少列, [需要返回的引用的行高], [需要返回的引用的列宽])
• OFFSET($E$1,,,COUNTA($E:$E),)：从 E1 单元格开始，引用的行高为 E 列的非空个数；也就是将普通下拉菜单公式中的截止单元格变成了动态的，根据 E 列的内容自动变化。

现在在 E 列增加新单元格，下拉菜单会自动新增选项；反之亦然，删除 E 列中的选项，下拉菜单也会自动减少。

很多同学会觉得 Excel 单个案例讲解有些碎片化，初学者未必能完全理解和掌握。不少同学都希望有一套完整的图文教学，从最基础的概念开始，一步步由简入繁、从入门到精通，系统化地讲解 Excel 的各个知识点。

现在终于有了，以下专栏，从最基础的操作和概念讲起，用生动、有趣的案例带大家逐一掌握 Excel 的操作技巧、快捷键大全、函数公式、数据透视表、图表、打印技巧等……学完全本，你也能成为 Excel 高手。

,