2014年2月9日 星期日

Excel-找出指定項目的最大值和最小值(陣列公式)

在網路上看到有人在討論:在一個資料範圍中,要找出指定項目的最大值和最小值,而指定一個項目時,該如何處理?指定二個項目時,該如何處理?(參考下圖)

通常這種題目,都必須透過陣列公式來處理。可以用來練習、認識陣列公式的處理!

【準備工作】

選取儲存格A1:B25,按一下 Ctrl+Shift+F3 鍵,選取「頂端列」,定義名稱:項目、內容。

 

【輸入公式】

以下公式全部是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵。

(1) 找出甲的最大值

儲存格E2:{=MAX((項目="甲")*內容)}

項目="甲":這是一個判斷式,會產生符合(True)和不符合(False)的陣列。

(項目="甲")*內容:上式乘以(*)內容時,True 會被視為 1,而 False 會被視為 0。得到的結果只有符合條件的內容會被留下,不符合者全會被視為「0」。

將上述透過 MAX 函數,即可找出符合條件者的最大值。

 

(2) 找出甲的最小值

儲存格F2:{=MIN((項目="甲")*內容)} (X)錯誤解答

當你在求最小值,如果仿(1)的作法,將會得到錯誤的結果。因為不符合條件者會被視為 0,因此 MIN 函數找到的最小值是 0,但是內容中的最小值並非 0。

更改成以下的公式:

儲存格F2:{=MIN(IF(項目="甲",內容,FALSE))}

將 (項目="甲")*內容) 修改成 IF(項目="甲",內容,FALSE),讓不符合者以 False 顯示,再透過 MIN 函數找尋最小值時,就不會找到 0 值了。

 

(3) 找出甲和乙的最大值

如果要找二種項目的最大值,參考以下的做法:

儲存格E8:{=MAX(((項目="甲")+(項目="乙"))*內容)}

公式中的「+」,乃執行邏輯 OR 運算,將該結果乘以內容,再取 MAX 函數的結果,即為甲和乙二個項目的最大值。

你也可以將式改成以下的寫法,結果是相同的:

儲存格F8:{=MAX((項目="甲")*內容,(項目="乙")*內容)}

 

(4) 找出甲和乙的最小值

參考(3)式,你應該會寫找出甲和乙的最小值了吧!參考下式:

儲存格E8:{=MIN(IF((項目="甲")+(項目="乙"),內容,FALSE))}

 

【延伸閱讀】

如果你想要了解陣列公式的處理過程,可以在[公式]功能表中選取「評估值公式」,然後再仔細觀察每個步驟的運算結果。

或是在公式中選取公式的片段,然後按一下 F9 鍵,即可看到部分公式的執行結果。

善用工具,將有助於增加對公式的理解!

沒有留言:

張貼留言

好康東東