【文末赠书】行列交叉查询公式汇总及解释(三)
友情提醒:
文末有赠书活动,赠送作者亲笔签名的《竞争力:玩转职场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