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.