VBA與SQL在數(shù)據(jù)庫與財(cái)務(wù)報(bào)表中的應(yīng)用

［摘 要］財(cái)務(wù)人員在跨賬套查詢科目余額表等財(cái)務(wù)數(shù)據(jù)時(shí)，需要使用財(cái)務(wù)軟件頻繁切換登錄各公司賬套，進(jìn)行一系列重復(fù)而大量的操作。這種查詢操作非常枯燥且耗費(fèi)時(shí)間。運(yùn)用VBA與SQL語言并結(jié)合Excel的窗體與控件的功能進(jìn)行程序設(shè)計(jì)，可以讓財(cái)務(wù)人員僅通過一個(gè)Excel文檔就直接從財(cái)務(wù)數(shù)據(jù)庫中快捷獲得科目余額表等財(cái)務(wù)數(shù)據(jù)。整個(gè)程序操作靈活、人機(jī)交互友好。該程序?qū)崿F(xiàn)了跨賬套智能、快捷批量查詢的功能，可以大幅節(jié)省工作時(shí)間，提高工作效率。

［關(guān)鍵詞］VBA；SQL；數(shù)據(jù)庫；財(cái)務(wù)賬套；科目余額表；財(cái)務(wù)報(bào)表

1? ? ?問題的提出

很多公司在發(fā)展過程中，隨著業(yè)務(wù)規(guī)模的擴(kuò)大，會(huì)相繼設(shè)立或并購很多分子公司，導(dǎo)致該公司財(cái)務(wù)人員會(huì)面對(duì)很多分子公司的賬套。財(cái)務(wù)人員在日常的工作中需要經(jīng)常在各分子公司的賬套查詢一些數(shù)據(jù)，如科目余額表等。查詢一次科目余額表，就需要切換登錄公司賬套，選定會(huì)計(jì)期間與會(huì)計(jì)科目等參數(shù)、執(zhí)行會(huì)計(jì)軟件查詢，輸出結(jié)果到Excel并重命名等一系列操作。受網(wǎng)絡(luò)速度、財(cái)務(wù)軟件運(yùn)行速度、Excel軟件運(yùn)行速度、計(jì)算機(jī)反應(yīng)速度等因素影響，這一套組合操作流程大約需要5分鐘時(shí)間。以此類推，若需查詢輸出100家公司的科目余額表，則至少需要持續(xù)工作8個(gè)多小時(shí)，整個(gè)過程顯得特別重復(fù)枯燥又相當(dāng)耗費(fèi)時(shí)間。

鑒于財(cái)務(wù)人員在日常工作中使用Excel較多，本文嘗試運(yùn)用Excel的VBA功能并結(jié)合SQL語言來設(shè)計(jì)一套程序，以便財(cái)務(wù)人員在不需要另外安裝程序軟件和插件，并且不需要掌握很多計(jì)算機(jī)專業(yè)知識(shí)與技能的情況下，能夠方便快捷地從財(cái)務(wù)軟件的數(shù)據(jù)庫中獲取所需要的數(shù)據(jù)。

2? ? ?程序設(shè)計(jì)思路

科目余額表是企業(yè)財(cái)務(wù)管理中一種常用的、不可或缺的工具。通過科目余額表，可以分析填列會(huì)計(jì)報(bào)表，如資產(chǎn)負(fù)債表、利潤(rùn)表及報(bào)表附注等；可以幫助企業(yè)實(shí)時(shí)了解最新的財(cái)務(wù)狀況、盈虧情況；可以對(duì)企業(yè)的運(yùn)營(yíng)狀況及時(shí)進(jìn)行分析和比較，找出存在的問題和風(fēng)險(xiǎn)。

目前國內(nèi)主流的財(cái)務(wù)軟件的數(shù)據(jù)管理是通過數(shù)據(jù)庫來支撐的，使用的數(shù)據(jù)庫主要有SQL Server、MySQL、Access、Oracle等。財(cái)務(wù)軟件是一個(gè)平臺(tái)，但其不存儲(chǔ)數(shù)據(jù)，數(shù)據(jù)是存放在上述數(shù)據(jù)庫中的。在這些財(cái)務(wù)軟件所使用的數(shù)據(jù)庫中，并不存放任意會(huì)計(jì)期間的科目余額表，而是存放一個(gè)個(gè)單獨(dú)月份的科目總賬表。財(cái)務(wù)軟件所調(diào)取出來的科目余額表是通過科目總賬及其他相關(guān)財(cái)務(wù)數(shù)據(jù)加工整理后展現(xiàn)出來的。以銀行存款、未分配利潤(rùn)、實(shí)收資本3個(gè)科目為例，2023年1—6月每月的科目總賬如表1所示。

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

通過分析上述兩表的異同點(diǎn)，我們發(fā)現(xiàn)可以進(jìn)行這些操作：期初方向、期末方向可以用金額的正負(fù)數(shù)來表示，如期初方向?yàn)榻瑁浣痤~為正數(shù)；期初方向?yàn)橘J，則金額為負(fù)數(shù)。期末方向與金額同理。如此設(shè)定有利于進(jìn)行程序設(shè)計(jì)和計(jì)算。同時(shí)還有以下發(fā)現(xiàn)：

a. 可以選取2023年1月科目總賬的期初借方金額作為2023年1—6月科目余額表的期初借方金額；

b. 可以選取2023年1月科目總賬的期初貸方金額作為2023年1—6月科目余額表的期初貸方金額；

c. 可以選取2023年1—6月科目總賬的每個(gè)會(huì)計(jì)科目6個(gè)月的借方金額、貸方金額的累計(jì)相加之和分別作為2023年1—6月科目余額表的本期借方金額、本期貸方金額；

d. 可以選取2023年6月科目總賬的期末借方金額作為2023年1—6月科目余額表的期末借方金額。

e. 可以選取2023年6月科目總賬的期末貸方金額作為2023年1—6月科目余額表的期末貸方金額。

按照上述思路，通過在數(shù)據(jù)庫中用SQL的select語句選取5項(xiàng)數(shù)據(jù)即可獲得2023年1—6月科目余額表的各項(xiàng)關(guān)鍵內(nèi)容，然后通過數(shù)據(jù)透視表等功能加工整理表格，即可完成科目余額表的編制。

3? ? ?VBA與SQL語言的程序設(shè)計(jì)

3.1? ?了解會(huì)計(jì)軟件的在數(shù)據(jù)庫中的基本情況

通過向公司IT網(wǎng)絡(luò)運(yùn)維人員詢問可以得到財(cái)務(wù)數(shù)據(jù)庫sa賬號(hào)的密碼、數(shù)據(jù)庫的IP地址等信息。通過查詢會(huì)計(jì)軟件的相關(guān)說明書，并實(shí)際打開數(shù)據(jù)庫查詢，可以得知會(huì)計(jì)賬套在數(shù)據(jù)庫中的名稱，科目總賬在數(shù)據(jù)庫中的表名，科目總賬的科目編碼、期初借方金額、期初貸方金額、本期借方金額、本期貸方金額、期末借方金額、期末貸方金額等項(xiàng)目在數(shù)據(jù)庫中的構(gòu)成、字段名稱等一系列信息。

由于不同會(huì)計(jì)軟件以及相同會(huì)計(jì)軟件的不同版本在數(shù)據(jù)庫中存放的表名和字段名都不相同，因此本文謹(jǐn)以科目余額表中各要素的中文名直接表示這些表名和字段名，以便于閱讀和理解。實(shí)際運(yùn)行中需要使用數(shù)據(jù)庫中實(shí)際查詢到的英文字母、數(shù)字、特殊符號(hào)等組合所代表的表名、字段名。

3.2? ? ?VBA及SQL語言程序設(shè)計(jì)的主要步驟與內(nèi)容

VBA是visual Basic for application的縮寫，是一種面向?qū)ο蟮木幊陶Z言，只需要打開任意一個(gè)Excel文件，在鍵盤上同時(shí)按下ALT+F11，即可快捷打開VBA程序的編輯界面，也可以通過窗口操作等方法打開。

SQL是Structured Query Language的縮寫，是一種專門用來與數(shù)據(jù)庫溝通的語言［1］。SQL的功能很強(qiáng)大，使用SQL命令即可以對(duì)數(shù)據(jù)庫進(jìn)行數(shù)據(jù)查詢。

第一步：定義賬套在數(shù)據(jù)庫中的各類變量。

將會(huì)計(jì)軟件在財(cái)務(wù)數(shù)據(jù)庫的中各種信息賦予VBA程序的自定義變量，其主要程序代碼如下：

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

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

IPfwq = "192.***.***.***" '（賬套所在數(shù)據(jù)庫的IP地址，“*” 的地方需要用公司的實(shí)際數(shù)據(jù)代替，下同）

AccountSet = "ACC *******" '（財(cái)務(wù)數(shù)據(jù)庫中的賬套名稱）

MimaSQL = "P********* " '（財(cái)務(wù)數(shù)據(jù)庫賬號(hào)sa的密碼）

hs = TotalArrows '（獲取excel現(xiàn)有數(shù)據(jù)的行數(shù)的自定義函數(shù)）

第二步：編寫SQL查詢命令的語句。

在自定義的文本數(shù)組SQLArry（5）中寫入5條SQL命令語句，方便后續(xù)循環(huán)導(dǎo)出所需要的2023年1—6月科目余額表的各項(xiàng)數(shù)據(jù)，其主要程序代碼分別如下：

（1）從數(shù)據(jù)庫中獲取2023年1—6月科目余額表的期初借方金額的SQL語言命令為：

SQLArry（1） = "select （月份）， ''''''+科目編碼， （期初金額）， null as 借方金額， null as 貸方金額， null as 期末金額 from 科目總賬 where 月份 ='202201' and 期初金額方向='借' order by ''''''+科目編碼"

（2）從數(shù)據(jù)庫中獲取2023年1—6月科目余額表的期初貸方金額的SQL語言命令為：

SQLArry（2） = "select （月份）， ''''''+科目編碼， （期初金額）*（-1）， null as 借方金額， null as 貸方金額， null as 期末金額 from 科目總賬表名 where 月份 ='202201' and 期初金額方向='貸' order by ''''''+科目編碼"

（3）從數(shù)據(jù)庫中獲取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）從數(shù)據(jù)庫中獲取2023年1—6月科目余額表的期末借方金額的SQL語言命令為：

SQLArry（4） = "select （月份）， ''''''+科目編碼， null， null as 借方金額， null as 貸方金額， sum（期末金額） as 期末金額 from 科目總賬表名 where 月份 ='202209' and 期末方向='借' order by ''''''+科目編碼"

（5）從數(shù)據(jù)庫中獲取2023年1—6月科目余額表期末貸方金額的SQL語言命令為：

SQLArry（5） = "select （月份）， ''''''+科目編碼， null， null as 借方金額， null as 貸方金額， sum（期末金額）*（-1） as 期末金額 from 科目總賬表名 where 月份 ='202209' and 期末方向='貸' order by ''''''+科目編碼"

第三步：使用ADO建立 SQL Server數(shù)據(jù)庫與Excel工作簿之間的連接。

ADO是Active Data Object的縮寫，它是一個(gè)訪問數(shù)據(jù)庫的編程接口［2］。在使用ADO的時(shí)候，需要先引用ADO對(duì)象庫，在VBA程序編輯窗口打開“工具”—“引用”，勾選上Microsoft ActiveX Data Objects 2.* library。2.* 是版本號(hào)，實(shí)際操作中根據(jù)自己計(jì)算機(jī)的顯示的情況予以勾選。

VBA中可以使用如連接（Connection）、記錄集（Recordset）等ADO對(duì)象來查詢滿足條件的數(shù)據(jù)，其主要程序代碼如下：

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

第四步：循環(huán)程序執(zhí)行SQL 的Select命令提取數(shù)據(jù)。

通過運(yùn)行程序執(zhí)行SQL命令，從數(shù)據(jù)庫導(dǎo)出所需要的2023年1—6月科目余額表的各項(xiàng)內(nèi)容，在Excel文件里新建一個(gè)“取數(shù)”的sheet，用于接收上述導(dǎo)出的數(shù)據(jù)，其主要程序代碼如下：

For j = 1 To 5

mySQL = SQLArry（j）

Set myRest = New ADODB.Recordset

myRest.Open mySQL， myCnn， adOpenStatic， adLockReadOnly

hs = TotalArrows

Sheets（"取數(shù)"）.Cells（1+hs， 1）. CopyFrom

Recordset myRest

Next j

運(yùn)行上述程序，可以導(dǎo)出2023年1—6月的科目總賬所需要的會(huì)計(jì)期間、科目代碼、期初金額、本期借方金額、本期貸方金額、期末金額等6項(xiàng)數(shù)據(jù)，科目名稱并不存放在科目總賬里，需要另外調(diào)取數(shù)據(jù)然后匹配出來。此處為方便閱讀理解將其展現(xiàn)出來。導(dǎo)出數(shù)據(jù)的形式如表3所示。

第五步：創(chuàng)建透視表，將從財(cái)務(wù)數(shù)據(jù)庫中提取的數(shù)據(jù)匯總整理。

從表3可以看出，導(dǎo)出的2023年1—6月的科目總賬，其期初金額、本期借方金額、本期貸方金額、期末金額等數(shù)據(jù)的形式類似一個(gè)分塊對(duì)角矩陣，不便于數(shù)據(jù)分析，因此需要運(yùn)用數(shù)據(jù)透視表進(jìn)行匯總整理。

若在Excel里通過手工方式一步步操作插入數(shù)據(jù)透視表，雖然也比較快捷，但也需要耗時(shí)約1分鐘才能設(shè)置好，而通過VBA程序制作的數(shù)據(jù)透視表對(duì)上述第四步導(dǎo)出的數(shù)據(jù)進(jìn)行匯總與整理，則很迅速。運(yùn)行上述程序，然后稍加調(diào)整后即可制作完成如表2所示格式的科目余額表。

上述五步所編寫的VBA程序雖然篇幅較多，但提取一家公司的科目余額表整個(gè)程序運(yùn)行的耗時(shí)只需大約3秒的時(shí)間。

4? ? ?運(yùn)用窗體與控件進(jìn)行批量查詢

為便于操作和執(zhí)行上述VBA程序，人與計(jì)算機(jī)友好地交互信息，可以在Excel中創(chuàng)建一些用戶窗體來執(zhí)行上述程序。窗體和控件是Excel VBA中最重要的對(duì)象，是構(gòu)成應(yīng)用程序界面的基本模塊［3］。在窗體中使用合理恰當(dāng)?shù)母鞣N控件來定義上述程序中的變量、設(shè)置查詢條件，就可以為財(cái)務(wù)人員提供一些美觀、友好的人機(jī)交互的界面。財(cái)務(wù)人員只需通過點(diǎn)擊窗體中的相關(guān)按鈕和控件，就可從數(shù)據(jù)庫中批量獲取所需要的任意選定的單一或多個(gè)公司的科目余額表。窗體和控件的設(shè)計(jì)樣式如圖1所示。

將各個(gè)公司的賬套在數(shù)據(jù)庫里賬套名、IP地址、sa密碼等參數(shù)賦予圖1中的控件以后，通過逐一點(diǎn)擊圖1中窗體上的控件，即可以選擇好所需要的任意一個(gè)或一批公司的財(cái)務(wù)賬套，為下一步工作做好準(zhǔn)備。

在財(cái)務(wù)人員日常工作中，需要調(diào)取不僅有科目余額表，還有記賬憑證、明細(xì)賬、賬齡分析表、會(huì)計(jì)報(bào)表等一系列相關(guān)的報(bào)表。將這些報(bào)表的所需要的相關(guān)程序分別賦予一個(gè)命名為“會(huì)計(jì)報(bào)表選擇”窗體中的每一個(gè)控件。點(diǎn)擊“會(huì)計(jì)報(bào)表選擇”窗體中的科目余額表的控件，即可以將與科目余額表相關(guān)的程序調(diào)入準(zhǔn)備好，為下一步工作做好準(zhǔn)備。

將科目余額表所需要的會(huì)計(jì)期間、會(huì)計(jì)科目范圍、是否選擇未記賬憑證等自定義的需求賦予圖2窗體中的控件。通過點(diǎn)擊圖2窗體中的各個(gè)控件，即可以通過對(duì)話框輸入所需要的會(huì)計(jì)期間以及特定需要的內(nèi)容。

上述窗體的控件點(diǎn)擊、選擇完畢之后，即可開始批量查詢?nèi)我庖粋€(gè)或多個(gè)公司的科目余額表及相關(guān)報(bào)表。為防止下一個(gè)公司的數(shù)據(jù)將前一個(gè)的數(shù)據(jù)覆蓋，需要將前述“取數(shù)”的sheet里已經(jīng)整理好的科目余額表等數(shù)據(jù)復(fù)制到一個(gè)專門的“科目余額表”sheet里，注意要從已有數(shù)據(jù)的最后一行開始粘貼。

按1個(gè)公司科目余額表耗時(shí)3秒計(jì)算，批量查詢100個(gè)公司的科目余額表只需要約5分鐘，相比手工登錄財(cái)務(wù)賬套逐一查詢100家公司的科目余額表所需要的8個(gè)多小時(shí)，提取財(cái)務(wù)數(shù)據(jù)的速度提高了大約100倍。

5? ? ?結(jié)束語

本文以科目余額表為例，運(yùn)用Excel的VBA與SQL語言并結(jié)合窗體、控件功能進(jìn)行程序設(shè)計(jì)，能方便快捷地從數(shù)據(jù)庫中批量提取所需要的各公司的科目余額表等財(cái)務(wù)數(shù)據(jù)。整個(gè)操作過程靈活、人機(jī)交互友好，能夠?qū)崿F(xiàn)跨賬套進(jìn)行智能化的批量查詢，從而為財(cái)務(wù)人員的賬務(wù)查詢、數(shù)據(jù)分析、合并報(bào)表等工作提供技術(shù)支持，并節(jié)省大量時(shí)間，大幅提高工作效率。

主要參考文獻(xiàn)

［1］Ben Forta. SQL必知必會(huì)［M］.第4版.鐘鳴，劉曉霞，譯.北京：人民郵電出版社，2013.

［2］韓小良. Excel VBA+SQL數(shù)據(jù)管理與應(yīng)用模板［M］.北京：中國水利水電出版社，2019.

［3］韓小良. Excel VBA快速入門數(shù)據(jù)處理實(shí)戰(zhàn)技巧精粹［M］.北京：中國水利水電出版社，2019.

［收稿日期］2023-07-05

［作者簡(jiǎn)介］朱家銀（1982— ），男，湖北黃梅人，碩士，注冊(cè)會(huì)計(jì)師、中級(jí)會(huì)計(jì)師、稅務(wù)師，主要研究方向：財(cái)務(wù)報(bào)告、信息披露、財(cái)務(wù)信息化。