Lookup的用法详解含实例和动画Word格式文档下载.docx
- 文档编号:20004820
- 上传时间:2023-01-14
- 格式:DOCX
- 页数:13
- 大小:24.51KB
Lookup的用法详解含实例和动画Word格式文档下载.docx
《Lookup的用法详解含实例和动画Word格式文档下载.docx》由会员分享,可在线阅读,更多相关《Lookup的用法详解含实例和动画Word格式文档下载.docx(13页珍藏版)》请在冰豆网上搜索。
62005019孙楠2530财务科
72005036孟庭苇2200工会
A列已排序(第四个参数缺省或用TRUE)
VLOOKUP(2005001,A1:
D7,2,TRUE)等于“周杰伦”
D7,3,TRUE)等于“2870”
D7,4,TRUE)等于“办公室”
VLOOKUP(2005019,A1:
D7,2,TRUE)等于“孙楠”
VLOOKUP(2005036,A1:
D7,3,TRUE)等于“2200”
D7,4,TRUE)等于“工会”
D7,4)等于“工会”
若A列没有排序,要得出正确的结果,第四个参数必须用FALAE
D7,2,FALSE)等于“周杰伦”
D7,3,FALSE)等于“2870”
D7,4,FALSE)等于“办公室”
D7,2,FALSE)等于“孙楠”
D7,3,FALSE)等于“2200”
D7,4,FALSE)等于“工会”
五、关于TRUE和FALSE的应用
先举个例子,假如让你在数万条记录的表格中查找给定编号的某个人,假如编号已按由小到大的顺序排序,你会很轻松地找到这个人;
假如编号没有排序,你只好从上到下一条一条地查找,很费事。
用VLOOKUP查找数据也是这样,当第一列已排序,第四个参数用TRUE(或确省),Excel会很轻松地找到数据,效率较高。
当第一列没有排序,第四个参数用FALSE,Excel会从上到下一条一条地查找,效率较低。
笔者觉得,若要精确查找数据,由于计算机运算速度很快,可省略排序操作,直接用第四个参数用FALSE即可。
关于Lookup的实例
示例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
输入要查找的名称:
输入要查找的月份:
结果是:
=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
70
刹车片
£290
300
290
310
£500
=VLOOKUP(C80,F74:
I78,MATCH(B80,G73:
I73,0)+1,FALSE)
示例3
下面的示例是一个建材经销商提供的不同采购数量的折扣率
价格表中显示了砖,木材和玻璃的单价.
折扣表提供了不同产品不同采购数量的折扣率.
采购表是采购预算.
所有的预算结果显示在采购表中.
产品名称列表在C列.
单价是从价格表中获得的.
FALSE选项表示产品名称在价格表中没有排序整理.
使用FALSE强迫搜索精确匹配.如果没有找到,则函数显示错误.
折扣是从折扣表中获得的
如果采购数量与折扣表中某个值匹配,函数=VLOOKUP将在折扣表中查找正确的匹配折扣.
TRUE选项表示采购数量在折扣表中经过了升序排列整理.
使用TRUE允许模糊匹配.如果采购数量在折扣表中没有找到匹配的值,则它下面较小的值将被使用.
比如采购数量为125将向下与100匹配,并且使用100对应列的折扣率.
折扣表
价格表
砖
木材
玻璃
£2
1
0%
£1
6%
3%
12%
£3
8%
5%
15%
采购表
项目
采购数量
单价
折扣
合计
125
£235
200
£194
150
£396
225
£423
£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
0.525
3.25
400
0.616
2.93
0.675
2.75
0.746
2.57
0.835
2.38
0.946
2.17
1.09
1.95
1.29
1.71
公式
说明(结果)
在A列中查找1,并从相同行的B列中返回值(2.17)
=VLOOKUP(1,B128:
D136,2)
在A列中查找1,并从相同行的C列中返回值(100)
D136,3,TRUE)
#N/A
在A列中查找0.746。
因为A列中没有精确地匹配,所以返回了一个错误值(#N/A)
=VLOOKUP(0.7,B128:
D136,3,FALSE)
在A列中查找0.1。
因为0.1小于A列的最小值,所以返回了一个错误值(#N/A)
=VLOOKUP(0.1,B128:
D136,2,TRUE)
在A列中查找2,并从相同行的B列中返回值(1.71)
=VLOOKUP(2,B128:
Excel查询函数Lookup和Vlookup区别
电脑爱好者
张剑悦
Excel查询函数中,Lookup和Vlookup有哪些区别?
它们在应用中应该如何把握?
请看本文讲解。
★Lookup——数与行列比
Lookup的工作职责是什么呢?
用一个数与一行或一列数据依次进行比较,发现匹配的数值后,将另一组数据中对应的数值提取出来。
·
工资税率表:
用数值比较
根据不同的工资进行不同的税率计算是一个常见的应用。
我们来看这张“工资税率查询”表(见图1)。
现在要在右侧根据“收入”(F列),直接得到对应的“税率”(G列)。
在计算第1个“税率”时,输入函数公式“=LOOKUP(F4,$B$3:
$B$8,$D$3:
$D$8)”,回车,便可得到“36.00%”。
这个结果是怎么来的?
用F4中的第1个收入数“$123,409”,与左侧表的“收入最低”各档数据(“$B$3:
$B$8”)进行对比,虽然“$123,409”在“收入最低”各档数中没有完全一致的数据与之匹配,但是会与其中小于它的最大数“$58,501”相匹配。
这样,同一行对应的“36.00%”就提取出来了。
图书销售表:
用文本比较
Lookup函数的对比数还可以是文本。
在这张图书销售查询表中(见图2),用下表输入的“编号”(A15单元格)文本当作查询数,与上表的“编号”一列($A$3:
$A$11)进行对比,查询到了匹配的文本后,将“教材名称”一列($B$3:
$B$11)对应的数据提取出来。
公式是“=LOOKUP(A15,$A$3:
$A$11,$B$3:
$B$11)”。
★Vlookup——数与表格比
Lookup有一个大哥——Vlookup函数。
两兄弟有很多相似之处,但大哥本领更大。
Vlookup用对比数与一个“表”进行对比,而不是Lookup函数的某1列或1行,并且Vlookup可以选择采用精确查询或是模糊查询方式,而Lookup只有模糊查询。
模糊匹配
用Vlookup函数进行模糊查询时,几乎与Lookup的作用完全一致。
我们用Vlookup函数来提取第1个例子中的工资税率结果。
函数公式为“=VLOOKUP(F4,$B$3:
$D$8,3,TRUE)”。
在这个函数中,用第1个收入“$123,409”(F4单元格)当作对比数,用它与左侧表(“$B$3:
$D$8”)的第1列数进行对比,虽然“$123,409”在“收入最低”各档数中没有完全一致的数据与之匹配,但是函数的最后一个参数是“TURE”(“TURE”就是模糊查询),所以它会与其中小于它的最大数“$58,501”相匹配。
并将表中第3列(函数的第3个参数为“3”)对应的数据提取出来,所以结果同样是“36.00%”。
订单明细表:
精确匹配
有时候,我们需要精益求精。
在下面这个“订单明细表”(见图3)中,最后一列“货运费用”中的数据要通过“交货方式”从左侧“配送公司收费表”中进行匹配查询。
这是一个典型的精确查询的例子,计算第1个数据的函数公式是“=VLOOKUP(H3,$B$2:
$D$6,3,FALSE)”。
小提示:
把最后一个参数从“TRUE”变更成“FLASE”,就是精确匹配。
而精确查询,就是查询数要与查询表第1列中的数据完全一致才能匹配提取,否则结果返回错误值“#N/A”。
点评:
Excel为我们提供了近20个有关“查找和引用”的函数,除了最常用的Lookup、Vlookup,还有Choos、Row、Colum、Index和Match等,大家可以通过函数的帮助查看具体的功能。
这些函数往往不是单独使用,可以与其他函数和Excel中的一些功能进行配合。
EXCEL函数VLOOKUP高级应用
VLOOKUP是处理根据条件查找对象的函数
这里有几个数组公式可以达到二个条件(在两列)或多个条件查找的功能
=VLOOKUP(A2&
B2,IF(COLUMN(A1:
B1)=COLUMN(A1:
A1),sheet1!
$A$2:
$A$110&
sheet1!
$B$2:
$B$110,sheet1!
$C$2:
$C$110),2,)
=INDEX(sheet1!
C$2:
C$110,MATCH(A2&
B2,sheet1!
A$2:
A$110&
B$2:
B$110,0))
=LOOKUP(2,1/((sheet1!
$A$110=A2)*(sheet1!
$B$110=B2)),sheet1!
$C$110)
=SUMPRODUCT((sheet1!
$B$110=B2),sheet1!
前两个需按数组公式组合键。
符合两列或两列以上数据查找对应结果{=VLOOKUP(A3&
C3&
E3,CHOOSE({1,2},基础表!
$A$3:
$A$123&
基础表!
$C$3:
$C$123&
$E$3:
$E$123,基础表!
$D$3:
$D$123),2,0)}
相当于ACE列合并形成数组:
$E$123和基础表!
$D$123中查找取第2个数组如果使用vlookup(XXXXX,choose({1,2,3,4...n},数组1,数组2...数组n),K),还可以解决许多比这复杂的问题即可取第N个数组
只有两个数组时CHOOSE和IF相同
返回有重复列数据中所有有重复的内容
=IF(ROW(A1)>
COUNT(IF((COUNTIF($A$2:
$A$7,$A$2:
$A$7)>
1)*(MATCH($A$2:
$A$7,0)=ROW($A$2:
$A$7)-1),ROW($A$2:
$A$7))),"
"
INDEX(A:
A,SMALL(IF((COUNTIF($A$2:
$A$7)),ROW(A1))))
返回有重复列数据中所有内容,达到不重复。
=IF(SUM(1/COUNTIF(A$2:
A$7,A$2:
A$7))>
=ROW(A1),INDEX(A$2:
A$7,SMALL(IF(ROW(A$2:
A$7)-1=MATCH(A$2:
A$7,0),ROW(A$2:
A$7)-1,"
0"
),ROW(A1))),"
)
另敬请注意看贴的网友,此楼的贴子如不注明为数组,即默认为数组公式。
顶一下,不能让他沉下去在B列中找出与A列重复的,{=OFFSET($B$1,SMALL(MATCH($A$1:
$A$8,$B$1:
$B$19,0),ROW(1:
1))-1,0)}
解释SMALL({10,20,30},1)中,得到的结果是“10”,SMALL函数是取得数据区域中指定的第几个最小值。
在这个公式中的“1”是指第一小值,也就是最小值。
公式...SMALL(MATCH($A$1:
1))...是先用MATCH()函数取得$A$1:
$A$8的数据在$B$1:
$B$19中的行数值,设A1在第12行,A2在第14行A3在15,A4在16,A5在3,A6在6行......,最后的结果是{12,14,15,16,3,6},然后再套上SMALL(),后面的ROW(1:
1)等于1,也就是取得{12,14,15,16,3,6}中的最小值是“3”,“3”对应在B列第3行内容,即公式得到的第一个重复内容,向下填充,即可相应得到第二个第..个
1)等于1,也就是取得{12,14,15,16,3,6}中的最小值是“3”,“3”对应在B列第3行内容,即公式得到的第一个重复内容,向下填充,即可相应得到第二个第..个=SUM((A2:
A100="
渭塘实小"
)*(C2:
C100="
男"
)*(D2:
D100-DATE(1958,1,1)>
0)*(E2:
E100="
本科"
))
用VLOOKUP函数查找引用其他工作表数据和自动填充数据
VLOOKUP函数,在表格或数值数组(数据表)的首列查找指定的数值(查找值),并由此返回表格或数组当前行中指定列(列序号)处的数值。
VLOOKUP(查找值,数据表,列序号,[匹配条件])
例如在SHEET2表中有全部100个学生的资料,B列为学号、C列为姓名、D列为班级,现在在SHEET1表的A列有学号,我们需要使用该函数,将SHEET2表中对应学号的姓名引用到SHEET1表的B列。
我们只需在SHEET1的B2输入以下公式 =VLOOKUP(A2,SHEET2!
$B:
$D,2,FALSE) (或者=VLOOKUP(A2,SHEET2!
$D$101,2,0),就得到了A2单元格学号对应的学生姓名。
同理, 在SHEET1表的C2输入公式 =VLOOKUP(A2,SHEET2!
$D,3,FALSE),即可得到对应的班级.
VLOOKUP(A2,SHEET2!
$D,
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Lookup 用法 详解 实例 动画