[EXCEL] 淺談陣列公式－高標低標的計算

　
　　相關函數：IF() / AVERAGE()

　　什麼是「陣列公式」？套用 Excel 的說明檔：「針對一或多組值執行多個計算，然後傳回單一結果或多個結果的公式。陣列公式括在大括弧 { } 中，且藉由按 CTRL+SHIFT+ENTER 輸入。」

　　舉例來說，一般公式 =IF(A1>60,A1,"") 是以單一儲存格 A1 為輸入值，計算結果也是單一值。這時，如果把 A1 改成陣列 A1:A5，再按 CTRL+SHIFT+ENTER 完成輸入，成為陣列公式 {=IF(A1:A5>60,A1:A5,"")}，結果會如何？

##CONTINUE##

　　答案是 Excel 會做 5 次 IF() 計算，每次的輸入值分別是 A1/A2/A3/A4/A5，最後得到 5 組答案，組成一個結果陣列。這個結果陣列，可以進一步當做其他函數的輸入值，或是把結果陣列的第一組結果，也就是針對 A1 運算所得的結果，顯示在儲存格中。

　　舉個實際的例子，如下表，

　　10 組分數，如果要計算平均分數，B12 =AVERAGE(B2:B11) 即可。但是如果要計算高標呢？

　　所謂高標，是指分數高於平均分數的所有人的平均分數。傳統的做法是，在 C 欄輸入公式把高於平均分數的人先挑出來，C2 =IF(B2>$B$12,B2,"")，公式往下複製，再將 C 欄做平均即可，結果如 C12 =AVERAGE(C2:C11)。

　　但是，如果不想多出一個 C 欄，想直接算出結果呢？那就得靠「陣列公式」了。如 B13，輸入陣列公式

=AVERAGE(IF(B2:B11>B12,B2:B11,""))

　　再按 CTRL+SHIFT+ENTER 完成輸入，就可以直接得到答案。

　　看出奧妙了嗎？B13 的陣列公式其實就是 C12 和 C2:C11 的組合！C2:C11 的內容，和陣列公式做了十次 IF() 運算之後產生的結果陣列一樣，只是在陣列公式中，這個結果陣列在 Excel 內部就做掉了，外面就不必再多用一個 C 欄來計算。

　　看懂了陣列公式的奧妙，那低標就不困難了，B14

=AVERAGE(IF(B2:B11<B12,B2:B11,""))

　　再按 CTRL+SHIFT+ENTER 完成輸入。

　　陣列公式注意事項：

• 一定要以 CTRL+SHIFT+ENTER 完成輸入
• 公式中用到的陣列大小一定要一樣

2007-3-29 補充：

　　如果高標的定義是「全班分數前50%的平均」，則可以利用函數 QUARTILE() 來取得全班前 50% 的分數分界點。新的公式為

=AVERAGE(IF(B2:B11>QUARTILE(B2:B11,2),B2:B11,""))

　　記得一樣要按 CTRL+SHIFT+ENTER 成為陣列公式。