欧美日韩亚洲国产欧美电影,亚洲狠狠婷婷综合久久久久

搜索

APP

起點課堂會員權(quán)益

職業(yè)體系課特權(quán)

線下行業(yè)大會特權(quán)

個人IP打造特權(quán)

30+門專項技能課

1300+專題課程

12場職場軟技能直播

12場求職輔導(dǎo)直播

12場專業(yè)技能直播

會員專屬社群

榮耀標識

發(fā)布

注冊 | 登錄

Excel數(shù)據(jù)透視表篇：4個方面出發(fā)，解決80%職場需求

團團

2019-12-27

2 評論 27682 瀏覽 87 收藏

21 分鐘

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

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

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

一、基本操作

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

2. 插入計算字段

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

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

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

3. 使用切片器

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

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

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

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

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

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

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

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

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

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

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

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

二、布局與格式

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

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

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

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

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

1. 布局

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

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

2. 格式

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

三、組合功能

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

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

1. 按時間組合

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

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

2. 按數(shù)值組合

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

3. 文本分組

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