網友根據這篇：Excel-根據日期區間計算每天人數(SUMPRODUCT)
參考下圖，網友問到如果要根據日期時間區間，計算特定時段內的人數，該如何處理？
【公式設計與解析】
1. 定義儲存格範圍名稱
(1) 將儲存格C2:C27，設定名稱：「入院」。
(2) 將儲存格D2:D27，設定名稱：「出院」。
(3) 將儲存格F2，設定名稱：「開始」。
(4) 將儲存格F5，設定名稱：「結束」。
2. 設定公式
(1) SUMPRODUCT((入院<=開始)*(出院>=結束))
(2) SUMPRODUCT((INT(入院)=INT(開始))*(MOD(入院,1)>=MOD(開始,1)))
(3) SUMPRODUCT((INT(出院)=INT(結束))*(MOD(出院,1)<=MOD(結束,1)))
儲存格F8：=第(1)式+第(2)式+第(3)式
(1) 公式：=SUMPRODUCT((入院<=開始)*(出院>=結束))
利用 SUMPRODUCT 函數依雙條件，將符合條件者予以加總。
條件一：入院<=開始，在所有入院日期陣列中找出小於或等於開始日期者。
條件二：出院>=結束，在所有出院日期陣列中找出大於或等於結束日期者。
公式：(入院<=開始)*(出院>=結束)，其中「*」相當於執邏輯 AND 運算。
(2) 公式：SUMPRODUCT((INT(入院)=INT(開始))*(MOD(入院,1)>=MOD(開始,1)))
因為第(1)中的條件：(入院<=開始)*(出院>=結束)，會將住院日期和開始時間同一天，但是時間晚於開始時間者不列入計算。所以用此式微調。
★ INT(入院)=INT(開始)：
以 INT 函數取出日期中的「日期」，比對後，找出入院日期和開始日期相同者。
★ MOD(入院,1)>=MOD(開始,1)：
以 MOD 函數取出日期中的「時間」，比對後找出入院時間小於或等於開始時間者。
(3) 公式：SUMPRODUCT((INT(出院)=INT(結束))*(MOD(出院,1)<=MOD(結束,1)))
因為第(1)中的條件：(入院<=開始)*(出院>=結束)，會將出院日期和結束時間同一天，但是時間早於結束時間者不列入計算。所以用此式微調。
★ INT(出院)=INT(結束)：
以 INT 函數取出日期中的「日期」，比對後，找出出院日期和結束日期相同者。
★ MOD(出院,1)>=MOD(結束,1)：
以 MOD 函數取出日期中的「時間」，比對後找出出院時間大於或等於結束時間者。
留言列表