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

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

網誌圖文版：

https://www.b88104069.com/archives/4388

上一節利用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其實用不到，但在拖曳複製公式的時候，便可以發揮作
用，幫助定位真正想要資料的欄位。

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

不過這裡有個問題，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的
範圍都沒有問題，同樣能達到預期效果。

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

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

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

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

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


延伸閱讀：

VBA取得財務報表

https://www.b88104069.com/archives/4374

償債能力分析：流動比率

https://www.b88104069.com/archives/4372

Excel如何取得XBRL網頁資料

https://www.b88104069.com/archives/4368

--


周末，我們繼續Excel：精華區=>21.心得=>5.其他=>3.office

會計人的Excel小教室： https://www.facebook.com/acctexcel


--