Excel 数据处理.docx
《Excel 数据处理.docx》由会员分享，可在线阅读，更多相关《Excel 数据处理.docx（67页珍藏版）》请在冰豆网上搜索。
Excel数据处理
第6章数据处理
通过Excel相关对象可对工作表中的数据进行操作，如处理单元格区域的公式、对数据进行查询、排序、筛选等操作。
本章演示使用VBA进行处理数据的实例。
6.1对数据进行排序
在使用Excel分析数据的时候，用户经常需要对数据进行排序，得到各种结果。
在ExcelVBA中，用户同样可以对数据进行各种排序。
其中包括：
按照某数据序列进行排序，自定义排序的条件等。
下面将根据条件进行分析。
案例112对数据进行排序
1．功能说明
在Excel的基础功能中，对数据进行简单排序是十分强大的功能。
用户可以对一组数据按照多个关键字进行排序，可以选择升序，也可以选择降序。
这些功能也可以通过ExcelVBA代码来实现。
2．语法说明
在Excel2007中，用户对数据进行排序时，在单元格中单击作为关键字的列，选择“开始”选项卡“编辑”组中的“排序和筛选”按钮中的相关命令，可对工作表中的数据进行排序。
但是，这种方法有一个明显的缺陷，就是用户排序的数据对象是全部数据表的数据，而无法选择特定区域的数据。
在VBA代码中，用户可方便地控制排序的区域，Range对象的Sort方法可对值区域进行排序。
其语法格式如下：
该方面的各参数的含义如下：
eKey1：
指定第一排序字段，作为区域名称（字符串）或Range对象；确定要排序的值。
eOrder1：
确定Key1中指定的值的排序次序，可设置为常量xlAscending（升序）或xlDescending（降序）。
eKey2：
第二排序字段。
eType：
指定要排序的元素。
eOrder2：
确定Key2中指定的值的排序次序。
eKey3：
第三排序字段
eOrder3：
确定Key3中指定的值的排序次序。
eHeader：
指定第一行是否包含标题信息。
eOrderCustom：
指定在自定义排序次序列表中的基于一的整数偏移。
eMatchCase：
设置为True，则执行区分大小写的排序，设置为False，则执行不区分大小写的排序；不能用于数据透视表。
eOrientation：
指定以升序还是降序排序。
可用常量xlSortColumns（按列排序）或
xlSortRows（按行排序，这是默认值）。
eSortMethod：
指定排序方法。
可用常量xlPinYin（按汉语拼音顺序排序，这是默认值）或xlStroke（按每个字符的笔划数排序）。
eDataOption1：
指定Key1中所指定区域中的文本的排序方式，可使用常量xlSortNormal
（分别对数字和文本数据进行排序，这是默认值）或xlSortTextAsNumbers（将文本作为数字型数据进行排序）。
eDataOption2：
指定Key2中所指定区域中的文本的排序方式。
eDataOption3：
指定Key3中所指定区域中的文本的排序方式。
3．案例说明
某公司统计了部分员工上、下两半年销量，同时提供了员工所处的地区。
本例中，用户可以对该数据按照各数据字段进行排序。
基础数据如图6.1所示。
图6.1销量数据表
4．编写代码
实现按照“上半年销量”数据列（C列）字段进行排序的代码如下：
SubSortNumber（）
DimrngAsRange
DimLongRowAsLong
DimLongColAsLong
LongRow=ActiveSheet.Range（"A1"）.CurrentRegion.Rows.CountLongCol=ActiveSheet.Range（"A2"）.CurrentRegion.Columns.CountSetrng=ActiveSheet.Range（Cells（2,1）,Cells（LongRow-1,LongCol））rng.Sortkey1:
=ActiveSheet.Range（Cells（2,3）,Cells（LongRow-1,3））
EndSub
以上代码首先获取当前工作表中需要排序的单元格区域，对该区域使用Sort方法按
“上半年销量”列进行排序。
5．程序结果
当用户运行上面的程序代码后，结果如图6.2所示。
图6.2排序的结果
6．程序分析
根据上面的程序代码分析，用户可以对数据系列进行其他的排序。
只需要修改下面这行代码的具体内容就可以
rng.Sortkey1:
=ActiveSheet.Range（Cells（2,3）,Cells（LongRow-1,3））
案例113两个关键字排序
1．功能说明
在Excel的排序中，用户除了可以选择数据表中任何一个字段进行排序之外，还可以对数据表按照多个关键字进行排序。
其主要方法是使用Add方法，添加新的排序信息。
在Excel操作中，就相当于图6.3所示。
图6.3添加排序条件
2．语法说明
在本例中，所使用的语法介绍和前面案例中的Sort方法类似。
只是需要使用Add方法来添加一个新的排序条件。
3．案例说明
某公司统计了部分员工的销量和顾客人数，同时提供了员工所处的地区。
本例中，用户可以对该数据首先按照地区排序，然后按照销量排序。
基础数据如图6.4所示。
图6.4销量数据表
4．编写代码
实现地区和销量排序的代码如下：
SSubSortMultiNumbers（）Range（"A1:
D10"）.Select
ActiveWorkbook.Worksheets（"Sheet1"）.Sort.SortFields.ClearActiveWorkbook.Worksheets（"Sheet1"）.Sort.SortFields.AddKey:
=Range（"B2:
B10"）_
SortOn:
=xlSortOnValues,Order:
=xlAscending,DataOption:
=xlSortNormalActiveWorkbook.Worksheets（"Sheet1"）.Sort.SortFields.AddKey:
=Range（"C2:
C10"）_
SortOn:
=xlSortOnValues,Order:
=xlAscending,DataOption:
=xlSortNormal
WithActiveWorkbook.Worksheets（"Sheet1"）.Sort
.SetRangeRange（"A1:
D10"）
.Header=xlYes
.MatchCase=False
.Orientation=xlTopToBottom
.SortMethod=xlPinYin
.ApplyEndWith
EndSub
5．程序结果
当用户运行上面的程序代码后，结果如图6.5所示。
图6.5排序的结果
6．程序分析
在上面的程序代码中，首先设置排序的条件是“地区”，然后添加排序条件“顾客人数”。
最后设置排序的结果。
案例114多关键字排序
1．功能说明
在用户对数据进行排序的时候，用户可能需要按照多列关键字进行排序。
而不仅仅是两个关键字。
对于多关键字（多条件）排序的情况，用户可以沿用前面案例的方法。
但是，对于多于3个关键字的情况，则可以使用前面章节所讲解的程序结构来完成。
2．语法说明
对于超过三个关键字的排序，本案例将使用循环结构，依次对数据按照关键字进行排序。
但是，用户需要注意的是，需要先将数据按最后一个关键字排序，接着再将数据按倒
数第二个关键字排序，最后将数据按主要（第一个）关键字排序，即可得到所需要的排列。
同时，使用这种方法，用户可使用任意数量的关键字进行排序。
3．案例说明
某公司统计了部分员工的销量和增加的顾客人数，同时提供了员工所处的地区。
本例中，用户可以对该数据依次按照地区、销量和增加顾客进行排序。
基础数据如图6.6所示。
图6.6多关键字排序
4．编写代码
多关键字排序的VBA代码如下：
SubMultSortNum（）DimrngAsRangeDimiAsInteger
Application.ScreenUpdating=FalseDimLongRowAsLong
DimLongColAsLong
LongRow=ActiveSheet.Range（"A1"）.CurrentRegion.Rows.CountLongCol=ActiveSheet.Range（"A2"）.CurrentRegion.Columns.CountSetrng=ActiveSheet.Range（Cells（2,1）,Cells（LongRow-1,LongCol））
Withrng
Fori=5To3Step-1
.Sortkey1:
=ActiveSheet.Range（"B2"）.Offset（,i-3）MsgBoxRange（"B2"）.Offset（,i-3）
NextEndWith
Application.ScreenUpdating=True
EndSub
5．程序结果
当用户运行上面的程序代码后，结果如图6.7所示。
图6.7排序的结果
6．程序分析
从图中的结果可以看出，首先按B列（地区）排序，部门相同时再按C列（销量）排序，基础工资相同再按D列（增加的销量）排序。
案例115自定义排序
1．功能说明
在Excel进行分析的时候，用户经常需要设定自己的排序标准。
而在Excel中，所提供的排序标准是常见的降序、升序或者字母排序等。
当用户需要按照自己特色标准排序时，则需要自行编写相应的代码。
2．语法分析
本例演示用VBA代码创建自定义序列的方法，主要用AddCustomList方法添加自定义序列。
AddCustomList方法为自定义自动填充和/或自定义排序添加自定义列表。
其语法格式如下：
表达式.AddCustomList（ListArray,ByRow）
参数的含义如下：
eListArray：
将源数据指定为字符串数组或Range对象。
eByRow：
仅当ListArray为Range对象时使用。
如果为True，则使用区域中的每一行创建自定义列表；如果为False，则使用区域中的每一列创建自定义列表。
如果省略该参数，并且区域中的行数比列数多（或者行数与列数相等），则Excel使用区域中的每一列创建自定义列表。
如果省略该参数，并且区域中的列数比行数多，则Excel使用
区域中的每一行创建自定义列表。
使用Application对象的GetCustomListNum方法返回字符串数组的自定义序列号。
其语法格式如下：
表达式.GetCustomListNum（ListArray）
参数ListArray为一个字符串数组。
3．案例说明
某公司统计了部分员工的销量和增加的顾客人数，同时提供了员工所处的地区。
本例中，用户需要对地区按照“东部、南部、西部和北部”的次序进行排序。
基础数据如图6.8所示。
图6.8原始数据表
4．编写代码
在本例中，用户首先需要定义自定义的排序序列，如图6.9所示。
图6.9自定义序列
自定义序列排序的VBA代码如下：
SubSelfSortNumbers（）DimrngAsRange
DimnAsIntegerDimrng1AsRangeDimarr
Application.ScreenUpdating=False
DimLongRowAsLongDimLongColAsLong
LongRow=ActiveSheet.Range（"A1"）.CurrentRegion.Rows.CountLongCol=ActiveSheet.Range（"A2"）.CurrentRegion.Columns.Count
Setrng1=ActiveSheet.Range（Cells（1,1）,Cells（LongRow-1,LongCol））
WithWorksheets（"Sheet2"）
r=.Range（"A1"）.End（xlDown）.Row
Setrng=.Range（.Cells（1,1）,.Cells（r,1））EndWith
WithApplication
arr=.WorksheetFunction.Transpose（rng）
.AddCustomListListArray:
=arrn=.GetCustomListNum（arr1）
EndWith
rng1.Sortkey1:
=ActiveSheet.Range（Cells（2,2）,Cells（LongRow-1,2））,_Order1:
=xlAscending,Header:
=xlYes,OrderCustom:
=n+1Application.ScreenUpdating=True
EndSub
5．程序结果
当用户运行上面的程序代码后，结果如图6.10所示。
图6.10排序结果
6．程序分析
以上代码首先获取需要排序的单元格区域，接着将工作表Sheet2中的数据添加到自定义序列中，再使用自定义序列进行排序。
这是自定义序列的通用方法。
同时，当用户运行该程序代码后，可以查看对应的排序条件如图6.11所示。
图6.11排序条件
如果用户希望删除相应的排序条件，可以使用DeleteCustomList方法。
使用Application
对象的DeleteCustomList方法删除一个自定义序列。
其语法格式如下：
表达式.DeleteCustomList（ListNum）参数ListNum为自定义序列数字。
此数字必须大于或等于5（Excel有四个不可删除的
内置自定义序列）。
案例116随机排序
1．功能说明
在实际分析过程中，用户除了需要对数据进行标准的排序之外，有时还需要一种特殊的排序：
随机排序。
也就是说，某些数据不需要任何特定的排序，完全随机的序列。
这个功能通过Excel的基础操作将无法实现，需要通过程序代码实现。
2．语法说明
随机排序是一种特殊的排序，使用VBA代码实现随机排序的基本思路是：
首先，自行生成随机序列，然后将这个随机序列当作关键字，对其进行排序，最后的结果就是随时排序的数据结果。
3．案例说明
某公司统计了部分员工的销量和工时，同时提供了员工所处的地区。
本例中，用户需要对原始数据进行随机排列。
基础数据如图6.12所示。
图6.12原始数据
3．编写代码
随机排序的VBA代码如下：
SubRandSort（）
DimrngAsRangeDimintColAsLongDimintRowAsLong
RandomizeApplication.ScreenUpdating=FalseWithActiveSheet
intRow=.Range（"A1"）.CurrentRegion.Rows.Count
intCol=.Range（"A2"）.CurrentRegion.Columns.Count
Fori=2TointRow-1
.Cells（i,intCol+1）=Int（（Rnd*50）+2）Next
Setrng=.Range（Cells（2,1）,Cells（intRow-1,intCol+1））
rng.Sortkey1:
=.Range（Cells（2,intRow+1）,Cells（intRow-1,intCol+1））
.Columns（intCol+1）.ClearEndWith
Application.ScreenUpdating=True
EndSub
5．程序结果
当用户运行上面的程序代码后，结果如图6.13所示。
图6.13随机排序的结果
由于是随机排序的，当用户再次运行程序代码时，结果会变化，如图6.14所示。
图6.14再次运行程序的结果
6．程序分析
以上代码中，先在在数据的右列添加随机数据，使用的是内置函数Rnd。
然后使用Sort
方法按该列的数据进行排序，最后删除增加的随机数据列。
案例117自动排序
1．功能说明
本案例的主要功能是实现排序的自动化。
自动化的意思是，用户不需要选择排序的操
作就可以实现按照某字段自动排列。
例如，某公司的员工花名册中，需要自动按照名字的字母次序进行排列，而不需要每次都进行排序的操作。
2．语法说明
本例需要根据用户对单元格数据的更改及时完成排序，所以需要在工作表的Change事件过程中编写代码。
该事件在用户对工作表进行操作编辑修改的时候触发。
另外，本例还使用了Application对象的Intersect方法，该方法返回一个Range对象，该对象表示两个或多个区域重叠的矩形区域。
其语法格式如下：
表达式.Intersect（Arg1,Arg2,Arg3,Arg4,Arg5,Arg6,Arg7,Arg8,Arg9,Arg10,Arg11,Arg12,
Arg13,Arg14,Arg15,Arg16,Arg17,Arg18,Arg19,Arg20,Arg21,Arg22,Arg23,Arg24,Arg25,Arg26,Arg27,Arg28,Arg29,Arg30）
该方法最多可使用30个单元格区域作为参数，至少需使用两个参数。
3．案例说明
某公司统计了部分员工的销量和工时，同时提供了员工所处的地区。
本例中，当用户输入新员工的地区属性时，需要按照“地区”列进行自动排序。
基础数据如图6.15所示。
图6.15原始数据
4．编写代码
要完成本例的功能，需要在工作表的Change事件过程中编写以下代码：
PrivateSubWorksheet_Change（ByValTargetAsRange）
DimrngAsRangeDimintRowAsIntegerDimintColAsInteger
IfTarget.Column<>2ThenExitSub
IfNotApplication.Intersect（Target,[B2:
B500]）IsNothingThenSetrng=ActiveSheet.Range（"A1"）.CurrentRegion
intRow=rng.Rows.CountintCol=rng.Columns.Count
Setrng=rng.Offset（2,0）.Resize（intRow-2,intCol）rng.SortKey1:
=Range（"B2"）
EndIf
EndSub
5．程序结果
本例是事件触发形式的。
因此，用户需要首先对工作表进行编辑，例如，在“地区”数据列输入新的数据“东部”，如图6.16所示。
图6.16输入新的数据
当用户输入新数据后，工作表的事件会自动触发，得到的结果如图6.17所示。
图6.17自动排序的结果
6．程序分析
在上面的代码中，首先判断更改数据的单元格是否为第2列（表示“地区”数据），然后判断更改数据单元格是否为“B2:
B500”单元格区域中的单元格，然后获取当前区域需要排序的单元格区域，使用Sort方法对这个区域进行排序。
6.2对数据进行筛选
在数据分析中，筛选是一个十分常见的功能。
当用户需要分析大量数据的时候，对数据进行筛选是很重要的操作。
从某种程度上来讲，筛选和分类很类似。
在Excel2007中，在“开始”选项卡的“编辑”组中，单击“排序和筛选”按钮，从下拉的菜单按钮中选择相应的命令即可进行数据筛选操作，如图6.18所示。
图6.18进行数据筛选
用户在Excel中进行的筛选操作，都可以通过VBA来实现。
在本小节中，将详细讲解如何通过VBA实现各种筛选功能。
案例118简单筛选
1．功能说明
本案例的主要功能是使用VBA对原始数据进行简单筛选。
简单筛选的含义就是，将原始数据按照某个数据字段进行筛选。
筛选后的数据结果是仅显示某类数据
2．语法说明
在ExcelVBA中，使用Range对象的AutoFilter方法，可对Range区域的数据中使用“自动筛选”筛选一个列表。
该方法的语法如下：
表达式.AutoFilter（Field,Criteria1,Operator,Criteria2,VisibleDropDown）
各参数的含义如下：
eField：
相对于作为筛选基准字段（从列表左侧开始，最左侧的字段为第一个字段）的字段的整型偏移量。
eCriteria1：
筛选条件，为一个字符串。
使用“=”可查找空字段，或者使用“<>”查找非空字段。
如果省略该参数，则搜索条件为All。
如果将Operator设置为xlTop10Items，则Criteria1指定数据项个数（例如，“10”）。
eOperator：
指定筛选类型，可用常量如表6-1所示。
eCriteria2：
第二个筛选条件（一个字符串）。
与Criteria1和Operator一起组合成复合筛选条件。
eVisibleDropDown：
如果为True，则显示筛选字段的自动筛选下拉箭头。
如果为False，则隐藏筛选字段的自动筛选下拉箭头。
默认值为True。
表6-1筛选类型
名称
值
描述
xlAnd
1
条件1和条件2的逻辑与。
xlBottom10Items
4
显示最低值项（条件1中指定的项数）。
xlBottom10Percent
6
显示最低值项（条件1中指定的百分数）。
xlFilterCellColor
8
单元格颜色
xlFilterDynamic
11
动态筛选
xlFilterFontColor
9
字体颜色
xlFilterIcon
10
筛选图标
xlFilterValues
7
筛选值
xlOr
2
条件1和条件2的逻辑或。
xlTop10Items
3
显示最高值项（条件1中指定的项数）。
xlTop10Percent
5
显示最高值项（条件1中指定的百分数）。
3．案例说明
某公司统计了部分员工上、下两半年销量，同时提供了员工所处的地区。
本例中，用户需要对数据按照“地区”数据字段
升级会员 