☰

如何比较 Excel 序列日期而不转换为 mm/dd/yy 类型的日期？

tag-icon • tag-icon microsoft-excel worksheet-function date-time
如何比较 Excel 序列日期而不转换为 mm/dd/yy 类型的日期？

我有一张表，其中包含许多表示 Excel 序列日期的值。经过多次尝试比较字段失败后，我目前的方法是比较序列日期而不是日历日期。我试图用公式按天汇总数据。

考虑：

41021       some data
41021.625   some data
41021.63542 some data
41022       some data
41022.26042 some data
41022.91667 some data
41023       some data
41023.375   some data

期望结果：

41021       sum of 41021, 41021.625 and 41021.63542 data
41022       sum of 41022, 41022.26042 and 41022.91667 data
41023       sum of 41023 and 41023.375 data

本质上，对于 SerialDate.SerialTime 的所有实例，与 SerialDate.* 关联的 SUM 数据值，而不管该日期的 *.SerialTime 是什么。

虽然我可以看到如何通过创建额外的日期列格式来做到这一点，但=TEXT(<DateField>,"mm/dd/yyyy")我正在寻找一种解决方案，使我能够处理公式中的这种“转换”，例如SUMIF((TEXT(<dateRange>,"yy/mm/dd"),=(TEXT(<dateField,"yy/mm/dd")),<dataRange>

有道理吗？有人有什么想法吗？

谢谢

答案1

假设你的序列时间在A列，相关数据在B列，则以下公式

=IF(INT($A1)=$A1,SUMIFS($B:$B,$A:$A,">="&$A1,$A:$A,"<"$A1+1),"")

将在 C1 中输入并扩展到列时，在 C 列中显示第一个整数时间值后面的总和。如果这就是您所需要的，那么您就完成了。

但是，如果你需要将所有总数一个接一个地放在一起，中间没有空行，那么你必须使用数组公式并插入一个额外的列作为参考标签。在 C 列中，选择包含数据的行中的单元格（即，如果您的数据填充了 1 到 100 行，请选择 C1 到 C100），然后在公式栏中插入以下公式（不要直接在单元格中插入！）：

=IFERROR(SMALL(IF(INT($A1:$A100)=$A1:$A100,$A1:$A100,""),ROW()),"")

请注意，要使其正常工作，您需要将其保存为数组公式通过使用Ctrl+Shift+Enter（不是Enter- 如果您输入正确，公式将显示在括号中）确认您的公式输入。您还必须根据需要调整范围，因为公式对空单元格返回 0。最后，请注意数组公式在大型数据集上会变得相当慢。ooo...o...o...w。

完成后，您可以添加

=IF($C1<>"",SUMIFS($B:$B,$A:$A,">="&$A1,$A:$A,"<"$A1+1),"")

到 D 列，你就得到了：

数组公式的实际应用

答案2

如果您尝试按天汇总数据（或获取每日小计），则可以尝试此数组公式。在公式栏中输入此公式，然后按Ctrl+ Shift+ Enter。：

=SUM((INT(datetimes)=D3)*somedata)

在哪里

  • datetimes包含您的序列日期，采用General或mm/dd/yyyy h:mm格式
  • somedata包含一些要添加的数字（下面示例中的 B2:B25）

在此处输入图片描述

Excel 将日期视为整数（序列值），将时间视为小数（24 小时的分数）。例如，06/01/2012 3:00:00 PM等于41061.625（如果您将单元格格式更改为General），您将看到此值）。整数表示日期，而0.625表示3:00 pm或15:00或15/24。要提取 DATE 段，您可以使用INT(A1)。

答案3

最简单的方法是使用 Excel（2007/2010）内置表格和数据透视表功能。

  1. 利用您的数据创建一个 Excel 表。
  2. 使用 Excel 的“INT”函数为日期创建一个辅助列，该函数会将所有值向下滚动到相应的日期（日期/时间字段的整数部分）。您还可以轻松地为字段的时间值创建一个辅助列。
  3. 使用数据表作为源创建数据透视表。
  4. 将行标签设置为辅助日期，将数据透视表值设置为数据点（并且可以将时间字段添加为列标签）。

然后，每当您更新表格时，您都可以刷新数据透视表以获取当前小计。作为奖励，您还可以选择任何其他聚合函数（最小值、最大值、平均值），并且可以选择以传统日期格式格式化数据透视表的标签，而不会影响数据表值。

如果您使用的是 Excel 2003，则可以使用列表功能（而不是表格）完成相同的操作。

示例 Excel 2010

Excel 2010

示例 Excel 2003

Excel 2003

相关内容