Excel 数据处理.docx

1、Excel 数据处理第 6 章 数据处理通过 Excel 相关对象可对工作表中的数据进行操作，如处理单元格区域的公式、对数 据进行查询、排序、筛选等操作。本章演示使用 VBA 进行处理数据的实例。6.1 对数据进行排序在使用 Excel 分析数据的时候，用户经常需要对数据进行排序，得到各种结果。在 Excel VBA 中，用户同样可以对数据进行各种排序。其中包括：按照某数据序列进行排序，自定 义排序的条件等。下面将根据条件进行分析。案例 112 对数据进行排序1功能说明在 Excel 的基础功能中，对数据进行简单排序是十分强大的功能。用户可以对一组数 据按照多个关键字进行排序，可以选择升序，也

2、可以选择降序。这些功能也可以通过 Excel VBA 代码来实现。2语法说明在 Excel 2007 中，用户对数据进行排序时，在单元格中单击作为关键字的列，选择“开 始”选项卡“编辑”组中的“排序和筛选”按钮中的相关命令，可对工作表中的数据进行 排序。但是，这种方法有一个明显的缺陷，就是用户排序的数据对象是全部数据表的数据， 而无法选择特定区域的数据。在 VBA 代码中，用户可方便地控制排序的区域，Range 对象的 Sort 方法可对值区域 进行排序。其语法格式如下：该方面的各参数的含义如下：e Key1：指定第一排序字段，作为区域名称（字符串）或 Range 对象；确定要排序的值。e O

3、rder1：确定 Key1 中指定的值的排序次序，可设置为常量 xlAscending（升序）或 xlDescending（降序）。e Key2：第二排序字段。e Type：指定要排序的元素。e Order2：确定 Key2 中指定的值的排序次序。e Key3：第三排序字段e Order3：确定 Key3 中指定的值的排序次序。e Header：指定第一行是否包含标题信息。e OrderCustom：指定在自定义排序次序列表中的基于一的整数偏移。e MatchCase：设置为 True，则执行区分大小写的排序，设置为 False，则执行不区分大 小写的排序；不能用于数据透视表。e Orient

4、ation：指定以升序还是降序排序。可用常量 xlSortColumns（按列排序）或xlSortRows（按行排序，这是默认值）。e SortMethod：指定排序方法。可用常量 xlPinYin（按汉语拼音顺序排序，这是默认值） 或 xlStroke（按每个字符的笔划数排序）。e DataOption1：指定 Key1 中所指定区域中的文本的排序方式，可使用常量 xlSortNormal（分别对数字和文本数据进行排序，这是默认值）或 xlSortTextAsNumbers（将文本作 为数字型数据进行排序）。e DataOption2：指定 Key2 中所指定区域中的文本的排序方式。e Da

5、taOption3：指定 Key3 中所指定区域中的文本的排序方式。3案例说明某公司统计了部分员工上、下两半年销量，同时提供了员工所处的地区。本例中，用 户可以对该数据按照各数据字段进行排序。基础数据如图 6.1 所示。图 6.1 销量数据表4编写代码实现按照“上半年销量”数据列（C 列）字段进行排序的代码如下：Sub SortNumber()Dim rng As RangeDim LongRow As LongDim LongCol As LongLongRow = ActiveSheet.Range(A1).CurrentRegion.Rows.Count LongCol = Active

6、Sheet.Range(A2).CurrentRegion.Columns.Count Set rng = ActiveSheet.Range(Cells(2, 1), Cells(LongRow - 1, LongCol) rng.Sort key1:=ActiveSheet.Range(Cells(2, 3), Cells(LongRow - 1, 3)End Sub以上代码首先获取当前工作表中需要排序的单元格区域，对该区域使用 Sort 方法按“上半年销量”列进行排序。5程序结果当用户运行上面的程序代码后，结果如图 6.2 所示。图 6.2 排序的结果6程序分析根据上面的程序代码分析，用

7、户可以对数据系列进行其他的排序。只需要修改下面这 行代码的具体内容就可以 rng.Sort key1:=ActiveSheet.Range(Cells(2, 3), Cells(LongRow - 1, 3) 案例 113 两个关键字排序1功能说明在 Excel 的排序中，用户除了可以选择数据表中任何一个字段进行排序之外，还可以 对数据表按照多个关键字进行排序。其主要方法是使用 Add 方法，添加新的排序信息。在 Excel 操作中，就相当于图 6.3 所示。图 6.3 添加排序条件2语法说明在本例中，所使用的语法介绍和前面案例中的 Sort 方法类似。只是需要使用 Add 方 法来添加一个新

8、的排序条件。3案例说明某公司统计了部分员工的销量和顾客人数，同时提供了员工所处的地区。本例中，用 户可以对该数据首先按照地区排序，然后按照销量排序。基础数据如图 6.4 所示。图 6.4 销量数据表4编写代码实现地区和销量排序的代码如下：S Sub SortMultiNumbers() Range(A1:D10).SelectActiveWorkbook.Worksheets(Sheet1).Sort.SortFields.Clear ActiveWorkbook.Worksheets(Sheet1).Sort.SortFields.Add Key:=Range(B2:B10) _, Sort

9、On:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal ActiveWorkbook.Worksheets(Sheet1).Sort.SortFields.Add Key:=Range(C2:C10) _, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormalWith ActiveWorkbook.Worksheets(Sheet1).Sort.SetRange Range(A1:D10).Header = xlYes.MatchCase

10、 = False.Orientation = xlTopToBottom.SortMethod = xlPinYin.Apply End WithEnd Sub5程序结果当用户运行上面的程序代码后，结果如图 6.5 所示。图 6.5 排序的结果6程序分析在上面的程序代码中，首先设置排序的条件是“地区”，然后添加排序条件“顾客人 数”。最后设置排序的结果。案例 114 多关键字排序1功能说明在用户对数据进行排序的时候，用户可能需要按照多列关键字进行排序。而不仅仅是 两个关键字。对于多关键字（多条件）排序的情况，用户可以沿用前面案例的方法。但是， 对于多于 3 个关键字的情况，则可以使用前面章节所

11、讲解的程序结构来完成。 2语法说明对于超过三个关键字的排序，本案例将使用循环结构，依次对数据按照关键字进行排 序。但是，用户需要注意的是，需要先将数据按最后一个关键字排序，接着再将数据按倒数第二个关键字排序，最后将数据按主要（第一个）关键字排序，即可得到所需要的排列。 同时，使用这种方法，用户可使用任意数量的关键字进行排序。3案例说明某公司统计了部分员工的销量和增加的顾客人数，同时提供了员工所处的地区。本例 中，用户可以对该数据依次按照地区、销量和增加顾客进行排序。基础数据如图 6.6 所示。图 6.6 多关键字排序4编写代码多关键字排序的 VBA 代码如下：Sub MultSortNum()

12、 Dim rng As Range Dim i As IntegerApplication.ScreenUpdating = False Dim LongRow As LongDim LongCol As LongLongRow = ActiveSheet.Range(A1).CurrentRegion.Rows.Count LongCol = ActiveSheet.Range(A2).CurrentRegion.Columns.Count Set rng = ActiveSheet.Range(Cells(2, 1), Cells(LongRow - 1, LongCol)With rng

13、For i = 5 To 3 Step -1.Sort key1:=ActiveSheet.Range(B2).Offset(, i - 3) MsgBox Range(B2).Offset(, i - 3)Next End WithApplication.ScreenUpdating = TrueEnd Sub5程序结果当用户运行上面的程序代码后，结果如图 6.7 所示。图 6.7 排序的结果6程序分析从图中的结果可以看出，首先按 B 列（地区）排序，部门相同时再按 C 列（销量）排 序，基础工资相同再按 D 列（增加的销量）排序。案例 115 自定义排序1功能说明在 Excel 进行分析的

14、时候，用户经常需要设定自己的排序标准。而在 Excel 中，所提 供的排序标准是常见的降序、升序或者字母排序等。当用户需要按照自己特色标准排序时， 则需要自行编写相应的代码。2语法分析本例演示用 VBA 代码创建自定义序列的方法，主要用 AddCustomList 方法添加自定 义序列。AddCustomList 方法为自定义自动填充和/或自定义排序添加自定义列表。其语法 格式如下：表达式.AddCustomList(ListArray, ByRow) 参数的含义如下：e ListArray：将源数据指定为字符串数组或 Range 对象。e ByRow：仅当 ListArray 为 Range

15、 对象时使用。如果为 True，则使用区域中的每一行 创建自定义列表；如果为 False，则使用区域中的每一列创建自定义列表。如果省略该 参数，并且区域中的行数比列数多（或者行数与列数相等），则 Excel 使用区域中的每 一列创建自定义列表。如果省略该参数，并且区域中的列数比行数多，则 Excel 使用区域中的每一行创建自定义列表。使用 Application 对象的 GetCustomListNum 方法返回字符串数组的自定义序列号。其 语法格式如下：表达式.GetCustomListNum(ListArray) 参数 ListArray 为一个字符串数组。3案例说明某公司统计了部分员工的

16、销量和增加的顾客人数，同时提供了员工所处的地区。本例 中，用户需要对地区按照“东部、南部、西部和北部”的次序进行排序。基础数据如图 6.8 所示。图 6.8 原始数据表4编写代码在本例中，用户首先需要定义自定义的排序序列，如图 6.9 所示。图 6.9 自定义序列自定义序列排序的 VBA 代码如下：Sub SelfSortNumbers() Dim rng As RangeDim n As Integer Dim rng1 As Range Dim arrApplication.ScreenUpdating = FalseDim LongRow As Long Dim LongCol As L

17、ongLongRow = ActiveSheet.Range(A1).CurrentRegion.Rows.Count LongCol = ActiveSheet.Range(A2).CurrentRegion.Columns.CountSet rng1 = ActiveSheet.Range(Cells(1, 1), Cells(LongRow - 1, LongCol)With Worksheets(Sheet2)r = .Range(A1).End(xlDown).RowSet rng = .Range(.Cells(1, 1), .Cells(r, 1) End WithWith Ap

18、plicationarr = .WorksheetFunction.Transpose(rng).AddCustomList ListArray:=arr n = .GetCustomListNum(arr1)End Withrng1.Sort key1:=ActiveSheet.Range(Cells(2, 2), Cells(LongRow - 1, 2), _ Order1:=xlAscending, Header:=xlYes, OrderCustom:=n + 1 Application.ScreenUpdating = TrueEnd Sub5程序结果当用户运行上面的程序代码后，结

19、果如图 6.10 所示。图 6.10 排序结果6程序分析以上代码首先获取需要排序的单元格区域，接着将工作表 Sheet2 中的数据添加到自定 义序列中，再使用自定义序列进行排序。这是自定义序列的通用方法。同时，当用户运行 该程序代码后，可以查看对应的排序条件如图 6.11 所示。图 6.11 排序条件如果用户希望删除相应的排序条件，可以使用 DeleteCustomList 方法。使用 Application对象的 DeleteCustomList 方法删除一个自定义序列。其语法格式如下： 表达式.DeleteCustomList(ListNum) 参数 ListNum 为自定义序列数字。此数

20、字必须大于或等于 5（Excel 有四个不可删除的内置自定义序列）。案例 116 随机排序1功能说明在实际分析过程中，用户除了需要对数据进行标准的排序之外，有时还需要一种特殊 的排序：随机排序。也就是说，某些数据不需要任何特定的排序，完全随机的序列。这个 功能通过 Excel 的基础操作将无法实现，需要通过程序代码实现。2语法说明随机排序是一种特殊的排序，使用 VBA 代码实现随机排序的基本思路是：首先，自 行生成随机序列，然后将这个随机序列当作关键字，对其进行排序，最后的结果就是随时 排序的数据结果。3案例说明某公司统计了部分员工的销量和工时，同时提供了员工所处的地区。本例中，用户需 要对原

21、始数据进行随机排列。基础数据如图 6.12 所示。图 6.12 原始数据3编写代码随机排序的 VBA 代码如下：Sub RandSort()Dim rng As Range Dim intCol As Long Dim intRow As LongRandomize Application.ScreenUpdating = False With ActiveSheetintRow = .Range(A1).CurrentRegion.Rows.CountintCol = .Range(A2).CurrentRegion.Columns.CountFor i = 2 To intRow - 1.

22、Cells(i, intCol + 1) = Int(Rnd * 50) + 2) NextSet rng = .Range(Cells(2, 1), Cells(intRow - 1, intCol + 1)rng.Sort key1:=.Range(Cells(2, intRow + 1), Cells(intRow - 1, intCol + 1).Columns(intCol + 1).Clear End WithApplication.ScreenUpdating = TrueEnd Sub5程序结果当用户运行上面的程序代码后，结果如图 6.13 所示。图 6.13 随机排序的结果由

23、于是随机排序的，当用户再次运行程序代码时，结果会变化，如图 6.14 所示。图 6.14 再次运行程序的结果6程序分析以上代码中，先在在数据的右列添加随机数据，使用的是内置函数 Rnd。然后使用 Sort方法按该列的数据进行排序，最后删除增加的随机数据列。案例 117 自动排序1功能说明本案例的主要功能是实现排序的自动化。自动化的意思是，用户不需要选择排序的操作就可以实现按照某字段自动排列。例如，某公司的员工花名册中，需要自动按照名字的 字母次序进行排列，而不需要每次都进行排序的操作。2语法说明本例需要根据用户对单元格数据的更改及时完成排序，所以需要在工作表的 Change 事件过程中编写代码

24、。该事件在用户对工作表进行操作编辑修改的时候触发。另外，本例 还使用了 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, Arg

25、27, Arg28, Arg29, Arg30)该方法最多可使用 30 个单元格区域作为参数，至少需使用两个参数。3案例说明某公司统计了部分员工的销量和工时，同时提供了员工所处的地区。本例中，当用户 输入新员工的地区属性时，需要按照“地区”列进行自动排序。基础数据如图 6.15 所示。图 6.15 原始数据4编写代码要完成本例的功能，需要在工作表的 Change 事件过程中编写以下代码：Private Sub Worksheet_Change(ByVal Target As Range)Dim rng As Range Dim intRow As Integer Dim intCol As I

26、ntegerIf Target.Column 2 Then Exit SubIf Not Application.Intersect(Target, B2:B500) Is Nothing Then Set rng = ActiveSheet.Range(A1).CurrentRegionintRow = rng.Rows.Count intCol = rng.Columns.CountSet rng = rng.Offset(2, 0).Resize(intRow - 2, intCol) rng.Sort Key1:=Range(B2)End IfEnd Sub5程序结果本例是事件触发形式

27、的。因此，用户需要首先对工作表进行编辑，例如，在“地区” 数据列输入新的数据“东部”，如图 6.16 所示。图 6.16 输入新的数据当用户输入新数据后，工作表的事件会自动触发，得到的结果如图 6.17 所示。图 6.17 自动排序的结果6程序分析在上面的代码中，首先判断更改数据的单元格是否为第 2 列（表示“地区”数据）， 然后判断更改数据单元格是否为“B2:B500”单元格区域中的单元格，然后获取当前区域 需要排序的单元格区域，使用 Sort 方法对这个区域进行排序。6.2 对数据进行筛选在数据分析中，筛选是一个十分常见的功能。当用户需要分析大量数据的时候，对数 据进行筛选是很重要的操作。

28、从某种程度上来讲，筛选和分类很类似。在 Excel 2007 中， 在“开始”选项卡的“编辑”组中，单击“排序和筛选”按钮，从下拉的菜单按钮中选择 相应的命令即可进行数据筛选操作，如图 6.18 所示。图 6.18 进行数据筛选用户在 Excel 中进行的筛选操作，都可以通过 VBA 来实现。在本小节中，将详细讲 解如何通过 VBA 实现各种筛选功能。案例 118 简单筛选1功能说明本案例的主要功能是使用 VBA 对原始数据进行简单筛选。简单筛选的含义就是，将 原始数据按照某个数据字段进行筛选。筛选后的数据结果是仅显示某类数据2语法说明在 Excel VBA 中，使用 Range 对象的 Au

29、toFilter 方法，可对 Range 区域的数据中使用 “自动筛选”筛选一个列表。该方法的语法如下：表达式.AutoFilter(Field, Criteria1, Operator, Criteria2, VisibleDropDown) 各参数的含义如下：e Field：相对于作为筛选基准字段（从列表左侧开始，最左侧的字段为第一个字段）的 字段的整型偏移量。e Criteria1：筛选条件，为一个字符串。使用“=”可查找空字段，或者使用“”查找 非空字段。如果省略该参数，则搜索条件为 All。如果将 Operator 设置为 xlTop10Items， 则 Criteria1 指定数据

30、项个数（例如，“10”）。e Operator：指定筛选类型，可用常量如表 6-1 所示。e Criteria2：第二个筛选条件（一个字符串）。与 Criteria1 和 Operator 一起组合成复合筛 选条件。e VisibleDropDown：如果为 True，则显示筛选字段的自动筛选下拉箭头。如果为 False， 则隐藏筛选字段的自动筛选下拉箭头。默认值为 True。表 6-1 筛选类型名称值描述xlAnd1条件1和条件2的逻辑与。xlBottom10Items4显示最低值项（条件1中指定的项数）。xlBottom10Percent6显示最低值项（条件1中指定的百分数）。xlFilterCellColor8单元格颜色xlFilterDynamic11动态筛选xlFilterFontColor9字体颜色xlFilterIcon10筛选图标xlFilterValues7筛选值xlOr2条件1和条件2的逻辑或。xlTop10Items3显示最高值项（条件1中指定的项数）。xlTop10Percent5显示最高值项（条件1中指定的百分数）。3案例说明某公司统计了部分员工上、下两半年销量，同时提供了员工所处的地区。本例中，用 户需要对数据按照“地区”数据字段