使用内置数据模型创建数据透视表和数据透视图

Excel 2016以上版本中，Power Pivot的大部分功能直接内置到了Excel中的数据模型之内，故所有用户均可在Excel中直接构建隐匿数据模型，并且将这些数据模型作为数据透视分析的基础。

【例8-14】　如图8-35为龙脊山集团公司的销售计划与实际执行情况数据。比较销售计划与实际情况的操作过程如下。

图8-35　龙脊山集团公司销售计划与执行情况

Step 1：选择“计划”表中的数据区域任一单元格→单击【插入】→单击【表格】中的“表格”按钮→在弹出的“创建表”对话框中，自动选中了连续区域，并勾选了【表包含标题】复选框→单击【确定】（图8-36①）。

也可以采用更简捷的方法：选择“计划”表中的数据区域任一单元格→【开始】/【套用表格式】，任选或自定义一种格式即可。此时，数据区域转化为数据表（图8-36②）。

Step 2：同理，将“实际”表中的数据区转化为数据表。

图8-36　数据区域转化为数据表的两种方式　

Step 3：点击【Power Pivot】→【添加到数据模型】→【管理】（图8-37①），出现“数据模型”窗口。点击【关系图视图】→在弹出的两张表的字段目录（图8-37②）→用鼠标左键按住“计划”下的“分公司”拖曳到“实际”下的“分公司”（图8-37③），这样就建立了两表之间的关系。

图8-37　管理数据模型

Step 4：点击图8-37④中【数据透视表】→选择【图和表垂直】（图8-38①）→在弹出的【创建透视图和透视表（垂直）】对话框中默认【现有工作表】选项→单击【确定】（图8-38②），结果如图8-38③所示。

图8-38　用数据模型创建数据透视表（一）

Step 5：点击图8-38③中【在此区域单击可使用数据透视表】→将“计划”表中的“分公司”字段拖曳到【行】，将“计划”表中的“计划销量”和“计划销售额”“实际”表中的“数量”和“销售金额”放入【值】（图8-39①）→选中“以下项目的总和：数量”按住鼠标将其拖曳至“以下项目的总和：计划销量”和“以下项目的总和：计划销售额”之间（图8-39②）→【开始】/【编辑】/【查找和选择】/【替换】→在【查找内容（N）：】里输入“以下项目的总和：”，在【替换为（E）：】里输入一个空格→点击【全部替换】（图8-39③）。(www.xing528.com)

图8-39　用数据模型创建数据透视表（二）

Step 6：将光标置于图8-38④所示【图表1】→在【数据透视图字段】里，将“实际”表中的“日期”字段拉到【轴（类别）】，【数量】拖曳到【值】（图8-40①）→【数据透视字段工具】/【分析】/【字段按钮】选择【全部隐藏】（图8-40②），则隐藏掉图8-40①所示数据透视图中的字段按钮。

图8-40　根据数据模型创建数据透视图

Step 7：复制图8-40①所示数据透视图并将之置于并排处，其他同上一步的操作。在【数据透视图字段】里，取消勾选原来的选项，然后将“实际”表中的“物料号”字段拉到【轴（类别）】，【销售金额】拖曳到【值】。在【数据透视字段工具】/【设计】栏里，用户可以选择自己喜欢的图表样式。

Step 8：点击【数据透视字段工具】/【分析】/【插入切片器】→选择“品种”，弹出品种切片器（图8-41①-③）。将切片器置于图8-40所示图表之上，并将之拉长等于两并列图表宽度，点击【选项】→【列】设为7（图8-41④）→【报表连接】（图8-41⑤），结果如图8-42所示。这样，数据透视表和两张数据透视图都会随着切片器选项的不同而发生变化，便于分析数据所包含的信息。

图8-41　插入数据切片器

图8-42　用切片器控制透视图和透视表展示

说明：

关系是两个数据表间的联系，它基于每个表的一列或多列。在数据模型中有两种关系类型：一对一，一对多。而数据模型的最大优势就在于通过创建表间的关系使来自不同数据源的数据表能统一地透视在一张数据透视表和透视图之中。

在上例中，“计划”与“实际”是典型的一对多的关系，“计划”表的“公司”中每公司只有唯一记录，代表“一”方，而“实际”表的“公司”每个公司都有若干记录，代表“多”方。

在创建“一对多”的关系时，须将“一”表作为相关表来创建关系。如果创建方向相反，数据模型为自动进行修正。如将代表“多”的表作为“相关表”创建关系，在“创建关系”对话框下将显示“无法按此方向创建该关系，因为相关查阅列包含重复值。单击‘确定’以按相关方向创建关系。”的警告。

本例以“计划”表作为相关表，“分公司”作为“相关列”来创建“计划”表与“实际”表的关系。最后，通过数据透视表汇总比较计划与实际的销售量和销售额数据。必须以“计划”表的“分公司”字段作为行字段，如果以“实际”表的分公司字段作为行字段进行汇总，将达不到预期效果。