您当前的位置:首页 > 文本与office

狂甩vlookup几条街,这3种EXCEL多表关联的方法了解一下

时间:07-03来源:作者:点击数:

EXCEL是我们工作中经常用到的工具,虽然功能很强大,但是缺点也不少,最让人苦恼的莫过于它的性能问题了,只要数据量一大,便跑不动了,让我们这些打工人苦不堪言。在讨论如何解决这个问题之前,我们先来看一个真实的案例:

一、案例:

小王是一家医药公司的销售助理,平时主要的工作是协助销售负责人整理公司的销售报表,然后定时把数据推送给公司的管理层。小王所需要的数据源主要来自于ERP系统,导出数据后,然后再利用EXCEL对数据源进行加工和分析。数据源里大概有400多个产品品类,其中包含了1500多个客户和150多个业务员,每个月的数据量接近一百万行。小王还需从部门的维度对数据进行整理,公司主要有3个大的销售部,但是又包含2个小部,17个大区,30多个小区,除此之外,还要区分连锁、乡镇等等。先不说这样的报表有多复杂,光是这数据量就已经让EXCEL难以负荷了,而且这些表格还要利用公式进行彼此关联,只要一打开就会卡死,让小王苦不堪言。

二、业务场景

1、痛点/需求

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

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

第二个是产品的维度表:

第三个是地区的维度表:

在打开这个EXCEL文件的时候,就已经表现的特别明显了,由于汽车销售数据源的行数是100万行,EXCEL打开的速度非常慢:

我们再尝试一下对这几个报表进行关联,EXCEL的多表关联最常用的方法是利用vlookup把两个表格连接起来,每关联一个字段,就要多写一次公式,特别麻烦,如果数据大的时候,就会变得非常卡,有时候根本就跑不动,让人崩溃。尝试了一会之后,数据君最后还是放弃了,因为这太不科学了,还是找其他更好的方法吧。

2、解决过程

通过这个案例,数据君认为要解决上述的痛点问题,至少要面临两个问题,第一个是数据量的问题,第二个是多表关联的问题。通过对目前所有市面上的工具进行比较,数据君找到了三种比较适用的方法,至于哪种方法更适合自己,这个答案就交给明智的你自己去选择吧。

1)数据库

运用数据库的语句做多表关联是一种不错的方式,学过数据库的人都知道,sql语句中提供了多种连接的方式,包括左连接(left join )、右连接(right join)、外连接(full join)和内连接(inner join)。我们利用数据库软件尝试做一下多表关联,首先在数据库中设计好表结构,并把3个表格导进数据库中:

然后新建一个查询,写好语句,把这三个表格进行关联,最后点击执行,便可以得到一个新的关联表:

但这种方法仅适用于熟悉数据库语句的人,如果您对数据库语句了解的不多,此方法可以略过。

2)power pivot

是EXCEL里一个非常厉害的建模组件,通过这个功能,您也可以快速完成多个报表关联的操作,您可以在EXCEL的工具栏上快速找到power pivot的菜单界面(我的EXCEL是2019版本):

进入到power pivot的界面后,点击“从其他源”,选择EXCEL导入,分别把3个数据源导入到power pivot里:

数据源成功导入后,点击“关系图视图”,这时可以看到3个表格是没有作任何关联的,但是我们可以通过连线的方式对这几个表格进行关联:

完成关联后,点击透视表,可以发现这三个表格已经是可以自由进行关联查询的了:

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

3)智分析

智分析是一款云端saas大数据分析工具,具有非常强悍的数据处理能力,无论是做数据清洗,还是做数据可视化,智分析都能轻松应付。智分析处理EXCEL文件非常方便,可以说就是专门为EXCEL用户打造的,只要你会使用EXCEL,就能轻松上手。针对前面介绍的两种多表关联方法,我更倾向于建议大家使用智分析去做多表关联,因为无论从处理性能,还是从操作上来说,智分析都更胜一筹。下面给大家介绍一下如何通过智分析做多表关联:

数据导入

首先把3个需要关联的EXCEL文件导入到智分析的系统里,导入成功之后,可以在数据连接的界面里找到这3个文件:

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

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

双击维度表后,两份数据源便会自动进行关联,这时可以对关联关系进行设置,例如左连接、右连接等等,这里我们设置为左连接:

同样的操作,点击另外一份维度表,并与数据源进行关联,这时便把3个数据源关联好了:

由于关联后的报表存在重复的字段,我们可以对这些重复的字段进行可见性的设置:

设置完成后,我们可以对数据进行预览,这时可以看到3个文件已经被整合成1个文件了,通过这个数据集,你便可以继续去做其他的数据分析了:

三、总结

通过上述介绍的案例,我们可以看出这几个方法都是解决多表关联很好的方法,前两种方法都在一定程度上解决了EXCEL的缺点问题,但是也会存在一定的技术性门槛,小白不是那么容易掌握。第三个方法的解决过程最为彻底,即使你是一个数据小白,也能快速掌握并且操作完成整个过程,因为所有步骤都是在可视化界面进行操作的,无需任何编程语句,而且数据量也不会受到限制,是目前解决多表关联的最好方法了。

方便获取更多学习、工作、生活信息请关注本站微信公众号城东书院 微信服务号城东书院 微信订阅号
推荐内容
相关内容
栏目更新
栏目热门