今天有个小伙伴私信问了个问题:“在Excel中如何制作多级下拉菜单,并且菜单数据可以自动更新?”。其实,这个问题也是大家日常工作中经常遇到的问题,今天就跟大家分享一下具体实现方法。

如何在excel中设置下拉菜单,教你在excel中制作自动更新的多级下拉菜单-趣帮office教程网

操作步骤:

一、制作分级菜单的数据源表格

1、制作固定格式的分级菜单表格

①如下图所示,制作分级菜单的数据表格“第一行”单元格内容必须设置为每列数据的上一级。比如省份“河北省”是城市“石家庄、张家口、唐山”的上一级,城市“石家庄”又是“长安区、桥西区、新华区”这些区县的上一级。就是数据区域表格第一行必须是下面每列数据的上一级,有几级下拉菜单,就需要根据级别设置几个对应的数据区域,我们这里省份、城市、区县三级,所以需要设置两个数据区域,如果我们有四个级别,那我们就需要设置三个数据区域。

如何在excel中设置下拉菜单,教你在excel中制作自动更新的多级下拉菜单-趣帮office教程网

2、把每列数据设置成超级表,并且以每列第一行名称命名表名称

选中“河北省”这列数据→通过组合键【Ctrl T】调出超级表格设置窗口,直接点击确定→然后把左上角的表名称命名为第一个单元格内容即“河北省”→最后点击一下回车键。(备注:其它列也是用上面同样的方法操作即可。)

如何在excel中设置下拉菜单,教你在excel中制作自动更新的多级下拉菜单-趣帮office教程网

二、设置多级下拉菜单

1、设置省份一级菜单

选中“省份”这列数据区域→点击【数据】-【有效性】下拉菜单下的【有效性】→在弹出数据有效性窗口中,有效性条件“允许”选中【序列】,“来源”选择“分级菜单数据源”工作表中的省份名称区域,如下图所示

如何在excel中设置下拉菜单,教你在excel中制作自动更新的多级下拉菜单-趣帮office教程网

如何在excel中设置下拉菜单,教你在excel中制作自动更新的多级下拉菜单-趣帮office教程网

2、设置二级城市菜单

首先在“省份”这列第一个单元格先选中一个省份,然后再选中城市这列下面第一个单元格→点击【数据】-【有效性】下拉菜单下的【有效性】→在弹出数据有效性窗口中,有效性条件“允许”选中【序列】,“来源”中输入公式:=INDIRECT(A2)→最后下拉填充下面的单元格即可,如下图所示

如何在excel中设置下拉菜单,教你在excel中制作自动更新的多级下拉菜单-趣帮office教程网

解读:

①在设置城市这个二级菜单时,前面的省份名称必须先选中,如果不选中的话在设置有效性时会提示错误

如何在excel中设置下拉菜单,教你在excel中制作自动更新的多级下拉菜单-趣帮office教程网

②公式=INDIRECT(A2)中的A2就是上一级省份所在单元格

如何在excel中设置下拉菜单,教你在excel中制作自动更新的多级下拉菜单-趣帮office教程网

3、设置三级区县菜单

同样先在“城市”这列第一个单元格中选中一个城市,然后再选中区县这列下面第一个单元格→点击【数据】-【有效性】下拉菜单下的【有效性】→在弹出数据有效性窗口中,有效性条件“允许”选中【序列】,“来源”中输入公式:=INDIRECT(B2)→最后下拉填充下面的单元格即可,如下图所示

如何在excel中设置下拉菜单,教你在excel中制作自动更新的多级下拉菜单-趣帮office教程网

解读:

大家会发现设置二级、三级菜单操作步骤基本一样,如果有更多级菜单也是相同的操作方法:

首先选中上一级第一个单元格数据→然后选中本级菜单第一个单元格,并且添加数据有效性验证,输入公式=INDIRECT(上一级菜单单元格)→最后下拉填充数据即可。

三、设置下拉菜单数据更新

因为在“分类菜单数据源”工作表中已经把工作表设置成的超级表格,超级表是可以实现动态的扩展区的,所以直接添加数据后,下拉菜单会自动更新,如下图所示

如何在excel中设置下拉菜单,教你在excel中制作自动更新的多级下拉菜单-趣帮office教程网

特别提醒:

在WPS中多个超级表挨在一起有时无法全部都实现动态的扩展区,如下图所在,在“河北省”和“江苏省”下面就可以实现动态的扩展区,“山东省”下面就不行,如下图所示

如何在excel中设置下拉菜单,教你在excel中制作自动更新的多级下拉菜单-趣帮office教程网

遇到这种情况,我们只能对无法实现动态扩展的超级表手动调整“表格大小”,操作方法如下:

首先先添加数据→选中超级表任意单元格→点击【表格工具】-点击【调整表格大小】→重新选择超级表区域即可,如下图所示

如何在excel中设置下拉菜单,教你在excel中制作自动更新的多级下拉菜单-趣帮office教程网