google-sheets - 如何使用带有 OFFSET 的 ARRAYFORMULA 到前一行而不会出现循环引用错误

Question

示例表: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!

Error Circular dependency detected. To resolve with iterative calculation, see File > Spreadsheet Settings.

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

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

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

Answer 1

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

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

=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

以倒数第二个 ItemC 为例:第一个 ItemC 是第 16 个项目列表，而我们正在查找的……“倒数第二个 ItemC”是在工作表的第 21 行。 21-3-16 = 2 ……你想要的数字。

如果您能接受，它就是一个单一的公式，并且可以根据您的要求发挥作用。