Excel VBA之让信息汇总的效率至少提高6.6倍

通过对之前内容的阅读，相信大家已经知道了不少的VBA知识，今天我们看一个案例，案例内容是利用VBA来进行信息汇总，通过本文的对比测试，发现利用VBA至少可以将效率提高6.6倍，同样地，本文会给出案例中使用到的函数的参考文章，供大家进一步研究。

信息汇总

信息汇总的需求出现在生活的方方面面，例如班长统计学生的团员信息，销售经理统计销售项目、人力资源统计客户信息等等，如果能够高效地满足这个需求，那么将会极大地方面我们的工作。

syn.png

传统方法

如果我们采用传统方式进行信息汇总，则我们会这样做：依次打开每一个待汇总的文件，并复制其中的内容，然后将复制的内容粘贴到用于汇总的文件中。对于每一个文件，我们都需要打开、复制、粘贴这些操作，如果有20个文件，我们需要重复上述操作20次，如果有100给个文件，则我们需要重复上述操作100次，……而且这些重复都是机械地重复，可见，传统的信息汇总方式是很浪费时间的。

小编亲自尝试汇总了20个学生的团员信息，汇总过程如下图，可以看到最后用时为4:50,即290秒。(过程确实挺无聊的~~(o|o) )

VBA方式

其实，我们在1中就提到了，如果操作过程是机械地重复，则就可以使用VBA来自动化这个过程，而VBA程序编写的流程就和我们按照传统方式进行处理的流程是一样的。

首先我们使用DIR函数获取待汇总文件所在目录(为了简单起见，我们提前将所有的待汇总文件放置到同一个目录中),DIR会返回第一个满足要求的文件名，再次调用DIR会返回满足要求的之后的文件名，注意再次调用DIR不需要参数.用例如下

Sub open_all_files()
Dim a
a = Dir("C:\Users\Administrator\Desktop\新建文件夹\*.txt")'将txt结尾的所有文件打开，但是在这里只打开第一个符合的文件，接下来的文件在do循环里依次打开
Workbooks.Open "C:\Users\Administrator\Desktop\新建文件夹\" + a
Do '遍历目录下的所有指定格式的文件名
a = Dir'之前dir()下已经打开了多个文件，这里就不用在写上，表示依次打开符合格式的文件
If a <> "" Then
Workbooks.Open "C:\Users\Administrator\Desktop\新建文件夹\" + a'打开每一个符合格式的文件
Else
Exit Sub
End If
Loop
End Sub
'参考：  https://www.cnblogs.com/gilgamesh-hjb/p/7291821.html

得到文件名后，调用Workbooks.Open()打开该文件，然后选中包含单元格A1在内的当前区域(CurrentRegion),再将除去表头后的所有内容复制到汇总的文件中。这过程可以用下面这一行代码表示：

wb.Sheets(1).Range("A1").CurrentRegion.Offset(1, 0).Copy ThisWorkbook.Sheets(1).Cells(position, 1)'将打开文件中除去'A1'所在行(即表头，用Offset实现)之后的所有内容复制到当前工作簿以(position行，1列)为起始位置的地方，其中position是自定义的变量。

完整代码如下

Sub collect()

    Dim rows As Integer, cols As Integer, targetFile As String, fileDir As String, position As Integer
    Dim wb As Workbook
    
    Set a = Application.FileDialog(msoFileDialogFolderPicker)'
    a.Show
    fileDir = a.SelectedItems(1)
    targetFile = dir(fileDir & "\" & "*.xls")
    position = 2
    If targetFile = "" Then
        MsgBox "所选目录中没有Excel文件，请重新选择", vbExclamation
    Else
        Do While targetFile <> ""
        Set wb = Workbooks.Open(fileDir & "\" & targetFile)
        wb.Sheets(1).Range("A1").CurrentRegion.Offset(1, 0).Copy ThisWorkbook.Sheets(1).Cells(position, 1)
        wb.Close
        position = position + 1
        targetFile = dir
        If targetFile = "" Then Exit Do
    Loop
    MsgBox "处理完毕", vbOKOnly
    End If
    
End Sub

作为对比，让我们用VBA来完成和刚才一模一样的任务，即汇总20个学生的团员信息，过程如下图，可以看到完成此过程用时约44秒，经过简单的计算可以发现，使用VBA会将效率提高6.6倍！

温馨提示

对上述任何一个函数，如果不理解，可以在VBA编辑区中选中该函数，然后按下F1，会自动打开关于该函数的官方帮助文档。
启动了VBA的excel文件，理论上可以干任何事情，具有一定的安全隐患，所以复制VBA代码前先确认代码发布者的可信度(”码上有效率“会确保发布的每一份代码都经过安全测试)。
启动了VBA的excel文件需要保存为.xlsm格式，而不能是.xlsx,要想显示VBA编辑区，需要打开开发工具，打开过程为:鼠标左键单击“文件”->“选项”->“自定义功能”，在右侧的主选项卡中，找到并选择“开发工具”，点击“确定”。

好了，本次的介绍就到这儿，如果你在读的过程中有什么疑问或者建议，欢迎留言讨论；如果觉得有帮助，麻烦点赞支持，谢谢。

参考文章

https://docs.microsoft.com/zh-cn/office/vba/language/reference/user-interface-help/dir-function

https://docs.microsoft.com/zh-cn/office/vba/api/excel.range.currentregion

Excel VBA之让信息汇总的效率至少提高6.6倍