Excel-VBA超级VLOOKUP查询引用输入工具
2023-06-13 09:14:17 时间
VLookup用起来好,当你的数多了,引用的时间也不少
所以设计一个,超级VLOOKUP查询引用输入工具
【功能】
1.多条件设定(因为姓名时有重名,身份证时有大小写,有时姓名与身份证对不上,所以最好的方法是:姓名+身份证)
2.多数据引用
(功能:先打开数据源文件,把姓名+身份证统一转化为大写,再以此为条件把要的数据存入字典,再打开输入文件,查询,如果存在字典中,就批量引用数据)
【代码】
Sub yhd超级查询引用()
Dim s_rng As Range, a_rng As Range, b_rng As Range, condition
Dim dic_out As Object
Set dic_out = CreateObject("scripting.dictionary")
With Sheets("超级查询引用")
'===取值“条件模式”
condition = .Range("C1").Value
If condition = "单条件" Then
Set s_rng = Union(.Range("B4:D4"), .Range("B8:D8"))
Call CheckBlank(s_rng)
If Len(Trim(.Range("D4"))) = 0 Or Len(Trim(.Range("D8"))) = 0 Then MsgBox "你选择了“单条件”模式,D4与D8必须填写": Exit Sub
Else
Set s_rng = Union(.Range("B4:E4"), .Range("B8:E8"))
Call CheckBlank(s_rng)
If Len(Trim(.Range("D4"))) = 0 Or Len(Trim(.Range("D8"))) = 0 Or Len(Trim(.Range("E4"))) = 0 Or Len(Trim(.Range("E8"))) = 0 Then MsgBox "你选择了“单条件”模式,D4与D8必须填写": Exit Sub
End If
Set a_rng = .Range("B4") '设置初取值
Set b_rng = .Range("B8")
'===数组情况:1=文件路径2=工作表名3=姓名4=身份证(后面可多可少)5=本期收入6=养老7=医疗8=失业9=公积金10=职业年金
arr = a_rng.Resize(1, [Iv4].End(xlToLeft).Column - a_rng.Column + 1)
brr = b_rng.Resize(1, [IV8].End(xlToLeft).Column - b_rng.Column + 1)
End With
Call disAppSet(False)
'=======打开数据源文件,把要“条件”存入key,把“数据”存入items,1=文件名2=工作表,3=标题行数,4-5=条件
Set wb_out = Workbooks.Open(brr(1, 1))
With wb_out.Sheets(brr(1, 2))
.Activate
endrow = .Cells.Find("*", , , , 1, 2).Row
For i = brr(1, 3) + 1 To endrow
If condition = "单条件" Then
'===如果是单条件,一个数据,如果是双条件就两个数据相加
dickey = .Cells(i, brr(1, 4)).Value
Else
dickey = .Cells(i, brr(1, 4)).Value & .Cells(i, brr(1, 5)).Value
End If
If Len(Trim(UCase(dickey))) > 0 Then
dicitem = ""
For ii = 6 To UBound(brr, 2)
dicitem = dicitem & "@" & .Cells(i, brr(1, ii))
Next ii
dic_out(dickey) = dicitem
Else
End If
Next i
End With
wb_out.Close False
' =======存入字典完成,关闭数据源文件======
' =======打开输入文件,进行数据查询引用=====
Set wb_in = Workbooks.Open(arr(1, 1))
With wb_in.Sheets(arr(1, 2))
.Activate
endrow = .Cells.Find("*", , , , 1, 2).Row
For i = arr(1, 3) + 1 To endrow
If condition = "单条件" Then
'如果是单条件,一个数据,如果是双条件就两个数据相加
dickey = .Cells(i, arr(1, 4)).Value
Else
dickey = .Cells(i, arr(1, 4)).Value & .Cells(i, arr(1, 5)).Value
End If
If dic_out.exists(Trim(UCase(dickey))) Then
temp_arr = Split(dic_out(dickey), "@")
' MsgBox dic_out(s)
For jj = 1 To UBound(temp_arr)
ajj = jj + 5
.Cells(i, arr(1, ajj)) = temp_arr(jj)
Next jj
End If
Next i
.Cells(5, 1).Select
ActiveWindow.ScrollRow = 2
' 激活窗体,选中a5单元格,滚到到第二行,方便查看,再自己按保存
End With
' wb_in.Close SaveChanges:=True
Call disAppSet(True)
MsgBox "完成,自己查看一下,再保存"
' =======查询引用完成,关闭输入文件======
End Sub
'========CheckBlank检测空值,如果有空就退出=========
'使用方法
' Dim r As Range
' Set r = Union(Range("M4:O4"), Range("M8:O8"))
' Call CheckBlank(r)
'=================
Sub CheckBlank(rng)
For Each r In rng
If Application.WorksheetFunction.CountBlank(r) Then
MsgBox "你在" & r.Address & "没有填写内容"
Exit Sub
End If
Next
End Sub
Sub disAppSet(flag As Boolean)
With Application
.ScreenUpdating = flag
.DisplayAlerts = flag
.AskToUpdateLinks = flag
If flag Then
.Calculation = xlCalculationAutomatic
Else
.Calculation = xlCalculationManual
End If
End With
End Sub
【使用方法】
设置好初始数据--按【执行】就可以批量多条件引用 多数据
相关文章
- 公司的报表工具太难用,我三天撸了个Excel工具,运营小姐姐直呼太好用了,现已开源!!(建议收藏)
- csv格式怎么转成excel_比较好一点的Excel转PDF
- mysql导入excel文件_将Excel数据导入MySQL「建议收藏」
- Excel VBA学习之 一键删除多行数据-并备份起来
- 赶快收藏零安装立即整合Excel与PowerBI的神奇工具ExcelBIPro
- 盘点一个Pandas操作Excel多条件取值的实战案例
- 如何使用Excel将某几列有值的标题显示到新列中
- Python xlwt数据保存到 Excel中以及xlrd读取excel文件画图
- Python 接口测试之Excel表格数据操作方法封装
- poi 导出Excel 工具类详解编程语言
- Java通用的Excel文件生成工具类,支持生成文件和浏览器直接下载详解编程语言
- 操作excel展示Linux下Poi妙用:操作Excel(linux下poi)
- 将Excel带入Linux:新的商业工具发现方式(excellinux)
- 导入Excel数据到Oracle数据库(将excel导入oracle数据库)
- 文件Linux下快速导出Excel文件(linux导出excel)
- 步骤简单!轻松实现SQL Server导入Excel操作(sqlserver导入excel)
- 将Excel表格数据导入MySQL数据库(excel到mysql)
- Oracle与Excel联合提升数据分析挖掘能力(oracle与excel)
- php,不用COM,生成excel文件
- js读取本地excel文档数据的代码
- C#WinForm导出Excel方法介绍
- c#将Excel数据导入到数据库的实现代码
- c#生成excel示例sql数据库导出excel
- datatable生成excel和excel插入图片示例详解
- 通过复制Table生成word和excel的javascript代码
- c#连接excel示例分享