2018年4月12日 星期四

Excel-計算名稱中含有關鍵字者的總和(SUMPRODUCT,SUBSTITUTE)

在 Excel 中的一個工作表(如下圖),如何根據指定的項目名稱來計算合乎的品名之加總領用量。
例如:罐子就有多種的樣式,也就是品名中含有『項目』的內容,合於項目的要挑出來予以加總。
Excel-計算名稱中含有關鍵者的總和(SUMPRODUCT,SUBSTITUTE)

【公式設計與解析】
先將儲存格範圍定義名稱。選取儲存格A1:B12,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:品名、領用量。
儲存格E2:=SUMPRODUCT((SUBSTITUTE(品名,D2,"")<>品名)*領用量)
(1) SUBSTITUTE(品名,D2,"")<>品名
在 SUMPRODUCT 函數中,利用 SUBSTITUTE(品名,D2,"") 將每品名的名稱,以空白取笩儲存格D2。若結果和原來品名不相同,則表示該品名中含有儲存格D2的內容。其結果會傳回 TRUE/FALSE 陣列。
(2) (SUBSTITUTE(品名,D2,"")<>品名)*領用量
將第(1)式傳回的 TRUE/FALSE 陣列乘以『領用量』,於 SUMPRODUCT 函數中將讓式的結果予以加總。

沒有留言:

張貼留言

檢視其他文章

好康東東