☰

Excel 2010 - 需要根据过去 3 个月对动态范围求和

tag-icon • tag-icon microsoft-excel-2010 worksheet-function array
Excel 2010 - 需要根据过去 3 个月对动态范围求和

我正在尝试对特定月份前 3 个月和后 3 个月的条目列表的动态范围求和。我想我需要使用数组来获取它，但我找不到我需要做什么。

以下是我目前所拥有的矩阵格式：

ROW  |  COLUMN A  |  COLUMN B  |  COLUMN C  |  COLUMN D  |  COLUMN E
---  |  ------------------------------------------------------------
1    |  Username  |  StartMo   |  January   |  February  |  March
     |  ____________________________________________________________
2    |  User 1    |  March     |  1,000     |  1,500     |  1,000
3    |  User 2    |  April     |  2,000     |  1,000     |  1,500
4    |  User 3    |  April     |  1,000     |  1,750     |  1,000
  • A 列中的用户 1-10
  • B 列中的开始月份
  • CM 列显示过去一年的月度数据
  • 第 1 行的标题标明了数据的月份

我需要做的是，如果 A 列有我想要的用户，则求和，但前提是第 1 行在开始月份的 3 个月内。

有什么想法吗？我很想发布数据图片，但由于我是新手，所以不允许我这样做。

答案1

对于每一行，您需要对该行的子集范围求和，该范围由 B 列中的“起始月份”决定。让我们首先获取范围的文本描述，并将其公式化。为此，我们需要将月份名称转换为数字，即 1 到 12。我不知道如何直接做到这一点，但很容易从日期，我们可以通过附加数字（月份中的日期）将月份名称转换为日期。例如，

  • 如果B2是March，那么
  • B2 & "1"是March1，并且
  • MONTH(B2 & "1")是3。
  • 如果此功能在您的 Excel 版本中不起作用，请尝试MONTH(DATEVALUE(B2 & "1"))。

好的，问题要求计算月份总数月份数−3 至月份数+3。例如，对于用户 2（第 3 行），其中B3是“四月”（即 4），我们想要一月（1）到七月（7）的总和，因为 4−3=1 和 4+3=7。但有一个问题：并非所有所需的七个月份都能保证出现在工作表中。对于用户 1（第 2 行），B2是“三月”，所以我们想要十二月（上一年）到六月。上一年和下一年度的数据不可用，因此我们将范围截断为一月到六月。我们通过强制起始月份不小于 1 并且终止月份不大于 12 来实现此目的。

如果我们用一个单一的公式来做到这一点，那么它实际上将难以阅读。使用“辅助列”来存储中间值会更清楚。因此，设置

  • P2– 对应的月份数B2：
    =MONTH(B2 & "1")
  • Q2– 范围的起始月份：
    =MAX(P2-3, 1)
  • R2– 范围的结束月份：
    =MIN(P2+3, 12)

突出显示我们选择的单元格是一个巧妙的技巧，可以帮助我们验证我们正在做我们想要做的事情。使用公式设置条件格式

=AND((COLUMN())-2>=$Q2, (COLUMN()-2)<=$R2)

在我们的数据矩阵上，我们得到了：

部分答案 （该图像是指向其自身更大副本的链接。）

在 Excel 中生成动态范围的一个好工具是OFFSET功能：

抵消（参考，行，列，[高度], [宽度]）

它通过相对于其他单元格的位置来标识单元格的矩形区域（可能是单个单元格；即 1×1 矩形；可能更大）。例如，OFFSET(B2, 0, 1, , 6)标识范围C2:H2，因为

  • 从单元格开始，向下移动 0 行，向右移动 1 列，B2到达C2
  • 高度默认为 1，因此范围完全在第 2 行，并且
  • 宽度为6：C2，D2，E2，F2，G2和H2，或C2:H2简称 ，为六列。

嗯，范围的宽度就是结束列号减去起始列号再加 1。所以你想要的结果就是

=SUM(OFFSET(B2, 0, Q2, , R2-Q2+1))

我们可以把它放入 Column 中O：

完整答案

以下是以上内容的文本形式，您可以复制并粘贴：

Username StartMo January February March April May June July August September October November December The Answer Andy March 102 202 402 1002 2002 4002 10002 20002 40002 100002 200002 400002 =SUM(OFFSET(B2,0,Q2,,R2-Q2+1)) =MONTH(B2&"1") =MAX(P2-3,1) =MIN(P2+3,12) Bob April 103 203 403 1003 2003 4003 10003 20003 40003 100003 200003 400003 =SUM(OFFSET(B3,0,Q3,,R3-Q3+1)) =MONTH(B3&"1") =MAX(P3-3,1) =MIN(P3+3,12) Charlie April 104 204 404 1004 2004 4004 10004 20004 40004 100004 200004 400004 =SUM(OFFSET(B4,0,Q4,,R4-Q4+1)) =MONTH(B4&"1") =MAX(P4-3,1) =MIN(P4+3,12)

您可能需要将其作为文本粘贴到 Word 中，然后将其复制并粘贴到 Excel 中。

相关内容