☰

Excel：在日期和时间列表中查找每个日期的最早/最新值

tag-icon • tag-icon microsoft-excel microsoft-excel-2016
Excel：在日期和时间列表中查找每个日期的最早/最新值

我有如下连续到达和离开时间的列表：

| IN/OUT | DATE/TIME             |
|--------|-----------------------|
| IN     | 1/2/2019  8:25:59 AM  |
| OUT    | 1/2/2019  11:51:59 AM |
| IN     | 1/2/2019  12:48:59 PM |
| OUT    | 1/2/2019  4:45:59 PM  |
| IN     | 1/3/2019  9:08:59 AM  |
| OUT    | 1/3/2019  12:38:59 PM |
| IN     | 1/3/2019  3:23:59 PM  |
| OUT    | 1/3/2019  4:59:59 PM  |
| IN     | 1/4/2019  8:49:59 AM  |
| OUT    | 1/4/2019  5:11:59 PM  |

我需要从该列表中提取第一的和最后的每天的条目（然后用于计算平均到达和离开时间、持续时间等）。

例如，我想从上表中提取以下内容：

| IN/OUT | DATE/TIME             |
|--------|-----------------------|
| IN     | 1/2/2019  8:25:59 AM  |
| OUT    | 1/2/2019  4:45:59 PM  |
| IN     | 1/3/2019  9:08:59 AM  |
| OUT    | 1/3/2019  4:59:59 PM  |
| IN     | 1/4/2019  8:49:59 AM  |
| OUT    | 1/4/2019  5:11:59 PM  |

我知道我可以使用 VLOOKUP 来获取所有“IN”或“OUT”值，但问题是列表中的大多数日子都有多个值。我只需要第一个和最后一个，但我不知道该怎么做。

有没有办法使用 VLOOKUP 来实现这一点，无论是否使用辅助表？或者也许另一种方法可能效果更好？

答案1

1. 记得先搜索再询问

在 Excel 中使用 MAX() 针对使用多个条件的日期列表进行计算

参见‘相关’部分？

相关查询

2. MIN/ MAXCSE 食谱

样本图像

MIN对于“IN”：

=MIN(IF($A$2:$A$99=F2,IF($B$2:$B$99=G2,$C$2:$C$99)))

这就是他们所说的数组或“CSE”公式 - 将其输入到公式栏中，然后按Ctrl Shift Enter将其作为数组公式输入，然后向下复制。

这很有道理，对吧？从匹配和匹配C中获取最低值。FAGB

但这只能获取最小 IN 值，而每隔一行交换 可获取最大 OUT 值...但是谁有时间做这些呢？让我们用一个简单的方法将它们组合MAX在一起：MINIF

3. 最终公式

最终公式

=IF(F2="IN",MIN(IF($A$2:$A$99=F2,IF($B$2:$B$99=G2,$C$2:$C$99))),MAX(IF($A$2:$A$99=F2,IF($B$2:$B$99=G2,$C$2:$C$99))))

再次，在 F2 中输入该公式，按下Ctrl Shift Enter，然后向下复制。

注意：

  1. 根据需要调整单元格引用
  2. 您没有明确说明数据输入的形状；我使用 Excel 的“文本到列”功能分离每个参数，以便可以独立操作日期和时间。
  3. 不包含任何错误捕获，解决方案假设您已经设置了所需的测试值（如图所示）。这应该很容易复制输入的 IN/OUT 和 DATE 列，并使用“删除重复项”生成匹配的唯一列表。
  4. 我在列中的内容没有特殊格式C。它只是如图所示的简单文本。您可能需要使用格式刷（摘自该栏目）在您的最终输出列上！
  5. 忽略我的列D，我在整理这些内容时将其用作简单的错误检查，但在截取屏幕截图之前忘记删除它。

相关内容