大家都知道Excel最大加載行為1048576行,超過的數據Excel會加載不出來。
那麼Excel如何處理百萬級以上的數據呢?那就是利用power query。
power query是Excel中的一個插件(Excel2016及以上版本自帶),是Power BI組件之一。
它主要是一個數據轉換和數據準備的引擎,它可以執行數據的提取、轉換和加載。
處理的數量級突破Excel的上線,更多的局限于電腦的性能。
加載文件首先我們将數據表放置于同一文件夾内,其中每張表有50萬條數據,3張表就是150萬條數據。
接着新建一個工作簿,依次點擊【數據】—【獲取數據】—【自文件】—【從文件夾】,選中上一步準備好的文件夾,點擊确定。
進入以下界面,點擊“轉換數據”,進入power query 編輯器界面。
界面整體如下,含有内容、名稱、類型創建日期等字段。
除了内容列(content)和名稱列(name)外,其餘列都是多餘的,我們先将其删除。
内容列Content字段“binary”表示二進制的意思,二進制數據無法直接獲取,我們需要利用M語言提取工作簿中的數據。
在“查詢編輯器”中點擊“添加列”—“添加自定義列”,輸入以下公式:
=Excel.Workbook([Content],true)
函數的第一個參數是需要轉換的二進制字段,第二個參數,用于确定是否使用原數據标題行。
然後界面中出現自定義的列,下拉之後,僅勾選“Data”,其它列不需要,點擊确定按鈕,接着程序需要處理一段時間,稍等片刻。
加載完畢後,再次下拉自定義列,取消勾選“使用原始列名作為前綴”,點擊确定,這樣所有的表頭字段全部顯現出來。
接着檢查下列的數據格式,發現銷售額等字段的數據類型為“ABC123”,表示為任意類型,這種格式在透視的時候可能無法求和,所以我們要将數字字段的類型修改下。
點擊左上方的“ABC123”,選擇合适的數據類型(這裡選擇小數)。
由于字段已經全部顯示出來,我們可以删除前面的Content字段,表名Name看需求可保留或者删除。
加載數據數據處理完畢,接着加載至Excel即可。
由于Excel最大僅支持1048576行,所以我們無法将所有數據全部加載到Excel中去,這裡采用數據透視表的方法。
點擊左上方的【關閉并上載】,選擇“關閉并上載至”:
勾選【數據透視表】,點擊确定按鈕。
接着Excel開始加載數據,加載出來的界面如下圖所示,我們驗證下數量級,一共有150萬條數據。
如果你的power query 加載出來的數據多于Excel本身,那是因為在使用自動篩選時,Excel會自動産生一個名稱_filterdatabase,它代表了自動篩選的區域,在處理的時候将這種類型的表删除即可。
演示版本為Excel2019版本,2016版本略有不同(上載的數據需要勾選“添加到數據模型”,在數據模型中插入數據透視表即可)。
以上就是關于用Excel處理百萬級以上數據的方法,分享給大家,希望對你有所幫助~
,更多精彩资讯请关注tft每日頭條,我们将持续为您更新最新资讯!