Excel实用技巧02——级联下拉列表

1. 单级下拉列表

1.1 操作步骤

1. 选中需要设置下拉列表的单元格，上图即为 A2 单元格，在数据选项卡的数据工具一栏中，选择数据验证工具；
2. 允许值选择为序列，勾选忽略空值和提供下拉箭头选项，来源选择下拉列表的 数据区域，点击确定后即可设置完成；
3. 当需要设置的下拉选项比较少且固定不变时（如月份、日期等），可以直接将选项写到来源中，选项间用英文 , 分割，如上图中来源可以写为 河北,河南,湖北,湖南，同样具有下拉列表效果。

2. 多级下拉列表

2.1 操作步骤

1. 多级下拉列表是利用 Excel 的名称功能和 INDIRECT 函数在第一级下拉列表的基础上实现的，因此第一级下拉列表和上述单级下拉列表设置方式相同；
2. 在第一级下拉列表设置完成的情况下，选择第二级下拉列表的 数据区域，这个数据区域要以第一级下拉列表的内容为表头，如上图示例中，即选择 E1:H4 区域；
3. 选中需要的第二级下拉列表的 数据区域 后，打开公式选项卡，在定义的名称一栏中选择根据所选内容创建选项，在弹出的界面中选择数据区域的表头（即第一级列表的数据区域）所在位置（首行、最左列、末行和最右列），将根据表头的值创建对应的名称，如上图省份为第一级列表，处在第二级下拉列表的 数据区域 的首行，则该值即选择首行；
4. 之后选中需要设置第二级下拉列表的单元格，打开数据选项卡，在数据工具一栏中，选择数据验证工具，允许值选择为序列，勾选忽略空值和提供下拉箭头选项，来源设置一个 INDIRECT 函数，格式为 =INDIRECT(参数) ，其中 参数 指的是第一级下拉列表的单元格的坐标，如上图第一级下拉列表的单元格坐标为 A2，要注意不要写成绝对引用 $A$2 的形式，如果是通过选择单元格自动生成的绝对引用坐标，可以通过快捷键 F4 调整到相对引用，输入完毕后点击确定后即可完成第二级下拉列表的设置；
5. 第三级及之后的下拉列表设置方法与第二级相同，在使用 INDIRECT 函数时将参数值设置为上一级下拉列表所在单元格坐标即可；

3. 操作说明

1. 单级或多级下拉列表所引用的 数据区域 在Excel表中是以数据表的形式存在的，使上一级列表的数据作为下一级列表的数据的表头，以此类推来设置多个相关且具有层级关系的数据表，各级下拉列表的数据区域关系如下图所示，为了表格整洁，通常会专门新建一个工作表用来存放这些数据表；

2. 当下拉列表级数不多时尽量使 数据区域 的表头在不同位置（首行、最左列、末行和最右列），防止构建下拉列表时出错；
3. 下拉列表的 数据区域 的表头有中英文标点或其他特殊符号时，可能会导致下一级下拉列表数据无法显示；

4. 进阶操作

4.1 常量定位剔除空单元格

在选择下拉列表 数据区域 时，有时候数据像下图一样，会有很多空单元格，并不是规整的，将空单元格一起选中的话，制作成的下拉列表选项也会包含这些空单元格。这种情况可在选择所有数据区域后 Ctrl + G 打开定位，点击定位条件，选择常量后点击确定，即可排除空单元格。具体操作如下：

4.2 使用超级表增强下拉列表

使用以上方法制作的单级下拉列表在数据区域中的数据进行更新和重排序的时候，下拉列表也会进行相应的更新和重排序，但一旦需要添加新的数据，是无法同步更新的，而需要重新设置一遍下拉列表。

可以通过将下拉列表的数据区域设置为超级表来解决这个问题，选中数据区域的任意一个单元格，执行 Ctrl + T 即可将整个数据区域设置为超级表，超级表中的数据可以在添加、更新和重排序后同步更新到下拉列表选项中。

需要注意的是，以上的超级表操作只能增强单级下拉列表，对多级下拉列表不起作用，并且单级下拉列表的数据区域只能在同一列，后续添加的数据也只能与原有数据在同一列，这样才会有同步更新的效果，不同列的数据是无法更新的。