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工具。
本文内容取自《让上班族狠赚的EXCEL财报分析术》，博客来书店网址：
https://www.books.com.tw/products/0010801639。
对于如何以Excel VBA执行财报分析有兴趣，欢迎报告赞赞小屋实体教室课程：
https://www.tibame.com/offline/excel_investment。
延伸阅读：
VBA取得财务报表
https://www.b88104069.com/archives/4374
偿债能力分析：流动比率
https://www.b88104069.com/archives/4372
Excel如何取得XBRL网页资料
https://www.b88104069.com/archives/4368