Skip to main content
 Web开发网 » office教程 » excel教程

用Excel制作二级联动下拉菜单,源数据在2列也可以!

在数据录入工作的时候,为了保证数据录入的准确性,我们可以使用两级联动的下拉菜单,效果如下所示:

用Excel制作二级联动下拉菜单,源数据在2列也可以!  excel下拉菜单 第1张

制作这种联动的下拉菜单,大部分的教程都是我们要将数据源格式调整成下面这个样子

第一行是省份,从第2行开始,是省份下面的城市,如下所示:

用Excel制作二级联动下拉菜单,源数据在2列也可以!  excel下拉菜单 第2张

我们首先选中所有的数据,按CTRL+G键,定位条件,选择常量,这样只会选择非空的数据源区域:

用Excel制作二级联动下拉菜单,源数据在2列也可以!  excel下拉菜单 第3张

然后在公式选项卡上方,选择根据所选内容创建,选择首行,创建名称。

用Excel制作二级联动下拉菜单,源数据在2列也可以!  excel下拉菜单 第4张

然后需要在 输入数据的位置,选中F列,数据有效性,数据验证,然后选择序列,选择A1:D1单元格。

用Excel制作二级联动下拉菜单,源数据在2列也可以!  excel下拉菜单 第5张

用同样的方法,选择G列,数据验证,选择序列,输入的内容是:

=INDIRECT(F1)

用Excel制作二级联动下拉菜单,源数据在2列也可以!  excel下拉菜单 第6张

这样就制作完成了,在F和G列就可以二级的联动输入了。

拓展应用:

有一个小伙伴说,它的原始数据源并不是上面那样的,而是两列的源数据,那该如何制作二级下拉菜单,

用Excel制作二级联动下拉菜单,源数据在2列也可以!  excel下拉菜单 第7张

当然可以将这两列的数据源,转换成上面的那种格式,再用上面的方法来制作。

今天教大学的方法是直接通过这种两列式的数据源来制作下拉菜单,如果AB列添加数据,能够实时的更新二级联动

❶首先,是提取城市的不重复值,存放在一个空列

=OFFSET(A$2,MATCH(0,COUNTIF(H$1:H1,A$2:A$99),0)-1,,,)&""

这个公式就是提取A列中的不重复值,可以直接套用。

在H列的第2行开始输入公式,输入完成后按CTRL+SHIFT+ENTER键,然后下拉填充第20行(如果数据较多,可以多拖一点)

用Excel制作二级联动下拉菜单,源数据在2列也可以!  excel下拉菜单 第8张

❷选择D列,用数据验证,选择序列,输入的公式是:

=OFFSET($H$2,0,0,COUNTA($H$2:$H$20)-COUNTBLANK($H$2:$H$20),1)

用Excel制作二级联动下拉菜单,源数据在2列也可以!  excel下拉菜单 第9张

❸选择E列,然后使用数据有效性,输入公式:

=OFFSET($B$1,MATCH(D1,$A:$A,0)-1,0,COUNTIF($A:$A,D1),1)

用Excel制作二级联动下拉菜单,源数据在2列也可以!  excel下拉菜单 第10张

用Excel制作二级联动下拉菜单,源数据在2列也可以!  excel下拉菜单 第11张

这就制作完成了。你学会了么?

评论列表暂无评论
发表评论
微信