分享

EXCEL表中如何进行多个表格数据关联?

 tbjx999 2017-12-28

在Excel中,关于表格数据的关联,有很多方法,需要看具体要解决什么问题:

1、如果只是为了引用某几个数据,直接用简单函数或公式即可;

2、如果是为了根据某一个标识去读取另一个表里的某一列数据,可以用vlookup函数;

但是,既然提到“多个表格”,很可能是要做的数据分析相对复杂的情况,这种情况下,在用Vlookup函数去关联数据,就很麻烦了。也是因为这个原因,微软将Power Query和Power Pivot功能引入了Excel当中,无论是在Power Query还是Power Pivot里,都可以非常简单地实现数据表之间的关联和数据应用,分别介绍如下:

vlookup虽好,然难承大数据之重【PQ关联表合并】

原创 大海 Excel到PowerBI

小勤:大海,现在公司的数据量越来越大,现在有订单表和订单明细表,经常要将订单表的一些信息读取到订单明细表里,给相关的部门去用,原来只要几列数还好,vlookup读一下就是了,但现在,经常要很多数,用vlookup要累屎了。这个订单表还算少的,还很多其他的一张表里都好几十列了。

大海:呵呵,大数据时代嘛。几十列算少的啦,我上次一个项目上的合同表,有差不多300列,而且这还不算真正多的。

小勤:那怎么办啊!有时候按列顺序读还好,很多时候还不是按顺序的,简直就没法整啊。而且,满篇公式的时候,工作表都要跑不起来了。

大海:这个时候用vlookup的确有点吃力了,虽然vlookup是Excel中极其重要的函数,但是,在大数据时代,已经很难承起数据关联合并的重担了,所以微软才在Excel里加了PowerQuery的功能嘛,点点点,你想要哪些列就哪些列。

小勤:这么神奇?

大海:这段时间PowerQuery的神奇你也不少见了,不用惊讶。现在就告诉你怎么弄。

Step-1:获取订单表数据并仅创建表连接上载

Step-2:获取订单明细表数据并仅创建表连接上载(需要直接上传结果的可以选择表)

Step-3:回到PowerQuery界面(当然,前面一个步骤如果没有关闭并上载的话,不需要这一步)

Step-4:选择要接入外部数据的查询,单击【开始】-【合并查询】

Step-5:选择要接入的外部表、选择两表之间用于匹配的列(可以是多列匹配,文末以另一个例子该步骤的附图方式说明)

Step-6:展开要接入表的列信息,选择要接入的列以及列名显示方式(是否加前缀)

Step-7:查看接入的结果,上载数据

Step-8:改变数据的加载方式(由“仅创建连接”方式改为“表”,若前面订单明细不是以“仅创建连接”的方式创建,该步骤不需要)

小勤:这样真是太方便了,只要选一下匹配要用的列,选择一下要接入哪些列就搞定了!对了,刚才你不是说可以多列匹配吗?原来用vlookup的时候可麻烦了,还得增加辅助列先将那些列连接起来,然后再用辅助列来匹配。

大海:是的。在PowerQuery里也不需要了,只要在选择匹配列时按住ctrl键就可以选择多列了。只是要注意两个表选择匹配列的顺序要一致。如下图所示:

小勤:太好了,以后数据列多的时候匹配取数就太简单了。


Excel中的数据建模:表间关系一线牵,何须大量公式拼数据

原创 大海 Excel到PowerBI

小勤:现在的数据分析往往涉及好多个表,比如客户表、产品表、订单表、订单明细表等等,经常要结合起来分析,每次都要把一个表的数据匹配到另一个表里才能分析,岂不要累屎?

大海:在传统数据透视表里的确是要那么干的,但到了Power Pivot里,当然就不用辣妈麻烦啦。直接拉根线连起来就把表的关系建好了,在数据分析的时候就可以直接用他们的关系了,数据根本不需要接进来。

小勤:太牛了,那好像是数据库里建表关系的方法?但我学得会吗?

大海:当然学得会,不要想得太复杂了,操作几下慢慢就理解了。

Step-01:将数据添加到数据模型(不分先后,这里先添加订单表的)

确定后,进入Power Pivot操作界面:

因为我们还要添加订单明细表和产品表,所以先点击右上角关闭窗口按钮关闭Power Pivot窗口。继续按上面的方法添加订单明细表和产品表到数据模型中,最后记得保存一下,结果如下:

Step-02:接下来我们就可以创建表间的关系了。点击【关系视图】进入创建表间关系的界面,可以看到3个表的内容分别显示在3个不同的框框里,用鼠标按住这些框框的顶部名称区域就可以按需要拖放到不同位置。

Step-03:建立表间关系

这个几个简单的数据表的关系是,订单表里的每个订单对应订单明细表里多个订单(产品)项目,订单明细里的产品可以从产品表里获取更详细的相关信息。

结果如下:

按同样的方法建立订单明细表和产品表之间的关系(注意,因为订单明细表里没有产品ID,所有这里用的是产品和产品名称建立关系,严格来说,表间关系的构建应该用ID,而不是用名称,因为有可能存在重名的情况,关于这方面的知识,后续会陆续发布),最终结果如下:

这样,3个表之间的关系就建好了。接下来就可以做各种数据透视了,操作方法跟Excel里的数据透视表几乎一模一样。只是,字段可以从各个表里直接拖拽了,而不像Excel里只能在自己一个表里玩儿。

比如,要分析各种产品类别的销量:

小勤:这样真是太好了,很多表之间其实都是有关系的,原来只能通过vlookup将另一个表的很多属性读到一起,现在只要连根线就搞定所有事情!而且,原来一个表里vlookup函数多了,那速度不是一般的卡啊。

大海:对的,通过Power Piovt这种建立表间关系的方法,不仅操作上简单,而且数据的统计速度也更快。

小勤:嗯,知道了,以后数据分析就靠它了。


欢迎关注【Excel到PowerBI】

我是大海,微软认证Excel专家,企业签约Power BI顾问

让我们一起学习,共同进步!

    本站是提供个人知识管理的网络存储空间,所有内容均由用户发布,不代表本站观点。请注意甄别内容中的联系方式、诱导购买等信息,谨防诈骗。如发现有害或侵权内容,请点击一键举报。
    转藏 分享 献花(0

    0条评论

    发表

    请遵守用户 评论公约

    类似文章 更多