有網友根據下圖左 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 陣列)和數值陣列執行乘積和,即為所求。

arrow
arrow
    全站熱搜

    vincent 發表在 痞客邦 留言(0) 人氣()