如何使用 ARRAYFORMULA 和 OFFSET 到前一行而不会出现循环引用错误 [英] How to use ARRAYFORMULA with OFFSET to previous row without getting circular reference error

问题描述

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

在ItemData表的B列中，我通过将公式复制到列中的每个单元格中获得了我想要的结果，但我想改用ArrayFormula来解决这个问题.

In column B of ItemData sheet, I have achieved the result I want by copying the formula into every cell in the column, but I want to solve this using ArrayFormula instead.

在 C 列中，我使用 ArrayFormula 获得了相同的结果.但是，此外，C 列指的是 B 列中的单元格，而 B 列指的是 B 列中的单元格.即B 列中的每个单元格都将上一行的单元格加 1.

In column C I have achieved the same result using ArrayFormula. However, for addition, column C is referring to cells in column B, while column B is referring to cells in column B. I.e. every cell in column B is adding 1 to the cell on the row above.

如果我选择 C3 公式文本并将其粘贴到单元格 B3 的单元格编辑字段中(以免在复制过程中搞砸单元格引用 - 我知道我可以使它们成为静态引用，但这不是我的问题)，单元格得到一个错误值

If I select the C3 formula text and paste it into the cell edit field for cell B3 (to not screw up cell references during copy - I know I could make them static references, but this is not my problem), the cell gets an error value of

#REF！

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

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

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

Do note that the additions that need to be done are the same in both cases: Add 1 to the value of the cell on the previous row, so there is no circular reference involved. There is a starting value provided in B2, and cells in B3 and downwards should use the data from the B cell in the previous row.

另外，请注意，我确实尝试过 File->Spreadsheet 设置并启用最多 25 个项目的循环引用计算，但这只会填充前两个单元格(B3 和 B4).

Also, note that I did try File->Spreadsheet settings and enabling circular reference computation with max 25 items, but this only fills in the first two cells (B3 and B4).

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

How can I solve this problem? I would prefer having something like ArrayFormula, where the formula only exists in a single cell. But copy-pasting would be acceptable as long as any new rows, inserted in between or added at the bottom, would get the same formula added in column B.

推荐答案

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

Will matching items always be consecutive? It seems that way since you're comparing each Item cell to the cell above it right in your formula logic. That breaks an [unwritten?] rule of spreadsheet normalization; values' addresses themselves generally should not be treated as data.

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

IF you're committed to it though, have you considered explicitly using location as a data source? Example:

=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,
))

就像你的公式一样，用英语做的就是:

Just like your formulas, all that does in English is:

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 行，出现的每个数字只是:

But then what is ¹ "the number that belongs in this cell" and how can we calculate it without using column B? I abuse locations of things to get it. Looking down your row B, each number that appears is just:

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 …你想要的数字.

Using the second-to-last ItemC as an example: the first ItemC is the 16th item listing, and the one we're looking up… the "second-to-last ItemC" is in row 21 of the sheet. 21-3-16 = 2 …the number you wanted.

如果你能忍受，它就是一个单一的公式，并且可以根据你的规格工作.

If you can stomach that, it's a single formula and does work according to your specifications.

这篇关于如何使用 ARRAYFORMULA 和 OFFSET 到前一行而不会出现循环引用错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！