使用了大量數組公式導致Excel卡死，有沒有解決方法？

01-08

原始數據是10列*近8萬行的一維表，按要求需要生成一張交叉表，所求的值是中位數。access或者透視表都沒法求中位數，所以我只能寫公式。但是這個交叉表總共有近3000個單元格，用了近3000次數組公式，所以一運行電腦就幾近死機…有沒有更好的解決方法呢？
可能我沒說明白，所以下面附上原始數據和表格的鏈接，麻煩各位指點一下，謝謝QAQ
https://pan.baidu.com/s/1i4PCNXz
---------------------------------------------------
想了一下還是想用VBA代替數組公式實現功能，因為不是很想破壞現在整個報表草稿的結構…（好不容易搞出來的…）然而我之前只錄過幾個簡單的宏，看了一下午VBA教程也沒搞懂該怎麼做…有沒有dalao能指點一下？

題主的鏈接過期了，由於可能涉及題主公司的隱私數據

因此餅乾將

需要用到的事實表及維度表（已去敏）
餅乾做的Pivot數據模型和度量

上傳到網盤供需要的人練習

鏈接: https://pan.baidu.com/s/1bppEfIf 密碼: f3fn

本答案適合仍在為製作報表犯愁的你

Warning！Warning！Warning！

長文多圖預警！！！

題主的問題是：

原始數據是10列×79562行的一維表要交叉分組求中位數，有沒有好的方法？

作為一個專業的&<刪除&>做表的&數據分析師，我可以很明確的告訴題主：

有！不需要學編程！也不需要裝軟體！

認真看完這篇&<刪除&>安利&答案就能上手！

熟練工 20分鐘 就能做完模板！

題主這個量級的數據跑一次根本用不了 10s！

不黑不吹！

畢竟我平時跑報表行數一般是這樣的…

在正式安利之前，我們先整理一下目前提到的方式

題主想到的：數據透視表、數組公式、Access、VBA
知友推薦的：Oracle、SSRS、SPSS、SAS、Python

為什麼不推薦這些方式？

我們看一下題主提供的原始報表：

這是一張具有中國特色的混合式報表

從上到下分為 4 塊

月度放款時效中位數

時長環比下降率（月環比的衍生）

指定日期的放款時效中位數

指定月份下每日放款時效中位數

同時最右側一列是讀取另一份數據源的放款時效，並且也分了上述 4 塊

現有的方式中

數據透視表適合處理大量數據，但不支持中位數

資料庫（Oracle）、統計軟體（SPSS、SAS、Python）能算中位數，但是和 Excel 的交互差…尤其是中國特色混合式報表…

&<刪除&>順便吐槽統計軟體有 Python 沒 R 不服&

SSRS 沒用過，但用過 MSTR 的我表示 BI 軟體的部署絕對是個大工程

而題主最後的選擇

數組公式，本身不適合用於大量數據，尤其被引用的單元格是公式計算的結果

VBA，可以通過 UDF 解決，但會涉及到多維數組

或者換句話說，既然題主問的是有沒有好的方法

那麼

以餅乾對這些方式的了解

並沒有可以在半小時甚至半天內達到以下效果的

顯示明細的月份切片

重點關注的日期切片

20 分鐘的製作時間

10 秒鐘的刷新時間

關鍵是

Excel 自帶！上手容易！！

不得不說，微軟確實厲害

早早看到個性化專業化數據分析的需求

Excel → Power Query → Power Pivot → Power View → Power BI

從查詢到分析到可視化一應俱全，絕對是新手上路的不二選擇！

咳咳，那麼就跟著我一起看看專業&<刪除&>做表的&數據分析師的日常

1、準備工作

Power Pivot 內建於 Excel 2016，在開發工具中的 COM 載入項中進行啟用

Excel 2016 的安裝可以…找 IT

PS.餅乾訂閱的是 Microsoft Office 365 ProPlus

2、數據ETL

由於題主提供了網盤，並且數據是 SQL 的結果

因此省了採集和清洗的過程

不過對於使用 Excel 作為數據源，餅乾還是有些建議不得不說

首先來看下原始數據的截圖：

可以看到，題主的數據較為規整

但 10 行中有 4 行是輔助列

包括了 2 行使用 Vlookup 進行 跨表整列 的查詢

這會

極大地影響數組公式的運算

雖然公式對於 Power Pivot 的影響相對較小

但是仍然不建議使用諸如 Vlookup、Sumif 等涉及整列的聚合函數

同時餅乾建議

對規整的數據使用表格的形式儲存並修改表名

這會讓之後的引用更加直觀！

下圖是根據欄位屬性拆分後的結果：

事實表

包括新增放款各環節時效明細和各公司放款時效匯總
題主的輔助列被我刪除並歸在了 維度表 中

維度表

包括節點對應、運營狀態對應及我添加的時間維

整理好以後，習慣性地檢查了下重：

有點懵逼，不過因為不確定具體的業務邏輯加上不知道刪哪個就沒改

3、上載數據模型

整理好數據之後，我們就需要把這些數據添加到 Power Pivot 的數據模型中

由於已經是用表格的形式了，可以直接找到添加的按鈕：

點擊添加到數據模型&<刪除&>使用了水濺躍&

什麼都沒有發生…

Power Pivot 有著自己獨立的操作界面的

順便這裡說一下，數據源不一定是表，也可以是各種格式文件或者資料庫。如果需要預處理的話強烈推薦前面提到的 Power Query，儲存過程式的處理加上與 Excel 和 Power Pivot 的完美銜接簡直酸爽，這裡因為&<刪除&>懶&用不到就不展開了

接下來點左邊的管理

或者數據標籤下的 管理數據模型

進入Power Pivot 的獨立 數據模型管理界面

還是熟悉的布局
還是熟悉的標籤

可以在這裡修改格式以及 使用 DAX 函數添加自定義列

Power Pivot 的 DAX 函數 涵蓋了 Excel 原有的大部分函數及一類非常非常非常神奇的函數，這裡先賣個關子~

4、數據關聯

相信熟悉資料庫的小夥伴們對創建關係一定不會陌生

Power Pivot 也可以對上載到數據模型的表格建立關係

因為之後會涉及到篩選方向，因此要求

被關聯的兩列不能是多對多的關係

由於題主的數據結構相對比較簡單

把名稱一樣的欄位連起來之後

可以切到關係圖視圖欣賞一下自動生成的關係圖：

&<刪除&>歐洲細作吃我一矛！&

5、數據透視

回到 Excel 的界面後，像往常一樣插入一個數據透視表

不過這次是用 Power Pivot 的數據模型作為透視表的數據源

6、創建度量值

重點來了！

重到你都可以跳過第 5 步

① 選擇 Power Pivot 標籤下的新建度量值

② 根據題主的要求編輯中位數的度量值公式

Median(哪張表[哪個欄位])

由於題主的困擾的關鍵點在於

數據透視表中沒有中位數這種統計方式

……

現在有了

……

全劇終…

……

什麼？你說你還想看玉樹臨風風流倜儻英俊瀟洒才高八斗貌似潘安號稱一朵梨花壓海棠人送綽號 Excel 小王子的餅乾怎麼應對中國特色混合式報表？

……

沒問題！兩種需求一次滿足！！

7、還是數據透視

使用 Power Pivot 數據模型創建的數據透視表有一個特點：

你可以在欄位列表裡看見並選擇所有的數據
小圖標標題

深色代表是已經載入到數據模型的表

淺色代表是尚未載入到數據模型的表

點開可以看到剛才新建的度量

注意

把度量放在值這個位置

就像這樣

然後我們回到欄位列表

直接選另一張表中的欄位

接下來，就是見證奇蹟的時刻！

中位數時效根據日期拆分了！

本來是想這麼說的…

但是

日期自動聚合成月了

然後兩個月的中位數時效還一樣這點讓我很尷尬啊…

心好累…

重新來

把月份去掉並加上節點狀態和運營狀態

接下來，就是見證奇蹟的時刻！

中位數時效根據日期、節點和運營狀態拆分了！

這樣題主所關心的最主要的問題已經解決了

那麼如何更進一步製作一張複雜的中國特色混合式報表呢？

8、拆解報表

首先，我們得了解為

什麼要用 Power Pivot 製作報表？

原因很簡單

再複雜的中國特色混合式報表也可以拆解為有限個數據透視表的組合

而能用數據透視表拼出來就意味著

自！動！更！新！

多麼美妙的四個字！！

咳咳…讓我們再來回顧一下題主的報表結構

之前有分析過從上到下分別是

月度數據

月環比衍生

目標日數據

月每日數據

我們要做的就是

用 4 張數據透視表分別作出這 4 個數據

其中難度比較大的可能是月環比衍生的時長環比下降率這個度量

能堅持看到這沒睡著的勇士們啊

還記得之前說過的非常非常非常神奇的函數么？

DAX 函數 中有專門的 時間智能函數

來對應著各種環比和 MTD 數據，比如這裡的

重算([中位數時效],用日期表[日期列]的上個月的數據)

簡直無情！

做完習慣和題主的數據核對一下

完美！完全

……

不一樣！？！？！？

……

Excuse Me？

……

嚇得我趕緊再建個度量核對一下…

發現原來

題主的中位數時效是截取過 2 位小數的…

這件事告訴我們一個深刻的道理

截取位數可能會對結果造成一定的差異

尤其另一個人做的時候沒有截取…

之前查到重複值沒有把各公司放款時效匯總放進來

這裡因為最後一列的要用到所以手動忽略問題

加進 Power Pivot 數據模型並同樣建立關係和度量值：

9、美化報表

時間過去了20分鐘

幾塊數據都做完數據透視表大概是這樣的

實際做報表的時候

也可以留白的更大一些以免各種

意想不到的意外

最後把留白都隱藏再加上切片器就完成了這張報表

Fin

字幕時間打個硬廣

知乎專欄

微信訂閱號：CookieData

給你的工作效率加個油~

升級PowerPivot ,PowerBI 使用建模分析一定是你最好的選擇，關鍵PowerBI的使用可以瀏覽專欄：Power BI http://zhuanlan.zhihu.com/leigongzi

會編程的話，就自己編程處理。

不會編程的話，不妨把數據導入SPSS ，用SPSS來算，SPSS對大量數據的處理能力，遠強於Excel。

複雜的計算你可能需要這個：

Reporting Services Tutorials (SSRS)

用python和pandas處理吧，才幾萬條數據，不算多，但用EXCEL就有點吃力了。

①計算還在excel可以承受範圍之內，你又不想改

設置為手動，只需要計算一次就可以了

②如果計算的時候已經不能承受了，那還是用VBA或者例如其它人說的各種方法即可。用excel來說，最方便使用的就是VBA了~

用java 或c#配合mysql做吧

用vba一條一條的算,算完就是個固定數值.

你數組公式弄那麼多,只能是關了自動重算.

或者把刷完的數據固定住,複製,選擇性粘貼,數值,把公式刪掉,只留結果.

最先用EXCEL最基本的功能記錄數據，後來直接跳到VBA，之後發現函數公式組合也挺強大的，目前已跳坑PYTHON

用vba的數組處理

換一顆cpu

vba 或者sql

Oracle

兄弟，我想你介紹一個最簡單的方法

上4路E7，絕對不會再卡了