Excel MAX IF多條件是資料分析時常見需求,本文以銷售明細表為範例,基礎介紹6個函數取最大值最小值,進階設計較為複雜陣列公式,最後補充MAXIFS及SUMIFS用法。
目錄
Toggle一、出貨單明細表
簡化的出貨明細表範例,欄位有出貨單、地區、客戶以及銷售額。
二、最大值最小值
求最大值函數公式:「=MAX(D:D)」,這裡的「D:D」指的是一整欄的範圍,所以D欄有資料的儲存格裡去比較大小,取最大值作為函數計算結果「120,000」。
最小值「10,000」公式為「=MIN(D:D)」,相同原理只是計算方式剛好相反。
三、第N大第N小
求第N大值函數公式:「=LARGE(D:D,3)」,這裡的「D:D」和上個步驟同樣是是一整欄範圍,第二個參數「3」是指這個範圍找出第3大的數值。
第N小值公式:「=SMALL(D:D),3」則是相同原理取第三小的值。
四、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 MAX IF多條件
公式:「{=MAX(IF((B:B=”高雄”)*(C:C=”甲”),D:D))}」,這裡的條件相較上個步驟多了客戶甲,因此IF函數是「(B:B=”高雄”)*(C:C=”甲”)」,同樣是利用真是1、假是0的特性,只要地區和客戶有哪個條件不成立,有一個為零,計算結果便是0,唯有在兩個條件都成立才會是1*1,值為1,表示真,也就是成立,因此會得到相對應D欄的值,亦即高雄地區而且甲客戶的最大銷售額「100,00」。
六、多條件SUM陣列
和上個步驟相比,這裡的「{=SUM(IF((B:B=”高雄”)*(C:C=”甲”),D:D))}」只是把MAX換成SUM,所以是從最大值改為是求加總值,高雄地區而且甲客戶的銷售額總共為「190,000」。
七、MAXIFS及SUMIFS
除了使用陣列公式,其實Excel普通函數也可以執行多條件計算,例如MAXIFS和SUMIFS,一個求最大值,一個求加總值,針對同一份報表,各欄位要加上複數條件也是很容易,如同截圖公式所示:
=MAXIFS(D:D,B:B,”高雄”)
=MAXIFS(D:D,B:B,”高雄”,C:C,”甲”)
=SUMIFS(D:D,B:B,”高雄”,C:C,”甲”)
Excel多條件計算的實務應用
本篇文章主要是以銷售報表中的地區及客戶進行多條件彙總計算,最大值或者加總值,從計算方式而言,其實也可以使用AVERAGE或COUNT計算平均值或個數,從應用場景而言,當然也能用在會計費用、採購進貨、製造工單等案例,讀者可以依照自己所遇到狀況照樣造句設計Excel函數公式,應該在資料分析時會更加得心應手。
參考資源
- 微軟Excel MAX函數說明。
- 贊贊小屋Excel教學手冊。
- Excel表格製作範例下載。
- YouTube:Excel函數教學。
- 課程:Excel職場應用大全集。