Excel数组公式的一个运用实例

昨天工作的时候学到了一个新的用法,巩固了一下对数组公式的知识,觉得挺有用处的,在此分享给大家。平常我们使用Excel,用得比较多的是VLOOKUP公式

=VLOOKUP(Lookup Value,Table Range,Column Index,Match Type)

VLOOKUP比较简单,可是有一些局限性。比如作为索引的列必须在引用的目标列的左边(Column Index不能为负),不能支持多条件的查找(当A=2,B=3时,C的值)等。在Excel中,有一个公式可以替代VLOOKUP,并且拥有更加丰富的功能,那就是INDEX MATCH数组公式。下面我们用一个实例来说明如何运用这几个公式,达到灵活查找的效果。

我们现在有如下一个简单的Excel表格,上面有国家,城市和人口和首都四列。

source2

我们需要通过公式求出,这两个国家非首都城市中,人口最多的是哪两个国家。数据是编的,只用于演示公式。因为数据表比较简单,只包括两个国家,数个城市,所以一眼就能看出来答案。但是如果这个表有一千行,一百个国家,就必须求助于数组公式了。

destination

首先理清思路。在这个问题中,涉及到求几个数之间的最大值、筛选统计区域、还有查找相应的值(根据人口查城市或相反)这几个步骤。我们一一来看。

1. 求最大值

求最大值的公式是MAX,不过有时候也可以用LARGE,这里为了简单,我们就用MAX吧。

=MAX(Data Range)

只要给定一个包含数字的区域,Excel就能指出这个区域内最大的那个数字,比如=MAX(2,3,4,5)返回5。所以,要求得某个国家中最大的那个人口数,只需要写成以下这样就行了。

=MAX(某国家非首都的人口数的集合)

2. 查找需要的人口数集合

查找一个或多个数值需要的公式就是INDEX,INDEX的作用是在一个数据区域中返回第X行第Y列的值。

=INDEX(Data Range,Row Number,Column Number)

在我们这个例子中,=INDEX(A1:B11,2,2)返回的是”上海”。如果数据仅包含单行或单列,那么对应的行数和列数可以忽略。如果包含多行或多列而行数或列数为0,就会返回一个数据集合。这里我们需要查找的是人口数,数据区域只有单列,所以公式变成了以下这样。

=MAX(INDEX(某国家非首都的数据区域,0))

注意,这里公式已经变成了数组公式,因为INDEX的第二个参数为0,返回的是一系列单元格。

3. 筛选某国非首都的数据区域

筛选用到的公式是IF,IF的作用是有条件地返回需要的值。

=IF(Condition,Value if True,Value if False)

其中Value if False可以不填,即不返回任何值。首先我们筛选非首都的数据区域。

=IF(D:D="否",C:C)

当D列的值为”否”时,返回C列对应的值给INDEX公式。你会发现,这里的条件并不是让D2=”否”,而是让一个区域等于一个值,这个就是数组公式的运用。这个公式的意思就是在D:D中,如果为”否”(也就是非首都),返回C:C,如果不是就不返回任何东西,那外层的INDEX也就无法作用了。这样就实现了仅包含非首都的意义。

其次我们筛选某一个国家的数据区域。这样就用到了IF的一个小技巧。

=IF(Condition1*Condition2*Condition3...,Value if True,Value if False)

在IF公式中,可以将多个条件相乘,达到多条件筛选的目的。实际上Condition返回的是true (1) 或者false (0)。只有当多个条件都为true (1)时,整个条件才为true (1)。在这里我们写成。

=IF((A:A=H2)*(D:D="否"),C:C)

其中H2就是目标表中的国家名称。

综合上述几个步骤,得出的公式如下。

=MAX(INDEX(IF((A:A=H2)*(D:D="否"),C:C),0))

输入完成后,不要直接按Enter或者Ctrl+Enter,而是按Ctrl+Shift+Enter,告诉Excel这是一个数组公式,否则Excel会返回#N/A!的错误。

接下来就是求出人口对应的城市了。

4. 查找对应的值

查找对应的值所需的公式是INDEX MATCH组合。首先看MATCH。

=MATCH(Lookup Value,Data Range,Match Type)

如果熟悉VLOOKUP,你会发现MATCH公式的参数和VLOOKUP差不多。只不过MATCH返回的是行数,而VLOOKUP直接返回数值。在这个例子中,我们需要将刚才求出的最大值带入公式,在C:C列找到相应的行数,然后通过INDEX求出对应的城市名称。首先写出MATCH。

=MATCH(MAX(INDEX(IF((A:A=H2)*(D:D="否"),C:C),0)),C:C,0)

然后写出INDEX。

=INDEX(B:B,MATCH(MAX(INDEX(IF((A:A=H2)*(D:D="否"),C:C),0)),C:C,0))

不要忘了最后要按Ctrl+Shift+Enter获得结果。最后的结果如下:

result2