分享高质量的原创Office教程、Word教程、Excel教程。
位置:主页 > excel教程 >

excel表格多级下拉列表制作的操作方法

发布时间:2019-06-20 08:40:17 来源:www.roce6.com 浏览量:
excel表格已经在我国普及,但有一些新用户会遇见对excel表格多级下拉列表制作进行设置的问题,其实有很多用户都反映过如何设置excel表格多级下拉列表制作的问题,那么到底怎样才能快速的设置excel表格多级下拉列表制作呢?其实很简单,只需要按照1、单击【数据】选项卡中的【数据有效性】,在“数据有效性”对话框的“设置”选项卡中,在“允许”下拉列表框中选择“序列”项。在“来源”框中直接输入项目,项目之间用英文逗号分隔。    2、如果下拉框中的数据比较多,在一个连续的单元格区域中输入列表中的项目来处理就搞定了,现在详细的给大家讲下excel表格多级下拉列表制作的具体步骤:

【正文】

 

一  一级下拉列表

在制作表格的时候,希望为一些具有固定选项的列(如性别、部门等),添加下拉框,制作如下图的效果,那我们就可以利用数据有效性来完成。

设置步骤:
1、单击【数据】选项卡中的【数据有效性】,在“数据有效性”对话框的“设置”选项卡中,在“允许”下拉列表框中选择“序列”项。在“来源”框中直接输入项目,项目之间用英文逗号分隔。

2、如果下拉框中的数据比较多,在一个连续的单元格区域中输入列表中的项目,如下图所示。

  • 单击【数据】选项卡中的【数据有效性】,在“数据有效性”对话框的“设置”选项卡中,在“允许”下拉列表框中选择“序列”项。
  • 在“来源”框中选择部门列表下的数据,单击“确定”按钮。

二  二级下拉列表

在填写地址时,当确定省份后,城市一栏内自动显示对应省份下的城市列表,方便我们进行选择。像这样的效果我们称为二级下拉列表。
设置步骤:
1、为各个省份定义名称
制作二级下拉菜单时,首先需要为各个省份的城市分别定义名称,之后才能根据省份读取到相应的城市。定义名称时,先选中广东省下所有城市(I1:I22),在【公式】选项卡下点击“根据所选内容创建”,然后勾选“首行”并点击“确定”,完成“广东省”的名称定义。以同样的方法,定义名称“湖南省”和“湖北省”。

2、为“省份”一列设置下拉菜单,来源可选择I1:K1。
3、选择“城市”一列,在“数据有效性”中选择“序列”,并在“来源”处输入公式:=INDIRECT(D2),点击“确定”。

注:①录入公式时需要切换单元格的引用方式。②若D2单元格为空,则可能会弹出错误警告,点击“是”即可。③设置成功后,若未选定“省份”,则“城市”一列也无法进行选择。

三  多级拉列表

我们除了会填写“省份”、“城市”外,还会选择“区”,那这种我们称为多级下拉列表。我们可以利用Vlookup、Offset、match、countif函数共同实现该功能。
设置步骤:

  1. 先来了解这几个函数,其语法分别为:
    • Match(查询值,查找范围,0),返回符合特定值特定顺序的查询值在数组中的相对位置;
    • Countif(条件范围,条件),计算区域中满足给定条件的单元格的个数;
    • Vlookup(查询值,查找范围,显示序列,匹配参数),搜索表区域首列满足条件的元素,确定待检索单元格在区域中的行序号,再进一步返回选定单元格的值;
    • Offset(参考单元格,偏移的行数,偏移的列数,返回引用区域的行数,返回引用区域的列数),以指定的应用为参照系,通过给定偏移量返回新的应用。
  2. 数据源需要按如下图排列:

  1. 在C2单元格我们借助于Match函数,计算“广东省”在A列中的位置,因此该函数为:=MATCH(B2,A:A,0)。随后将该函数分别复制至C3、C6、C7、C8、C9单元格即可计算对应的项在A列中的起始位置,该数值用于指导offset函数往下偏移几行;
  2. 接下来要计算每个项目共有几个小项,在D2中利用countif函数计算个数,此处的公式为:=COUNTIF(A:A,B2)。该数值可以用在offset函数中的返回行数中;
  3. 最后在G列设置一级下拉列表。如图:

  1. 对二级“市”设置数据有效性。因为我们需要根据一级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函数来得到,如此一来,二级下来列表的“市”也就完成了。

  1. 接下来,我们就用同样的offset函数来制作三级下拉列表,因此在I2单元格的数据有效性的公式为:=OFFSET($B$1,VLOOKUP($H$2,$B:$D,2,0)-1,0,VLOOKUP($H$2,$B:$D,3,0))


最后的效果为:


那么有了这种方法以后,我们想设置任意级别的下拉列表都可以实现了。