制作带层级的 Excel 下拉菜单,应该选子级,若选父级会有出错提示

请在微信客户端打开

公众号平台最新的推送规则对技术类文章不太友善，如果不想错过干货，请务必“设为星标”哦！！！

点击任意文章上方的“☆星标”即可。

对于老读者来说，制作下拉菜单没人会觉得太过陌生吧。

然而，按数据层级依次缩进的下拉菜单用过吗？

案例：

下图 1 是公司各部门的人员名单列表，请设置一个带有层级的下拉菜单，用以显示人员所在的部门，但是选择的时候只可以选择人名，如果选择了部门名，单元格会变成红底白字，旁边还会出现提示语句。

效果如下图 2 至 4 所示。

解决方案：

1. 选中数据表的任意单元格 --> 选择菜单栏的“插入”-->“数据透视表”

2. 在弹出的对话框中选择“现有工作表”--> 选择需要放置的目标位置 --> 点击“确定”：

3. 在右侧的“数据透视表字段”区域，将“部门”和“姓名”都拖动到“行”区域。

4. 选中数据透视表的任意单元格 --> 选择菜单栏的“设计”-->“报表布局”-->“以大纲形式显示”

5. 将 G 列设置为辅助列，在 G2 单元格中输入以下公式 --> 下拉拖动公式：

=IF(D2<>"",D2,IF(E2<>"",REPT(" ",3)&E2))

公式释义：

• D2<>"",D2：如果 D2 单元格不为空，就显示 D2 单元格的内容；

• IF(E2<>"",REPT(" ",3)&E2)：如果 D2 单元格为空，且 E2 单元格不为空，则在 3 个空格之后显示 E2 单元格的值，也就是将姓名缩进 3 个空格的距离。

接下来开始设置下拉菜单。

6. 选中 H2 单元格 --> 选择菜单栏的“数据”-->“数据验证”-->“数据验证”

7. 在弹出的对话框中选择“设置”选项卡，按以下方式设置 --> 点击“确定”：

• 允许：选择“列表”

• 来源：输入“=”，然后选择区域 $G$2:$G$17

下拉菜单就设置好了，而且是有层次的，姓名缩进了三个空格。

8. 选中 I2 单元格 --> 输入以下公式：

=IF(ISERROR(FIND(" ",H2)),"请选择姓名","")

公式释义：

• IF(ISERROR(FIND(" ",H2))：在 H2 单元格中查找空格，如果找不到，结果就会出错，说明这是部门名，而不是人名；为什么？因为我们设置下拉菜单的时候在人名前面加了 3 个空格；

• "请选择姓名",""：如果出错，即找不到空格，说明这是部门名，就显示“请选择姓名”；否则留空，因为选择了人名

选择“销售部”，I2 单元格就自动出现了提示语句。

现在开始设置条件格式。

9. 选中 H2 单元格 --> 选择菜单栏的“开始”-->“条件格式”-->“新建规则”

10. 在弹出的对话框中选择“使用公式确定要设置格式的单元格”--> 输入以下公式 --> 点击“格式”按钮：

• =$I$2<>""

11. 在弹出的对话框中选择“填充”选项卡 --> 选择红色

12. 选择“字体”选项卡 --> 选择白色

13. 点击“确定”

现在就设置好了，当选择了部门名时，不仅 I2 单元格会显示提示信息，H2 单元格还会变成红底白字。

如果选择人名，则一切风平浪静。