数据统计实时最新(百万到亿级数据)(1)

大家好,我是dk。这是Excel神器PowerQuery实战入门系列的第3篇。往后,我会更新更多关于PQ的相关内容,有兴趣的小伙伴可以关注下。

众所周知,Excel2003版最大行数是65536行,到了2007及之后的版本最大行数是1048576行,也就是说1百万的数据,已经是Excel处理数据的权限了。事实上,Excel在处理30万左右的数据时,用公式或操作都会很慢,还经常会卡机。

不过,有了Power Query,几百万的数据,也能实现统计和查询。(据官方介绍,Power Query可以处理上亿的数据,前提是你的内存足够大。)下面举个例子:导入一个约为200万行的数据,进行统计。

大家可以跟着我一起操作:

Step1:导入TXT。

新建一个空白工作簿,点选【数据】选项卡中的【从文本/CSV】(见下图) ,点击后会弹出【导入数据】对话框,从对话框中找出文件所在路径,选中【百万到亿级数据,快速统计查询.txt】,进入【导航器】对话框。

数据统计实时最新(百万到亿级数据)(2)

Step2:设置分隔符。

【导航器】中,我们可以预览文件数据,Power Query会自动检测文件的格式,分隔符,数据类型。这些Power Query都自动检测完成,少数情况需要自己修改下分隔符,然后点击【编辑】进入Power Query。

数据统计实时最新(百万到亿级数据)(3)

Step3:加载到数据。

数据加载到Power Query后,我们就可以进行一些简单的查询,比如,要查询数据中,尺子有多少笔,数量总共是多少?选择对应列【产品】筛选尺子,根据弹出的对话框,尺子的左边打钩,按确定结束。(界面与操作跟Excel上是一样的)。

数据统计实时最新(百万到亿级数据)(4)

结果返回了列【产品】是尺子的所有数据,选中列【数量】,点选【转换】选项卡中的>>【统计信息】>>【值计数】>>返回结果:574253,这代表了尺子的行数。

数据统计实时最新(百万到亿级数据)(5)

如果要知道尺子的总数量,在右边【查询设置】>>点选【筛选的行】返回上一步的操作>>选中【数量】这一列,点选【转换】选项卡中的>>【统计信息】>>【求和】>>返回结果:4598525,这代表了尺子的数量之和。

数据统计实时最新(百万到亿级数据)(6)

Step4:删除步骤。

上面的查询只适用于少量的查询统计,如果查询统计的项目多,并不建议用这个方法。这时候我们可以借助数据透视表来查询统计。首先,把多余的步骤删除,点击【筛选的行】>>【计算的总和】>>【计算的计数】前面的X,删除掉,返回未筛选前的数据。

数据统计实时最新(百万到亿级数据)(7)

Step5:加载到数据透视表。

点选左上角的【文件】>>【关闭并上载至】>>【数据透视表】>>按确定结束。

数据统计实时最新(百万到亿级数据)(8)

Step6:透视表操作。

数据加载200万行后,会在左面出现空白的透视表区域,右边数据透视表字段列表,可以拖动和设置。大家自己尝试一下就好。

数据统计实时最新(百万到亿级数据)(9)


如果你喜欢本文,请转发分享一下,想要获得更多信息,关注我吧!,