學不完．教不停．用不盡

在 Excel 中有一個各班的成績表，有老師想要從成績總表摘要出各班總分最高分且數學大於或等於90分者，該如何處理？(本例假設總分無同分者)

【準備工作】

選取儲存格A1:G25，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，建立名稱：班級、座號、姓名、國文、英文、數學、總分。

【公式設計與解析】

為了說明方便，特別建立了一個輔助欄位(M欄)。

儲存格M2：

｛=SUMPRODUCT((班級=I2)*(總分=MAX(IF((班級=I2),總分)))*(數學>=90),
ROW(班級))｝

這是陣列公式，輸入完成要按 Ctrl+Shift+Enter 鍵，Excel 會自動加上｛｝。

MAX(IF((班級=I2),總分))：在陣列公式中找出班級名稱和儲存格I2相同者的陣列，其總分最大值者。

在 SUMPRODUCT 函數中找出符合以下三個條件者的乘積和：

(1) 班級=I2：找出班級陣列中和儲存格I2相同者。

(2) 總分=MAX(IF((班級=I2),總分))：找出總分陣列中和「符合班級陣列為儲存格I2者的最大值」相同者。

(3) 數學>=90：找出數學陣列中大於或等於90者。

儲存格J2：=IF($M2=0,"",OFFSET(INDIRECT(J$1),$M2-2,0,1,1))

INDIRECT(J$1)：將儲存格J1的文字藉由 INDIRECT 函數轉換為真實儲存格範圍(已定義名稱)。

再由 OFFSET 函數顯示對應位址的儲存格內容。如果儲存格M2的內容為0，表示沒有符合的項目。

儲存格K2：=IF($M2=0,"",OFFSET(INDIRECT(K$1),$M2-2,0,1,1))

儲存格L2：=IF($M2=0,"",OFFSET(INDIRECT(L$1),$M2-2,0,1,1))

複製儲存格J2:M2，貼至儲存格J2:M7。