（友情提醒，多圖，請在wifi下觀看！流量壕請無視本條）
有些朋友后臺給小奚留言說很喜歡之前講的透視表內(nèi)容，問能不能整理出一個Excel透視表的合集，只要是透視表的內(nèi)容都能在里面找，今天小奚就給大家?guī)砹诉@篇文章。
不用寫公式，不用手工計算，數(shù)據(jù)透視表通過簡單的拖拽就能完成各個維度你想要的數(shù)據(jù)分類匯總，可以說是基礎(chǔ)的Excel操作里面最簡單易上手，最實(shí)用，最常用的功能了。
話不多說，上目錄。本文內(nèi)容較長，干貨較多，大家可以根據(jù)目錄挑選自己想看的內(nèi)容。
我是目錄，目錄是我
上篇：基礎(chǔ)操作
01 創(chuàng)建數(shù)據(jù)透視表
【問】：老板給你公司今年的訂單明細(xì)，讓你告訴他每個銷售今年的銷售額是多少？
看見了嗎？
創(chuàng)建透視表就是如此簡單，通過拖拽，就能立馬得到老板想要的結(jié)果。
有很多初次接觸數(shù)據(jù)透視表的同學(xué)對透視表的四個字段（篩選器、行、列、值）的意思不太了解，小奚用一張圖告訴大家：
字段拖放在不同的區(qū)域，就會以不同的顯示方式顯示匯總的結(jié)果，并且同一個區(qū)域內(nèi)的順序不同，在數(shù)據(jù)透視表內(nèi)匯總的先后層次也會不同。這也就是為什么透視表能完成各個維度的數(shù)據(jù)匯總。
02 ?透視表轉(zhuǎn)普通表格
【問】：小奚啊， 老板只需要最終的匯總結(jié)果，不需要把數(shù)據(jù)明細(xì)發(fā)給他，那么怎么把透視表轉(zhuǎn)成普通表格發(fā)給老板呢？
這個很簡單，只需要復(fù)制數(shù)據(jù)透視表，粘貼成值就行了，如果喜歡透視表的格式還可以像小奚一樣粘貼一下格式。
03 ?更改值字段計算方式
【問】：老板說我不僅想看每個銷售這一年銷售的總金額，還想看他們這一年簽了多少筆合同。
在前面我們用到的是求和的計算方式，對于這個問題，我們可能就需要用到計數(shù)的計算方式了。
訂單編號是文本類型，我們可以看到將訂單編號拖到值字段時Excel會自動計算，因?yàn)槲谋绢愋筒荒芮蠛?，而金額是數(shù)值，可以求和，也可以計數(shù)。
除此之外，值字段的計算方式還有以平均值，最大值，最小值等計算，但是在我們平時的運(yùn)用中，最常用到的還是計數(shù)和求和這兩種。
04 ?插入計算字段
【問】：老板說，我還想知道每個銷售以現(xiàn)在的年銷售額他們的提成能拿多少（按千分之七來算）？
這里我們有一種偷懶的做法，直接在透視表外面乘以0.007，對于這種方法會出現(xiàn)兩個問題：
第一，有的同學(xué)會發(fā)現(xiàn)寫完公式下拉后，數(shù)字并不會改變，仍然是第一個的計算結(jié)果，對于這種情況可以如此操作：【文件】-【選項(xiàng)】-【公式】-取消勾選【使用GetPivotData函數(shù)獲取數(shù)據(jù)透視表引用】，如下圖所示。
但即使是解決了第一個問題，也會發(fā)現(xiàn)一旦改變透視表的結(jié)構(gòu)，我們的計算結(jié)果就不能使用了，因?yàn)樗谕敢暠硗饷娌粫S著透視表的改變而改變。
所以，其實(shí)最好的方法是在Excel里面插入計算字段。
05 ?透視表排序與篩選
【問】：老板說，把每個銷售以銷售額降序排列，同時，給我篩選出總銷售額排名前10的銷售。（老板，您的需求不能一次說完嗎？）
降序排列其實(shí)比較好辦，只需要選中金額所在的地方，右鍵選擇降序排列即可。
如果存在多個字段的情況下，怎么篩選出老板想要的呢？直接使用行標(biāo)簽去篩選是行不通的，解決方法是：將鼠標(biāo)點(diǎn)在列標(biāo)簽外面一格，然后使用篩選功能，具體操作請看下圖。
06 ?行列百分比匯總
【問】：前面說的都是數(shù)值，但是我想看百分比怎么辦？
想要值以百分比的形式顯示可以：【右鍵】-【值顯示方式】-可選擇相應(yīng)的百分比選項(xiàng)。
數(shù)據(jù)透視表有多種數(shù)據(jù)百分比的顯示方式，下面我們只挑選最常用的：總計百分比、行/列匯總百分比和父級匯總百分來看。
總計百分比
當(dāng)我們分析各個項(xiàng)目占總值的百分比時，就可以用總計百分比。例如：老板想看銷售1部在服裝這個產(chǎn)品占整個公司的銷售額的多少。
行/列匯總百分比
當(dāng)我們想看某個數(shù)據(jù)在行字段或者列字段的垂直維度上的占比時，就需要用到行/列匯總百分比。
列匯總百分比，例如：老板想看每個銷售在單個產(chǎn)品上的占比是多少，我們就需要拉列匯總百分比給他看。
行匯總百分比，例如：老板想知道單個銷售在每個產(chǎn)品的上售賣金額占比是多少，我們就需拉行匯總百分比給他看。
父級匯總百分比
當(dāng)我們不想看某個數(shù)據(jù)占全部的占比，只是想看它在細(xì)分維度的占比的時候就需要用到父級匯總占比。
例如：老板說我想知道王麻子的銷售額在他們團(tuán)隊(duì)占比是多少。
07 ?修改行列字段順序
【問】：小奚，透視表自己出來的行列字段的順序有的時候并不是我們想要的順序，是不是只能【右鍵】-【移動】-【上移/下移/移至開頭或結(jié)尾】？
其實(shí)小奚曾經(jīng)也是這么干的，當(dāng)時字段還特別多，小奚拿著鼠標(biāo)點(diǎn)右鍵，上移下移了無數(shù)次，差點(diǎn)沒崩潰。
這時領(lǐng)導(dǎo)從小奚身后飄過，實(shí)在看不下去了，說了一句：“我都是直接拖就可以了?！贝丝绦∞傻膬?nèi)心戲是：“領(lǐng)導(dǎo)，我錯了，是我學(xué)藝不精，你就當(dāng)我是在練手指的靈活度吧！”
恩，忍住笑，嚴(yán)肅臉。下面來看領(lǐng)導(dǎo)是怎么拖的，上動圖（前部分為上下移動的操作方式，后部分為直接拖動的操作方式）。
丟了這么大個人，不扳回一城不是小奚的風(fēng)格啊，所以小奚又潛心學(xué)習(xí)，一個關(guān)于修改字段的更高階技能出現(xiàn)啦！
這個技能對于反復(fù)使用的字段比較方便，平時若用得少，直接拖動會更方便一些。
【先做一個輔助表】-【文件】-【選項(xiàng)】-【高級】-【常規(guī)】-【編輯自定義列表】-【選擇最開始建立的輔助表】-以后就只需要排序就能按我們希望的字段順序出現(xiàn)了。
08 ?刷新與更改數(shù)據(jù)源
刷新
【問】：如果我想修改源表的數(shù)據(jù)，透視表會自動更新嗎？
默認(rèn)是不會自動更新的，需要手動刷新，如果是在原基礎(chǔ)上修改，不增加行列的話，我們只需要刷新就可以了，如果有多個數(shù)據(jù)透視表可以選擇全部刷新。如果害怕自己忘記刷新，也可以設(shè)置【打開文件時刷新數(shù)據(jù)】
更改數(shù)據(jù)源
【問】：我的數(shù)據(jù)源表修改了，刷新了也沒有出現(xiàn)我修改的東西怎么辦？
這種情況，就是我上面說的增加了行或者列啦，只是刷新是不行的，還需要更改數(shù)據(jù)源。
更高級的用法
有的時候我們并不想每次都去修改數(shù)據(jù)源，那太過于麻煩。又怎么辦呢？
把數(shù)據(jù)源把設(shè)置成“表格”就可以解決這個問題。
只要把數(shù)據(jù)源表設(shè)置成“表格”，不管增加行還是列都不需要再去更改數(shù)據(jù)源，只需要刷新即可（注意，只針對將數(shù)據(jù)源更改為“表格”之后建立的透視表有效，這也是為什么在動圖的例子里小奚要重新建透視表的原因）。
并且通過動圖可以看到，設(shè)置成“表格”后，如果在首行輸入公式都不需要下拉，會自動匹配。
09 ?透視表的復(fù)制與刪除
【問】：我想復(fù)制或者刪除透視表怎么辦？刪除必須要把整個sheet刪除掉嗎？
復(fù)制透視表的情況其實(shí)蠻常見的，因?yàn)橛械臅r候選取的數(shù)據(jù)源是相同的，需要做不同維度的匯總分類，如果不想重新新建sheet，那么復(fù)制透視表后在這基礎(chǔ)上更改字段是最好的方法。
只需要全選透視表，復(fù)制粘貼即可。
刪除透視表只需要全選透視表，直接按detele鍵就能全部刪除。
中篇：美觀與布局
10? 透視表的三種布局
【問】：做為一個有追求的員工，只會Excel透視表默認(rèn)的呆板展示方式簡直不能忍，好嗎？小奚呀，為什么別人的透視表和自己的就是長得不一樣呢？
那我們就要從透視表的三類展示姿勢開始說起了，這三類布局分別是：壓縮形式、大綱形式、表格形式。在哪里找到這三類布局呢？
【設(shè)計】選項(xiàng)卡-【布局】菜單欄-【報表布局】（注意喲，給透視表穿上美麗外衣的大多數(shù)功能都是在【設(shè)計】選項(xiàng)卡實(shí)現(xiàn)，這里也是我們今天的主要陣地，大家可以自己研究研究這個選項(xiàng)卡的內(nèi)容噢!）
壓縮形式
有眼尖的同學(xué)已經(jīng)發(fā)現(xiàn)啦，其實(shí)壓縮形式就是我們Excel默認(rèn)的透視表格式，它主要的特點(diǎn)呢就是：
無論疊加多少個行字段，都只占一列。如果對這個概念還不是特別明白，可以多和下面講到的兩個布局方式做對比，相信你很快就能明白啦。
大綱形式
大綱形式與壓縮形式最重要的區(qū)別就是：大綱形式有幾個行字段就會占幾列，即行字段會并排顯示，就如我們例子中的行字段有三個，那么大綱形式的布局就會占三列而不像壓縮形式只占一列。
另外，大綱形式的分項(xiàng)匯總顯示在每項(xiàng)的上方。
表格形式
表格形式的透視表是小奚最常用的一種形式。它的主要特點(diǎn)呢是：
1、與大綱形式一樣，行字段會并排顯示，有幾個行字段會占幾列；
2、與大綱形式不同的是，表格形式是有表格的（好像在說廢話，記得看圖找區(qū)別噢）；
3、與大綱形式第二個不同是表格形式的分項(xiàng)匯總是在每項(xiàng)的下方，而大綱形式是在上方。
以上的三種布局形式的特點(diǎn)，你都了解了嗎？
11? 顯示和隱藏分類匯總
【問】：小奚啊，你的例子里面，我只想看每個銷售細(xì)分到各省份客戶的銷售額，并不想看銷售匯總的銷售額，并且那些銷售匯總放在里面看得我眼花，怎么解決呢？
嗯，確實(shí)是這樣，有的時候我們并不需要看分類匯總，但是透視表會自動顯示，有的時候我們甚至都不需要看總計，那么怎么隱藏和顯示分類匯總和總計呢？還是在【設(shè)計】選項(xiàng)中哦！
看完動圖大家應(yīng)該比較清楚在哪里顯示和隱藏分類匯總和總計了吧？
不過細(xì)心的同學(xué)應(yīng)該已經(jīng)發(fā)現(xiàn)動圖里一個小問題了吧？在這里小奚要考一考大家喲！
【提問】：為什么小奚選擇的是在組的頂部顯示分類匯總，最后Excel還是在組的底部顯示的分類匯總呢？要結(jié)合我們前面講的內(nèi)容哦，知道答案的同學(xué)請大聲在留言區(qū)告訴我吧！
12? 標(biāo)簽項(xiàng)重復(fù)顯示
【問】：對于大綱形式布局和表格形式布局，因?yàn)樾凶侄问遣⑴棚@示，特別是前面的行字段，常常一個就對應(yīng)了后面多個字段，讓表格不好看，怎么優(yōu)化呢？
哇！這個問題好，在這里小奚要告訴大家兩個優(yōu)化的方法：第一個是讓我們的標(biāo)簽項(xiàng)重復(fù)顯示，第二個是合并行標(biāo)簽。
先講讓標(biāo)簽項(xiàng)重復(fù)顯示：【設(shè)計】選項(xiàng)卡- 【布局】-【報表布局】-【重復(fù)所有項(xiàng)目標(biāo)簽】。（注意，標(biāo)簽重復(fù)項(xiàng)只對大綱式和表格式有效，對壓縮式無效，想想為什么？）
13? 合并行標(biāo)簽
合并行標(biāo)簽也是回答上面的提問，這是另外一個優(yōu)化行字段并排顯示的方式。
當(dāng)然，大多數(shù)的同學(xué)可能會對合并行標(biāo)簽更感興趣一點(diǎn)，因?yàn)樾∞砂l(fā)現(xiàn)，大多數(shù)的同學(xué)在操作Excel的時候，對合并單元格尤為熱衷。（雖然合并單元格一直被稱為Excel處理數(shù)據(jù)時的一大殺手）
好了，廢話不多說，直接上操作：【右鍵】-【數(shù)據(jù)透視表選項(xiàng)】-【布局和格式選項(xiàng)卡】-【合并且居中排列帶標(biāo)簽的單元格】（注意：合并行標(biāo)簽只對表格形式布局有效，對大綱式和壓縮式無效，不信你可以試試喲！）
14? 插入空行間隔
【問】：小奚啊，我們公司的透視表數(shù)據(jù)很多，老板說看得他密集恐懼癥都要犯了，該怎么辦呀？（同學(xué)，你是認(rèn)真的嗎？）
我們在每一項(xiàng)之間插入空行，對于透視表數(shù)據(jù)很多的情況可能會有所幫助。
具體操作：【設(shè)計】選項(xiàng)卡- 【布局】-【空行】-【在每個項(xiàng)目后插入空行】
對于插入空行，在展示的時候會比較好，因?yàn)榭雌饋碜屓瞬荒敲蠢?。但是在日常處理?shù)據(jù)的時候，空行會帶來一些麻煩，所以處理數(shù)據(jù)時還是建議大家不插入空行喲。
15? 取消字段前+/-符
【問】：透視表行標(biāo)簽前面總是有+/-符號，看起來有些丑，可以隱藏嗎？
當(dāng)然可以呀，上操作：【分析】選項(xiàng)卡-【顯示】-【+/-按鈕】
16? 刷新后格式保持不變
【問】：前兩天一個同事一臉悲痛地來找我，說透視表的排版布局我都做好了要給領(lǐng)導(dǎo)看，行高和列寬都需要固定，不能變，但是每次我一刷新透視表列寬和格式就全變了，要崩潰了。
不知道工作中你是否也遇到了這樣的情況呢？不要心急，一招就能幫你搞定：
【右鍵】-【數(shù)據(jù)透視表選項(xiàng)】-【布局和格式選項(xiàng)卡】-取消勾選【更新時自動調(diào)整列寬】
17? 透視表模板套用
【問】：透視表模板套用是不是讓透視表變美觀的東西呀？
真聰明，在Excel里面已經(jīng)內(nèi)置了一些透視表的模板，我們可以選擇自己喜歡的模板，直接套用就行了，還是在【設(shè)計】選項(xiàng)卡里面。
最后的最后，關(guān)于美觀與布局，還可以給透視表更改好看的字體和字號，例如微軟雅黑等，再結(jié)合以上的內(nèi)容，立馬讓你的透視表秒殺同事的呆板透視表。
下篇一：分組
18 文本分組
【問】：小奚，老板除了看一個省的銷售情況，還希望看一個區(qū)域的情況，比如：想看西區(qū)的銷售情況（西區(qū)包含：貴州、湖北、陜西、四川、云南、重慶），該怎么辦呢？
對于這種情況，其實(shí)有兩種方式，一是在數(shù)據(jù)源表里面去添加區(qū)域的輔助列，二是不添加輔助列，直接在透視表里面創(chuàng)建文本分組啦。
按住Ctrl鍵選擇貴州、湖北、陜西、四川、云南、重慶，然后點(diǎn)擊“鼠標(biāo)右鍵”選擇“創(chuàng)建組”數(shù)據(jù)透視表按照我們所選定的內(nèi)容進(jìn)行組合。接著可以修改組的名稱，例如改為西區(qū)。
19 日期分組
【問】：既然可以Ctrl鍵選中想要分組的內(nèi)容來分組，那么如果對日期來分組是不是也可以這樣呢？
嚴(yán)肅地講是可以這樣的，以前領(lǐng)導(dǎo)讓小奚拉一個月的數(shù)據(jù)，小奚選中了這個月的每一天然后創(chuàng)建了分組，但這樣做有點(diǎn)傻，因?yàn)镋xcel是很智能的，能夠?qū)θ掌谶M(jìn)行識別，對于日期的分組，我們有個簡便的方法。
選中日期，右鍵選擇“創(chuàng)建組”，就可以按照我們的需求進(jìn)行月、季度、年的匯總啦！
怎么樣？Excel還是很智能吧？
20 數(shù)值分組
【問】：1、小奚，我是一個HR，要對員工進(jìn)行工齡匯總，5年為一個工齡段，有什么簡單的方法嗎？
2、小奚，老板需要對銷售的金額段分析，該怎么分金額段呢？
那就需要用到透視表的數(shù)值分組啦，依然是選中司齡或者金額，右鍵選擇“創(chuàng)建組”，在組合中可自己設(shè)置起始和結(jié)尾以及步長，如有不清楚，請看動圖吧。
司齡分組：
金額段分組：
大家發(fā)現(xiàn)了嗎？透視表只能按照等距步長來組合喲！比如司齡的步長設(shè)置為5，那么只能按照5的間隔來組合。
但是在實(shí)際運(yùn)用中，特別是對于金額段的實(shí)際運(yùn)用，我們常常需要的是不等距組合，比如：1000-2000金額段，2000-5000金額段。對于不等距的組合我們該怎么操作呢？
不等距組合有兩種操作方式：
一是按照18分本分組講的Ctrl選中做組合的方式創(chuàng)建不等距組合，這種方式對于數(shù)據(jù)量小的時候，還能操作，對于數(shù)據(jù)量大的時候其實(shí)是蠻絕望的。
第二種方法是需要在源表建立輔助列，然后用VLOOKUP的模糊匹配，這個我們會在下一次講到。
下篇二：數(shù)據(jù)表出錯原因
常常有人來問我為什么我的透視表出問題了呢？其實(shí)透視表出錯的原因絕大多數(shù)都是因?yàn)樵蹅兊?b>數(shù)據(jù)源表出了問題。
21? 某些列沒有標(biāo)題
【問】：我無法創(chuàng)建數(shù)據(jù)透視表，顯示透視表字段名無效是什么意思啊？
這種情況是因?yàn)槲覀兊臄?shù)據(jù)源表某些列沒有標(biāo)題，這種情況常見于前一列的標(biāo)題過長，覆蓋了沒有標(biāo)題的那一個讓我們誤以為都是有標(biāo)題的，具體請看動圖。
操作：需要我們把數(shù)據(jù)源表的標(biāo)題行補(bǔ)全就可以正常創(chuàng)建透視表啦。
22 存在合并單元格
【問】：我的數(shù)據(jù)源表中明明沒有空白，但是在透視表中卻出現(xiàn)了空白是怎么回事呢？
這可能是數(shù)據(jù)源表中存在合并單元格，Excel會將除了合并單元格的第一格以外的全部當(dāng)做空值處理。
操作：取消合并單元格，并填充完整，再刷新數(shù)據(jù)透視表就可以看見沒有空白啦。
23 日期非法
【問】：為什么我不能對透視表的日期進(jìn)行月、季度、年的分組呢？
檢查一下源表，看看是否存在非法日期？例如：20170101、2017.1.1等。
操作：回到數(shù)據(jù)源表，選中日期列-分列-選擇日期-完成。就可以看到日期被變成了標(biāo)準(zhǔn)格式，再去透視表刷新，就可以按日期創(chuàng)建組啦！
24? 數(shù)據(jù)源存在文本類型的數(shù)字
【問】：小奚，為什么我的源表里面明明是數(shù)字，但是透視表里只能計數(shù)，求和的結(jié)果總是顯示0呢？
同樣的我們需要去檢查一下數(shù)據(jù)源表，看看是不是用文本形式保存的數(shù)字呢？
操作：將以文本形式儲存的數(shù)字按數(shù)值類型儲存，方法是：選中-分列-完成，刷新透視表即可。
那我們來總結(jié)一下，在創(chuàng)建透視表的時候出問題絕大多數(shù)情況下需要我們?nèi)z查源表，看看是否：某些列沒有標(biāo)題、存在合并單元格、非法日期以及文本類型存儲的數(shù)字。
學(xué)完本文，幾乎就能解決Excel透視表絕大多數(shù)的問題啦！
還在看什么？趕緊動手操作吧！
如果你覺得本文對你有所幫助，請盡情收藏或轉(zhuǎn)發(fā)吧！歡迎轉(zhuǎn)載，另外，若想要本文案例數(shù)據(jù)源歡迎簡信撩我。
END
