Excel VBA技術(shù)在財務工作中的應用

姚哲

[摘 要] Excel函數(shù)提高了財務工作的效率，盡管Excel的版本不斷升級，Excel的函數(shù)庫不斷擴展且功能更加強大，但這并不能解決財務工作中遇到的所有問題。本文應用Excel VBA 編程技術(shù)，一方面編寫了Excel的用戶自定義函數(shù)，彌補Excel函數(shù)的不足，從而可無限擴展Excel函數(shù)庫，另一方面編寫了Excel的宏，可實現(xiàn)Excel函數(shù)無法實現(xiàn)的功能，如單元格各屬性的修改；VBA技術(shù)讓Excel功能無所不能，讓我們的財務工作事半功倍。為展示VBA的強大功能，本文以解決VLOOKUP函數(shù)中關(guān)鍵字在代碼表中重復時的可供選擇這一有亟待解決的問題為例，運行VBA編寫用戶自定義函數(shù)，實現(xiàn)關(guān)鍵字在代碼表中重復時，函數(shù)返回全部相關(guān)值所串連的字符串，然后使用VBA編寫宏，自動快速查找到前面多值所在的單元格，實現(xiàn)將多值的字符串轉(zhuǎn)變成單元格的下拉列表選項。

[關(guān)鍵詞] VBA；自定義函數(shù)；宏；VLOOKUP

doi ： 10 . 3969 / j . issn . 1673 - 0194 . 2016. 21. 030

[中圖分類號] F232；TP317.3 [文獻標識碼] A [文章編號] 1673 - 0194（2016）21- 0065- 05

0 引 言

在財務工作中經(jīng)常出現(xiàn)這樣的操作，如水電科提供教職工的扣水電費的Excel表，表中只有姓名而沒有工資號，這時因無工資號而無法導入工資系統(tǒng)代扣水電費，如果在表中只使用VLOOKUP，又因姓名有重名，而無法找到正確的工資號，最后只能依靠大量的人工操作，本文可以高效便捷地解決此類問題。

1 VBA的相關(guān)概念及使用方法概述

1.1 VBA和宏

Visual Basic for Applications（VBA）是Visual Basic的一種宏語言，是微軟開發(fā)出來在其桌面應用程序中執(zhí)行通用的自動化（OLE）任務的編程語言。主要能用來擴展Windows的應用程式功能，特別是Microsoft Office軟件。也可說是一種應用程式視覺化的Basic 腳本。通俗的講，VBA是Microsoft Office系列的內(nèi)置編程語言，即應用程序中的VB。它“寄生于”O(jiān)ffice應用程序，是Office套裝軟件的一個重要組件。

宏是VBA語言編出的一段程序，是一系列存儲于Visual Basic中的命令和函數(shù)，并且在需要執(zhí)行該項任務時可隨時運行。

VBA中Sub過程與Function的區(qū)別：

（1）Sub過程不能返回一個值，而Function可以返回一個值。

（2）Sub過程可作為Excel中的宏來調(diào)用，而Function函數(shù)不會出現(xiàn)在“選擇宏”對話框中，要在工作表中調(diào)用Function函數(shù)，可像使用Excel內(nèi)部函數(shù)一樣。

（3）在VBA中，Sub過程可作為獨立的基本語名調(diào)用，而Function函數(shù)通常作為表達式的一部分。

（4）在VBA中，出于安全原因，Sub過程可修改單元格的值， 而Function函數(shù)不可以。

1.2 打開Visual Basic for Application窗口的方法

打開“文件”菜單，點擊“選項”，打開EXECL選項框，然后選右邊的“主選項卡”復選列表，在列表中選中開發(fā)工具，這樣在Excel的工具條中就多了“開發(fā)工具”標簽，內(nèi)有Visual Basic按鈕。

2 VBA對VLOOKUP的改進

2.1 VLOOKUP函數(shù)的概述

VLOOKUP是Excel中基本函數(shù)之一，用于搜索表區(qū)域首列滿足條件的元素，確定待檢索單元格在區(qū)域中的行序號，再進一步返回選定單元格的值。其語法結(jié)構(gòu)如下：

VLOOKUP（lookup_value， table_array， col_index_num， [range_lookup]）

也就是說，需要四條信息才能構(gòu)建 VLOOKUP 語法：

（1）lookup_value，即要查找的值，也被稱為查閱值。

（2）table_array，查閱值所在的區(qū)域。請記住，查閱值應該始終位于所在區(qū)域的第一列，這樣 VLOOKUP 才能正常工作。例如，如果查閱值位于單元格 C2 內(nèi)，那么您的區(qū)域應該以 C 開頭。

（3）col_index_num，區(qū)域中包含返回值的列號。例如，如果指定 B2：D11 作為區(qū)域，那么應該將 B 算作第一列，C 作為第二列，以此類推。

（4）range_lookup（可選），如果需要返回值的近似匹配，可以指定 TRUE；如果需要返回值的精確匹配，則指定 FALSE。如果沒有指定任何內(nèi)容，默認值將始終為 TRUE 或近似匹配。

現(xiàn)在將上述所有內(nèi)容集中在一起，如下所示：

=VLOOKUP（查閱值、包含查閱值的區(qū)域、區(qū)域中包含返回值的列號以及（可選）為近似匹配指定 TRUE 或者為精確匹配指定 FALSE）。

2.2 VLOOKUP問題分析

在實際應用中，若要在大型電子表格中查找所需信息，或總是要查找相同類別的信息時，出現(xiàn)以下情況時，VLOOKUP將無法解決問題：

（1）查閱值（lookup_value）在查閱值所在的區(qū)域（table_array）的任意一列（而VLOOKUP只能是第一列）。

（2）查閱值（lookup_value）在查閱值所在的區(qū)域（table_array）內(nèi)出現(xiàn)多次時，需要返回所有滿足條件的元素（VLOOKUP只能返回第一次出現(xiàn)查閱值對應行的元素）。

2.3 VBA對VLOOKUP的改進代碼概述

（1）首先在Visual Basic for Application窗口中插入模塊，添加一個函數(shù)，代碼如下：

Function MyLookup（lookup_value As Range， table_arry As Range _

， key_index As Byte， value_index As Byte）

Dim arr As Variant， i As Integer， S As String

Dim SValue1 As String， SValue2 As String 轉(zhuǎn)成字符串

arr = table_arry： S = ""

SValue1 = lookup_value

For i = 1 To UBound（arr， 1） 查找區(qū)域的每一行

SValue2 = arr（i， key_index）查找區(qū)域的第i行第key_index列的值

If SValue1 = SValue2 Then

If S <> "" Then 出現(xiàn)多個值時，將他們串聯(lián)，并用“|”相隔

S = S + "|"

End If

S = S & arr（i， value_index）

End If

Next

MyLookup = S

End Function

在上面這個函數(shù)MyLookup中：

①lookup_key 為“查閱值”，相當于VLOOKUP中的lookup_value。

②table_arry 為“查閱值所在的區(qū)域”， 相當于VLOOKUP中的table_array。

③key_index 為“查閱值”在“查閱值所在的區(qū)域”的列號。

④value_index 為“區(qū)域中包含返回值的列號”，相當于VLOOKUP中的col_index_num。

該函數(shù)中key_index參數(shù)的設(shè)置，解決了查閱值只能在查閱值所在的區(qū)域的第一列的問題，使得該函數(shù)較之Excel自帶的VLOOKUP函數(shù)更加靈活，易用性大大增強。

（2）然后再在這個模塊中添加一個sub過程，該sub過程將MyLookup函數(shù)中用“|”相隔的字符串轉(zhuǎn)換為下拉列表，代碼如下：

Sub 生成列表（）

Dim iRow As Long， iCol As Integer

Dim iRow_min As Long， iCol_min As Integer

Dim iRow_max As Long， iCol_max As Integer

Dim Rng As Range

Dim SValue

iCol_min = 256 當前表中數(shù)據(jù)所在最小列號與最大列號

iCol_max = 1

For iRow = 1 To 10

For iCol = 1 To 256

SValue = Trim（Cells（iRow， iCol）.Text）

If SValue <> "" Then

If iCol < iCol_min Then iCol_min = iCol

If iCol > iCol_max Then iCol_max = iCol

End If

Next

Next

iRow_min = 65536當前表中數(shù)據(jù)的最小行號與最大行號

iRow_max = 1

For iRow = 1 To 65536

For iCol = iCol_min To iCol_max

SValue = Trim（Cells（iRow， iCol）.Text）

If SValue <> "" Then

If iRow < iRow_min Then iRow_min = iRow

If iRow > iRow_max Then iRow_max = iRow

End If

Next

If iRow > iRow_max + 10 Then Exit For

Next 如果有連續(xù)10行沒有數(shù)據(jù)，則認為下面都沒有數(shù)據(jù)，結(jié)束查找

For iRow = iRow_min To iRow_max

For iCol = iCol_min To iCol_max

Set Rng = Cells（iRow， iCol）

SValue = Rng.Text

If InStr（1， SValue， "|"） > 0 Then轉(zhuǎn)換成下拉列表

With Rng.Validation

.Delete

.Add Type：=xlValidateList， Formula1：=Replace（SValue， "|"， "，"）

End With

Rng.Interior.ColorIndex = 43 單元格背景色

Rng.Value = "" 單元格清空

End If

Next

Next

Set Rng = Nothing

End Sub

2.4 VBA使用截圖

（1）打開Excel“開發(fā)工具”（如Excel中找不到“開發(fā)工具”，需要在“文件”—“選項”—“自定義功能區(qū)”中勾選“開發(fā)工具”），在如圖1 “工程”—“模塊”—“模塊1”中鍵入MyLookup函數(shù)和“生成列表”sub過程的代碼。

（2）在表格中有若干“姓名”重復的人員信息，我們需要在F列中顯示出所有與E列“姓名”相匹配的學號信息，并且在G列中顯示與F列“學號”相對應的學院信息，以輔助我們的選擇。我們在F3單元格中輸入“=MyLookup（E3，■A■2∶■C■10，2，1）”，如圖2所示。其中2表示查閱值在查閱值所在的區(qū)域的第2列（VLOOKUP要求查閱值必須在查閱值所在的區(qū)域的第一列，而不作任何參數(shù)設(shè)定）。

下面幾行使用填充柄填充即可，光標移出時，F(xiàn)列中將顯示所有姓名為查閱值的人員的學號，并且用“|”分隔，如圖3所示。

（3）選中F列單元格，依次點擊“開發(fā)工具”—“宏”，選中我們編寫的名為“生成列表”的宏執(zhí)行，如圖4所示。這樣用“|”分隔的字符串將被轉(zhuǎn)換為下拉列表，使得查詢結(jié)果更加美觀和人性化，如圖5所示。

（4）在“G3”單元格中輸入“=MyLookup（F3，■A■2∶■C■10，1，3）”，用填充柄填充G列其他行，如圖6所示。這樣G列就會根據(jù)F列下拉列表中選中的學號顯示相應的學院信息，來輔助工作人員的選擇，如圖7所示。

3 結(jié) 語

在財務工作中，Excel 函數(shù)和 VBA技術(shù)都是提高工作效率、減少勞動強度的有效手段。但是有限的Excel 函數(shù)并不能夠解決所有的問題，滿足所有的辦公需求。當遇到Excel 函數(shù)無法正確有效解決的問題時，我們可以通過定制VBA函數(shù)或者過程來滿足自己的需要。靈活地使用Excel 函數(shù)和 VBA技術(shù)，將大大提高財務工作的工作效率。

主要參考文獻

[1]陳國棟. 利用Excel加載宏制作工資條[J].財會月刊， 2014（4）：107-109.

[2]魏汪洋. Excel VBA語法速查手冊[M].北京：化學工業(yè)出版社，2011.