运用VBA编程和VLOOKUP函数实现EXCEL数据快速录入

运用 VBA编程和 VLOOKUP函数实现 EXCEL数据快速录入

杨剑峰

身份证号码： 61210219780601**** ，河南 洛阳 471000

摘 要： 利用EXCEL中的VBA语言编写程序，配合使用VLOOKUP函数和“下拉菜单”，使得工程数据在EXCEL的录入工作变得简单快捷，值得同行业或相关类似工作借鉴和推广。

关键字：VBA编程、下拉菜单、VLOOKUP函数

工程施工中为了方便查找和归集，需要录入大量数据，比如材料数据、焊接数据和焊接合格率等，并且各种数据的内容都非常庞大。如果逐个逐条输入，那将会是个复杂、繁琐的工作，而且稍不留心会出现录入上的错误。运用EXCEL中的VBA编程和内部函数可以减少工作量、确保录入质量。本文以大庆石化60万吨/年乙烯裂解装置中工艺管线材料数据库的录入为例对此方法进行阐述。

1. 实现快速录入功能

创建“复制上行”和“复制指定行”按钮实现方便快捷录入。

在单元格中输入内容后，点击 “复制上行”按钮，EXCEL会自动将上一行内容填充到下一行相应的单元格中；点击“复制指定行”按钮并在文本框中输入需复制的行号，然后点击“确定”按钮,EXCEL会将指定的行的内容自动填充到下一行。点击单元格上的下拉菜单将需要填充的内容自动填充到单元格中。

2. 实际操作步骤

   1. 利用EXCEL中的VBA编程实现向下填充和指定行填充

在EXCEL2007中创建新文件，点击“开发工具→插入→ActiveX控件→按钮”创建“CommandButton1”按钮，将“CommandButton1”按钮重新命名为“复制上行”。点击“查看代码”在编码框中输入以下源代码。

Private Sub CommandButton1_Click()

Dim R As Integer

R = Me.[A65536].End(xlUp).Row

If R = 2 Then Exit Sub '为了放按键多加一行,如不想加将2变为1

Me.Rows(R + 1).Value = Me.Rows(R).Value

Me.Cells(R + 1, 1).Select

End Sub

使用同样的方法创建“复制指定行”按钮，并在编码框中输入如下源代码。

Private Sub CommandButton2_Click()

Dim R As String, I As Integer

R = InputBox("请输入你要指定的行号:")

If Len(R) = 0 Then Exit Sub

If CInt(R) < 2 Then Exit Sub '为了放按键多加一行,如不想加将2变为1

If Len(Me.Cells(CInt(R), 1)) = 0 Then MsgBox "行无数据", 64: Exit Sub

I = Me.[A65536].End(xlUp).Row

Me.Rows(I + 1).Value = Me.Rows(CInt(R)).Value

Me.Cells(I + 1, 1).Select

End Sub

1. 2. 创建下拉菜单

点击打开“SHEET2”文档，输入源文件。选择源文件所在的列后点击“公式→根据所选内容创建”在对话框中选择“首行”然后按“确定”。回到“SHEET1”文档选择需要输入管线号的列点击“数据→数据有效性”将对话框中的“有效性条件”改为“序列”，在“来源”中输入=管线号，然后点击“确定”。然后使用同样的方法对需要输入页码、版次、管道级别、隔热代号、隔热厚度所在的列依次进行上述操作。

1. 3. 创建二级下拉菜单

对于像管线号、页码、版次等这些内容相对较少的使用一级下拉菜单就可以满足。对于材料代码这样的内容非常多，仅使用一级菜单所列出来的选择项也很多，选择起来非常不方便，因此有必要使用二级菜单。先将所有项分类，然后再在分类后的项中查找就变得简单多了。实践中，将乙烯装置中的材料代码按材料名称分为管子、弯头、三通、法兰、阀门等种类并输入到“SHEET2”中。然后选择所输入的列按“公式→根据所选内容创建”在对话框中选择“首行”然后按“确定”。在选择所在列的第一行按“公式→定义名称”在名称文本中输入“材料名称”然后按确定关闭对话框。点击进入“SHEET1”文档选择材料名称列点击“数据→数据有效性”将对话框中的“有效性条件”改为“序列”，在“来源”中输入=材料名称。选择材料代码列点击“数据→数据有效性”将对话框中的“有效性条件”改为“序列”，在“来源”中输入=INDIRECT($F2)，按“确定”。这是会出现一个报错，点“是”完成设置。

1. 4. 使用VLOOKUP函数查找源文档中内容并填充到目标文档

点击“SHEET3”文档将文档重命名为“数据源”。在“数据源”文档A列中输入所有的材料代码，B列中输入材料代码所对应的描述。返回“SHEET1”文档点击I3单元格并输入=VLOOKUP(E3,数据源!A:B,2,FALSE)，按回车键完成设置。注意，点击“复制上行”后会自动将上一行的内容填充到下一行中。点击材料代码下拉菜单选择所需的内容后对应行中第I列的内容会依然是上一行的内容(如图6所示)。此时先不用管它，继续往下输入。等输入完成后，点击I3单元格将鼠标移至右下角待鼠标变成十字形后点击鼠标左键向下拖动鼠标。此时，I列的内容会自动变成E列中材料代码所对应的描述内容（如图7所示）。

图6 图7

3. 优越性的体现

   1. 提高了数据录入速度

在大庆石化60万吨/年乙烯裂解装置工艺管线施工中，运用EXCEL中的VBA编程和内部函数的方法录入材料库，平均每人每天可录入的图纸量从原来的18页提高到了100多页，大大减轻了数据录入的工作量，节省了工作时间，提高了工作效率，收到了不错的效果。

1. 2. 保证了数据录入质量

使用此方法，目标文件中的内容主要是通过引用源文件中的内容进行填充，避免了逐个逐条的输入由于人为原因造成录入错误，保证了数据的录入质量。与此同时，如果在录入过程中发现填充内容有错误，只需将源文件的内容进行修改即可，目标文件中内容会随之自动修改，无需逐条进行修改，使得纠错工作变得很轻松、快捷。

4. 结束语

运用EXCEL中的VBA编程和内部函数的方法进行数据录入，使得繁琐、复杂的工作变得简单快捷，值得同行业或相关类似工作借鉴和推广。

参考文献

《中文版OFFICE 2007实用教程》 作者：徐贤军 出版社：清华大学出版社 

注：

VBA： 全称为Visual Basic for Applications，是Visual Basic的一种宏语言，主要能用来扩展Windows的应用程式功能，特别是Microsoft Office软件。

VLOOKUP函数：纵向查找函数。