☰

如果 12 个单元格中的值大于 0，则返回它们的平均值

tag-icon • tag-icon microsoft-excel
如果 12 个单元格中的值大于 0，则返回它们的平均值

我需要计算假期工资，取前 12 周的平均值。如果某一周没有任何价值，显示为 0 值，则应将这一周排除在计算之外，并计入额外一周。因此，总是需要 12 周的工资。这是一个持续的公式。

每周工资在单元格 B1:P1 中（15 周的工资）。2 周的值为 0（F1 和 M1），因此应将其排除在外并包括另外 2 周。

因此，公式应返回最近 12 个单元格的平均值。因此，基本上应返回单元格 C1、D1、E1、G1、H1、I1、J1、K1、L1、N1、O1、P1 的总值的平均值。

对于接下来的一周，它必须从 D1、E1、G1、H1、I1、J1、K1、L1、N1、O1、P1、Q1 等返回值。

克里斯蒂娜

答案1

如果要避免使用 volatileOFFSET函数，可以使用：

=AVERAGE(INDEX(2:2,1,AGGREGATE(14,6,ISNUMBER(1/(B2:P2))*COLUMN(B2:P2),SEQUENCE(12))))

如果你没有该SEQUENCE功能，你可以使用：

=AVERAGE(INDEX(2:2,1,AGGREGATE(14,6,ISNUMBER(1/(B2:P2))*COLUMN(B2:P2),ROW(INDEX($A:$A,1):INDEX($A:$A,12)))))

两个公式都取最右边十二列非零数值。

如果您的范围可能超出 P 列，则只需将范围更改B2:P2为确保包含潜在使用范围的范围。例如，B2:XFD2最大值仍然有效。

在此处输入图片描述

答案2

我找到了一个更强大的解决方案，它看起来很吓人，但它分成几个部分：

=AVERAGE(IFERROR(INDEX(FILTER(SORTBY($B1:P1,COLUMN($B1:P1),-1),SORTBY($B1:P1,COLUMN($B1:P1),-1)<>0),SEQUENCE(12,1,1,1)),""))

让我们把它分解成几个部分，从SORTBY()出现两次的函数开始：

SORTBY($B1:P1,COLUMN($B1:P1),-1)

请注意，引用的第一部分固定在，$B1因此当我们向右复制/粘贴时，它不会更新。如果我们输入此公式P2并向右复制/粘贴，我们将始终查看从到B1我们现在所在的任何列的范围。简而言之，到目前为止的所有数据。现在，我们将按-1列号降序对数据进行反向排序（这就是所做的）。既然已经讨论了这一部分，让我们将公式重写为更短的版本：

=AVERAGE(IFERROR(INDEX(FILTER(SORTBY(~),SORTBY(~)<>0),SEQUENCE(12,1,1,1)),""))

接下来，关注INDEX(FILTER(),SEQUENCE())设置。该FILTER()函数从上面获取已排序的数组并过滤掉所有零值。它会将空白视为零，因此这些也包括在内。现在我们有一个按相反顺序排列的所有非零值的数组。通过将其与INDEX()和相结合SEQUENCE()，我们可以从该列表中提取前 12 个值。结合所有这些意味着我们现在拥有从范围到任何范围的 12 个最新非零值B1。提取这些位后，其余部分就非常简单了：

=AVERAGE(IFERROR(INDEX(~,""))

如果没有至少 12 个非零值，则INDEX()返回一个包含一些错误值的数组。我们习惯用IFERROR()空白替换这些错误，以便AVERAGE()可以工作。现在您应该看到错误的唯一方法是如果没有任何非零值。

截屏

如果您愿意，LET()可以通过为函数命名来省去重复操作的麻烦。以下函数与上面的函数相同，但杂乱之处SORTBY()已消除：

=LET(sortedRange,SORTBY($B1:P1,COLUMN($B1:P1),-1),AVERAGE(IFERROR(INDEX(FILTER(sortedRange,sortedRange<>0),SEQUENCE(12,1,1,1)),"")))

答案3

使用此宏

Sub average()

    Dim sum As Long
    Dim count As Long
    Dim column As Integer
    Dim row As Integer
    Dim row_count As Integer

    Let row_count = 2
    
    For row = 1 To row_count
        Let sum = 0
        Let count = 0
        Let column = 2
        While count < 12
            If Cells(row, column).Value > 0 Then
                sum = sum + Cells(row, column).Value
                count = count + 1
            End If
            column = column + 1
        Wend
        Cells(row, 1).Value = sum / 12
    Next row
       
End Sub

它会在第一个单元格中打印平均值。您可以通过编辑来更改它。Cells(row, 1).Value = sum / 12
如果有多行，只需更改row_count值即可。
它会搜索前 12 个有效（非零）值，然后计算平均值。

答案4

这个问题很有意思。理论上，这很简单 - 只需计算过去 12 个数字的平均值，但忽略所有零。在 Excel 中，这要求您真正想出一些复杂的公式来计算零，并对它们进行排序并排除它们。

这之所以如此具有挑战性，部分原因在于这实际上是一个迭代或递归问题，可能会永远持续下去（给定足够多的过去数据和足够多的零）。使用老式的 Excel 函数，您无法从第一个/最近的数字开始计算来解决这个问题（就像人类或图灵机那样），但您必须考虑所有数字并排除零，然后取最后 12 个。至少在 2020 年 12 月之前，Excel 才变得图灵完备并引入了 LAMBDA 函数！

如果您正在运行 Office 365，请使用递归 LAMBA 函数将新名称（公式 > 定义名称）定义为“SumNZ12”：

=LAMBDA(i，ref，如果(ref=0，SumNZ12(i，OFFSET(ref, 1, 0))，如果(i=11，ref，ref + SumNZ12(i+1，OFFSET(ref, 1, 0)))))

SumNZ12 计数并向后求和 12 个非零数字。使用方法如下：

=SumNZ12(0，C1)/12

全面披露：我还没有测试过这一点。ref 参数可能作为数字而不是变量传递，在这种情况下，公式应该调整，以便 ref 是文本参数并与 indirect(ref) 一起使用。

相关内容