Excel資料分析入門-資料透視表

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

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

注：

• 本文基於 Excel 2016 for Mac 完成，個別介面和 Windows 版略有差異
• 如果要完成 VBA 的部分，Excel for Mac 需要升級到 15.38 版本以上
• Excel 2007 及之後的頂部 Ribbon 選單，文中簡稱為 Ribbon
• 開啟“開發工具”選單的方法也請自行了解
  

I. 源資料

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

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

II. 建立資料透視表

• 此處將工作表重新命名為sheet1
• 首先確保表格第一行是表頭
• 點選表中任意位置
• 選中 Ribbon 中的“插入”
• 點選第一個圖示“資料透視表”，出現“建立資料透視表”對話方塊

注意觀察對話方塊中的各種選項，這裡我們都採用預設值

點選“確定”後，一個空的資料透視表出現在了新工作表中：

III. 資料透視表中的欄位

• 在“資料透視表生成器”選單中，選擇“球隊、平、進球、失球、積分、更新日期”幾個欄位

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

• 分別對當前“值”列表中的幾個欄位，點選其右側的i圖示
• 因為本例中無需計算其預設的“求和”，故將這幾個欄位的“彙總方式”都改為“平均值”

• 暫時關閉“資料透視表生成器”
• 該視窗隨後可以用“欄位列表”按鈕重新開啟

此時一個基本的資料透視表已經成型

IV. 增加自定義欄位

有時基本的欄位並不能滿足分析的需要，此時就可以在資料透視表中插入基於公式計算的自定義欄位。

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

4.1 簡單運算的公式

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

• 點選資料透視表中的任意位置，以啟用“資料透視表分析” Ribbon 標籤
• 點選“欄位、專案和集”按鈕，在彈出的下拉選單中選擇“計算欄位”
• “插入計算欄位”對話方塊會出現
• 在“名稱”中填入“場均進球”
• 在“欄位”列表中分別雙擊“進球”和“場次”
• 以上兩個欄位會出現在“公式”框中，在它們中間鍵入表示除法的斜槓/
• 也就是說，此時“公式”部分為 =進球/場次

• 點選“確定”關閉對話方塊，資料透視表中出現了新的“求和/場均進球”欄位
• 按照之前的方法，將欄位的彙總方式改為“平均值”，確定關閉對話方塊

4.2 呼叫 Excel 公式

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

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

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

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

• 將欄位的彙總方式改為“計數” -- 雖然在此處並無太多實際意義

V. 利用切片器過濾資料

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

切片器的建立非常簡單：

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

• 點選切片器中的專案就可以篩選
• 結合 ctrl 鍵可以多選

VI. 成果

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

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

以上就是建立資料透視表的基本過程。

VII. 自動化建立

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

本例中使用 VBA 指令碼完成與上述例子一樣的任務，對於 VBA 語言僅做簡單註釋，想更多瞭解可以自行查閱官方的文件等

7.1 一鍵生成

此處我們放置一個按鈕在源資料所在的資料表，用於每次點選自動生成一個資料透視表。

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

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

7.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 = True
End Sub複製程式碼

7.3 執行程式

回到介面中，每次點選按鈕就會在新工作表中生成結構和之前例子一致的資料透視表

VIII. 總結

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

IX. 解決 windows 版本中無法開啟 VBA 編輯器

如果遇到此類問題，可按以下步驟嘗試解決：

1. download vbe6ext.OLB from http://www.121down.com/soft/softview-63611.html
2. copy vbe6ext.OLB to C:\Program Files\Common Files\microsoft shared\VBA\VBA6and C:\Program Files\Common Files\microsoft shared\VBA\VBA7and C:\Program Files\Common Files\microsoft shared\VBA\VBA7.1

X. 參考資料

• https://support.office.com/client/Import-and-analyze-data-ccd3c4a6-272f-4c97-afbb-d3f27407fcde#ID0EAABAAA=PivotTables
• https://msdn.microsoft.com/zh-cn/library/office/hh243933(v=office.14).aspx
• https://www.thespreadsheetguru.com/blog/2014/9/27/vba-guide-excel-pivot-tables
• https://zhidao.baidu.com/question/52748104.html
• http://www.databison.com/pivot-table-vba-vba-to-read-modify-a-pivot-table-in-excel/
• https://msdn.microsoft.com/en-us/vba/excel-vba/articles/pivotfield-object-excel
• http://blog.sina.com.cn/s/blog_54044c930100jg9p.html
• http://www.globaliconnect.com/excel/index.php?option=com_content&view=article&id=152:excel-pivot-tables-insert-calculated-fields-a-calculated-items-create-formulas-using-vba&catid=79&Itemid=475
• http://www.databison.com/slicer-vba-code-create-change-or-modify-a-pivot-table-slicer-using-vba/
• https://social.technet.microsoft.com/Forums/office/en-US/1d3e9aa0-2069-4f7b-b6cf-c47e00d637f5/vbe6extolb-could-not-be-loaded?forum=officeitproprevious