學不完．教不停．用不盡

有網友根據下圖左 Excel 的基本資料表，其中包含日期、項目和數值的清單，而項目是由多個不固定的內容所組成。若要轉換成下圖右的資料表，分別依內容和月份計算數值總和，該如何處理？

【公式設計與解析】

選取儲存格A1:C24，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：

日期、項目、數值。

儲存格F2：=SUMPRODUCT((MONTH(日期)=ROW(1:1))*(NOT((SUBSTITUTE(
項目,F$1,"")=項目)))*數值)

複製儲存格F2，貼至儲存格F2:D13。

(1) MONTH(日期)=ROW(1:1)

在日期陣列中利用 MONTH 函數取出每個日期的月份，再和 ROW(1:1)比對是否相等，傳回 TRUE／FALSE 陣列。當公式向下複製時，ROW(1:1)=1→ROW(2:2)=2→ ... →ROW(12:12)=12。

(2) NOT((SUBSTITUTE(項目,F$1,"")=項目))

SUBSTITUTE(項目,F$1,"")：利用 SUBSTITUTE 函數將項目陣列儲存格中的內容消除儲存格F1(="A")的內容。(將儲存格F1(="A")的內容，以空字串(="")取代)

SUBSTITUTE(項目,F$1,"")=項目：若消除儲存格F1(="A")中的內容和原來儲存格相同，表示該儲存格中沒有含有儲存格F1(="A")的內容。

NOT((SUBSTITUTE(項目,F$1,"")=項目))：利用 NOT 函數取得項目儲存格中含有儲存格F1(="A")的內容者。

第(2)式會傳回一個 TRUE／FALSE 陣列。

最後，利用 SUMPRODUCT 函數將第(1)(2)式中的二個條件結果(TRUE／FALSE 陣列)和數值陣列執行乘積和，即為所求。