在 Excel 的资料表中有两组数值( A 组和 B 组),要以阵列公式在某一组中找出合适前提所对应另外一组的均匀。本例中例举分歧二种要求来操练,固然对一些初学者也许有些难处,可是多多体味才有机遇进一步利用。
【準备工作】
拔取贮存格A1:B22,按一下 Ctrl+Shift+F3 键,界说名称:A组、B组。
【输入公式】
(1) 求在 A 组年夜于 B 组中,B 组数值前 3 名的均匀
贮存格E2:{=AVERAGE(LARGE(IF(A组>B组,B组,),{1,2,3}))}
这是阵列公式,输入完成要按 Ctrl+Shift+Enter 键。
IF(A组>B组,B组,) = {0;57;1;72;0;0;78;63;0;0;33;15;0;0;0;29;34;0;9;0;0},找出 A > B 的数值阵列。
LARGE(IF(A组>B组,B组,),{1,2,3}) = {78,72,63},找出前 3 名的数值阵列。此中 {1,2,3} 为 1, 2, 3 构成的阵列。
再透过 AVERAGE 函数将这三个数加以均匀。
(2) 求 B 组的前 3 名所对应 A 组数值的均匀
贮存格E3:{=SUM(IF(RANK(B组,B组)<=3,A组,))/(SUM(IF(RANK(B组,B组)<=3,1,)))}
这是阵列公式,输入完成要按 Ctrl+Shift+Enter 键。
IF(RANK(B组,B组)<=3,A组,) = {0;0;0;0;0;0;0;0;47;83;0;0;85;0;0;0;0;0;0;0;98},找出 B 组前 3 名对应的 A 组数值阵列,本例很特别,对应的数值有 4 个。
SUM(IF(RANK(B组,B组)<=3,A组,)):求得上述数值阵列的和。
IF(RANK(B组,B组)<=3,1,)) = {0;0;0;0;0;0;0;0;1;1;0;0;1;0;0;0;0;0;0;0;1},找出合适 B 组前 3 名的阵列(合适者为1),再透过 SUM 函数便可得个数。
将「和」除以「个数」便可得均匀。(想一想看:为什么不直接以 AVERAGE 来处置呢?)
【弥补资料】
上述(1)中的公式可以改写:
贮存格E2:=AVERAGE(LARGE(IF(A组>B组,B组,),ROW(1:3)))
此中 ROW(1:3北京拓展公司) = {1,2,3},可以合用于若是阵列值较多项时,例如:ROW(1:30)可以代表 {1,2, … , 29, 30}
别的,你若是想要领会公式中阵列的值在公式履行中的成果,可以在资料编纂列中先拔取部门的公式(下图反白处):
按一下 F9 键,便可当即获得阵列的成果(下图反白处):
创作者先容 vincent