☰

了解 Excel 中的时间/日期

tag-icon • tag-icon microsoft-excel google-spreadsheets
了解 Excel 中的时间/日期

我在 Excel 中有一张简单的数据表，我需要一个公式来计算今天的总金额。因此，它将是 TODAY() 日期 00:00 到 23:59 之间的所有金额的总和。我该怎么做？

为了（可能）使事情稍微复杂一点，我无法在源电子表格中添加任何列（下图）。所有数学运算都在单独的工作表中完成。因此，我无法在每一行中添加另一列来添加一些辅助数字/中间步骤。理想情况下，所有这些都应该是一个大公式，我可以将其插入单独工作表的一个单元格中。

示例数据：

在此处输入图片描述

答案1

首先要弄清楚的是，您的日期是文本还是 Excel 格式化为日期的数字。这是一个非常重要的区别，您需要后者，因为它允许您使用更多 Excel 内置公式。一些可能的测试是

  • =ISTEXT(A2)
  • =ISNUMBER(A2)
  • 将单元格格式改为常规，看看显示的内容是否发生变化

为了解决这个问题，您的日期将被假定为数字。如果不是，有很多关于如何将文本日期转换为数字/excel 格式的问题/答案。

了解 Excel 中的时间/日期

Excel 将日期存储为整数。它是自 1900 年 1 月 0 日以来的天数计数。因此 1 表示 1900 年 1 月 1 日。（我相信参考年份是 1905 年，但我可能错了）。因此，excel 中的所有日期都只是整数，并应用了一些特殊格式，以便以我们习惯的方式显示它们。另一方面，时间以一天的百分比或如果您喜欢的话，以一天的小数形式存储。0.5 代表半天或午餐。因此，如果您只需要从混合时间日期中查找日期，则只需去掉整数部分。相反，如果您只需要时间，则只需去掉小数部分。

选项 1 SUMPRODUCT

SUMPRODUCT是一个执行类似数组计算的常规函数​​。因此，您需要将范围引用限制为大致的数据，而不是使用完整的列/行引用。如果您最终使用后者，您的机器可能会因对空单元格进行过多无用的计算而陷入瘫痪。

=SUMPRODUCT((INT($A$2:$A$12)=TODAY())*$B$2:$B$12)

注意TODAY()，这是一个易失性函数。这意味着，每次工作表上的内容发生变化时，即使不影响该函数，它也会重新计算。非易失性函数（大多数常规函数）仅在发生影响它们的更改时才重新计算。因此，如果您将易失性函数复制到许多单元格，则每次工作表上的内容发生更改/输入时，您的计算机都可能会陷入瘫痪。

选项 2 - SUMIFS

SUMIFS是一个常规公式，您可以放心使用完整的列引用，这是我处理这种情况的首选方案。此公式的形式为：

SUMIFS(Range to be summed, range for condition 1, Condition 1 check, ..., range for condition n, Condition n check)

因此，在您的情况下，您想要检查一天的开始和结束时间，因此您的公式必须进行条件检查，并且最终应该看起来像下面这样。

=SUMIFS($B$2:$B$12,$A$2:$A$12,">="&TODAY(),$A$2:$A$12,"<"&TODAY()+1)

相关内容