利用Excel的新功能,Power Query或者Power Pivot,来解决多个表之间的匹配合并问题,对于很多人来说可能是一个全新的想法!非常简单有效!
EXCEL可以自动将两个表之间的数据匹配、完成、合并到一个表中,这通常类似于一个主表(如订单表)和一个明细列表(如订单项目列表)之间的匹配、完成、合并。
以前这个问题可以用vlookup之类的函数解决,或者要匹配的列很多的话,用vlookup会比较麻烦,或者Excel因为大量的公式计算而卡死的时候经常用VBA来解决这个问题。
不过现在随着Excel2016、Power Query、Power Pivot这些超级强大的新功能的推出(相应的插件可以在Excel 2010或者Excel2013中从微软官方下载),这个问题不再需要vlookup函数或者VBA来解决了。而且通过Power Query和PowerPivot的解决方案不仅非常简单,而且可以随着数据源的更新一键刷新,得到最新的结果。
以下解决方案分别由Power Query和Power Pivot提供。
随着数据时代的到来,公司的数据量越来越大,对综合分析的要求也越来越高。比如现在有订单和订单列表,订单的一些信息经常被读入订单列表,供相关部门使用。本来只要几个栏目好,vlookup看一下就行了。但是现在,使用vlookup非常麻烦,因为它经常需要大量的数字。下面的订单比较少。我某个项目的合同表格差不多有300栏,其实也不算多。
这种情况下,用vlookup就有点吃力了。虽然vlookup是Excel中极其重要的功能,但是在大数据时代很难承担数据关联和合并的负担,所以微软在Excel中加入了PowerQuery功能。具体实现方法如下。
步骤05-展开要访问的表和所需的列。
通过以上五个简单的步骤,点击几下鼠标,两个表的所有数据就会匹配合并在一起,并可以返回到Excel中,如下图所示:
以上是通过幂查询实现表间数据合并的方法。但实际上,在很多数据分析中,如果将数据合并在一起,会导致大量数据重复,存储容量增加。但实际分析目的只需要根据相关数据进行分析即可。所以Power Pivot提供了更进一步的解决方案——直接构造两个表之间的数据关系,然后进行分析,不需要对数据进行集成。
仍然以订单和订单列表为例。
加载后,Power Pivot中的数据如下:
单击“关系视图”可以看到三个表的内容显示在三个不同的框中。通过用鼠标按住这些框顶部的名称区域,您可以根据需要将它们拖放到不同的位置。
订单、订单列表和产品列表之间的关系是,订单列表中的每个订单对应订单列表中的多个订单(产品)项,订单列表中的产品可以从产品列表中获取更详细的相关信息。
结果如下:
按照同样的方法,也可以建立订单列表和产品列表的关系,最终结果如下:
这样就建立了三个表之间的关系,然后就可以直接从每个表中拖拽所需的信息进行数据透视分析,如下图所示:
例如,要分析各种产品类别的销售额:
介绍了在EXCEL中通过Power Query将两个表之间的数据自动匹配、完成并合并到一个表中的方法,以及通过Power Pivot构建多个表之间的关系直接进行统计分析的解决方案,可以根据实际需要选择使用。