學不完．教不停．用不盡

網友問到：

在 Excel 中有一個資料表（如下圖左），其各個類別有多個項目，例如：洗髮精有三個項目：洗髮精A、洗髮精B、洗髮精C，如何將甲、乙、丙依各類別加以小計總和（單價×數量的總和）？

【公式設計與解析】

先選取儲存格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) 將儲存格內的『甲、乙、丙』文字轉換為儲存格範圍。（先前已定義名稱『甲、乙、丙』的儲存格範圍）