Excel數據透視表的兩個小技巧:計算排名和計算堆積百分比

今天介紹兩個關於數據透視表的小技巧：如何計算排名以及如何計算堆積百分比。這兩個技巧在我所有的課上都沒講過，一是因為簡單，二是因為沒時間。今天就補上這一課。

1. 排名的問題

在Excel的數據處理和分析時，關於排名有很好的解決辦法。首先，Excel有排名函數，其次，對於複雜的排名需求，可以設計各種公式來滿足。我們以前有相應的介紹，感興趣的同學可以看以前的教程和文章：

Excel中的排名問題（視頻教程）

如何對數據進行排名？如何進行唯一排名？如何進行中國式排名？

我們今天介紹的問題與那些場景都不一樣：

上圖是我們的源數據，這裡記錄了產品銷售的明細數據。我們的需求是在下面的透視表中計算每個產品的排名：

這個需求基本不可能通過在源數據上加列的方式來解決（實際上可以，但是會用到非常複雜的公式，在透視表中還需要做特殊設置），所以，很多同學就會在透視表後面寫公式來代替：

這麼做可以得到結果，不過有兩個問題：

 需要透視表結構步改變。否則就會覆蓋掉公式或者公式計算出錯誤結果。

如果源數據發生了變化，比如增加了某個產品或者減少了某個產品，這個公式計算的結果也會錯誤。

實際上，我們可以在透視表中很簡單的解決這個問題。

首先，我們在透視表中，再次將數量添加到值欄位中：

在透視表區域的「求和項:數量2」上點擊滑鼠右鍵，點擊「值顯示方式」：

選擇「升序排列」後：

在這個對話框中將基本欄位選擇為「產品」（意思是將產品根據合計數量進行排名），得到如下透視表：

排名已經完成。如果是希望倒序排名，可以選擇「降序排列」

實際上很簡單，對吧？主要是大部分朋友不了解在這裡有這個選項。另外，這個菜單項其實有點困惑，反正我剛看到這兩個菜單項時就沒反應過來是幹這個使用的。

2. 堆積合計和堆積百分比

我們做圖表時，有堆積柱形圖，也有堆積條形圖，還有堆積百分比柱形圖和條形圖。很多時候在數據表中，我們也希望有這種堆積合計和堆積百分比：

在上圖中，我們不光匯總了每月了合計，而且展示了到每個月的進展。例如，在5月的數據中，我們看到了前5個月的合計數量，以及前5個月佔全年的百分比。這類分析報表在展示預算或任務完成數據的內部結構（每個月的進展或每個客戶的情況）時經常用到。

同樣，我們沒有辦法在源數據上做些什麼事情，然後通過數據透視直接得到這個結果。那麼，是否有不使用透視表上寫公式的方法呢？

答案還是很簡單 ，使用透視表可以直接完成。

首先，我們做出如下透視表（日期進行組合即可）：

然後將數量重複添加到值欄位中兩次：

在透視表區域的「求和項:數量2」列的任意單元格上點擊滑鼠右鍵，點擊「值顯示方式」，點擊「按某一欄位匯總」：

在接下來的對話框中，將基本欄位選擇為「日期」：

可以看到，透視表的這個欄位已經變成了累計的合計值：

在「求和項:數量3」列的任意單元格是點擊滑鼠右鍵，選擇「值顯示方式」，點擊「按某一欄位匯總的百分比」：

在接下來的對話框中，將基本欄位選擇為「日期」：

得到透視表如下：

大功告成！

這兩個技巧的難點就在於「不知道」，一旦知道了，使用非常簡單。希望這些技巧能夠幫助你在工作中大顯身手！