lookup函数的使⽤⽅法,含向量和数组形式实例及与vlookup的区别在 Excel 中,lookup函数有两种形式,⼀种为向量形式,另⼀种为数组形式;其中向量形有
三个参数,数组形式有两个参数,即数组形式省略了返回结果域。⽆论是向量形式还是数组形
式,查区域必须按升序排序,否则可能返回错误的结果;另外,当不到值时,它们都返回
⼩于或等于查值的最⼤值。以下是就 Excel lookup函数的使⽤⽅法,列举了向量形式和数组
形式两种实例,并且分享了 lookup 与 vlookup 的区别,实例中操作所⽤版本均为 Excel 2016。
⼀、lookup函数向量形式使⽤⽅法
lookup函数向量形式是在⼀⾏或⼀列中查值,返回另⼀⾏或另⼀列对应位置的值。
(⼀)lookup函数向量形式的语法规则
表达式为:=LOOKUP(lookup_value, lookup_vector, [result_vector])
中⽂表达为:=LOOKUP(查值,查区域,返回结果区域)
(⼆)表达式说明
1、lookup_value 为查值,是必选项;可以是对单元格的引⽤、数字、⽂本、名称或逻辑值。
2、lookup_vector 为查区域,是必选项;只能是⼀⾏或⼀列;查区域的值必须按升序排
列,否则可能返回错误的结果;可以是对单元格引⽤、数字、⽂本、名称或逻辑值,⽂本不区
分⼤⼩写。
3、[result_vector] 为返回结果区域,是可选项(即可填可不填);只能是⼀⾏或⼀列,且与查
区域⼤⼩要相同;如果返回结果区域为⼀个单元格(如A2 或A2:A2),则默认为⾏(即横
向),相当于 A2:B2。
注意:
A、如果不到查值,lookup函数会返回⼩于或等于查值的最⼤值。
B、如果查值⼩于查区域的最⼩值,lookup函数会返回 #N/A 错误。
(三)lookup函数的使⽤⽅法举例与查原理说明
实例⼀:从服装销量表中查价格为39元的服装
excel数组函数的实例1、框选E2:E10,选择“数据”选项卡,单击“升序”排序图标,弹出“排序提醒”窗⼝,选择“扩展选
定区域”,单击“确定”,把“价格”列按升序排列;把公式=LOOKUP(A13,E2:E10,B2:B10) 复制到
B13 单元格,按回车,则返回“⽩⾊T恤”,操作过程步骤,如图1所⽰:
图1
2、公式说明
公式中 A13 为查值,E2:E10 为查区域,B2:B10 为返回结果区域,查区域与返回结果区
域都为列,并且查区域按升序排列。
3、查原理说明
A、lookup函数⽤折半查(即⼆分查)来搜索要的值。折半查的算法为:每次⽤查区
域的数值的个数除以2,取得中间值的下标,根据下标取出中间值与要查值⽐较;如果查值
等于中间值,则到返回;如果查值⼩于中间值,则在中间值前⾯继续折半查;如果查
值⼤于中间值,则在中间值后⾯继续折半查;⼀直到完所有数值。
B、如果查区域的数值是奇数个,则折半后恰是中间哪个值;如果查区域的数值是偶数个,
折半后有两个中间值,则取左边(或上边)哪个与查值⽐较。
C、由于查区域的值按升序排列,因此,每次折半总能把查值分到它可能在的⼀边;如果在没有按升序排序的区域中查,则⽆法确保把查值分到它可能在的⼀边,从⽽导致到错误的结果。
D、如果查过程中遇到错误值或空,则会忽略继续折半查。若不到查值,则会返回⼩于或等于查值的最⼤值。
E、实例中“价格列”共有9个值,即35、35、36、38、39、49、85、86、98,第⼀次折半后恰好是要查的值 39,因此第⼀次就到。如果要 86,第⼀次也到 39;由于 86 ⼤于 39,因此在后半段 39、49、85、86、98 中继续折半查,第⼆次到85;由于 86 ⼤于 85,因此第三次在 85、86、98 中继续折半查,折半后恰好到 86。
实例⼆:查近似值(查区域中没有的值)
1、假如要查价格为 50 元的服装。把公式 =LOOKUP(A13,E2:E10,B2:B10) 复制到 B13 单元格,如图2所⽰:
图2
2、按回车,则返回“⿊⾊T恤”,如图3所⽰:
图3
3、服装销量表中并没有价格为50 元的服装,返回的“⿊⾊T恤”价格为49 元,这正验证了如果不到查值,返回⼩于或等于查值(50)的最⼤值。
实例三:查⽐查区域中最⼩还⼩的值
1、假如要查价格为24 元的服装。同样把公式=LOOKUP(A13,E2:E10,B2:B10) 复制到B13单元格,如图4所⽰:
2、按回车,则返回 #N/A 错误,如图5所⽰:
图5
3、服装销量表中价格最低的为 35 元,⽽现在要价格为 24 元的服装,24 ⼩于 35,因此返回#N/A 错语;这验证了查值⼩于查区域的最⼩值时将返回 #N/A 错误。
⼆、lookup函数数组形式使⽤⽅法
lookup函数的数组形式是在数组的第⼀⾏或第⼀列查指定值,并返回数组最后⼀⾏或最后⼀列中对应位置的值。
(⼀)lookup函数数组形式的语法规则
表达式为:=LOOKUP(lookup_value, array)
中⽂表达为:=LOOKUP(查值,数组)
(⼆)表达式说明
1、lookup_value 为在数组中的查值,是必选项;可以是对单元格的引⽤、数字、⽂本、名称或逻辑值。
2、array 为数组,是必选项;它是⾏和列中值的集合;数组的值必须按升序排列,否则会返回错误的结果;可以是对单元格的引⽤、数字、⽂本、名称或逻辑值,⽂本不区分⼤⼩写。
3、注意
A、如果不到查值,与向量形式⼀样会返回⼩于或等于查值的最⼤值。
B、如果查值⼩于第⼀⾏或第⼀列的最⼩值,lookup函数会返回 #N/A 错误。
C、如果数组的列数⼤于⾏数,则lookup函数会在第⼀⾏中查要的值。如果数组的⾏数⼤于列数,则lookup函数会在第⼀列中查要的值。
D、lookup函数总是返回⾏或列中最后⼀个值。
(三)向量形式lookup函数的使⽤⽅法举例
实例⼀:
1、假如要在服装销量表的A2:E10 这⽚区域查编号为WS-581 的服装。框选A2:A10,选择“数据”选项卡,单击“升序”图标,则把所有服装按升序排列;在 A13 单元格中输⼊WS-581,继续在B13 单元格输⼊公式= LOOKUP(A13,A2:E10),按回车,则返回38,这正是编号为WS-581 服装的价格;操作过程步骤,如图6所⽰:
图6
2、公式说明
公式中 A13 为查值,A2:E10 为数组,共由五列组成,查前必须对它们按升序排序,否则可能返回错误的结果。
实例⼆:近似查
1、假如要在服装销量表中查编号为S-39 的服装。在A13 单元格输⼊编号S-39,然后在B13 单元格输⼊公式=LOOKUP(A13,A2:E10),按回车,则返回98;操作过程步骤,如图7所⽰:
图7
提⽰:查前同样需要对编号进⾏升序排序,由于前⾯已经排好序,因此这⾥省略了排序操作。
2、为什么会返回98?从26个字母排序可知,S 在 N 与 W 之间,当不到要的编号时,返回⼩于或等于要值的最⼤值,⽽⼩于编号S-39 的编号共有三个,分别为NS-281、NS-286 和NS-832,⽽ NS-832 最⼤,⼜根据 lookup函数总是返回⾏或列中最后⼀个值,因此返回 98。
三、lookup函数与vlookup函数的区别
lookup函数相当于vlookup函数的近似匹配;lookup主要⽤于查⼀⾏或⼀列,vlookup即可⽤于查⼀⾏⼀列,也可⽤于查多⾏多列,功能⽐ lookup 强⼤得多。
版权声明:本站内容均来自互联网,仅供演示用,请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198,我们将在24小时内删除。
发表评论