时间戳

首页 > 解决方案 > 根据日期阈值对重复项的最大值求和

excel - 根据日期阈值对重复项的最大值求和

问题描述

我有以下数据列：

 Unique ID | Value | Date
     1     |   20  | 15/10/2018
     2     |   30  | 15/10/2018
     1     |   40  | 16/10/2018
     2     |   50  | 16/10/2018

然后我需要编写一个公式来根据日期阈值对 Value 列求和，但我只想包括每个唯一 ID 的最大值。

Date Threshold | Sum Formula
16/10/2018     | =SUMIF(C:C,"<="&F2,B:B)

显然，上面的公式将对所有值求和（140）。我只需要对每个唯一 ID (40+50=90) 小于或等于阈值日期的单个 MAX 值求和。

标签： excelexcel-formuladuplicatesmax

解决方案

步骤 1) 生成唯一的 ID 列表

创建唯一 ID 列表。有一些公式可以为您执行此操作，或者您可以输入它们。假设您的示例在 F2:F3 中编写 ID

步骤 2) 截止日期

将您的日期截止日期放在一个单元格中，比如说 E2。

步骤 3) 找到 MAX

我相信在 2016 年有一个 MAXIF 功能。使用它来根据唯一 ID 和 <= 截止日期提取最大值。MAXIF 是更好的公式，但不幸的是，我无法使用它，也无法指导您完成这里描述的更多内容。如果您没有 2016 年或访问 MAXIF，那么您可以使用聚合。聚合可以是执行类似数组操作的函数。因此，不要在其函数中使用完整的列引用，否则将进行过多的计算，这将使您的系统陷入困境或更糟。

=AGGREGATE(14,6,$B$2:$B$5/(($A$2:$A$5=$F2)*($C$2:$C$5<=$E$2)),1)

将该公式放入 G2 并在每个唯一 ID 旁边复制。然后它将在截止日期之前或等于截止日期之前提供该日期的最大值。

步骤 4) 对最大值求和

在未使用的列中，对上一个公式的结果求和

=SUM(G2:G3)

这假设您的第一个表是 A:C 列。它还假设您的日期采用 excel 序列日期格式，而不是伪装成 excel 日期的字符串。您可以使用=ISNUMBER(A1)A1 是带有日期的单元格来测试后者。false 值表示它是一个字符串，需要转换。调整范围以适合您的数据。

推荐阅读