作者：乔山办公网日期：
返回目录：excel表格制作
通过数据的有效性实现下拉菜单
1
我们e69da5e6ba90e799bee5baa6e79fa5e98193365先做一个不联动的下拉菜单：
我们打开office2007→数据→数据有效性
2
在弹出的对话框里面点击“允许”下拉菜单里的“序列”：
3
在“序列”下面的“来源”里面有以下两种写法：
1、手动输入序列名并以拼音下的逗号隔开。
2、选择一个引用。
4
用第一种手动输入序列名方法制作下拉菜单。
5
用第二种选择引用方法制作下来菜单。
怎么定义名称
1
我们点击“公式”→“定义名称”：
2
在弹出的窗口"名称"里键入我们想定义的名称。在“引用位置”选择一个区域。
如下图：“NUM”就代表这A1：A10的区域。
创建多级联动的下拉菜单
我们首先将：
A公司所含部门C2：C4定义为“A公司”
A公司综合办的职工所包含的员工E2:E3定义为“综合办”
剩下的同理定义名称即可。
我们在A20列，通过数据有效性的“序列”，添加下拉菜单：
在B20列，通过数据有效性的“序列”，键入“=indirect（$A$20）”：
表示，B20里的内容是——名称为“A20”（表示A20里面的值）所对应的区域。这里如果A20为A公司，那么B20=INDIRECT($A$20)就表示，在定义名称里名为A20所定义的C2：C4。
indirect的意思为，把其后面括号里的内容根据已定义的“名称”转成其代表的“区域”。
在C20列，通过数据有效性的“序列”，键入“=indirect（$B$20）”：
同理上。
5
最后我们就实现如图的多级联动下拉菜单：
以WPS 2019版本为例：
关于excel表格怎样设置多级联动的下拉菜单，在WPS「表格（Excel）」中操作方法如下：
1、首先，我们在表格将数据分别录入到Sheet2和Sheet3中，Sheet2中是一级和二级，Sheet3中是二级和三级的数据。和之前制作二级下拉菜单的方式差不多，我们先进入Sheet2中，选择所有数据，点击「公式-指定-只保留「首行」前面的勾，其他的全部取消掉。同e799bee5baa6e79fa5e98193e4b893e5b19e337样的方法，我们再次进入Sheet3中进行操作；
2、设置一级下拉菜单：进入Sheet1，选中A2单元格，进入「数据-有效性-有效性-选择序列」，在「来源」中选择Sheet2中的「A1:C1」单元格（就是一级下拉菜单的内容）；
（注意：设置完成后续先选中一个选项，否则设置二级时会提示错误）
3、设置二级下拉菜单。光标定位到B2单元格，然后进入「数据-有效性-有效性-选择序列」「来源」中输入「=INDIRECT(A2)」确定；
4、设置三级下拉菜单。选中C2单元格，同样的操作，只是在「来源」中输入的是「=INDIRECT(B2)」。最后，我们选中A2:C2单元格，向下填充一下。此时，我们的多级下拉菜单就已经全部完成。
1.一级菜单（即N列）由e79fa5e98193e59b9ee7ad94365以下公式得到区域：
=INDIRECT(ADDRESS(2,14)&":"&ADDRESS(Sheet1!$N$15,14))
即是区域：N2：N4
ADDRESS(2,14)=N2
因Sheet1!$N$15=4，所以
ADDRESS(Sheet1!$N$15,14)=N4
2.二级菜单定义的原理与一级菜单相同，只不过是所选取的是P列。
另，N列的数据由数组公式：
=IF(SUM(1/COUNTIF($B$2:$B$29,$B$2:$B$29))>=ROW($B1),INDEX($B$2:$B$29,SMALL(IF(ROW($B$2:$B$29)-1=MATCH($B$2:$B$29,$B$2:$B$29,0),ROW($B$1:$B$28),"0"),ROW($B1))),"")
从B列的省份中筛选不重复记录所得。
O列的数据由数组公式：
=IF(ROW()-1>COUNTIF($B$2:$B$30,$H$4),"",INDEX($C:$C,SMALL(IF($B$2:$B$30=$H$4,ROW($C$2:$C$30),30),ROW(1:1))))
从C列的筛选，有个条件是：B列的值等于H4，C列的值的排列。
而P列则是对O列进行不重复记录的筛选。
后面的原理一级压一级的相同。
区域N15:T15的值只是定义各级菜单有效性区域的截止行号所用。
不知这样说明，是否明白。
首先来说一下我们要实现的效果，就是我们选择1级菜单后，2级菜单里就有对应1级菜单的内容，我们选择2级菜单的项的时候，3级菜单会产生对应2级菜单项的内容，以此类推，来说一下我实现这种功能的原理，就是为不同的列的内容设置了名称，而这个名称恰好就是上一级菜单里的选择项。这样的情况下，在数据有效性中的函数读取的其实是引用这个名词所对应的区域内的内容，也就把我们下一级的菜单内容读取出来了。
2
接下来，我们来准备数据，根据第一步所描述的原理，我将列名进行了颜色处理，以方便大家去理解。
3
从1级项到3级项，高级别项是下一级的列名，这就是我们的数据源的格式，如果有的网友问，不加这些列名可以吗？回636f7079e799bee5baa6e79fa5e98193332答是：可以的，不加列名的话，你需要知道每列对应的上一级的内容，加上只是方便查看。
4
接下来的一步是Office2003同2003以上版本不同的地方，创建名称，2003需要逐一创建，而像2013可以全选以后，系统会根据选择区域自行创建，不过逐一创建也有好处，就是我们不会出现部分值出现2次的现象。接下来，我们就逐一进行创建，首先创建“百度”的2级项内容。选中数据区域。
5
点击Ctrl+F3，或者从菜单栏中选择“插入”--“名称”--“定义”。
6
需要注意的是，系统会默认所选区域的首单元格内容为当前工作薄名称，我们需要改为上一级的名称。 这里，它的上一级是“百度”。
点击“添加”，这样一个工作薄的名称就添加完成了，剩下的同理。
全部添加完成后，我们就完成了对所需要的数据区域的命名工作。
接下来，我们准备表格，我们需要将数据在表格中进行多级关联以实现我们想要的效果。
我们选择机构名称，然后在菜单栏中选择“数据”--“有效性”。
有效性条件选择“序列”，来源直接选择机构名称下的内容项。
确定之后，我们会发现，机构名称下就会有内容项了，当然了，这一步可能很多人都已经知道了，我就不过多解释了。
接下来，我们来关联2级的内容，同样在菜单栏中选择“数据”--“有效性”，有效性条件选择“序列”，但来源中，我们需要用到INDIRECT(目标单元格)，然后点击确定。
这样，我们就会发现，2级菜单的关联已经有了，而且不会出现重复的1级菜单项。
接下来我们关联3级菜单，同关联2级菜单一致，在来源中，我们需要用到INDIRECT($F14)。
步骤阅读
这样，我们3级菜单也就关联完成了，包括4级，5级甚至更多，都可以这样来实现。