放弃vlookup吧,这3种EXCEL多表关联方法“强得很”

Smartbi大数据分析平台

功能覆盖数据分析全流程,一站式平台,产品功能可组合!

申请试用

Smartbi大数据分析平台

功能覆盖数据分析全流程,一站式平台,产品功能可组合! 申请试用

放弃vlookup吧,这3种EXCEL多表关联方法“强得很”

相信很多人对Excel都是又爱又恨的,这个我们日常工作中频繁用到的工具,功能虽然强大,但缺点也很明显。如在性能方面,当数据量较大时Excel运行就会变得非常缓慢,严重降低了工作效率。在讨论如何解决这个问题之前,我们先来看一个真实的案例:

 

一、案例:

小杨是一家医疗器材公司的销售助理,整理公司的销售报表并定时将数据发送给领导是他的日常工作之一,但往往小杨要在这个工作上花费很多时间,导致其他工作没法按时完成,因此每周都要加班,小杨苦不堪言。小杨公司的数据源存储在ERP系统,将数据导入Excel,再对数据源进行加工、整理、分析。但这个数据源不仅数据量巨大而且维度非常多,十分复杂。如产品品类就有400多个,销售150多位、客户更是多达了1500多位,每个月的数据量接近可以到达一百万行。不仅如此,小杨还要按维度整理数据,如公司的销售部主要有3个,但里面又包含有2个小部,部门里又要按区域划分。分完大区、小区,还要区分连锁、乡镇等。除了有巨大的数据量和复杂的维度,小杨还需要把这些报表运用公式进行关联。

 

vlookup.png 

 

二、业务场景

1、痛点/需求

为了对上述的案例进行场景模拟,并找到最优的解决方案,我找来了3个数据源文件,先看一下在Excel里做多表关联会遇到什么问题:

 

大数据.png 

 

第一个是汽车销售数据源,数据量是100万行:


汽车销售.png

 

第二个是产品的维度表:


产品维度.png 

 

第三个是地区的维度表:


地区维度.png

 

由于报表中光汽车销售的数据源就多达100万行,不用说运行,只是打开Excel都已经带不动了,非常缓慢。

 

excel.png 

 

我们再尝试一下对这几个报表进行关联,Excel的多表关联最常用的方法是利用vlookup把两个表格连接起来,每关联一个字段,就要多写一次公式,特别麻烦,如果数据大的时候,就会变得非常卡,有时候根本就跑不动,让人崩溃。一波操作后,小编选择了放弃,还是另寻他法吧!

 

报表1.png 

 

2、解决过程

根据上面的案例,小编认为如果想要解决上述Excel的问题,可以从2个方面进行考虑。一是数据量,二是多表关联。经过对目前市场上知名度较高的工具进行多番尝试后,终于给小编找到了3种比较适合的方法,至于哪种方法更适合自己,这个答案就交给明智的你自己去选择吧。

 

1)数据库

运用数据库的语句做多表关联是一种不错的方式,学过数据库的人都知道,sql语句中提供了多种连接的方式,如左连接、右连接、外连接、内连接。先在我们来实操一下,打开数据库软件,在数据库中将表结构设计好,然后把这3个表格都导进数据库中:

 

报表2.png 

 

接着建一个查询,将语句写好,然后再将这三个表格进行关联,最后一步——点击“执行”,就能够得到一个新的关联表:

 

报表3.png 

 

这种方法是有一定的门槛,要求有一定的数据库语句基础,如果完全没有基础的话,可以忽略此方法。

 

2)Powerpivot

这是Excel中的建模组件,功能非常强大,利用这个功能也能够协助你完成报表关联。如何快速找到这个菜单界面?操作方式如下,查看Excel工具栏上:

 

报表4.png 

 

进入到powerpivot的界面后,点击“从其他源”,选择Excel导入,再分别将这3个数据源导入:

 

报表5.png 

 

待数据源导入后,鼠标点击“关系图视图”,观察可发现这3个表格没有任何关联,我们可以运用连线方式对这几个表格进行关联:

 

报表6.png 

 

关联完成之后,鼠标点击“透视表”,可见3个表格已经关联起来了,且能够自由关联查询:

 

报表7.png

 

但是用powerpivot也有一个很大的缺点,那就是数据性能太差劲,如果数据量太大,很容易会被卡死。

 

3)智分析

智分析的数据处理能力非常强大,数据清洗、数据可视化等都能完美应对,这个大数据分析工具属于云端saas。智分析处理Excel文件非常方便,对Excel用户非常友好。综上所述,做多表关联的话小编更建议大家选择智分析,与前2种多表关联方法相比,智分析的处理性能、操作步骤等方面都是更具优势的。下面给大家介绍一下如何通过智分析做多表关联:

 

数据导入

老规矩,先将3个报表导入到智分析系统,导入完成后,在数据连接的界面里找到这3个文件:

 

报表8.png 

 

然后打开数据准备里的自助数据集:

 

报表9.png 

 

进入到自助数据集的界面后,在数据连接里找到您的数据源,点击数据源后,便可以刷新出明细数据:

 

报表10.png 

 

然后用鼠标双击维度表,刚导入的两份报表就会实现自动关联,这时可以对关联关系进行设置,例如左连接、右连接等等,这里我们设置为左连接:

 

报表11.png 

 

重复以上操作,鼠标点击剩下的那个维度表,也与数据源进行关联,这样3个数据源就已经关联好了:

 

报表12.png 

 

现在需要对关联后报表中存在的重复字段进行处理,需要对其进行可见性的设置:

 

报表13.png 

 

设置完成后,我们可以对数据进行预览,现在3个报表已经合并为同1个报表了,有了这个数据集,我们就可以去做其他的数据分析了:

 

报表14.png

 

三、总结

怎么样?这3个方法是不是还是挺实用的,前2个方法相对来说对小白不是太友好,需要一定的技术基础。所以小编还是比较推荐第3个方法,因为只要你会用Excel,就能很快上手,轻松完成操作。另外,第3个方法的操作步骤完全不需要打代码,且都在可视化得界面进行操作,能承受的数据量也能大,算得上是目前解决多表关联的最好方法了。


申请试用


试用申请

更聪明的大数据分析软件,快速挖掘企业数据价值!

新一代商业智能BI工具

2024年度精选《思迈特制造行业BI最佳实践合集》

立即下载
Copyright© 广州思迈特软件有限公司  粤ICP备11104361号

电话咨询

售前咨询
400-878-3819 转1

售后咨询
400-878-3819 转2
服务时间:工作日9:00-18:00

sales邮箱

商务咨询请联系邮箱

邮箱地址:sales@smartbi.com.cn