網友提問 Excel 的問題:在下圖上半部的資料清單,如何轉換為下半部的結果?

Excel-資料表重組(SUMPRODUCT,OFFSET,ROW)

 

【公式設計與解析】

儲存格B13:=IFERROR(OFFSET($B$1,SUMPRODUCT(($A$2:$A$9=$A13)*
($C$2:$F$9=B$12)*ROW($C$2:$F$9))-1,0),"")

複製儲存格B13,貼至儲存格B13:I17。

(1) ($A$2:$A$9=$A13)*($C$2:$F$9=B$12)

條件一:$A$2:$A$9=$A13

判斷儲存格A13和儲存格A2:A9陣列中的那一個相符,傳回 TRUE/FALSE 陣列。

條件二:$C$2:$F$9=B$12

判斷儲存格A12和儲存格C2:F9陣列中的那一個相符,傳回 TRUE/FALSE 陣列。

其中『*』運算子,相當於執行邏輯 AND 運算,會將 TRUE/FALSE 陣列轉換為 1/0 陣列。

Excel-資料表重組(SUMPRODUCT,OFFSET,ROW)

(2) SUMPRODUCT(第(1)式*ROW($C$2:$F$9))

SUMPRODUCT 函數將第(1)式的傳回結果乘以ROW(C2:F9),其結果會將第(1)式傳回 1 時,運算結果為對應的列號(2~9)。若第(1)式傳回 0 時,運算結果為 0。

Excel-資料表重組(SUMPRODUCT,OFFSET,ROW)

(3) OFFSET($B$1,第(1)式-1,0)

將第(2)式代入 OFFSET 函數,可以傳回儲存格B2:B9中的其中一個內容。

Excel-資料表重組(SUMPRODUCT,OFFSET,ROW)

(4) IFERROR(第(2)式,"")

當第(3)式傳回錯誤訊息時,以 IFFERROR 函數將其轉換為空白。

arrow
arrow
    文章標籤
    EXCEL SUMPRODUCT OFFSET ROW
    全站熱搜

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