2013年6月9日 星期日

Excel-取符合條件的最大值(陣列公式)

在 Excel 的工作表中建立了一個學生的成績表,常會有老師在進行成績分析時,會需要用條件來篩選想要的結果。例如下圖中,要求「國文>80且英文>75者的數學最高分」和「五科均及格者的英文最高分」。

如果你用篩選工具來操作,面對條件較多時的操作將會煩瑣且修改不易,還是以公式來設計較為妥當。

【準備工作】

選取儲存格B2:F26,按一下 Ctrl+Shift+F3 鍵,定義名稱:國文、英文、數學、社會、自然。

 

【輸入公式】

(1) 找出國文>80且英文>75者的數學最高分

儲存格H2:{=MAX((國文>80)*(英文>75)*數學)}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵。

國文>80:在陣列公式中產生國文>80 的 True/False 陣列。

英文>75:在陣列公式中產生英文>75 的 True/False 陣列。

(國文>80)*(英文>75):其中的「*」執行 AND 邏輯運算,二者均為 True 時,結果才為 True。

(國文>80)*(英文>75)*數學):第二個「*」為執行「乘法」算術運算。而當 True/False 陣列執行算術運算時 True 視為 1、False 視為 0。本式子的結果會得符合二個條件者的數學成績陣列,不會任一個條件者的數學成績會被視為 0。

透過 MAX 函數從上式的數學陣列中取出最大值。

 

(2) 找出五科均及格者的英文最高分

儲存格H5:{=MAX((國文>=60)*(英文>=60)*(數學>=60)*(社會>=60)*(自然>=60)*英文)}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵。

原理同 (1) 之說明。

沒有留言:

張貼留言

好康東東