你担心的查找函数原来是这么回事。

十年前,Excel函数方兴未艾,大家还在热情探索函数的世界。LOOKUP是当时公认的超级函数之一。超级有两层意思。一方面,LOOKUP非常强大。在数据查询的问题上,基本上没有什么是它解决不了的;另一方面,这个家伙非常复杂,甚至有人根据它的语法猜测这个函数使用了经典编程算法的二分法(但事实证明这个猜想在逻辑上完全不符合,有兴趣的话我们以后再说)。

-这是十年前。现在十年过去了,连复杂的功能都总结成了简单的套路。

LOOKUP function有两种官方语法——但它们都没有太大的实用价值。这个功能已经发挥到官方定义的语法成为废纸,民间套路成为事实语法的地步;所以看一下官方语法还是不错的。

1语法向量形式▼

=LOOKUP(查找值,查找向量,结果向量)

2语法数组形式▼

=查找(lookup_value,array)

......自古深情留不住,套路总流行。网上流传的查找套路很多,但看透表象后,核心规则只有两个:区间查询和条件查询。

另外,虽然是老生常谈,但还是要说,如果你是函数新手,这一章有一些部分你是看不懂的,你是真的想看懂。请重读函数基础,即函数系列教程的第二、三、七章,尤其是第七章:为什么说0和1是Excel函数逻辑运算的核心要领?

一个

区间查询

查找函数区间查询例程的语法格式如下:

=LOOKUP(查找值,按第一列升序排列的查找区域,结果区域)

应该注意的是,当使用这个例程时,搜索范围的第一列必须按升序排列。

比如说。

如下图所示,A:C数据区的分数需要按照F:G评分标准进行评分。大于等于0小于60为及格,大于等于60小于80为及格,大于等于80小于90为优秀,大于等于90小于95为优秀...也就是说,查询区域被分成多个部分,并按升序排列。

你所烦恼的LOOKUP函数,原来是这么回事

C2单元格,输入以下公式,复制并填写下来:

=查找(B2,F:F,G:G)

F列是升序排列的搜索区域,G列是其对应的结果区域。LOOKUP从搜索区域中查找小于或等于搜索值的最大值,例如81。查询区域(F列)中小于该值的最大值是80,所以先得到结果80,然后返回80对应的G列结果& # 8221;优秀& # 8221;。

…可能需要再次强调,这种区间查询方式要求搜索区域的第一列必须升级排列!有朋友可能会说,F列不是升序的。0高于F1细胞& # 8221;得分& # 8221;小。没事的。LOOKUP很聪明。你不说他也知道F1是不是题线。

这个例子也可以使用下面的公式:

=查找(B2,女:男)

你所烦恼的LOOKUP函数,原来是这么回事

这是因为当搜索区域有多列且结果区域被省略时,LOOKUP会将搜索区域的第一列(本例中的F列)默认为搜索区域,将最后一列(本例中的G列)默认为结果区域。

注意,我们说的是最后一列,不是第二列。

例如,在单元格D2中输入以下公式,以返回h列的评级

=查找(B2,F:H)

默认情况下,LOOKUP的第一列F是搜索区域,最后一列H是结果区域。

你所烦恼的LOOKUP函数,原来是这么回事

看个广告放松一下。

毕竟,没有广告的推文是不真诚的..▼

2

条件查询

查找函数条件查询例程的语法格式如下:

=LOOKUP(大于查找范围、查找区域、结果区域中所有相同类型值的值)

该例程返回与搜索区域中相同类型的最后一个数据相对应的结果。

例如,查找A列中的最后一个文本:

= LOOKUP(& # 8220;Seat & # 8221,答:答,答:答)

“Seat & # 8221是文本数据中的最大值,大于大多数常见的文本值,因此该公式返回A列中的最后一个文本值..

由于此公式的搜索区域和结果区域相同,因此也可以写成以下形式:

= LOOKUP(& # 8220;Seat & # 8221,答:答)

当省略结果区域时,LOOKUP的默认查找区域是结果区域。

例如,查找A列中的最后一个值:

=LOOKUP(9^9,A:A)

9 9 9是9的9次方,这是一个很大的值,大于大多数值,因此该公式返回a列中的最后一个值。

你所烦恼的LOOKUP函数,原来是这么回事

……

我觉得这个套路没什么实用价值。

打响指,然后让我们以另一种形式扩展这个例程...

如下图所示,您需要根据A: B列的数据来源,查询单元格D2中指定名称的考试成绩

你所烦恼的LOOKUP函数,原来是这么回事

这是一个单条件查询问题,查找公式如下:

=LOOKUP(1,0/(A1:A10=D2),B1:B10)

Part (A1:A10=D2)判断A1:A10区的值是否等于D2,返回一个由逻辑值真和假组成的内存数组:

{假;真实;假的;假的;假的;假的;假的;假的;假的;FALSE}

用0除数组,0/TRUE得出0,0/FALSE得出错误值#DIV/0!,它返回由0和错误值组成的内存数组:

{#DIV/0!;0;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!}

LOOKUP有一种天然的忽略错误值的能力,查找值1大于查找范围内的全零,所以公式可以直接返回最后一个合格值对应的结果。

总结一下这个公式,它就成了一个经典的查找单条件查询例程:

=LOOKUP(1,0/(条件区域1=条件值),结果区域)

……

再延伸这个套路,你就可以变成& # 8221;还有& # 8221;关系的多条件查询的经典例程(注意括号的数量和位置):

看不全代码可以左右拖动& # 8230;▼

=LOOKUP(1,0/(条件区域1=条件值1)*(条件区域2=条件值2)*……(条件区域n=条件值n)),结果区域)

比如说。

如下图所示,需要根据A:C数据源计算同时满足E列期间和F列名称的数据。

你所烦恼的LOOKUP函数,原来是这么回事

公式G2如下:

=LOOKUP(1,0/((A$1:A$10 = E2)*(B$1:B$10 = F2)),C$1:C$10)

在(A$1:A$10=E2)*(B$1:B$10=F2)段中,利用乘法运算判断两个条件是否同时为真,返回由逻辑值TRUE和FALSE组成的内存数组。

然后将数组除以0,得到一个由0和错误值组成的单列内存数组。

1作为查找的查找值大于查找范围内的所有值,因此该公式直接返回最后一个符合条件的查询结果。

另外,与INDEX+MATCH函数不同的是,由于LOOKUP天生支持数组运算,因此虽然公式进行了多次运算(一次计算多个值),但不需要使用数组三键来结束公式的输入——这给公式的编写和维护带来了极大的便利。

……

继续延伸,把上面套路的乘法改成加法,就可以变成& # 8221;或者& # 8221;关系的多条件查询例程:

看不全代码可以左右拖动& # 8230;▼

=LOOKUP(1,0/((条件区域1=条件值1)+(条件区域2=条件值2)+…(条件区域n=条件值n)),result)

举个同样的例子。

如下图,需要根据A列的数据来源:c查询E列的名称符合A列的昵称或者B列的全名的结果。

你所烦恼的LOOKUP函数,原来是这么回事

公式F2如下:

看不全代码可以左右拖动& # 8230;▼

=LOOKUP(1,0/((A$1:A$10 = E2)+(B$1:B$10 = E2)),C$1:C$10)

Part (A$1:A$10=E2)+(B$1:B$10=E2),利用加法的形式,判断两个条件中是否至少有一个为真,返回一个由逻辑值TRUE和FALSE组成的内存数组,其余的计算步骤回到原轨道。

……

尽管如此,使用FIND函数,它可以成为模糊匹配的查询例程:

=LOOKUP(1,0/FIND(条件区域,条件值),结果区域)

还是举个例子:

如下图,A列是缩写,B列是成就。你需要根据d列的全称找到对应的成绩。

你所烦恼的LOOKUP函数,原来是这么回事

公式如下:

=LOOKUP(1,0/FIND(A$1:A$8,D2),B$1:B$8)

在查找(A$1:A$8,D2)部分,判断A1:A8的值是否存在于D2单元格中。如果是,它返回数字序列号,如果不是,它返回错误值。例如,此示例返回内存数组:

{ #值!;3;#值!;#值!;#值!;#值!;#值!;#值!}

用这个数组除0,生成一个由0和误差值组成的内存数组…下面的计算步骤回到原来的轨道。

放开思维继续延伸,就能得到& # 8221;还有& # 8221;基于关系的多条件模糊匹配例程:

=LOOKUP(1,0/(FIND(条件区域1,条件值1)*FIND(条件区域2,条件值2)),结果区域)

如果改成加法,就变成了OR关系的多条件模糊匹配例程:

=LOOKUP(1,0/(查找(条件区域1,条件值1)+查找(条件区域2,条件值2)),结果区域)

……

结束语

分享这么多栗子,其实只是LOOKUP在一个相对规则的单元格区域的查询用法,并不涉及数据结构转换和字符串处理。也就是说,这一切只是LOOKUP强大用法的冰山一角...

所以,当INDEX+MATCH说他们比VLOOKUP强一百倍的时候,VLOOKUP的大哥LOOKUP只会冷笑着撸起袖子,心想,呵呵,我比你的组合强一百倍以上...

当然,强大不代表实用。

就好像大炮威力巨大,用来打蚊子就是个笑话。INDEX+MATCH比VLOOKUP强,但很多情况下并不比VLOOKUP好写。LOOKUP比INDEX+MATCH强,但是很多时候计算效率比较低。

三者之间各有什么优缺点?我们将在下一章讨论。