Power Pivot量值公式:樞紐分析表建立KPI關鍵效能指標

Power Pivot資料表可以跟Excel一樣利用函數新增資料行,在此以傳票明細帳為例介紹如何操作,並且進而建立量值公式,在樞紐分析表呈現費用的KPI關鍵效能指標。

一、Power Pivot資料行

延續先前贊贊小屋關於Power Pivot的文章,在Power Pivot編輯器可以直接在資料表加入資料行,並且和Excel一樣是引用報表其他欄位的資料進行計算,例如這裡的傳票「借方金額」減掉「貸方金額」,得到「交易金額」。

Power Pivot量值公式:樞紐分析表建立KPI關鍵效能指標 1

二、Power Pivot函數

Power Pivo可以使用函數,大致上是在繼承Excel函數庫的基礎上進行開發擴展,因此很多函數和Excel一模一樣,例如這裡看到的「SUM」加總函數。注意到公式是寫在Power Pivo下方如同Excel空白工作表般的區域,不過雖然是一個一個的儲存格,但它沒有第幾欄第幾列的機制,在每一個空白儲存格都可以輸入公式,這個便是 Excel Power Pivo的量值,後來發展到為Power BI的DAX函數。

另外注意到函數公式是以資料表中的資料行作為計算對象,也就是報表中的欄位資料,這個Excel表格公式是相同模式,因此如果熟悉Excel表格的結構化參照,應該很容易上手量值公式的設計。

一張含有 桌 的圖片

自動產生的描述

三、建立KPI工具

建立好了量值之後,在上方功能區的「主資料夾>計算」這裡選擇「建立KPI」,可以參考它的文字說明:「將所選取的量值轉換成關鍵效能指標(KPI)」。

Power Pivot量值公式:樞紐分析表建立KPI關鍵效能指標 4

四、定義目標絕對值

在跳出來的「關鍵效能指標(KPI)」視窗中,如何「定義目標值」選擇以「絕對值」的方式設定,在「定義狀態臨界值」中設定為「10000」和「50000」。

Power Pivot量值公式:樞紐分析表建立KPI關鍵效能指標 6

五、設定圖示樣式

由於文章範例是費用的傳票明細帳,而費用是越少越好,因此在「定義狀態臨界值」的右下角選擇左邊是綠色、右邊是紅色的狀態,進一步還可以適當設定「選取圖示樣式」。

Power Pivot量值公式:樞紐分析表建立KPI關鍵效能指標 8

六、樞紐分析表更新

從Power Pivo編輯器回到Excel,將先前已經建立好的樞紐分析表「重新整理」。另外注意到其實在Excel也可以直接建立「KPI」就在Excel上方功能區「Power Pivot」頁籤的「計算」工具箱中。

Power Pivot量值公式:樞紐分析表建立KPI關鍵效能指標 10

七、KPI分析報表

樞紐分析表更新之後,欄位會多出本文第二步驟所建立的量值:「總交易金額」並且因為已經建立了KPI,所以可以看到有「目標」及「狀態」欄位,將其中的「狀態」欄拉到值區域,然後再適當調整樞紐分析表欄位配置,變成是依照科目別各個部門的月份金額統計,紅色是超標,超過5萬元,綠色是安全,小於1萬元,處於中間值的部門則是黃色。

Power Pivot量值公式:樞紐分析表建立KPI關鍵效能指標 12

Power Pivot量值及KPI

這篇文章主要介紹Excel Power Pivot的量值及KPI,建立好量值公式再使用 KPI工具,然後在樞紐分析表相對應欄位配置關鍵效能指標。熟悉Excel讀者應該知道即使沒有Power Pivo的KPI工具,善用指令函數也能手工建立類似的圖表效果。這裡比較關鍵的技術是量值公式,微軟後來進一步將它設計成Power BI強大的DAX函數。如果讀者在工作上常常要進行大量複雜的資料計算分析,建議可以學習掌握 Power BI DAX函數,贊贊小屋的Power BI大全集課程中便有一篇是零基礎入門到進階的介紹DAX函數,有興趣可以參考看看。

每天學習,每天充電:Power BI文章合集