Excel函數教學:最大值最小值以及多條件陣列公式

Excel資料分析時常常需要多條件計算,本文以銷售明細表為範例,介紹MAX及MIN如何取最大值最小值,再利用陣列公式多條件計算,最後補充MAXIFS及SUMIFS函數應用。

一、出貨單明細表

簡化的出貨明細表範例,欄位有出貨單、地區、客戶以及銷售額。

Excel函數教學:最大值最小值以及多條件陣列公式 1

二、最大值最小值

求最大值函數公式:「=MAX(D:D)」,這裡的「D:D」指的是一整欄的範圍,所以D欄有資料的儲存格裡去比較大小,取最大值作為函數計算結果「120,000」。

最小值「10,000」公式為「=MIN(D:D)」,相同原理只是計算方式剛好相反。

Excel函數教學:最大值最小值以及多條件陣列公式 3

三、第N大第N小

求第N大值函數公式:「=LARGE(D:D,3)」,這裡的「D:D」和上個步驟同樣是是一整欄範圍,第二個參數「3」是指這個範圍找出第3大的數值。

第N小值公式:「=SMALL(D:D),3」則是相同原理取第三小的值。

Excel函數教學:最大值最小值以及多條件陣列公式 5

四、MAX陣列公式

求高雄最大值函數公式:「{=MAX(IF(B:B=”高雄”,D:D))}」,這裡的IF函數是指如果條件成立的話是真,條件不成立是假,其中「B:B=”高雄”」指的是在B欄一整欄的範圍中找尋”高雄”, 而這裡的「D:D」和前面步驟同樣是是一整欄範圍,會以B欄中為”高雄”的資料列,取相對應D欄資料去計算最大值作為函數最終傳回值。

這裡的陣列公式利用到了Excel真是1、假是0的特性,因此所有條件不成立的都是0,條件成立是原來的值(高雄銷售額),在所得到的陣列資料中以MAX取最大值,剛好會是條件成立的最大值。

輸入完一般的函數公式後,要將函數公式轉為陣列公式,截圖中的大括號不能夠手工輸入,要使用Control+Shift+Enter鍵,Excel會自動將一般函數公式變成是陣列公式。

Excel函數教學:最大值最小值以及多條件陣列公式 7

五、多條件MAX陣列

公式:「{=MAX(IF((B:B=”高雄”)*(C:C=”甲”),D:D))}」,這裡的條件相較上個步驟多了客戶甲,因此IF函數是「(B:B=”高雄”)*(C:C=”甲”)」,同樣是利用真是1、假是0的特性,只要地區和客戶有哪個條件不成立,有一個為零,計算結果便是0,唯有在兩個條件都成立才會是1*1,值為1,表示真,也就是成立,因此會得到相對應D欄的值,亦即高雄地區而且甲客戶的最大銷售額「100,00」。

Excel函數教學:最大值最小值以及多條件陣列公式 9

六、多條件SUM陣列

和上個步驟相比,這裡的「{=SUM(IF((B:B=”高雄”)*(C:C=”甲”),D:D))}」只是把MAX換成SUM,所以是從最大值改為是求加總值,高雄地區而且甲客戶的銷售額總共為「190,000」。

Excel函數教學:最大值最小值以及多條件陣列公式 11

七、MAXIFS及SUMIFS

除了使用陣列公式,其實Excel普通函數也可以執行多條件計算,例如MAXIFS和SUMIFS,一個求最大值,一個求加總值,針對同一份報表,各欄位要加上複數條件也是很容易,如同截圖公式所示。

Excel函數教學:最大值最小值以及多條件陣列公式 13

Excel多條件計算的實務應用

本篇文章主要是以銷售報表中的地區及客戶進行多條件彙總計算,最大值或者加總值,從計算方式而言,其實也可以使用AVERAGE或COUNT計算平均值或個數,從應用場景而言,當然也能用在會計費用、採購進貨、製造工單等案例,讀者可以依照自己所遇到狀況照樣造句設計Excel函數公式,應該在資料分析時會更加得心應手。

加強學習:贊贊小屋Excel函數文章

Excel函數教學:最大值最小值以及多條件陣列公式 15
加入Line社群,口袋裡的Excel小教室!