办公小技巧:拒绝出错 制作Excel多级联动下拉列表

CFan 电脑爱好者 2021-05-19 10:01应用 标签:技巧 办公 制作

一个街道经常管辖着许多社区,每个社区又包含多个小区,数据录入时就需要输入社区、小区名称。由于没有准备规范的数据名称,对于同一小区,不同统计员会录入不同名称,比如下表中的“燕沙·后(东润枫景)”小区,有人记成“燕沙”,有人则记成“东润枫景”(图1)。这样数据给后期汇总、归类带来极大的不便,现在我们可以借助Excel(本文以2016版为例)函数打造多级联动菜单,这样用户只需选择性输入即可,从而确保数据字段名称的统一性。

201923djld1

图1 示例数据

从上面的数据可以看到,这里主要有三级地址,分别是“街道办”、“社区”和“小区”,每个上级分别包含不同数目的下级,要实现数据选择性的输入,这里我们就要将不同级别的数据分别对应。比如在选择罗星街道香梨社区时,选择的列表就是B列的内容,效果和我们平常网购时选择地址类似。

首先建立一级数据,这里的一级数据是街道办名称。新建一个工作表,按提示在单元格F2及F3处输入街道办的名称,接着定位到A2单元格,点击“数据→数据验证”,在“允许”项选择“序列”,在来源处选择“=$F$2:$F$3”,将A2单元格下拉进行填充(图2)。

201923djld2

图2 一级数据验证

这样A列数据输入只能从F2:F3单元格中进行选择,这是一级菜单的内容。如果要添加其他内容,只要在序列中增加内容即可(图3)。

201923djld3

图3 数据验证后选择性输入一级菜单内容

接下来对二级菜单进行设置,这里的二级菜单对应的是各个社区。因为每个街道办管辖的是不同社区,这样二级菜单就要和相应的一级菜单对应。二级菜单的设定可以使用INDIRECT函数进行动态引用。

定位到单元格G5和H5,分别输入“罗星街道办”和“角美街道办”,为了方便引用,这里输入的名称一定要和一级菜单名称一致。选中G2:H5区域,切换到菜单栏点击“公式→名称管理器→根据所选内容创建”,在弹出的窗口中勾选“首行”,分别创建名为“罗星街道办”和“角美街道办”的两个新名称(图4)。

201923djld4

图4 创建名称

这里需要注意的是,因为每个一级菜单(街道办)包含的下级菜单数目可能不同,比如上述例子中,罗星街道办管辖社区是3个,另一个街道办则只有2个,这样我们还需要在名称管理器中进行设置。打开名称管理器,选中“角美街道办”,将引用位置更改为“=Sheet2!$H$3:$H$4”,因为它的上一级角美街道办只管辖两个社区(图5)。

201923djld5

图5 编辑名称

定位到B2单元格,同上打开数据验证设置,“允许”项选择“序列”,在来源处输入“=INDIRECT($A2)”,这里B2单元格的输入使用INDIRECT函数进行引用(图6)。

201923djld6

图6 INDIRECT函数设置

在INDIRECT函数中,这里“($A2)”表示的是对行的相对引用。表示在B2单元格的输入是引用A2的内容,这样在A2(一级菜单)选择不同的内容时,B2的序列会显现对应的二级菜单的内容,从而实现动态引用,按提示下拉填充(图7)。

201923djld7

图7 动态引用一级菜单

三级菜单设置类似,先在I2:M2单元格依次输入“香梨社区、角砾社区、黄双社区、黄山社区、合和社区”,然后同上根据内容创建名称,在数据验证中来源处输入“=INDIRECT($B2)”,这样C2单元格的输入使用INDIRECT函数动态引用B2的内容进行输入。现在我们在B2选择不同社区,C2会同步显示对应社区下的小区名称(图8)。

201923djld8

图8 动态引用二级菜单

以后在输入统计表名称的时候,数据录入只能在下拉列表中选择预置好的标准数据,从而有效确保了数据的统一。为了表格的简洁,还可以选中F1:M18数据,右击选择“隐藏”将其隐藏,或者直接在另一个工作表中输入预先准备的数据,并将工作表设置为“只读”、“隐藏”,这样可以更方便数据录入操作(图9)。同理,四级、五级(甚至更多级)菜单的设置可依照上述方法进行,对于需要动态引用上一级菜单的输入,只要先根据上一级菜单内容建立对应的名称,最后再使用INDIRECT进行引用即可。

201923djld9

图9 最终录入界面

QQ图片20210420165237