☰

根據匹配的日期範圍傳回數據

tag-icon • tag-icon microsoft-excel worksheet-function
根據匹配的日期範圍傳回數據

我們小組有一個 Excel 工作表，用於追蹤輪調職責。一個簡單的例子如下：

  • A 欄：開始具有定義每個行項目所代表的時間段開始的日期。
  • B 欄：結尾具有定義每個行項目所代表的時間段結束的日期。
  • C 欄：受讓人具有表示在同一行上 START 和 END 定義的時間段內指派給給定職責的人員的字串。

列表本身是通常按開始日期升序排列。但是，工作表最終可能會重新排列。

清單中定義的時間段之間不應有任何重疊。

我想添加的是包含三個值的資訊部分（在單獨的單元格中）：

  • 以前的將是與當前時間段之前的時間段相對應的受讓人。
  • 目前的將是當前負責的受讓人。
  • 下一個將是與我們目前所處時間段之後的時間段相對應的受讓人。

假設電子表格保持正確的順序（按 START 升序），在計算出 CURRENT 的公式後，返回 PREVIOUS 和 NEXT 應該很容易。但我甚至不確定從哪裡開始。

答案1

我思考這將有助於找到電流。我已經在一定程度上測試過它，但是我猜測在這種情況下，忽略一個特徵並不是問題。

首先，在 B 和 C 之間新增一列（受讓人從此變為 D）。在此列中，輸入以下公式：

=IF(NOW()-A2>0,IF(NOW()-B2<0,"Yes","No"),"No")

這應該產生一行為“是”，其餘行為“否”（對於當前插槽）。

現在，在目前受讓人的儲存格中輸入：

=INDEX($C$2:$D$4, MATCH("Yes", $C$2:$C$4,0), 2)

對於上一個： =INDEX($C$2:$D$4, MATCH("Yes", $C$2:$C$4,0) - 1, 2)
對於下一個： =INDEX($C$2:$D$4, MATCH("Yes", $C$2:$C$4,0) + 1, 2)

通常，MATCH 希望對搜尋列進行排序，但因為我們應該在該列中只包含一個“是”，所以我思考（這是猜測部分）我們可以忽略該限制。

我的測試電子表格只有 3 行，所以 YMMV。

當目前是第一行等時，您將需要對前一個進行一些錯誤檢查，並且命名您的來源範圍可能是一個好主意。

您可能還想隱藏額外的列。

答案2

有序輸入：

目前受讓人：currRow=match(now(),A:A,1)- 當您的資料被排序時，這將找到目前行，不需要輔助列。將計數單元格的名稱設為 currRow 只是為了更容易引用。
受讓人：=indirect("C"& currRow + x)- x: -1, 0, 1 分別代表前一行、目前行和下一行。

無序輸入：

目前行：currRow=match(max(if(A:A>now(),"",A:A)),A:A,0)- 這是一個陣列公式，因此您需要使用 CTRL+SHIFT+ENTER 輸入。
目前受讓人：與以前相同的公式。
上一篇：=indirect("C" & match(max(if(A:A>=indirect("A" & currRow),"",A:A)),A:A,0))- 也是陣列公式。
下一步：=indirect("C" & match(min(if(A:A<=indirect("A" & currRow),"",A:A)),A:A,0))- 仍然是一個陣列公式。

相關內容