江米小棗tonylua | 作者

掘金 | 來源

處理數量較大的數據時，一般分為數據獲取、數據篩選，以及結果展示幾個步驟。在 Excel 中，我們可以利用數據透視表(Pivot Table)方便快捷的實現這些工作。

本文首先手把手的教你如何在 Excel 中手動構建一個基本的數據透視表，最后用 VBA 展示如何自動化這一過程。

注：

本文基于 Excel 2016 for Mac 完成，個別界面和 Windows 版略有差異

如果要完成 VBA 的部分，Excel for Mac 需要升級到 15.38 版本以上

Excel 2007 及之后的頂部 Ribbon 菜單，文中簡稱為 Ribbon

開啟“開發工具”菜單的方法也請自行了解

1、源數據

Excel 提供了豐富的數據來源，我們可以從 HTML、文本、數據庫等處獲取數據。

這個步驟本文不展開討論，以下是我們作為分析來源的工作表數據：

2、創建數據透視表

• 此處將工作表重命名為sheet1
• 首先確保表格第一行是表頭
• 點擊表中任意位置
• 選中 Ribbon 中的“插入”
• 點擊第一個圖標“數據透視表”，出現“創建數據透視表”對話框

注意觀察對話框中的各種選項，這里我們都采用默認值

點擊“確定”后，一個空的數據透視表出現在了新工作表中：

3、數據透視表中的字段

• 在“數據透視表生成器”菜單中，選擇“球隊、平、進球、失球、積分、更新日期”幾個字段

• 將“平”拖放至“行”列表中的“球隊”上方；表示在“平局”的維度上，嵌套(nesting)的歸納了“球隊”的維度
• 將“更新日期”拖放至“篩選器”列表中；表示可以根據更新日期來篩選顯示表格數據

• 分別對當前“值”列表中的幾個字段，點擊其右側的i圖標
• 因為本例中無需計算其默認的“求和”，故將這幾個字段的“匯總方式”都改為“平均值”

• 暫時關閉“數據透視表生成器”
• 該窗口隨后可以用“字段列表”按鈕重新打開

此時一個基本的數據透視表已經成型

4、增加自定義字段

有時基本的字段并不能滿足分析的需要，此時就可以在數據透視表中插入基于公式計算的自定義字段。

下面用不同的方法加入兩個自定義字段：

1.簡單運算的公式

首先簡單計算一下各隊的場均進球數：

• 點擊數據透視表中的任意位置，以激活“數據透視表分析” Ribbon 標簽
• 點擊“字段、項目和集”按鈕，在彈出的下拉菜單中選擇“計算字段”
• “插入計算字段”對話框會出現
• 在“名稱”中填入“場均進球”
• 在“字段”列表中分別雙擊“進球”和“場次”
• 以上兩個字段會出現在“公式”框中，在它們中間鍵入表示除法的斜杠/
• 也就是說，此時“公式”部分為 =進球/場次

• 點擊“確定”關閉對話框，數據透視表中出現了新的“求和/場均進球”字段
• 按照之前的方法，將字段的匯總方式改為“平均值”，確定關閉對話框

2.調用 Excel 公式

再簡單的評估一下球隊的防守質量，這里我們假設以如下 Excel 公式判斷：

= IF(凈勝球>=0,2,1)

防守還不錯的取 2，不佳的則標記為 1。

• 按照剛才的方法新建一個計算字段
• 將上述公式填入“公式”框

• 將字段的匯總方式改為“計數” -- 雖然在此處并無太多實際意義

5、利用切片器過濾數據

除了可以在“數據透視表生成器”中指定若干個“過濾器”，切片器(Slicers)也可以用來過濾數據，使分析工作更清晰化。

切片器的創建非常簡單：

• 在 Ribbon 中點擊“插入切片器”按鈕
• 在字段列表中選擇“勝”、“負”
• 兩個切片器就出現在了界面中

• 點擊切片器中的項目就可以篩選
• 結合 ctrl 鍵可以多選

6、成果

至此，我們得到了一個基于源數據的、可以自由組合統計維度、可以用多種方式篩選展示的數據透視表。

可以在 Ribbon 的“設計”菜單中選擇預設的樣式等，本文不展開論述。

以上就是創建數據透視表的基本過程。

7、自動化創建

基本的數據透視表的創建和調整并不復雜，但如果有很多類似的重復性工作的話，使用一些簡單的 VBA 來自動化這一過程，將極大提升工作的效率。

本例中使用 VBA 腳本完成與上述例子一樣的任務，對于 VBA 語言僅做簡單注釋，想更多了解可以自行查閱官方的文檔等

1.一鍵生成

此處我們放置一個按鈕在源數據所在的數據表，用于每次點擊自動生成一個數據透視表。

• 在 Ribbon 的“開發工具”中點擊按鈕
• 在界面任意位置框選一個按鈕的尺寸
• 釋放鼠標后彈出“指定宏”對話框
• 此處我們將“宏名稱”框填入 ThisWorkbook.onCreatePovit
• “宏的位置”選擇“此工作簿”
• 點擊"編輯"后關閉對話框

• 將按鈕名稱改為“一鍵生成透視表”

2.腳本編寫

• 點擊 Ribbon 中“開發工具”下面第一個按鈕“Visual Basic”
• 在出現的“Visual Basic”編輯器中，選擇左側的“ThisWorkbook”類目
• 在右側編輯區貼入下面的代碼

Sub onCreatePovit()????Application.DisplayAlerts =?False????' 聲明變量????Dim sheet1 As Worksheet????Dim pvtTable As PivotTable????Dim pvtField As PivotField????Dim pvtSlicerCaches As SlicerCaches????Dim pvtSlicers As slicers????Dim pvtSlicer As Slicer????'?刪除可能已存在的透視表????Dim existFlag?As?Boolean????Dim ws?As?Worksheet????For?Each ws In Worksheets????????If?ws.Name =?"pivot1"?Then existFlag =?True:?Exit?For????Next????If?existFlag =?True?Then????????Sheets("pivot1").Select????????ActiveWindow.SelectedSheets.Delete????End?If????' 初始化????Set sheet1 = ActiveWorkbook.Sheets("sheet1")????Set pvtSlicerCaches = ActiveWorkbook.SlicerCaches????'?指定數據源????sheet1.Select????Range("A1").Select????' 創建透視表????Set pvtTable = sheet1.PivotTableWizard????ActiveSheet.Name = "pivot1"????????'?指定行和列????pvtTable.AddFields _????????RowFields:=Array("平",?"球隊"), _????????ColumnFields:="Data"????' 指定數據字段????Set pvtField = pvtTable.PivotFields("失球")????pvtField.Orientation = xlDataField????pvtField.Function = xlAverage????pvtField.Name = "平均值/失球"????Set pvtField = pvtTable.PivotFields("進球")????pvtField.Orientation = xlDataField????pvtField.Function = xlAverage????pvtField.Name = "平均值/進球"????Set pvtField = pvtTable.PivotFields("積分")????pvtField.Orientation = xlDataField????pvtField.Function = xlAverage????pvtField.Name = "平均值/積分"????'?指定計算字段????pvtTable.CalculatedFields.Add Name:="場均進球", Formula:="=進球/場次"????Set pvtField = pvtTable.PivotFields("場均進球")????pvtField.Orientation = xlDataField????pvtField.Function = xlAverage????pvtField.Name =?"平均值/場均進球"????pvtTable.CalculatedFields.Add Name:="防守質量", Formula:="= IF(凈勝球>=0,2,1)"????Set pvtField = pvtTable.PivotFields("防守質量")????pvtField.Orientation = xlDataField????pvtField.Function = xlCount????pvtField.Name =?"計數/防守質量"????' 指定切片器????Set pvtSlicers = pvtSlicerCaches.Add(pvtTable, "勝", "勝_" & ActiveSheet.Name).slicers????Set pvtSlicer = pvtSlicers.Add(ActiveSheet, , , , 300, 400)????Set pvtSlicers = pvtSlicerCaches.Add(pvtTable, "負", "負_" & ActiveSheet.Name).slicers????Set pvtSlicer = pvtSlicers.Add(ActiveSheet, , , , 350, 450)????????????'?指定過濾器????Set pvtField = pvtTable.PivotFields("更新日期")????pvtField.Orientation = xlPageField????Application.DisplayAlerts =?TrueEnd Sub

3.運行程序

回到界面中，每次點擊按鈕就會在新工作表中生成結構和之前例子一致的數據透視表

8、總結

• 本文簡單的展示了在 Excel 中創建透視表的過程，以及其篩選、展示數據的方式
• 通過 VBA 可以完成和手動創建一樣甚至更多的功能，并大大提高工作效率