2017年3月24日 星期五

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

如果想要調整為列出每日住院的病歷,該如何處理?
Excel-垂直資料轉換為矩陣形式(SUMPRODUCT,OFFSET,COLUMN)2
【公式設計與解析】
選取儲存格A1:D15,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:病歷號、入院日、出院日。
1. 計算每日人數
儲存格B18:=SUMPRODUCT((入院日<=$A18)*(出院日>=$A18))
複製儲存格B18,貼至儲存格B18:B33。
在陣列公式中判斷入院日儲存格陣列是否小於儲存格A18並且出院日大於儲存格A18,若是,則傳回TRUE,若否,則傳回FALSE。公式中的『*』運算,可以將TRUE/FASLE陣列轉換為1/0陣列。

2. 填入每日病歷號碼
{=IFERROR(OFFSET($A$1,SMALL(IF((入院日<=$A18)*(出院日>=$A18),ROW(病歷號),""),COLUMN(A:A))-1,0),"")}
這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動加上「{}」。
複製儲存格B18,貼至儲存格B18:M33。

沒有留言:

張貼留言

檢視其他文章

好康東東