學不完．教不停．用不盡

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