數(shù)據(jù)分析Excel必備技能：數(shù)據(jù)透視表使用教程

江米小棗tonylua?| 作者

掘金?|?來源

https://juejin.im/post/6844903504209772557

---

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

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

注：?

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

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

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

• 開啟“開發(fā)工具”菜單的方法也請自行了解
  

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

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

• 此處將工作表重命名為sheet1

• 首先確保表格第一行是表頭

• 點擊表中任意位置

• 選中 Ribbon 中的“插入”

• 點擊第一個圖標“數(shù)據(jù)透視表”，出現(xiàn)“創(chuàng)建數(shù)據(jù)透視表”對話框

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

點擊“確定”后，一個空的數(shù)據(jù)透視表出現(xiàn)在了新工作表中：

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

• 將“平”拖放至“行”列表中的“球隊”上方；表示在“平局”的維度上，嵌套(nesting)的歸納了“球隊”的維度

• 將“更新日期”拖放至“篩選器”列表中；表示可以根據(jù)更新日期來篩選顯示表格數(shù)據(jù)

• 分別對當前“值”列表中的幾個字段，點擊其右側(cè)的i圖標

• 因為本例中無需計算其默認的“求和”，故將這幾個字段的“匯總方式”都改為“平均值”

• 暫時關(guān)閉“數(shù)據(jù)透視表生成器”

• 該窗口隨后可以用“字段列表”按鈕重新打開

此時一個基本的數(shù)據(jù)透視表已經(jīng)成型

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

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

1.簡單運算的公式

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

• 點擊數(shù)據(jù)透視表中的任意位置，以激活“數(shù)據(jù)透視表分析” Ribbon 標簽

• 點擊“字段、項目和集”按鈕，在彈出的下拉菜單中選擇“計算字段”

• “插入計算字段”對話框會出現(xiàn)

• 在“名稱”中填入“場均進球”

• 在“字段”列表中分別雙擊“進球”和“場次”

• 以上兩個字段會出現(xiàn)在“公式”框中，在它們中間鍵入表示除法的斜杠/

• 也就是說，此時“公式”部分為?=進球/場次

• 點擊“確定”關(guān)閉對話框，數(shù)據(jù)透視表中出現(xiàn)了新的“求和/場均進球”字段

• 按照之前的方法，將字段的匯總方式改為“平均值”，確定關(guān)閉對話框

2.調(diào)用 Excel 公式

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

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

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

• 按照剛才的方法新建一個計算字段

• 將上述公式填入“公式”框

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

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

切片器的創(chuàng)建非常簡單：

• 在 Ribbon 中點擊“插入切片器”按鈕

• 在字段列表中選擇“勝”、“負”

• 兩個切片器就出現(xiàn)在了界面中

• 點擊切片器中的項目就可以篩選

• 結(jié)合?ctrl?鍵可以多選

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

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

以上就是創(chuàng)建數(shù)據(jù)透視表的基本過程。

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

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

1.一鍵生成

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

• 在 Ribbon 的“開發(fā)工具”中點擊按鈕

• 在界面任意位置框選一個按鈕的尺寸

• 釋放鼠標后彈出“指定宏”對話框

• 此處我們將“宏名稱”框填入?ThisWorkbook.onCreatePovit

• “宏的位置”選擇“此工作簿”

• 點擊"編輯"后關(guān)閉對話框

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

2.腳本編寫

• 點擊 Ribbon 中“開發(fā)工具”下面第一個按鈕“Visual Basic”

• 在出現(xiàn)的“Visual Basic”編輯器中，選擇左側(cè)的“ThisWorkbook”類目

• 在右側(cè)編輯區(qū)貼入下面的代碼

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

????'?指定數(shù)據(jù)源
????sheet1.Select
????Range("A1").Select

????' 創(chuàng)建透視表
????Set pvtTable = sheet1.PivotTableWizard
????ActiveSheet.Name = "pivot1"
????
????'?指定行和列
????pvtTable.AddFields _
????????RowFields:=Array("平",?"球隊"), _
????????ColumnFields:="Data"

????' 指定數(shù)據(jù)字段
????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:="防守質(zhì)量", Formula:="= IF(凈勝球>=0,2,1)"
????Set pvtField = pvtTable.PivotFields("防守質(zhì)量")
????pvtField.Orientation = xlDataField
????pvtField.Function = xlCount
????pvtField.Name =?"計數(shù)/防守質(zhì)量"

????' 指定切片器
????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

3.運行程序

回到界面中，每次點擊按鈕就會在新工作表中生成結(jié)構(gòu)和之前例子一致的數(shù)據(jù)透視表

• 本文簡單的展示了在 Excel 中創(chuàng)建透視表的過程，以及其篩選、展示數(shù)據(jù)的方式

• 通過?VBA?可以完成和手動創(chuàng)建一樣甚至更多的功能，并大大提高工作效率

后臺回復(fù)關(guān)鍵字：破解，獲取Pycharm 破解版，親測有效哦
后臺回復(fù)關(guān)鍵字：自學(xué)，獲取一份精心整理的 5本 Python 經(jīng)典用書
后臺回復(fù)關(guān)鍵字：國慶，獲取50本電子書。
后臺回復(fù)關(guān)鍵字：1109，獲取PYTHON進階書。

對了，看完記得一鍵四連，這個對我真的很重要。