VBA-SZ第2节|表格数据处理的实用技巧
最近更新:'2019-05-09'
1.多重循环的使用技巧
2.选择排序法
3.Range.sort:常规用法
4.Range.sort:自定义序列
5.Range.sort:关键字排序
6.删除重复字典/批量处理数组
7.VBA处理合并单元格:Range.mergeArea
8.VBA处理合并单元格的排序
1.多重循环的使用技巧
1.1 跨列匹配
案例1:标记客户发生额是否异常
H列的用户,在E列的发生额超出了I列对应的上限,则H列用户对应的B列流水号标红色.
对于这个案例,如果没有VBA程序,人类又是怎么操作的呢?
Sub demo1()
Dim i As Long, k As Long, name As String, amount As Long
For i = 3 To 15
name = Cells(i, 3): amount = Cells(i, 5)
For k = 3 To 5
If Cells(k, 8) = name And amount > Cells(k, 9) Then
Cells(i, 2).Interior.Color = vbRed
Exit For
End If
Next k
Next i
End Sub
代码显示的最终结果如下:
1.2 同列匹配
案例2:标记客户是否首次交易
如果客户是第一次交易,则在F列的首次交易标注为YES,如果不是第一次交易,则在F列的首次交易标准为NO.
操作步骤如下图:
Sub demo2()
Dim i As Long, k As Long
For i = 3 To 24
If Cells(i, 6) = "" Then
Cells(i, 6) = "yes"
For k = i + 1 To 24
If Cells(k, 3) = Cells(i, 3) Then
Cells(k, 6) = "NO"
End If
Next k
End If
Next i
End Sub
代码显示的最终结果为:
2.选择排序法
根据国家的2016年GDP进行排名
对于这种类型的题,可以人类的操作方法进行反思.
Option Explicit
Sub mySort()
Dim i As Long, j As Long, t
For i = 3 To 11
For j = i + 1 To 12
If Cells(j, 3) > Cells(i, 3) Then
t = Cells(j, 2)
Cells(j, 2) = Cells(i, 2)
Cells(i, 2) = t
t = Cells(j, 3)
Cells(j, 3) = Cells(i, 3)
Cells(i, 3) = t
End If
Next j
Next i
End Sub
代码显示的最终结果为:
缺点:简单选择排序速度比较慢.
3.Range.sort:常规用法
Range.sort与excel自带的排序菜单是一一对应的.
3.1 key1对应主关键字
关于B列,以下这几行代码其实是等价的
3.2 order1对应次序
3.3 order2,key2对应添加条件
range.sort只支持三个排序关键字,与excel自带的次要关键字是有区别的.
3.4 Header对应标题
参考案例如下截图:
3.5 MatchCase对应区分大小写
参考案例如下截图:
3.6 Orientation对应排序方向
参考案例如下截图:
3.7 SortMethod对应排序方法
excel版本不同,显示的SortMethod会有差异
参考案例如下截图:
3.8 DataOption对应文本的排序方式(字符串或数字)
参考案例如下截图:
3.8.1 DataOption对应文本的排序方式:字符串
3.8.2 DataOption对应文本的排序方式:数字
3.9其他参数:ordercustom和type
自定义排序的实际案例,如下:
4.Range.sort:自定义序列
4.1excel菜单栏自定义排序与vba的ordercustom的关系
在excel中的排序的菜单栏有自定义序列,可以根据自己希望的排序序列.
在VBA中的range.sort参数ordercustom也可以自定义排序.
4.2ordercustom参数的意义
如果想使用ordercustom参数的第9个规则,则应该按如下截图进行设置
代码显示的最终结果为:
4.3 ordercustom设置自定义
4.3.1 Application.AddCustomList添加序列的方法
4.3.2 设置ordercustom的编号
4.3.3 删除序号
为什么添加序号位置的时候要加1,而删除序号的时候又不用?
加1只针对ordercustom这个参数,而其他算法根本就没有加1这个问题.
为什么会这样乱七八糟的呢?这个只能问VBA的编程任意了.
4.4 设置自定义需要注意的事项
4.4.1 自定义序列与排序的单元格内容不一致
如图的排序结果可以看出,自定义排序后,单元格的内容与自定义序列关键字不一样时,按汉语的拼音进行排序.
4.4.1 自定义序列使用数组做对象
之前自定义序列使用的是range作为对象,这样很容易被修改
使用数组作为对象,写入到代码中,比较不容易被修改.
可以使用数组赋值的方法,与前一段代码是等价的,这样会更加干净利落.
容易犯错的地方,每个元素需要用""并且逗号分隔开.
5.Range.sort:关键字排序
5.1多个关键字排序的原理
Range.sort只有3个关键字排序,超过3个关键字排序那又应该怎么使用呢?
首先知道多个关键字排序的原理是什么.
红色的是根据range.sort中的两个关键参数对足球联赛进行排序.而绿色的是分别用range.sort的1个个关键参数排序的结果.排序的结果是一样的.
因此我们可以得到,如果关键字3个,并且用range.sort的1个关键参数,想达到range.sort的多个个关键参数的效果一致,可以要求如下:
- 先对最小的关键字进行排序
- 排好的结果再按照第二个关键字进行排序
- 最后排好的结果再按照第一个关键字进行排序.
可以使用更加简洁的代码,如下截图:
5.2多个关键字排序规则的案例
比如以下的案例,对球队名称的总积分,获胜次数,净胜球,进球数进行排序.相当于有4个关键字进行排序.
相关代码如下截图:
5.3多个关键字排序不规则的案例
可以定义一个数组a(),让数组a(0)等于主关键字第3列,a(1)等于第二个关键字第5列,a(2)等于第三个关键字第2列,a(3)等于第四个关键字第6列.
5.3独特功能排序的案例
这个在排序菜单以及VBA程序相关的函数都是无法正确的处理.
这个可以通过自己编写一个双循环.
6.删除重复字典/批量处理数组
假设有10个展会工作表,每个工作表都有出席的名单,如果弄一个统计所有展会出席次数的名单,又应该怎么弄?
这里假设每个人的名字都是唯一的,没有重复的.
6.1案例1:使用双循环
以上截图这段代码不太容易让人理解并且运行速度也比较慢.
6.2案例1:使用字典改进
对红色画框的内容,可以用字典语句代替
6.3案例1:使用数组改进
那又没有更加简洁的方法呢?
大批量数据的时候最好使用数组.
6.4数组排序案例
7.VBA处理合并单元格:Range.mergeArea
7.1计算合并单元格
案例:对甲/乙/丙/丁的应缴税金进行统计
按照之前的方法,由如下截图可知,并不可行.
那么应该怎么解决这个问题呢?
解决方案如下:
需要注意事项如下:
7.2生成合并单元格
对单元格中的同一地区进行合并.如下截图:
一般使用倒序循环解决问题.
7.3拆分合并单元格
以下方法会导致大量空白单元格
那么需要怎么样可以恢复原样呢?以下是比较通用的方法
最后运行的结果如下截图:
运行结果虽然没有多大的问题,但是代码的性能存在问题,如下截图:
如果数据量不大,其实前面的代码完全可以满足要求.为了提高性能,对代码进行优化,如下截图:
8.VBA处理合并单元格的排序
以下是对合并单元格的有效排序
8.1案例1:合并单元格的交替染色
对单元格进行交替染色相关的代码,如下截图:
运行的结果如下截图:
运行的结果并不达到要求,要求染色的是合并区域而非是每行的单元格.
对案例1的代码的改进,关键点在于i这个变量
从上面的截图可看出,运行结果并非达到目的,仅将合并单元格的区域染了一行,其他行并没有染色.
需要更改resize的range区域,才能达到最终的目的
8.2案例2:合并单元格的排序
excel自带的排序并不能处理合并单元格.因此需要自己编辑一个排序算法.
比较常用的方法是简单排序法,具体如下截图:
合并区域的最后一行行号:
怎么样把j条记录移动到第i条记录前?
代码最终的结果如下截图:
思考题:
8.3 合并单元格的总结
