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

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

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

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

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

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

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

第三層，檢驗(yàn)運(yùn)算過(guò)程

最簡(jiǎn)單粗暴的方式，利用函數(shù)框中對(duì)輸入不同顏色的標(biāo)注，對(duì)運(yùn)算過(guò)程及輸入?yún)��?shù)進(jìn)行檢查，看是否達(dá)到預(yù)期或者有犯二的差錯(cuò)。

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

再升級(jí)一些，可以對(duì)一些關(guān)鍵指標(biāo)以及核心等式就行復(fù)檢。比如對(duì)于財(cái)務(wù)報(bào)表中最基礎(chǔ)的等式“資產(chǎn)=負(fù)債+權(quán)益”，在做Financial Modeling的過(guò)程中，都要設(shè)置專門的一行進(jìn)行檢查，類似于化學(xué)方程式的配平檢查。

著重講下圖用紅心標(biāo)注的第四層和第五層

第四層，提升自動(dòng)化程度

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

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

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

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

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

函數(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)))

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

函數(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))

第五層，使用先進(jìn)的“編程思想”

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

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

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

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

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

在做大部分Financial Model的時(shí)候基本就是按照MVC的框架來(lái)要求自己的。

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

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

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