Visual Basic處理多財(cái)務(wù)數(shù)據(jù)Excel表格

胡敏，李梅，鄭增國(guó)

（上饒師范學(xué)院網(wǎng)絡(luò)中心，上饒 334001）

0 引言

高校財(cái)務(wù)報(bào)賬各二級(jí)部門都會(huì)將費(fèi)用表格上交給財(cái)務(wù)部門審核，審核通過后才能夠正常的劃賬下發(fā)。以我校為例，我校的專業(yè)經(jīng)費(fèi)報(bào)銷表格每月個(gè)數(shù)平均是在60個(gè)以上，在高峰期時(shí)會(huì)超過100。不同類型費(fèi)用表格經(jīng)常是不同的人員制造，在造表過程中就人名這一基本信息有時(shí)會(huì)出現(xiàn)同音不同字或者同名同姓但不同人員等現(xiàn)象，人工的檢測(cè)和校對(duì)需要耗費(fèi)大量的時(shí)間在重復(fù)的比對(duì)上，雖然Excel表格中有匹配函數(shù)可以減輕工作量，但是頻繁的打開關(guān)閉表格也需要人工的看守，為此通過Visual Basic與Excel的對(duì)接，對(duì)報(bào)銷表格中的人名檢測(cè)、結(jié)構(gòu)檢測(cè)等功能使用Visual Basic程序編輯完成。

1 Excel操作Visual Basic的關(guān)聯(lián)

在VB中有專門連接Excel外部對(duì)象Microsoft Excel 11.0 Object Library對(duì)象庫(kù)。在其中將Excel的結(jié)構(gòu)和VB的結(jié)構(gòu)一一對(duì)應(yīng)。

（1）Excel是以層次結(jié)構(gòu)組織對(duì)象的工作簿、工作表、行列單元格，其對(duì)象模型中含有許多不同的對(duì)象元素。編程過程中主要用到以下4個(gè)層次的對(duì)象，在Vi?sual Basic程序中有專門面向Excel的對(duì)象Excel Appli?cation[1]。

（2）Application 對(duì)象，即 Excel程序本身，Excel Ap?plication對(duì)象表示Excel應(yīng)用程序本身。聲明一個(gè)Ex?cel應(yīng)用程序?qū)ο蟮拇a為Dim xls As New Excel.Appli?cation。

（3）WorkBook對(duì)象，即Excel的工作簿文件對(duì)象；聲明一個(gè)Excel工作薄對(duì)象的代碼為Dim xbook As New Excel.Workbook[2]。

（4）WorkSheets對(duì)象，表示的是Excel的工作表對(duì)象集；

例如：worksheets（1）表示第一個(gè)工作表。聲明一個(gè)Excel工作表象的代碼為：

Dim xsheet As New Excel.Worksheet。

（5）Range對(duì)象：是Excel應(yīng)用程序中最常用的對(duì)象。在能夠處理Excel內(nèi)的任何范圍之前，必須將它表示為Range對(duì)象，并處理該對(duì)象的方法和屬性[3]。

（6）Cells、Range、Rows、Columns 對(duì)象，分別表示Excel工作表中的單元格對(duì)象集、區(qū)域?qū)ο蟆⑿袑?duì)象集、列對(duì)象集[4]。

2 實(shí)例應(yīng)用及主要代碼

我校財(cái)務(wù)報(bào)賬人員姓名檢測(cè)流程如圖1。

圖1

第一步：導(dǎo)入母本：

從人事部門獲得學(xué)校在職教師信息Excel表格，包括姓名、工號(hào)、所在部門。在本系統(tǒng)中稱為母本即憑證文件。導(dǎo)入效果如圖2所示。

圖2

定義人員信息類型：Public Type person_info

xm_info As String'姓名

gonghao_info As String'工號(hào)

End Type

Public zaizhi（）as person_info'將excel中在職人員的信息導(dǎo)入數(shù)組

在VB中定義表格，引用表格

Set xlApp=CreateObject（"Excel.Application"）

Set xlsheet=xlBook.Worksheets（"在職"）

xlsheet.Activate

zaizhi_num=xlsheet.UsedRange.Rows.Count-1

ReDim zaizhi（xlsheet.UsedRange.Rows.Count-1）

'根據(jù)人事部門給的人事Excel表格行數(shù)重新定義zaizhi數(shù)組的大小

For i=1 To xlsheet.UsedRange.Rows.Count-1'向zaizhi數(shù)組導(dǎo)入數(shù)據(jù)包括工號(hào)和姓名

zaizhi（i）.gonghao_info=Trimstring（xlsheet.Cells（i+1，1））'第一列工號(hào)

zaizhi（i）.xm_info=Trimstring（xlsheet.Cells（i+1，2））'第二列姓名

Next i

第二步：獲得待報(bào)銷表格中有效記錄的開始行列以及條數(shù)信息，用于導(dǎo)入數(shù)組。

通過過程 zhiDuanPosition（xlsheet，r，excelfile）獲得表格的行數(shù)和報(bào)銷人員有效記錄開始的位置，包括在Excel中的行列值。并將結(jié)構(gòu)正常并且姓名正確的表格傳入b_to_sz（）過程中轉(zhuǎn)換為數(shù)組，便于后期的匯總分析。在導(dǎo)入的待測(cè)Excel表格中匹配帶有姓字的字段，定位為報(bào)賬人信息的開始包括行列。

For i=1 To R_Count'從待測(cè)Excel表格的第一行開始查找

flag=False'默認(rèn)沒有找到姓名字段

For j=1 To C_Count

If InStr（Trimstring（xlsheet.UsedRange.Cells（i，j）），"姓"）<>0 Then '遍歷該行中的每一個(gè)單元格匹配是否有”姓”字，如果找到表示以下行列為報(bào)賬人員的信息，記錄行列信息用于后面b_sz（）數(shù)組內(nèi)容的導(dǎo)入。

xm_j=j

flag=True

Exit For

End If

Next j

If flag=True Then Exit For‘如果沒有姓字則認(rèn)為該表格的結(jié)構(gòu)異常，沒有報(bào)賬人員姓名字段。

Next i

第三步：將結(jié)構(gòu)正常的Excel表格數(shù)據(jù)信息的導(dǎo)入到數(shù)組b_sz（），篩選出姓名行列信息導(dǎo)入數(shù)組b_szF（）中。

Public Sub b_to_sz（xlsheet As Object）

ReDim b_sz（1 To xlsheet.UsedRange.Rows.Count，1 To xlsheet.UsedRange.Columns.Count）As String‘根據(jù)表格大小先定義二維原始數(shù)組，將Excel中姓名對(duì)應(yīng)列的內(nèi)容和實(shí)發(fā)的金額列的內(nèi)容存入b_sz數(shù)組中，精簡(jiǎn)成b_szF（）數(shù)組。

（1）找出字段開始的第一列zd_start_c For j=1 To UBound（b_sz，2）

If Len（Trimstring（xlsheet.UsedRange.Cells（zd_r，j）））<>0 Then

zd_start_c=j‘

Exit For

End If

Next j

（2）找出字段最后一列zd_end_c

For j=UBound（b_sz，2）To zd_start_c Step-1 '從后往前找字段最后一列zd_end_c

If Len（Trimstring（xlsheet.UsedRange.Cells（zd_r，j）））<>0 Then'從后往前找第一個(gè)不為零的就是字段結(jié)束列

zd_end_c=j

Exit For

End If

Next j

（3）找出最后一個(gè)姓名所在的行xm_end_r

For i=zd_r To UBound（b_sz，1）+1'找出最后一個(gè)姓名所在的行xm_end_r

If Len（Trimstring（xlsheet.UsedRange.Cells（i，xm_c）））=0 Then

xm_end_r=i-1

Exit For

End If

Next i

（4）根據(jù)報(bào)賬人員信息開始的行列，定義精簡(jiǎn)數(shù)組存放報(bào)賬人員姓名

ReDim b_szF（1 To xm_end_r-zd_r+1，1 To zd_end_c

zd_start_c+1）As String'定義精簡(jiǎn)的數(shù)組

For i=zd_r To xm_end_r

b_j=1

For j=zd_start_c To zd_end_c

b_szF（b_i，b_j） =Trimstring（xlsheet.

UsedRange.Cells（i，j））

b_j=b_j+1

End If

Next j

b_i=b_i+1

Next i

第四步：將選擇后的數(shù)組b_szF（）與母本信息核對(duì)，并記錄不規(guī)范的姓名所在行列便于人工核對(duì)。

判斷標(biāo)準(zhǔn)和處理方法見表1。

（1）在母本中查找將待檢測(cè)的姓名，如果姓名不在要記錄在第幾行，便于后期人工核查。

checkName_bsz（zaizhi（），b_szF（），excelfile，Dao_Excel_Name，zd_r）

For i=2 To UBound（b_szF，1） '從內(nèi)容行開始

xm_is_in=False '先設(shè)定待測(cè)姓名不在母本中

For zaizhi_i=1 To UBound（zaizhi） '將待測(cè)姓名與zaizhi數(shù)組中依次對(duì)比查找

If Trimstring（b_szF（i，xm_c））=Trimstring

（zaizhi（zaizhi_i）.xm_info）Then

xm_is_in=True'如果找到了則xm_is_in

設(shè)置為True，并退出循環(huán)

Exit For

End If

Next zaizhi_i

If xm_is_in=False Then '遍歷完zaizhi數(shù)組后，如果xm_is_in仍然為false表示待檢測(cè)’的姓名沒有在人事處給的在職母本中

xm_cw_tishi=xm_cw_tishi&"在第"&i+zd_r-1&"行，沒有找到"&b_szF（i，xm_c）&vbCrLf '將這個(gè)沒在在職人事的姓名的行列和內(nèi)容記錄

End If

Next i

（2）將姓名不在母本中的表格的信息記錄保存在錯(cuò)誤提示.txt文本中，如圖3所示。

圖3

（3）姓名無異常且表格結(jié)構(gòu)正常的表格信息列出，如圖4所示。

圖4

3 結(jié)語

本文講解了Visual Basic操作多張財(cái)務(wù)報(bào)賬Excel表的實(shí)際應(yīng)用，并取得了良好的效果。通過設(shè)計(jì)的Vi?sual Basic界面就能夠操作多個(gè)Excel表格，利用數(shù)組的特性進(jìn)行表結(jié)構(gòu)和保障人員信息檢測(cè)，并記錄異常信息的位置便于人工核對(duì)，大大提高了工作的效率。