Google Sheets大数据集优化：用单个数组公式替代5000个单元格公式

问题背景 #

当前在Google Sheets的5000个单元格中逐个使用以下公式：

=ifna(query(sheet1!$A$2:$S, "Select s,p Where p <>'' AND B="&""""&n2&""""&"Limit 1"),ifna(query(sheet1!$a$2:$s, "Select s,p where P<>'' and B="&""""&o2&""""&"Limit 1"),""))

公式逻辑：

• 优先用当前工作表N列对应行的值匹配Sheet1的B列，返回Sheet1中P列非空的对应S列、P列首个匹配值（IFNA处理空值）；
• 若N列无匹配结果，则用O列对应行的值匹配Sheet1的B列，返回相同规则的结果；无匹配时返回空值。

公式可正常运行，但5000个单元格重复使用会导致效率低下，尝试过ARRAYFORMULA、QUERY、VLOOKUP的组合均未成功，需要一个单个数组公式实现批量遍历匹配。

解决方案 #

合并返回S、P列值的数组公式 #

在目标区域的起始单元格（如Q2）输入以下公式，即可自动填充所有行：

=ARRAYFORMULA(
  IFERROR(
    VLOOKUP(
      IFERROR(
        XMATCH(N2:N, FILTER(Sheet1!B2:B, Sheet1!P2:P<>"")),
        XMATCH(O2:O, FILTER(Sheet1!B2:B, Sheet1!P2:P<>""))
      ),
      {SEQUENCE(COUNTA(FILTER(Sheet1!B2:B, Sheet1!P2:P<>""))), FILTER(Sheet1!S2:S&"|"&Sheet1!P2:P, Sheet1!P2:P<>"")},
      2,
      FALSE
    ),
    ""
  )
)

之后可通过「数据>拆分文本到列」，以|为分隔符，将S、P列值拆分到相邻单元格。

分别返回S、P列值的数组公式 #

如果需要直接将S、P列值分别放入不同单元格，可使用以下两个公式：

提取S列匹配值

=ARRAYFORMULA(
  IFERROR(
    INDEX(
      Sheet1!S2:S,
      IFERROR(
        XMATCH(N2:N, FILTER(Sheet1!B2:B, Sheet1!P2:P<>"")),
        XMATCH(O2:O, FILTER(Sheet1!B2:B, Sheet1!P2:P<>""))
      )
    ),
    ""
  )
)

提取P列匹配值

=ARRAYFORMULA(
  IFERROR(
    INDEX(
      Sheet1!P2:P,
      IFERROR(
        XMATCH(N2:N, FILTER(Sheet1!B2:B, Sheet1!P2:P<>"")),
        XMATCH(O2:O, FILTER(Sheet1!B2:B, Sheet1!P2:P<>""))
      )
    ),
    ""
  )
)

公式逻辑说明 #

1. 筛选基准数据集：通过FILTER(Sheet1!B2:B, Sheet1!P2:P<>"")筛选出Sheet1中P列非空的B列值，缩小匹配范围；
2. 双重匹配查找：先用XMATCH查找N列值在基准数据集中的位置，无匹配则自动切换为查找O列值的位置；
3. 提取目标值：通过INDEX或VLOOKUP根据找到的位置，提取Sheet1对应行的S、P列值，IFERROR处理无匹配场景，返回空值。

这种方式仅需1-2个数组公式即可覆盖所有行，大幅提升表格运行效率。

内容的提问来源于stack exchange，提问作者LemNick