大家都知道Excel最大加载行为1048576行,超过的数据Excel会加载不出来。

excel怎么处理500万行的数据(Excel如何处理一百万以上的数据)(1)

那么Excel如何处理百万级以上的数据呢?那就是利用power query

power query是Excel中的一个插件(Excel2016及以上版本自带),是Power BI组件之一。

excel怎么处理500万行的数据(Excel如何处理一百万以上的数据)(2)

它主要是一个数据转换和数据准备的引擎,它可以执行数据的提取、转换和加载

处理的数量级突破Excel的上线,更多的局限于电脑的性能。

加载文件

首先我们将数据表放置于同一文件夹内,其中每张表有50万条数据,3张表就是150万条数据。

excel怎么处理500万行的数据(Excel如何处理一百万以上的数据)(3)

接着新建一个工作簿,依次点击【数据】—【获取数据】—【自文件】—【从文件夹】,选中上一步准备好的文件夹,点击确定。

excel怎么处理500万行的数据(Excel如何处理一百万以上的数据)(4)

进入以下界面,点击“转换数据”,进入power query 编辑器界面。

excel怎么处理500万行的数据(Excel如何处理一百万以上的数据)(5)

界面整体如下,含有内容、名称、类型创建日期等字段。

excel怎么处理500万行的数据(Excel如何处理一百万以上的数据)(6)

除了内容列(content)和名称列(name)外,其余列都是多余的,我们先将其删除。

excel怎么处理500万行的数据(Excel如何处理一百万以上的数据)(7)

转换数据

内容列Content字段“binary”表示二进制的意思,二进制数据无法直接获取,我们需要利用M语言提取工作簿中的数据。

在“查询编辑器”中点击“添加列”—“添加自定义列”,输入以下公式:

=Excel.Workbook([Content],true)

excel怎么处理500万行的数据(Excel如何处理一百万以上的数据)(8)

函数的第一个参数是需要转换的二进制字段,第二个参数,用于确定是否使用原数据标题行。

然后界面中出现自定义的列,下拉之后,仅勾选“Data”,其它列不需要,点击确定按钮,接着程序需要处理一段时间,稍等片刻。

excel怎么处理500万行的数据(Excel如何处理一百万以上的数据)(9)

加载完毕后,再次下拉自定义列,取消勾选“使用原始列名作为前缀”,点击确定,这样所有的表头字段全部显现出来。

excel怎么处理500万行的数据(Excel如何处理一百万以上的数据)(10)

检查数据格式

接着检查下列的数据格式,发现销售额等字段的数据类型为“ABC123”,表示为任意类型,这种格式在透视的时候可能无法求和,所以我们要将数字字段的类型修改下。

点击左上方的“ABC123”,选择合适的数据类型(这里选择小数)。

excel怎么处理500万行的数据(Excel如何处理一百万以上的数据)(11)

由于字段已经全部显示出来,我们可以删除前面的Content字段,表名Name看需求可保留或者删除。

加载数据

数据处理完毕,接着加载至Excel即可。

由于Excel最大仅支持1048576行,所以我们无法将所有数据全部加载到Excel中去,这里采用数据透视表的方法。

点击左上方的【关闭并上载】,选择“关闭并上载至”:

excel怎么处理500万行的数据(Excel如何处理一百万以上的数据)(12)

勾选【数据透视表】,点击确定按钮。

excel怎么处理500万行的数据(Excel如何处理一百万以上的数据)(13)

接着Excel开始加载数据,加载出来的界面如下图所示,我们验证下数量级,一共有150万条数据。

excel怎么处理500万行的数据(Excel如何处理一百万以上的数据)(14)

如果你的power query 加载出来的数据多于Excel本身,那是因为在使用自动筛选时,Excel会自动产生一个名称_filterdatabase,它代表了自动筛选的区域,在处理的时候将这种类型的表删除即可。

excel怎么处理500万行的数据(Excel如何处理一百万以上的数据)(15)

小结

演示版本为Excel2019版本,2016版本略有不同(上载的数据需要勾选“添加到数据模型”,在数据模型中插入数据透视表即可)。

以上就是关于用Excel处理百万级以上数据的方法,分享给大家,希望对你有所帮助~

,