打个响指,今天给大家聊一下什么是交叉表查询。这词听起来很高冷,但在工作中其实很常见。 我举个例子。这例子是前两天初阶函数培训群的练习题,某些凶残的男男女女提供了近20种函数解法… 如下图所示,A:F是数据源。现在需要在I2:K5区域编写函数,查询H列人名相关科目的考试成绩。 这个问题就是一个比较典型的交叉表查询问题,由行列交叉返回查询结果。
通常使用VLOOKUP函数解决:
=IFERROR(VLOOKUP($H2,$A:$F,MATCH(I$1,$A$1:$F$1,0),0),"查无") 解释一下公式: MATCH(I$1,$A$1:$F$1,0)部分,使用MATCH函数计算查询标题在数据源区域标题中的序号。比如'语文'返回结果5,说明语文成绩在数据源的第5列。 MATCH函数的计算结果作为VLOOKUP函数的第3参数,使VLOOKUP返回数据源指定列的数据。如果MATCH或者VLOOKUP查无结果,会返回一个错误值,所以最后再使用IFERROR函数屏蔽错误值,使它返回字符串"查无"。
就这么回事。 不过事没完,除了VLOOKUP函数之外,还有N种解法。我数一数,零零碎碎的有几十种,基本都是函数系列教程里讲过的。 接下来给大家分享其中常用的十几种。如有困惑,VIP会员可以根据函数名称,在知识星球内查阅相关教程;当然也可以在微信答疑群中提问交流。 牵牵手,跟我走。 ……
INDEX/OFFSET/INDIRECT这仨计算过程差不多,都是搭配MATCH函数定义行列交叉位置。 1,INDEX函数: 公式看不全可以左右拖动...▼ =IFERROR(INDEX($A:$F,MATCH($H2,$A:$A,0),MATCH(I$1,$A$1:$F$1,0)),"查无")
2,OFFSET函数: 小思考:为什么MATCH函数计算结果要-1? =IFERROR(OFFSET($A$1,MATCH($H2,$A:$A,0)-1,MATCH(I$1,$A$1:$F$1,0)-1),"查无")
3,INDIRECT函数: =IFERROR(INDIRECT(ADDRESS(MATCH($H2,$A:$A,0),MATCH(I$1,$A$1:$F$1,0))),"查无")
……
然后就是LOOKUP家族的其它三位▼ 4,HLOOKUP函数: =IFERROR(HLOOKUP(I$1,$A:$F,MATCH($H2,$A:$A,0),0),"查无")
5,LOOKUP函数: =IFERROR(LOOKUP(1,0/($A$1:$A$18=$H2),OFFSET($A$1:$A$18,0,MATCH(I$1,$B$1:$F$1,0))),"查无")
6,XLOOKUP函数: 小思考:为什么不能使用XLOOKUP第4参屏蔽错误值? =IFERROR(XLOOKUP($H2,$A$2:$A$18,XLOOKUP(I$1,$A$1:$F$1,$A$2:$F$18)),"查无")
……
由于案例中的查询结果是数值,而且是唯一值,因此还可以使用以下聚合函数: 7,MAX函数: =TEXT(MAX(($A$2:$A$18=$H2)*($B$1:$F$1=I$1)*$B$2:$F$18),"0;;查无")
8,MIN函数:
小思考:为什么MAX不用搭配IF函数而MIN函数需要? =TEXT(MIN(IF(($A$2:$A$18=$H2)*($B$1:$F$1=I$1),$B$2:$F$18)),"0;;查无")
9,SUM函数: 数组公式: =TEXT(SUM(($A$2:$A$18=$H2)*($B$1:$F$1=I$1)*$B$2:$F$18),"0;;查无")
10,SUMPRODUCT函数: =TEXT(SUMPRODUCT(($A$2:$A$18=$H2)*($B$1:$F$1=I$1)*$B$2:$F$18),"0;;查无")
11,SUMIF函数: =TEXT(SUMIF($A:$A,$H2,OFFSET($A:$A,0,MATCH(I$1,$B$1:$F$1,0))),"0;;查无")
……
此外还有一些其它由于版本和水平限制,而不常用的解法:
12,FILTER函数:
=IFERROR(FILTER(FILTER($A$1:$F$18,$A$1:$A$18=$H2),$A$1:$F$1=I$1),"查无")
13,TEXTJOIN函数:
=TEXTJOIN("",1,IF(($A$2:$A$18=$H2)*($B$1:$F$1=I$1),$B$2:$F$18,""))
14,CONCAT函数
=CONCAT(IF(($A$2:$A$18=$H2)*($B$1:$F$1=I$1),$B$2:$F$18,""))
15,MMULT函数 =TEXT(SUM(MMULT(($A$2:$A$18=$H2)*($B$1:$F$1=I$1)*$B$2:$F$18,ROW($1:$5)^0)),"0;;查无")
16,FREQUENCY函数
仅限成绩范围是1-100之间的整数: =IFERROR(MATCH(1,FREQUENCY(IF($A$2:$A$18&$B$1:$F$1=$H2&I$1,$B$2:$F$18),ROW($1:$100)),),"查无")
…… 没了,今天给大家分享的内容就这样,打完收工,下期再见。
|