利用Access進行數據分析通常不是因為Excel不夠用，因為，現在Excel能分析的數據遠不止你所能看到的一個表里的1048576行——因為，現在Excel有了PowerQuery和PowerPivot兩項極其強大的功能：

一、逆天給力的PowerQuery

二、引爆Excel數據分析、20年來最大革新的PowerPivot

沒有嘗試前先不要說Excel用不了

來看看大神用PowerQuery和PowerPivot做的1億行的數據測試：

Excel一億行數據分析實踐

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

本次演示的方式

這種方式的優點

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

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

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

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

數據導入和耗時

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

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

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

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

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

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

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

結論

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

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

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

簡單分析的效率

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

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

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

復雜分析的效率

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

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

計算用時（毫秒）

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

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

優化后計算用時（毫秒）

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

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

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

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

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

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

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

流失客戶公式和計算結果

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

說明：

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

2.時間統計基于少量的測試結果，存在一定偶然性，僅供參考。

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

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

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

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

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

建議

從提問的方式來看，你本身應該就一直在用Excel做數據分析，只是現在當數據量變得更大了，如果真是這樣，那么你完全可以去嘗試使用新功能PowerQuery和PowerPivot來解決，而不需要考慮Access。

【私信“材料”直接下載系列訓練材料】

• 【Excel必備基礎小動畫】

• 【60+函數匯總案例】

• 【數據透視基礎精選10篇】

• 【PowerQuery入門到實戰80篇】

• 【PowerPivot基礎精選15篇】

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

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

【您的關注和轉發鑄就我前行的動力！謝謝支持！】