2018年1月9日 星期二

Excel-依多條件取出清單中符合的項目加總(SUMPRODUCT)

(網友提問)根據下圖左的 Excel 工作表中的資料清單,來找出物料碼以 EIM 為首者的各月訂單數量總和,該如何處理?
Excel-依多條件取出清單中符合的項目加總(SUMPRODUCT)

【公式設計與解析】
假設資料來源位於儲存格A1:C115。
選取儲存格A1:C115,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:交貨日期、物料、訂單數量。
儲存格G2:
=SUMPRODUCT((MONTH(交貨日期)=F3)*(LEFT(物料,3)="EIM")*訂單數量)
(1) 條件一:MONTH(交貨日期)=F3
在 SUMPRODUCT 函數中判斷「交貨日期」儲存格陣列中的月份(利用 MONTH 函數)是否和儲存格F3相同,傳回 TRUE/FALSE 陣列。
(2) 條件二:(LEFT(物料,3)="EIM"
在 SUMPRODUCT 函數中判斷「物料」儲存格陣列中的前 3 碼(利用 LEFT 函數)是否為「EIM」,傳回 TRUE/FALSE 陣列。
(3) (MONTH(交貨日期)=F3)*(LEFT(物料,3)="EIM")*訂單數量
在 SUMPRODUCT 函數中執行乘積和的運算,其中『*』運算子,相當於執行邏輯 AND 運算,運算過程會將 TRUE/FALSE 陣列轉換為 1/0陣列。

沒有留言:

張貼留言

檢視其他文章

好康東東