深入掌握Pivot函数的用法

一、Pivot函数简介

Pivot函数是Excel中的高级函数之一，它可以把一列数据格式化为多列。在数据透视表中，我们经常会使用到pivot函数。pivot函数允许将一个表格中多列的数据转换成新的表格，表格行和列的数据、格式可以根据定义的规则产生并进行更改。

以下是Pivot函数语法:

PivotTable_Field.Name

其中，PivotTable_Field代表透视表中要选取的字段，Name代表数据透视表中的任何数据项，例如Sum、Count等等。

二、Pivot函数基本用法

使用pivot函数，第一步是将Excel数据转换为表格，然后在表格中使用pivot表功能。以下是pivot数据的表格示例：

Student  Course  Grade  TotalGrade
Tom       Math     80      230
Tom      English   85      230
Tom     Geography  65      230
Lucy     Biology   90      275
Lucy      Music    85      275
Lucy     Science   100     275
...

我们希望将四个项目按照学生分组，以便更好地了解每个学生的成绩情况。这时我们就可以使用pivot函数。以下是示例代码：

Sub TestPivotTable()
    '选中数据
    Range("A1:D7").Select
    '插入透视表，位置设置为单元格F3
    '我们选取了数组A1:D7的范围
    ActiveWorkbook.PivotTableWizard TableDestination:=Range("F3"), _
        TableName:="PivotTable1", _
        RowGrand:=True, _
        ColumnGrand:=True, _
        BaseField:="Student", _
        BaseItem:="All"
End Sub

可以看到，我们使用了PivotTableWizard函数，它接受以下参数:

• TableDestination: 定义透视表的位置。
• TableName: 定义透视表的名称。
• ColumnGrand: 是否显示列总计。
• RowGrand: 是否显示行总计。
• BaseField: 定义透视表显示的字段。
• BaseItem: 定义透视表中表格的内容, All表示所有项目。

三、Pivot函数高级用法——自定义计算项

对于一些需要复杂计算的情况，我们可以使用pivot函数的高级用法自定义计算项。以下是一个示例：

Sub TestPivotTable2()
    Dim PT As PivotTable
    Dim PF As PivotField

    '选中多个单元格
    Range("A1:C7").Select
    '创建透视表，并设置相应参数
    Set PT = ActiveSheet.PivotTableWizard(TableDestination:=Range("H1"), _
    TableName:="PivotTable2", _
    SourceType:=xlDatabase, _
    SourceData:=Range("A1:C7"), _
    RowGrand:=True, _
    ColumnGrand:=True, _
    BaseField:="Student", _
    BaseItem:="All")
    '设置分析字段，允许添加新字段
    Set PF = PT.PivotFields("Grade")
    PF.Orientation = xlDataField
    PF.Function = xlSum
    '添加新字段
    PT.AddDataField PT.PivotFields("Grade"), "Average", xlAverage
End Sub

在此示例中，我们首先选择了A1到C7的范围，然后通过PivotTableWizard函数创建了一个透视表，透视表名称为PivotTable2。接下来，我们设置了分析字段为Grade，使用Sum函数计算数据，并添加了一个新的字段，该字段使用Average函数计算数据。

四、Pivot函数高级用法——自定义换行运算符

使用自定义运算符，我们可以更加灵活地控制透视表中的数据。以下是一个实例：

Sub TestPivotTable3()
    Dim PT As PivotTable
    Dim PF As PivotField

    '选中多个单元格
    Range("A1:C7").Select
    '创建透视表，并设置相应参数
    Set PT = ActiveSheet.PivotTableWizard(TableDestination:=Range("H1"), _
    TableName:="PivotTable3", _
    SourceType:=xlDatabase, _
    SourceData:=Range("A1:C7"), _
    RowGrand:=True, _
    ColumnGrand:=True, _
    BaseField:="Student", _
    BaseItem:="All")
    '设置分析字段，允许添加新字段
    Set PF = PT.PivotFields("Course")
    '循环遍历值，并添加新字段到透视表中
    For Each sItem In PF.PivotItems
        PT.AddDataField PT.PivotFields("Grade"), sItem.Value & "_Avg", xlAverage
    Next sItem
    '设置换行运算符，依赖于重音符号
    PT.DataPivotField.PivotFilters(1).Value1 = "~"
End Sub

在此示例中，我们仍然选择了A1到C7的范围。我们首先对Course字段进行循环处理，并添加每个Course值的平均值，然后使用重音符号作为分隔符，将平均值分隔开。接下来，我们将设置DataPivotField.PivotFilters（1）.Value1属性，将符号~用作换行符。

五、Pivot函数高级用法——自定义样式

最后，我们可以使用Pivot函数的高级用法自定义透视表的样式，使其看起来更美观。以下是示例代码：

Sub TestPivotTable4()
    Dim PT As PivotTable
    Dim PF As PivotField
    Dim pi As PivotItem

    '选中多个单元格
    Range("A1:D7").Select
    '创建透视表，并设置相应参数
    Set PT = ActiveSheet.PivotTableWizard(TableDestination:=Range("H1"), _
    TableName:="PivotTable4", _
    RowGrand:=True, _
    ColumnGrand:=True, _
    BaseField:="Student", _
    BaseItem:="All")
    '设置分析字段为TotalGrade
    PT.AddDataField PT.PivotFields("TotalGrade"), "Total Grade", xlSum
    '设置样式
    PT.TableStyle2 = "PivotStyleMedium2"
End Sub

在此示例中，我们首先选择了A1到D7的单元格。然后我们开始创建透视表，在透视表中使用了总成绩字段，总成绩字段使用Sum函数计算，然后我们使用“PivotStyleMedium2”样式设置了透视表的样式。

总结

通过上述的步骤和代码示例，我们可以看到Pivot函数在透视表中的强大功能，它可以帮助我们更好地组织和过滤数据。