excel - Sum Column 基于前 12 列的行条目(滚动计数)

Question

请看图片。我有一个包含多行的数据表(我正在使用的真实数据有 2,000 多行)。



我需要对每列中的行条目求和，但我需要将这些总和归为两个不同的类别:"new"和“返回”。新值从该值出现的第一个日期开始，然后在接下来的 12 个月内被视为新值(因此 2005 年 10 月 1 日 - 2006 年 10 月 1 日)。在最初的 12 个月窗口之后，该类型的任何值都被视为“返回”。但是，对于每种类型(岩石、纸张、剪刀)，第一个值出现的日期总是不同的，所以没有一个我可以测量的静态日期，对于每一行，第一个值可能出现在任何日期。

因此，即使一个值出现，然后在 9 个月后出现一个值，这两个值都是“新的”。只有在第一次发生后的 12 个月之后，它才会成为“回归”。

我显然可以手动执行此操作，但是超过 2,000 行可能需要很长时间并导致很多错误。任何解决方案都是有帮助的——条件格式、公式、VBA。

Answer 1

哇!! - 这对我来说是一个doozie，但它可以使用数组公式来完成。 (我想看看我能不能做到——不过，更好的解决方案肯定是在 VBA 中做到这一点)

我这样做的方式是，假设您设置的数据与您在示例图片中的完全一样 - 这样您的 10/1/2005数据将在 B 列中。
因此，要了解您每年将如何做，假设您正在处理 2/1/2007 的列。 (在这个例子中，它将在 R 列中。

所以，你的 返回 计算(在单元格 R6 中)如下所示:

=SUMPRODUCT(--(MMULT(IF(F2:Q4="",0,F2:Q4),TRANSPOSE({1,1,1,1,1,1,1,1,1,1,1,1}))>0),R2:R4)

请注意 这必须作为数组函数输入 (输入后使用 ctrl + shift + Enter )使其工作。

然后，您的 新 计算只是此列的总和减去您的返回。所以，在单元格 R5 ， 你将会拥有:

=SUM(R2:R4)-R6

(作为普通公式输入)。

作为一个简单的解释，您使用矩阵乘法将每行中的值乘以 1 并在每行的基础上将其相加( MMULT() 部分)，然后您只需检查它是否返回 >0 - 表示过去 12 个月该行中至少有一个值。

然后，如果有值，则将当前月份的值添加到 返回 sum (这是使用 SUMPRODUCT() 函数完成的)... 这给了你你需要的东西。

这样做的好处是它很容易适用于任意数量的数据行，所以你的情况应该是满意的，坏处是它会相当消耗处理器，但它会工作!!

我希望这至少可以帮助您前进!