制作下拉菜单、制作多级联动下拉菜单，甚至是随数据源自动增减的下拉菜单，这些我都讲解过详细案例，不算什么新鲜知识点，今天为什么我又要讲动态下拉菜单了呢？

因为今天的重点是讲动态数组区域的引用。这是一个全新的知识点，请务必耐心看完。

案例：

下图 1 是各部门的人员列表，请制作部门和姓名联动的二级下拉菜单，重点是：如果原始数据表中的部门名称有更新，“部门”下拉列表选项会自动随之更新。

效果如下图 2 至 4 所示。

解决方案：

1. 在部门前面添加辅助列 --> 在 A2 单元格中输入以下公式 --> 下拉复制公式：

=B2&COUNTIF(B$2:B2,B2)

公式释义：

该公式的作用是在部门名字后面加上重复出现的次数，从而重新生成一列没有重复值的新部门名

* 请注意：countif 第一个参数的起始单元格行值必须绝对引用，终止单元格的行值要相对引用。

接下来提取部门的唯一值列表。

2. 在 H2 单元格中输入以下公式：

=UNIQUE(B2:B10)

unique 是 O365 版本才有的函数，作用是提取区域内的唯一值；O365 不仅是增加了新函数，还简化了公式的用法，只要在返回区域的第一个单元格内输入公式即可，既不需要复制公式，也不需要运用数组公式，就会自动返回一个动态数组区域。

3. 在 I2 单元格中输入以下公式 --> 向右向下拖动复制公式：

=VLOOKUP($H2&COLUMN(A1),$A:$C,3,0)

公式释义：

COLUMN(A1)：计算 A1 单元格的列号，单元格向右拖动的时候，列号递增，向下拖动则不变；$H2&..：将 $H2 单元格与上述列号连接起来，得到一组与 A 列相匹配的值；VLOOKUP(...,$A:$C,3,0)：在区域 $A:$C 中查找上述值，返回第 3 列的值

* 请注意：$H2 单元格的列标需要绝对引用，行号要相对引用；查询区域 $A:$C 要绝对引用。

接下来开始制作下拉菜单。

4. 选中 E2 单元格 --> 选择菜单栏的“数据”-->“数据验证”-->“数据验证”

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

允许：选择“序列”来源：输入“=$H$2#”

* 敲黑板，今天的重点知识点来了：

前面已经说了，sort 函数返回的结果是一个动态区域；引用这个动态区域的方式，只要在区域的第一个单元格后面加个“#”号，就能动态地引用这个区域了。

然后创建二级联动下拉菜单。

6. 按 Ctrl+F3 --> 在弹出的对话框中点击“新建”按钮

7. 在弹出的对话框中按以下方式设置：

名称：输入“销售一部”引用位置：选择 I2:K2 区域

8. 再次点击“新建”按钮 --> 用同样的方式创建其他部门的名称。

9. 选中 F2 单元格 --> 选择菜单栏的“数据”-->“数据验证”-->“数据验证”

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

允许：选择“序列”来源：输入“=INDIRECT($E$2)”

现在选择“部门”下拉菜单中的选项，“姓名”下拉菜单中的选项就会动态变成相应部门的人员列表。

既然今天的重点讲的是动态区域的引用，那么我们继续验证一下，如果部门列的源数据更新了，最终是否会导致下拉菜单自动更新。

11. 修改任意一个部门的名称，如下图所示。

我什么都没做，H 列就自动增加了这个新的部门名，这就是动态数组结果的魅力所在。

因为“部门”下拉菜单引用的是动态区域，所以下拉选项中也自动新增了这个部门。

这简直太方便了，所有联动一气呵成，连刷新动作都不需要。

12. 向下拖动 I 列的公式，就能查询出新部门对应的人员。

13. 此时只要重复步骤 6、7，创建新的名称列表，就能在“名称”下拉菜单中增加新的联动列表。

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

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