金多多VBA实用技巧一：“高大上”的调查问卷信息录入与汇总工具

应用背景：

在工作当中，大家可能经常会遇到需要用标准化模板收集调查信息的情况。这份看似简单的工作，对很多朋友来说却是份费时费力的工作。其实这种信息收集的工作并无难度，但想要干的出彩让领导眼前一亮着实有些难度，而且调查问卷涉及N多人填写，把所有人的信息汇总就是个老大难的问题。今天我就交给大家一个VBA使用技巧，如何用VBA高效、准确、无误地完成信息的录入与汇总，让大家今后的工作“高大上”起来！

表单结构设计：

表单的设计需要结构清晰、层级分明，因此在开始制作工具之前需要厘清思路，把不同功能的表单设计在不同的Sheet上。对于我们目前研究的信息录入与汇总工具来说，顾名思意，就需要完成两个动作，或者说两类功能：

Ø  第一是信息的录入，我们需要将设计让被调查人填写数据的操作界面。这个界面不仅需要给用户提示如何使用操作该工具，更需要区分数据录入的区域以及填写的标准格式（最为关键的是，在设计该页面时，必须思考如何与信息保存的页面进行关联）。该页面可以说就是平时我们说提到的用户输入终端，因此在该界面，必须放置让用户进行信息存储和修改的操作控件。

Ø  第二是信息的汇总，我们需要设计一个把调查信息进行保存的界面，也就是数据存储区域。该区域可以说就是用户的后台，因此可以将此页面设计成在一般情况下不被用户操作的权限。也就是说只用程序开发人员以及表单设计人员才能采集该页面的信息，而普通的信息录入人员是不能进行操作的。这样就能成功实现前、后台的隔离

上述两界面的设计大致如下：

界面一：信息录入

界面二：数据存储

程序设计：

1.       生成数据链接

大家通过界面一和界面二的对比不难发现，界面一和界面二的都能找到相同的数据名称。心细的同学能猜到，这两栏信息之间可能存在关联关系。确实，我们可以通过一定的方法把信息录入界面上的内容导入到数据存储页面上来，而且方法很简单。只要使用“=”就可以，比如说我们在数据存储界面上的C3单元格中写入“=信息补录界面!$D17”，就可以将数据录入界面中的用户所填写的“高管层平均从业年限”链接过来。当然，这个关联关系仅是实现了将前台数据导入后台的渠道工作，实现的是数据的缓存功能，因此“生成数据链接”的工作只是数据保存的准备工作。

2.       如何用程序进行新数据保存

以下代码实现了数据的保存功能：

Range(“A4″).Select

Selection.End(xlDown).Select

RowNumb = Selection.Row – 5 + 1

StartRow = 5

LastRow = Selection.Row + 1

If WorksheetFunction.CountIf(Range(Cells(StartRow, 1), Cells(LastRow, 1)), Cells(3, 1).Value) = 0 Then

    Range(“A3:J3″).Select

    Selection.Copy

    Cells(LastRow, 1).Select

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

    MsgBox “客户（” & Cells(3, 1) & “）的财报数据成功保存“

Else  

【代码分析】

其实思路很简单，就是将数据存储界面中数据缓存地带的信息复制到下方的数据存储介质中。但主要的核心在于，我们需要将“新鲜”的信息放在“正确”的位置。比如我们新录入了一个客户的信息，且该客户信息之前没被保存过，在发现这样一条数据的情况下，我们应该在保存时把这段数据紧挨着先前最后一条保存过的数据进行存储。所以对于这个“正确”存储位置的定位是关键。而在上述代码中，定位的代码使用Range(“A4″).Select以及Selection.End(xlDown).Select完成，通过这两段代码的协同完成了“定位之前最后过的那条保存过的数据”的动作。

当然我们还需要一个判断语句来辨别当前缓存数据区域的数据是否是“新鲜”，而If WorksheetFunction.CountIf(Range(Cells(StartRow, 1), Cells(LastRow, 1)), Cells(3, 1).Value) = 0 Then这段化就很好的实现了该识别功能。

3.       如何用程序进行老数据修改

以下代码实现了数据的修改功能：

For i = 6 To LastRow

If Cells(i, 1) = Cells(3, 1) Then

    Range(“A3:J3″).Select

    Selection.Copy

    Cells(i, 1).Select

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

    GoTo Here

End If

Next

Here:   MsgBox “客户（” & Cells(3, 1) & “）的财报数据已修改“

End If

【代码分析】

其实要对已有数据进行修改，前提必须先找到或者定位已存储的数据。最常见的做法就是先确定我们要找的目标数据，然后再在已保存的数据列表中一个一个遍历查询是否存过这个数据，找到后再把新的数据将老数据覆盖掉。我们可以用“for i=… to …”的循环语句完成上述伪代码，具体如何实现可参看上面的代码区域。

值得一提的是，上述代码虽然思路简编写方便，但是如果数据量太大的话会大大影响程序运行的效率。而且目标数据在存储介质中的存放位置很有讲究，如果放在存储介质中的顶部，很快就可以跳出循环，而如果目标数据是压箱底的话（也就是说放在存储队列的末端），那么整个查找的过程就会较为漫长了。为了加快效率，可以使用vba中的find（）属性进行目标数据的查找，其实现的功能就和我们在表单界面中使用“ctrl+f”的快捷键效果是一样的。相比于“for…next”的循环语句，find（）的语句简单而且速度较快，我们今后还会继续讲到，有兴趣的同学不妨业余尝试用用。

成为VBA高手

回帖下载excel数据和PDF文档