基于Ｅｘｃｅｌ ＶＢＡ的動(dòng)態(tài)數(shù)據(jù)透視表技術(shù)在醫(yī)院會(huì)計(jì)工作中的高效應(yīng)用

［摘 要］ 基于Excel VBA的動(dòng)態(tài)數(shù)據(jù)透視表技術(shù)可滿足日常會(huì)計(jì)工作需求，從而彌補(bǔ)了醫(yī)院財(cái)務(wù)軟件統(tǒng)計(jì)、分析、匯總等功能的不足。本文利用Excel VBA自動(dòng)控制數(shù)據(jù)透視表技術(shù)動(dòng)態(tài)生成醫(yī)院會(huì)計(jì)科目試算平衡表，以避免會(huì)計(jì)憑證電算化錄入工作中的錯(cuò)誤，并比對(duì)分析醫(yī)院會(huì)計(jì)科目總賬和明細(xì)賬數(shù)據(jù)在一段時(shí)間內(nèi)的變化特征，以便及時(shí)、準(zhǔn)確、高效地為領(lǐng)導(dǎo)層提供決策依據(jù)。

［關(guān)鍵詞］ Excel；VBA；數(shù)據(jù)透視表；醫(yī)院會(huì)計(jì)

［中圖分類號(hào)］F232［文獻(xiàn)標(biāo)識(shí)碼］A［文章編號(hào)］1673-0194（2008）01-0030-03

專門的醫(yī)院會(huì)計(jì)電算化軟件在處理醫(yī)院會(huì)計(jì)數(shù)據(jù)的過程中實(shí)現(xiàn)了流程化，而Excel主要用于實(shí)現(xiàn)專門的會(huì)計(jì)電算化軟件所無法實(shí)現(xiàn)的功能，如對(duì)醫(yī)院會(huì)計(jì)數(shù)據(jù)的動(dòng)態(tài)處理、預(yù)測(cè)、分析、加工、統(tǒng)計(jì)匯總等。Excel已經(jīng)在各醫(yī)院會(huì)計(jì)核算中有不同程度的應(yīng)用，但大多是基于會(huì)計(jì)人員手工操作的，而且多是重復(fù)操作，步驟煩瑣，這樣既影響了工作效率，又不能快速、準(zhǔn)確地生成有價(jià)值的會(huì)計(jì)信息。因此熟練掌握Excel中內(nèi)嵌的VBA技術(shù)，不但能提高日常會(huì)計(jì)工作質(zhì)量，而且可以Excel VBA加載宏的形式，一次生成特定的工作模板，多次使用，提高了會(huì)計(jì)日常工作效率，為確保會(huì)計(jì)信息質(zhì)量以及試算平衡提供了最基礎(chǔ)的保障。本文利用Excel VBA自動(dòng)控制數(shù)據(jù)透視表技術(shù)動(dòng)態(tài)生成醫(yī)院會(huì)計(jì)科目試算平衡表，以避免電算化會(huì)計(jì)憑證錄入工作中的錯(cuò)誤，并比對(duì)分析醫(yī)院會(huì)計(jì)科目數(shù)據(jù)在一段時(shí)間內(nèi)的變化特征，以便及時(shí)、準(zhǔn)確、高效地為領(lǐng)導(dǎo)層提供決策依據(jù)。

1 資料來源

資料來源于某醫(yī)院2006年AC90數(shù)據(jù)庫，本例提取了會(huì)計(jì)原始記賬憑證32 679條記錄。

2 關(guān)鍵技術(shù)與方法

2. 1數(shù)據(jù)源的獲取

Excel內(nèi)嵌的VBA具有強(qiáng)大的后臺(tái)數(shù)據(jù)庫連接功能，可支持Oracle、SQL Server大型數(shù)據(jù)庫系統(tǒng)，直接讀取AC90、用友等財(cái)務(wù)系統(tǒng)后臺(tái)數(shù)據(jù)庫。本文在VBA編輯器工程中添加兩個(gè)通用模塊（SetUp Trial Balance，SetUp Pivot），如圖1所示。

以下是生成會(huì)計(jì)科目試算平衡表的通用模塊：

Option Explicit

Sub SetUpTrialBalance（）

On Error Resume Next

ActiveWorkbook.Sheets.Add

Dim connstring As String

connstring=\"ODBC；DRIVER=SQL Native Client；SERVER=（local）；\" \"UID=用戶名；PWD=登錄密碼；Trusted_Connection=Yes；APP=Microsoft Office 2003；\"

\"WSID=；DATABASE=數(shù)據(jù)庫名；\"http://根據(jù)單位實(shí)際情況填入后臺(tái)數(shù)據(jù)庫連接用戶名、密碼以及數(shù)據(jù)庫名。

Dim sqlstring As String

sqlstring =\"select * from 會(huì)計(jì)憑證表 where （年份=2006 or 年份=2005） and 月份=1\"http://根據(jù)實(shí)際工作需要填入組合SQL查詢語句，本文采用的示例是為說明清楚。

With ActiveSheet.QueryTables.Add（Connection:=connstring，Destination:=Range（\"A1\"），Sql:=sqlstring）

.Refresh

End With

Call SetUpPivot

End Sub

Sub SetUpPivot（）

Dim r As Range

Range（\"A1\"）.CurrentRegion.Select

Selection.Name=\"TrialBalance\"

ActiveWorkbook.Sheets.Add

Dim PTcache As PivotCache

Dim PT As PivotTable

Dim Prange As Range

For Each PT In ActiveWorkbook.ActiveSheet.PivotTables

PT.TableRange2.Clear

Next PT

Set Prange = Range（\"TrialBalance\"）

Set PTcache = ActiveWorkbook.PivotCaches.Add（xlDatabase，SourceData:=Prange）

Set PT = PTcache.CreatePivotTable（TableDe-stination:=Range（\"A3\"），tablename：=\"PivotTable1\"）

PT.AddFields RowFields:=Array（\"科目代碼\"，\"科目名稱\"），ColumnFields:=Array（\"年份\"，\"月份\"）

With PT.PivotFields（\"借方金額\"）

.Orientation = xlDataField

.Function = xlSum

.Position = 1

End With

With PT.PivotFields（\"貸方金額\"）

.Orientation = xlDataField

.Function = xlSum

.Position = 2

End With

With PT.PivotFields（\"數(shù)據(jù)\"）

.Orientation = xlColumnField

.Position = 3

End With

PT.RowGrand = False

PT.PivotFields（\"年份\"）.Subtotals=Array（False，F(xiàn)alse，F(xiàn)alse， False， False， False， False， False， False， False， False，F(xiàn)alse）

PT.PivotFields（\"月份\"）.Subtotals=Array（False，F(xiàn)alse，F(xiàn)alse， False， False， False， False， False， False， False， False，F(xiàn)alse）

PT.PivotFields（\"科目名稱\"）.Subtotals=Array（False，F(xiàn)alse， False， False， False， False， False， False， False， False，F(xiàn)alse）

ActiveWorkbook.ShowPivotTableFieldList=False

End Sub

運(yùn)行上面的通用模塊，最終生成的數(shù)據(jù)表格及透視表如圖2、圖3所示。

2. 2醫(yī)院實(shí)際應(yīng)用

由于醫(yī)院財(cái)務(wù)電算化軟件有其自身的局限性，因而充分應(yīng)用Excel VBA的動(dòng)態(tài)數(shù)據(jù)透視表技術(shù)，從某院的應(yīng)用實(shí)踐來看，效果較好，既可對(duì)動(dòng)態(tài)產(chǎn)生的醫(yī)院科目平衡表進(jìn)行匯總分析，也可避免會(huì)計(jì)登賬等錯(cuò)誤。這樣每月只需導(dǎo)出會(huì)計(jì)憑證原始數(shù)據(jù)集，即可自動(dòng)生成科目匯總表、明細(xì)總表。通過此工作簿可以大大減少月統(tǒng)計(jì)、匯總、分析等工作量，及時(shí)完成會(huì)計(jì)報(bào)表，并可為查詢、核對(duì)及登記賬簿提供方便。應(yīng)用Excel VBA控制數(shù)據(jù)透視表技術(shù)，可簡化醫(yī)院會(huì)計(jì)工作程序，可在醫(yī)院會(huì)計(jì)工作的諸多方面得到廣泛應(yīng)用，如在動(dòng)態(tài)數(shù)據(jù)透視表中結(jié)合Spread Sheet建模方法等，可在醫(yī)院財(cái)務(wù)管理模型分析中，在醫(yī)院財(cái)務(wù)預(yù)算、預(yù)測(cè)、報(bào)表數(shù)據(jù)比較分析、決策中，在醫(yī)院財(cái)務(wù)動(dòng)態(tài)報(bào)表生成中廣泛應(yīng)用。

3 體 會(huì)

（1）Excel很容易應(yīng)用，可按照要求生成復(fù)雜多樣的財(cái)務(wù)分析表，可以直接從一個(gè)或多個(gè)數(shù)據(jù)庫表中提取數(shù)據(jù)。

（2）數(shù)據(jù)透視表是動(dòng)態(tài)產(chǎn)生的并且功能非常強(qiáng)大，可自動(dòng)也可手動(dòng)生成，并且與目前的會(huì)計(jì)電算化系統(tǒng)使用無關(guān)。

（3）數(shù)據(jù)透視表有很好的成本效應(yīng)，不需要購置專門的分析軟件。

（4）數(shù)據(jù)透視表開發(fā)出來的分析表快捷、高效、準(zhǔn)確。

（5）數(shù)據(jù)透視表是一個(gè)自定義表格，允許使用者對(duì)源數(shù)據(jù)清單中的字段按多種方式組合，行列可以轉(zhuǎn)置，可以對(duì)數(shù)據(jù)進(jìn)行全方位的透視。不管對(duì)數(shù)據(jù)怎樣重排，數(shù)據(jù)透視表都會(huì)自動(dòng)篩選、分類和匯總，即刻得到源數(shù)據(jù)的不同匯總及分析結(jié)果。

4 結(jié)束語

善于靈活運(yùn)用VBA控制數(shù)據(jù)透視表功能，做成加載宏的通用格式XLA文件，可以快速、高效地完成日常工作中的多種會(huì)計(jì)、統(tǒng)計(jì)分析任務(wù)，補(bǔ)充目前醫(yī)院財(cái)務(wù)電算化系統(tǒng)及HIS系統(tǒng)在數(shù)據(jù)分析、數(shù)據(jù)挖掘功能方面的不足，可直接提取醫(yī)院財(cái)務(wù)電算化系統(tǒng)后臺(tái)數(shù)據(jù)庫，也可通過醫(yī)院財(cái)務(wù)電算化系統(tǒng)的數(shù)據(jù)導(dǎo)出功能獲得數(shù)據(jù)源，再用Excel VBA的動(dòng)態(tài)數(shù)據(jù)透視表技術(shù)進(jìn)行透視分析。

主要參考文獻(xiàn)

［1］ ［美］Timonthy Zapawa. Excel Advanced Report Development［M］. 北京：電子工業(yè)出版社，2005.

［2］ ［美］John Walkenbach. Excel 2003 Power Programming with VBA［M］. 北京：電子工業(yè)出版社，2005.

［3］ ［美］Steven M Hansen. Mastering Excel 2003 Programming with VBA［M］. 北京：電子工業(yè)出版社，2004.

［4］ ［美］Bill Jelen，Michael Alexander. Pivot Table Data Crunching［M］. Que Publisher，2005.