PowerBI之家

關於如何用Excel處理200萬行以上數據問題，現在已經完全沒有壓力了，雖然Excel工作表本身只支持1048576行數據，而且如果真的在一個表裡數據導到100萬行以上，這個表基本就跑不動了。

但是，隨著Excel2016的內置新功能Power Query、Power Pivot等（Excel2010或Excel2013可到微軟官方下載相應的插件）的推出，這個問題已經得到很好的解決。

理論上，Power Query和Power Pivot支持的數據行數是沒有限制的，但Power Pivot僅支持2G以下的數據文件。

大神高飛曾就Power Query和Power Pivot對大數據支持做過相關測試，情況如下：

Excel 一億行數據分析實踐（總結篇）

高飛 PowerBI極客

測試目的

本次測試目的並非與其他數據分析方法對比優劣、而是嘗試介紹一種完全基於EXCEL的本地化大數據集處理方式。

分析人員常用的大數據處理方式

本次演示的方式

這種方式的優點

• 降低成本。減少工具間的切換成本，直接使用Excel作為存儲和分析工具。

• 展現靈活。展現端繼續使用Excel，發揮它靈活、自定義程度高的優勢。

• 便於交付。其他方式得到的結果為了便於交付，還要導出為Excel，而現在整個分析流都在Excel內部完成。

• 結果可交互。PowerPivot相當於一個存儲了源數據的OLAP引擎，通過控制切片器等外部篩選條件，可以迅速、動態的查看結果，使用其他方法，可能需要返回分析端改變計算條件重新導出。

測試項目一：數據導入和耗時

向Excel導入大數據，有兩種方式：

1. PowerPivot導入，直接導入，不支持數據轉換和清洗操作。

2. PowerQuery導入，在導入前可以對數據做預處理。

本次使用的測試數據集共有19列，有多列需要進行格式轉換和日期提取操作，使用第一種方式，需要導入後在PowerPivot內部進行，使用方式二可以在載入前完成，很明顯的是，對於方式二，預處理步驟越多，加載時間會越長。

下圖展示了不同量級不同導入方式的耗時情況（單位：秒）

為了直接對比PowerQuery和PowerPivot的加載效率，增加了一個*號方式，這種方式不對數據做任何清洗轉換，直接加載到模型，與PowerPivot步驟相同。

現象

• 對比前兩行結果，PowerQuery的數據導入效率與PowerPivot不分伯仲。

• PowerQuery沒有數據量的限制，而PowerPivot不到導入超過2G的文件。

• 清洗步驟和數據量的增多，都會顯著增加PowerQuery的導入時間，比如一億行數據，即使三個簡單的清洗步驟，用時已經超過了30分鐘

結論

1. PowerPivot導入方式使用的是Access連接器，受限於Access文件本身的限制，不能導入超過2G的數據，這也說明，PowerPivot數據存儲能力超過了Access。

2. PowerQuery是輕型ETL工具，處理大數據集性能不強（基於Excel版本的 PQ）。

如果嘗試使用Buffer函數緩存數據，會發現這個緩存過程非常漫長，實際上，Buffer函數並不適合緩存大數據集，因為無法壓縮數據，內存可能會很快爆掉。

測試項目二：文件壓縮比率

對比不同導入方式生成的文件大小，與數據源文件做比較。

影響文件壓縮比率的因素，主要是數據集本身的特徵和PowerPivot引擎的性能

結論：

1. 數量級越大，壓縮比率越高。

2. 同一數據量級，清洗步驟越多，最終文件會越大，並且隨著數據量的增加，這種現象會越明顯。

測試項目三：簡單分析的效率

我們真正關心的內容是，Excel能否快速、高效的對大數據集開展分析。

簡單分析定義的場景是，逐月統計有多少位顧客發生了購買。做法是把年和月拖入透視錶行字段，將CustomerKey拖入值區域，修改值彙總方式為統計不重複值。

測試發現，即便使用一億行數據，這個計算過程的用時也很短，小於1s。於是我增加了一點難度，加入兩個切片器對結果做交叉篩選，計算用時仍然小於1s，看來PowerPivot處理這類分析比較輕鬆

，最終此項測試沒有計時。

測試項目四：複雜分析的效率

新客統計：逐月計算當月產生購買的顧客中，有多少是新客戶（第一筆購買發生在當月）

為了獲取到PowerPivot引擎的計算時間，測試在DAX Studio內完成，同時為了模擬透視表的計算結果，需要對公式做一點改動。

計算用時（毫秒）

二次運算的用時指的是首次運算結束後，不清空緩存再次執行重複計算所花費的時間。相比第一次運算，節約時間在30%左右。原因是DAX的兩個引擎中，有一個可以緩存計算結果，被緩存的內容可以在之後被公式內部調用，也可以跨公式調用。

結合這個知識，對DAX的表達式進行優化，可以獲得更好的性能表現，下面是新客統計優化之後的寫法，我們來對比計值時間的變化。

優化後計算用時（毫秒）

可以看出引擎的緩存起到了顯著效果，二次計算直接調用首次運算的結果，計算時間不隨數據量的增加而增加。

以一億行數據集的結果為例，對比算法優化前後的用時：

複雜統計測試項目二，流失客戶統計

與新客的呈現方式相同，依然是逐月計算當月的流失客戶，不同的是流失客戶的定義更為複雜。

自定義一個流失天數，被判定流失的客戶需同時滿足以下兩個條件：

1. 所有在當月之前最後一次購買的日期+自定義流失天數，落在當前時間區間內。

2. 當月如果發生購買，第一次購買日期不能早於判定流失的日期。

流失客戶公式和計算結果

計值流如此複雜的一個公式，PowerPivot會耗時多久呢，我只用了一億行數據的文件做測試，結果是首次計算4093ms，二次計算1720ms。

說明：

1. 以上測試模擬了透視表的呈現佈局，而且你可以加入切片器改變公式的上下文條件，迅速得出特定產品、特定商戶和特定促銷活動的新客戶以及流失客戶，非常方便。

測試環境

電腦配置也是影響計算性能的重要因素，需要說明的是，以上進行的所有測試都基於臺式機，在做現場分享的時候，我在筆記本電腦上重新運行了一遍流失客戶公式，兩個環境的用時如下：

結合平時其他測試，我的筆記本執行同樣的計算，用時平均在臺式機的兩倍左右。兩臺電腦的配置如下

注意：提升CPU主頻、核心數、1、2、3級緩存；內存的大小和頻率都會提升引擎的性能表現。

總結

對於本地化大數據集的分析，本文提供了一種新的可能，嚴格來講，2010年的時候你已經可以使用，只不過彼時它羽翼未豐，計算性能和穩定性難堪大任。

而現在，你已經見識到了這套工具（PowerPivot+PowerQuery）的能力，無論大數據還是複雜運算，Excel公式和VBA已經無法望其項背。

一般說來，積累通常是好事，財富可以通過積累不斷增加、寫作能力可以通過積累不斷增強，但在某些知識領域，迭代是如此的快速和徹底，以至於底層的方法論都將被淘汰掉，過去的知識成為此刻的負擔，你最好儘快丟掉過去，擁抱未來

從上面可以看出，使用Excel處理200萬行的數據完全沒有問題，那麼，Power Query和Power Pivot怎麼用呢？可以參考以下系列內容：

Power Query從入門到實戰80篇

Power Pivot基礎及Dax入門15篇

更多精彩內容，敬請關注【Excel到PowerBI】

私信我即可下載60+Excel函數、數據透視10篇及Power系列功能95篇彙總訓練材料

我是大海，微軟認證Excel專家，企業簽約Power BI顧問

讓我們一起學習，共同進步！

Excel到PowerBI

鑑於本人就是學的此類專業，所以跟大家分享一下這些知識。常用辦公軟件無非就是WPS或者office系列，他們都有自己相應的組件，可結合使用。當然軟件版本越高支持的的操作就越多，也會很方便，但是目前很多人都是習慣用自己熟悉的版本，所以當處理大量數據時，首先要考慮你的版本能否支持你的操作，並且數據加載情況和用戶自己的電腦也是有關係的。對於大量的數據，個人比較推薦用數據庫（或者Excel中的【獲取外部數據】）進行操作。注：access就是數據庫的一種

如果你本人只會使用Excel，那麼Excel裡面有很多操作可以利用函數和菜單（排序、查找、條件格式等等）完成，下面我簡單介紹一些函數：

更多優質內容，請持續關注鎂客網~~

鎂客網

EXCEL是輕量級的數據處理軟件，但可以處理200萬行以上的項目，一般是用POWER QUERY或POWER PIVOT來處理。EXCEL文件表格在中超過200萬行，這種情況下的數據來源基本都是從數據庫中導出來的，既然已經有了數據庫軟件系統，最好還是用數據庫來處理這些數據，這應該是最合理的處理方法。

Office技巧共享

excel單個最大能支持的數據行是100萬多點。數據量太大，excel會加載很慢，查詢和檢索也不方便。

比較常用的解決方法是將excel和access組合起來使用，access負責數據存取，excel負責展示。因為都是office家族，兩者的兼容不錯，操作也很簡單。

如果只想要excel，可以考慮按照數據類型分表頁和文件，注意命名規劃，後期數據查詢，也可以借用query 和跨表頁取數公式之類的。

康愉子

EXCEL正常用到幾十萬行了，沒有出現超限提示呀！

恪守三戒

據我所知，Excel似乎最多隻能允許65535行。

日常工作也不需要這麼多，畢竟數據量大了，運行速度太慢了。

Excel本身只能進行一些簡單的數據處理，如果有大量的數據，為什麼不直接使用數據庫呢？

關鍵字: Excel 以上 科技