Excel 查詢值的日期範圍

Question

嘗試查詢 Excel 文件以確定某個值出現的次數，以便我可以在熱圖上新增條件格式，以確定某個日期期間資源繁忙的次數。

我在各個單元格中有 Jan1-Dec31 我需要查詢分成三列的數據，如下所示

       A       B       C
      Jan1    Jan12   Bob
      Jan2    Jan10   Roger
      Jan11   Jan14   Bob

此公式需要使用 A 列中的開始日期和 B 列中的結束日期，並計算 C 列中資源名稱的出現次數。

所以我的輸出將進入我的熱圖，如下所示。

        Bob  Roger
 Jan1    1    0

 Jan2    1    1

 Jan3    1    1

 Jan4    1    1

 Jan5    1    1

 Jan6    1    1

 Jan7    1    1

 Jan8    1    1

 Jan9    1    1

 Jan10   1    1

 Jan11   2    0

 Jan12   2    0

 Jan13   1    0

 Jan14   1    0

如果可以擴展而無需為每種資源製作 365 個公式，那就太好了。

Answer 1

我思考我正確理解你的問題。我的解釋是，您正在尋找一種方法來擴展您的“熱圖”輸入，但我可能讀錯了。如果我弄錯了，請告訴我。

這是 SUMProduct 的一個經典案例。

=SUMPRODUCT(--($E2>=$A$2:$A$4)*($E2<=$B$2:$B$4)*(F$1=$C$2:$C$4))

您可以上下複製（見圖）。顯然，您需要相應地編輯範圍以適合您的真實數據。

它的工作原理是這樣的。考慮鮑伯的 2016 年 1 月 1 日。它會取得日期並將該日期與開始日期進行比較，並根據該日期是否大於或等於開始日期陣列中的開始日期建立 true/false 陣列。然後它執行相同的操作，小於或等於結束日期數組中的結束日期。然後它檢查 Bob 的資源數組。最後，你有三個陣列：

{真、假、假} * {真、真、真} * {真、假、真} -> {真、假、假}。 * 是 AND 運算符，因此任何出現 TRUE、TRUE、TRUE 的地方都是 1，如果出現 FALSE 則為零。然後，它將 TRUE、FALSE、FALSE 陣列強制為 1、0、0 並對結果求和！

編輯：這是解決我們在評論中討論的問題的方法。