excel实现查询、统计系统来学习vba中公式的使用

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

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

题目：

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

分析：

• 在一个表里面的查询统计只需要用到count,vlookup函数即可完成，我们这里有多张表需要循环查找，所以要通过vba实现
• 上面查询和统计是两个按钮，所以要分成两个宏来写
• vba调用工作表函数的方法：Application.WorksheetFunction.公式

代码：

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

'查询部分代码
Sub chaxun()
	'如果出现错误继续向下执行，防止程序崩溃
	On Error Resume Next
  	'执行查询前清空数据，防止没找到查询数据，上条数据信息仍在
	Sheet1.Range("d14").ClearContents
	Sheet1.Range("d16").ClearContents
	Sheet1.Range("d18").ClearContents
	Sheet1.Range("d20").ClearContents
	Sheet1.Range("d22").ClearContents
	For 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") <> "" Then        
  		Exit For    
     End If
  Next
End Sub

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

题目：

利用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 Next
  For 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) & "周"
  Next
End Sub

题目：

查找邮箱zhangsan@163.com 中@在第几位

用法：

InStr("zhangsan@163.com", "@")

总结：

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

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

课件下载地址：

链接:

https://pan.baidu.com/s/1iuGPnMeRoENDE5zDBW6V3Q

提取码: bdss