实例讲解vlookup函数的使用方法
今天我们继续与大家一起来学习 Excel 电子表格中各个常用公式(函数)的详细用法,
Excel中的一个纵向查找函数,它与LOOKUP函数和HLOOKUP函数属于一类函数,VLOOKUP是按列查找,最终返回该列所需查询列序所对应的值;与之对应的HLOOKUP是按行查找的。
掌握了公式,我们的很多工作就会大大提高效率的,大家要好好学哟,不要辜负office之家的一片期望哦,大家有进步,我就会觉得高兴,这一讲,我们来学习一下Vlookup,感兴趣的各位可不要错过了哈,希望可以帮助虚心上进的你
列数并非必需的,它只是便于在不便中说明 | ||||||||
列1 | 列2 | 列3 | 列4 | 列5 | 列6 | |||
元月 | 10 | 20 | 30 | 40 | 50 | |||
二月 | 80 | 90 | 100 | 110 | 120 | |||
三月 | 97 | 69 | 45 | 51 | 77 | |||
输入要查找的月份: | 三月 | |||||||
需要挑选出的列: | 4 | |||||||
结果是 : | 45 | |||||||
=VLOOKUP(G11,C6:H8,G12,FALSE) |
这个函数在表格左侧的行标题中查找指定的内容
当找到时,它再挑选出该行对应的指定列的单元格内容。
语法
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
Lookup_value 为需要在数组第一列中查找的数值。Lookup_value 可以为数值、引用或文本字符串。
Table_array 为需要在其中查找数据的数据表。可以使用对区域或区域名称的引用,例如数据库或数据清单。
如果 range_lookup 为 TRUE,则 table_array 的第一列中的数值必须按升序排列:…、-2、-1、0、1、2、…、-Z、FALSE、TRUE;否则,函数 VLOOKUP 不能返回正确的数值。如果 range_lookup 为 FALSE,table_array 不必进行排序。
通过在“数据”菜单中的“排序”中选择“升序”,可将数值按升序排列。
Table_array 的第一列中的数值可以为文本、数字或逻辑值。
文本不区分大小写。
Col_index_num 为 table_array 中待返回的匹配值的列序号。Col_index_num 为 1 时,返回 table_array 第一列中的数值;col_index_num 为 2,返回 table_array 第二列中的数值,以此类推。如果 col_index_num 小于 1,函数 VLOOKUP 返回错误值值 #VALUE!;如果 col_index_num 大于 table_array 的列数,函数 VLOOKUP 返回错误值 #REF!。
Range_lookup 为一逻辑值,指明函数 VLOOKUP 返回时是精确匹配还是近似匹配。如果为 TRUE 或省略,则返回近似匹配值,也就是说,如果找不到精确匹配值,则返回小于 lookup_value 的最大数值;如果 range_value 为 FALSE,函数 VLOOKUP 将返回精确匹配值。如果找不到,则返回错误值 #N/A。
说明
如果函数 VLOOKUP 找不到 lookup_value,且 range_lookup 为 TRUE,则使用小于等于 lookup_value 的最大值。
如果 lookup_value 小于 table_array 第一列中的最小数值,函数 VLOOKUP 返回错误值 #N/A。
如果函数 VLOOKUP 找不到 lookup_value 且 range_lookup 为 FALSE,函数 VLOOKUP 返回错误值 #N/A。
格式
没有专门的格式
示例 1
下面的下示例是以指定的名字和月份为基础查找一个数值.
=VLOOKUP()是用于沿第一列向下查找指定的名字.
难点是如何向右查找指定的月份.
解决这个难题的方法是使用=MATCH()函数.
函数=MATCH()通过使用找到名字的列表查找对应月份.并推算该月份在列表中的位置.
不巧的是,因为月份列表的搜索范围与查找数值的范围不等宽.
函数=MATCH()函数返回的数字比我们需要的数字少1, 因此在公式中用了+1进行调整.
函数=VLOOKUP()现在使用函数 =MATCH()得到的调整的数字n,在对应名字所在行向右查找到该行第n列对应单元格的输入内容.
函数=VLOOKUP()中最后使用了 FALSE,因此左侧标题行不用排序。
元月 | 二月 | 三月 | ||
程香宙 | 10 | 80 | 97 | |
刘冰 | 20 | 90 | 69 | |
程龙 | 30 | 100 | 45 | |
程坤 | 40 | 110 | 51 | |
chengxiang | 50 | 120 | 77 | |
输入要查找的名称: | 程龙 | |||
输入要查找的月份 : | 三月 | |||
结果是: | 45 | |||
=VLOOKUP(F60,C54:F58,MATCH(F61,D53:F53,0)+1,FALSE) | ||||
3 | =MATCH(F61,D53:F53,0) |
示例 2
这个示例使用函数=VLOOKUP() 查找不同小车生产厂商不同配件的价值。
函数=VLOOKUP()向下扫描F列的标题行并查找对应的位于C列的配件名称.
找到配件后,函数VLOOKUP根据函数MATCH找到的位置查找到对应配件的价格。
公式中使用了绝对引用,为的是确保公式复制移动时函数 =HLOOKUP() 和=MATCH()引用的范围不发生变化。
厂商 | 配件 | 价值 | 查找表格 | ||||
日本丰田 | 火花塞 | £50 | 日本丰田 | 福特 | 奔驰 | ||
奔驰 | 变速箱 | £600 | 变速箱 | 500 | 450 | 600 | |
福特 | 引擎 | £1,200 | 引擎 | 1000 | 1200 | 800 | |
奔驰 | 方向盘 | £275 | 方向盘 | 250 | 350 | 275 | |
福特 | 火花塞 | £70 | 火花塞 | 50 | 70 | 45 | |
福特 | 刹车片 | £290 | 刹车片 | 300 | 290 | 310 | |
日本丰田 | 变速箱 | £500 | |||||
福特 | 引擎 | £1,200 | |||||
=VLOOKUP(C80,F74:I78,MATCH(B80,G73:I73,0)+1,FALSE) |
示例 3
下面的示例是一个建材经销商提供的不同采购数量的折扣率
价格表中显示了砖,木材和玻璃的单价.
折扣表提供了不同产品不同采购数量的折扣率.
采购表是采购预算.
所有的预算结果显示在采购表中.
产品名称列表在C列.
单价是从价格表中获得的.
FALSE选项表示产品名称在价格表中没有排序整理.
使用FALSE强迫搜索精确匹配. 如果没有找到,则函数显示错误.
折扣是从折扣表中获得的
如果采购数量与折扣表中某个值匹配,函数 =VLOOKUP将在折扣表中查找正确的匹配折扣.
TRUE选项表示采购数量在折扣表中经过了升序排列整理.
使用TRUE允许模糊匹配.如果采购数量在折扣表中没有找到匹配的值,则它下面较小的值将被使用.
比如采购数量为125将向下与100匹配,并且使用100对应列的折扣率.
折扣表 | |||||||
价格表 | 砖 | 木材 | 玻璃 | ||||
砖 | £2 | 1 | 0% | 0% | 0% | ||
木材 | £1 | 100 | 6% | 3% | 12% | ||
玻璃 | £3 | 300 | 8% | 5% | 15% | ||
采购表 | |||||||
项目 | 采购数量 | 单价 | 折扣 | 合计 | |||
砖 | 125 | £2 | 6% | £235 | |||
木材 | 200 | £1 | 3% | £194 | |||
玻璃 | 150 | £3 | 12% | £396 | |||
砖 | 225 | £2 | 6% | £423 | |||
木材 | 50 | £1 | 0% | £50 | |||
玻璃 | 500 | £3 | 15% | £1,275 |
公式为
单价 E118: =VLOOKUP(C118,C106:D108,2,FALSE)
折扣 F118: =VLOOKUP(D118,F106:I108,MATCH(C118,G105:I105,0)+1,TRUE)
合计 G118: =(D118*E118)-(D118*E118*F118)
示例4
该示例使用 1 个大气压的空气值。
密度 | 粘度 | 温度 |
0.457 | 3.55 | 500 |
0.525 | 3.25 | 400 |
0.616 | 2.93 | 300 |
0.675 | 2.75 | 250 |
0.746 | 2.57 | 200 |
0.835 | 2.38 | 150 |
0.946 | 2.17 | 100 |
1.09 | 1.95 | 50 |
1.29 | 1.71 | 0 |
公式 | 说明(结果) | |||
2.17 | 在 A 列中查找 1,并从相同行的 B 列中返回值 (2.17) | =VLOOKUP(1,B128:D136,2) | ||
100 | 在 A 列中查找 1,并从相同行的 C 列中返回值 (100) | =VLOOKUP(1,B128:D136,3,TRUE) | ||
#N/A | 在 A 列中查找 0.746。因为 A 列中没有精确地匹配,所以返回了一个错误值 (#N/A) | =VLOOKUP(0.7,B128:D136,3,FALSE) | ||
#N/A | 在 A 列中查找 0.1。因为 0.1 小于 A 列的最小值,所以返回了一个错误值 (#N/A) | =VLOOKUP(0.1,B128:D136,2,TRUE) | ||
1.71 | 在 A 列中查找 2,并从相同行的 B 列中返回值 (1.71) | =VLOOKUP(2,B128:D136,2,TRUE) |