Excel VBA技術在財務工作中的應用

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

[關鍵詞] VBA；自定義函數；宏；VLOOKUP

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

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

0 引 言

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

1 VBA的相關概念及使用方法概述

1.1 VBA和宏

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

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

VBA中Sub過程與Function的區別：

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

（2）Sub過程可作為Excel中的宏來調用，而Function函數不會出現在“選擇宏”對話框中，要在工作表中調用Function函數，可像使用Excel內部函數一樣。

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

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

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

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

2 VBA對VLOOKUP的改進

2.1 VLOOKUP函數的概述

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

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

也就是說，需要四條信息才能構建 VLOOKUP 語法：

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

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

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

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

現在將上述所有內容集中在一起，如下所示：

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

2.2 VLOOKUP問題分析

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

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

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

2.3 VBA對VLOOKUP的改進代碼概述

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

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 '轉成字符串

arr = table_arry： S = \"\"

SValue1 = lookup_value

For i = 1 To UBound（arr， 1） '查找區域的每一行

SValue2 = arr（i， key_index）'查找區域的第i行第key_index列的值

If SValue1 = SValue2 Then

If S <> \"\" Then '出現多個值時，將他們串聯，并用“|”相隔

S = S + \"|\"

End If

S = S arr（i， value_index）

End If

Next

MyLookup = S

End Function

在上面這個函數MyLookup中：

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

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

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

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

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

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

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 '當前表中數據所在最小列號與最大列號

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'當前表中數據的最小行號與最大行號

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 '如果有連續10行沒有數據，則認為下面都沒有數據，結束查找

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'轉換成下拉列表

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“開發工具”（如Excel中找不到“開發工具”，需要在“文件”—“選項”—“自定義功能區”中勾選“開發工具”），在如圖1 “工程”—“模塊”—“模塊1”中鍵入MyLookup函數和“生成列表”sub過程的代碼。

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

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

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

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

3 結 語

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

主要參考文獻

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

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