這樣用Excel，才能讓你的效率翻倍！

Excel是一款功能強大，操作靈活的工具，除了自己學習探索外，參考別人的經驗和方法也是十分有效的提高途徑。一起來看達人是如何用Excel施展魔法的吧~！

以下內容轉自何明科在問題「Excel 有哪些可能需要熟練掌握而很多人不會的技能？」下的回答。

在諮詢公司、VC/PE/Hedge Fund等基金混跡多年，一直靠著Excel的各種技巧安身立命和升職加薪。可能是因為程序員出身的原因，在學會以上各個答案提到的裝逼炫酷圖表、快捷鍵和一些略複雜的函數（VLookup等）之外，總是希望從更深的層次去探索Excel及各類Office軟體，直到遇到了數組函數和VBA編程。這些技能一旦掌握能將工作效率提高數倍甚至是十倍以上，然而周圍卻很少有人掌握。

數組函數和VBA編程，簡直就是為程序員而生的：

數組函數充滿了資料庫的思維，而VBA本身就是徹頭徹尾的編程，再加之各種介面，能夠將Office各套軟體以及OS下的各種功能完美結合在一起。因為Excel+VBA是圖靈完備的，最後輔以Excel簡單高效的數據呈現界面。

在我的心目中，Excel+數組函數+VBA，簡直就是網頁前端+客戶端+後台程序+資料庫。感覺學會了這些，某種意義上就是成為了Full Stack Developer （全棧工程師），各互聯網公司夢寐以求想招到的人。

數組函數往往會和Index、Indirect及Address等地址相關和數據塊相關的函數搭配使用，如果不考慮效率的話，基本可以替代各種SQL語句了。

數組函數之案例1：計算某類產品的總價值

計算AA產品的總價值，替代select sum(產品數量x產品單價) from ... where 產品編號=『AA』

{=SUM(IF(($B$4:$B$8="AA"),($C$4:$C$8)*($D$4:$D$8),0))}

如果沒有數組函數，使用sumif等函數也會很複雜。

數組函數之案例2：挑選不重複的值並計算總和

左邊的白色區域是原始數據，右邊的彩色區域使用了數組函數的輸出區域。數組函數實現了兩大功能：

· 黃色區域：將不重複的name+month篩選出來。

· 藍色區域：替代了select sum(tot) from ... group by name, month，將name+month對應的tot進行加總。

如果沒有數組函數，只能使用Pivot Table等複雜方式，不僅程序開銷很大，而且還不夠靈活。

數組函數之案例3：

這是一個幫助某國際家用電器廠商預測各家電品類市場潛力及規模的項目，從2005-2024年。一般的Excel函數只能解決兩維的問題，而這次客戶提出了這個變態的n維需求，需要精確到年份、電器品類、渠道類型、用戶高中低端以及城市級別共5個維度來查看市場規模及潛力。簡單說就是利用下面這個表格隨時查看指定維度下的某年份的市場潛力及規模。

通過使用數組函數建模輕鬆實現如下功能，只要在指定的區域內選擇相關值，就能計算值所需的市場規模及潛力，等於使用了SQL語句：select * from table where 條件1=A1 and 條件2=A2 and 條件3=A3……（共5個條件）

複雜的數組函數編碼如下

=SUM(((Summary_Market!$S$71:$S$308=$A7)+(Summary_Market!$S$71:$S$308=$V7)+(Summary_Market!$S$71:$S$308=$AA7)+($A7="")>0)*((Summary_Market!$T$71:$T$308=$B7)+(Summary_Market!$T$71:$T$308=$W7)+(Summary_Market!$T$71:$T$308=$AB7)+($B7="")>0)*((Summary_Market!$U$71:$U$308=$C7)+(Summary_Market!$U$71:$U$308=$X7)+(Summary_Market!$U$71:$U$308=$AC7)+($C7="")>0)*((Summary_Market!$V$71:$V$308=$D7)+(Summary_Market!$V$71:$V$308=$Y7)+(Summary_Market!$V$71:$V$308=$AD7)+($D7="")>0)*(Summary_Market!BB$71:BB$308))

整個模型的界面及複雜的數組函數如下圖，左邊部分的界面其實就是圖形化的SQL語句。這個模型被該客戶及我們諮詢公司使用了不下5年，部分依賴於其超強的靈活性。

首先不要被「編程」二字嚇跑，因為VBA不會編程也可以進行，通過錄製宏的方式就可以搞定。錄製宏的訣竅見下圖：

如果真要升級成為VBA編程達人，還是需要自己學習和自己編寫VBA程序。下面列舉若干自己親手編寫的若干Excel+VBA項目。

VBA編程之案例1：自動列印

剛進職場的新人，只要爸爸不是李剛，基本都做過影帝影后（影=印，各種複印列印的體力勞動）。特別是諮詢投行服務行業，在某次給客戶的大彙報或者大忽悠會議之前，花數小時或者整晚來列印數個文件，並不是天方夜譚。而且這件事情是對著同樣一堆不斷修改的文件，會經常不斷重複發生。

我加入BCG的第一個項目，就是幫助某大型企業從上到下設計KPI體系並實施。從上到下涉及到幾十個部門，大概有100多張的KPI表格需要完成，這些KPI表格分佈在各個Excel文件里。我們4個諮詢顧問的任務：

①設定好KPI的基本格式，然後每個顧問負責幾個部門，在Excel里不斷修改KPI表格，列印出來後去各個當事人及其領導那裡討論並修改；

②每周把所有的Excel文件中的KPI表格歸集在一起，按順序分部門列印出來，並需要多份，找負責該項目的HR頭兒彙報進度和情況。

這裡面有個費時費力的環節，每周需要在多個Excel文件中找出目標Worksheet，然後選定合適的區域作為輸出的表格，按照一定的格式和一定的順序，列印出這100多張表格。之前我們全是憑藉人力，每周由一個Analyst把所有最新的Excel文件收集在一起，然後挨個打開文件選中合適的Worksheet，選中區域設置好格式進行列印。每進行一次，幾乎耗費一兩個小時，還不能保證不出錯。

於是寫下了我的第一個VBA程序，而且基本上是宏錄製之後來改的，沒有使用參考書及搜索引擎，全靠F1和自動提示，所以貼出來特別紀念一下。實現的功能就是將上述的人肉實現的功能全部自動化。按下一個妞，就慢慢等著印表機按順序出結果吧。

後來這個程序的升級版是：調度多台印表機，進一步提高效率，以及將印表機卡紙造成隊列錯誤的概率降到極小的範圍內。

VBA編程之案例2：製作複雜的矩陣式分析圖表

下圖是研究各個車型之間的用戶相互轉換關係，因為要將一維的轉化率向量，變成兩維的矩陣，所以使用了如下的複雜公式。

=IF(ISERROR(OFFSET($C$2,MATCH(CONCATENATE(ROW(A4),"-", COLUMN(A4)),$D$3:$D$600,FALSE),0) / OFFSET($C$2,MATCH(CONCATENATE(ROW(A4),"-",ROW(A4)),$D$3:$D$600,FALSE),0)),"",OFFSET($C$2,MATCH(CONCATENATE(ROW(A4),"-", COLUMN(A4)),$D$3:$D$600,FALSE),0)/ OFFSET($C$2,MATCH(CONCATENATE(ROW(A4),"-", ROW(A4)),$D$3:$D$600,FALSE),0))

同時為了用顏色的深淺來表示轉化率的大小關係而便於比較，使用了VBA對下面的矩陣進行著色。當然有人肯定會說可以使用條件化格式，但是使用VBA保持了最高靈活度和效率。

VBA編程之案例3：管理分佈的任務流，並將Excel表格輸出到Powerpoint

這是協助某國際大型汽車製造廠完成新品牌及其新款車型上市，面臨車型即將斷檔的窘境，該新車型的上市非常關鍵，不能錯失時間節點。然而，新車型上市涉及到無數分支：製造、產品、市場、渠道、營銷、公關、財務等等，同時還要協調歐洲的兩個總部以及的兩個分部。

這次諮詢的核心任務就是項目管理，總控整個大項目的進度，並每周向區的CEO彙報進度併發掘出易出現問題的關鍵節點以調配資源。我們4個諮詢顧問分配下去各自負責幾個部門或者項目分支，和團隊一起規劃流程、畫甘特圖、確認里程碑及時間點、安排負責人等等。當每天回到辦公室大家將進度匯總在一起的時候發現了挑戰及難點，每條任務線並不是獨立發展的，而是各條任務線交織在一起並互相影響。

某些核心人員在多個任務線出現。比如：負責預算的財務人員，幾乎要出現在各條線中負責相關預算的審批環節；

某些任務線的里程碑是其他任務線里程碑的必要條件而相互關聯。比如：新車的下線時間影響發布會的時間，相關法規測試的通過又影響車輛的下線時間等等。

當任務線增多以及任務線之間的交叉越發頻繁的時候，匯總的任務將會幾何級數增加，這就是我們在項目過程中遇到的問題。於是我利用Excel+VBA完成了這個工作的自動化。主要實現的功能是

各顧問手中的表格是按照部門維度來劃分的，匯總後需要按照不同的維度來輸出不同類型的表格，比如：按任務線輸出表格、按責任人輸出表格、所有延誤任務的表格、所有需要資源重點投入任務的表格等等。

在此基礎之上，還要將上面提到的各種維度下的所有表格（大概有200多張），按要求格式粘貼到PPT中，每周提交給區的總部進行彙報和評估。密密麻麻的表格如下圖。於是，我又寫了一個程序將Excel中的表格輸出到Powerpoint中，將一個秘書每次需要數小時才能完成的工作，簡化成了一鍵發布，並可以在Excel中完成對PPT的更新。

這個項目的程序量不小，近似於寫了一個迷你版的Microsoft Project來進行項目管理。

最後，下圖中密密麻麻的PPT每周需要更新一次，每次都是快100張的工作量，然而基本上都是靠Excel來自動完成更新的。因為PPT的模版每次變化不大，我將這些模版記錄下來，每周更新的時候只要根據Excel中最新的數據更改PPT中的數據即可。

VBA編程之案例4：構建Financial Model並根據結果倒推假設

一般的Financial Model都是根據重重假設計算最終結果。而在為某頂級手機品牌服務的過程中，我們卻遭遇了逆向的尷尬。本來是根據地面銷售人員的一定服務水平，計算所需要的銷售人員數量；結果在項目過程中，總部已經確定好了銷售人數的Head Count，轉而要求我們根據HC確定服務水平。然而，服務水平不是一個單變數，是由零售店的覆蓋率、銷售拜訪頻率、拜訪中的服務深度等多重因素來決定的，同時還可以根據一線至無線城市來變化。

於是只好再次寄出Excel+VBA法寶。先根據常規思路建立好Financial Model，得出HC的初步結果。然後寫VBA程序，根據不同的情景、不同的優先順序以及不同的權重來調節零售店的覆蓋率、銷售拜訪頻率、拜訪中的服務深度等多因素，同時設定這幾大因素的可接受範圍，逐步逼近HC的預設值。

如果沒有程序，以前基本是靠人工手動調節來湊結果，而且因為各種情景的不同，還需要多次調節。而通過程序，基本是自動完成，還可智能得設置優先順序及權重，無需人工參與。

VBA編程之案例5：海量下載Bloomberg數據並完成分析

通過Bloomberg的VBA API，海量下載數百隻目標股票的tick data以及order book。

並根據實現構建好的數學模型，在後台完成計算，將上述的實時數據轉化成每隻股票實時的trading cost，實時展現在交易員最常用的Excel界面中，方便交易員評估當下的交易成本以便於優化交易策略。

在項目中對Excel的要求很綜合。首先通過數組函數，對每年對RIO酒購買時刻的提及率按省進行統計。

其次，利用VBA程序，將上表中H列和I列的數據，按省份塗色到下圖中的地圖中。

最後，再次利用VBA編程以及調用外部程序（GIFSICLE），將一幅幅圖表合成在一起生成GIF動畫。

最後的彩蛋，還可以用VBA來畫油畫，零基礎成為用美術作品把妹撩漢的藝術青年，下圖把女神用Excel畫成油畫送給她。

寫過書翻譯過書，《體驗寶馬Driven》、《感悟福特》等；做過投資做過互聯網產品；目前專註於數據和互聯網產品中。

「天吶這玩意兒實在太難啦！還沒入門就想放棄啊！」

別怕！MOOC自習室為大家量身打造了入門款Excel教程，每天30分鐘，用30天掌握足以應對日常工作的Excel技能！還能加微信群和大家一起學，你的疑問都有群里大神為你解答，進群還可以獲得專屬學習資源包~

掃碼來學！教程每日更新喲！