2013年10月9日 星期三

Excel-在某一組中找出符合條件所對應另一組的平均(陣列公式)

在 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 鍵,即可立即得到陣列的結果(下圖反白處):

沒有留言:

張貼留言

好康東東