2015年9月18日 星期五

Excel-運用雙條件從資料表摘要資料(OFFSET,INDIRECT,SUMPRODCUT)

在 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。

沒有留言:

張貼留言

好康東東