什么是數(shù)據(jù)透視表（Pivot Table）

它是一種可以快速匯總大量數(shù)據(jù)的交互式方法。這里得【匯總】可以指的是數(shù)據(jù)呈現(xiàn)的方式：求和、計(jì)數(shù)、平均值、最大值、最小值等等！學(xué)習(xí)透視表可以解決80%函數(shù)問題，俗話說但凡有能力的人都是有脾氣的，所以在使用數(shù)據(jù)透視表之前是要規(guī)范你的數(shù)據(jù)源：而且每個人都有自己的做表習(xí)慣，但不僅僅局限于以下這么多情況。

不規(guī)范數(shù)據(jù)源要求

一、如何創(chuàng)建數(shù)據(jù)透視表

很多朋友在學(xué)習(xí)數(shù)據(jù)透視表可能會搞不懂字段/標(biāo)簽、值區(qū)域、篩選頁……等等這些亂七八糟的名詞之間的關(guān)系。But沒有關(guān)系，下面我們來看一個excel表中的二維表，相信很多朋友制作這樣的報(bào)表，在數(shù)據(jù)透視表的經(jīng)典布局就將透視表各個區(qū)域顯示得十分清楚，并且和我們的二維表十分相似。

透視表為壓縮形式

• 1、那么這些字段是怎么分布到各個區(qū)域的？在字段列表下面為我們提供了一句重要的的話：在以下區(qū)域拖動字段。這就說明了只要將上面的字段拖如到各個區(qū)域就能達(dá)到我們想要的匯總。下面演示一下如何創(chuàng)建透視表？如果你對這些字段還不是很清楚，那么在插入透視表旁邊有一個推薦的表格，具體想實(shí)現(xiàn)一個什么樣的效果還需要根據(jù)自己的需求布局。

• 教程演示

看到?jīng)]，你是不是覺得創(chuàng)建很簡單？只要將需要將字段拖入各個區(qū)域或者點(diǎn)點(diǎn)鼠標(biāo)就可以，輕松將拖一拖！最簡單的理解就是甭管懂不懂，反正就是拖一拖，拖得多了就懂了！而且數(shù)據(jù)透視表的學(xué)習(xí)成本非常低，通過簡單的拖拽就能完成數(shù)據(jù)的分類匯總。

• 2、 透視表工具：分析和設(shè)計(jì)

更改布局樣式：設(shè)計(jì)選項(xiàng)卡下為我們了提供了布局是否可以顯示分類匯總、以表格的形式等顯示

在分析下選擇是否顯示 /-按鈕，列表字段以及標(biāo)題，設(shè)置透視表選項(xiàng)：

• 合并單元格、錯誤值/空值的顯示方式

• 經(jīng)典布局:（默認(rèn)為表格形式）調(diào)出等等

• 更新時(shí)候列寬是否調(diào)整

• 壓縮字符：最大支持127個（這個格式僅僅只是在報(bào)表以壓縮形式時(shí)候）

• 打印：跨頁打印透視表標(biāo)題

• 教程演示

二、 創(chuàng)建/更改動態(tài)數(shù)據(jù)源

數(shù)據(jù)透視表的一個優(yōu)勢就是：當(dāng)數(shù)據(jù)源變更后更新了就可反映到數(shù)據(jù)報(bào)表中。這種變化體現(xiàn)第一種如果只是數(shù)據(jù)源中的數(shù)值內(nèi)容發(fā)生變化了，點(diǎn)擊鼠標(biāo)右鍵刷新即可得到實(shí)時(shí)變化！另外一個設(shè)置就是在透視表選項(xiàng)中【打開文件時(shí)候刷新數(shù)據(jù)】

那么另外一種就是透視表區(qū)域范圍變化：

• 方式1：手動選擇區(qū)域，當(dāng)區(qū)域較大的時(shí)候使用快捷鍵CTRL A或者CTRL SHIFT 方向鍵

• 方式2：快捷鍵CTRL T創(chuàng)建超級表，創(chuàng)建成功后，點(diǎn)擊設(shè)計(jì)就會看到表格被重新命名為表n，那么數(shù)據(jù)透視表的范圍就要改成這個名字

這樣在增加數(shù)據(jù)的時(shí)候后刷新透視表就會自動更新了。

三、透視表的操作

• 選擇、移動、復(fù)制、刪除：有時(shí)候覺得透視表字段較多并不是我們想要得到的效果，在分析中選擇全部字段清除后再次重新布局！

四、透視表計(jì)算與值的匯總方式和顯示方式

1、 插入、刪除計(jì)算字段

對于大于二維表一般是無法匯總求和的，比如我想按照地區(qū)得到A產(chǎn)品3個月的總銷量，這時(shí)候計(jì)算字段極大擴(kuò)展了數(shù)據(jù)透視表的計(jì)算功能。當(dāng)然你也可以選擇其他計(jì)算平均值等或者還需要計(jì)算銷售員提成等等。在分析選項(xiàng)卡添加計(jì)算字段公式='1月' '2月' '3月' 或者=SUM('1月','2月', '3月' )。這里需要注意的是當(dāng)字段中有數(shù)字就可以輸入=1月 2月 3月，但是當(dāng)為我文本的時(shí)候，比如地區(qū)為列字段的時(shí)候總計(jì)可以直接輸入=東北 華北

計(jì)算平均值：

• 那么我們就要判斷東北地區(qū)有多少筆銷售額，大于0的才可以統(tǒng)計(jì)為1次！其實(shí)這里的公式就是相當(dāng)于if函數(shù)，透視表中也是支持函數(shù)計(jì)算的，相比于普通的表格中函數(shù)參數(shù)不能是單元格，區(qū)域，定義名稱！

• 平均值就是上一步的=合計(jì)/個數(shù)

2、 插入計(jì)算項(xiàng)

• 比如我想把前面3個分類為粗糧，再插入計(jì)算項(xiàng)目匯總求和！此時(shí)就會得到新的計(jì)算只匯總這3類！相信大家可以看到兩個界面的不同。

計(jì)算項(xiàng)的公式在編輯欄中是可以看到的，而且比如在華北地區(qū)的粗糧銷售額我不想?yún)R總小米的數(shù)量，此時(shí)將公式就可以在編輯欄中修改為=糙米 小米。但是計(jì)算字段是在編輯欄中是看不到公式的！

• 插入迷你圖

在分析選項(xiàng)卡下插入計(jì)算項(xiàng)，輸入名稱后添加不同類型的迷你圖。

字段和項(xiàng)的刪除：還是在分析下計(jì)算中從下拉菜單中找到改字段或者項(xiàng)刪除/編輯！細(xì)心的朋友有沒有看到計(jì)算字段和項(xiàng)的界面區(qū)別。但是使用此功能還是有局限性的，當(dāng)透視表中有組合功能的時(shí)候就會限制計(jì)算的。

3、更改值匯總依據(jù)

我們都知道【值】區(qū)域是數(shù)據(jù)透視表的核心部分，通過數(shù)據(jù)透視表提供的強(qiáng)大數(shù)據(jù)計(jì)算功能，可以使用多種匯總方式和值顯示方式來計(jì)算值字段數(shù)據(jù)。

值匯總依據(jù)，就是你要用數(shù)據(jù)透視表對原始數(shù)據(jù)中的數(shù)值進(jìn)行怎樣的計(jì)算，比如求和、計(jì)數(shù)還是求平均值。需要設(shè)置的時(shí)候右鍵調(diào)出或者雙擊字段列表修改匯總依據(jù)。

4、更改值顯示方式

數(shù)據(jù)透視表不僅可以按照不同的方式匯總數(shù)據(jù)，它還可以按照不同的方式顯示數(shù)據(jù)，從而更清晰的看出數(shù)據(jù)之間的關(guān)系和邏輯。

①總計(jì)的百分比

總計(jì)百分比含義是：這個是比較簡單的，每一項(xiàng)分類匯總的值占總計(jì)的百分比，比如這里的總計(jì)就是所有地區(qū)（包括華南/華北、西南、西北等）和所有類別的總銷售。透視表中只有一個100%。

②父級百分比

總計(jì)的百分比就是分析個體占整體的情況，比如求出每個類別的銷售額在各個地區(qū)的銷售占比，這個基本字銷售地區(qū)就是作為分母，各小組地區(qū)里百分比總計(jì)就是100%。以點(diǎn)心銷售為例子在北京的銷售為51765，華北地區(qū)所有城市的點(diǎn)心總銷售為480662，所以北京銷售額在華北地區(qū)的占比為10.77%，那么點(diǎn)心在所有城市銷售額總計(jì)百分比為100%。

③差異：就是以某一個值為基準(zhǔn)，兩個數(shù)值的差異（用于計(jì)算環(huán)比、同比差異）

差異百分比：用于計(jì)算環(huán)比增長率、同比增長率

④按某一字段的百分比：逐漸累加得到百分之百。

⑤升序/降序排序：按照匯總數(shù)量大小進(jìn)行數(shù)量排列，用于中國式排名

五、組合功能

數(shù)據(jù)透視表中有一個叫做'組合'的功能，一方面它能自動識別日期、數(shù)值等可計(jì)算的字段，然后按照給定的跨度區(qū)間進(jìn)行組合：

• 比如日期/時(shí)間組合：按年、季度、月、日，甚至小時(shí)、分……的匯總；

• 文本格式的數(shù)據(jù)按照自定義的方式進(jìn)行組合，比如選擇區(qū)域?qū)⑷藛T分組銷售1組、2組、3組

• 數(shù)字組合：比如我想查看不同單價(jià)區(qū)間的銷售額，選中列標(biāo)簽任意一個單價(jià)后點(diǎn)擊右鍵，選中組合，設(shè)置單價(jià)的起始數(shù)據(jù)，步長就是以50為增長

六、透視表的排序和篩選

• 排序：可以自定義序列排序、按照字符筆畫排序、手工拖動字段

比如當(dāng)我們創(chuàng)建月份為行列標(biāo)簽的時(shí)候，excel中默認(rèn)的排序是10月、11月、12月是在前面的，在排序的時(shí)候你可以手工拖下來

或者設(shè)置Excel選項(xiàng)——高級——常規(guī)——自定義序列——導(dǎo)入或者手動輸入——確定——在透視表表分析選項(xiàng)下設(shè)置排序以自定義序列

• 篩選：可以分為值和數(shù)值的篩選，從字面上都是很好理解的！如果你需要更快的篩選，還是使用切片器

比如數(shù)值的前10項(xiàng)：就是篩選處前10名的最大值，這和excel中的條件格式篩選是一致的

七、使用切片器與透視圖：讓你的圖表動起來

• 怎么使用？ 需要定位透視表后點(diǎn)擊分析插入多個切片器

• 功能是什么？ 在excel中的高級篩選器，通過使用多個切片器設(shè)置報(bào)表鏈接達(dá)到多級聯(lián)動，比如想要查看任意地區(qū)銷售員的銷售額

• 美化：切片器設(shè)置樣式和排序規(guī)則

最后透視表和透視圖能干什么？

1、制作動態(tài)銷售看板

2、制作動態(tài)日歷

3、整理打卡記錄

4、簡單的庫存統(tǒng)計(jì)

5、賬齡分析

6、家庭收入支出分析

7、制作生產(chǎn)看板

總結(jié)：如果你在日常工作處理過程中不想使用復(fù)雜的vba代碼，或者函數(shù)公式學(xué)習(xí)有難度，那么數(shù)據(jù)透視表是你必備的，而且只是拖一拖鼠標(biāo)的事情就可以定時(shí)更新的數(shù)據(jù)報(bào)表實(shí)現(xiàn)動態(tài)交互性。然而本文只是涉及透視表的一些初級知識，如果可以更加深入的學(xué)習(xí)數(shù)據(jù)透視表才能從多方位描述數(shù)據(jù)，讓看似雜亂無章的數(shù)據(jù)也變得高大上！