![Excel:在日期和时间列表中查找每个日期的最早/最新值](https://linux22.com/image/1585458/Excel%EF%BC%9A%E5%9C%A8%E6%97%A5%E6%9C%9F%E5%92%8C%E6%97%B6%E9%97%B4%E5%88%97%E8%A1%A8%E4%B8%AD%E6%9F%A5%E6%89%BE%E6%AF%8F%E4%B8%AA%E6%97%A5%E6%9C%9F%E7%9A%84%E6%9C%80%E6%97%A9%2F%E6%9C%80%E6%96%B0%E5%80%BC.png)
我有如下连续到达和离开时间的列表:
| 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
/ MAX
CSE 食谱
MIN
对于“IN”:
=MIN(IF($A$2:$A$99=F2,IF($B$2:$B$99=G2,$C$2:$C$99)))
这就是他们所说的数组或“CSE”公式 - 将其输入到公式栏中,然后按Ctrl Shift Enter将其作为数组公式输入,然后向下复制。
这很有道理,对吧?从匹配和匹配C
中获取最低值。F
A
G
B
但这只能获取最小 IN 值,而每隔一行交换 可获取最大 OUT 值...但是谁有时间做这些呢?让我们用一个简单的方法将它们组合MAX
在一起:MIN
IF
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,然后向下复制。
注意:
- 根据需要调整单元格引用
- 您没有明确说明数据输入的形状;我使用 Excel 的“文本到列”功能分离每个参数,以便可以独立操作日期和时间。
- 不包含任何错误捕获,解决方案假设您已经设置了所需的测试值(如图所示)。这应该很容易复制输入的 IN/OUT 和 DATE 列,并使用“删除重复项”生成匹配的唯一列表。
- 我在列中的内容没有特殊格式
C
。它只是如图所示的简单文本。您可能需要使用格式刷(摘自该栏目)在您的最终输出列上! - 忽略我的列
D
,我在整理这些内容时将其用作简单的错误检查,但在截取屏幕截图之前忘记删除它。