Excel快速批量查找值
在工作中（特别是在体制内），你辛辛苦苦设计好一个excel表格让下级部门填写，但总有一些沙雕不走寻常路，不填进你的表格，只把他们维护的乱七八糟excel表格文件发给你。。。
你还奈他不何，往往这些沙雕都倚老卖老，不是蠢就是坏，要是跟他们理论，只会严重伤害革命友谊。。。
这时候，你只能靠自己把不同列、不同sheet甚至不同文件内的值给匹配到一个sheet上。
简单来说，需要解决的问题是：
- 有一列用于匹配的数据（以下称为键），键存在于一个sheet内的不同两列、或者不同的工作表（以下称为sheet）中的两列、或者不同的excel文件（即工作簿，以下称为workbook）中的两列。
- 有一个主表，在这个主表中我们需要填进去相关的数据。
- 我们想要填进去的数据在主表以外。
- 我们关注的数据与主表之间通过键来关联。
一、VLOOKUP
VLOOKUP是最被人熟悉的方法了，简单易懂。下面是如何使用 VLOOKUP 的示例。
=VLOOKUP(B2,C2:E7,3,TRUE)
在此示例中，B2 是第一个参数-函数需要使用的数据元素。
对于 VLOOKUP，此第一个参数是要查找的值，把键填上去。
第二个参数是单元格区域（C2:E7)，把我们搜索范围填上去。
第三个参数是包含您要查找的值的单元格区域中的列。把我们需要查找的数据在第几列填上去。例子中填了3，则代表搜索范围的第3列（E列）。
第四个参数可选。TRUE 或 FALSE，默认为TRUE，即近似匹配，近似匹配的效果可见下图。
没有一个键值是21500，所以这个函数就找了个最接近的键来匹配。当然一般工作中需要指定第四个参数是FALSE，也可以将值=0。
知道了vlookup以后，其实hlookup也是类似的，把v(vertical)换成h(horizontal)意思就是把原来竖向变为横向。
二、LOOKUP 或 XLOOKUP
1.普通查找
很多人知道VLOOKUP，但是知道LOOKUP函数的人不多，应该知道XLOOKUP的就更少。
其实就连微软在VLOOKUP的帮助文档中，也建议用户使用XLOOKUP（仅Excel 365可以使用）。
因为VLOOKUP虽然使用简单，但是有一些限制：
- 只能竖向查找（类似的，HLOOKUP只能横向查找）
- 键一定要在搜索范围的左侧
LOOKUP与XLOOKUP都大同小异，用下面这个用名称找代码的示例，说明一下XLOOKUP的使用方法：
从上面可以看出，XLOOKUP仅包括 lookup_value（单元格 F2）、lookup_array（范围 B2:B11）和 return_array（范围 D2:D11）参数。
它不包括 match_mode 参数，因为 XLOOKUP 默认为完全匹配。
2.同时查找多列
作为合格的工具人，一般不会让我只查找一列，一般都是很多列。。。
XLOOKUP的一个优势就在于，它的返回结果可以是有多个项的数组！
比如，根据员工 ID 编号查找员工信息：
3.如果没有找到的情况
VLOOKUP如果有某一个键不存在，妥妥地返回错误，你只能用iferror套在VLOOKUP上面来避免错误
但是XLOOKUP有if_not_found参数，在某一个键不存在的情况下不至于返回ERROR：
三、VBA
VLOOKUP跟XLOOKUP有一点不好
就是不够拉风（虽然作为社畜也没啥好拉风的）
另外还有一个致命的问题就是：消耗资源太多，太慢
如果主表跟附表都有超过30W行，强硬的使用lookup函数，16G内存的电脑没有10分钟也不会有结果
最气人的是，你中途还不能停下，也很难知道进度到了哪里。。。
毕竟Excel函数不是给我们这么玩的。如果不是轻量级的使用，建议还是使用VBA。
1.字典
VBA里面的字典对象还是挺好用的
字典就是键值对，大概操作如下
Dim dict
Set dict = CreateObject("scripting.dictionary") '声明
dict.Item(theKey) = theValue '赋值
theValue = dict.Item(theKey) '读取值
2.进度显示
在运行当中，如果能够看到进度去到哪里就会心安一点，如果出bug了也知道是哪一行的问题
thisWs.Cells(1, nextColumn) = i & "/" & rMaxSource '在Cells(1, nextColumn)展示当前i的值，以及最大行数rMaxSource的值
3.关闭自动刷新
因为有了自动显示，如果不关闭自动刷新，每循环一次都刷新则会很慢
但是如果关闭自动刷新，则进度显示的意义就不存在了
可以设定一个定时，没隔一段时间就刷新一次，cd内不刷新
thisWs.Cells(1, nextColumn) = i & "/" & rMaxSource
If Timer - t > 1 Then
Application.ScreenUpdating = True
DoEvents
t = Timer
Application.ScreenUpdating = False
End If
4.自动建新一列
自动寻找第一行为空的第一个列，将其视为空列，输出匹配的结果
For i = 1 To 1000
If thisWs.Cells(1, i) = "" Then
nextColumn = i
Exit For
End If
Next
5.所有命令合起来：
Sub 匹配()
Dim i As Long
Dim j As Long
Dim dict
Set dict = CreateObject("scripting.dictionary")
Dim theKey As String
Dim theValue As String
Dim sourceSheet As Long
Dim sourceIndexColumn As Long
Dim sourceMatchColumn As Long
Dim outputIndexColumn As Long
Dim nextColumn As Long
Dim thisWs As Worksheet
Set thisWs = ActiveSheet
'寻找第一个出现的空列
For i = 1 To 1000
If thisWs.Cells(1, i) = "" Then
nextColumn = i
Exit For
End If
Next
Dim t As Single
t = Timer
Application.ScreenUpdating = False
'以sourceSheet的sourceIndexColumn为副表的键，以当前activeSheet的outputIndexColumn为主表的键
'将sourceSheet的sourceMatchColumn的值填写到第一个出现的空列上面
sourceSheet = 8
sourceIndexColumn = 1
sourceMatchColumn = 5
outputIndexColumn = 3
Dim rMaxSource As Long
Dim rMaxThis As Long
rMaxSource = Sheets(sourceSheet).Cells(Rows.Count, 1).End(xlUp).Row
rMaxThis = thisWs.Cells(Rows.Count, 1).End(xlUp).Row
thisWs.Cells(1, nextColumn).Select
'making dict
With Sheets(sourceSheet)
For i = 1 To rMaxSource
theKey = .Cells(i, sourceIndexColumn)
theValue = .Cells(i, sourceMatchColumn)
dict.Item(theKey) = theValue
thisWs.Cells(1, nextColumn) = i & "/" & rMaxSource
If Timer - t > 1 Then
Application.ScreenUpdating = True
DoEvents
t = Timer
Application.ScreenUpdating = False
End If
Next
End With
'output the matching value
With thisWs
For i = 2 To rMaxThis
theKey = .Cells(i, outputIndexColumn)
theValue = dict.Item(theKey)
.Cells(i, nextColumn) = theValue
.Cells(1, nextColumn) = i & "/" & rMaxThis
If Timer - t > 1 Then
Application.ScreenUpdating = True
DoEvents
t = Timer
Application.ScreenUpdating = False
End If
Next
.Cells(1, nextColumn) = Sheets(sourceSheet).Cells(1, sourceMatchColumn)
End With
Application.ScreenUpdating = True
End Sub
