本文总共2686个字,阅读需9分钟,全文加载时间:2.507s,本站综合其他专栏收录该内容! 字体大小:

文章导读:相信大部分童鞋们(特别是财务人员)使用Excel来处理大量数据的时候,都会碰到这个问题 Excel真的不适合处理大量数据。特别是当数据规模达到百万级的时候,我给你一台外星人台式计算机都没用,依然卡巴斯基呀! ……各位看官请向下阅读:

相信大部分童鞋们(特别是财务人员)使用Excel来处理大量数据的时候,都会碰到这个问题

Excel真的不适合处理大量数据。特别是当数据规模达到百万级的时候,我给你一台外星人台式计算机都没用,依然卡巴斯基呀!

下面我来演示给你看,证明Excel常规功能下根本不适合处理大数据!

演示用的电脑是陪伴我7年、2010年出厂的HP笔记本,i5 4核CPU+新买的DDR3 8鸡币内存+固态硬盘(也是新的)

样例

数据源1中有“名字”、“手机号码”,数据源2中有“名字”、“签字日期”

所有的名字都独一无二,“手机号码”、“签字日期”是随机生成的。

要求

合并两表,依据:查找列A对应匹配值

该表格的数量级在1,006,600条数据,大小分别为22MB和27MB。

Excel表格功能

既然要用到条件查找,自然能想到的是vlookup或者lookup了。我们来试下对这个表格要弄多久。

在C2中输入公式

=vlookup(A2,[工作薄_手机.xlsx]Sheet1!$A:$B,2,0)

双击单元格右下角,自动向下填充整列

然后慢慢等。。。

MMP的!老子为了弄这个教程把这台老爷机都豁出去了!从23:47:15开始0%让它一直满CPU运行,但是太卡了截不了图,23:48:53才截到图,然后上床睡觉,一直听着CPU风扇不断地轰轰转响。我还以为它要自杀了。

后来02:36:49惊醒的时候,过去看了下,尼玛的,竟然还只是34%!不管了,关了!

假设它以23:47:15~02:36:49间处理了34%一样的速度运行到结束,则需要3倍的时间。

期间需要等待的耗时为:(02:36:49+24:00:00-23:47:15)*3=8小时28分42秒

Power Query

Power Query是巨硬为了让Excel能处理大量数据而专门开发的一个加载项。在Excel2016版里已经作为内置功能嵌入了(在[数据]-[获取和转换]里)

注:

至于Excel2010和2013版,可浏览以下官方网页下载并安装

https://www.microsoft.com/zh-cn/download/details.aspx?id=39379&CorrelationId=85f847dd-369e-4417-b604-6a2f3c673084

Excel2003和Excel2007? 不存在的,巨硬早就放弃它们了。

现在我们来试试用Power Query,看会快多少。

Step 1

数据→新建查询→从文件→从工作薄

Step 2

选择要作为主表的文件

Step 3

等待加载预览,大概4秒,选择对应的表,编辑

Step 4

保持默认,直接点击 开始→关闭并上载至

Step 5

仅创建链接

此时这里就有一个工作薄的查询链接了

我们还需要加载另外一个表,两个表一起才能处理。

Step 6

重复Step 1到Step 5,加载另一个表

Step 7

对第一加载的表右击,选择“合并”

Step 8

按照下图设置,并选中表1的“名字”列,表2的“名字”列。这时系统会计算匹配数量,大概等待40秒,结果出来后就可以点击确定了。

(本案例只对一列进行匹配。Power Query的合并支持匹配多列,请按着Ctrl同时点击即可)

Step 9

在弹出的查询编辑器中,点击下列按钮展开,去掉“名字”前面的勾勾,点击确认。

等待大概42秒

Step 10

把第三列拖到第二列的前面,等待了58秒

再改下表头,耗时62秒(真不懂为啥这个也要耗时。。。)

Step 11

点击【关闭并上载】

步骤好像挺多,但实际操作起来真的很简单、很快。

期间需要等待的耗时为:4''+42''+40''+58''+62''=3分26秒

而且最最最方便的是啥?它可以随时刷新!!!你源文件被改了,在刚刚生成的表格中右击→刷新!就可以更新了!其他操作都不用!

适用场景:每个月总有那么几天都要导出数据,放在同样的文件夹里,做同样的合并、整理、筛选操作。

如果用了Power Query组件,你做一次这个表格后,以后的每个月只需要将导出的数据,放在那个文件夹中,回到这个表格里点刷新即可!!!瞬间秒射完成呀!

借助Access

Access作为专业级的数据库工具,同时也是office套装之一,只是很多人都不用它。

不管是搜索还是匹配,百万级的数据在Excel都要耗费个几分钟才能查找出来的操作,在Access里几乎也是瞬间!

Step 1

打开Access,新建个空白数据库

Step 2

外部数据→从Excel导入

Step 3

在导入向导中选择需要导入的Excel源文件,点击确认,大概等待12秒

Step 4

根据向导,点击“下一步”,等待17秒。勾选“第一行包含列标题”,“完成”,然后等待大概2分48秒

Step 5

这样数据源1就导入完成了。接下来要对导入的“Sheet 1”进行重命名,不然再导入数据源2时会出现冲突。

Step 6

重复Step 1~4,继续导入数据源2。再次耗时12''+17''+12'25''(这次竟然要这么久T。T)=13分54秒

Step 7

选中任意一个数据源(导入数据库后就成了表了),创建→查询→查询设计

Step 8

将两个表格都添加进去

Step 9

拖动 表1的“名字”,到表2的“名字”,建立联系,点击“创建”

Step 10

在查询字段表设计中,按如下设置

Step 11

点击 设计→结果→运行

Step 12

等待大概13秒,出现结果。然后点击 外部数据→导出→Excel,在弹出窗口中选择合适选项。

?!

Σヽ(゚Д ゚; )ノ

一次只能输出65,000条???

这是因为我们选择了“导出数据时包含格式和布局”,这样会导致它导出来的数据默认以 97-2003.xls版本格式

所以想要导出超过65,000条数据的,一定要把这个勾勾去掉!

点确认。耗时12分13秒,完成。

期间需要等待的耗时为:12''+17''+2'48''+13'54''+13''+12’13‘’=29分24秒

总结

用Excel的常规表格功能来编辑上10万的数据都是脑瓜卡壳滴。

而Access和Power Query则各有适合场景:

1、Access虽然导入导出时间长,但起码支持Office2003和Office2007,对于条件不允许的情况下可以使用。比起Excel表格功能的8小时,29分钟还是可以接受的。

2、Power Query对大数据进行了优化,使用M语言专门对数据库查询更新,通过只呈现部分数据预览,加快处理时间,用户体验超快,而且每个步骤都能撤销(只需点击“应用步骤”栏的叉叉就ok)。缺点就是不支持Office2010之前的版本,而office2010和office2013则需要下载组件安装(不大,才16M)。

原创不易,转载请保留出处。

以上内容由优质教程资源合作伙伴 “鲸鱼办公” 整理编辑,如果对您有帮助欢迎转发分享!

你可能对这些文章感兴趣:

发表评论

您的电子邮箱地址不会被公开。 必填项已用*标注