2014年2月18日 星期二

Excel-找出分組最大值和最小值(陣列公式,MAXIF,MINIF)

在 Excel 中有一個資料表,其中有多個分組的資料(參考下圖),如何找出各組最大值和最小值呢?

你可能使用過 SUMIF、COUNTIF、AVERAGEIF 等條件式運算函數,但 Excel 中並沒有 MAXIF 或 MINIF 等函數,不過你可以使用陣列公式來取代。

【準備工作】

選取儲存格A1:B21,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:組別、數值。

【建立公式】

(1) 求各組最大值

儲存格C2:{=IF(B2=MAX(IF(組別=A2,數值,FALSE)),"V","")}

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

{IF(組別=A2,數值,FALSE)}:找出和「組別」陣列中和儲存格A2符合的儲存格陣列(判斷式中若不符合者,則給予 False)。

{MAX(IF(組別=A2,數值,FALSE))}:利用 MAX 函數取出上式中的取大值。

 

(2) 求各組最小值

儲存格D2:{=IF(B2=MIN(IF(組別=A2,數值,FALSE)),"V","")}

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

原理同(1),將 MAX 函數改用 MIN 函數。

 

【延伸學習】

儲存格C2:{=IF(B2=MAX(IF(組別=A2,數值,FALSE)),"V","")},其中的 FALSE 如果以 0 或空白來取代,都會得到錯誤的結果,因為 0 或空白都會被視為 0,而 0 可能會誤成為各組中的最小值。

沒有留言:

張貼留言

好康東東