在excel中使用vba实现查询、统计系统

excel里面有很多强大的公式，可以快速实现我们想要的结果。如果在vba中可以使用公式，可以大大减少编码，提升工作效率！这么强悍的功能让我们一起学习一下吧！

我们直接通过案例来学习公式在vba中的使用

题目：

实现多个工作表数据的查询，统计功能，如下图：

分析：

• 在一个表里面的查询统计只需要用到count,vlookup函数即可完成，我们这里有多张表需要循环查找，所以要通过vba实现

• 上面查询和统计是两个按钮，所以要分成两个宏来写

• vba调用工作表函数的方法：Application.WorksheetFunction.公式

代码：

1. '统计部分代码
2. Sub tongji()
3. Dim i, k, l, m As Integer
5. For i = 2 To Sheets.Count
6.     '累加每张表A列非空单元格的个数
7. k = k + Application.WorksheetFunction.CountA(Sheets(i).Range('a:a')) - 1
8. '累加每张表F列为“男”的单元格的个数
9. l = l + Application.WorksheetFunction.CountIf(Sheets(i).Range('f:f'), '男')
10. '累加每张表F列为“女”的单元格的个数
11. m = m + Application.WorksheetFunction.CountIf(Sheets(i).Range('f:f'), '女')
12. Next
14. '将所有表A列单元格的总计赋值给统计工作表的“d26”单元格
15. Sheet1.Range('d26') = k
16. '将所有表F列为“男”单元格的总计赋值给统计工作表的“d27”单元格
17. Sheet1.Range('d27') = l
18. '将所有表F列为“女”单元格的总计赋值给统计工作表的“d28”单元格
19. Sheet1.Range('d28') = m
20. End Sub

'查询部分代码Sub chaxun()'如果出现错误继续向下执行，防止程序崩溃On Error Resume Next'执行查询前清空数据，防止没找到查询数据，上条数据信息仍在Sheet1.Range('d14').ClearContentsSheet1.Range('d16').ClearContentsSheet1.Range('d18').ClearContentsSheet1.Range('d20').ClearContentsSheet1.Range('d22').ClearContentsFor i = 2 To Sheets.Count'在A到H列中查找该准考证号学生的姓名Sheet1.Range('d14') = Application.WorksheetFunction.VLookup(Sheet1.Range('d9'), Sheets(i).Range('a:h'), 5, 0)'在A到H列中查找该准考证号学生的性别Sheet1.Range('d16') = Application.WorksheetFunction.VLookup(Sheet1.Range('d9'), Sheets(i).Range('a:h'), 6, 0)'在A到H列中查找该准考证号学生的专业Sheet1.Range('d18') = Application.WorksheetFunction.VLookup(Sheet1.Range('d9'), Sheets(i).Range('a:h'), 3, 0)'在A到H列中查找该准考证号学生的总分（原始分）Sheet1.Range('d20') = Application.WorksheetFunction.VLookup(Sheet1.Range('d9'), Sheets(i).Range('a:h'), 8, 0)'查找到数据的表名也就是学生所在地区Sheet1.Range('d22') = Sheets(i).Name'查到了，就退出循环If Sheet1.Range('d14') <> '' ThenExit ForEnd IfNextEnd Sub

上面我们用到的是工作表函数，vba也有自己的函数，下面一起来看两个简单的小例子！

例1：

利用vba的split函数实现下面功能（当然工作表mid函数也能完成）

用法：

• Split('pw-023-2015-37-001', '-')就是将字符串'pw-023-2015-37-001'以 '-'为分隔符分割。结果会得到一个数组，下标从零开始。

• Split('pw-023-2015-37-001', '-')(0)就是取第1个数据pw

• Split('pw-023-2015-37-001', '-')(1)就是取第2个数据023

• Split('pw-023-2015-37-001', '-')(2)就是取第3个数据2015

代码：

Sub tiqu()On Error Resume NextFor i = 2 To Sheet2.Range('a65536').End(xlUp).Row    Sheet2.Range('b' & i) = Split(Sheet2.Range('a' & i), '-')(2) & '年 第' & Split(Sheet2.Range('a' & i), '-')(3) & '周'NextEnd Sub例2：查找邮箱[e-mail]  中@在第几位 用法：InStr('[e-mail]', '@')

总结：

这个案例我们用到了三个工作表函数公式CountA，CountIf，VLookup和两个vba函数Split，instr.不熟练的小伙伴赶快去学习一下，后面我会整理一份excel和vba的函数供小伙伴参考。

每天学一点，薪资翻一番。看了这篇文章觉得对你有用的话，关注我的公众号“学会数据分析”并且用你的小手帮忙分享一下，我会经常总结一些案例和大家一些分享。