Google Excel IMPORTRANGE 数组输出

根据列表中一列表格链接和一列引用范围使用 IMPORTRANGE 引用数据并且数组输出。

例如表格内 A 列是表格链接，B 列是引用范围。

那么常规的方法一般会使用 {}（大括号），把内容合并为数组输出。

={
  IMPORTRANGE(A1, B1);
  IMPORTRANGE(A2, B2)
}

虽然也可以实现这个效果，但是引用的范围是静态的，当列表中增加或者删减需要引用的表格链接和范围，那么就需要手动更改函数。

解决方法

=WRAPROWS(
  TOROW(MAP(
    A1:A2, B1:B2, 
    LAMBDA(
      ssUrl, ssRange, 
      TOROW(
        IFERROR(IMPORTRANGE(ssUrl, ssRange))
      )
    )
  ))
, 5)

原理分析

这里使用了 LAMBDA 和 MAP 组合函数，把引用的 A 列和 B 列在 LAMBDA 函数中分别自定义 ssUrl 和 ssRange 变量，这两个变量分别对应表格链接和引用范围。然后再把这两个变量的值传入 IMPORTRANGE 函数中。

=MAP(A1:A2, B1:B2, LAMBDA(ssUrl, ssRange, IMPORTRANGE(ssUrl, ssRange)))

但是直接这么使用的话函数会出错，因为 LAMBDA 函数只支持单行输出，而 IMPORTRANGE 引用的数据是一整个范围的。

所以需要把 IMPORTRANGE 引用的数据转换成行。这里用到 TOROW 函数，作用是将一个数组或者一个范围的数据转换成单行。

=MAP(A1:A2, B1:B2, LAMBDA(ssUrl, ssRange, TOROW(IMPORTRANGE(ssUrl, ssRange))))

因为列表中 A 列的表格链接有两个，虽然刚才把 IMPORTRANGE 输出的结果转换成单行了，但是有两个链接，所以会输出两行的数据，需要再次使用 TOROW 函数把所有数据都转换成单行。

=TOROW(MAP(A1:A2, B1:B2, LAMBDA(ssUrl, ssRange, TOROW(IMPORTRANGE(ssUrl, ssRange)))))

现在已经把所有的数据都引用过来了，需要把数据的格式还原。这里用到 WRAPROWS 函数，将数据按照指定的列数拆分并且换行。

在列表中 B 列引用的范围是 A 到 E 列，这个范围一共有 5 列，那么在 WRAPROWS 函数中第二个参数需要设置 5，意思是每 5 列将数据换行。

=WRAPROWS(TOROW(MAP(A1:A2, B1:B2, LAMBDA(ssUrl, ssRange, TOROW(IMPORTRANGE(ssUrl, ssRange))))), 5)

这样就实现了 IMPORTRANGE 数组输出，只需要在列表中 A 列和 B 列修改表格链接和引用范围就可以自动根据这些范围引用内容。

如果想去掉引用数据的空行，可以使用 QUERY 函数筛选非空行，例子如下。

=QUERY(
  WRAPROWS(
    TOROW(MAP(
      A1:A2, B1:B2, 
      LAMBDA(
        ssUrl, ssRange, 
        TOROW(IMPORTRANGE(ssUrl, ssRange)))
      )
    )
  , 5)
, "select * where Col1 is not null")