内容
在 Excel 中构建数据透视表时，在第一个对话框中，我们被要求设置初始范围并选择插入数据透视表的位置，下面有一个不起眼但非常重要的复选框 - 将此数据添加到数据模型 （添加此数据 到数据模型） 再高一点，开关 使用本书的数据模型 （使用本工作簿的数据模型）:
不幸的是，许多长期熟悉数据透视表并在工作中成功使用它们的用户有时并不真正理解这些选项的含义，并且从未使用它们。 而徒劳。 毕竟，与经典的 Excel 数据透视表相比，为数据模型创建数据透视表给我们带来了几个非常重要的优势。
然而，在仔细考虑这些“包子”之前，让我们先了解一下，这个数据模型到底是什么？
什么是数据模型
资料模型 （缩写为 MD 或 DM = 数据模型）是 Excel 文件中的一个特殊区域，您可以在其中存储表格数据 - 如果需要，可以将一个或多个表相互链接。 事实上，这是一个嵌入在 Excel 工作簿中的小型数据库（OLAP 多维数据集）。 与 Excel 本身工作表上以常规（或智能）表格形式存储数据的经典存储相比，数据模型具有几个显着优势：
- 表可高达 2亿行，而 Excel 工作表可以容纳 1 万多一点。
- 尽管规模巨大，但仍会执行此类表的处理（过滤、排序、对其进行计算、构建摘要等） 非常快 比 Excel 本身快得多。
- 使用模型中的数据，您可以使用 内置 DAX 语言.
- 加载到数据模型中的所有信息都非常 强烈压缩 使用特殊的内置存档器，并适度增加原始 Excel 文件的大小。
该模型由 Microsoft Excel 内置的特殊插件管理和计算 - 的PowerPivot我已经写过。 要启用它，请在选项卡上 开发人员 点击 COM 加载项 （开发者——COM 插件） 并选中相应的框：
如果选项卡 开发人员 （开发商）你在功能区上看不到它，你可以通过它打开它 文件 - 选项 - 功能区设置 （文件 - 选项 - 自定义功能区）. 如果在上面显示的 COM 加载项列表中的窗口中您没有 Power Pivot，则它不包含在您的 Microsoft Office 版本中🙁
在出现的 Power Pivot 选项卡上，会有一个浅绿色的大按钮 管理 （管理），单击其中将打开 Excel 顶部的 Power Pivot 窗口，我们将在其中看到当前书籍的数据模型的内容：
一路上的一个重要说明：Excel 工作簿只能包含一个数据模型。
将表加载到数据模型中
要将数据加载到模型中，首先我们将表格变成动态的“智能”键盘快捷键 按Ctrl+T 并在标签上给它一个友好的名字 构造函数 （设计）. 这是必需的步骤。
然后您可以使用以下三种方法中的任何一种进行选择：
- 按按钮 添加到模型 （添加到数据模型） 标签 的PowerPivot 标签 主页 （首页）.
- 选择团队 插入 - 数据透视表 （插入 - 数据透视表） 并打开复选框 将此数据添加到数据模型 （将此数据添加到数据模型）. 在这种情况下，根据加载到 Model 中的数据，也会立即构建一个数据透视表。
- 在高级选项卡上 时间 （日期） 点击按钮 从表/范围 （从表/范围）将我们的表加载到 Power Query 编辑器中。 此路径最长，但如果需要，您可以在此处执行额外的数据清理、编辑和各种转换，其中 Power Query 非常强大。
然后通过命令将梳理后的数据上传到Model 主页 — 关闭并加载 — 关闭并加载... （首页 — 关闭并加载 — 关闭并加载到…）. 在打开的窗口中，选择选项 只需创建一个连接 （仅创建连接） 而且，最重要的是，打勾 将此数据添加到数据模型 （将此数据添加到数据模型）.
我们构建数据模型的摘要
要构建摘要数据模型，您可以使用以下三种方法中的任何一种：
- 按下按钮 汇总表 （数据透视表） 在 Power Pivot 窗口中。
- 在 Excel 中选择命令 插入 - 数据透视表 并切换到模式 使用本书的数据模型 （插入 - 数据透视表 - 使用此工作簿的数据模型）.
- 选择团队 插入 - 数据透视表 （插入 - 数据透视表） 并打开复选框 将此数据添加到数据模型 （将此数据添加到数据模型）. 当前的“智能”表将被加载到模型中，并且将为整个模型构建一个汇总表。
既然我们已经弄清楚了如何将数据加载到数据模型中并在其上构建摘要，那么让我们来探讨一下这给我们带来的好处和优势。
好处1：不使用公式的表之间的关系
只能使用来自一个源表的数据来构建常规摘要。 如果您有几个，例如销售、价格表、客户目录、合同登记等，那么您首先必须使用 VLOOKUP 等功能将所有表中的数据收集到一个表中 （VLOOKUP）， 指数 （指数）, 更多暴露 （匹配）, 萨默斯林 （总和） 之类的。 这是冗长而乏味的，并且会使您的 Excel 陷入包含大量数据的“思想”。
就数据模型的总结而言，一切都简单得多。 在 Power Pivot 窗口中设置表之间的关系一次就足够了 - 并且已经完成。 为此，在选项卡上 的PowerPivot 按下按钮 管理 （管理） 然后在出现的窗口中——按钮 图表视图 （图表视图）. 它仍然是在表之间拖动公共（键）列名（字段）以创建链接：
之后，在数据模型的摘要中，您可以在摘要区域（行、列、过滤器、值）中放入任何相关表中的任何字段——所有内容都将自动链接和计算：
好处 2：计算唯一值
常规数据透视表使我们有机会选择几个内置计算函数之一：求和、平均值、计数、最小值、最大值等。在数据模型摘要中，此标准列表中添加了一个非常有用的函数来计算唯一（非重复值）的数量。 例如，借助它，您可以轻松计算我们在每个城市销售的独特商品（范围）的数量。
右键单击该字段 - 命令 值字段选项 并在选项卡上 操作 不同元素的数量 （不同的计数）:
好处 3：自定义 DAX 公式
有时您必须在数据透视表中执行各种额外的计算。 在常规摘要中，这是使用计算字段和对象完成的，而数据模型摘要使用特殊 DAX 语言（DAX = 数据分析表达式）中的度量。
要创建度量，请在选项卡上选择 的PowerPivot 命令 度量 - 创建度量 （措施——新措施） 或者只需右键单击 Pivot Fields 列表中的表并选择 添加度量 （添加措施） 在上下文菜单中：
在打开的窗口中，设置：
- 表名创建的度量将被存储在哪里。
- 测量名称 – 您理解的新领域的任何名称。
- 课程描述 - 可选的。
- 公式 – 最重要的是，因为这里我们要么手动输入，要么点击按钮 fx 并从列表中选择一个 DAX 函数，当我们将度量值放入 Values 区域时，它应该计算结果。
- 在窗口的下部，您可以立即为列表中的度量设置数字格式 产品类别.
DAX 语言并不总是很容易理解，因为它不是对单个值进行操作，而是对整个列和表进行操作，即需要在经典 Excel 公式之后对思维进行一些重组。 然而，这是值得的，因为它在处理大量数据方面的能力很难被高估。
好处 4：自定义字段层次结构
通常，在创建标准报表时，您必须以给定的顺序将相同的字段组合放入数据透视表中，例如 年-季-月-日或 类别-产品或 国家-城市-客户 等等。在数据模型总结中，这个问题很容易通过创建自己的来解决 层次结构 — 自定义字段集。
在 Power Pivot 窗口中，使用 按钮切换到图表模式 图表视图 标签 主页 （首页-图表视图）, 选择 按Ctrl 所需的字段并右键单击它们。 上下文菜单将包含命令 创建层次结构 （创建层次结构）:
可以重命名创建的层次结构并用鼠标将所需字段拖入其中，以便稍后在一次移动中将它们放入摘要中：
好处 5：定制模板
延续上一段的思路，在Data Model的总结中，你也可以为每个字段创建自己的元素集。 例如，从整个城市列表中，您可以轻松地制作一组仅在您的责任范围内的城市。 或者仅将您的客户、您的商品等收集到一个特殊的集合中。
为此，在选项卡上 数据透视表分析 在下拉列表中 字段、项目和集合 有对应的命令 （分析 - 领域，我tems & Sets——基于行/列项目创建集合）:
在打开的窗口中，您可以有选择地删除、添加或更改任何元素的位置，并以新名称保存结果集：
所有创建的集合将显示在单独文件夹中的数据透视表字段面板中，从中可以将它们自由拖动到任何新数据透视表的行和列区域：
好处 6：有选择地隐藏表和列
虽然这是一个很小的，但在某些情况下是非常令人愉快的优势。 通过右键单击字段名称或 Power Pivot 窗口中的表选项卡，您可以选择命令 从客户端工具包中隐藏 （从客户端工具中隐藏）:
隐藏的列或表将从数据透视表字段列表窗格中消失。 如果您需要向用户隐藏一些辅助列（例如，计算或用于创建关系的具有键值的列）甚至整个表，这非常方便。
好处 7. 高级向下钻取
如果双击常规数据透视表中值区域中的任何单元格，Excel 将在单独的工作表上显示参与该单元格计算的源数据片段的副本。这是一个非常方便的事情，正式称为“Drill-down”（他们通常说“失败”）。
在数据模型摘要中，这个方便的工具更巧妙地工作。 通过站在我们感兴趣的结果的任何单元格上，您可以单击旁边弹出的带有放大镜的图标（称为 表达趋势)，然后在任何相关表中选择您感兴趣的任何字段：
之后，当前值（Model = Explorer）将进入过滤区域，汇总将由 office 构建：
当然，这样的过程可以重复多次，始终按照您感兴趣的方向深入研究您的数据。
好处 8：将 Pivot 转换为 Cube 函数
如果您在数据模型的摘要中选择任何单元格，然后在选项卡上选择 数据透视表分析 命令 OLAP 工具 - 转换为公式 （分析 - OLAP 工具 - 转换为公式），然后整个摘要将自动转换为公式。 现在行列区域中的字段值和值区域中的结果将使用特殊的多维数据集函数：CUBEVALUE 和 CUBEMEMBER 从数据模型中检索：
从技术上讲，这意味着现在我们不是在处理摘要，而是在处理几个带有公式的单元格，即我们可以轻松地对摘要中不可用的报表进行任何转换，例如，在中间插入新行或列报告，在摘要中进行任何其他计算，以任何所需的方式排列它们，等等。
同时，与源数据的联系当然仍然存在，并且将来这些公式将在源发生变化时更新。 美丽！
- 使用 Power Pivot 和 Power Query 在数据透视表中进行计划事实分析
- 带有多行标题的数据透视表
- 使用 Power Pivot 在 Excel 中创建数据库