一、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函數在透視表中的強大功能,它可以幫助我們更好地組織和過濾數據。
原創文章,作者:AZFV,如若轉載,請註明出處:https://www.506064.com/zh-hk/n/136490.html