☰

查找第三个先前事件的日期

tag-icon • tag-icon microsoft-excel worksheet-function
查找第三个先前事件的日期

基本上，该公式需要找到一项任务的最近 3 次完成，然后输出这 3 次完成中最早完成的日期。也就是说，每个任务第三最近完成的日期。

如果我的解释不清楚的话，这里有一个例子：

Date    | Task   | Count of Task
--------------------------
6/30/18 | Task A | 2
6/30/18 | Task B | 3
7/01/18 | Task A | 2
7/02/18 | Task B | 1
7/03/18 | Task B | 1
7/03/18 | Task B | 1
7/05/18 | Task A | 2
7/09/18 | Task C | 7

该公式需要返回以下内容：

Task A: 7/1/18
Task B: 7/2/18
Task C: 7/9/18

任务 A：7 月 5 日（最近）完成了 2 项任务，因此第 3 项任务在 7 月 1 日完成。
任务 B：7 月 3 日（最近）完成了 2 项任务，因此第 3 项任务在 7 月 2 日完成。
任务 C：所有任务都在同一天完成，因此第 3 项任务在 7 月 9 日完成。

公式需要能够处理在同一日期具有同一任务的两行独立数据，因为数据集包含这样的条目。

我认为解决方案将涉及 Vlookup 和 sumif 的某种组合，但这超出了我目前的技能范围。

答案1

在此处输入图片描述

在单元格中C10写入此数组公式，以Ctrl+Shift+Enter& 填充结束。

{=MIN(IF($B$3:$B$8=A10,$A$3:$A$8))}

答案2

  1. 创建数据透视表。

    • 突出显示数据。
    • 转到插入 > 表格 > 数据透视表。
    • 选择放置桌子的位置。
    • 检查日期、任务和数量。
    • 将“日期”拖到“行”，将“任务”拖到“列”，将“计数”拖到“值”。然后选择“计数总和”（如果尚未选择）。

  2. 按降序对日期（行标签）进行排序。

  3. 创建累积和：在 F3 中，输入公式=SUM(B$3:B3)。并延伸至 H8。
  4. 创建布尔值来指示总和何时至少为 3：在 I3 中，输入公式=F3>=3。并扩展到 K8。
  5. 重复日期，因为 VLOOKUP 要求查找在右侧进行：在 L3 中，输入公式=$A3。并延伸到 N8。
  6. 创建 VLOOKUP。在 I9 中，输入公式=VLOOKUP(TRUE, I3:L8,4,FALSE)。扩展到 K9

答案在I9到K9。

此解决方案占用许多单元格，但设置起来很简单。请注意，无需将步骤 5 中的公式扩展到 N8，只需将 J9 中的 VLOOKUP 更改为引用第 3 列，将 K9 中的 VLOOKUP 更改为引用第 2 列。

带有值的电子表格：

带值的电子表格

带有公式的电子表格：

带公式的电子表格

答案3

这是我之前的回答和 Rajesh S 的回答的结合。

  1. 针对每个任务创建累积总和：在单元格中D2写入=SUMIF($B2:B$9, B2, $C2:C$9) >= 3并填写至单元格C9。

  2. TRUE查找步骤 1 中标记的每个任务的最大日期：在单元格中D11写入以下数组公式（然后按Ctrl+Shift+Enter）：

    =MAX(如果(($B$2:$B$9=B11)*($D$2:$D$9),$A$2:$A$9))

  3. 将其填充至 D13。

备注：为了使解决方案有效，任务必须按日期升序排列。

带公式的电子表格

相关内容