|
在Excel中使用下拉列表的功能，能帮助我们限制填写的内容，保证数据的有效无误。然而常规的数据有效性（下拉列表）我们都会制作，可是如果要制作更多级别的数据有效性，似乎有点困难了。那么在本文中，将教大家制作多级别的下拉列表。
【正文】
一 一级下拉列表
在制作表格的时候，希望为一些具有固定选项的列（如性别、部门等），添加下拉框，制作如下图的效果，那我们就可以利用数据有效性来完成。
设置步骤：
1、单击【数据】选项卡中的【数据有效性】，在“数据有效性”对话框的“设置”选项卡中，在“允许”下拉列表框中选择“序列”项。在“来源”框中直接输入项目，项目之间用英文逗号分隔。
2、如果下拉框中的数据比较多，在一个连续的单元格区域中输入列表中的项目，如下图所示。
- 单击【数据】选项卡中的【数据有效性】，在“数据有效性”对话框的“设置”选项卡中，在“允许”下拉列表框中选择“序列”项。
- 在“来源”框中选择部门列表下的数据，单击“确定”按钮。
二 二级下拉列表
在填写地址时，当确定省份后，城市一栏内自动显示对应省份下的城市列表，方便我们进行选择。像这样的效果我们称为二级下拉列表。
设置步骤：
1、为各个省份定义名称
制作二级下拉菜单时，首先需要为各个省份的城市分别定义名称，之后才能根据省份读取到相应的城市。定义名称时，先选中广东省下所有城市（I1：I22），在【公式】选项卡下点击“根据所选内容创建”，然后勾选“首行”并点击“确定”，完成“广东省”的名称定义。以同样的方法，定义名称“湖南省”和“湖北省”。
2、为“省份”一列设置下拉菜单，来源可选择I1：K1。
3、选择“城市”一列，在“数据有效性”中选择“序列”，并在“来源”处输入公式：=INDIRECT(D2)，点击“确定”。
注：①录入公式时需要切换单元格的引用方式。②若D2单元格为空，则可能会弹出错误警告，点击“是”即可。③设置成功后，若未选定“省份”，则“城市”一列也无法进行选择。
三 多级拉列表
我们除了会填写“省份”、“城市”外，还会选择“区”，那这种我们称为多级下拉列表。我们可以利用Vlookup、Offset、match、countif函数共同实现该功能。
设置步骤：
- 先来了解这几个函数，其语法分别为：
- Match（查询值，查找范围，0），返回符合特定值特定顺序的查询值在数组中的相对位置；
- Countif（条件范围，条件），计算区域中满足给定条件的单元格的个数；
- Vlookup（查询值，查找范围，显示序列，匹配参数），搜索表区域首列满足条件的元素，确定待检索单元格在区域中的行序号，再进一步返回选定单元格的值；
- Offset（参考单元格，偏移的行数，偏移的列数，返回引用区域的行数，返回引用区域的列数），以指定的应用为参照系，通过给定偏移量返回新的应用。
- 数据源需要按如下图排列：
- 在C2单元格我们借助于Match函数，计算“广东省”在A列中的位置，因此该函数为：=MATCH(B2,A:A,0)。随后将该函数分别复制至C3、C6、C7、C8、C9单元格即可计算对应的项在A列中的起始位置，该数值用于指导offset函数往下偏移几行；
- 接下来要计算每个项目共有几个小项，在D2中利用countif函数计算个数，此处的公式为：=COUNTIF(A:A,B2)。该数值可以用在offset函数中的返回行数中；
- 最后在G列设置一级下拉列表。如图：
- 对二级“市”设置数据有效性。因为我们需要根据一级G2单元格选择的不同，设置不一样的下拉列表，而每个一级“省”会有不一样个数的二级“市”，所以我们借助offset函数来完成。在H2单元格设置数据有效性的“来源”位置，输入以下公式：=OFFSET($B$1,VLOOKUP(G2,$B:$D,2,0)-1,0,VLOOKUP(G2,$B:$D,3,0),1)。
该公式的意思为：以B1单元格为参考单元格，往下偏移几行，往右不偏移列，返回引用区域的行数，返回一列的数据。那么往下偏移几行，要根据前面的G2单元格的内容变化，所以利用vlookup函数来查找G2单元格的内容，位于B:D范围中第二列的结果，我们便可以从B1单元格往下偏移6行至B7单元格，再减去1，得到“广州市”的B6单元格；同样的，返回引用区域的行数，也借助vlookup函数来得到，如此一来，二级下来列表的“市”也就完成了。
 
- 接下来，我们就用同样的offset函数来制作三级下拉列表，因此在I2单元格的数据有效性的公式为：=OFFSET($B$1,VLOOKUP($H$2,$B:$D,2,0)-1,0,VLOOKUP($H$2,$B:$D,3,0))
最后的效果为：
那么有了这种方法以后，我们想设置任意级别的下拉列表都可以实现了。
|