今天教大家制作三级联动下拉菜单。
很多同学以为三级联动下拉做法跟二级联动下拉菜单是一样的,举一反三即可。其实不然!因为第三级要考虑的不仅仅是二级菜单的选择,而是一、二级菜单的组合情况,网上有很多教程是错的,恰恰就是因为忽略了这个关键点。
现在就跟着案例来学习正确的做法。
案例:
下图是学校某兴趣小组的人员名单,请给以下三列数据制作三级联动下拉菜单。
![0d57de2834c2a1196541d07b4e5957f2.png](https://img-blog.csdnimg.cn/img_convert/0d57de2834c2a1196541d07b4e5957f2.png)
解决方案:
1. 数据整理:
将原始表格的数据拆分开来,按以下样式整理:
- 年级及其对应的班级:从这个列表可以看出,参加兴趣小组的同学并不是每个班都有,所以每个年级对应的班级列表各不相同
- 年级班级对应的同学:从这个表格就不难理解了,三级下拉菜单不仅要考虑二级菜单选项(即“班级”),还要考虑一级菜单选项(即“年级”)
![a9dee8314f0e0f4442b22eb9cb96aaba.png](https://img-blog.csdnimg.cn/img_convert/a9dee8314f0e0f4442b22eb9cb96aaba.png)
2. 制作一级下拉菜单:
1) 选中需要制作下拉菜单的单元格 --> 选择菜单栏的“数据”-->“数据验证”
![730a28b6e03ecb903bdd65720807e211.png](https://img-blog.csdnimg.cn/img_convert/730a28b6e03ecb903bdd65720807e211.png)
2) 在弹出的对话框中,按以下方式设置 --> 点击“确定”:
- 允许:“序列”
- 来源:选中 I1:K1 单元格,即菜单选项
![ee118c8d15b4c42c2a1a78037dd6c09d.png](https://img-blog.csdnimg.cn/img_convert/ee118c8d15b4c42c2a1a78037dd6c09d.png)
现在一级下拉菜单就已经做好了。
![950278ad4efa8dcab2b4a95f5881757a.png](https://img-blog.csdnimg.cn/img_convert/950278ad4efa8dcab2b4a95f5881757a.png)
3. 制作二级下拉菜单:
1) 选中下图红框中的数据区域 --> 按 Ctrl+G --> 在弹出的对话中选择“定位条件”
![49a7e66e12c33f5c437c571bd10dba57.png](https://img-blog.csdnimg.cn/img_convert/49a7e66e12c33f5c437c571bd10dba57.png)
2) 在下一个弹出的对话框中,选择“常量”--> 点击“确定”
![96f87a0be077a3a458fe7d0b817b0136.png](https://img-blog.csdnimg.cn/img_convert/96f87a0be077a3a458fe7d0b817b0136.png)
这样,就选中了整个区域中的非空单元格。
![ccf6491795fd478348cd016e1a18c2ef.png](https://img-blog.csdnimg.cn/img_convert/ccf6491795fd478348cd016e1a18c2ef.png)
3) 选择菜单栏的“公式”-->“根据所选内容创建”
![328fa1eab3441e37e900b12020f73733.png](https://img-blog.csdnimg.cn/img_convert/328fa1eab3441e37e900b12020f73733.png)
4) 在弹出的对话框中勾选“首行”--> 点击“确定”
![6de8b226fbc3146bdeb25aa19035b6f9.png](https://img-blog.csdnimg.cn/img_convert/6de8b226fbc3146bdeb25aa19035b6f9.png)
现在按 Ctrl+F3 打开“名称管理器”,可以看到三个以年级命名的数据区域已经创建好了。
![d8822daa89e9d93152f290b9f9c5eac4.png](https://img-blog.csdnimg.cn/img_convert/d8822daa89e9d93152f290b9f9c5eac4.png)
5) 选中需要制作二级下拉菜单的单元格 --> 选择菜单栏的“数据”-->“数据验证”
![011ec78f2e1e4b1e0c8bb9a2f285332b.png](https://img-blog.csdnimg.cn/img_convert/011ec78f2e1e4b1e0c8bb9a2f285332b.png)
6) 在弹出的对话框中,按以下方式设置 --> 点击“确定”:
- 允许:“序列”
- 来源:=INDIRECT(E2)
公式释义:
- indirect 函数的参数不加 "",表示地址引用,E2 单元格内的年级名称被当作地址,该地址的内容就是我们在前几步创建的名称所对应的内容
- E2 要相对引用,即前面不加 $,这样下拉复制公式时,引用的单元格也会随之变化
![0a0979fadc6bc60e214b8edfe0930694.png](https://img-blog.csdnimg.cn/img_convert/0a0979fadc6bc60e214b8edfe0930694.png)
现在二级联动下拉菜单就制作好了。
![33d4574fdb928fdb579efa003e40113d.png](https://img-blog.csdnimg.cn/img_convert/33d4574fdb928fdb579efa003e40113d.png)
![71fa88d9ad41316e87b20bcc6dc66fad.png](https://img-blog.csdnimg.cn/img_convert/71fa88d9ad41316e87b20bcc6dc66fad.png)
3. 制作三级联动下拉菜单:
1) 选中下图红框中的数据区域 --> 按 Ctrl+G --> 选择“定位条件”--> 选择“常量”--> 点击“确定”:
即可选中区域中的非空单元格。
2) 选择菜单栏的“公式”-->“根据所选内容创建”
![a334c0dbea9c8e0f62f80be3af2ca384.png](https://img-blog.csdnimg.cn/img_convert/a334c0dbea9c8e0f62f80be3af2ca384.png)
3) 在弹出的对话框中勾选“首行”--> 点击“确定”:
即可创建出所有以年级班级命名的数据区域
![6de8b226fbc3146bdeb25aa19035b6f9.png](https://img-blog.csdnimg.cn/img_convert/6de8b226fbc3146bdeb25aa19035b6f9.png)
4) 选中需要制作三级下拉菜单的单元格 --> 选择菜单栏的“数据”-->“数据验证”
![4ef9df2bcaa998edb98de9b358b5ae10.png](https://img-blog.csdnimg.cn/img_convert/4ef9df2bcaa998edb98de9b358b5ae10.png)
5) 在弹出的对话框中,按以下方式设置 --> 点击“确定”:
- 允许:“序列”
- 来源:=INDIRECT(E2&F2)
公式释义:
- 三级下拉菜单不仅仅要匹配该同学所在的班级,还要匹配其所在的年级,所以需要将年级和班级两个参数连接起来。这是今天要教的重点,也就是三级联动下拉菜单和二级联动下拉菜单的最大不同之处。
- 所有参数都要相对引用,下拉复制公式时,引用的单元格才会动态变化
![77db67cac29fff2d66b9ec25263c2fed.png](https://img-blog.csdnimg.cn/img_convert/77db67cac29fff2d66b9ec25263c2fed.png)
现在,三级联动下拉菜单的就全部制作完成了。
![809a03bb41ddc1da098980c5acbb1b04.png](https://img-blog.csdnimg.cn/img_convert/809a03bb41ddc1da098980c5acbb1b04.png)
![c77f5c3f6485f63b252ced9cc6ef021d.png](https://img-blog.csdnimg.cn/img_convert/c77f5c3f6485f63b252ced9cc6ef021d.png)
很多同学会觉得 Excel 单个案例讲解有些碎片化,初学者未必能完全理解和掌握。不少同学都希望有一套完整的图文教学,从最基础的概念开始,一步步由简入繁、从入门到精通,系统化地讲解 Excel 的各个知识点。
现在终于有了,以下专栏,从最基础的操作和概念讲起,用生动、有趣的案例带大家逐一掌握 Excel 的操作技巧、快捷键大全、函数公式、数据透视表、图表、打印技巧等……学完全本,你也能成为 Excel 高手。