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

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

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

　　1 问题的提出

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

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

　　2 程序设计思路

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

　　目前国内主流的财务软件的数据管理是通过数据库来支撑的，使用的数据库主要有SQLServer、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"

　　.Connection String = "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.

关注SCI论文创作发表，寻求SCI论文修改润色、SCI论文代发表等服务支撑，请锁定SCI论文网！

文章出自SCI论文网转载请注明出处：https://www.lunwensci.com/guanlilunwen/77420.html