未登录

开通VIP,畅享免费电子书等14项超值服

开通VIP
比vlookup更强大的函数lookup函数(下:数组用法)

上次我们说了lookup的向量用法,今天说下它的数组用法。

-01-

函数说明

lookup函数的数组结构如下图第2种写法,有2个参数。lookup的数组用法是在数组的第一行或第一列中查找指定的值,并返回数组最后一行或最后一列中同一位置的值。

lookup_value:必须有。在数组中查找的值,可是是数字,文本,逻辑值,定义名称和单元格引用。

array:必须有。包含要与lookup_value进行比较的数字,文本和逻辑值的单元格区域。

与向量用法一样,数组用法,也有以下要求:

  1. 数组中的值必须按升序排列,否则lookup可能返回不正确的值。

  2. 如果lookup找不到lookup_value的值,它会使用数组中小于或等于 lookup_value的最大值。(模糊查找)

  3. 如果lookup_value的值小于第一行或第一列中的最小值(取决于数组维度),lookup会返回 #N/A 错误值。

-02-

示例解释

如下图所示,左表是一个姓名成绩源表,右表想根据姓名查出数学的成绩。在F列中先用vlookup精确查找,为了和G列中的lookup做对比。

F2=VLOOKUP(E2,A$2:C$9,3,),G2=LOOKUP(E2,A$2:C$9)。发现G列中很多和F列中的都对不上。这是什么原因呢?是因为lookup函数中的查找区域没有按升序排列。

对A列中的姓名排序,在A列数据区域中任选一个单元格,点击升序,发现lookup的值正确了,看下面第2张图。说明lookup要求查找区域为升序排列,而且在G2=LOOKUP(E2,A$2:C$9)这个公式中,查找的区域为A2:C9,一共3列,返回最后一列的值。此时按第一列查找,返回最后一列对应的值。

那你会问什么时候按行查询,什么时候按列查询呢?这个要看查询区域的行数和列数。如果列数大于行数,则按第一行查询;如果列数小于等于行数,则按第一列查询。下面举例说明。

在G2单元格中输入如下公式,结果为小包。查询区域,列数大于行数,按第一行查找,找到刘卓,返回最后一行对应的小包。

在G2单元格中输入如下公式,结果为58。查询区域,列数小于行数,按第一列查找,找到刘卓,返回最后一列对应的58。

在G2单元格中输入如下公式,结果为73。查询区域,列数等于行数,按第一列查找,找到刘卓,返回最后一列对应的73。

-03-

具体应用

1.求出下图左表中所有客服和发货员的总工资

AB两列为姓名和岗位表,F1:G5为相应岗位的工资表。

通常的做法是,添加辅助列,根据岗位查询出每个人的工资,然后用sumif条件求和。C2的公式为=LOOKUP(B2,F$2:G$5),要确保查询区域为升序排列,否则返回不正确的值。然后在D1单元格中输入公式=SUM(SUMIF(B:B,F9:G9,C:C)),三键结束求出所有客服和发货员的总工资。

如果要求不用辅助列,只要一条公式就算出,又该怎么做呢?这就用到lookup这个函数。在F10单元格中输入公式=SUM(IFERROR(LOOKUP(IF(B2:B23=F9:G9,F9:G9),F2:G5),))lookup这个函数第一参数支持数组,这是它的优点,而vlookup第一参数不支持数组,还要用其他的方法才能实现。

解释一下这个公式,我认为这个公式的难点在于if函数,它是个数组形式,而且是个2维数组。B2:B23=F9:G9这个是不同维度的一维数组的比较,会形成一个二维数组。如下图I和J列。

IF(B2:B23=F9:G9,F9:G9),F2:G5)的结果如下图I和J列所示,意思是在B列岗位列中等于客服或者发货员的还是返回客服和发货员,不等于的返回false。这样再用lookup查询这个二维区域,就查找出所有客服和发货员的工资了,中间会有错误值,所以用了iferror,最后求和。

我觉得上面那个if形成的二维区域不好理解,所以用下面这个公式,F11=SUM(IFERROR(LOOKUP(IF((B2:B23=F9)+(B2:B23=G9),B2:B23),F2:G5),))。效果是一样的。IF((B2:B23=F9)+(B2:B23=G9),B2:B23)的意思是B列岗位列中是客服或发货员的,返回它本身,不是的返回false,这样的话是个一维数组。然后用lookup查找出来的就是所有客服和发货员的总工资,最后排除错误值,求和。

其实还有一种更好理解的方法,F13=SUMPRODUCT(LOOKUP(F9:G9,F2:G5),COUNTIF(B:B,F9:G9))。解释一下,LOOKUP(F9:G9,F2:G5)这个为查出客服和发货员的工资,形成一个数组;COUNTIF(B:B,F9:G9)这个为查出客服和发货员的个数,也形成一个数组,然后用sumproduct乘积求和。

好了,lookup的数组用法,你都学会了吗?

本站仅提供存储服务,所有内容均由用户发布,如发现有害或侵权内容,请点击举报
打开APP阅读全文并永久保存 更多类似文章
猜你喜欢
类似文章
VLOOKUP函数家族大解密
查询函数Choose、Lookup、Hlookup、Vlookup应用技巧解读
6个查询函数应用技巧合集,简单易学,收藏备用!
Lookup函数的几种用法
Vlookup函数实例(全)
Excel中row函数的使用教程步骤图(2)
更多类似文章 >>
生活服务
绑定账号成功
后续可登录账号畅享VIP特权!
如果VIP功能使用有故障,
可点击这里联系客服!