如何平均前 5 个有值的单元格 [英] How to average the previous 5 cells that have values

问题描述

我有一个看起来像这样的电子表格

I have a spreadsheet that looks like this

我只想对包含数据的前 5 个单元格求平均值.我该怎么做?

I want to average only the previous 5 cells that have data in them. How can I do this?

随着列表获取更多数据，如下所示:

As the list gets more data, like this:

我希望能够定位一个单元格(如 A10)并从中返回 5.同样，仅当其中包含数据时.

I want to be able to target a cell (like A10) and go 5 back from that. Again, only if it has data in it.

所以图像的公式应该是这样的:(23 + 60 + 53 + 42 + 40)/5 = 43.6

So the formula for the image should be like this: (23 + 60 + 53 + 42 + 40)/5 = 43.6

它向后移动并跳过黑色空间，但仍然获得前一个 5.

It's going backward and skipping the black space, yet still getting the 5 previous.

推荐答案

以下公式假定您的值位于 A 列(如果不是，请调整所有 A:A 引用):

The following formula assumes your values are on column A (adjust all A:A references if they're not):

=Average(Filter(Sort(A:A;If(A:A="";0;Row(A:A));false);Row(A:A)<6))

要理解上面的公式，单独粘贴它的部分(当你得到一个没有 Filter 公式的部分时，你需要把它包装在一个 ArrayFormula 上.例如

To understand the formula above, paste its parts separately (when you get a part without the Filter formula, you'll need to wrap it on an ArrayFormula. e.g.

=Filter(Sort(A:A;If(A:A="";0;Row(A:A));false);Row(A:A)<6)
=ArrayFormula( Sort(A:A; If(A:A="";0;Row(A:A)); false) )
=ArrayFormula( Row(A:A) )

将这些辅助"公式放在工作表上可能有的空列上(在第一个单元格中，例如 D1、T1).

Place these "auxiliary" formulas on empty columns you might have on the sheet (in the first cells, e.g. D1, T1).

对于非整列，如您所建议的，您可能还需要将整个公式包装在一个数组公式中，例如

For non-entire columns, like you suggested, you may also need to wrap the whole formula in an array formula, e.g.

=ArrayFormula( Average(Filter(Sort(K1:K10;If(K1:K10="";0;Row(K1:K10));false);Row(K1:K10)<6)) )

我最初认为 Filter 公式的行为类似于 ArrayFormula，但它似乎并没有递归"到其参数的内部.因此，IF 需要 ArrayFormula 才能正常工作.我不知道为什么它对 A:A 起作用.

I initially thought that Filter formula would behave like the ArrayFormula, but it seems it does not "recursion" down to the inners of its parameters. Hance, the ArrayFormula is required for the IF to work properly. I do not know why it did work for A:A though.

这篇关于如何平均前 5 个有值的单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！