在 Excel 中你可能用過 SUMIFS 和 AVERAGEIFS 函數,可以運用多個條件計算總和或是平均值。但是沒有 MINIFS 或是 MINIFS 這類的相似函數,如何利用其他的公式來模擬這個結果呢?

例如,在下圖中,想要找出一個日期區間中的最大數值和最小數值。

Excel-找出一個日期區間中的最大值和最小值(陣列公式,模擬MAXIFS和MINIFS)

 

【公式設計與解析】

這類問題可以藉助『陣列公式』來處理。

為了讓公式更易於理解,先選取儲存格A1:B26,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:日期、數值。

(1) 求日期區間中的最大數值

儲存格E4:{=MAX(IF((日期>=E1)*(日期<=E2),數值,0))}

公式輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動產生「{}」。

(日期>=E1)*(日期<=E2):執行『*』運算,相當於執行這兩個條件的邏輯 AND 運算。

IF((日期>=E1)*(日期<=E2),數值,0):在陣列公式中當日期符合這兩個條件者,傳回對應的『數值』陣列,否則傳回『0』。(因為要求最大值,『0』只是給予一個很小的值)

再利用 MAX 函數對上式的傳回值取最大值。

 

(2) 求日期區間中的最小數值

同理:

儲存格E5:{=MIN(IF((日期>=E1)*(日期<=E2),數值,2^99))}

IF((日期>=E1)*(日期<=E2),數值,0):在陣列公式中當日期符合這兩個條件者,傳回對應的『數值』陣列,否則傳回『2^99』。(因為要求最小值,『2^99』只是給予一個很大的值)

再利用 MIN 函數對上式的傳回值取最小值。

arrow
arrow
    全站熱搜

    vincent 發表在 痞客邦 留言(4) 人氣()