【摘要】 在Excel中使用下拉列表的功能,能帮助我们限制填写的内容,保证数据的有效无误。然而常规的数据有效性(下拉列表)我们都会制作,可是如果要制作更多级别的数据有效性,似乎有点困难了。那么在本文中,将教大家制作多级别的下拉列表。 【正文】 一 一级下拉列表 在制作表格的时候,希望为一些具有固定选项的列(如性别、部门等),添加下拉框,制作如下图的效果,那我们就可以利用数据有效性来完成。 设置步骤: 1、单击【数据】选项卡中的【数据有效性】,在“数据有效性”对话框的“设置”选项卡中,在“允许”下拉列表框中选择“序列”项。在“来源”框中直接输入项目,项目之间用英文逗号分隔。 2、如果下拉框中的数据比较多,在一个连续的单元格区域中输入列表中的项目,如下图所示。
二 二级下拉列表 在填写地址时,当确定省份后,城市一栏内自动显示对应省份下的城市列表,方便我们进行选择。像这样的效果我们称为二级下拉列表。 设置步骤: 1、为各个省份定义名称 制作二级下拉菜单时,首先需要为各个省份的城市分别定义名称,之后才能根据省份读取到相应的城市。定义名称时,先选中广东省下所有城市(I1:I22),在【公式】选项卡下点击“根据所选内容创建”,然后勾选“首行”并点击“确定”,完成“广东省”的名称定义。以同样的方法,定义名称“湖南省”和“湖北省”。 2、为“省份”一列设置下拉菜单,来源可选择I1:K1。 3、选择“城市”一列,在“数据有效性”中选择“序列”,并在“来源”处输入公式:=INDIRECT(D2),点击“确定”。 注:①录入公式时需要切换单元格的引用方式。②若D2单元格为空,则可能会弹出错误警告,点击“是”即可。③设置成功后,若未选定“省份”,则“城市”一列也无法进行选择。 三 多级拉列表 我们除了会填写“省份”、“城市”外,还会选择“区”,那这种我们称为多级下拉列表。我们可以利用Vlookup、Offset、match、countif函数共同实现该功能。 设置步骤:
数据源需要按如下图排列:
该公式的意思为:以B1单元格为参考单元格,往下偏移几行,往右不偏移列,返回引用区域的行数,返回一列的数据。那么往下偏移几行,要根据前面的G2单元格的内容变化,所以利用vlookup函数来查找G2单元格的内容,位于B:D范围中第二列的结果,我们便可以从B1单元格往下偏移6行至B7单元格,再减去1,得到“广州市”的B6单元格;同样的,返回引用区域的行数,也借助vlookup函数来得到,如此一来,二级下来列表的“市”也就完成了。
最后的效果为: 那么有了这种方法以后,我们想设置任意级别的下拉列表都可以实现了。 |
|