內容
在 Excel 中構建數據透視表時，在第一個對話框中，我們被要求設置初始範圍並選擇插入數據透視表的位置，下面有一個不起眼但非常重要的複選框 - 將此數據添加到數據模型 （添加此數據 到數據模型） 再高一點，開關 使用本書的數據模型 （使用本工作簿的數據模型）:
不幸的是，許多長期熟悉數據透視表並在工作中成功使用它們的用戶有時並不真正理解這些選項的含義，並且從未使用它們。 而徒勞。 畢竟，與經典的 Excel 數據透視表相比，為數據模型創建數據透視表給我們帶來了幾個非常重要的優勢。
然而，在仔細考慮這些“包子”之前，讓我們先了解一下，這個數據模型到底是什麼？
什麼是數據模型
數據模型 （縮寫為 MD 或 DM = 數據模型）是 Excel 文件中的一個特殊區域，您可以在其中存儲表格數據——如果需要，一個或多個表相互鏈接。 事實上，這是一個嵌入在 Excel 工作簿中的小型數據庫（OLAP 多維數據集）。 與在 Excel 本身工作表上以常規（或智能）表格形式存儲數據的經典存儲相比，數據模型具有幾個顯著優勢：
- 表可高達 2億行，而 Excel 工作表可以容納 1 萬多一點。
- 儘管規模巨大，但仍會執行此類表的處理（過濾、排序、對其進行計算、構建摘要等） 非常快 比 Excel 本身快得多。
- 使用模型中的數據，您可以使用 內置 DAX 語言.
- 加載到數據模型中的所有信息都非常 強烈壓縮 使用特殊的內置存檔器，並適度增加原始 Excel 文件的大小。
該模型由 Microsoft Excel 內置的特殊插件管理和計算 - 動力樞軸我已經寫過。 要啟用它，請在選項卡上 開發人員 點擊 COM 加載項 （開發者——COM 插件） 並選中相應的框：
如果選項卡 開發人員 （開發商）你在功能區上看不到它，你可以通過它打開它 文件 - 選項 - 功能區設置 （文件——選項——自定義功能區）. 如果在上面顯示的 COM 加載項列表中的窗口中您沒有 Power Pivot，則它不包含在您的 Microsoft Office 版本中🙁
在出現的 Power Pivot 選項卡上，會有一個淺綠色的大按鈕 管理 （管理），單擊其中將打開 Excel 頂部的 Power Pivot 窗口，我們將在其中看到當前書籍的數據模型的內容：
一路上的一個重要說明：Excel 工作簿只能包含一個數據模型。
將表加載到數據模型中
要將數據加載到模型中，首先我們將表格變成動態的“智能”鍵盤快捷鍵 按Ctrl+T 並在標籤上給它一個友好的名字 構造函數 （設計）. 這是必需的步驟。
然後您可以使用以下三種方法中的任何一種進行選擇：
- 按按鈕 添加到模型 （添加到數據模型） 選項卡 動力樞軸 選項卡 首頁
（家）.
- 選擇團隊 插入 - 數據透視表 （插入 - 數據透視表） 並打開復選框 將此數據添加到數據模型 （將此數據添加到數據模型）. 在這種情況下，根據加載到 Model 中的數據，也會立即構建一個數據透視表。
- 在高級選項卡上 數據 （日期） 點擊按鈕 從表/範圍 （從表/範圍）將我們的表加載到 Power Query 編輯器中。 此路徑最長，但如果需要，您可以在此處執行額外的數據清理、編輯和各種轉換，其中 Power Query 非常強大。
然後通過命令將梳理後的數據上傳到Model 主頁 — 關閉並加載 — 關閉並加載... （首頁 — 關閉並加載 — 關閉並加載到…）. 在打開的窗口中，選擇選項 只需創建一個連接 （僅創建連接） 而且，最重要的是，打勾 將此數據添加到數據模型 （將此數據添加到數據模型）.
我們構建數據模型的摘要
要構建摘要數據模型，您可以使用以下三種方法中的任何一種：
- 按下按鈕 匯總表 （數據透視表） 在 Power Pivot 窗口中。
- 在 Excel 中選擇命令 插入 - 數據透視表 並切換到模式 使用本書的數據模型 （插入 - 數據透視表 - 使用此工作簿的數據模型）.
- 選擇團隊 插入 - 數據透視表 （插入 - 數據透視表） 並打開復選框 將此數據添加到數據模型 （將此數據添加到數據模型）. 當前的“智能”表將被加載到模型中，並為整個模型構建一個匯總表。
既然我們已經弄清楚瞭如何將數據加載到數據模型中並在其上構建摘要，那麼讓我們來探討一下這給我們帶來的好處和優勢。
好處1：不使用公式的表之間的關係
只能使用來自一個源表的數據來構建常規摘要。 如果您有幾個，例如銷售、價格表、客戶目錄、合同登記等，那麼您首先必須使用 VLOOKUP 等功能將所有表中的數據收集到一個表中 （VLOOKUP）， 指數 （指數）, 更多暴露 （匹配）, 薩默斯林 （總和） 之類的。 這是冗長而乏味的，並且會使您的 Excel 陷入包含大量數據的“思想”。
就數據模型的總結而言，一切都簡單得多。 在 Power Pivot 窗口中設置表之間的關係一次就足夠了 - 並且已經完成。 為此，在選項卡上 動力樞軸 按下按鈕 管理 （管理） 然後在出現的窗口中——按鈕 圖表視圖 （圖表視圖）. 它仍然是在表之間拖動公共（鍵）列名（字段）以創建鏈接：
之後，在數據模型的摘要中，您可以在摘要區域（行、列、過濾器、值）中放入任何相關表中的任何字段——所有內容都將自動鏈接和計算：
好處 2：計算唯一值
常規數據透視表使我們有機會選擇幾個內置計算函數之一：求和、平均值、計數、最小值、最大值等。在數據模型摘要中，此標準列表中添加了一個非常有用的函數來計算唯一（非重複值）的數量。 例如，借助它，您可以輕鬆計算我們在每個城市銷售的獨特商品（範圍）的數量。
右鍵單擊該字段 - 命令 值字段選項 並在選項卡上 手術 選擇 不同元素的數量 （不同的計數）:
好處 3：自定義 DAX 公式
有時您必須在數據透視表中執行各種額外的計算。 在常規摘要中，這是使用計算字段和對象完成的，而數據模型摘要使用特殊 DAX 語言（DAX = 數據分析表達式）中的度量。
要創建度量，請在選項卡上選擇 動力樞軸 命令 度量 - 創建度量 （措施——新措施） 或者只需右鍵單擊 Pivot Fields 列表中的表並選擇 添加度量 （添加措施） 在上下文菜單中：
在打開的窗口中，設置：
- 表名稱創建的度量將被存儲在哪裡。
- 測量名稱 – 您理解的新領域的任何名稱。
- 產品描述 - 可選的。
- 公式 – 最重要的，因為這裡我們要么手動輸入，要么點擊按鈕 fx 並從列表中選擇一個 DAX 函數，當我們將度量值放入 Values 區域時，它應該計算結果。
- 在窗口的下部，您可以立即為列表中的度量設置數字格式 類別.
DAX 語言並不總是很容易理解，因為它不是對單個值進行操作，而是對整個列和表進行操作，即需要在經典 Excel 公式之後對思維進行一些重組。 然而，這是值得的，因為它在處理大量數據方面的能力很難被高估。
好處 4：自定義字段層次結構
通常，在創建標準報表時，您必須以給定的順序將相同的字段組合放入數據透視表中，例如 年-季-月-日， 或者 類別-產品， 或者 國家-城市-客戶 等等。在數據模型總結中，這個問題很容易通過創建自己的來解決 層次結構 — 自定義字段集。
在 Power Pivot 窗口中，使用 按鈕切換到圖表模式 圖表視圖 選項卡 首頁
（首頁-圖表視圖）, 選擇 按Ctrl 所需的字段並右鍵單擊它們。 上下文菜單將包含命令 創建層次結構 （創建層次結構）:
可以重命名創建的層次結構並用鼠標將所需字段拖入其中，以便稍後在一次移動中將它們放入摘要中：
好處 5：定制模板
延續上一段的思路，在Data Model的總結中，你也可以為每個字段創建自己的元素集。 例如，從整個城市列表中，您可以輕鬆地製作一組僅在您的責任範圍內的城市。 或者僅將您的客戶、您的商品等收集到一個特殊的集合中。
為此，在選項卡上 數據透視表分析 在下拉列表中 字段、項目和集合 有對應的命令 （分析 - 領域，我tems & Sets——基於行/列項目創建集合）:
在打開的窗口中，您可以有選擇地刪除、添加或更改任何元素的位置，並以新名稱保存結果集：
所有創建的集合都將顯示在數據透視表字段面板中的單獨文件夾中，從中可以將它們自由拖動到任何新數據透視表的行和列區域：
好處 6：有選擇地隱藏表和列
雖然這是一個很小的，但在某些情況下是非常令人愉快的優勢。 通過右鍵單擊字段名稱或 Power Pivot 窗口中的表選項卡，您可以選擇命令 從客戶端工具包中隱藏 （從客戶端工具中隱藏）:
隱藏的列或表將從數據透視表字段列表窗格中消失。 如果您需要向用戶隱藏一些輔助列（例如，用於創建關係的計算或具有鍵值的列）甚至整個表，這非常方便。
好處 7. 高級向下鑽取
If you double-click on any cell in the value area in a regular pivot table, then Excel displays on a separate sheet a copy of the source data fragment that was involved in the calculation of this cell. This is a very handy thing, officially called Drill-down (in they usually say “fail”).
在數據模型摘要中，這個方便的工具更巧妙地工作。 通過站在我們感興趣的結果的任何單元格上，您可以單擊旁邊彈出的帶有放大鏡的圖標（稱為 表達趨勢)，然後在任何相關表中選擇您感興趣的任何字段：
之後，當前值（Model = Explorer）將進入過濾區域，匯總將由 office 構建：
當然，這樣的過程可以重複多次，始終按照您感興趣的方向深入研究您的數據。
好處 8：將 Pivot 轉換為 Cube 函數
如果您在數據模型的摘要中選擇任何單元格，然後在選項卡上選擇 數據透視表分析 命令 OLAP 工具 - 轉換為公式 （分析 - OLAP 工具 - 轉換為公式），然後整個摘要將自動轉換為公式。 現在行列區域中的字段值和值區域中的結果將使用特殊的立方體函數從數據模型中檢索：CUBEVALUE 和 CUBEMEMBER：
從技術上講，這意味著現在我們不是在處理摘要，而是在處理幾個帶有公式的單元格，即我們可以輕鬆地對摘要中不可用的報表進行任何轉換，例如，在中間插入新行或列報告，在摘要中進行任何額外的計算，以任何所需的方式排列它們，等等。
同時，與源數據的聯繫當然仍然存在，並且將來這些公式將在源發生變化時更新。 美麗！
- 使用 Power Pivot 和 Power Query 在數據透視表中進行計劃事實分析
- 帶有多行標題的數據透視表
- 使用 Power Pivot 在 Excel 中創建數據庫