☰

根据匹配的日期范围返回数据

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))- 仍然是一个数组公式。

相关内容