返回目录:excel表格制作
为了提高数据录入速度,也为了在录入数据的时候强制统一,制作下拉列表是一个常见的做法。如下图,在【客户信息表】,如何在"省份"和"城市"字段快速制作多级下拉列表呢?
创建步骤
1. 建立下拉列表序列数据。
第一步就是要建立下拉列表的的序列数据。就是说,将客户的省份数据和城市的数据全部录入到指定位置。举例如下图:
注意,最好按照上图中的格式来组织数据(【省份】一列,每个省份对应的城市在同一列。
2. 创建名称-自定义区域
选中所有序列数据(利用定位条件跳过空值),"根据所选怎内容创建名称",操作看动图:
通过这几步操作已经为指定数据区域创建了名称。
3. 通过"数据验证"制作下拉列表
a) 省份字段的数据序列很简单"=省份"。
b) 城市字段的数据需要根据省份的变化而变化,所以要用一个间接引用函数indirect。"=indirect($B2)"
到这一步,我们就已经快速地建立和多级下拉列表。在实际使用的情况就是,通过下拉菜单选择"省份",再通过下拉菜单选择这个省份中对应的"城市"。
可以说是相当方便了,既提高了效率又避免了录入不规范的数据。
可是,可能没有人告诉你,这里面还有一个大BUG
假设一个情景,我先选择了"广东"-"广州",然后再在此基础上想修改成"广西"-"南宁"。当将"广东"改为"广西"的时候,"城市"字段的"广州"不会有任何变化,我需要手动重新选择为"南宁"。
但是,问题就出在这里了,如果我将省份改为"广西",一不小心忘记将"广州"改为"南宁"了,Excel不会有任何错误提示!
这样就会出现了尴尬的"广西-广州",显然这是一个明显的逻辑错误!
我们一定必须要避免这种错误,因为一旦有大量数据,出现这种错误是非常难排错的(如果有1万行数据,人工排错能把眼睛看瞎~)
更严重的是,导致后续对于区域数据的分析报表也一定是错误的—问题大了!
所以,我们需要一个措施,当"省份"发生变化的时候,"城市"自动清空。这样,就算我们忘记选择了城市,但是也能很容易查错或者补充。
启动VBE编辑器,通过VBA代码可以实现
实现效果如下动图:
代码的逻辑很简单,当B列某单元格的数据发生改变的时候,那么C列同一行的单元格内容自动清空。
代码:
Private Sub Worksheet_Change(ByVal Target As Range)With Target If .Column = 2 Then Cells(.Row, 3).ClearContents End IfEnd WithEnd Sub
如果你希望用这段代码,你需要理解并相应地修改列参数。
如果还有不清楚的地方,留言评论,或者私信/私聊我。
总结
如果你的工作情景中需要用到和案例类似的多级下拉列表,那么一定要记得加上后面那一段VBA代码,否则一旦出错后果很严重。
我是微软认证讲师MCT,关注我,一起玩Office。