18岁白手起家创业,26岁身价3000万SUMIF函数五个不同求和技巧,个个都很实用Excel舍入和取整函数,玩点高层次的行不行?

lookup函数的使用方法,含向量和数组形式实例及与vlookup的区别

哪位大神能解释表格函数IF的使用方法?
2015年移动互联网创业,你该牢记这八大忠告!
2015年微信公众号媒体价值研究报告
在 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元的服装

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 强大得多。

责任编辑:18岁白手起家创业,26岁身价3000万