VBA与SQL在数据库与财务报表中的应用

［摘 要］财务人员在跨账套查询科目余额表等财务数据时，需要使用财务软件频繁切换登录各公司账套，进行一系列重复而大量的操作。这种查询操作非常枯燥且耗费时间。运用VBA与SQL语言并结合Excel的窗体与控件的功能进行程序设计，可以让财务人员仅通过一个Excel文档就直接从财务数据库中快捷获得科目余额表等财务数据。整个程序操作灵活、人机交互友好。该程序实现了跨账套智能、快捷批量查询的功能，可以大幅节省工作时间，提高工作效率。

［关键词］VBA；SQL；数据库；财务账套；科目余额表；财务报表

1     问题的提出

很多公司在发展过程中，随着业务规模的扩大，会相继设立或并购很多分子公司，导致该公司财务人员会面对很多分子公司的账套。财务人员在日常的工作中需要经常在各分子公司的账套查询一些数据，如科目余额表等。查询一次科目余额表，就需要切换登录公司账套，选定会计期间与会计科目等参数、执行会计软件查询，输出结果到Excel并重命名等一系列操作。受网络速度、财务软件运行速度、Excel软件运行速度、计算机反应速度等因素影响，这一套组合操作流程大约需要5分钟时间。以此类推，若需查询输出100家公司的科目余额表，则至少需要持续工作8个多小时，整个过程显得特别重复枯燥又相当耗费时间。

鉴于财务人员在日常工作中使用Excel较多，本文尝试运用Excel的VBA功能并结合SQL语言来设计一套程序，以便财务人员在不需要另外安装程序软件和插件，并且不需要掌握很多计算机专业知识与技能的情况下，能够方便快捷地从财务软件的数据库中获取所需要的数据。

2     程序设计思路

科目余额表是企业财务管理中一种常用的、不可或缺的工具。通过科目余额表，可以分析填列会计报表，如资产负债表、利润表及报表附注等；可以帮助企业实时了解最新的财务状况、盈亏情况；可以对企业的运营状况及时进行分析和比较，找出存在的问题和风险。

目前国内主流的财务软件的数据管理是通过数据库来支撑的，使用的数据库主要有SQL Server、MySQL、Access、Oracle等。财务软件是一个平台，但其不存储数据，数据是存放在上述数据库中的。在这些财务软件所使用的数据库中，并不存放任意会计期间的科目余额表，而是存放一个个单独月份的科目总账表。财务软件所调取出来的科目余额表是通过科目总账及其他相关财务数据加工整理后展现出来的。以银行存款、未分配利润、实收资本3个科目为例，2023年1—6月每月的科目总账如表1所示。

我们所需要的2023年1—6月科目余额表的格式如表2所示。

通过分析上述两表的异同点，我们发现可以进行这些操作：期初方向、期末方向可以用金额的正负数来表示，如期初方向为借，其金额为正数；期初方向为贷，则金额为负数。期末方向与金额同理。如此设定有利于进行程序设计和计算。同时还有以下发现：

a. 可以选取2023年1月科目总账的期初借方金额作为2023年1—6月科目余额表的期初借方金额；

b. 可以选取2023年1月科目总账的期初贷方金额作为2023年1—6月科目余额表的期初贷方金额；

c. 可以选取2023年1—6月科目总账的每个会计科目6个月的借方金额、贷方金额的累计相加之和分别作为2023年1—6月科目余额表的本期借方金额、本期贷方金额；

d. 可以选取2023年6月科目总账的期末借方金额作为2023年1—6月科目余额表的期末借方金额。

e. 可以选取2023年6月科目总账的期末贷方金额作为2023年1—6月科目余额表的期末贷方金额。

按照上述思路，通过在数据库中用SQL的select语句选取5项数据即可获得2023年1—6月科目余额表的各项关键内容，然后通过数据透视表等功能加工整理表格，即可完成科目余额表的编制。

3     VBA与SQL语言的程序设计

3.1   了解会计软件的在数据库中的基本情况

通过向公司IT网络运维人员询问可以得到财务数据库sa账号的密码、数据库的IP地址等信息。通过查询会计软件的相关说明书，并实际打开数据库查询，可以得知会计账套在数据库中的名称，科目总账在数据库中的表名，科目总账的科目编码、期初借方金额、期初贷方金额、本期借方金额、本期贷方金额、期末借方金额、期末贷方金额等项目在数据库中的构成、字段名称等一系列信息。

由于不同会计软件以及相同会计软件的不同版本在数据库中存放的表名和字段名都不相同，因此本文谨以科目余额表中各要素的中文名直接表示这些表名和字段名，以便于阅读和理解。实际运行中需要使用数据库中实际查询到的英文字母、数字、特殊符号等组合所代表的表名、字段名。

3.2     VBA及SQL语言程序设计的主要步骤与内容

VBA是visual Basic for application的缩写，是一种面向对象的编程语言，只需要打开任意一个Excel文件，在键盘上同时按下ALT+F11，即可快捷打開VBA程序的编辑界面，也可以通过窗口操作等方法打开。

SQL是Structured Query Language的缩写，是一种专门用来与数据库沟通的语言［1］。SQL的功能很强大，使用SQL命令即可以对数据库进行数据查询。

第一步：定义账套在数据库中的各类变量。

将会计软件在财务数据库的中各种信息赋予VBA程序的自定义变量，其主要程序代码如下：

Dim MySQL$， IPfwq$，AccountSet$， MimaSQL$

Dim i&， j&， hs&， SQLArry（5） As String

IPfwq = "192.***.***.***" '（账套所在数据库的IP地址，“*” 的地方需要用公司的实际数据代替，下同）

AccountSet = "ACC *******" '（财务数据库中的账套名称）

MimaSQL = "P********* " '（财务数据库账号sa的密码）

hs = TotalArrows '（获取excel现有数据的行数的自定义函数）

第二步：编写SQL查询命令的语句。

在自定义的文本数组SQLArry（5）中写入5条SQL命令语句，方便后续循环导出所需要的2023年1—6月科目余额表的各项数据，其主要程序代码分别如下：

（1）从数据库中获取2023年1—6月科目余额表的期初借方金额的SQL语言命令为：

SQLArry（1） = "select （月份）， ''''''+科目编码， （期初金额）， null as 借方金额， null as 贷方金额， null as 期末金额 from 科目总账 where 月份 ='202201' and 期初金额方向='借' order by ''''''+科目编码"

（2）从数据库中获取2023年1—6月科目余额表的期初贷方金额的SQL语言命令為：

SQLArry（2） = "select （月份）， ''''''+科目编码， （期初金额）*（-1）， null as 借方金额， null as 贷方金额， null as 期末金额 from 科目总账表名 where 月份 ='202201' and 期初金额方向='贷' order by ''''''+科目编码"

（3）从数据库中获取2023年1—6月科目余额表的本期借方金额与本期贷方金额的SQL语言命令为：

SQLArry（3） = "select 0， ''''''+科目编码， null， sum

（借方金额） as 借方金额， sum （贷方金额） as 贷方金额， null as 期末金额 from 科目总账表名 where 月份  between '202201' and '202209' group by ''''''+科目编码 order by ''''''+科目编码"

（4）从数据库中获取2023年1—6月科目余额表的期末借方金额的SQL语言命令为：

SQLArry（4） = "select （月份）， ''''''+科目编码， null， null as 借方金额， null as 贷方金额， sum（期末金额） as 期末金额 from 科目总账表名 where 月份 ='202209' and 期末方向='借' order by ''''''+科目编码"

（5）从数据库中获取2023年1—6月科目余额表期末贷方金额的SQL语言命令为：

SQLArry（5） = "select （月份）， ''''''+科目编码， null， null as 借方金额， null as 贷方金额， sum（期末金额）*（-1） as 期末金额 from 科目总账表名 where 月份 ='202209' and 期末方向='贷' order by ''''''+科目编码"

第三步：使用ADO建立 SQL Server数据库与Excel工作簿之间的连接。

ADO是Active Data Object的缩写，它是一个访问数据库的编程接口［2］。在使用ADO的时候，需要先引用ADO对象库，在VBA程序编辑窗口打开“工具”—“引用”，勾选上Microsoft ActiveX Data Objects 2.* library。2.* 是版本号，实际操作中根据自己计算机的显示的情况予以勾选。

VBA中可以使用如连接（Connection）、记录集（Recordset）等ADO对象来查询满足条件的数据，其主要程序代码如下：

Dim myCnn As New ADODB.Connection

Dim myRest As ADODB.Recordset

Set myRest = New ADODB.Recordset

With myCnn

.Provider = "SQLOLEDB"

.ConnectionString = "Server=" & IPfwq & ";Database="

& AccountSet & ";Uid=sa;Pwd=" & MimaSQL

.Open

End With

第四步：循环程序执行SQL 的Select命令提取数据。

通过运行程序执行SQL命令，从数据库导出所需要的2023年1—6月科目余额表的各项内容，在Excel文件里新建一个“取数”的sheet，用于接收上述导出的数据，其主要程序代码如下：

For j = 1 To 5

mySQL = SQLArry（j）

Set myRest = New ADODB.Recordset

myRest.Open mySQL， myCnn， adOpenStatic， adLockReadOnly

hs = TotalArrows

Sheets（"取数"）.Cells（1+hs， 1）. CopyFrom

Recordset myRest

Next j

运行上述程序，可以导出2023年1—6月的科目总账所需要的会计期间、科目代码、期初金额、本期借方金额、本期贷方金额、期末金额等6项数据，科目名称并不存放在科目总账里，需要另外调取数据然后匹配出来。此处为方便阅读理解将其展现出来。导出数据的形式如表3所示。

第五步：创建透视表，将从财务数据库中提取的数据汇总整理。

从表3可以看出，导出的2023年1—6月的科目总账，其期初金额、本期借方金额、本期贷方金额、期末金额等数据的形式类似一个分块对角矩阵，不便于数据分析，因此需要运用数据透视表进行汇总整理。

若在Excel里通过手工方式一步步操作插入数据透视表，虽然也比较快捷，但也需要耗时约1分钟才能设置好，而通过VBA程序制作的数据透视表对上述第四步导出的数据进行汇总与整理，则很迅速。运行上述程序，然后稍加调整后即可制作完成如表2所示格式的科目余额表。

上述五步所编写的VBA程序虽然篇幅较多，但提取一家公司的科目余额表整个程序運行的耗时只需大约3秒的时间。

4     运用窗体与控件进行批量查询

为便于操作和执行上述VBA程序，人与计算机友好地交互信息，可以在Excel中创建一些用户窗体来执行上述程序。窗体和控件是Excel VBA中最重要的对象，是构成应用程序界面的基本模块［3］。在窗体中使用合理恰当的各种控件来定义上述程序中的变量、设置查询条件，就可以为财务人员提供一些美观、友好的人机交互的界面。财务人员只需通过点击窗体中的相关按钮和控件，就可从数据库中批量获取所需要的任意选定的单一或多个公司的科目余额表。窗体和控件的设计样式如图1所示。

将各个公司的账套在数据库里账套名、IP地址、sa密码等参数赋予图1中的控件以后，通过逐一点击图1中窗体上的控件，即可以选择好所需要的任意一个或一批公司的财务账套，为下一步工作做好准备。

在财务人员日常工作中，需要调取不仅有科目余额表，还有记账凭证、明细账、账龄分析表、会计报表等一系列相关的报表。将这些报表的所需要的相关程序分别赋予一个命名为“会计报表选择”窗体中的每一个控件。点击“会计报表选择”窗体中的科目余额表的控件，即可以将与科目余额表相关的程序调入准备好，为下一步工作做好准备。

将科目余额表所需要的会计期间、会计科目范围、是否选择未记账凭证等自定义的需求赋予图2窗体中的控件。通过点击图2窗体中的各个控件，即可以通过对话框输入所需要的会计期间以及特定需要的内容。

上述窗体的控件点击、选择完毕之后，即可开始批量查询任意一个或多个公司的科目余额表及相关报表。为防止下一个公司的数据将前一个的数据覆盖，需要将前述“取数”的sheet里已经整理好的科目余额表等数据复制到一个专门的“科目余额表”sheet里，注意要从已有数据的最后一行开始粘贴。

按1个公司科目余额表耗时3秒计算，批量查询100个公司的科目余额表只需要约5分钟，相比手工登录财务账套逐一查询100家公司的科目余额表所需要的8个多小时，提取财务数据的速度提高了大约100倍。

5     结束语

本文以科目余额表为例，运用Excel的VBA与SQL语言并结合窗体、控件功能进行程序设计，能方便快捷地从数据库中批量提取所需要的各公司的科目余额表等财务数据。整个操作过程灵活、人机交互友好，能够实现跨账套进行智能化的批量查询，从而为财务人员的账务查询、数据分析、合并报表等工作提供技术支持，并节省大量时间，大幅提高工作效率。

主要参考文献

［1］Ben Forta. SQL必知必会［M］.第4版.钟鸣，刘晓霞，译.北京：人民邮电出版社，2013.

［2］韩小良. Excel VBA+SQL数据管理与应用模板［M］.北京：中国水利水电出版社，2019.

［3］韩小良. Excel VBA快速入门数据处理实战技巧精粹［M］.北京：中国水利水电出版社，2019.

［收稿日期］2023-07-05

［作者简介］朱家银（1982— ），男，湖北黄梅人，硕士，注册会计师、中级会计师、税务师，主要研究方向：财务报告、信息披露、财务信息化。