Excel如何一次匯总比较三家公司财务报表




上一节介绍如何一次取得多家公司的损益表,作法是分别放在不同的工作表。实务上财务报表分析时,将不同公司不同期间放在同一张工作表,格式上适当整理,才方便作有意义的比较。虽然上一节所取得的资料,可以Excel手工汇总到一张工作表,但既然是编写VBA,总希望一步到位,在设计程式时把所有需求考虑在内,例如自动将三家公司财报匯总在一起,方便直接分析,这个当然也可以设计VBA程式达成,以下具体分享:

一、想取得三家公司损益表,年度期间从2014年到2016年。

Excel如何一次匯总比较三家公司财务报表 VBA网络爬虫 第1張

二、编写VBA程式码如下:

Excel如何一次匯总比较三家公司财务报表 VBA网络爬虫 第2張

10:建立一个巨集程序「Stock_Year_IS」,「Public」的意思是该程序是被其他模组呼叫,例如像「Call」引用;

30~40:新增工作表「Income Statement」;

60:宣告变数;

80~90:计算清单共有多少行;

100~110:将开始年度设定為「Year1」、截止年度设定為「Year2」;

130~150:设置三家公司的迴圈「i」,将公司代码设定為「Stock1」、公司名称设定為「Stock2」;

170:设置三个年度的迴圈「j」;

190:将年度「j」从数值转换成文字「Year」;

200:此為程式编写的习惯用法,「n=n+1」等於是一个计数器,每循环一次迴圈就加一;

210~240: 先设定取得网页资料的参数,以「Stock1」作為公司代码、「Year」作為年度,同时以计数器「n」适当决定每次下载的储存格位置「DesCell」、并且以第一行作為公司名称「Stock2」的标题行;

260~300:本书一直使用的「QueryTables.Add」方法;

320~360:执行下一个循环,最后结束程序「Stock_Year_IS」。

三、执行结果如图所示,一家公司一个年度是三栏资料,有三家公司三个年度,所以从A栏到AA栏总共27栏。黄色部门是大类的损益项目,从图片可以看出来,不同公司损益表结构不一样,同样的「营业利益(损失)」在不同公司不同行,其实以先前第二节「多期损益报表」的范例而言,同一家不同年度因為状况不同,也有可能损益表结构不尽相同。

Excel如何一次匯总比较三家公司财务报表 VBA网络爬虫 第3張

四、通常财务报表的比较不用到很细,如图所示只需到大项目即可。

Excel如何一次匯总比较三家公司财务报表 VBA网络爬虫 第4張

五、编写程式码如下:

Excel如何一次匯总比较三家公司财务报表 VBA网络爬虫 第5張

30~50:先利用「UsedRange.Columns.Count」方法计算出有多少行及多少栏,如同步骤三所述,共有27栏。因為每次报表有三栏,「Column = Column / 3」得到总共有多少组报表。

70~110:有「i」组报表、设计迴圈「i」,先将每组报表的公司代码及公司名称复製到工作表「六」。

130~230:设计「j」「k」三个迴圈,「j」代表想要擷取的损益项目、「k」代表损益表总共有多少行,配合迴圈「i」,效果等同於Excel的Vlookup查找函数,只要顺著三个迴圈的设定试著跑一两次,应该能理解「i」、「j」、「k」如同Vlookup公式中的三个参数,这裡是用VBA程式码把Vlookup实际执行过程编写出来。

六、执行结果,成功汇总出简易损益表,每家公司各个年度一览无遗。(為了图片清晰起见,部份栏位作了寛窄的调整)

Excel如何一次匯总比较三家公司财务报表 VBA网络爬虫 第6張

七、每家公司规模不同,各个损益项目直接比较,并不具有分析意义。实务作法是计算财务比率,例如毛利率、营业利益率、净利率,这些在上一步骤的基础上,很容易整理出来。(同样為了图片清晰起见,部份栏位作了寛窄的调整)

Excel如何一次匯总比较三家公司财务报表 VBA网络爬虫 第7張

这一节范例是三家公司三个年度的损益表,只要迴圈设定再修改一下,便可以取得更多家公司、更多年度的报表,这是VBA迴圈方便的地方。另外这一节也介绍如何利用迴圈实现Vlookup函数功能,这样做可以把熟悉的Excel函数内化成VBA程式码的一部份,重点是将函数作用以VBA形式编写,其实不限於Vlookup函数,其他熟悉的Excel函数皆能如法泡製,待有适当范例再作介绍。

本文内容取自《人人做得到的网路资料整理术》,博客来书店网址:http://www.books.com.tw/products/0010775391


有系統、全面性的學習Excel職場應用,:會計人的Excel小教室PressPlay頻道


當前文章分類:
VBA网络爬虫