如何使用带有OFFSET的ARRAYFORMULA到上一行而不出现循环引用错误 [英] How to use ARRAYFORMULA with OFFSET to previous row without getting circular reference error
问题描述
在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.
推荐答案
匹配项总是会连续吗?因为您要在公式逻辑中将每个项目"单元格与其上方的单元格进行比较，所以似乎是这样.这打破了电子表格规范化的[未写入?]规则；值的地址本身通常不应被视为数据.
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.
这篇关于如何使用带有OFFSET的ARRAYFORMULA到上一行而不出现循环引用错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
