2017年3月9日 星期四

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

網友提問 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 函數將其轉換為空白。

沒有留言:

張貼留言

檢視其他文章

好康東東