Excel-根據起迄日期列出姓名清單

如下圖,如何根據起日和迄日,列出姓名清單?以下提供二種方式供參考。

1. 使用 Excel 2021 版的 FILTER 函數

Excel-根據起迄日期列出姓名清單

選取儲存格A4:C25,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:姓名、上班起、上班迄。

儲存格D4:=FILTER(姓名,(D$3>=上班起)*(D$3<=上班迄))

複製儲存格D4,貼至儲存格D4:L4。

利用雙條件執行邏輯 AND 運算,再取出符合條件的姓名:

條件一:D$3>=上班起

條件二:D$3<=上班迄

 

2. 使用傳統陣列公式

Excel-根據起迄日期列出姓名清單

儲存格D4:

{=IFERROR(INDEX(姓名,SMALL(IF((D$3>=上班起)*(D$3<=上班迄),ROW(姓名),""),ROW(1:1))-3),"")}

複製儲存格D4,貼至儲存格D4:KL25。

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動加上「{}」。

(1) IF((D$3>=上班起)*(D$3<=上班迄),ROW(姓名),"")

利用雙條件執行邏輯 AND 運算,

條件一:D$3>=上班起

條件二:D$3<=上班迄

將符合者傳回姓名欄位的列號,否則傳回空字串。

(2) SMALL(第(1)式,,ROW(1:1))

利用 SMALL 函數於第(1)式的傳回值依序由小至大取出對應的列號。

ROW(1:1)=1,公式向下複製時,ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→…。

(3) INDEX(姓名,第(2)式-3)

將第(2)式的傳回值代入 INDEX 函數取得姓名欄位中對應的儲存格內容。

(4) IFERROR(第(3)式,"")

利用 IFERROR 函數將第(3)傳回的錯誤訊息以空字串顯示。

 

【參考資料】

FILTER 函數參考微軟提供的說明網頁:

https://support.microsoft.com/zh-tw/office/filter-函數-f4f7cb66-82eb-4767-8f7c-4877ad80c759

INDEX 函數參考微軟提供的說明網頁:

https://support.microsoft.com/zh-tw/office/index-函數-a5dcf0dd-996d-40a4-a822-b56b061328bd

OFFSET 函數參考微軟提供的說明網頁:

https://support.microsoft.com/zh-tw/office/offset-函數-c8de19ae-dd79-4b9b-a14e-b4d906d11b66

​ SMALL 函數參考微軟提供的說明網頁:

https://support.microsoft.com/zh-tw/office/small-函數-17da8222-7c82-42b2-961b-14c45384df07

ROW 函數參考微軟提供的說明網頁:

https://support.microsoft.com/zh-tw/office/row-函數-3a63b74a-c4d0-4093-b49a-e76eb49a6d8d

 

學不完.教不停.用不盡文章列表

    vincent 發表在 痞客邦 留言(0) 人氣()