excel 6種辦法技巧，讓Excel效力加倍

在Excel中少犯二并不簡單，如同要做到如何寫好程序一樣，需要引入一套方法論，在下面分步驟列出。

第一層：提升輸入質量

通過使用“數據有效性”功能，減少輸入的錯誤，設置輸入的有效性檢查（比如：手機號碼是11位等等），盡量避免"Garbage In, Garge Out"。

Excel數據模型中，存在不同類型的數據：常數、可調參數、中間結果、最終結果等等。不少的錯誤發生在混淆各種類型的數據或者更改了不能調整的中間結果而污染最終結果。因此可以考慮對不同的數據，用顏色或者worksheet進行區隔，下圖左方就是利用顏色標注不同的數據：有些是供修改的，有些是最后結果及中間過程而不能修改。這些措施都是為了在模型的制作過程以及調試過程中，避免誤改誤刪數據。如果要想做到極致，還可以使用下圖右方的方法，直接利用“保護工作表”功能，對不能修改的數據進行保護。

另外，對于一些經常使用的輸入區域（比如，A1:A299），往往反復出現在Sum或者Vlookup等函數中，可以考慮將它們定義成變量，并可以在“名稱管理器”中進行修改、增加和刪除等管理。反復使用的時候就會非常方便，比如：=Vlookup(A1,data,2,false)這種簡單的寫法。

第二層，提升輸出質量和增加輸出維度

不少人在用Excel輸出結果的時候，往往就事論事，把輸出局限在較小的范圍，不僅容易忽略錯誤，而且會失去多個視角。但如果能夠在現有輸出結果的基礎擴展審視輸出的維度，就能獲得更全息的視角。以財務三張報表而言，當然可以妥妥得輸出經典的報表格式，但如果能夠多計算一些財務指標并放在合適的位置（如下圖中所示的EBIDTA、Cash flow from operation、EBIDA/Interest Expenses等等），不僅可以部分校驗運算過程，還能從多個維度反應企業的財務狀況，當然也更容易發現可能存在的計算誤差，從而提升輸出的質量。

第三層，檢驗運算過程

最簡單粗暴的方式，利用函數框中對輸入不同顏色的標注，對運算過程及輸入參數進行檢查，看是否達到預期或者有犯二的差錯。

升級一些，可通過“追蹤引用/從屬單元格”，對運算過程進行檢查，盡可能避免公式運算中輸入參數的犯二。

再升級一些，可以對一些關鍵指標以及核心等式就行復檢。比如對于財務報表中最基礎的等式“資產=負債+權益”，在做Financial Modeling的過程中，都要設置專門的一行進行檢查，類似于化學方程式的配平檢查。

著重講下圖用紅心標注的第四層和第五層

第四層，提升自動化程度

就像在IDE中使用了關鍵詞提示以及經常檢查Code Review，還是不能寫好程序一樣，這里面還有套路。

Excel中的錯誤經常發生在不斷的手工人肉操作，再簡單的事情做個幾十遍或者幾百遍，出錯的概率也會非常低。因此在Excel中可通過使用系統工具、高階函數甚至VBA來提高自動化程度，避免反復輸入函數或者重復操作，就能大大降低出錯概率。

比如，逐漸學會使用Excel自帶的豐富數據清洗（排序、篩選、根據統一分隔符來分隔數據等）及分析工具（包括高階的統計工具，ANOVA及多元線性回歸等等一個都不少），減少人肉人工參與的過程。

再比如，下面表格中，要求白色區域中的矩陣元素等于所在行、列及worksheet上對應數字的總和。最笨的辦法是每個單元格寫一次加總函數，重復幾十次值幾百次（應該會有許多張worksheet），非常容易出錯；進階的辦法是利用絕對地址和相對地址，寫一次函數，整個矩陣的函數拷貝粘貼就完成，出錯概率大大降低，但是每出現一個新的表格就要更新函數，仍然有出錯的不低概率；最高級的辦法就是在上一個辦法的基礎上，利用CELL函數獲取Worksheet的名字并提煉數字，然后一氣呵成，整個表格的函數完全是動態的，Worksheet復制之后只要改成相應的名字就可以完成任務，在出錯方面的魯棒性很強。

又比如，制作Financial Modeling的時候經常需要將季度或者半年度數據匯總成年度的（或者反向實施），一般的做法都是寫加減等簡單的函數，然而卻不能成塊拖拽或者復制函數而需要手工不斷寫函數，不僅麻煩而且容易出錯，利用Offset等函數，可以寫好函數就一步成型，完成整個過程。

函數寫法是：
=IF(MOD(COLUMN(Constant!A1),2)=1,OFFSET($M4,0,INT((COLUMN(Constant!A1)-1)/2)),?
-OFFSET($M4,0,INT((COLUMN(Constant!A1)-1)/2))+OFFSET($C4,0,INT((COLUMN(Constant!A1)-1)/2)))

又比如，在第二層中，使用設置Check Point（檢查站）的方式來檢測三張報表是否配平，然而這種土法炮制的方式只能防止最后的結果不能出錯，而不能保證中間的狀態以及提升效率。為了偷懶和提高財務模型的健壯性，將各類索引函數及數組函數用到極致，于是實現自動配平以及檢查。

函數寫法是：=SUM(('Balance Sheet'!$AA$8:$AA$100='Cash Flow'!$B44)*('Balance Sheet'!O$8:O$100-'Balance Sheet'!N$8:N$100)*('Balance Sheet'!$AB$8:$AB$100))

第五層，使用先進的“編程思想”

以上都是技法，讓編程真正成為一門科學或者手藝的是，里面存在心法或者思想。圍繞著這些編程思想，構建出一套套體系：MVC框架、MVP框架以及OO等等。這些體系的目的大概都是提高工作效率、復用率以及魯棒性等等，都是多快好省少出錯得完成任務。然而世間萬物，不少都是觸類旁通。利用Excel做數據分析的基本思想其實和編程非常類似，許多框架都可以參考編程思想，這樣就能提高效率和降低出錯概率。

所以歸根結底，還是要做“有思想”的人和“有思想”的事。

Excel最大的實戰價值就是制作各類財務模型（Financial Model）或者簡單的數學模型，用正確的方式方法來做模型（所謂的“套路”）才是心法。

比如可以借鑒著名而老套的MVC到Excel的Financial Modeling，實戰性強且效果好。將構建Financial Model的邏輯被分成三層， Model（負責數據），View（負責呈現）和Controller（負責業務邏輯），理想狀態下其中一層的改動不會影響到另一層。

• 靈活性高，需要有靈活的框架快速滿足老板及客戶多變的需求
• 復用性強，這個項目做得Financial Model，隨便改改就能投入到下一個毫不相關的項目中使用
• 健壯性強，盡量減少頻繁的手工輸入或者操作，將原始數據集中在一個模塊，改一個數據，相關的數據及模塊自動更改

在做大部分Financial Model的時候基本就是按照MVC的框架來要求自己的。

Financial Model搭建的過程就如同修建高樓一層層往上累加模塊

• 常數/核心數據/假設數據部分，包括：商業常數（匯率及稅率等）、歷史數據（過去的財報以及市場規模的歷史數據）、認為靠譜而不能改動的預測數據、核心假設（比如假定宏觀經濟按照6-7%來增長）等等。這些數據略等于C語言的h文件部分，動一發而動全身，所以要單獨對待。如同程序一樣，Excel的函數中是不能出現hard-code的數字，所以如果一個財務模型中出現“=2*3.14*r”，基本是可以打回去重做的。
• Scenario場景，包括：模型中需要經常調節的重要輸入參數（比如：市場滲透率、Exit PE ratio等）。這些參數最好剝離出來成為一個單獨的界面，可以比較方便的控制和調整，為之后的Sensitivity Analysis做準備，甚至可能遇到在上文中提到的類似于用梯度下降法尋求最優值的情況。
• 基礎模型。這一步的核心就是做出預測的三張財務報表，最令人痛苦的是配平。可以使用各類復雜函數（Indirect/Offset/VLookup等）來進行配平而不會出錯，而且復用性極高。
• 進階模型。基于歷史及預測的三張報表，做一些更復雜的財務分析或者估值預測，包括：DCF、Comparable、敏感性分析等等。
• 呈現。把用戶（包括老板或者客戶）最關心的產出放出來，用最友好的界面展現出來。當然做得極致些，可以把調整Scenario以及重要參數的界面也放出來，方便用戶Manipulate Data（其實翻譯成中文更有趣一些：猥褻數據）以便得到最滿意的結果。

下圖是曾經奮戰過的一個Financial Model，基本涵蓋了上述的邏輯和構建過程，供大家參考。