Excel061-你知道不同方向的数组怎样交叉运算么？

布衣公子PPT · 公众号 · PPT · 2017-11-20 21:00

正文

你知道不同方向的数组如何进行交叉运算么？我们由一个实际问题来引入今天的话题。

如上图所示案例，要求根据右表的奖励标准在左表计算奖金金额。我们有哪些方法呢？

我第一个想到的就是用VLOOKUP函数结合MATCH函数。具体方法如下：

①在D2单元格录入公式：=VLOOKUP(B2,$H$2:$L$4,MATCH(C2,$H$1:$L$1,0),0)

②向下填充公式。

公式原理分析：

①VLOOKUP函数的第一参数：找谁？以D2单元格的公式为例，我们要找的是B2单元格的级别（二级）。

②VLOOKUP函数的第二参数：在哪里找？我们需要在$H$2:$L$4区域查找，查找的区域应该以要查找的内容所在列作为首列，并包含需要返回的所有列。

③VLOOKUP函数的第三参数：需要返回从首列开始数的第几列？这个我们用MATCH函数来确定，MATCH(C2,$H$1:$L$1,0)得到的结果即为我们要返回的列号，类别C应该为第4列。

④VLOOKUP函数的第四参数：很显然我们是要进行精确查找，所以第四参数为0。

既然MATCH函数能获得类别所在的列号，那就能获得级别所在的行号。这样我们用INDEX函数也能计算出奖金。具体方法如下：

①在E2单元格录入公式：=INDEX($I$2:$L$4,MATCH(B2,$H$2:$H$4,0),MATCH(C2,$I$1:$L$1,0))

②向下填充公式。

公式原理分析：

①INDEX函数的第一参数：在哪里查找？以E2单元格公式为例，我们要在$I$2:$L$4区域内进行查找。

②INDEX函数的第二参数：要查找的内容在第几行？MATCH(B2,$H$2:$H$4,0)帮助我们获得要查找的级别（二级）所在的行号。

③INDEX函数的第三参数：要查找的内容在第几列？ MATCH(C2,$I$1:$L$1,0)帮助我们获得要查找的类别（C）所在的列号。

上面两种方法是比较传统的方法，那么数组能帮我们解决这个问题么？答案毋庸置疑。方法如下：

①在F2单元格录入公式：

=SUMPRODUCT((B2=$H$2:$H$4)*(C2=$I$1:$L$1)*$I$2:$L$4)

②向下填充公式。

公式原理分析：

我们用F9分别查看公式的每一部分。以F2单元格公式为例：

1）B2=$H$2:$H$4 得出的结果是一个纵向的逻辑值数组{FALSE;TRUE;FALSE}，此处我们用①表示。

2）C2=$I$1:$L$1 得出的结果是一个横向的逻辑值数组{FALSE,FALSE,TRUE,FALSE}，此处我们用②表示。

3）一个横向的逻辑值数组乘以一个纵向的逻辑值数组就得出了一个包含12个元素（3行×4列）的数组{0,0,0,0;0,0,1,0;0,0,0,0}。此处我们用④表示。

4）$I$2:$L$4 区域为数据返回区域，也是一个包含12个元素的数组。{500,400,300,100;200,150,100,50;50,20,10,5}，此处我们用③表示。

5）最后数组③乘以数组④得出的结果用SUMPRODUCT 求和即可计算出奖金。

往期教程：

Excel060-数组真的可以让复杂的公式一步到位