未登录

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

开通VIP
VLOOKUP函数配合数组公式进阶应用
 本帖最后由 天远 于 2013-4-28 19:32 编辑

首先来认识一下数组公式:
数组公式可以对一组或多组值执行多重计算,并返回一个或多个结果。按 Ctrl+Shift+Enter 可以键入数组公式,此时通过编辑框可以看到数组公式的两端被自动加上了大括号{}。
(以上引用自WPS的帮助文档)

示例1

如上表,可以利用vlookup函数方便地查询某人的年收入。
对B14单元格,在C14单元格中输入:
  1. =VLOOKUP(B14,B3:O11,14,FALSE)
复制代码
下面,再提供两种方法:
1.本例中我们只用到了姓名列和年收入列,那么,查找区域是不是可以缩小一下,只要B列和O列呢,当然是可以的。
我们知道,","(逗号是合并单元格区域的运算符),于是我们就想,对B15单元格,在C15单元格中输入:
  1. =VLOOKUP(B15,(B3:B11,O3:O11),2,FALSE)
复制代码
一试,发现出错了,显然,这样直接合并的方法不对,这样,就想到了用数组公式,我们想让其将两个区域都返回一次,可以用IF来完成:
  1. =IF({TRUE,FALSE},B3:B11,O3:O11)
复制代码
这样,对B15单元格,在C15单元格中输入完整公式 : (注意这是数组公式,输入后按Ctrl+Shift+Enter )
  1. =VLOOKUP(B15,IF({TRUE,FALSE},B3:B11,O3:O11),2,FALSE)
复制代码
2.要是原表中没有年收入怎么办呢,没关系,不是有每个月的数据吗,相加就好了,那么,怎么一步完成:
对B16单元格,在C16单元格中输入公式 : (注意这是数组公式,输入后按 Ctrl+Shift+Enter )
  1. =SUM(VLOOKUP(B16,B3:N11,ROW(2:13),FALSE))
复制代码
ROW(2:13)返回一个垂直数组(注意这里不能用COLUMN(C:N),因为它返回的是水平数组,这里我们想要的是垂直数组)
由此,我们可以利用VLOOKUP函数分别查找出某人每个月的收入,最后再这些数据用SUM求和,就是年收入。

示例2

根据姓名和地址查找业绩,我们首先想到的是构造一个辅助列:
在C和D列中插入一列,再用 &再B、C两列数据连接,再用VLOOKUP查找。
那么,有没有更直接的办法呢,有:
我们可以把查找值看成两列连接值,把查找区域看成B、C列的连接值的整体与D列合并。因示例1,我们借用IF数组公式可以合并得到查找区域:
  1. =IF({TRUE,FALSE},(B3:B11&C3:C11),(D3:D11))
复制代码
这样我们再用VLOOKUP查询,在D15中输入公式: (注意这是数组公式,输入后按 Ctrl+Shift+Enter )
  1. =VLOOKUP(B15&C15,IF({TRUE,FALSE},(B3:B11&C3:C11),(D3:D11)),2,FALSE)
复制代码
这里,B15&C15是查找值,IF({TRUE,FALSE},(B3:B11&C3:C11),(D3:D11)) 是查找区域,列序数是2,匹配模式是FASLE(精确匹配)
对于列序数是2 而不是3,解释:
B3:B11&C3:C11 整体是第一列,D3:D11 是第二列

总结,VLOOKUP函数是一个非常强大的查找函数,但它也有一些不足,比如只能返回一个查找结果,查找值也只能指定一个,但是,我们可以利用数组公式将这"一个"扩展成"一个数组",那就相当于是多个了,这样,VLOOKUP函数的应用范围将大大扩展!

测试文档:
VLOOKUP函数配合数组公式进阶应用.et(12.5 KB, 下载次数: 40)


补充1
=I F  ({TRUE,FALSE},(B3:B11&C3:C11),(D3:D11))中,第一参数{TRUE,FALSE}用大括号,表示是一个数组,在内容,相当于各执行一次
=I F  (TRUE,(B3:B11&C3:C11),(D3:D11))
=I F  (FALSE,(B3:B11&C3:C11),(D3:D11))
分别返回一个区域,两个区域就组成了一个区域数组
补充2:
请看5楼,水月团长给出的示例3

——————修订记录——————

2013年04月28日上传附件
VLOOKUP函数综合应用.et(21 KB, 下载次数: 14)

1.示例1增加INDEX+MATCH函数使用示例
2.收录@松风水月 增加的示例3
3.根据@无魂帖子增加示例4
4.http://bbs.wps.cn/thread-22368733-1-1.html 这个帖子的内容和示例2,3,4相同


本站仅提供存储服务,所有内容均由用户发布,如发现有害或侵权内容,请点击举报
打开APP阅读全文并永久保存 更多类似文章
猜你喜欢
类似文章
使用VLOOKUP函数返回查找到的多个值的方法
Match函数 | 完美Excel
EXCEL中常见函数应用与条件功能
Excel函数公式:不一样的函数学习方法,一看就懂
应用技巧十:特殊的查找函数
LOOKUP函数
更多类似文章 >>
生活服务
绑定账号成功
后续可登录账号畅享VIP特权!
如果VIP功能使用有故障,
可点击这里联系客服!