文章為B端產(chǎn)品經(jīng)理根據(jù)入職1年來工作所需，結(jié)合Excel線上課程所學(xué)，總結(jié)沉淀的數(shù)據(jù)透視表文檔。

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

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

一、基本操作

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

1. 3步創(chuàng)建數(shù)據(jù)透視表

那什么是數(shù)據(jù)透視表？

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

結(jié)合函數(shù)理解數(shù)據(jù)透視表及其基本操作：

舉個(gè)例子：A公司銷售的KPI要求為“每天30秒以上電話數(shù)/人為25個(gè)”，現(xiàn)需統(tǒng)計(jì)12月2日各部門KPI完成情況。如下：左圖為A公司12月2日銷售外呼數(shù)據(jù)，右圖為需要獲取的A公司銷售部各部門KPI完成情況（部門完成率=部門實(shí)際值總和/部門目標(biāo)值總和）。

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

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

第一步：選中目標(biāo)數(shù)據(jù)：選中目標(biāo)區(qū)域任意單元格，Ctrl+A。

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

（1）【請(qǐng)選擇要分析的數(shù)據(jù)】：如針對(duì)工作簿內(nèi)數(shù)據(jù)分析，則點(diǎn)擊“選擇一個(gè)表或區(qū)域”（因?yàn)槲覀儾迦霐?shù)據(jù)透視表前，已經(jīng)選擇區(qū)域，所以一般情況下，此處默認(rèn)即可，也可以進(jìn)行修改）；針對(duì)非工作簿內(nèi)數(shù)據(jù)分析，則點(diǎn)擊“使用外部數(shù)據(jù)源”。

（2）【選擇要放置數(shù)據(jù)透視表的位置】：如數(shù)據(jù)字段數(shù)較多且分析較復(fù)雜的情況下，一般選擇“新工作表”，會(huì)在新的“sheet”中生成透視表；數(shù)據(jù)字段數(shù)較少的情況下，可選擇“現(xiàn)有工作表”，在當(dāng)前“sheet”中所選區(qū)域生成透視表。

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

（1）行、列、值的應(yīng)用：數(shù)據(jù)維度方在行，自變量放在列（因變量為值）。

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

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

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

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

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

①總計(jì)的百分比：個(gè)體占總體的情況，每一項(xiàng)分類匯總的值占總計(jì)的百分比。如：“某日期某部門30秒電話量總和” 占“數(shù)據(jù)源中所有日期、所有部門30秒電話量總和”的百分比

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

2. 插入計(jì)算字段

需求的結(jié)果數(shù)據(jù)一般情況下都可使用“值”字段生成，因?yàn)椤爸怠弊侄沃械?strong>匯總方式包含了使用頻率較高的通用的計(jì)算功能，但有一定的局限，而計(jì)算字段極大擴(kuò)展了數(shù)據(jù)透視表的計(jì)算功能。

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

1. 選中透視表任意單元格區(qū)域，右擊

2. 選擇公式-計(jì)算字段

3. 輸入字段名稱，輸入公式：公式中的字段在“字段列表”選擇字段插入

3. 使用切片器

切片器功能同我們?nèi)粘Ｊ褂玫臄?shù)據(jù)報(bào)表（或產(chǎn)品在設(shè)計(jì)報(bào)表功能）時(shí)的篩選項(xiàng)是一樣的，如下圖所示，的數(shù)據(jù)報(bào)表中支持按日期篩選，2010版以上的excel版本的切片器功能也可以實(shí)現(xiàn)，方法如下。

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

4. 數(shù)據(jù)源刷新和更改

（1）數(shù)據(jù)源刷新

很多時(shí)候我們的數(shù)據(jù)源是不定期發(fā)生變化的，這就要求在數(shù)據(jù)透視表中也要體現(xiàn)出來，此時(shí)不需要重新創(chuàng)建一個(gè)新的數(shù)據(jù)透視表，刷新一下即可（原基礎(chǔ)上修改，不增加行列的話）。

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

1）手動(dòng)刷新數(shù)據(jù)透視表：在數(shù)據(jù)透視表中的任意單元格區(qū)域鼠標(biāo)右鍵，在彈出的快捷菜單中單擊【刷新】命令即可；或，在【數(shù)據(jù)透視表】工具選項(xiàng)卡中，單擊【刷新】/【全部刷新】按鈕。

2）打開文件時(shí)刷新數(shù)據(jù)透視表：在數(shù)據(jù)透視表中的任意單元格區(qū)域鼠標(biāo)右鍵，在彈出的快捷菜單中單擊【數(shù)據(jù)透視表選項(xiàng)】命令，在【數(shù)據(jù)】選項(xiàng)卡小紅，勾選“打開晚間時(shí)刷新數(shù)據(jù)”。

（2）數(shù)據(jù)源更改

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

1）選中數(shù)據(jù)透視表中的任意單元格區(qū)域，在【數(shù)據(jù)透視表】工具選項(xiàng)卡中，單擊【更改數(shù)據(jù)源】按鈕，更改數(shù)據(jù)源區(qū)域。

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

二、布局與格式

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

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

第一步：選中透視表任意單元格，【設(shè)計(jì)】選項(xiàng)卡-【報(bào)表布局】-“表格形式”且“重復(fù)項(xiàng)目標(biāo)簽”；

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

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

1. 布局

數(shù)據(jù)透視表共有三種布局形式，分別是壓縮形式、大綱形式、表格形式，各有不同的特點(diǎn)。如下圖所示：選中數(shù)據(jù)透視表中的任意單元格區(qū)域，【設(shè)計(jì)】選項(xiàng)卡，點(diǎn)擊【報(bào)表布局】更改布局形式。

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

2. 格式

1. 標(biāo)簽項(xiàng)重復(fù)顯示：如“一、布局”中所述，布局格式選擇中，可進(jìn)行標(biāo)簽是否重復(fù)的設(shè)置。

2. 顯示/隱藏分類匯總：選中透視表任意單元格區(qū)域，【右擊】，勾選/取消勾選【分類匯總】即可。

3. 合并行標(biāo)簽：選中透視表任意單元格區(qū)域，【右擊】-【數(shù)據(jù)透視表選項(xiàng)】-【布局和格式選項(xiàng)卡】-【合并且居中排列帶標(biāo)簽的單元格】（只對(duì)表格形式布局有效）

4. 插入空行間隔：【設(shè)計(jì)】選項(xiàng)卡- 【布局】-【空行】-【在每個(gè)項(xiàng)目后插入空行】

5. 取消字段前”+-“符：選中透視表任意單元格區(qū)域，【右擊】-【數(shù)據(jù)透視表選項(xiàng)】-【展開/折疊按鈕】

三、組合功能

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

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

1. 按時(shí)間組合

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

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

2. 按數(shù)值組合

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

3. 文本分組

如果是按地區(qū)統(tǒng)計(jì)或者個(gè)性化統(tǒng)計(jì)需求，可直接在透視表里面創(chuàng)建文本分組，在需要統(tǒng)計(jì)的列中，按住Ctrl鍵選擇要組合的單元格，然后點(diǎn)擊“鼠標(biāo)右鍵”選擇“創(chuàng)建組”即可，數(shù)據(jù)透視表就會(huì)按照我們所選定的內(nèi)容進(jìn)行組合，可以自行修改組的名稱，例如改為華北大區(qū)。

四、技巧與建議

1. 透視表技巧

（1）表頭格式：表頭只能有一行；字段不能為空（相同字段名會(huì)被自動(dòng)添加序號(hào)，進(jìn)行區(qū)別）。

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

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

（4）需對(duì)透視表數(shù)據(jù)再進(jìn)行函數(shù)計(jì)算的，可將透視表轉(zhuǎn)為普通表格：粘貼為值。

2. 3點(diǎn)學(xué)習(xí)建議（同上篇）

（1）工作中嘗試承接涉及數(shù)據(jù)分析相關(guān)需求，有目標(biāo)、具體場景的情況下學(xué)習(xí)速度會(huì)倍增。執(zhí)行過程中，會(huì)遇到各種各樣的問題，可通過快速百度查詢、請(qǐng)教數(shù)據(jù)分析的同事等方式解決。

（2）報(bào)名1門exel線上課程（價(jià)格￥150左右），歷時(shí)1個(gè)月。推薦起點(diǎn)學(xué)院的“21天Excel零基礎(chǔ)俗稱訓(xùn)練營”https://vip./course/detail/if5qd.html；

推薦理由：性價(jià)比高、實(shí)用性強(qiáng)，表現(xiàn)在：內(nèi)容好、有小節(jié)/章節(jié)作業(yè)檢驗(yàn)+有答疑、有班主任老師管理(時(shí)間節(jié)點(diǎn))+PK/獎(jiǎng)勵(lì)（上課期間還因?yàn)橥獬?周，錯(cuò)過了1個(gè)星期沒有結(jié)業(yè)）。

（3）輸出exel學(xué)習(xí)經(jīng)驗(yàn)并建立自己的知識(shí)速查表。學(xué)完不是自己的，只有消化吸收了才是自己的。對(duì)于技能學(xué)習(xí)只有孰能生巧一條路，學(xué)習(xí)完課程看似掌握了，但如果學(xué)完前期缺少實(shí)際場景的不斷應(yīng)用，很容易忘記。

所以學(xué)習(xí)完，建議輸出exel學(xué)習(xí)經(jīng)驗(yàn)，強(qiáng)化理解吸收；并形成自己的知識(shí)速查表，方便工作中使用快速查詢及不斷完善。

如上，有錯(cuò)誤之處歡迎大家指正/交流。