Excel:LOOKUP函数的经典用法

1、逆向查询

下面这个表中,A:C列是员工基础信息表,分别是部门、姓名和职务。

Excel:LOOKUP函数的经典用法

​现在要根据E5单元格中的员工姓名,在这个信息表中查询属于哪个部门,也就是咱们常说的逆向查询,就可以使用LOOKUP函数了。

F5单元格输入以下公式:

=LOOKUP(1,0/(B2:B10=E5),A2:A10)

得出的结果是“生产部”。

我靠,可怜的天竺僧还上三班倒呢 ^_^

上面这个公式就是LOOKUP函数最典型用法。

可以归纳为:

=LOOKUP(1,0/(条件),目标区域或数组)

其中,条件可以是多个逻辑判断相乘组成的多条件数组。

=LOOKUP(1,0/((条件1)*( 条件2)* ( 条件N)),目标区域或数组)

以0/(B2:B10=E5)构建一个0、#DIV/0!组成的数组,再用永远大于第2个参数中所有数值的1作为查找值,即可查找最后一个满足非空单元格条件的记录。


2、查询A列中的最后一个文本

这个题目也非LOOKUP函数莫属,用到的公式是:

=LOOKUP("々",A:A )

"々"通常被看做是一个编码较大的字符,它的输入方法为<Alt 41385>组合键。

如果感觉每次写这个符号有点费事儿,也可以写成:

=LOOKUP("座",A:A )

一般情况下,第一参数写成“座”也可以返回一列或一行中的最后一个文本。


​3、查询A列中的最后一个数值

用到的公式是:

=LOOKUP(9E307,A:A)

9E307被认为是接近Excel规范与限制允许键入最大数值的数,用它做查询值,可以返回一列或一行中的最后一个数值。

有朋友会说了,如果我A列中的数据既有文本也有数值,想得到最后一个单元格内容,那咋办?当然不能凉拌!

哈哈^_^ ,写成这样就可以的:

=LOOKUP(1,0/(A:A<>""),A:A)

注意,上面这个公式中整列引用的写法在03版本中不适用,可以写成实际的单元格区域引用。


​4、根据简称查询全称

这个问题相信大家都会经常遇到吧?

如下面这个图中所示,A列是客户的简称,要求根据E列的客户全称对照表,在C列写出客户的全称。

Excel:LOOKUP函数的经典用法

​C2单元格输入以下公式,可得到“上海沛发”的客户全称“上海沛发包装材料有限公司”。

=IFERROR(LOOKUP(1,0/FIND(A2,E$2:E$13),E$2:E$13),"")

公式中“0/FIND(A2,E$2:E$13)”部分,首先用FIND函数查询A2单元格“上海沛发”在E$2:E$13的起始位置,得到一个由错误值和数值组成的数组。

余下部分的计算过程就和咱们前面说过的一样了,使用IFERROR函数来屏蔽公式查询不到对应结果时返回的错误值。


​5、多个区间的条件判断

话说某公司组织员工技能考核,根据不同的分值,给出相应的评语。

50分以下的为“很差”

50-59分的为“差”

60-74分的为“一般”

75-85分的为“较好”

86-95分的为“优秀”

96分及以上的为“能手”。

Excel:LOOKUP函数的经典用法

​这种多个区间的判断,如果需要判断的条件和区间都很多,再使用IF函数来计算,估计会把自己都转晕了。

而使用LOOKUP函数来解决,不过是小菜一碟而已。

C2单元格输入以下公式,向下复制即可。

=LOOKUP(B2,{0,50,60,75,86,96;"很差","差","一般","较好","优秀","能手"})

Excel:LOOKUP函数的经典用法

​除此之外,LOOKUP函数还被用于带有合并单元格的汇总计算,以及单元格中数值字段的提取等等,这些内容咱们留到以后慢慢再说。







一、查找最后一条符合条件的记录

公式的模式化写法为:

=LOOKUP(1,0/(条件区域=条件),查询区域)

Excel:LOOKUP函数的经典用法

二、查询符合多个条件的记录

公式的模式化写法为:

=LOOKUP(1,0/((条件区域1=条件1)*(条件区域2=条件2)),查询区域)

Excel:LOOKUP函数的经典用法

三、逆向查询

最后的查询区域可以灵活写成任意一列,从右至左、自下而上查询,统统没问题。

Excel:LOOKUP函数的经典用法

四、查询最后一笔业务日期

公式的模式化写法为:

=LOOKUP(1,0/(条件区域<>""),查询区域)

Excel:LOOKUP函数的经典用法

本例来说,公式可以简化为:

=LOOKUP(1,0/B4:B11,$A4:$A11)

但是只适合条件区域为数值的情况,而失去通用性了。

五、模糊查询考核等级

Excel:LOOKUP函数的经典用法

这种方法可以取代IF函数完成多个区间的判断查询,前提是对照表的首列,必须是升序处理。

六、提取单元格内的数字

A2公式为

=-LOOKUP(1,-LEFT(A2,ROW($1:$99)))

Excel:LOOKUP函数的经典用法

首先用LEFT函数从A2单元格左起第一个字符开始,依次返回长度为ROW($1:$99)也就是1至99的字符串,添加负号后,数值转换为负数,含有文本字符的字符串则变成错误值。

LOOKUP函数使用1作为查询值,在由负数、0和错误值构成的数组中,忽略错误值提取最后一个等于或小于1的数值。

最后再使用负号,将提取出的负数转为正数。

七、带合并单元格的查询

Excel:LOOKUP函数的经典用法

下图中,根据E2单元格的姓名查询A列对应的部门。



上一篇:P2649 - 【NOIP2017】列队


下一篇:Android自定义UI的实现和应用