【知识点】VLOOKUPG2语法格式及解决办法(二)
VLOOKUP 语法格式:
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
VLOOKUP(要搜索的值,搜索区域,搜索区域的哪一列是要返回的结果,完全匹配还是近似匹配)
一、精确搜索
按名称查找对应的部门:
输入公式:=VLOOKUP(G2,A:C,3,0)
G2:寻找什么
A:C:查找区域,注意搜索区域的第一列包含搜索到的内容
3:要返回的结果在搜索区域的第三列
0:精确搜索
二、近似搜索
根据分数找到对应的关卡:
输入公式:=VLOOKUP(B2,E:F,2,1)
B2:寻找什么
E:F:搜索区,注意搜索区第一列包含搜索内容
2:要返回的结果在搜索区域的第二列
1:近似搜索
请注意,搜索区域中第一列的内容必须按升序排列。
三、搜索不一致的格式
求数据4的个数:
输入公式:=VLOOKUP(D2,A:B,2,0)
D2:寻找什么
A:B:查找区域,注意搜索区域的第一列包含搜索到的内容
2:要返回的结果在搜索区域的第二列
0:精确搜索
这样就可以了一次函数教案格式,为什么结果返回错误值#N/A?
仔细看会发现格式不一致
求值数值型(D2单元格内容4为数值型)
搜索区域文本类型(A列数据为文本类型)
如何解决这个问题呢?
格式一致
首先可以使用column函数将A列排序为正则,与D2单元格的格式一致
二是D2单元格的内容可以设置为文本格式,与A列的格式一致
第三个是变量公式
公式:=VLOOKUP(D2&"",A:B,2,0)
将查找值转换为文本
接下来顺便说一下,还有一个格式不一致的问题:
搜索值文本类型、搜索区域数字类型
查找值文本类型(D2单元格内容4为文本类型)
搜索区域数字类型(A列数据为数字类型)
输入公式:
=VLOOKUP(D2^1,A:B,2,0)
^1 是将搜索值转换成与搜索区域一致的格式
转换方法有很多:--、+0、-0、*1、/1...等。
四、通配符搜索
根据缩写查找对应的应收账款:
输入公式:
=VLOOKUP("*"&D2&"*",A:B,2,0)
星号 (*) 匹配任何字符串。
五、用“~”搜索
按名称查找对应的部门:
公式没有错,为什么结果返回错误值#N/A?
因为搜索内容有波浪号 (~)
输入公式:
=VLOOKUP(SUBSTITUTE(G2,"~","~~"),A:C,3,0)
查找本身包含通配符的内容时,需要在通配符前输入“~”
使用函数 SUBSTITUTE 将“~”替换为“~~”。
六、取消合并单元格
内容为数字,取消合并单元格:
输入公式:
=VLOOKUP(9E+307,A$2:A2,1,1)
9E+307是科学记数法,表示9*10^307,是Excel允许输入的最大值。
内容为文本,取消合并单元格:
输入公式:
=VLOOKUP("块",E$2:E2,1,1)
七、找到第一个价格
根据物料名称找到对应的首价:
输入公式:
=VLOOKUP(F2,B:D,3,0)
当第一个搜索区域中列出的两个或多个现有值与搜索值匹配时,函数 VLOOKUP 返回相应值的第一次出现。
八、交叉查询
根据产品和地区查找对应的销量:
输入公式:
=VLOOKUP(A12,A2:G8,MATCH(B12,A1:G1,0),0)
在MATCH(B12,A1:G1,0)部分,找到A1:G1区域内B12单元格内容“华北”的位置5,作为VLOOKUP函数的第三个参数;
公式为:=VLOOKUP(A12,A2:G8,5,0)
找到单元格A12“产品D”的内容
返回值位于 A2:G8 范围内的第 5 列,即 E 列
即单元格 E5 中的值 6945
九、反向搜索
根据工号找到对应的名字:
VLOOKUP函数可以利用IF{1,0}和IF{0,1}、CHOOSE{1,2}和CHOOSE{2,1}的结构将逆序转换为顺序实现查找。
函数 VLOOKUP+ IF{1,0} 结构:
输入公式:
=VLOOKUP(D2,IF({1,0},B2:B11,A2:A11),2,0)
IF({1,0},B2:B11,A2:A11) 部分
为1时,条件成立,返回B2:B11
为0时,不满足条件,返回A2:A11
可以把IF({1,0},B2:B11,A2:A11)的部分涂抹一下,按F9查看
就是要交换的两列的顺序,逆序转换为顺序
函数 VLOOKUP+ IF{0,1} 结构:
输入公式:
=VLOOKUP(D2,IF({0,1},A2:A11,B2:B11),2,0)
函数 VLOOKUP+CHOOSE{1,2} 的结构:
输入公式:
=VLOOKUP(D2,CHOOSE({1,2},B2:B11,A2:A11),2,0)
函数CHOOSE:根据给定的索引值,从参数字符串中选择对应的值或操作。
选择(index_num, value1, [value2], ...)
如果第一个参数为 1,则 CHOOSE 返回 value1;如果第一个参数是 2,则 CHOOSE 返回 value2。
选择({1,2},B2:B11,A2:A11) 部分
当条件为 1 时,返回 B2:B11
当条件为2时,返回A2:A11
函数 VLOOKUP+CHOOSE{2,1} 的结构:
输入公式:
=VLOOKUP(D2,CHOOSE({2,1},A2:A11,B2:B11),2,0)
选择({2,1},A2:A11,B2:B11) 部分
当第一个参数为2时,CHOOSE返回B2:B11对应的值;
当第一个参数为 1 时,CHOOSE 返回 A2:A11 对应的值。
把CHOOSE({2,1},A2:A11,B2:B11)的部分涂抹一下一次函数教案格式,按F9查看
AB两列的顺序互换,逆序转换为顺序,然后用函数VLOOKUP查找。
十、搜索返回多列数据
输入公式:
=VLOOKUP($F2,$A:$D,COLUMN(B1),0), 右拉填充
公式右拉返回第4列结果2、3、
用函数 COLUMN 构造
COLUMN(B1)=2,公式右拉变成COLUMN(C1),COLUMN(D1)得到3、4.
十一、 重复指定次数
输入公式:
=IFERROR(VLOOKUP(ROW(A1),IF({1,0},SUMIF(OFFSET(B$2,,,,ROW($1:$4)),""),A$2 : A$5),2,0),E2)&""
按三个键结束
十个二、结果引用合并单元格的内容
A 列中的区域是合并的单元格。根据营业员查找对应区域:
输入公式:
=VLOOKUP("座",OFFSET(A2,,,MATCH(D2,B2:B14,0)),1,1)
MATCH(D2,B2:B14,0) 部分找到B2:B14 11区销售员“Awen”的位置
OFFSET(基点、偏移行号、偏移列号、行高、列宽)
OFFSET(A2,,,11) 以单元格A2为基点,偏移0行0列,返回行高为11的新区域A2:A12的引用。
OFFSET部分失信按F9键得到:
用“座”等较大的汉字查找该区域最后一个单元格的内容,即返回“华北”。
十三、 查找合并单元格
A 列中的产品是合并单元格。如何找到A列产品对应的单价?
输入公式:
=VLOOKUP(VLOOKUP(“座”,A$2:A2,1,1),F:G,2,0)
例如D5单元格中的公式=VLOOKUP(VLOOKUP("座",A$2:A5,1,1),F:G,2,0)
A$2:A5 部分返回 {"Product 1";"Product 3";0;0}
VLOOKUP("Block",A$2:A5,1,1) 部分使用"Block"查找最后一个单元格的内容,即返回"Product 3"
在外层设置一个 VLOOKUP 进行精确搜索
即D5单元格的公式为=VLOOKUP("Product 3",F:G,2,0),单价为12
10.四、和T+IF的组合应用
输入公式:
=SUM(VLOOKUP(T(IF({1},A2:A8)),D2:E8,2,0)*B2:B8)
数组公式,按三个键结束
IF({1},A2:A8) 部分构成一个三维内存数组
VLOOKUP函数的第一个参数不能直接是数组
函数T起到降维的作用,将一个3维的引用转换为一个一维数组,返回的结果仍然是一个数组,用函数SUM求和求和。
十五、多条件搜索
与反向搜索一样,您可以使用 IF{1,0} 和 IF{0,1}、CHOOSE{1,2} 和 CHOOSE{2,1} 结构
输入公式:
=VLOOKUP(E2&F2,IF({1,0},A2:A11&B2:B11,C2:C11),2,0)
数组公式,按三个键结束
十个六、一对多搜索
输入公式:
=IFERROR(VLOOKUP($E$2&ROW(A1),IF({1,0},$A$2:$A$11&COUNTIF(INDIRECT("A2:A"&ROW($2:$11)@) >),$E$2),B$2:B$11),2,),"")
数组公式,按三个键结束
效果图:
十个七、动态图
【数据】→【数据验证】
输入公式:
=VLOOKUP($A9,$A$2:$G$5,COLUMN(B1),0),右拉填充
【插入】→【插入柱状图】
操作演示:
我们不嫌她老