VLOOKUP函数配合数组公式进阶应用

良好开端 2015-10-12

展开全文

本帖最后由天远于 2013-4-28 19:32 编辑

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

示例1：
VLOOKUP函数配合数组公式进阶应用_16115473

如上表，可以利用vlookup函数方便地查询某人的年收入。
对B14单元格，在C14单元格中输入:

=VLOOKUP(B14,B3:O11,14,FALSE)

复制代码

下面，再提供两种方法：
1.本例中我们只用到了姓名列和年收入列，那么，查找区域是不是可以缩小一下，只要B列和O列呢，当然是可以的。
我们知道，","(逗号是合并单元格区域的运算符)，于是我们就想,对B15单元格，在C15单元格中输入:

=VLOOKUP(B15,(B3:B11,O3:O11),2,FALSE)

复制代码

一试，发现出错了，显然，这样直接合并的方法不对，这样，就想到了用数组公式，我们想让其将两个区域都返回一次，可以用IF来完成：

=IF({TRUE,FALSE},B3:B11,O3:O11)

复制代码

这样，对B15单元格，在C15单元格中输入完整公式 : (注意这是数组公式，输入后按Ctrl+Shift+Enter )

=VLOOKUP(B15,IF({TRUE,FALSE},B3:B11,O3:O11),2,FALSE)

复制代码

2.要是原表中没有年收入怎么办呢，没关系，不是有每个月的数据吗，相加就好了，那么，怎么一步完成：
对B16单元格，在C16单元格中输入公式 : (注意这是数组公式，输入后按 Ctrl+Shift+Enter )

=SUM(VLOOKUP(B16,B3:N11,ROW(2:13),FALSE))

复制代码

ROW(2:13)返回一个垂直数组（注意这里不能用COLUMN(C:N)，因为它返回的是水平数组，这里我们想要的是垂直数组）
由此，我们可以利用VLOOKUP函数分别查找出某人每个月的收入，最后再这些数据用SUM求和，就是年收入。

示例2：
VLOOKUP函数配合数组公式进阶应用_16115474

根据姓名和地址查找业绩，我们首先想到的是构造一个辅助列:
在C和D列中插入一列，再用 &再B、C两列数据连接，再用VLOOKUP查找。
那么，有没有更直接的办法呢，有：
我们可以把查找值看成两列连接值，把查找区域看成B、C列的连接值的整体与D列合并。因示例1,我们借用IF数组公式可以合并得到查找区域:

=IF({TRUE,FALSE},(B3:B11&C3:C11),(D3:D11))

复制代码

这样我们再用VLOOKUP查询，在D15中输入公式: (注意这是数组公式，输入后按 Ctrl+Shift+Enter )

=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相同