文章為B端產品經理根據入職1年來工作所需，結合Excel線上課程所學，總結沉淀的數據透視表文檔。

作者嘗試用兩篇（函數篇+透視表篇）講述初階產品Excel80%職場需求，接上篇，本篇講述數據透視表部分。

本文將從如下圖所示 基本操作、布局與格式、組合功能、技巧四個部分進行講述，只要用心掌握以下四個部分，基本解決80%的難題，工作效率會有質的提升。

一、基本操作

基本操作包括【創建透視表基本操作】、【插入計算字段】、【使用切片器】、【數據源更新與更改】四部分。

1. 3步創建數據透視表

那什么是數據透視表？

數據透視表是交互式的匯總和分析數據的工具，簡單來說就是把明細表進行分類匯總的過程，可以使用戶通過簡單的拖拽操作，完成復雜的數據分類匯總，可以說是Excel中最實用、最常用的功能。所謂“透視”，即從數據背后找到聯系，從而將看似雜亂的數據轉化為有價值的信息。

結合函數理解數據透視表及其基本操作：

舉個例子：A公司銷售的KPI要求為“每天30秒以上電話數/人為25個”，現需統計12月2日各部門KPI完成情況。如下：左圖為A公司12月2日銷售外呼數據，右圖為需要獲取的A公司銷售部各部門KPI完成情況（部門完成率=部門實際值總和/部門目標值總和）。

根據上篇內容，我們可以用SUMIF函數，快速計算出銷售一部、銷售二部、銷售三部的30秒電話數和30秒電話數目標值，最后在完成率列輸入公式=I4/J4計算出完成率，如下：

函數計算的方法，雖然也能較快的計算出我們需要的結果，但效率不高（這里畢竟只是計算2個值，如果我們計算的值較多時效率問題會更明顯）。在此，我們介紹快速按需求獲取匯總數據的方法-數據透視表：

第一步：選中目標數據：選中目標區域任意單元格，Ctrl+A。

第二步：插入數據透視表：【插入】選項卡-【數據透視表】，【創建數據透視表】彈窗：“選擇要分析的數據”（默認即可）和“選擇要放置數據透視表的位置（現有工作表）”。彈窗選項說明如下：

（1）【請選擇要分析的數據】：如針對工作簿內數據分析，則點擊“選擇一個表或區域”（因為我們插入數據透視表前，已經選擇區域，所以一般情況下，此處默認即可，也可以進行修改）；針對非工作簿內數據分析，則點擊“使用外部數據源”。

（2）【選擇要放置數據透視表的位置】：如數據字段數較多且分析較復雜的情況下，一般選擇“新工作表”，會在新的“sheet”中生成透視表；數據字段數較少的情況下，可選擇“現有工作表”，在當前“sheet”中所選區域生成透視表。

第三步：選擇字段，生成透視表：從【字段名稱】列表里，點擊字段拖拽至“篩選器、列、行、值”當中，如下圖所示：給到的案例比較簡單，只需要【行】和【值】兩部分即可獲取需要的結果。透視表結構如下圖，詳細說明如下：

（1）行、列、值的應用：數據維度方在行，自變量放在列（因變量為值）。

如果我們想要看的是每一天，不同部門“30秒電話量總和”的差異，則日期是我們查看的數據維度（按照日期把數據拆分組，一個日期為一組數據，占到一行，呈現出來的就是有多少個日期就會有多少行數據）；部門是自變量；而“某天某部門的30秒電話量總和”是因變量。

如果我們想要看的是同一部門，不同日期“30秒電話量總和”的差異，則部門是我們查看的數據維度（按照部門把數據拆分組，一個部門為一組數據，占到一行，呈現出來的就是有多少個部門就會有多少行數據）；日期是自變量；而“某部門某日期的30秒電話量”是因變量。

（2）值：匯總方式和顯示方式介紹如下

匯總方式：如上所說的因變量-某日期某部門30秒電話量總和，即對數據源表的數據進行求和，求和就是匯總方式。常用的主要是求和和計數；

數據顯示方式：即將匯總出來的結果以某種方式展示，從而更清晰的看出數據之間的關系和邏輯。常用的主要是總計的百分比和父行匯總的百分比；

①總計的百分比：個體占總體的情況，每一項分類匯總的值占總計的百分比。如：“某日期某部門30秒電話量總和” 占“數據源中所有日期、所有部門30秒電話量總和”的百分比

②父級百分比：個體占局部的情況，局部百分比。某列*行字段的匯總結果/行字段*所有列（即父行）的匯總結果（如上左圖：12月2日銷售二部的30秒電話數之和/12月2日所有部門的30秒電話數之和）

2. 插入計算字段

需求的結果數據一般情況下都可使用“值”字段生成，因為“值”字段中的匯總方式包含了使用頻率較高的通用的計算功能，但有一定的局限，而計算字段極大擴展了數據透視表的計算功能。

比如原始數據表中有一列數據為目標值，有一列數據為實際值，那么在數據透視表中可以通過計算字段輸入公式=30秒電話量/30秒電話量目標值，來求出完成率，方法如下圖所示：

選中透視表任意單元格區域，右擊選擇公式-計算字段輸入字段名稱，輸入公式：公式中的字段在“字段列表”選擇字段插入

3. 使用切片器

切片器功能同我們日常使用的數據報表（或產品在設計報表功能）時的篩選項是一樣的，如下圖所示，的數據報表中支持按日期篩選，2010版以上的excel版本的切片器功能也可以實現，方法如下。

選中數據透視表任意單元格，在【數據透視表工具】選項卡下的【選項】子選項卡下單擊【插入切片器】的下拉按鈕，在彈出的【插入切片器】對話框中勾選自己所需的內容即可。切片器對象的右上角，有兩個按鍵，左邊的是多選按鈕，后面的按鍵是取消篩選的按鈕。

4. 數據源刷新和更改

（1）數據源刷新

很多時候我們的數據源是不定期發生變化的，這就要求在數據透視表中也要體現出來，此時不需要重新創建一個新的數據透視表，刷新一下即可（原基礎上修改，不增加行列的話）。

說明：大多數場景下使用的數據源均非外部數據源，本文僅介紹的為數據源為本工作簿的刷新方法。

1）手動刷新數據透視表：在數據透視表中的任意單元格區域鼠標右鍵，在彈出的快捷菜單中單擊【刷新】命令即可；或，在【數據透視表】工具選項卡中，單擊【刷新】/【全部刷新】按鈕。

2）打開文件時刷新數據透視表：在數據透視表中的任意單元格區域鼠標右鍵，在彈出的快捷菜單中單擊【數據透視表選項】命令，在【數據】選項卡小紅，勾選“打開晚間時刷新數據”。

（2）數據源更改

如果增加了行或者列，只是刷新是不行的，還需要更改數據源。

1）選中數據透視表中的任意單元格區域，在【數據透視表】工具選項卡中，單擊【更改數據源】按鈕，更改數據源區域。

2）將數據源表設置成“表格”，選中數據源，【插入選項卡】點擊【表格】按鈕，設置成表格。不管增加行還是列都不需要再去更改數據源，只需要刷新即可。（需要注意：只針對將數據源更改為“表格”之后建立的透視表有效）

二、布局與格式

首先介紹綜合應用，1圖看懂布局與格式的作用，3步解決在工作中的需求場景中的布局與格式問題：

在涉及到多個行字段的時，Excel生成的透視表的默認格式（如下圖1左）是不滿足我們查看和分析的需要的，一般都期望調整成常規的表格格式（如下圖1右）。只需3步操作，操作說明如下（見下圖2）：

第一步：選中透視表任意單元格，【設計】選項卡-【報表布局】-“表格形式”且“重復項目標簽”；

第二步：選中透視表任意單元格，右擊，取消勾選【分類匯總”…”】。

第三步：選中透視表任意單元格，右擊，選擇【數據透視表】-【顯示】，取消“展開/折疊按鈕”；

1. 布局

數據透視表共有三種布局形式，分別是壓縮形式、大綱形式、表格形式，各有不同的特點。如下圖所示：選中數據透視表中的任意單元格區域，【設計】選項卡，點擊【報表布局】更改布局形式。

壓縮形式：是Excel默認的透視表格式，主要的特點是：無論疊加多少個行字段，都只占一列，分項匯總顯示在每項的上方。大綱形式：主要特點是：有幾個行字段就會占幾列，即行字段會并排顯示，分項匯總顯示在每項的上方。如下圖，有部門和小組兩個行字段，大綱形式的布局會占兩列，而壓縮形式只占一列。表格形式：是最常用的一種形式。主要特點是：與大綱形式一樣，有幾個行字段就會占幾列，行字段會并排顯示，有幾個行字段會占幾列；與大綱形式不同的是，表格形式是有表格的（如下圖所示）且分項匯總顯示在每項的下方。

2. 格式標簽項重復顯示：如“一、布局”中所述，布局格式選擇中，可進行標簽是否重復的設置。顯示/隱藏分類匯總：選中透視表任意單元格區域，【右擊】，勾選/取消勾選【分類匯總】即可。合并行標簽：選中透視表任意單元格區域，【右擊】-【數據透視表選項】-【布局和格式選項卡】-【合并且居中排列帶標簽的單元格】（只對表格形式布局有效）插入空行間隔：【設計】選項卡- 【布局】-【空行】-【在每個項目后插入空行】取消字段前”+-“符：選中透視表任意單元格區域，【右擊】-【數據透視表選項】-【展開/折疊按鈕】三、組合功能

數據透視表中的組合功能，一方面能按照給定的跨度對“日期、數值等可計算字段””進行組合，比如組合出按年、季度、月、日，甚至小時、分……的匯總；另一方面，也可通過手動選擇的方式，將文本格式的數據按照自定義的方式進行組合，比如組合出一線城市、二線城市等等。

通過組合功能將這些不同數據類型的數據項按多種組合方式進行分組，大大增強了數據表分類匯總的延伸性，方便用戶提取滿足特定需求的數據子集。

1. 按時間組合

在工作場景中，一般會獲取時間范圍(幾個月)的天維度的明細數據為一個數據源，在通過透視表進行分析。比如：想看本季度各月各部門電話量的完成情況，對于這一需求，可對日期進行組合。

具體方法如下圖所示：選中透視表日期列任意單元格，右擊，選擇【組合】，進行分組設置，可根據需求更改起始日期，從完成日期列表中選擇分類維度“月/季度/年……”，即可生成我們需要的數據格式。

2. 按數值組合

如果是統計得分情況或年齡分段情況等數據列數值的分布情況，就需要用到透視表的數值分組，選中“分值”列的任意單元格，右鍵選擇“創建組”，在組合中可設置起始和結尾以及步長。

3. 文本分組

如果是按地區統計或者個性化統計需求，可直接在透視表里面創建文本分組，在需要統計的列中，按住Ctrl鍵選擇要組合的單元格，然后點擊“鼠標右鍵”選擇“創建組”即可，數據透視表就會按照我們所選定的內容進行組合，可以自行修改組的名稱，例如改為華北大區。

四、技巧與建議1. 透視表技巧

（1）表頭格式：表頭只能有一行；字段不能為空（相同字段名會被自動添加序號，進行區別）。

（2）不能有合并單元格。如下圖，3步處理數據源：取消合并單元格、定位空值、自動填充。

（3）數值類數據不能為文本格式。轉換成常規數值的方法：使用“分列”功能進行處理，選中數據，【數據】菜單-選擇“分列”（點擊“下一步”-完成即可）。

（4）需對透視表數據再進行函數計算的，可將透視表轉為普通表格：粘貼為值。