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 -- 周末，我们继续Excel：=>21.心得=>5.其他=>3.office 会计人的Excel小教室： https://www.facebook.com/acctexcel --

※ 发信站: 批踢踢实业坊(ptt.cc), 来自: 112.22.233.65 ※ 文章网址: https://webptt.com/cn.aspx?n=bbs/Accounting/M.1558194244.A.94F.html