“如何在Excel中少犯二”。從這個話題開始的理由特別簡單：首先，時常在別人的Excel數(shù)據(jù)模型中發(fā)現(xiàn)各種細小錯誤，哪怕作者花費再多的時間把圖表搞得再精美，頓時對整個模型的結(jié)論產(chǎn)生懷疑；其次，Excel數(shù)據(jù)模型是經(jīng)常要在現(xiàn)場分享并不斷調(diào)試數(shù)據(jù)，經(jīng)?？匆娮髡呤置δ_亂得更改輸入和公式，忙中經(jīng)常出錯。

巴菲特曾說，犯錯少就是成功。特別是那些重大而簡單的弱智錯誤。這個理論套用到Excel做數(shù)據(jù)模型上面，一樣成立。對于Excel這種人人都會的工具，少犯二的目標看似簡單。然而，量變產(chǎn)生質(zhì)變，當成千上萬的數(shù)據(jù)堆積在一起，同時帶來千絲萬縷的函數(shù)關系，少犯二就會成為一個高大上的目標。如果不信，可以試試從1開始寫數(shù)字寫到600，能否保證一字不差（這是一個經(jīng)典的街頭騙局）。

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

第一層：提升輸入質(zhì)量

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

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

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

第二層，提升輸出質(zhì)量和增加輸出維度

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

第三層，檢驗運算過程

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

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

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

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

第四層，提升自動化程度

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

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

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

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

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

函數(shù)寫法是：
=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（檢查站）的方式來檢測三張報表是否配平，然而這種土法炮制的方式只能防止最后的結(jié)果不能出錯，而不能保證中間的狀態(tài)以及提升效率。為了偷懶和提高財務模型的健壯性，將各類索引函數(shù)及數(shù)組函數(shù)用到極致，于是實現(xiàn)自動配平以及檢查。

函數(shù)寫法是：=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))

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

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

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

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

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

• 靈活性高，需要有靈活的框架快速滿足老板及客戶多變的需求

• 復用性強，這個項目做得Financial Model，隨便改改就能投入到下一個毫不相關的項目中使用

• 健壯性強，盡量減少頻繁的手工輸入或者操作，將原始數(shù)據(jù)集中在一個模塊，改一個數(shù)據(jù)，相關的數(shù)據(jù)及模塊自動更改

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

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

• 常數(shù)/核心數(shù)據(jù)/假設數(shù)據(jù)部分，包括：商業(yè)常數(shù)（匯率及稅率等）、歷史數(shù)據(jù)（過去的財報以及市場規(guī)模的歷史數(shù)據(jù)）、認為靠譜而不能改動的預測數(shù)據(jù)、核心假設（比如假定宏觀經(jīng)濟按照6-7%來增長）等等。這些數(shù)據(jù)略等于C語言的h文件部分，動一發(fā)而動全身，所以要單獨對待。如同程序一樣，Excel的函數(shù)中是不能出現(xiàn)hard-code的數(shù)字，所以如果一個財務模型中出現(xiàn)“=2*3.14*r”，基本是可以打回去重做的。

• Scenario場景，包括：模型中需要經(jīng)常調(diào)節(jié)的重要輸入?yún)?shù)（比如：市場滲透率、Exit PE ratio等）。這些參數(shù)最好剝離出來成為一個單獨的界面，可以比較方便的控制和調(diào)整，為之后的Sensitivity Analysis做準備，甚至可能遇到在上文中提到的類似于用梯度下降法尋求最優(yōu)值的情況。

• 基礎模型。這一步的核心就是做出預測的三張財務報表，最令人痛苦的是配平?？梢允褂酶黝悘碗s函數(shù)（Indirect/Offset/VLookup等）來進行配平而不會出錯，而且復用性極高。

• 進階模型。基于歷史及預測的三張報表，做一些更復雜的財務分析或者估值預測，包括：DCF、Comparable、敏感性分析等等。

• 呈現(xiàn)。把用戶（包括老板或者客戶）最關心的產(chǎn)出放出來，用最友好的界面展現(xiàn)出來。當然做得極致些，可以把調(diào)整Scenario以及重要參數(shù)的界面也放出來，方便用戶Manipulate Data（其實翻譯成中文更有趣一些：猥褻數(shù)據(jù)）以便得到最滿意的結(jié)果。

下圖是曾經(jīng)奮戰(zhàn)過的一個Financial Model，基本涵蓋了上述的邏輯和構(gòu)建過程，供大家參考。

何明科，曾服務于BCG和軟銀賽富等金融機構(gòu)，曾在美國Mellon Capital擔任Trading Analyst，從事Trading Cost定量研究，前快播SVP，負責部分產(chǎn)品的技術(shù)研發(fā)，圖像技術(shù)及AI研究。目前專注于數(shù)據(jù)和互聯(lián)網(wǎng)產(chǎn)品中。（微信公眾號：數(shù)據(jù)冰山）

本文由專欄作者 @數(shù)據(jù)冰山 原創(chuàng)發(fā)布于產(chǎn)品社區(qū)，未經(jīng)許可，禁止轉(zhuǎn)載。