LOOKUPVLOOKUP查找与引用函数专题详细Word文件下载.docx
- 文档编号:3606481
- 上传时间:2023-05-02
- 格式:DOCX
- 页数:16
- 大小:717.95KB
LOOKUPVLOOKUP查找与引用函数专题详细Word文件下载.docx
《LOOKUPVLOOKUP查找与引用函数专题详细Word文件下载.docx》由会员分享,可在线阅读,更多相关《LOOKUPVLOOKUP查找与引用函数专题详细Word文件下载.docx(16页珍藏版)》请在冰点文库上搜索。
2.Lookup_value是一个很重要的参数,它可以是数值、文字字符串、或参照地址。
我们常常用的是参照地址。
用这个参数时,有三点要特别提醒:
A)参照地址的单元格格式类别与去搜寻的单元格格式的类别要一致,否则的话有时明明看到有资料,就是抓不过来。
特别是参照地址的值是数字时,最为明显,若搜寻的单元格格式类别为文字,虽然看起来都是123,但是就是抓不出东西来的。
而且格式类别在未输入数据时就要先确定好,如果数据都输入进去了,发现格式不符,已为时已晚,若还想去抓,则需重新输入。
B)在使用参照地址时,有时需要将lookup_value的值固定在一个格子内,而又要使用下拉方式(或复制)将函数添加到新的单元格中去,这里就要用到“$”这个符号了,这是一个起固定作用的符号。
比如说我始终想以D5格式来抓数据,则可以把D5弄成这样:
$D$5,则不论你如何拉、复制,函数始终都会以D5的值来抓数据。
C)用“&
"
连接若干个单元格的内容作为查找的参数。
在查找的数据有类似的情况下可以做到事半功倍。
3.Table_array是搜寻的范围,col_index_num是范围内的栏数。
Col_index_num 不能小于1,其实等于1也没有什么实际用的。
如果出现一个这样的错误的值#REF!
,则可能是col_index_num的值超过范围的总字段数。
4.在使用该函数时,lookup_value的值必须在table_array中处于第一列。
语法
该函数的语法规则如下:
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
参数
简单说明
输入数据类型
Lookup_value
要查找的值
数值、引用或文本字符串
Table_array
要查找的区域
数据表区域
Col_index_num
返回数据在区域的第几列数
正整数
Range_lookup
模糊匹配
TRUE(或不填)/FALSE
示例
★HLOOKUP函数
要结合MATCH函数研究,略。
。
★LOOKUP函数
含义
返回向量或数组中的数值。
函数
LOOKUP有两种语法形式:
向量和数组。
函数LOOKUP的向量形式是在单行区域或单列区域(向量)中查找数值,然后返回第二个单行区域或单列区域中相同位置的数值;
函数LOOKUP的数组形式在数组的第一行或第一列查找指定的数值,然后返回数组的最后一行或最后一列中相同位置的数值。
函数LOOKUP有两种语法形式:
提示
LOOKUP_vector的数值必须按升序排序:
...、-2、-1、0、1、2、...、A-Z、FALSE、TRUE;
否则,函数LOOKUP不能返回正确的结果。
文本不区分大小写。
(1)向量形式:
公式为=LOOKUP(lookup_value,lookup_vector,result_vector)
式中lookup_value—函数LOOKUP在第一个向量中所要查找的数值,它可以为数字、文本、逻辑值或包含数值的名称或引用;
Lookup_vector—只包含一行或一列的区域lookup_vector的数值可以为文本、数字或逻辑值;
Result_vector—只包含一行或一列的区域其大小必须与lookup_vector相同。
(2)数组形式:
公式为
=LOOKUP(lookup_value,array)
式中array—包含文本、数字或逻辑值的单元格区域或数组它的值用于与lookup_value进行比较。
例如:
LOOKUP(5.2,{4.2,5,7,9,10})=5。
注意:
lookup_vector的数值必须按升序排列,否则函数LOOKUP不能返回正确的结果。
如果函数LOOKUP找不到lookup_value,则查找lookup_vector中小于或等于lookup_value的最大数值。
如果lookup_value小于lookup_vector中的最小值,函数LOOKUP返回错误值#N/A。
该函数对引用区LOOKUP-vector有顺序要求,所以相对严谨。
=LOOKUP(E2,$B$2:
$B$7,$C$2:
$C$7)
所以LOOKUP只适合于升序的数字查找区,而引用区的数字或文本顺序没要求。
这类适用于查找有编号的数字区。
,
修改结果返回区后,发现result-vectorI不一定要为数字,其它文本符号等都可以。
这里千万要记住引用的查找区lookup–vector一定要为顺序排列的数字。
反例
对于求和的情况,那LOOKUP虽然查找效果不错,但不适合用,反倒SUMIF合适。
=IF(SUM(SUMIF($A$1:
$A$4,F1,$C$1:
$C$4),SUMIF($A$8:
$A$12,F1,$C$8:
$C$12))>
0,SUM(SUMIF($A$1:
$C$12)),"
)
因为字母虽然按顺序来,但不是数字,所以LOOKUP做不到所需要的效果。
EH均查找不到按照lookup的查找规则会返回小于EH的数D为最后一个小于E和H的数所以D即为查找值
所以可以采用lookup(1,0/
这种方式就是精确查找
其公式详解见
另外还有match+index
高深的研究,有待再探索(下面是别人发的一个LOOKUP比VLOOKUP优秀的例子)
=LOOKUP((ROW(1:
4)-1)*5+COLUMN(A:
E),ROW(1:
20),A1:
A20)
举个上面的例子lookup的变形一列转换为4列多单元格数组公式
再举个例子
合并单元格的查找
利用lookup返回小于等于查找值这个原理
将合并单元格中为空的
补齐:
=INDEX(C:
C,MATCH(F1&
G1,LOOKUP(ROW(1:
12),IF(A1:
A12<
>
ROW(1:
12)),A1:
A12)&
B1:
B12,))
这其中的
lookup
就是补齐为空的a列数据
还有文本+数字
提取数字的
这个很常见
就不举例了
与LOOKUP相关结合的函数:
offset,matchsumproduct
在Excel中,OFFSET函数的功能为以指定的引用为参照系,通过给定偏移量得到新的引用
OFFSET(reference,rows,cols,height,width)
MATCH函数含义:
返回指定数值在指定数组区域中的位置
MATCH(lookup_value,lookup_array,match_type)
lookup_value:
需要在数据表(lookup_array)中查找的值。
lookup_array:
可能包含有所要查找数值的连续的单元格区域,区域必须包含在某一行或某一列。
match_type:
为1时,查找小于或等于lookup_value的最大数值在lookup_array中的位置,lookup_array必须按升序排列:
为0时,查找等于lookup_value的第一个数值,lookup_array按任意顺序排列:
为-1时,查找大于或等于lookup_value的最小数值在lookup_array中的位置,lookup_array必须按降序排列。
另外,大家是否有另一个担忧——如果区域不是升序排列的呢?
lookup还可能完成查找吗?
当然,答案仍然是肯定的。
下面举例说明:
图十 lookup处理乱序排列的数据
从上图中,我们可以看到,参考数据区的姓名以乱序排列,而lookup是不能查找乱序数据的。
怎么办?
变通方式是:
将查找区域值变成一个只有两种类型的值,例如五个数据{#DIV/0!
0,#DIV/0!
#DIV/0!
},其中四个是错误值,一个是0.那么我们将lookup的第一参数设为0,就可以找到它对应的位置。
但是如何将当字变成错误值和0呢?
请看思路:
“张三丰”=“张三丰”,它的结果是true,表示两者相等,而0/true的结果等于0;
“张三丰”=“李四光”结果为false,表示两者不等,而0/false的结果等于错误值,因为除法中0不能做为除数,强制计算那么结果就为#DIV/0!
有了思路就可以动手搭建公式了。
1.以图十为例,首先用A2=D2:
D9,它得到的结果是{FALSE;
FALSE;
TRUE;
FALSE}
2.然后用0/(A2=D2:
D9)得到一串错误值和一个唯一的零值:
{#DIV/0!
;
#DIV/0!
0;
}
3.有了这个结果所有问题都解决了,直接在其中查找0就行了。
最后公式是:
=LOOKUP(0,0/(A2=D$2:
D$9),E$2:
E$9)
大家可以把这个普通公式当做一个定理来用,当目标区不是升序排列时(是升序也可以用):
=lookup(0,0/(查找值=查找区域),目标存放区域)
LOOKUP二分法的理解
先引一个例子进行理解性的扩展解释:
=LOOKUP(1,0/(A1:
A65535<
),A1:
A65535)
公式解析:
这是单条件查找唯一值的查询公式,区域<
条件,是一个条件表达式,生成的结果是逻辑值集合。
0/逻辑值集合的结果是0和1的集合(逻辑值之间是可以相互运算的)。
a1:
a100<
在执行过程中
第一步判断
A1是不是不为空,如果是空单元格
那么
值为FALSE,如果不是,结果为TRUE。
判断好了A1
就接着判断A2,一直判断到区域的第后一个单元格。
每一次判断,都会形成一个逻辑值结果,也因此,区域<
会形成一个逻辑值的集合。
比如{true,false,fasle。
区域中有多少个单元格,也就会生成多少个逻辑值的集合,在你这个应用中,集合中的TRUE值只有1个。
0/逻辑值
你可以想一下
0/true,其实就是
0除以1
结果为0
0/false
其实就是0除以0,结果为错误值
0是不能当除数的,那样无意义
在唯一值查找中,条件生成的逻辑值集中,仅有一个TRUE
那么0/逻辑值集合的结果,是一堆错误值
和一个唯一的0的集合
lookup(1,0/逻辑值集合,结果区域)
LOOKUP
是遵循模糊查找的规则
也默认第二参数的查找区域为
升序
尽管你生成的查找区域为乱序的,LOOKUP在查找过程中也当它是升序
因为查找区域中,是一个0和多个错误值的集合
尽管集合中没有1。
0/后不可能会出现大于1的数,所以必定返回最后一个等于0的值
但LOOKUP查找是模糊查找,而不是精确查找
所以,返回的是
<
=查找值
且最接近于查找值的值
因为集合中只有一个数字
所以,返回来的是这个数字
确定这个数字的位置后,再返回
结果区域同样位置的值,也就是最终的答案了吧
LOOKUP可以用于多条件查找唯一值
之所以唯一值,核心部分是条件生成的集合中,只有一个0,只要定位这个0在集合中的位置,再提取结果区域相同位置的值就行了。
0/((条件区域1=条件1)*(条件区域2=条件2))
lookup之所以查找速度快跟它的二分法查找原理有关。
这个理解了以后就很难出错了。
TRUE*TRUE=1
TRUE*FALSE=0
FALSE*FALSE=0
条件*条件,只有两个条件都成立的情况下,结果才为1,这就是双条件,达到了双条件成立,被0除时,才不会是错误值,才能让LOOKUP定位到位置。
所以,这个查找值,你可以是1也可以是2,只要>
=0的自然数,都可以。
计算原理:
第一次比较,取数据中的中间值0.2。
满足条件的话,继续向后比较,第二次取0.1比较
满足条件,继续向后比较,第三次取0.3
仍满足条件,得到结果5
多条件查找多个结果中的最后一个结果,可不能这样子来了
max(条件1*条件2*row(区域))
可以确定满足两条件的多个结果中最后一个结果所在的行号。
这也就意味着确定了,两个条件满足后的最后一个结果的行号。
再通过
index
indirect
offset
的常规查找手段就可以得出结果了。
一个复杂的应用,可以拆解成多个小应用,你只要把多个小应用逐个逐个的实现,再拼合成一个大应用。
这也就要求你对常用函数能有所掌握了,如果你理解的多,解题的思路也就多。
我可以肯定你现在是一知半解,甚至还没理解,哈
你仅仅是通过了这个应用,了解了一个LOOKUP的解题的一个方法
而实质的原理,为什么要这样子做的原因,你肯定不理解的
在乱序的状态下,LOOKUP得出来的结果很有可能是莫名其妙的
乱序仍旧遵循二分法规则,结果不会乱,看看这个就知道了虽然有满足条件的三个1,但最终还是第一个1满足条件,同样只需要比较两次,第一次取5,第二取1。
A1:
A9是一个区域,有9个数字,查找值59,为什么不是45呢?
而是10!
在查找时,首先要确定一个“二分点”,你可以理解为“中点”。
因为是9个数,中点就是60,查二分点>
查找值时,LOOKUP会认为,二分点下面的所有的数都>
查找值,所以,下面的整体忽略
三次比较
就得到最终结果了
只要上面的四个数字中继续
查找
四个数字中,继续
定位
第二个二分点
第一次取5满足。
第二次取7满足。
第三次取8满足。
第四次取9,9不满足,返回8对应的数据
二分法,这就相当于一段
自来水管子
如果
有一段没有水了
那就在水管的中间取一点看看有没有水
如果有,那上前面的肯定有,后面的继续查
后面的在取中间
如果没有水,那断点在前面
这就是速度
VLOOKUP是遍历的
LOOKUP都是跳跃的
对于VLOOKUP,会在查找区域中,一个一个进行比较,不对的,继续往下找
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- LOOKUPVLOOKUP 查找 引用 函数 专题 详细