[心得] Excel如何設計函數公式整理財報資料

看板Stock作者moodyblue時間4年前 (2019/05/18 23:40)推噓24(24推 0噓 1→)

留言25則, 25人參與討論串1/1

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 -- ※ 發信站: 批踢踢實業坊(ptt.cc), 來自: 112.22.233.65 ※ 文章網址: https://www.ptt.cc/bbs/Stock/M.1558194039.A.2DA.html

推

cutegiordano

05/18 23:56, 4年前 , 1^F

05/18 23:56, 1^F

推

xuane

05/19 00:10, 4年前 , 2^F

05/19 00:10, 2^F

推

resy

05/19 00:45, 4年前 , 3^F

05/19 00:45, 3^F