EXCEL 如何制作瀑布图,对于有些数据做图表非常适合用瀑布图,比如生活开支......
定制Excel选择性输入列表:[3]VBA分级连选法
数据量较大的信息经常是分级管理的,比如像行政区划按省、市、县三级区划管理,
类似这样的信息输入适合使用“VBA分级连选法”。
本文以地址输入为例,介绍“Excel选择性输入列表——VBA分级连选法”制作流程。
工具/原料
- Excel
数据结构规划
本文以地址输入为例,使用国家统计局发布的全国三级行政区划数据库,我们将在其既有的数据结构上进行编程。如果是未经规划的数据,可仿照此例进行数据结构规划。先来了解一下全国三级行政区划数据库的数据结构。
数据一共四列:
第一列是编号。三级区划放在一起统一进行编号,是名称的唯一性标识。
第二列是名称。
第三列是本级序号。
第四列是上级名称的编号。用于上级索引。
- 此文章未经许可获取自百度经验
数据按“块”划分。第一块是省级数据,后面以省为单位将所属市、县组织为不同的块,块与块之间间隔一行。
数据块中,第一行是省级名称,后面每个市分为一个小块。
通过第四列的索引值可以找到上一级。
Excel选择性输入列表——VBA分级连选法制作流程
插入用户窗体
参考上期方法插入一个用户窗体,在属性窗口中将其名称改为“F2”,标题(Caption)改为“地址连选”,背景色设置为浅绿色。
插入控件
在窗体F2中插入三个标签、三个复合框(也叫下拉列表框)、一个按钮,调整好大小、位置。在属性窗口中将三个标签的BackStyle属性值设置为1,Caption属性分别设置为:“省/自治区/直辖市”、“市”、“县/区”,将三个复合框的名称分别改为:CB1、CB2、CB3,将命令按钮的名称改为confirm。其它属性使用默认值即可。
窗体程序设计
双击窗体F2进入代码窗口,开始下一个环节:窗体程序设计。
窗体程序设计——UserForm_Activate()事件
'当窗体被激活时触发该事件,在事件过程中给复合框CB1赋值省级名称(数据存放在“省市县”工作表b2:b34区域),设置窗体显示位置为单元格跟随,将CB2、CB3置为不可用状态(此时一级选项尚未选择)。
Private Sub UserForm_Activate()
CB1.List = Sheets("省市县").Range("b2:b34").Value
F2.Top = ActiveCell.Top 50
F2.Left = ActiveCell.Left ActiveCell.Width 25
CB2.Enabled = False
CB3.Enabled = False
End Sub
窗体程序设计——CB1_Change()事件
'当完成一级选项的选择时触发该事件。在事件过程中激活CB2,并给其赋值二级名称。通过全局变量“id1”将所选一级名称的编号传递给CB2_Change()事件。
Dim Id1 As String
Private Sub CB1_Change()
For i = 2 To 34
If Sheets("省市县").Cells(i, 2).Value = CB1.Value Then
CB2.Enabled = True: CB2.Clear
Id1 = Sheets("省市县").Cells(i, 1)
Exit For
End If
Next i
If i > 34 Then
CB2.Enabled = False: CB3.Enabled = False
Else
For i = 37 To 3401
If Sheets("省市县").Cells(i, 4) = Id1 Then
CB2.AddItem Sheets("省市县").Cells(i, 2).Value
End If
Next i
End If
End Sub
窗体程序设计——CB2_Change()事件
'当完成二级选项的选择时触发该事件。在事件过程中激活CB3,并给其赋值三级名称。
Private Sub CB2_Change()
With Sheets("省市县")
For i = 36 To 3401 '定位一级数据大块
If .Cells(i, 1).Value = Id1 Then Exit For
Next i
Do While .Cells(i, 1) > "" '定位二级数据小块
If .Cells(i, 4) = Id1 And .Cells(i, 2) = CB2.Value Then
id2 = .Cells(i, 1): Exit Do
End If
i = i 1
Loop
If .Cells(i, 1) = "" Then
CB3.Enabled = False
Else
CB3.Enabled = True: i = i 1: CB3.Clear '激活CB3
Do While .Cells(i, 4) = id2 '将三级数据赋值给CB3
CB3.AddItem .Cells(i, 2).Value: i = i 1
Loop
End If
End With
End Sub
窗体程序设计——confirm_Click()事件
'当点击命令按钮时触发该事件。在事件过程中将三级选项的值合并在一起赋值给当前单元格,之后卸载窗体F2。
Private Sub confirm_Click()
ActiveCell.Value = CB1.Value & CB2.Value & CB3.Value
Unload F2
End Sub
工作表程序设计
在VBA工程窗口中双击需调用F2窗体的工作表,进入其代码窗口,输入下面的程序。
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim EndRow As Single
EndRow = Range("a65536").End(xlUp).Row
If Target.Row > 1 And Target.Row = EndRow And _
Target.Column = 4 And Target.Rows.Count = 1 _
Then F2.Show
End Sub
评价
分级连选法适合处理数据量大、容易分级管理的信息录入,是前两种方法的延伸,编程难度稍大一些。不过不要紧,文中对程序做了详细说明,很容易根据实际需要修改。
注意事项
- 示例文档下载地址:http://pan.baidu.com/s/1pJPw8Fx
- 一定要启用宏,VBA编写的程序才能生效。
- 在家靠父母出门靠朋友!糊口度日卖艺为生!烦请各位父老乡亲赏投一票!抱拳称谢了!
以上方法由办公区教程网编辑摘抄自百度经验可供大家参考!