2017年12月8日 星期五

Excel-根據同一欄有資料的儲存格對應標題依序顯示(OFFSET,COLUMN)

(網友提問)在 Excel 的工作表有下圖(上)的資料表,如何轉換為下圖(下)的資料表。
參考下圖,在每個姓名列中,要將有數字的儲存格,對照日期名稱後依序列在同一列中。
Excel-根據同一欄有資料的儲存格對應標題依序顯示(OFFSET,COLUMN)

【公式設計解析】
儲存格B13:{=IFERROR(OFFSET($B$1,0,SMALL(IF($B2:$K2<>"",
COLUMN($B2:$K2),""),COLUMN(A:A))-2),"")}
這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動加「{}」。
複製儲存格B13,貼至儲存格B13:K20。
(1) IF($B2:$K2<>"",COLUMN($B2:$K2),"")
判斷儲存格B2:K2的內容中,如果不是空白內容,則傳回所在的欄號,否則傳回空字串。
(2) SMALL(第(1)式,COLUMN(A:A))
根據第(1)式傳回的欄號,利用 SMALL 函數由小至小依序取出。當公式向右複製時,COLUMN(A:A)=1→COLUMN(B:B)=2→COLUMN(C:C)=3→...。
(3) OFFSET($B$1,0,第(2)式-2)
根據第(2)式取出的欄號,代入 OFFSET 函數中,取出對應的儲存格內容。
(4) IFERROR(第(3)式,"")
當公式向右複製時,第(2)式可能會傳回錯誤訊息,所以利用 IFERROR 函數將錯誤訊息顯示為空白。

沒有留言:

張貼留言

檢視其他文章

好康東東