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小教室!
Ask ChatGPT
Set ChatGPT API key
Find your Secret API key in your ChatGPT User settings and paste it here to connect ChatGPT with your Tutor LMS website.