Excel資料模型入門介紹，第一次學必看！

Excel資料模型一詞聽起來很深奧、像是很後端的技術，但簡單來講，資料模型只是將資料表之間建立起關聯性，聯結成一個資料模型，以利進行整體資料的篩選、計算與分析，進而建立出互動式儀錶板(Dashboard)與報表(Report)；若要講地專業些，Excel資料模型可讓用戶以不同的資料維度(Dimension)與深度(Granularity)來進行資料分析，提升了資料分析的能力。讀到文章最後，各位將可自製出簡易的資料模型。

Excel資料模型歷史簡介 Excel資料模型製作流程 第1步：藉由Power Query載入資料表到資料模型 第2步：將資料表之間建立關聯性 第3步：增加資料行(Column)、量值(Measures) 第4步：建立儀錶板、報表 總結與補充

Excel資料模型歷史簡介

讓Excel具備資料模型功能的是一個叫做Power Pivot的工具，Power Pivot最早起源於Excel 2010版本的插件，在Excel 2013/2016專業版本之後已成為內建的功能(若欲查看您的Excel版本是否具備Power Pivot工具請點選此官方超連結)。
PS 在Excel中Power Pivot一詞等同於資料模型(Data Model)。

Excel資料模型的功能為：連結資料來源並載入到資料模型，接著將資料表之間建立關聯性，最後再載回Excel進行資料分析、製作圖表。資料模型功能原來自於後端資料庫系統功能，因此部分非小編專業，小編有Google搜尋了一下，簡單舉個例子Oracle SQL Developer Data Modeler的軟體畫面給各位參考。(如下圖)

Power Pivot與上篇文章介紹的Power Query編輯器一同聯手將Excel資料分析能力提升到另一個境界：藉由Power Query可連結不同資料來源載入資料模型的能力，解開了既往Excel資料分析資料列數的限制(1百萬列)；及藉由Power Pivot資料模型可將不同資料表建立關聯性的功能，賦予了Excel(前端)具備了後端資料倉儲(Data Warehouse)的資料架構，可以維度表(Dimension Table)來分析事實表(Fact Table)，此部分因屬於維度設計(Dimensional Design)的領域，將另寫文章介紹。

除了Excel軟體具有資料模型功能外，於2016年一個專門做為資料模型及(互動式)視覺化報表的軟體誕生了-Power BI，Power BI軟體介面有3個檢視畫面：(1)模型檢視、(2)資料檢視、及(3)報表檢視，清楚地將其定位在資料模型的工具(如下圖)；Power BI較Excel具備更豐富的視覺化物件、更多可連結的資料來源、可嵌入在更多應用程式裡，及更廣泛應用於雲端，小編將再另寫文章介紹。

Excel資料模型製作流程

製作Excel資料模型大致分為4個步驟，包含：

(1)藉由Power Query載入資料表到資料模型

(2)於資料模型中，將資料表之間建立關聯性

(3)增加資料行(Column)、量值(Measures)

(4)建立儀錶板、報表

小編有自製了一個資料模型製作流程如下圖。

接下來，小編將以範例逐步說明，製作出簡易的資料模型及儀錶板。

小編有將一個Microsoft的Contoso資料庫檔案轉檔成Excel檔案供大家練習此範例(數據與小編不同)，檔案下載連結如下，另請各位打開一個空白的Excel工作簿依照以下步驟進行練習。

Contoso資料庫檔案

第1步：藉由Power Query載入資料表到資料模型

若讀者尚未接觸過Power Query，可以先參考上篇文章Power Query入門介紹，針對此範例，連結資料的第一步驟同樣為： Tab Data -> Get Data -> From File 。

選擇資料來源檔案後，會跳出瀏覽視窗，因本範例需要4個資料表：Calendar(日曆)、Customer(客戶)、Product(產品)及Sales(銷售)，故可先點選Select multiple items(多重選取)，就可一次勾選4個資料表。

因為此範例資料來源的格式不需再進行其他資料轉換(Transform Data)，所以不需要進入Power Query Editor，可直接點選Load -> Load To進行數據處理ETL的L載入步驟(還沒聽過數據處理ETL的朋友可參考之前的文章)，出現載入資料(Import Data)視窗後，要特別注意須勾選Add this data to the Data Model，才可將資料載入資料模型中。

第2步：將資料表之間建立關聯性

載入資料表後，接著要進入資料模型建立資料表關聯性，進入資料模型有兩個方式，以Microsoft 365版本來講，可從Tab Power Pivot或Tab Data裡點選Power Pivot資料模型圖示進入(如下圖)。

若您的Excel Tab沒有Power Pivot，請依照下圖流程開啟此功能：
Options -> Add-ins -> Manage: COM Add-ins -> Microsoft Power Pivot for Excel。若還是不行，代表您的Excel版本不支援Power Pivot資料模型功能。

進入Power Pivot視窗後，會預設在資料檢視(Data View)畫面，整體介面與Excel相似，視窗下方可以看到我們載入的4個資料表。詳細的Power Pivot工具使用方式將另寫文章介紹。

將資料表之間建立關聯性最快的方式是先進入圖表檢視(Diagram View)，進入後會看到4個資料表的圖示以預設的水平排列方式排列，建議大家可先將資料表重新排列成如下圖形狀或星形(Star)，會較易操作建立資料表關聯性。

關聯性的建立是將維度表(Dimension Table)的主鍵(Primary Key)與事實表(Fact Table)的外鍵(Foreign Key)進行連結，可直接以滑鼠拖拉的方式建立關聯性(如下動圖)。

關聯線的兩邊有”1″和”*”的符號，一般來說”1″側為維度表，主鍵不會重複，”*”側為事實表，外鍵有重複，此關係稱為1對多的關係(將另寫文章介紹)。另外各位下載的檔案裡的主鍵及外鍵名稱跟小編的不太一樣，為ProductKey、CustomerKey、Date與Order Date。

建立資料表之間的關聯性後，接著要回到Excel準備進行下一步驟，回到Excel有兩個方式：(1)點選Power Pivot視窗右上角”X”鍵或(2)左上角Excel圖示鍵，說明如下圖。

在進行下一步驟「增加資料行(Column)、量值(Measures)」之前，讓我們先從資料模型中建立一個樞紐分析表(Pivot Table)，操作步驟為：Tab Insert -> PivotTable -> From Data Model。

於下圖各位可以看到從資料模型(Power Pivot)建立的樞紐分析表與一般的樞紐分析表圖示不太一樣，多了一個橘色的資料庫標示。

它與一般樞紐分析表操作上最直接的差異就是所有帶有橘色資料庫標示的資料表皆可使用於同一個樞紐分析表內；一般的樞紐分析表僅能使用一個資料表的資料。(若對於樞紐分析概念尚未完全了解的朋友可以先參考此篇文章：Excel樞紐分析表概念介紹)

資料模型樞紐分析表的操作步驟如同一般的樞紐分析表，將資料拖拉至欄(Columns)、列(Rows)與值(Values)即可，此範例將年(Year)與月(Month)放在欄、產品種類(Product Category)放在列，及銷售數量(Quantity)放在值。

但大家有沒有發現，在我們的資料表中找不到最重要的銷售額(Sales Amount)項目，這時候，增加資料行(Column)與量值(Measures)的功能就派上用場了，讓我們一起進入下一步驟。

第3步：增加資料行(Column)、量值(Measures)

撰寫資料行與量值的公式係屬於另一種資料分析語言，稱作Data Analysis Expressions(簡稱DAX)，詳細DAX介紹請參考此篇文章，本篇文章將只說明增加資料行與量值的介面操作。
本篇文章僅需增加一個量值「銷售額」，「銷售額」的公式為：
=SUMX( ‘Sales’, ‘Sales'[Quantity] * RELATED(‘Product'[Unit Price]))，語意為將銷售資料表中每列的銷售量乘以於產品資料表對應的單價後進行加總。

有兩個方法可輸入量值公式，第1個方式為：Tab Power Pivot -> Measures -> New Measure。

第2個方式為進入Power Pivot視窗裡進行輸入。

增加量值後，Excel會自動將此量值加入到樞紐分析表內的值(Values)欄位，若沒有自動加入代表你不是在樞紐分析表激活狀態下輸入，可再以滑鼠勾選量值的核取方塊(CheckBox)或以手動方式將量值拖拉至值(Values)欄位。另外下圖各位可以看到量值的顯示符號為”fx“。

PS 此樞紐分析表是以產品種類與年月的維度(Dimension)來分析銷售額。

第4步：建立儀錶板、報表

本範例儀錶板的組成將包含一個資料表及一個圖表，資料表已於上個步驟裡完成，接著將進行插入圖表。
那要插入什麼圖表呢？在Excel中可使用資料模型資料來源的圖表為樞紐分析圖表(PivotChart)，插入步驟為：Tab Insert -> PivotChart -> PivotChart，點選後會跳出PivotChart建立視窗，資料來源預設值為Use this workbook’s Data Model(使用本工作簿資料模型)，點選OK。

因為上個步驟所做的資料表是以產品種類與年月的維度(Dimension)來分析銷售額，此圖表小編想換為以客戶及產品種類的維度(Dimension)進行銷售額分析，故將客戶名稱拉入軸(Axis)、銷售額拉入值(Values)，出現的樞紐分析圖表為預設的直條圖。PS 等會兒再以產品種類設定篩選器(Slicer)。

因本範例欲使用的圖表為橫條圖，故需變更圖表種類，操作步驟為：
Tab Design -> Change Chart Type -> Bar。(如下圖)

接著我們替樞紐分析圖表增加一個互動式篩選器(Slicer)，以產品種類作為篩選項目(產品種類維度)，操作步驟為滑鼠右鍵點選Product Category – > Add as Slicer。

增加完樞紐分析圖表及篩選器後之畫面如下圖，另可於篩選器上勾選重複選取的按鈕，或是按住鍵盤Ctrl鍵再以滑鼠點選也可以達到多重選取的效果。

最後，將資料表及圖表進行排版、顏色等格式調整後，即完成了本範例之儀錶板。(如下動圖)

總結及補充：

本篇文章以深入淺出的方式介紹Excel資料模型，相信大家照著步驟操作可以成功完成此範例儀錶板的製作，但若要真的去設計一個資料模型及儀錶板其實不容易，需要具備的技能包含：
1.Power Query(文章連結)
2.Power Pivot
3.樞紐分析表(文章連結)
4.DAX語言(文章連結)
5.資料架構維度設計(Dimensional Design)，含星形模型(Star Schema)、雪花模型(Snokeflake Schema)等。
6.儀錶板(Dashboard)設計。

鼓勵各位朋友可先學習上述六項技能的基本知識，及實際應用在日常工作上，其實很多實際應用不會那麼複雜，例如COVID-19快報儀錶板、人員取證狀況儀錶板、生產狀況儀錶板等等；另外於工廠應用裡，較複雜的資料模型屬於財務模型，希望未來也可以跟大家分享財務模型的製作方式。