2017年3月23日 星期四

Excel-垂直資料轉換為矩陣形式(SUMPRODUCT,OFFSET,COLUMN)

網友問到:在 Excel 工作表有一個如下圖的上方資料表,如何轉換為下方的資料表?
Excel-垂直資料轉換為矩陣形式(SUMPRODUCT,OFFSET,COLUMN)

【公式設計與解析】
選取儲存格C1:C15,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:入院日。

1. 計算每日人數
儲存格B18:=SUMPRODUCT((入院日=A18)*1)
複製儲存格B18,貼至儲存格B18:B24。
在 SUMPRODUCT 函數中的『*1』,用以將判斷式傳回的 TRUE/FALSE 陣列,轉換為 1/0 陣列。

2. 填入每日病歷號碼
儲存格C17:{=IFERROR(OFFSET($A$1,SMALL(IF(入院日=$A18,ROW(入院日),""),
COLUMN(A:A))-1,0),"")}
這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動加上「{}」。
複製儲存格B18,貼至儲存格B18:H24。
(1) IF(入院日=$A18,ROW(入院日),"")
在陣列公式中判斷入院日儲存格陣列是否和儲存格A18相同,若是,則傳回對應的列號,若否,則會空字串。
(2) SMALL(第(1)式,COLUMN(A:A))
根據第(1)傳回的列號,由小到大依序取出最小者。若COLUMN(A:A)往右方向複製時,則COLUMN(A:A)=1→COLUMN(B:B)=2→COLUMN(C:C)=3→...。
(3) OFFSET($A$1,第(2)式-1,0)
根據第(2)式傳回的列號,代入 OFFSET 函數取出對應的儲存格內容。
(4) IFERROR(第(3)式,"")
因為第(2)式可能傳回錯誤訊,所以令傳回值為錯誤訊息者,改顯示為空字串。

沒有留言:

張貼留言

檢視其他文章

好康東東