excel中创建多级联动菜单(含图解)

Excel2007中创建多级下拉菜单[图解]

(2011-05-08 23:00:00)

转载▼

分类：07.MSoffice

标签：

郑来轶

数据分析

excel2007

下拉菜单

多级

列表

indirect

Excel2007中创建多级下拉菜单

有如下一份省市数据：省份以及省份所辖地市清单，需要实现多级联动下拉菜单操作，具体操作方式如下所示：

第一步：对各单元格区域进行名称定义

选中A1：A5单元格区域，然后公式——根据所选内容创建——如图设置

PS:分别对B、C、D、E列重复以上操作

第二步：设置省份列的选项

选中G1：G22单元格区域，然后数据——数据有效性弹出数据有效性设置窗口，然后如图设置

其中来源“=省份”，这里的省份与之前自定义的单元格区域名称是相对应的省份选项搞定

第三步：设置地市列的选项

选中H1：H22单元格区域，然后数据——数据有效性

在弹出的窗口，设置如下

其中来源=indirect($G1)

PS:indirect函数，返回由文本字符串指定的引用。

地市选项也搞定了，N级操作以此类推

------------------------------我是完美的分割线-----------------------

条件查找:

=OFFSET(C1,MATCH(A9&B9,A2:A6&B2:B6,0),)

图片来源：王喆

点击放大图组索引

我们经常会遇到如图中的EXCEL表的情况，第一行是一级单位，下面都是二级单位的这种EXCEL形式的小型数据库，我们需要在表中先填写一级单位后再填写二级单位，但是为了防止填写EXCEL工作表的人乱填，需要严格限定单元格内容，同时由于有些数据量非常大，就需要减轻填表人的劳动强度，因此需要在数据有效性的基础上再进一步设置联动。

图片来源：王喆

点击放大图组索引

在EXCEL的sheet1表中选中A列，在数据菜单下选择有效性菜单，进入数据有效性对话框进行设置。

图片来源：王喆

点击放大图组索引

在弹出的数据有效性对话框的有效性条件下选择允许序列，在来源里填写数据库中的一级单位“工业企业,外贸企业,商业企业,个体户”需要注意的是它们之间用英语的逗号隔开。同时选中忽略空值和提供下拉箭头选项。

图片来源：王喆

点击放大图组索引

由于数据有效性不支持从另一个EXCEL工作表中直接选取单元格，因此要做定义名称的工作，用快捷键ctrl+F3打开定义名称对话框，用自己认为好记的名词做为定义后的词，在引用位置设置需要引用的单元格。本例中使用company来代表sheet2中的A1至D1单元格的一级企业。

图片来源：王喆

点击放大图组索引

使用two来代表sheet2中A2这个单元格，由于二级单位有可能很多，因此只引用一个，在后面写公式时再详细说明。

图片来源：王喆

点击放大图组索引

定义在对二级单位进行数据有效性性设置时在来源中写如下的公式：

"=OFFSET(two,,MATCH($A2,company,)-1,COUNTA(OFFSET(two,,MATCH($A2,com pany,)-1,65535)))"这个公式的意思是A列某单元格的内容是某个一级公司，则在本列对应的单元格内显示这个一级公司下属的二级企业的下拉菜单供人们选择，最多可以显示65535个。

图片来源：王喆

点击放大图组索引

这个就是数据有效性多重下拉菜单的效果，当A2单元格选中工业企业时，B2单元格就自动列出工业企业的二级单位名称，这样就达到了即能防止填表人乱填表，又能减轻填表人的劳动强度的目的。

如何利用Excel数据有效性制作多级联动下拉列表

相信很多朋友都想通过Excel数据有效性实现多级联动下拉列表的制作，多级联动下拉列表就是第一列选择了某项，第二列则只会提供相对应的选项供我们选择，这个在网页中可以经常看到。

现在EXCEL也可以实现这样的效果，主要利用Indirect函数和数据有效性，此技巧来自新浪微薄@MrExcel 。

操作演示详见下面两个GIF动态演示图，需要单独点击图片，再点击“查看原图”即可进行播放演示。

1、二级下拉菜单联动

原文地址：/1811781304/xzhIW2Ep9 2、三级下拉菜单联动

原文地址：/1811781304/z3OMQiYY3

数据——有效性——设置——在有效性条件设置：允许选择“序列”，在来源中直接输入下拉菜单中所要显示的内容，中间用半角“,”隔开

设置下拉内容的各种颜色：开始--条件格式--突出显示单元格规则--文本包含