excel - 引用前一行的ArrayFormula？ (循环依赖错误)

Question

我遇到了一些问题，这似乎是一个相当常见的要求:包含先前行的数组公式计算。问题似乎是数组公式中的所有单元格都会立即计算，因此它认为存在循环依赖，并给出该错误。

我模拟了一个toy example on Google Spreadsheet您应该能够看到并发表评论。 (我已禁止编辑访问以防止破坏。)

它看起来像这样，其中所有内容都在行 2 中，项目符号 1,2,3,4 引用列 A,B >、C、D 分别为:

1. “更改”- 手动输入为空
2. '例如。兴趣' - =ARRAYFORMULA(IF(ROW(B2:B)>2, OFFSET(D2:D, -1, 0) + A2:A,))
3. “利率” - 手动输入为空
4. “余额” - =ARRAYFORMULA(IF(ROW(D2:D)=2, 0, B2:B*(1+C2:C)))

这个想法是在每行上都有复利(可能具有可变利率)和正/负资本变化的选项，但是，单元格B2和D2(第 2、4 点)错误 #REF，并在悬停时报告“循环依赖”。

我已经尝试了我能想到的一切:OFFSET、INDIRECT、D1:D(而不是2 code>)，甚至一个辅助列什么都不做，只是引用 D 的前一行(这只是导致了三列循环依赖)。

我怎样才能拥有“前任”？ “利息”列引用数组公式中前一行的“余额”列，而不会导致此错误？

类似的问题可以通过对行进行 SUMIF 来解决此问题，条件是该行小于数组公式中的“当前”行。 我看不出这对我的情况有用，因为我需要将整个运行总和乘以每一步的利息。我尝试了 SUMIF(... "="&DATE(...) ...) 引用前一行；这可以在辅助列中打印偏移余额，但尝试使用它(通过下面的迭代计算)的行为就像它为零一样。

根据 @JackBrown 的建议，我启用了迭代计算，消除了循环依赖错误，但它仍然不起作用 - 第二个填充行从前一行获取值 0，尽管上一行具有非零值。就好像它在早期迭代中获取了值，并且在更改时没有更新。

Answer 1

Excel

在Excel中制作智能表格并使用公式:

=If(行()=2,A2*(1+B2),(A1+C1)*(1+B2))

请在此处下载示例文件: https://drive.google.com/file/d/0B79ClRnKS87QTTR5VDBld0plajg/view?usp=sharing

<小时/>

Google 表格

为了尝试解决您的问题，我进行了一项研究。简短的回答:我不能这样做，而且我相信那是不可能的。

背景

我将案例简化为通过变化的利率查找 future 利率值的任务:

Rate+   Recursive formula for rate
1.00%   101.0000%    =(1+A2)
1.50%   102.5150%    =B2*(1+A3)
1.10%   103.6427%    =B3*(1+A4)
1.10%   104.7827%    =B4*(1+A5)

然后我手动创建了一个数组以获得相同的结果并得到了这个:

={1+A2;
  1+A2+(1+A2)*A3;
  1+A2+(1+A2)*A3+(1+A2+(1+A2)*A3)*A4;
  1+A2+(1+A2)*A3+(1+A2+(1+A2)*A3)*A4+(1+A2+(1+A2)*A3+(1+A2+(1+A2)*A3)*A4)*A5}

如您所见，参数数量增长得非常快:

1  = 2 ^1 - 1   1+B2         
3  = 2 ^2 - 1   1+B2+(1+B2)*B3 
7  = 2 ^3 - 1   1+B2+(1+B2)*B3+(1+B2+(1+B2)*B3)*B4   
15 = 2 ^4 - 1   1+B2+(1+B2)*B3+(1+B2+(1+B2)*B3)*B4 + ... 

为了计算最后一行的结果，我们需要 2^n - 1 变量:

• 第 32 行的数字是 4 294 967 295!

这就是为什么即使您启用迭代计算=开，arrayformula也无法处理计算

<小时/>

解决方法但这对于递归函数或脚本来说是非常简单的任务，因为它们可以记住最后找到的值。所以我建议编写脚本并将其用作自定义数组公式:

function futureValue(values, rates) {

  var result = [];
  var resultCurrent = 0;

  for (var i = 0, len = values.length; i < len; i++)
  {
    resultCurrent = (+values[i] + resultCurrent) * (1 + +rates[i]);
    result.push(resultCurrent);
  }

  return result;

}

链接:

1. Sample file脚本运行
2. Guide on custom functions