☰

每个月末变化的平均值 - 动态公式

tag-icon • tag-icon microsoft-excel microsoft-excel-365
每个月末变化的平均值 - 动态公式

我正在尝试建立现金流量电子表格，但发现很难找到正确的公式。

我想计算从本月开始的过去 6 个月的平均值 - 1，6 月至 10 月的平均值，并且公式会在每个月末进行调整

在此处输入图片描述

感谢您的帮助

答案1

这个公式非常简单。

细胞H3公式：

    =AVERAGE(B2:G2)

因此，对于 7 月，我们计算 1 月至 6 月的平均值。将公式拖到右侧。

在此处输入图片描述

您没有提到您需要将平均值填充到任何特殊位置，这就是我提出这样的答案的原因。如果您想在其他地方显示最近的平均值，那么您可以将此公式输入到所需的单元格中：

    =LOOKUP(2,1/(A3:M3<>""),A3:M3)

这会将最后一个非空单元格拉至单元格 A3 至 M3 之间。

答案2

假设您要从 A1 到 F1 计算平均值。G1 为 =AVERAGE(A1:F1)。当您在 F1 和 G1 之间添加一列新数据时，问题就出现了。公式现在移至 H1，但引用保持不变（奇怪的是，如果您现在在 G1 中输入数字，H1 中的公式将自动更新以将其包含在 A1:G1 中 - 至少在较新版本的 Excel 中）。要使其动态化（即始终平均左侧的前六个单元格），请使用 G1 中的偏移公式：

=AVERAGE（偏移（G1,0，-6,1,6））

这里有一个小警告。OFFSET 是一个易失性函数，这意味着 Excel 会在工作簿（或任何其他打开的工作簿）发生任何更改时重新计算它，因为它无法确定该单元格的静态依赖关系树，仅在其依赖关系发生变化时才更改/重新计算。现在这对于小型电子表格来说不是问题，但对于大型数据集，速度会很快变得非常慢。更好的解决方案是使用 INDEX 函数根据其在该行中的绝对位置有效地从整行中挑选出正确的六个数字。G1 变成

=AVERAGE(INDEX(A1:G1，COLUMN()-COLUMN(A1)-5):INDEX(A1:G1，COLUMN()-COLUMN(A1)))

让索引数组引用到 G1（即包含自身）很重要。这可能看起来像一个循环引用，但它将确保数组随着您插入更多列而不断增长。

将最好的留到最后，输入“动态命名范围”！我喜欢这个优雅的解决方案，尽管它有点晦涩难懂（请阅读 excel magic）。确保选择了 G1。单击公式 > 定义名称。将范围命名为“sixleft”，将范围更改为 Sheet1（或任何工作表名称），并将“引用”设置为 =Sheet1!A1:F1，然后单击确定。现在输入 G1：

=AVERAGE（六左）

相关内容