Excel如何設計函數公式整理財報資料

上一節利用VBA巨集程式,一次取得同一家公司連續五個年度的資產負債表,如此已經大大節省了Excel指令操作的工作效率,然而還留下一個問題:縱然是取得了財務報表的資料,但其實是要進行財務比率分析,以流動比率而言,僅需要資產負債表那麼多科目中的流動資產合計和流動負債合計,如果沿用第五章第三節的方法,仔細找出來之後儲存格參照連結,顯然不是很聰明的作法,這一節分享如何設計Excel函數,讓這樣查找連結的過程更加AI智能化。

一、基本思路沒變,將原始資料中和財務比率有關的金額帶到另一個工作表計算,所以先新增工作表,模擬原始資料報表的結構,設置年度和欄位,這麼做是方便設計好的函數公式複製,只要將第一儲存格公式設好,滑鼠拖曳可以很快地延伸公式。

模擬原始資料報表的結構

二、結構架好之後,首先在儲存格B9設計MATCH函數公式:「=MATCH($A9,資產負債表!A:A,0)」。

MATCH有三個參數,第一個參數「$A9」表示要查找的值,也就是「 流動資產合計」,注意到這裡前面有四個空格,它是由資產負債表資料直接複製過來的,通常從別的地方得到的資料可能會有這種情形,所以避免直接輸入「流動資產合計」,這樣Excel會查找不到,最好用複製方式將要查找資料填進去。第二個參數「資產負債表!A:A」,表示要在資產負債表這個工作表的A欄查找,也就原始資料中的第一個年度會計欄位,第三個參數「0」,這是MATCH函數固定用法,表示要找到完全相符的內容。

在儲存格B9輸入好函數公式,滑鼠游標移到儲存格右下角,游標會從白粗十字架變成小黑十字架,按住往右拖曳到N9儲存格,如此即複製好了公式。首先第一個參數「$A9」,在A前面有個「$」,表示將A欄固定住,在往右拖曳公式時不會跟著跑,會一直是「$A9」,第二個參數「資產負債表!A:A」由於沒有固定欄位,拖曳公式時就會跟著跑:「資產負債表!B:B」、「資產負債表!C:C」、……,從這裡可以知道為何在上一個步驟要先佈局架構,即使第六行中的BC、EF、HI、KL其實用不到,但在拖曳複製公式的時候,便可以發揮作用,幫助定位真正想要資料的欄位。

=MATCH($A9,資產負債表!A:A,0

三、接下來還需要流動負債,一樣將原始資料複製過來,「 流動負債合計」前面有四個空格,在複製公式時,只要先選取B9到N9的範圍,跟上個步驟一樣小黑十字架從第9行拉下複製到第10行,因為公式中第一個參數只有固定欄位、沒有固定行數,所以如圖所示,儲存格N10的公式便會是「=MATCH($A10,資產負債表!M:M,0)」。

不過這裡有個問題,H10到N10的公式計算結果為「#N/A」,表示查找不到(No Available),這就好像寫程式出現錯誤,需要依照執行過程再理一遍,看看是哪裡出錯了。

H10到N10的公式計算結果為「#N/A」,表示查找不到(No Available)

四、原來是從2015年開始,流動負債的欄位前面有五個空格,比先前年度多了一個空格:「 流動負債合計」,如此導致Excel無法識別。這邊想到的解決方法是,既然有兩種情況,那麼設置兩個關鍵字,在函數公式增加一個邏輯判斷:=IFERROR(A,B),如果A方案出狀況了,四個空格不行,那麼改用B方案,五個空格作為查找條件,依照這個思路設計的公式為:=IFERROR(MATCH($A10,資產負債表!M:M,0),MATCH($B10,資產負債表!M:M,0)),A10不行、找B10,此公式在這裡是普遍性的,將它用小黑十字架複製到整個C9到O10的範圍都沒有問題,同樣能達到預期效果。

=IFERROR(MATCH($A10,資產負債表!M:M,0),MATCH($B10,資產負債表!M:M,0))

五、定位出原始資料中哪些是目標,接下來是取得目標內容:「=INDEX(資產負債表!B:B,C9)」,意思是在資產負債表的B欄,引用第28行(C9儲存格值)的內容,公式一拉,馬上得到五個年度的資產負債表。INDEX函數除了以欄數作為坐標引用之外,列數或者兩者一起引用都可以,有興趣讀者可以進一步研究,或者後面有適當案例再進一步介紹。

=INDEX(資產負債表!B:B,C9)

六、精準整理出所需要的財務資訊後,財務比率的計算相對較簡單:「=C18/C19」,同樣可以很方便地複製公式。

財務比率的計算相對較簡單:「=C18/C19」

七、最終將結果引用到新工作表,額外補充基本資訊,稍微修飾報表格式。注意到這裡的連結是從原始資產負債表經由函數公式計算、間接引用到最終報表,如此安排是假使原始資產負債表金額有變更,最後報表也會隨之改變,在設計Excel函數公式應保持這個良好習慣,維持資料串流的單一性。

這一節的範例也可以把所有東西全放在一張工作表上,不過還是建議另外新增工作表,逐步處理引用,從原始網頁資料、計算工作底稿、結果彙總報表,三張工作表各司其職,這樣會讓整體結構更加井然有序。第一張表方便替換不同公司資料、第二張表在必要時重新調試函數公式、第三張表陳述基本資料和設置報告格式,如此在每個步驟都保留了彈性,就好像買一台保留有擴充槽的電腦一樣,方便未來作因應。

隨著第二篇所介紹的財務比率越來越多,勢必要藉助工具有效率地進行。上一節分享如何以VBA取得多年度資,這一節再分享如何以函數公式帶出所需財務資訊,已經涵蓋了財務比率分析所需的資料來源,往後章節會再繼續完善這個Excel工具。

本文內容取自《讓上班族狠賺的EXCEL財報分析術》,博客來書店網址:

https://www.books.com.tw/products/0010801639

對於如何以Excel VBA執行財報分析有興趣,歡迎參加贊贊小屋實體教室課程:

https://www.tibame.com/offline/excel_investment