如何使用 ARRAYFORMULA 与上一行的偏移量而不会出现循环引用错误

示例表：https://docs.google.com/spreadsheets/d/14ma-y3esh1S_EkzHpFBvLb0GzDZZiDsSVXFktH3Rr_E/edit?usp=sharing https://docs.google.com/spreadsheets/d/14ma-y3esh1S_EkzHpFBvLb0GzDZZiDsSVXFktH3Rr_E/edit?usp=sharing

在 ItemData 表的 B 列中，我通过将公式复制到该列中的每个单元格中来实现我想要的结果，但我想使用 ArrayFormula 来解决这个问题。

在 C 列中，我使用 ArrayFormula 获得了相同的结果。然而，对于加法，C 列引用 B 列中的单元格，而 B 列引用 B 列中的单元格。 B 列中的每个单元格都会向上一行的单元格加 1。

如果我选择 C3 公式文本并将其粘贴到单元格 B3 的单元格编辑字段中（为了在复制过程中不搞乱单元格引用 - 我知道我可以将它们设为静态引用，但这不是我的问题），单元格会收到错误的价值

#REF!

错误 检测到循环依赖。要通过迭代计算来解决，请参阅文件 > 电子表格设置。

请注意，两种情况下需要执行的加法是相同的：将上一行单元格的值加 1，因此不涉及循环引用。 B2 中提供了一个起始值，B3 及以下的单元格应使用上一行 B 单元格中的数据。

另请注意，我确实尝试了“文件”->“电子表格”设置并启用最多 25 个项目的循环引用计算，但这仅填充前两个单元格（B3 和 B4）。

我怎么解决这个问题？我更喜欢像 ArrayFormula 这样的东西，其中公式仅存在于单个单元格中。但只要在中间插入或在底部添加的任何新行都会在 B 列中添加相同的公式，复制粘贴就是可以接受的。

将匹配项目always是连续的吗？看起来是这样，因为您在公式逻辑中将每个项目单元格与其上方的单元格进行比较。这打破了电子表格标准化的[不成文？]规则；价值观'地址它们本身通常不应被视为数据。

如果您决心这样做，您是否考虑过明确使用位置作为数据源？例子：

=ARRAYFORMULA(IFS(
  NOT(LEN(A3:A40)),,
  ROW(A3:A40)-3-MATCH(A3:A40,A$3:A$40,0)<=VLOOKUP(VLOOKUP(A3:A40,Items!$A$2:$D,2,false),DataPerColor!$A$2:$B,2,false),ROW(A3:A40)-3-MATCH(A3:A40,A$3:A$40,0),
  true,
))

就像你的公式一样，英文的全部内容是：

for each row,  
if there's no Item, don't output any ItemData,  
if the number that belongs in this cell¹ is less than or equal to the lookup, print it,  
otherwise, don't output any ItemData

但是，“属于该单元格的数字”是什么？我们如何在不使用 B 列的情况下计算它呢？我滥用物品的位置来获取它。往下看 B 行，出现的每个数字只是：

this row's number,  minus  
the row where items start [always 3],  minus  
the row number [in just the Item rows] of the first row containing this row's Item

Using 倒数第二个项目C举个例子：第一个 ItemC 是第 16 个项目列表，我们正在查找的项目...“倒数第二个 ItemC”位于工作表的第 21 行。 21-3-16 = 2 …你想要的数字。

如果您能接受，它是单一配方，并且可以根据您的规格发挥作用。