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

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

图1 示例数据

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

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

图2 一级数据验证

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

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

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

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

图4 创建名称

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

图5 编辑名称

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

图6 INDIRECT函数设置

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

图7 动态引用一级菜单

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

图8 动态引用二级菜单

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

图9 最终录入界面