2016年3月14日 星期一

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

在 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 函數對上式的傳回值取最小值。

沒有留言:

張貼留言

檢視其他文章

好康東東