Excel中如何利用PowerPivot实现跨表的的关联查询

Excel

本文重点介绍如何应用excel的PowerPivot实现跨表的关联查询，实际应用于淘宝后台数据、ERP数据导出后如何进行匹配筛选 。自从excel2013以来，安装时自动增加了PowerPivot这组应用程序和服务，强大的分析功能可以取代Access数据库的一些基本功能，也简化了很多运算。关联表的操作，通过两表间的关联字段匹配进行全字段的输出，都有了应用。以前还用函数lookup()(含Hlookup和vlookup)来查找匹配，总碰到重复项的处理和逻辑判断的问题，现在用数据模型可以简化解决了。

应用场景描述： 管理员小云负责售后跟踪，每天都会有一个有特殊要求或者需要特别跟踪的订单号列表，要根据每个订单号提取该订单全部信息。他发现电商后台的订单信息没有那么完善，于是就将自己系统所有订单都导出来，与跟踪订单号列表进行匹配查询，筛选所需内容。

需求分析：①整理订单列表和订单信息表;②匹配查询，筛选信息。

学习了本文后，有兴趣的可以看excel数据模型制作进销存的实例。

规范销售出库表补值 。ERP或者电商系统导出来的数据表格，有可能存有空白格，需要填齐，这样才能用数据模型和数据透视表。补值也很简单，空白格就是用上一行的值填补即可。

1、 选择销售出库表的数据区域(可整列整列选择)菜单开始查找和选择定位条件空值 这时，活跃单元格框出现在最左上角的空白处， 键盘输入=按上箭头 或鼠标选择上一个单元格 按着Ctrl回车 (结束公式编辑状态)。这样，就完成了数据补值。

2、 规范订单号列表按列排序 。经理给的订单号列表是一个单元格存储了很多个单号，这样是无法进行匹配查询的。先将它用逗号分列成横表，再复制选择性粘贴转置粘贴成竖表。

3、 添加表建立数据模型 。在销售出库表， 整列整列地选择表 ，点选菜单PowerPivot添加到数据模型勾选我的表含有标题，确认。第一次启动PowerPivot会很慢，要耐心等待。同样道理，将跟踪的订单号也添加进去。

4、 在数据模型里面建立关系 。关系是关系型数据库里面一个很重要的概念，这里不展开，有兴趣可自己上网查。这里应用 关系，起到数据从一个表传递到另一个表的作用 。回到PowerPivot界面，点选菜单的开始在查看组里选择关系视图。将表2的订单号拖到表1的单据编号，这样，就建立了1个一对多的关系。

5、 用数据模型建数据透视表 。菜单插入数据透视表 选择使用此工作表的数据模型 ，由于之前建立了数据模型，所以这个选项没有致灰位置选现有工作表，跟踪的订单号!D5，其他内容不需要改，确认。 将表2的订单号拖到行，将表1的单据编号、产品名称拖到行，将表1的实发数量、销售金额拖到值 。

6、 调整数据透视表格式 。 菜单设计分类汇总，不显示汇总报表布局，已表格形式显示 。这样，累赘的排列变得清爽了。如果把产品名称从行删除，就更加清爽，容易得出结果，匹配出2条订单(出现在上面)，匹配不出的订单在下方。此外，根据实际查询需要，可以将更多字段拖入行，这里略。

注意事项： 文件的扩展名必须是.xlsx，如果用03版的表格(扩展名.xls)，要先另存为新版excel文件，关闭重新打开才能按上述操作。

在excel中使用PowerPivot实现跨表的关联查询，首先需要确保你的数据已经导入到PowerPivot数据模型中。通过“数据”选项卡中的“从表格/范围”或“从其他来源”导入各个表格。

导入后，点击“PowerPivot”选项卡，然后选择“模型”下的“关系”。在弹出的“创建关系”对话框中，选择你要关联的两个表，以及它们之间的匹配字段。

接下来，定义关系的方向性和基数。方向性是指数据流动的方向，通常是“一到多”或“一对一”。基数是指一个记录在另一个表中可以匹配多少条记录。

创建好关系后，你可以在PowerPivot字段列表中看到这些表，可以通过拖拽字段到“数据透视表”或“数据透视图”来创建跨表查询的报表。PowerPivot会自动识别并使用你定义的关系来汇总和分析数据。

此外，你还可以使用DAX（数据分析表达式）来编写更复杂的查询和计算，这些计算可以跨表进行。