2013年5月7日 星期二

Excel-擇優計算平均分數(LARGE,陣列)

有老師重覆問到如果想要將學生的數次平時成績,以擇優方式來計算平均成績,不知何種方式較為方便。因為之前以陣列公式來設計,有些老師無法瞭解原理,本次改用另一種方法來設計。

參考下圖中的資料表,以擇優前 6 名的成績來計算:

儲存格O2:=AVERAGE(LARGE(C2:N2,{1,2,3,4,5,6}))

此公式的意義在於使用 LARGE 函數由儲存格C2:N2中,挑選最大的 1,2,3,4,5,6 數值,透過 AVERAGE 函數,計算平均。

{1,2,3,4,5,6} 是一種陣列的表示法,這樣處理可以在一個公式中做 6 個計算。

如果分數中出現「文字」,例如:缺(缺考),或是空白,也不影響公式結果。

【補充】

如果想要將前 6 名以紅色標示,該如何設計?

1. 選取儲存格C2:N26。

2. 在設定格式化的條件中,選取規則類型:使用公式來決定要格式化哪些儲存格。

3. 輸入公式:=N(C2)>=LARGE($C2:$N2,6)

(條件為:儲存格內容大於或等於第 6 名者,其中使用 N 函數,是因為儲存格中可能出現文字或空白。)

4. 設定格式:字型色彩為紅色

沒有留言:

張貼留言

檢視其他文章

好康東東