學不完．教不停．用不盡

網友問到：如下圖，如何計算日期區間裡每天的人數。

在下圖中有一個人員清單，每個人員都有一個起始、結束的日期，例如：計算每天公出（請假）的人數、飯店每天的住宿人數等。

【設計與解析】

選取儲存格B4:C14，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：起始、結束。

1. 列出日期

儲存格E4：=ROW(INDIRECT(MIN(起始)&":"&MAX(結束)))

公式會自動溢出至其他儲存格。

(1) MIN(起始)

找到起始日期的最小值，傳回一個數值。

(2) MAX(結束)

找到結束日期的最大值，傳回一個數值。

(3) INDIRECT(MIN(起始)&":"&MAX(結束))

將第(1)式和第(2)式傳回的日期組成：MIN(起始)&":"&MAX(結束)。

再透過 INDIRECT 函數轉成位址。

(4) ROW(第(3)式)

將第(3)式的位址代入 ROW 函數中即可列日期由小至大的清單。

2. 計算各天人數

儲存格F4 ：=SUMPRODUCT((E4>=起始)*(E4<=結束))

複製儲存格F4，貼至儲存格F4:F16。

本例利用 SUMPRODUCT 函數計算合於雙條件的數量：

雙條件：(E4>=起始)*(E4<=結束)

其「*」運算子相當於執行邏輯 AND 的運算，會傳回 TRUE／FALSE 陣列並轉換為 1／0 陣列。

再由 SUMPRODUCT 函數予以將 1／0 陣列加總，即為所得。