【文末赠书】行列交叉查询公式汇总及解释(三)

友情提醒:

文末有赠书活动,赠送作者亲笔签名的《竞争力:玩转职场Excel,从此不加班》共本,嗯,本次五本,我不会告诉你还有漂亮金属书签的。

在交叉查询公式汇总的前二篇文章中,我们介绍了VLOOKUP+MATCH、OFFSET+MATCH、INDEX+MATCH、以及使用二个INDEX函数和空格来做交叉引用的方法,文章链接:

行列交叉查询公式汇总及解释(一)

行列交叉查询公式汇总及解释(二)

下面我们来介绍使用求和函数来引用数值。

5、使用SUMIF函数

我们知道SUMIF是条件求和公式,一般都是用它来按指定的条件来求和。实际上,在特定的条件下,也可用来引用,这个特定的条件是:

  • 要引用的是数值。

  • 符合条件的记录有且仅有一条。

比如在本案例中,要引用“职员A04”1月的金额,引用的是数值(满足前提1),A列”职员A04“只有一条记录(满足前提2),那么,就可以使用SUMIF来引用:

=SUMIF(A1:A8,"职员A04",B1:B8)

实际上,SUMIF函数第三参数的单元区域起作用的是其左上角的那个单元格(B1),起到的作用是定位定点,第三参数完全可以只写左上角那个单元格,

关于SUMIF的详细解释详见:

http://blog.sina.com.cn/s/blog_4e6c2b960100xove.html

因而,上面的公式可以写成:

=SUMIF(A1:A8,"职员A04",B1)

现在,我们要用SUMIF来实现交叉引用,只需用函数编制公式,根据指定的值计算第三参数就是了,比如查询3月,第三参数就是D1:D8(可简写为D1),查询7月,第三参数就是H1:H8(可简写为H1)。

要得到D1、H1,可使用前面介绍的INDEX和MATCH函数,编制下面的公式:

=INDEX(B1:I1,MATCH(B10,$B$1:$I$1,0))

将其套入到SUMIF中,完整的公式为:

=SUMIF(A1:A8,A10,INDEX(B1:I1,MATCH(B10,$B$1:$I$1,0)))。

6、使用SUMPRODUCT函数

同样的道理,也可使用SUMPRODUCT函数来交叉引用,需要强调的是,也得满足前面所述的二个前提。

其交叉引用的公式为:

=SUMPRODUCT((A2:A8=A10)*(B1:I1=B10)*B2:I8)

7、使用LOOKUP函数

我们可以使用LOOKUP的数组形式来交叉查询:

=LOOKUP(A10,A2:INDEX(B8:I8,MATCH(B10,B1:I1,0)))

但是,需要强调的是:

  • 该函数要求查询列表按升序排列;

  • 如果数组包含宽度比高度大的区域(列数多于行数)LOOKUP 会在第一行中搜索 查找值;

  • 如果数组是正方的或者高度大于宽度(行数多于列数),LOOKUP 会在第一列中搜索查找值;

因而,上面的公式是不太完善的。我们可以将其修改为完善的公式,并使用向量形式:

=LOOKUP(1,0/(A2:A8=A10),INDEX(B2:I8,0,MATCH(B10,B1:I1,0)))

8、使用INDIRECT

INDIRECT函数就象马良的神笔,可以将文本字符串变为真正的引用。比如我们在本案例的A1单元格输入文本F5,那么它就是一个字符串F5,并不是表示F5单元格,比如,我们在A9单元格输入公式

=A1

那么A9单元格会链接显示A1单元格中的内容“F5”,但是,如果我们在A9单元格输入公式:

=INDIRECT(A1)

或者:

=INDIRECT("F5")

此公式是将字符串F5变为单元格引用,也就是引用F5单元格的值345。

我们平时用的引用样式是A1、B3、F11这种样式,用数字表示行,用字母表示列,这种样式简称为A1样式。实际上Excel中还可使用一种叫R1C1样式,R表示行,C表示列,R1C1表演第一行第一列,即A1;

R5C3表示第五行第三列,即C5。

要切换引用样式到R1C1中,如果在INDIRECT中使用R1C1样式,只要将R1C1第二参数设为0,即可。

在本案例,要实现交叉引用,只需用计算MATCH计算出要查找对象的位置,然后套入到INDIRECT中即可,比如职员A04在5月中的金额,就是引用F5单元格,用R1C1表示就是R5C6,

行号“5”可用公式MATCH(A10,A1:A8,0)计算得出

列号“6”可用公式MATCH(B10,$A$1:$I$1,0)得出

套用INDIRECT函数,完整的公式就是:

=INDIRECT("R"&MATCH(A10,A1:A8,0)&"C"&MATCH(B10,$A$1:$I$1,0),0)

One More Thing

(0)

相关推荐