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

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

用“数据验证”功能设置下拉菜单，大部分同学都会，不会也没关系，今天的案例还是会带大家走一遍最基础的设置方法。


今天的重点是：用普通方式设置的下拉菜单，所引用的选项区域是固定死的，也就是说，如果菜单区域有新增，那么下拉菜单项是不会自动随之更新的。


如何解决这个问题？


案例：


请将下图 1 的 B 列设置成下拉菜单，菜单项在 D 列。


要求当 D 列的区域新增了选项后，B 列的下拉菜单会随之自动更新。


效果如下图 2 所示。


解决方案：


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


2. 在弹出的对话框中选择“设置”选项卡，进行以下设置 --> 点击“确定”：

  • 允许：选择“序列”
  • 来源：选择下拉菜单区域


下拉菜单设置好了。


但是如果在 D 列的菜单项下方增加新选项，B 列的下拉菜单并不会自动更新。


接下来我们就来解决这个问题。


3. 再次选中 B 列需要设置下拉菜单的区域 --> 选择“数据”-->“数据验证”-->“数据验证”


4. 在弹出的对话框中选择“设置”选项卡 --> 按以下方式设置 --> 点击“确定”：

  • 允许：选择“序列”
  • 来源：输入 =OFFSET($D$1,0,0,COUNTA($D:$D))


公式释义：

  • OFFSET 函数的作用是返回对单元格或单元格区域中指定行数和列数的区域的引用；
  • 语法为 OFFSET(起始位置, 偏移的行数, 偏移的列数, [引用区域的高度], [引用区域的宽度])；
  • 对照上述语法，此处的公式含义为：从 $D$1 开始，行列都不作偏移，引用高度为 COUNTA($D:$D)，即 D 列中非空单元格数的区域；当 D 列中输入新选项后，counta 的结果会增加，从而实现自动引用新增选项的目的


就是这个效果。