在金融行業中，常用的 Excel 分析操作技巧有哪些？

11-25

在金融行業中，Excel最重要的工作就是做模型對各類指標進行估算，在（投行常說的「模型」(model) 是什麼東西？ - 何明科的回答）中有許多交代。主要的模型包括但不限於：

偏會計或財務：財務三張報錶的歷史及預測
偏項目投資或管理：NPV/IRR等模型
偏股權或債券投資：市場規模預測、投資價值預測（DCF及Comparable等等）、針對各種股權的回報預測等
偏交易方面：針對各類金融產品（FX、衍生品等等）的模型估算和執行策略等

因此本文緊緊圍繞在金融行業內的財務模型來解釋各種技巧，簡單將其分為技法和心法兩類。簡單介紹三個技法而著墨在心法，因為技法讓人在道路上跑得更快，而只有心法讓人能跑在正確的道路上。

技法1：掌握快捷鍵
如同程序員崇尚用VIM甚至極致到用「hjkl」來代替"↓↖↑↗"，金融行業在使用Excel的過程中，把雙手牢牢放在鍵盤上翻飛才是正確姿勢。這就需要熟知各類快捷鍵，詳見（excel 鍵盤操作技巧？ - 何明科的回答），裡面提到的都是金融建模中最常用的快捷鍵，滿足七八成的實戰。另外高效的學習方法是遇到重複發生的操作然後去掌握和記憶快捷鍵，在實踐中學習，而不是先死記硬背然後再實踐。

檢驗金融建模中使用Excel快捷鍵水平高低的重要標準是：能否脫離滑鼠而手不離鍵盤。不過滑鼠卻是繞不過的，這時候職場人士的標配Thinkpad小紅點就顯示威力了。

技法2：掌握初中高級各類函數及Excel自帶的工具
初級一些的函數有Sum/Average/Sumproduct/If等，完成基礎的四則運算和邏輯運算。複雜一些之後，就需要使用VLookup/HLookup這類標準的查找函數來匹配數據，再複雜一些就需要花式查找（ Match/Offset/ Indirect/Address等）。查找完之後，就涉及到統計計算，於是Sumif或者Sumifs的需求就來了。然而這還不是效率的極值點，當繁衍出類似於資料庫的「Select…Where…Group By…」等多重複雜條件的需求，於是就不得不引入了數組函數。

Excel上面還提供了許多金融財務的專業函數（比如：IRR/NPV/PMV/PV/FV等），使用的時候一定要把參數和輸出搞清楚，一些關鍵點模糊絕對就是產生錯誤的結果，比如：NPV或IRR計算時候的起始點選取。

另外，Excel還提供各類工具：

數據清洗工具：分割和去重等等
數據分析工具：排序、篩選、數據透視表等
統計工具：ANOVA、各類檢驗及多元線性回歸等等
求解工具：Solver

技法3：掌握Macro和VBA
已經厚顏無恥得推廣VBA多次，它們絕對是提升效率和完成複雜運算的利器（曾經使用VBA來完成蒙特卡洛模擬），這裡不多說，詳細的答案參考（Excel 到底有多厲害？ - 何明科的回答）。這裡就簡單舉例一枚給不願跳轉的同學。一般的Financial Model都是根據假設計算結果，而在為某頂級手機品牌服務的過程中，卻遭遇了逆向需求。本來是根據地面銷售人員的一定服務水平，計算所需要的銷售人員數量；結果在項目過程中，總部已經確定好銷售人數，轉而要求根據人數確定服務水平。然而，服務水平不是一個單變數，是由零售店的覆蓋率、銷售拜訪頻率、拜訪中的服務深度等多重因素來決定的，同時還可以根據一線至五線城市來變化。於是只好再次祭出VBA，先根據常規思路建立好Financial Model，得出人數的初步結果。然後寫VBA，根據不同的情景、不同的優先順序以及不同的權重來調節零售店的覆蓋率、銷售拜訪頻率、拜訪中的服務深度等多因素，同時設定這幾大因素的可接受範圍，逐步逼近銷售人數的預設值。

高攀一下，是不是有點深度學習的梯度下降法的味道？

心法：掌握構建財務模型的框架和基本原則
做Financial Model的基本思想其實和編程非常類似，比如著名而老套的MVC：整個Financial Model的邏輯被分成三層， Model（負責數據），View（負責呈現）和Controller（負責業務邏輯），理想狀態下其中一層的改動不會影響到另一層。我在做大部分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，基本涵蓋了上述的邏輯和構建過程，供大家參考。

...更多文章請到數據冰山 - 知乎專欄
...更多回答請看何明科的主頁

先說說操作，以前常用的技巧有

對表格不同行列的分組，許多excel表格佔地方但是內容暫時不想看，可以考慮分組摺疊起來，win下的快捷鍵是alt shift -&>
使用宏可以對excel的不同標籤頁的格式保持統一（另外需要注意不同單元格的放大比例可能不同，導致字體大小的顯示出現問題）
記得最後檢查好的excel表格都選擇左上角的單元格（也就是把滑鼠在左上角的那個A1單元格上點一下，這樣下一個人在打開的時候單元格是從第一行第一列開始的，以免出現從底部開始閱讀的問題，給別人省事是美德……）
excel中如果需要設置中文和英文的不同字體記得在先設置中文再設置英文，否則先設定英文再設中文則英文的字體會被中文字體中自帶的英文字體覆蓋掉。
推薦在製作excel的時候打開列印頁面虛線，至少知道自己的excel表格是個多大的篇幅，具體列印的時候可以選擇列印到一張頁面上，excel會自動壓縮到一個頁面之內。
用不上的excel行、列要隱藏，以免別人誤操作給你把游標移到了5000行之外的地方，很不爽的……
個人很不喜歡excel默認的表格線，認真做的話把背景設置成白色，那些表格的邊框就會消失，真正需要邊框的再自己設置，我覺得這樣會看上去很清爽（個人意見）。

關於分析
金融行業有多關注excel的分析？這個用的不多，現在只要是財經院校金融相關授課都不推薦excel做分析了，matlab的數據計算簡單實用，操作也不複雜，滿大街都是教程，真的已經走入千家萬戶了，指望用excel做分析的我接觸到的是各種財務數據的分析，在會計這邊用的多一些：

關於趨勢圖，要會把實際數字和增長率畫在一個表格里，左邊的坐標是實際值，右邊的坐標是增長百分比，清晰有簡潔。
要各種財務數字的計算，建議你準備一個模版，到時候只需要把財務報表按照那個格式貼進去要的比率自動出來了。（記得紅色表式負數）
office2010出現了趨勢圖，在一個單元格里直接畫出過去幾年的變動趨勢，很實用。
另一個常用的是數據透視表，但是不限於金融行業，對閱讀者的要求也有點高，一般年終總結經常會用到。

大體先想到這麼多，金融行業大家看excel時間挺長的，讓看你做的excel表的人覺得輕鬆，方便，就是最好的結果，多和看你excel表的人交流一下，客戶才是上帝。

我就說幾點我觀察到的很多人不知道的小技巧
1)比如表格有上千行,有的人拉最右邊的條條找到最後,其實你只需要安Ctrl+下方向鍵,就到了這一列的尾巴了,當然有時候有空行的話也會停住,你還需要再按一次繼續. 同理如果你想選擇整個一列,只需要按住Ctrl+Shift+下,就行了,我經常看到有人用滑鼠往下拖,拖了快1分鐘才到低.
2)有時候你在最上面輸入一個公式,按住單元格右下的小加號往下拖,要是幾千行不是要拖到死,其實你只需要雙擊那個小加號就行了
3)如果你用的是office2010及以上版本,大部分功能在上方工具欄中都有快捷按鈕,請在試圖學習任何高級技巧前搞清楚工具欄每一個按鈕是什麼意思,什麼情況下會用到,有的人會用vba編程做檢索,但是卻不知道excel有數據透視表這麼個簡單傻瓜的神器

此外做分析的話要經常建立數據篩選,vlookup,offset,index,match,find,這幾個函數都很重要,如果有時間一定要熟練掌握哦
我有想到的我再來更新吧

====新補充====
4)很多人經常用excel的篩選功能,就是圖標是個小漏斗的那個,有些時候篩選之後想清楚篩選,換個條件再篩選,這時候有的人直接點那個漏斗圖標取消篩選,再重新建立篩選,超級麻煩無比.其實你仔細看漏斗旁邊有個清楚篩選按鈕,你按一下那個就可以了.
5)篩選功能超級強大但是很多人不太會用,比如如果你想篩選出日期為今天的數據,很多人從下面找到今天的日期,勾選...其實你看有個日期篩選,你只需要選擇列表裡面的"今天"就可以了,同理你可以選明天,昨天,某個日期之前,某個日期之後
6)這個可能有點理論,就是excel表不是亂編的,一行一行是一條條數據,千萬不能把一行一行當作屬性,之後一列一列當作一條條數據,這樣做你以後什麼分析都做不了.還有一個重要的一點就是一個完整的資料庫一定要有一個主鍵,所謂主鍵說白了就是與眾不同的一個編號,比如你統計全校師生成績,千萬別就是名字加成績,哪怕沒有學號,你也要在加1列,1,2,3,這樣排下去,保證每一行有一列的數據與眾不同.否則萬一兩個人名字一樣成績不一樣就悲劇了
7)學會用表格顏色填充.也許你經常看到有的excel裡面表格花花綠綠的,各種背景顏色,看著很亂,但其實這樣做很有效.比如還是全校師生成績,如果給你個名單讓你關注10個人的成績,你可以用搜索分別搜出這10個人的成績,但是萬一第二次考試成績出了,表格更新了,你難道再搜索10次嗎?其實你只需要把這10個人的名字的表格加入背景顏色,比如常見的黃色,之後你建立篩選==&>顏色篩選==&>點擊黃色,直接就能將這10個人篩出來,以後不論表格增加多少數據,你都可以很快將這10個人篩出來

想到了新的我再繼續更新,謝謝各位支持了

作為一個quantitative finance苦逼研究生來談一下我的經驗吧。上個學期學了一學期的financial modeling，用的基本上是vba, 所以也當是一次複習了。

1.首先，就像樓上的諸位前輩所說，熟練用各種快捷鍵可以極大地提高處理表格的各種數據。我曾經在課堂上看到我們的教授，就用鍵盤噼里啪啦幾下就根據數據弄出了portfolio的有效前沿，速度極快，嘆為觀止。

2.僅僅在表格里用函數還是停留在初級階段。想要發揮excel的全部功效還得學vba,至少得知道怎麼條件、循環等，進一步要學一學怎麼用userform.用上這些會使得excel的功效得到極大的發揮。在我上課的時候就用excel實現了filter，macd, pair trading, tracking error等比較經典的交易策略。這些都不是只用excel函數可以做到的，都要用vba。

3. 更為重要一點的是，善用help功能，無論是在excel里還是在vba中。現在稍稍常用的函數其實excel都自帶了，需要用時直接可以在help裡面看看應該怎麼用，裡面經常有寫函數的詳細說明和例子。英語好，看help會比較有優勢。

4.還有一些各種各樣的技巧，比如說會用solver, 矩陣乘法mmult等等，這些都太多了。想要用好，基本上需要認真花功夫學vba等東西…想我們之前學financial modeling這門課時真心被弄的很慘…在網上可以找到很多學vba的教材，我覺得簡單入門級別的excelhome論壇出的那本書很不錯。

5.說了這麼多來總結一下。其實以上都說的是一些excel和vba的心得，並沒有涉及到金融怎麼用。的確在在處理數據當中，毫無疑問地，Matlab，R，c/c++，sas, SQL 等要好用得多，據我所知，美國業界quants用R，Matlab，c++等居多。相比起來，excel優勢就在於簡單明了，易於交流。比如說不是技術出身的大佬們很多都不會專業的編程語言，這時用excel跟他們交流才是最好的選擇，可以做一個讓他們這樣自己輸入數據，按一下按鈕就可以得到答案或圖像的宏無疑是他們很喜歡的的方式，例如可以用excel做一個根據black-scholes公式算期權價格的計算器, john hull的DerivaGem應該算是使用excel的一個登峰造極的表現。畢竟做決定的都是大佬但大佬們都很忙，沒時間用什麼Matlab，他們電腦上也不一定有Matlab或者R，但肯定有excel…易於交流，這就是為什麼quant用的編程語言多一些，而front office更多用excel vba。

以上都是一些小小心得。有什麼錯誤請大家斧正。

不要合併單元格！

我經常說我大概45分鐘內就可以架構好model，算好，run scenarios，核對，完成。最後寫狗血的ppt，排版，格式alignment整了了5個小時。

1，多用快捷鍵，少用滑鼠點。剛開始可能會比較慢，熟練了會快很多，參見dota操作。excel無影手是可以不用滑鼠的哦。（常用快捷鍵：alt:選定菜單欄；ctrl+z:撤銷上一步；ctrl+f：查詢(替換)；ctrl+g:選定特殊單元格；f4:重複上一步/絕對引用；ctrl+s：保存；Ctrl+A ：全選；page up: 返回最上方。還有設置字體的快捷鍵等等。）
2，善用數據透視表。因此，請盡量少用合併單元格，用重複相同的單元格。
3，善用函數。普通的if, and, average, sum,max/min,left，進階點的countif,index,vlookup一族，match等等。每個人的工作內容不同，日常多想想自己的工作，看看是否可以用函數解決。
4，做圖的時候請追求「信素比」。就是你想表達的信息與你使用的圖表類元素的比值，請盡量得高。比如你用了網格線，就不要在你的bar上標數字了。不要用三維/陰影。注意次要元素的不要太大號，加粗等等。當然，如果你的領導喜歡的話，另說......
5，請注意格式和顏色搭配。格式的原則是對比/重複/對齊/親密性，參見《寫給大家看的設計書》。顏色的話去搜一下各種情況下顏色的搭配。懶的話，就用藍橙白灰吧...
6，搭建自己的模板。比如日常用的圖/表的模板，顏色板的模板，數據透視表的模板等等，會節約很多很多時間
7，相信excel的便捷性。一般情況下我們的工作excel都能很便捷的完成，如果我們操作起來很累，說明我們走彎路了。請善用搜索/論壇/身邊人

先想到這麼多，其他以後再說吧
最後，知乎的管理員都從不用手機回答嗎？現在好累······

1. 做表的時候，一個格子就是一個格子，盡量避免使用合併單元格之類的功能，否則在做分析和匯總的時候會非常麻煩
2. 匯總分析的時候使用數據透視表
3. 花一個下午的時候看一本入門讀物

純記憶手打。。

金融行業用到的excel功能並不複雜，很多功能都是工作需要時才去找，比如需要去掉空格時，會找到trim。宏什麼的會用當然好，不會的話也有很多方法來達到目的。各種功能用得好可以提高效率和逼格。
另外金融行業用excel，包括word和ppt都會花大量時間在formatting上，目的是增加可讀性和保持風格的一致性，而不是為了花哨。這方面的要求包括字體，大小，對齊，圖標的顏色，軸的刻度，橫軸時間要標示出最新時間，單位等等。文件要儘可能簡潔乾淨小巧。

提高效率有幾個方面（只列出來一些有代表性的，同學們有需要可以舉一反三）：

1. 對數據結構了解。比如這個單元格儲存的是數字還是字元串，怎麼樣在不同的類型之間轉換，像value, left, trim, find, replace, date, edate等等函數。另外要會用if等條件判斷。

2. excel單元格的相對和絕對位置，像$A$1。進階的會使用indirect, offset函數。

3. 數據的運算，常用得就是vlookup, index, match, sumif, counta之類。另外還有統計上的一些函數。

4. 快捷鍵。這個對逼格提升巨大，熟練的同學完全不需要滑鼠，全鍵盤操作。常用的比如點End+方向，快速到達當前列/行最後，中間如果加Shift可以選中所有數據。輸入數字的時候Tab和Enter不同的功能。其他的組合鍵比如選擇性粘貼公式Win+S+F, 選擇性粘貼數值Win+S+V, Freeze pane Alt+W+F, 篩選 Alt+D+F+F, 儲存Ctrl+S, 單元格屬性Ctrl+1, 更改單元格格式為日期 Ctrl+Shift+3等。

5. excel本身的一些功能。比如分列，規劃求解，單變數求解，Data table（忘了中文叫什麼了）。

不是金融行業，來添加幾個我常用的小竅門吧……
1.F5鍵的使用：主要是定位功能，也可以用Ctrl+G，可以一次性選擇表格內的空白單元格、含公式單元格等不連續或者無法一次性拖選的單元格，試一下就知道，好用！
2.F4鍵的使用：重複上一次操作。比如上一步設置了單元格格式，含字體、邊框線等格式，如果要重複上一步這些操作，F4一鍵搞定！
3.Ctrl+Enter：選中要輸入內容的單元格，在編輯欄填寫內容後（可以是文本或者公式），Ctrl+Enter兩鍵，所有單元格填充完畢！和F5鍵配合使用，怎一個爽字了得！
手機打字，實在太慢！
希望有用吧！

看來好多人不知道Excel里的複利計算、利息、貼現率如pmt pv fv i irr 等計算啊。可能搞金融的才知道吧，雖然我只是個建築師.

學會了數據透視表，才發現自己以前太low了

vlookup

零售行業基本用很多透視表，還有if，vlookup

數據透視，高級篩選，二維餅圖，折線圖，雷達圖，帕累托圖，據說不精通一點函數，你都無法找工作，這個不是吹牛，可真的是活生生的例子。