☰

在 Microsoft Excel 中使用 AVERAGE() 公式时，自动填充功能无法正确迭代数字

tag-icon • tag-icon microsoft-excel
在 Microsoft Excel 中使用 AVERAGE() 公式时，自动填充功能无法正确迭代数字

我有一列月度数据，我想用它创建一个新的季度数据列。为了做到这一点，我从月度数据列中取出 3 个月的增量数据，并对它们使用 AVERAGE() 公式。我有类似的东西；

平均值(D13:D15) Y1（一月 - 三月）

平均值(D16:D18) Y1（4 月 - 6 月）

平均值(D19:D21) Y1（七月 - 九月）

平均值(D22:D24) Y1（十月 - 十二月）

由于数据跨越了近一个世纪，我想使用自动填充功能来让我的生活更轻松。令我沮丧的是，Excel 无法识别我一次迭代 3 个单元格，而是给我这样的结果；

平均值(D13:D15) Y1（一月 - 三月）

平均值(D16:D18) Y1（4 月 - 6 月）

平均值(D19:D21) Y1（七月 - 九月）

平均值(D22:D24) Y1（十月 - 十二月）

平均值(D17:D19) Y1（五月 - 七月）

平均值(D20:D22) Y1（8 月 - 10 月）

AVERAGE(D23:D25) Y1（11 月、12 月）- Y2（1 月）

平均值(D26:D28) Y2（二月 - 四月）

而不是：

平均值(D13:D15) Y1（一月 - 三月）

平均值(D16:D18) Y1（4 月 - 6 月）

平均值(D19:D21) Y1（七月 - 九月）

平均值(D22:D24) Y1（十月 - 十二月）

平均值(D25:D27) Y2（一月 - 三月）

平均值(D28:D30) Y2（4 月 - 6 月）

平均值(D31:D33) Y2（七月 - 九月）

平均值(D34:D36) Y2（十月 - 十二月）

有没有什么解决方法，或者我是否必须手动将所有月度数据转换为季度数据？

答案1

假设您的第一个季度单元格（包含 的单元格=AVERAGE(D13:D15)）是Q42。将该公式替换为=AVERAGE(OFFSET($D$13, 3*(ROW()-42), 0, 3, 1))，然后将其向下拖动/填充。该OFFSET函数允许您访问单元格，而无需逐字输入其地址（例如D25）；实际上，您可以说从单元格 向下的第 12 个单元格D13。此公式表示，

  • 取当前行号 ( ROW()) 并减去 42（单元格 的行号Q42，即您想要 Y1 Q1 平均值的位置）。显然，这在单元格 中计算结果为 0。Q42当将其拖到单元格 时Q43，计算结果为 1，等等。在Q46（您想要 Y2 Q1 平均值的位置）中，您得到 4。
  • 乘以 3。显然，这会给出自开始（Y1 年 1 月）以来的月份数。
  • 从 开始D13，向下移动刚刚计算的月份数，然后向右移动零列。然后取AVERAGE高三行、宽一列的范围。

答案2

自动填充只会在每拖动一行时增加一行引用。要获得不同的间隔，请使用=row()带乘数的函数。

=ROW(A1)*3+10计算结果为 A1 * 3 的行数，加上 10，即 13，每行增加 3

INDIRECT接受一个文本字符串并将其转换为引用，因此

=AVERAGE(INDIRECT("D"&ROW(A1)*3+10&":D"&ROW(A1)*3+12))

评估结果为

average(indirect("D"&13&":D"&15)

这进一步表明

=AVERAGE(D13:D15)

这将拖累如下

=AVERAGE(D13:D15)
=AVERAGE(D16:D18)
=AVERAGE(D19:D21)
=AVERAGE(D22:D24)
=AVERAGE(D25:D27)
=AVERAGE(D28:D30)
=AVERAGE(D31:D33)
=AVERAGE(D34:D36)
=AVERAGE(D37:D39)
=AVERAGE(D40:D42)
=AVERAGE(D43:D45)
=AVERAGE(D46:D48)
=AVERAGE(D49:D51)

ETC。

作为奖励，以下公式将拖低您的标签，尽管这可以通过巧妙地自动填充 2 个单元格（1 个包含年份部分，一个包含月份部分）来实现，并使用&)连接在一起

="Y"&ROUNDUP(ROW(A1)/4,0)&" "&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(MOD(ROW(A1),4),1,"(January - March)"),2,"(April - June)"),3,"(July - September)"),0,"(October - December)")

将会拖累为：

Y1 (January - March)
Y1 (April - June)
Y1 (July - September)
Y1 (October - December)
Y2 (January - March)
Y2 (April - June)
Y2 (July - September)
Y2 (October - December)
Y3 (January - March)
Y3 (April - June)
Y3 (July - September)
Y3 (October - December)
Y4 (January - March)
Y4 (April - June)
Y4 (July - September)
Y4 (October - December)
Y5 (January - March)

ETC。

相关内容