Excel:我可以用计算来做 sumif 吗?

Excel:我可以用计算来做 sumif 吗?

我正在 Excel 中对一长串列表进行重新计算,以便为其他程序中的统计分析做准备,但是我在做一件事时遇到了麻烦。

我有一些变量的列表,其中包括一个标识符、一个日期变量和一个数值变量。我需要添加一个变量,该变量将位于具有相同标识符的实例 365 天内的所有实例的数值变量相加。使用 sumifs() 可以相对轻松地完成此操作。但我还想为数值添加权重,以便日期值接近今天值的实例比时间更早的实例具有更高的权重(昨天 364/365 权重、一个月前的 335/365、明天一年前的 1/365 等)。

有没有办法用函数来实现这个功能?

#     A         B           C         D
1    ID#    datenumber    value    newvar
2    Ken    01-01-2015    1000     somevalue
3    Ida    01-01-2014    1000     somevalue
4    Ida    01-06-2015    1234     somevalue
5    Ida    01-10-2015    5678     somevalue
6    Ida    01-12-2015    1000     y


y = weighted sum of all values of all Ida within one year, line 3 and 4 above.

The newvar sumifs y would be (if "ID#" was in cell A1):

=SUMIFA(C:C,A:A,A6,B:B,"<"&B6,B:B,">"&B6-365) or 1234+5678 = 6912

The "weighted sumifs" would be y = (182/365*1234)+(304/365*5678) = 5344.38

我想我可以创建一个新列,其中包含特定日期的加权值,对具有该日期数字的所有实例执行 newvar,复制粘贴值,然后将日期向上移动一天,然后使用具有该日期值的实例重复此操作等,通过 VBA 但这种黑客行为确实会限制工作簿在其他领域的可用性。所以我希望有人有一个使用单元格函数而不是 VBA 的解决方案。

答案1

妈的,这比我想象的要简单多了。Sumproduct 允许你在将各个值相加之前对其进行类似数组的操作

=SUMPRODUCT((A2:A6=A6)*(B2:B6<B6)*(B2:B6>B6-365)*C2:C6*((B2:B6)-(B6-365))/365)

(A2:A6=A6)*(B2:B6<B6)*(B2:B6>B6-365) 是条件

C2:C6 是要考虑的值(如果它们满足上述条件)

((B2:B6)-(B6-365))/365 是要添加到满足条件的值的权重

相关内容