2013年5月19日 星期日

Excel-符合條件的最大值(MAX,WEEKDAY,陣列)

在 Excel 中有一個含有日期、人員、數值欄位的工作表(如下圖,其中的星期欄位是一個輔助說明的欄位),想要求取某些條件下的數值的最大值,該如何處理?

【準備工作】

選取儲存格A1:D30,按一下 Ctrl+Shfit+F3 鍵,勾選「頂端列」,定義名稱:日期、星期、人員、數值。

【輸入公式】

(一) 各個人員的最大值

儲存格G2:{=MAX(IF(人員=F2,數值,))}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵。複製儲存格G2,貼至儲存格G2:G5。

IF(人員=F2,數值,):因為採用陣列公式,可以求得在人員欄位中符合儲存格F2(甲)的數值陣列。

IF 公式中的第三個參數為空白,可以讓求得的結果為「空白」;如果填入 0 或是 Fasle,則結果會顯示「0」。

最後藉由 MAX 函數,將求得的數值陣列中取最大值,即為所求。

 

(二) 各個星期幾的最大值

儲存格G8:{=MAX(IF(WEEKDAY(日期,1)=ROW(1:1),數值,))}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵。複製儲存格G8,貼至儲存格G8:G14。

原理同(一)。

WEEKDAY(日期,1):WEEKDAY 函數可以求得一個星期幾對應的數值。本例中選取「1」,所以對應ROW(1:1)=1,因此可以求得星期日的數值陣列。若往下複製公式時,ROW(1:1)→ROW(2:2)→ROW(3:3)→ …,如此可以求得各個星期幾對應的數值陣列。

image

 

(三) 各個月份的最大值

儲存格G17:{=MAX(IF(MONTH(日期)=ROW(4:4),數值,))}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵。複製儲存格G17,貼至儲存格G17:G20。

原理同(一)和(二)。

IF(MONTH(日期)=ROW(4:4),數值,):透過 MONTH 函數取得日期中的月份,而 ROW(4:4)=4,即求得 4 月份的數值陣列。

 

(四) 各個月份中某個人員的最大值

儲存格G23:{=MAX(IF((MONTH(日期)=ROW(4:4))*(人員="甲"),數值,))}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵。(以下亦同)

儲存格G24:{=MAX(IF((MONTH(日期)=ROW(5:5))*(人員="乙"),數值,))}

儲存格G25:{=MAX(IF((MONTH(日期)=ROW(6:6))*(人員="丙"),數值,))}

儲存格G26:{=MAX(IF((MONTH(日期)=ROW(7:7))*(人員="丁"),數值,))}

原理同(一)、(二)和(三)。

IF((MONTH(日期)=ROW(4:4))*(人員="甲"):在 IF 函數中使用雙條件運算,其中的「*」運算子,相當於將條件做 AND 運算。

沒有留言:

張貼留言

好康東東