使用 IMPORTRANGE 作为数组公式 [英] Using IMPORTRANGE as an array formula

问题描述

我在一个单元格中有一个 URL，这是 IMPORTRANGE 的标准.例如:

I have a URL in one cell which is the criterion for the IMPORTRANGE. e.g.:

=IMPORTRANGE(B2,"sheet1!$A$1")

我找到了一个公式来收集它作为标准，但不是在数组版本中.

I found a formula to collect it as criterion, but not in array version.

可以在此处找到示例.

推荐答案

虽然您不能在数组公式中使用 IMPORTRANGE()，如另一个答案中所述，您可以在数组文字中使用它.在您的情况下，对于包含电子表格 URL 的 B 列中的前五个单元格:

While you cannot use IMPORTRANGE() in an arrayformula, as detailed in the other answer, you can use it in an array literal. In your case, for the first five cells in your column B that contain spreadsheet URLs:

={
  IMPORTRANGE(B2,"sheet1!$A$1");
  IMPORTRANGE(B3,"sheet1!$A$1");
  IMPORTRANGE(B4,"sheet1!$A$1");
  IMPORTRANGE(B5,"sheet1!$A$1");
  IMPORTRANGE(B6,"sheet1!$A$1")
}

因此，您必须多次编写 IMPORTRANGE() 并且只有在您知道要导入多少张纸之前才能使用此技术.但优点是这也适用于 IMPORTRANGE() 返回多行的情况 - 在这种情况下，向下复制公式是没有选择的，因为 IMPORTRANGE() 会产生错误以防其结果会进一步覆盖单元格内容.

So you'll have to write IMPORTRANGE() multiple times and you can only use this technique if you know before how many sheets there are to import. But the advantage is that this also works where IMPORTRANGE() returns multiple rows each – in that case, copying the formulas down is no option, as IMPORTRANGE() creates an error in cases where its results would overwrite cell contents further down.

这篇关于使用 IMPORTRANGE 作为数组公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！