实用·技巧之用“Power Pivot for Excel”分析数据

Microsoft Power Pivot for Excel是Excel 2016中的一个加载项，用于增强Excel的数据分析功能。

运用 PowerPivot，可以从多个不同类型的数据源将数据导入 Excel 的数据模型并创建关系，利用数据模型中的数据可以轻松地创建数据透视表。

加载 Microsoft Power Pivot for Excel

默认情况下，Microsoft Power Pivot for Excel 加载项不会被加载，用户需要进行手动设置，操作步骤如下。

步骤 1 依次单击【文件】→【选项】命令，在弹出的【Excel 选项】对话框中选择【加载项】命令，在【管理】下拉列表中选择【COM加载项】命令，单击【转到】按钮，如图8-79所示。

步骤 2 在【COM 加载项】对话框中，选中【Microsoft Power Pivot for Excel】复选框，单击【确定】按钮完成加载，在功能区会出现【Power Pivot】选项卡，如图 8-80 所示。

利用 PowerPivot 分析数据

图 8-81 展示了某金融公司“出单明细”“产品信息”和“理财师信息”数据列表，这些数据列表分别保存在不同的工作表中，现在希望利用 PowerPivot 将这 3 张数据列表创建关联后进行分析。

操作步骤如下。

步骤 1 单击“出单明细”工作表中的任意一个单元格，如 B3, 在【Power Pivot】选项卡中单击【添加到数据模型】按钮，弹出【创建表】对话框，此时【表的数据在哪里】文本框会自动输入当前连续的数据区域（如 $A$1 : $G$256），选中【我的表具有标题】复选框，单击【确定】按钮关闭对话框，进入【Power Pivot for Excel】窗口，显示已创建的PowerPivot 链接表“表 1”，如图 8-82 所示。

步骤 2 重复以上步骤，分别以工作表“产品信息”和“理财师信息”中数据列表创建 PowerPivot链接表“表 2”和“表 3”。

步骤 3 鼠标右键单击“表标签”中的表名称【表 1】，在弹出的快捷菜单中选择【重命名】命令，输入“出单明细”按 <Enter> 键，将“表 1”重命名为“出单明细”，如图 8-83 所示。

步骤 4 重复以上步骤，分别将“表 2”和“表 3”重命名为“产品信息”和“理财师信息”。

步骤 5 在【主页】选项卡中单击【关系图视图】按钮，调出【关系图视图】界面。将【出单明细】列表框中的“产品编号”字段拖曳至【产品信息】列表框中的“产品编号”字段上，完成“出单明细”和“产品信息”表以“产品编号”为关联关系的创建。重复以上步骤，将“出单明细”和“理财师信息”表以“理财师工号”为关联关系创建关联，如图 8-84 所示。

步骤 6 在【主页】选项卡中单击【数据视图】按钮，调出【数据视图】界面，在“出单明细”表的字段标题栏的最右侧的【添加列】字段处右击鼠标，在弹出的快捷菜单中选择【重命名列】，在编辑框中输入“考核业绩”后按 <Enter> 键，创建“考核业绩”字段。选中【考核业绩】字段，在【编辑栏】中输入如下公式，如图 8-85 所示。

RELATED 函数是一个筛选器函数，用于从其他表返回相关值。本例中的“RELATED(' 产品信息 '[ 考核系数 ]) ”部分，用于按照“出单明细”表和“产品信息”表的关联关系，从“产品信息”表中返回相应“产品编号”对应的“考核系数”。然后再用“出单明细”表中的“金额”乘以对应的“考核系数”，得出“考核业绩”。

例如，合同编号为“TZ00102”的出单明细中，产品编号“CX003”对应的“产品信息”表中的“考核系数”为“2”，则考核业绩（1 700）= 金额（850）* 考核系数（2），如图 8-86 所示。

步骤 7 在【主页】选项卡中依次单击【数据透视表】→【图和表 ( 垂直 )】命令，系统将自动返回Excel 界面，并弹出【创建数据透视图和数据透视表 ( 垂直 )】对话框，选中【新工作表】单选按钮，最后单击【确定】按钮，如图 8-87 所示。

步骤 8 此时将在新工作表中创建一张空白的数据透视图和数据透视表，如图 8-88 所示。

步骤 9 单击【图表 1】区域，在【数据透视表字段】列表中单击【出单明细】下拉按钮，单击垂直滚动条，显示出【考核业绩】字段，选中【考核业绩】复选框。重复以上操作，选中【产品名称】复选框，如图 8-89 所示。

步骤 10 单击【数据透视表字段】列表中【理财师信息】下拉按钮，将【分公司】字段拖曳至【图例 ( 系列 )】区域内，如图 8-90 所示。

步骤 11 参考步骤 9、步骤 10 的方法，为【数据透视表 1】添加分析维度，将【理财师信息】表内的“分公司”和“理财师姓名”字段添加到【行】区域，将【出单明细】表内的“考核业绩”和“客户编号”字段添加到【值】区域，生成的数据透视表如图 8-91 所示。

步骤 12 选中数据透视表中【以下项目的计数 : 客户编号】字段中任意一个单元格，如 D20，在【数据透视表工具】的【分析】选项卡下单击【字段设置】命令，弹出【值字段设置】对话框。

选择【值汇总方式】标签下的【非重复计数】选项，然后单击【确定】按钮关闭对话框，如图 8-92 所示。

步骤 13 分别把字段名“以下项目的总和 : 考核业绩”和“以下项目的非重复计数 : 客户编号”修改为“考核业绩 ( 万元 )”和“客户数量”。修改数据透视表的报表布局为【以表格形式显示】（请参阅技巧 131 步骤 1），并以“理财师姓名”的“考核业绩”降序排列（请参阅技巧 131 步骤 3），最终完成的数据透视表如图 8-93 所示。