2017年4月8日 星期六

Excel-取同類型名稱小計(SUBSTITUTE,SUMPRODUCT)

網友問到:
在 Excel 中有一個資料表(如下圖左),其各個類別有多個項目,例如:洗髮精有三個項目:洗髮精A、洗髮精B、洗髮精C,如何將甲、乙、丙依各類別加以小計總和(單價×數量的總和)?
Excel-取同類型名稱小計(SUBSTITUTE,SUMPRODUCT)
【公式設計與解析】
先選取儲存格A1:E8,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:項目、單價、甲、乙、丙。
作法一:
儲存格H2:=SUMPRODUCT((SUBSTITUTE(項目,G2,"")<>項目)*單價*甲)
(1) SUBSTITUTE(項目,G2,"")
利用 SUBSTITUE 函數,在項目陣列中判斷各個項目是否含有儲存格G2的內容。 SUBSTITUTE(項目,G2,"") 用以將各個項目含有的儲存格G2內容以空白取代。
(2) SUBSTITUTE(項目,G2,"")<>項目
在 SUMPRODUCT 函數中利用條件:SUBSTITUTE(項目,G2,"")<>項目,判斷是否成立。若成立,代表該項目『含有』儲存格G2內容,結果傳回 TRUE/FALSE 陣列。
(3) (SUBSTITUTE(項目,G2,"")<>項目)*單價*甲
公式中的『*』運算,會將 TRUE/FALSE 陣列轉換為 1/0 陣列。
同理:
儲存格I2:=SUMPRODUCT((SUBSTITUTE(項目,G2,"")<>項目)*單價*乙)
儲存格J2:=SUMPRODUCT((SUBSTITUTE(項目,G2,"")<>項目)*單價*丙)
複製儲存格H2:J2,貼至儲存格H2:J4。

作法二:
儲存格H2:
=SUMPRODUCT((SUBSTITUTE(項目,$G2,"")<>項目)*單價*INDIRECT(M$1))
複製儲存格H2,貼至儲存格H2:J4。
在此和作法一不同之處,在於利用 INDIRECT(M$1) 將儲存格內的『甲、乙、丙』文字轉換為儲存格範圍。(先前已定義名稱『甲、乙、丙』的儲存格範圍)

沒有留言:

張貼留言

檢視其他文章

好康東東