linux类似的vlookup函数,VLOOKUP进阶——你可能所不知道的VLOOKUP用法

本帖最后由 看见星光 于 2014-9-9 12:54 编辑

第三节：VLOOKUP条件求和以及T/N+IF{1}技巧建立内存数组的一个应用小例。

这一节，我们来回答开篇所提到的第二个以及第三个问题：

VLOOKUP能否进行条件求和？就像SUMIF那样？

VLOOKUP第一个参数能否支持数组引用？

如下图，有这样一道题，需要在E1，求出A列存在的D3:D6班级的成绩之和。

5.jpg (34.4 KB, 下载次数: 3649)

2014-8-19 11:31 上传

解这道题的方法有很多种，我们通常使用SUMIF：

数组：=SUM(SUMIF(A1:B5,D4:D6,B1))

或者：

数组：=SUM((A2:A5=TRANSPOSE(D4:D6))*B2:B5)

如果用VLOOKUP，又怎么做呢？

我们可以写成这样：

E1=SUM(VLOOKUP(T(IF({1},D4:D6)),A:B,2,0))

这个公式不需要按数组三键。

我们来简单了解下这个公式。

重点是VLOOKUP的查找值，T(IF({1},D4:D6))。

我们知道D4:D6，是需要进行查找统计的班级名称，那么为什么要在其外套T和IF函数？或者，我们反过来想，为什么不套T和IF函数，VLOOKUP就只对查询范围的第一个数值(金庸班)进行查询呢？

我们可以这么简单的理解。

T/N+IF组合，是让VLOOKUP函数的第一参数，接受数组形式，因此返回相应的内存数组。

如此，VLOOKUP方能对每一个查找值进行查询统计。

如果为了避免错误值的问题，比如D4:D6出现了查询范围不曾出现的班级名称：天仙班，公式可以修改为：

数组：=SUM(IFERROR(VLOOKUP(T(IF({1},D4:D6)),A:B,2,0),0))

=========我是温暖恰春的分割线=========

6.jpg (33.16 KB, 下载次数: 3660)

2014-8-19 11:32 上传

再看一道题。

如上图，对A列存在的D4:D6的班级进行求和，班级重复的只计算一次，答案是305。

我们通常使用这样的数组公式：

=SUM(SUMIFS(B:B,A:A,D4:D6)/COUNTIF(A:A,D4:D6))

或者：

=SUM(N(INDIRECT("b"&MATCH(D4:D6,A1:A9,))))

其实我们也可以使用VLOOKUP：

=SUM(VLOOKUP(T(IF({1},D4:D6)),A:B,2,0))

依然由于VLOOKUP天生就只取首个匹配的结果的缘故，所以咱们也就不需要对重复数据进行二次处理。

如果要屏蔽错误值，依然要增加IFERROR：

数组=SUM(IFERROR(VLOOKUP(T(IF({1},D4:D6)),A:B,2,0),0))

=========我是恰春温暖的分割线=========

综合以上两个问题，咱们不难发现，在条件求和方面，VLOOKUP和SUMIF还是有所不同的。

如果未加以处理，VLOOKUP只对第一次出现的数据进行计算，这是它的短处，当然，未必不是它的长处。

如果未加以处理，SUMIF会对所有数据进行求和，不论重复与否，这是它的长处，当然，未必不是它的短处。

=========我只是分割线=========

T/N+IF{1}技巧建立内存数组的一个应用小例

QQ图片20140821223202.jpg (38.68 KB, 下载次数: 3492)

2014-8-21 22:30 上传

如图，判定D列姓名的相对累计重复次数(中文名和英文名如果是同一个人的名字，则同样视为重复)

这道题如果用辅助列，会很简单。

直接把名字统一转换为中文或者英文，再使用COUNTIF进行重复次数计算。

比如E2公式：=IFERROR(VLOOKUP(D2,A:B,2,0),D2)，下拉后将名字统一更换为中文；再使用公式=COUNTIF($E$2:E2,E2),下拉后便可得出正确结果。

但如果不用辅助列呢？

如果我们继续之前的解题思路，将查询的名字，统一更换为中文或者英文，再进行重复次数的计算，我们依然可以使用VLOOKUP函数。

比如公式：F2=SUM(N(IFERROR(VLOOKUP(T(IF({1},$D$2:D2)),A:B,2,0),$D$2:D2)=IF(CODE(D2)<91,VLOOKUP(D2,A:B,2,),D2)))

IFERROR(VLOOKUP(T(IF({1},$D$2:D2)),A:B,2,0),$D$2:D2)，是在公式下拉的过程中，将第二行到公式所在行的D列姓名，统一转换为中文，并以可以计算的内存数组的形式保存相关值。

IF(CODE(D2)<91,VLOOKUP(D2,A:B,2,),D2),是将D列需要判定重复次数的值，统一转化为中文。

SUM(N……)是统计第一个公式的内存数组值等于第二个公式返回值的次数，即相关名字的重复次数。

...

..

.