excel - 在传递给公式之前向每个单元格添加一个

Question

我想取值可能小于 1 的列的 geomean。我想在计算 geomean 之前为每个单元格添加一个，并且我不想在计算中包含隐藏的行。

到目前为止我一直在使用=POWER(AGGREGATE(6,5,O6:O22),1/AGGREGATE(2,5,O6:O22))，因为这让我可以忽略隐藏的行。但是，当 O6:O22 中给定单元格中的值小于 1 时，这在数学上是不正确的。我想将值向上偏置 1，进行计算，然后在最后取消偏置结果。实际上，类似=POWER(AGGREGATE(6,5,O6:O22 + 1),1/AGGREGATE(2,5,O6:O22 + 1)) - 1

我知道数组公式可以做这样的事情，但我还没有找到一个有效的例子。以上在语法上是不正确的，尽管我发现一些例子表明并非如此。

我故意避免使用另一个隐藏列进行计算，因为我已经在此表上隐藏了列和行以用于数据呈现。在取消隐藏数据时不断重新隐藏计算列会很麻烦。

标签： excel

Answer 1

她是根据您的公式使用的公式（请参阅下面的较短版本）。

=POWER(PRODUCT(IF(SUBTOTAL(3,OFFSET(O6:O22,ROW(O6:O22)-MIN(ROW(O6:O22)),,1)),O6:O22+1)),1/SUMPRODUCT(SUBTOTAL(3,OFFSET(O6:O22,ROW(O6:O22)-MIN(ROW(O6:O22)),,1))))-1

这是一个数组公式，退出编辑模式时必须使用 Ctrl-Shift-Enter 而不是 Enter 来确认。

---

编辑：

您可以将 GEOMEAN() 与上面的 IF() 一起使用，以更短的公式返回相同的结果：

=GEOMEAN(IF(SUBTOTAL(3,OFFSET(O6:O22,ROW(O6:O22)-MIN(ROW(O6:O22)),,1)),O6:O22+1))-1

仍然是一个数组公式，退出编辑模式时需要使用 Ctrl-Shift-Enter 而不是 Enter 来确认。