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

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

Excel-計算日期區間裡每天的人數

【設計與解析】

選取儲存格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 陣列加總,即為所得。

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

arrow
arrow
    文章標籤
    Excel 動態陣列
    全站熱搜

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