Excel公式对非重叠单元格范围求和？

Question

假设我在 A1 到 A20 中有数字，我想对 A 列中 5 个单元格的非重叠范围求和，并将结果存储在 E 列的单元格中，它看起来像这样(如果将公式输入到每个单元格中)手动E柱单元):

E1 = SUM(A1:A5)
E2 = SUM(A6:A10)
E3 = SUM(A11:A15)
E4 = SUM(A16:A20)

但是，我不想手动将公式输入 E 单元格。我希望能够选择 E1 中的公式并将其拖动到 E4，同时保持 A 中 5 个单元格的不重叠范围!默认情况下，Excel 不会给我这种行为，而是这样做:

E1 = SUM(A1:A5)
E2 = SUM(A2:A6)
E2 = SUM(A3:A7)
E4 = SUM(A4:A8)

看看每个 SUM() 函数中 5 个单元格的范围如何重叠？例如A1:A5 和 A2:A6。这不是我想要的。

那么，什么公式可以让我做到这一点？基本上，以下伪代码可以工作，但我似乎无法在 Excel 中实现类似的东西:

SUM(CELL(COLUMN, (CURRENT_ROW - 1) * 5 + 1):CELL(COLUMN, (CURRENT_ROW - 1) * 5 + 5))

例如，对于 E2，CURRENT_ROW = 2，这意味着它看起来像这样:

SUM(CELL(A, (2 - 1) * 5 + 1):CELL(A, (2 - 1) * 5 + 5))
= SUM(CELL(A, 6):CELL(A, 10))

此伪代码假设 CELL 具有方法签名 CELL(row, column)。

Answer 1

关键是 OFFSET 函数。 Offset 采用一个起始单元格、一些行、列以及可选的高度和宽度来动态生成对单元格/范围的引用。诀窍是使用其他函数来生成行偏移和其他参数。

我刚刚拼凑了一些似乎可以满足您要求的东西，我会看看是否可以解释它......

A 列:整数 (A1) 0、(A2) 1、(A3) 2、3、4、5 等...这是您要偏移的索引，您可以使用以下命令删除它类似于 ROW 函数)D 列(或任何其他任意位置):您想要求和的数字，从 D1 开始 - 我实际上只是使用了 (D1) 1, (D2) 2,3,4,5...

B 列是您感兴趣的列，公式为:

=SUM(OFFSET(D$1,A1*5,0,5,1))

它的作用:对由以下定义的范围求和:从 (A1 * 5) 个单元格向下开始的单元格 block ，从 $D$1 穿过 0 个单元格，其高度为 5，宽度为 1。请参阅编辑，我将其保留，因为您可以在 A 单元格中放入任意数字并使用此原理。

希望这有某种意义吗？ Excel 不适合文本解释!

编辑:删除单元格 A1...，记住 ROW() 允许自引用，这意味着您可以这样做

 =SUM(OFFSET(D$1,(ROW(B1)-1)*5,0,5,1))

此公式位于单元格 B1 中(粘贴下来)，您的数据位于 D 列中，从 1 开始。