2016年9月1日 星期四

Excel-計算多組項目中指定內容各月的個數和總和(SUMPRODUCT,SUBSTITUE,MOTH)

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

【公式設計與解析】
選取儲存格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 陣列)和數值陣列執行乘積和,即為所求。

沒有留言:

張貼留言

檢視其他文章

好康東東