2015年10月31日 星期六

Excel-在月曆型式中顯示排班結果(SUMPRODUCT,OFFSET,DATE,ROW)

有網友想要將一個 Excel 的排班清單(下圖右)直接在一個月曆中顯示(下圖右),該如何處理?
Excel-在月曆型式中顯示排班結果(SUMPRODUCT,OFFSET,DATE,ROW)

【公式設計與解析】
先將日期範圍內的儲存格定義名稱為:日期。
以下以 2015/10/4 為例:
儲存格A8:="[早]"&OFFSET($J$2,SUMPRODUCT((日期=DATE($A$1,$G$1,A7))*
ROW(日期))-2,,,)
DATE($A$1,$G$1,A7):取得每個儲存格所代表的日期。
SUMPRODUCT((日期=DATE($A$1,$G$1,A7))*ROW(日期)):取得儲存格A8所代表日期在資料清單中的『列號』。
OFFSET($J$2,SUMPRODUCT((日期=DATE($A$1,$G$1,A7))*ROW(日期))-2,,,):依據上式的『列號』,代入 OFFSET 函數查得J欄中對應的儲存格內容。
同理:
儲存格A9:="[中]"&OFFSET($K$2,SUMPRODUCT((日期=DATE($A$1,$G$1,A7))*
ROW(日期))-2,,,)
儲存格A10:="[晚]"&OFFSET($L$2,SUMPRODUCT((日期=DATE($A$1,$G$1,A7))*
ROW(日期))-2,,,)
複製儲存格A8:A10,貼至所有日期的儲存格中。

【延伸處理】
如果在排班表中不是每個日期都有排班,則可以修改公式:
儲存格A8:=IFERROR(原公式,"")
即:
儲存格A8:=IFFERROR("[早]"&OFFSET($J$2,SUMPRODUCT((日期=
DATE($A$1,$G$1,A7))*ROW(日期))-2,,,),"")

沒有留言:

張貼留言

檢視其他文章

好康東東