excel中创建多级联动菜单含图解范本模板.docx

excel中创建多级联动菜单含图解范本模板.docx

《excel中创建多级联动菜单含图解范本模板.docx》由会员分享，可在线阅读，更多相关《excel中创建多级联动菜单含图解范本模板.docx（12页珍藏版）》请在冰豆网上搜索。

excel中创建多级联动菜单含图解范本模板

Excel2007中创建多级下拉菜单［图解］

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,company，）-1,65535）））”这个公式的意思是A列某单元格的内容是某个一级公司，则在本列对应的单元格内显示这个一级公司下属的二级企业的下拉菜单供人们选择，最多可以显示65535个.

图片来源：

王喆

点击放大图组索引

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