如何用Excel處理200萬行以上數據？

2018.03.02

關于如何用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極客
測試目的
本次測試目的并非與其他數據分析方法對比優(yōu)劣、而是嘗試介紹一種完全基于EXCEL的本地化大數據集處理方式。
分析人員常用的大數據處理方式
本次演示的方式
這種方式的優(yōu)點
降低成本。減少工具間的切換成本，直接使用Excel作為存儲和分析工具。
展現靈活。展現端繼續(xù)使用Excel，發(fā)揮它靈活、自定義程度高的優(yōu)勢。
便于交付。其他方式得到的結果為了便于交付，還要導出為Excel，而現在整個分析流都在Excel內部完成。
結果可交互。PowerPivot相當于一個存儲了源數據的OLAP引擎，通過控制切片器等外部篩選條件，可以迅速、動態(tài)的查看結果，使用其他方法，可能需要返回分析端改變計算條件重新導出。
測試項目一：數據導入和耗時
向Excel導入大數據，有兩種方式：
PowerPivot導入，直接導入，不支持數據轉換和清洗操作。
PowerQuery導入，在導入前可以對數據做預處理。
本次使用的測試數據集共有19列，有多列需要進行格式轉換和日期提取操作，使用第一種方式，需要導入后在PowerPivot內部進行，使用方式二可以在載入前完成，很明顯的是，對于方式二，預處理步驟越多，加載時間會越長。
下圖展示了不同量級不同導入方式的耗時情況（單位：秒）
為了直接對比PowerQuery和PowerPivot的加載效率，增加了一個*號方式，這種方式不對數據做任何清洗轉換，直接加載到模型，與PowerPivot步驟相同。
現象
對比前兩行結果，PowerQuery的數據導入效率與PowerPivot不分伯仲。
PowerQuery沒有數據量的限制，而PowerPivot不到導入超過2G的文件。
清洗步驟和數據量的增多，都會顯著增加PowerQuery的導入時間，比如一億行數據，即使三個簡單的清洗步驟，用時已經超過了30分鐘
結論
PowerPivot導入方式使用的是Access連接器，受限于Access文件本身的限制，不能導入超過2G的數據，這也說明，PowerPivot數據存儲能力超過了Access。
PowerQuery是輕型ETL工具，處理大數據集性能不強（基于Excel版本的 PQ）。
如果嘗試使用Buffer函數緩存數據，會發(fā)現這個緩存過程非常漫長，實際上，Buffer函數并不適合緩存大數據集，因為無法壓縮數據，內存可能會很快爆掉。
測試項目二：文件壓縮比率
對比不同導入方式生成的文件大小，與數據源文件做比較。
影響文件壓縮比率的因素，主要是數據集本身的特征和PowerPivot引擎的性能
結論：
數量級越大，壓縮比率越高。
同一數據量級，清洗步驟越多，最終文件會越大，并且隨著數據量的增加，這種現象會越明顯。
測試項目三：簡單分析的效率
我們真正關心的內容是，Excel能否快速、高效的對大數據集開展分析。
簡單分析定義的場景是，逐月統(tǒng)計有多少位顧客發(fā)生了購買。做法是把年和月拖入透視表行字段，將CustomerKey拖入值區(qū)域，修改值匯總方式為統(tǒng)計不重復值。
測試發(fā)現，即便使用一億行數據，這個計算過程的用時也很短，小于1s。于是我增加了一點難度，加入兩個切片器對結果做交叉篩選，計算用時仍然小于1s，看來PowerPivot處理這類分析比較輕松，最終此項測試沒有計時。
測試項目四：復雜分析的效率
新客統(tǒng)計：逐月計算當月產生購買的顧客中，有多少是新客戶（第一筆購買發(fā)生在當月）
為了獲取到PowerPivot引擎的計算時間，測試在DAX Studio內完成，同時為了模擬透視表的計算結果，需要對公式做一點改動。
計算用時（毫秒）
二次運算的用時指的是首次運算結束后，不清空緩存再次執(zhí)行重復計算所花費的時間。相比第一次運算，節(jié)約時間在30%左右。原因是DAX的兩個引擎中，有一個可以緩存計算結果，被緩存的內容可以在之后被公式內部調用，也可以跨公式調用。
結合這個知識，對DAX的表達式進行優(yōu)化，可以獲得更好的性能表現，下面是新客統(tǒng)計優(yōu)化之后的寫法，我們來對比計值時間的變化。
優(yōu)化后計算用時（毫秒）
可以看出引擎的緩存起到了顯著效果，二次計算直接調用首次運算的結果，計算時間不隨數據量的增加而增加。
以一億行數據集的結果為例，對比算法優(yōu)化前后的用時：
復雜統(tǒng)計測試項目二，流失客戶統(tǒng)計
與新客的呈現方式相同，依然是逐月計算當月的流失客戶，不同的是流失客戶的定義更為復雜。
自定義一個流失天數，被判定流失的客戶需同時滿足以下兩個條件：
所有在當月之前最后一次購買的日期+自定義流失天數，落在當前時間區(qū)間內。
當月如果發(fā)生購買，第一次購買日期不能早于判定流失的日期。
流失客戶公式和計算結果
計值流如此復雜的一個公式，PowerPivot會耗時多久呢，我只用了一億行數據的文件做測試，結果是首次計算4093ms，二次計算1720ms。
說明：
1. 以上測試模擬了透視表的呈現布局，而且你可以加入切片器改變公式的上下文條件，迅速得出特定產品、特定商戶和特定促銷活動的新客戶以及流失客戶，非常方便。
2. 時間統(tǒng)計基于少量的測試結果，存在一定偶然性，僅供參考。
測試環(huán)境
電腦配置也是影響計算性能的重要因素，需要說明的是，以上進行的所有測試都基于臺式機，在做現場分享的時候，我在筆記本電腦上重新運行了一遍流失客戶公式，兩個環(huán)境的用時如下：
結合平時其他測試，我的筆記本執(zhí)行同樣的計算，用時平均在臺式機的兩倍左右。兩臺電腦的配置如下
注意：提升CPU主頻、核心數、1、2、3級緩存；內存的大小和頻率都會提升引擎的性能表現。
總結
對于本地化大數據集的分析，本文提供了一種新的可能，嚴格來講，2010年的時候你已經可以使用，只不過彼時它羽翼未豐，計算性能和穩(wěn)定性難堪大任。
而現在，你已經見識到了這套工具（PowerPivot+PowerQuery）的能力，無論大數據還是復雜運算，Excel公式和VBA已經無法望其項背。
一般說來，積累通常是好事，財富可以通過積累不斷增加、寫作能力可以通過積累不斷增強，但在某些知識領域，迭代是如此的快速和徹底，以至于底層的方法論都將被淘汰掉，過去的知識成為此刻的負擔，你最好盡快丟掉過去，擁抱未來