创建类似于没有VBA的Excel“循环”

I am trying to build a formula that simulates a "loop" in excel. The spreadsheet pulls data from a database into four spreadsheets. The formula needs to iterate through the sheets, moving onto the next sheet if the value found is #N/A. The current set up uses index(match()) to find the value in the table on the sheet. To move between sheets, I am using choose() to drive an indirect() to change to the next sheet, but I'm not sure how to simulate the loop... the problem seems to be that I would need to refer the function back to itself and then break out of the loop.

我正在尝试构建一个公式来模拟excel中的“循环”。电子表格将数据从数据库中提取到四个电子表格中。公式需要遍历这些表，如果找到的值是#N/A，则转到下一个表。当前设置使用索引(match())查找表中的值。为了在表之间移动，我使用了choose()来驱动一个间接()来更改下一个表，但是我不确定如何模拟循环……问题似乎是，我需要将函数引用回本身，然后跳出循环。

I'm imagining the solution would be something like

我想答案应该是这样的

=IFNA(CHOOSE( [the next number], INDEX(Sheet[the next number]!A1:Z500, MATCH(G1, Sheet[the next number]!A1:Z1), MATCH(A250, Sheet[the next number]!A1:A500)

The sheet cannot use VBA.

表格不能使用VBA。

2 个解决方案

#1

Further to my comment: Iterative calculations could be used, similar to this setup -

进一步的我的评论:可以使用迭代计算，类似于这个设置

• A1: =INDEX(B1:B4,A2)*1

  A1:=指数(B1:B4,A2)* 1

• A2: IF(ISERR(A1),A2+1,A2)

  A2:如果(ISERR(A1,A2 + 1,A2)

• B1:B4: lookup values

  B1:B4:查找值

When Index in A1 encounters a letter in B1:B4 it throws an error (from *1)

当A1中的索引与B1:B4中的字母相遇时，它会抛出一个错误(来自*1)

When A2 sees an error in A1, it increments

当A2在A1中看到一个错误时，它会增加。

Since A2 is the row value that A1 is indexing, A1 looks at the next row, which may or may not cause an error

由于A2是A1索引的行值，A1会查看下一行，这可能会导致错误，也可能不会

When A1 is not an error, A2 stops incrementing. See the below image

当A1不是错误时，A2停止递增。看到下面的图片

A similar setup could be used to iterate through the sheets looking for an error in the index formula using ISNA, I would have used that as the example, but I'm not 100% sure what your formula is doing (it has too many open brackets!)

类似的设置可以用于遍历表，使用ISNA查找索引公式中的错误，我本可以将其用作示例，但我不能100%确定您的公式正在做什么(它有太多的开放括号!)

Notes

To access the iterative formulae option, go File->Options->Formulas->Check Enable iterative calculation. Set the value of maximum iterations to 1 if you want to hit F9 and increment through the sheets one at a time manually, otherwise set it to the number of sheets you are looking at (4?). That way you will loop your helper cell 4 times, and it will stop incrementing on whatever occasion it does not detect an error.

为了访问迭代公式选项，go File->选项->公式->检查允许迭代计算。如果您想要按F9并手工一次一个地递增，请将最大迭代值设置为1，否则将它设置为您正在查看的表的数量(4?)。这样，您将对辅助单元格进行4次循环，无论何时，它都将停止递增，而不会检测到错误。

Also, I like to wrap my circular reference formula, A2, in an extra IF(A3 = "rst",1,...) so that I can reset the value without retyping the formula

另外，我喜欢用一个额外的IF(A3 = "rst"，1，…)来包装我的循环引用公式A2，这样我就可以在不重新输入公式的情况下重置值

Iterative calculations are great as they allow you effectively to save data in Excel mid calculation, but they must be used with caution to avoid infinite loops and huge calculation times - I use VBA because it's safer for that

迭代计算很好，因为它可以有效地保存Excel中计算的数据，但必须谨慎使用它们，以避免无限循环和巨大的计算时间——我使用VBA是因为这样更安全

#2

It would be simpler to split the formula in to 5 different formulas:
the first 4 do the lookups on the 4 sheets
then the 5th formula uses nested If (or IFS if you are on Office 365 Excel 2016) to select the lookup with a valid result from the 4 formulas.

是简单的将公式在5个不同的公式:第一个4做的查找表然后第五公式使用嵌套的If(或IFS如果你在办公室365 Excel 2016)选择查找4公式的一个有效的结果。