我們，讓Excel變簡單

這是一個在很長時間内困擾我的問題。因為這個問題，導緻我在很多項目中不得不采取我不願意使用的粗暴方法。

假設我們有數據如下圖：

數據存放在超級表（Table）BugetTrace中，記錄了各部門不同客戶的計劃和實際數據。

我們希望做出如下的結果報表：

在這個報表中，我們統計各部門各項目的完成率（實際/計劃）

當然我們可以寫公式完成。但是根據“Excel工作的标準模式”，在制作報表時，公式永遠不是第一選擇。我們希望用數據透視表來完成，以便實現報表工作的自動化。

很簡單就可以完成下面的數據透視表：

因為源數據中沒有完成率這個數據，所以沒有辦法使用透視表直接得到這個結果，需要在透視表中進行計算。

由于我們的表格是将計劃和實際作為兩行存放在數據表中的一列中的，所以應該使用“計算項”進行計算：

選中透視表區域的列字段中的任意單元格，

在“數據透視表工具-分析”選項卡看中，點擊“字段、項目和集”，點擊“計算項”：

在對話框中，将名稱修改為“完成率”，在公式中輸入：=實際/計劃，點擊“添加”，

點擊确定後，透視表中添加了計算項：

乍一看上去挺對的，但是仔細看就會發現結果有問題：

以市場部為例，科目1和科目2的完成率是正确的，但是到市場部彙總行的完成率就是錯誤的，因為市場部總的完成率是通過将科目1和科目2的完成率加在一起實現的。

這顯然與我們理解的完成率不一樣。用數據透視表是做不出我們期望的結果的。

這個問題可以使用Power Pivot來輕松完成。

選中超級表（BudgetTrace）區域的任意單元格，在“Power Pivot”選項卡中點擊“添加到數據模型”：

在彈出的“Power Pivot for Excel”中，點擊“主頁”選項卡下的“數據透視表”：

彈出對話框：

點擊确定後，添加透視表。

在Power Pivot選項卡中，點擊度量值，點擊新建度量值：

在對話框中，将度量值名稱修改為“計劃”，公式中輸入：

=CALCULATE(Sum('表1'[金額]),'表1'[項目]="計劃")

點擊确定，在透視表字段列表中出現這個度量值：

同樣的步驟，創建一個新度量值“實際”：使用公式：

=CALCULATE(Sum('表1'[金額]),'表1'[項目]="實際")

再創建一個度量值“完成率”，使用公式：

=CALCULATE(SUM('表1'[金額]),'表1'[項目]="實際")/CALCULATE(SUM('表1'[金額]),'表1'[項目]="計劃")

将透視表字段按下列方式布局，創建透視表：

得到透視表：

為部門添加分類彙總，并且修改完成率的數字格式為百分比：

大功告成！

總結

我在各種場合下都說過，創建報表最好的工具是數據透視表，因為它可以讓我們的工作自動化，從而極大的提高工作效率。不要擔心做不出那些報表，基本上超過95%的報表需求都可以用透視表實現。但是，總是有一些報表不能用普通的數據透視表實現，這個問題也困擾了我很久，直到我發現了Power Pivot這個工具。Power Pivot絕對是一個數據分析的利器，希望大家都能夠掌握并使用這個工具。

END

更多精彩资讯请关注tft每日頭條，我们将持续为您更新最新资讯!