學不完．教不停．用不盡

網友根據這篇：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 函數取出日期中的「時間」，比對後找出出院時間大於或等於結束時間者。