- 畅销套餐
- 精选套餐
- 人气套餐
- 尊享套餐
- 高薪套餐
- 课程介绍
- 课程大纲
适合人群:
日常工作使用Excel较多,希望可以更好的使用Excel提升工作效率的职场人
你将会学到:
通过学习VBA实现VLOOKUP查找匹配功能入门VBA宏语言。
- Excel函数
- Vlookup函数
- VBA编程实现VLOOKUP功能
课程简介:
课程目标:通过学习VBA实现VLOOKUP查找匹配功能入门VBA宏语言。 | |||
类别 | 功能 | 方法 | 举例 |
基础知识 | VBA(Visual Basic for Applications)宏语言 | Sub shishi() End Sub | |
单元格赋值 | [单元格] [单元格范围] | [E1]=1 [E1:F5]=2 | |
Range("单元格") Range("单元格范围") | Range("E1") = 1 Range("E3:F5") = 2 | ||
Cells(行, 列) | Cells(1, 5) = 1 | ||
Range与Cells组合 | Range(Cells(3, 5) , Cells(5, 6) )=2 | ||
Cells与Resize组合 | Cells(3, 5).Resize(3, 2) = 2 | ||
带变量单元格赋值 | Range("单元格") Range("单元格范围") | i = 1 j = 3 k = 5 Range("E"& i) = 1 Range("E"& j &":F"& 5) = 2 | |
Cells(行, 列) | r=1 c=5 Cells(r, c) = 1 | ||
Range与Cells组合 | r1 = 3 r2 = 5 c1 = 5 c2 = 6 Range(Cells(r1, c1), Cells(r2, c2)) = 2 | ||
Cells与Resize组合 | r1 = 3 r2 = 5 c1 = 5 c2 = 6 Cells(r1, c1).Resize(r2-r1+1, c2-c1+1) = 2 | ||
FOR循环 | FOR循环赋值列 | For i = 1 To 20 Range("A"& i) = i Next | |
FOR循环赋值列 | For i = 1 To 20 Cells(i, 1) = i Next | ||
FOR循环赋值行 | For i = 1 To 20 Cells(1, i) = i Next | ||
FOR循环赋值二维表 | For i = 1 To 20 For k = 1 To 20 Cells(i, k) = i + k Next Next | ||
IF判断 | If Then | If Range("A1") = 1 Then Range("B1") = 2 End If | |
If Then Else | If Range("A1") = 1 Then Range("B1") = 2 Else Range("B1") = 3 End If | ||
If Then ElseIf Then | If Range("A1") = 1 Then Range("B1") = 2 ElseIf Range("A1") = 2 Then Range("B1") = 3 End If | ||
If Then ElseIf Then Else | If Range("A1") = 1 Then Range("B1") = 2 ElseIf Range("A1") = 2 Then Range("B1") = 3 Else Range("B1") = 4 End If | ||
功能实现 | VBA实现VLOOKUP功能 | 查找匹配指定值 | look = Range("A2") For i = 1 To 335 If Range("O"& i) = look Then Range("C2") = Range("P"& i) End If Next |
循环要查找的值 | For l = 2 To 6 look = Range("A"& l) For i = 1 To 335 If Range("O"& i) = look Then Range("C"& l) = Range("P" & i) End If Next Next | ||
功能进阶 | 自动获取数据行数 | ActiveSheet.[A65536].End(xlUp).Row Sheets("表名").[A65536].End(xlUp).Row | |
跨表匹配 | 执行前选选表 | Sheets("表名").Select | |
指定表数据 | Sheets("表名").Range("D2:F10") | ||
信息表装入数组 | Dim tablearr tablearr=Range("D2:F10") | ||
要匹配的值装入数组 | Dim lookarr lookarr=Range("A2:A10") | ||
匹配结果装入数组一次输出 | ReDim shuchu(1 To jshs) shuchu(i) = Range("B2:B10") = shuchu Range("B2:B10") = Application.Transpose(shuchu) | ||
关闭屏幕更新 | Application.ScreenUpdating = False Application.ScreenUpdating = True |
课程大纲-EXCEL VBA实现VLOOKUP查找匹配 提升软实力
第1章Excel VBA基础(37分钟4节)
1-1
Excel VBA基础之单元格赋值'单元格赋值演示'--------------------Sub shishi()[A1] = 0[A2] = 0End SubSub shishi1()Range("A1") = 1Range("A2") = 1End SubSub shishi2()Cells(1, 1) = 2Cells(2, 1) = 2End SubSub shishi3()[A1:A15] = 3[C1:D15] = 3End SubSub shishi4()Range("A1:A15") = 4Range("C1:D15") = 4End SubSub shishi5()Range(Cells(1, 1), Cells(15, 1)) = 5Range(Cells(1, 3), Cells(15, 4)) = 5End SubSub shishi6()Cells(1, 1).Resize(15, 1) = 6Cells(1, 3).Resize(15, 2) = 6End Sub
「仅限付费用户」点击下载“单元格赋值.txt”
[10:57]开始学习第2章实现查找匹配(7分钟1节)
第3章功能完善进阶(31分钟5节)
“孙忠”老师的其他课程更多+