2018年5月4日 星期五

Excel-在多個日期的時間清單中找出每日最早和最晚時間所對應的人員(SUMPRODUCT,OFFSET,陣列公式)

根據這篇:
如果要延伸找到最早的人員和最晚的人員,該如何處理。
Excel-在多個日期的時間清單中找出每日最早和最晚時間所對應的人員(SUMPRODUCT,OFFSET,陣列公式)

【公式設計】
1. 求取最早和早晚的時間
儲存格F2:{=MIN(IF((日期=E2),打卡,""))}
儲存格H2:{=MAX(IF((日期=E2),打卡,""))}
以上是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動加上「{}」。

2. 求取最早和早晚的時間對應的人員
先來求取最早時間對應的人員
儲存格G2:{=OFFSET($B$1,SUMPRODUCT((日期=E2)*(打卡=MIN(IF((日期=E2),
打卡,"")))*ROW(人員))-1,0)}
以上是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動加上「{}」。
(1) MIN(IF((日期=E2),打卡,""))
在陣列公式中找到對應日期最早的時間。
(2) SUMPRODUCT((日期=E2)*(打卡=第(1)式)*ROW(人員))
在 SUMPRODUCT 函數中利用二個條件找尋完全相符者的列號:
日期=E2:找尋日期陣列中和儲存格E2相符者。
打卡=第(1)式:找尋打卡時間陣列中和第(1)式傳回值相同者。
在 SUMPRODUCT 函數中執行:((日期=E2)*(打卡=第(1)式)*ROW(人員),可以傳回符合者列號的和。(本例預設只會傳回一個符合的列號。若有二個打卡時間都是最早的,該公式會產生錯誤。)
(3) OFFSET($B$1,第(2)式-1,0)
利用 OFFSET 函數依第(2)傳回的列號求取符合的儲存格內容。
同理:
儲存格I2:{=OFFSET($B$1,SUMPRODUCT((日期=E2)*(打卡=MAX(IF((日期=E2),
打卡,"")))*ROW(人員))-1,0)}

沒有留言:

張貼留言

檢視其他文章

好康東東