如何规避Google Sheets中TEXTJOIN字符限制以匹配数据？

我有一个Google Sheets文件，包含四个工作表，记录两家公司的水表用量与费用数据。水表本身相同，但更换公司后生成了新ID，用量（C列）和费用（D列）数据保持一致。需求是：匹配两个工作表中的用量和费用数据，返回新公司的ID，同时最好能返回旧ID作为手动转换的标识。

当前可正常运行的公式 #

该公式从旧公司表（Copy of BillHistory）中筛选出与新公司表（Copy of Add Bills-Water）用量、费用匹配的记录：

=QUERY('Copy of BillHistory'!1:9916,"SELECT A,B,E WHERE D MATCHES '"&TEXTJOIN("|", 1, 'Copy of Add Bills-Water'!D:D)&"' AND C MATCHES '"&TEXTJOIN("|", 1, 'Copy of Add Bills-Water'!C:C)&"' AND D > 0", 1)

无法运行的目标公式 #

我需要从新公司表中筛选匹配旧公司表用量、费用的记录，但运行以下公式报错：

=QUERY('Copy of Add Bills-Water'!1:1000,"SELECT A,B,E WHERE D MATCHES '"&TEXTJOIN("|", 1, 'Copy of BillHistory'!D:D)&"' AND C MATCHES '"&TEXTJOIN("|", 1, 'Copy of BillHistory'!C:C)&"' AND D > 0",1)

错误信息 #

Text result of TEXTJOIN is longer than the limit of 50000 characters.

补充说明 #

尝试通过限制读取1000行来解决TEXTJOIN长度问题，但修改后公式仅显示表头，无数据返回。

问题根源是TEXTJOIN拼接旧表的用量、费用数据时，生成的正则字符串超出了Google Sheets的50000字符限制。推荐使用以下两种方案规避该限制：

方案1：用VLOOKUP+拼接匹配键实现关联 #

该方案通过拼接用量（C列）和费用（D列）作为唯一匹配键，关联新旧表，同时返回新公司ID、辅助字段、费用及对应的旧公司ID：

=FILTER(
  ARRAYFORMULA(
    IFERROR(
      VLOOKUP(
        'Copy of Add Bills-Water'!C:C&"|"&'Copy of Add Bills-Water'!D:D,
        {'Copy of BillHistory'!C:C&"|"&'Copy of BillHistory'!D:D, 'Copy of BillHistory'!A:A, 'Copy of Add Bills-Water'!A:A, 'Copy of Add Bills-Water'!B:B, 'Copy of Add Bills-Water'!E:E},
        {3,4,5,2},
        FALSE
      ),
      ""
    )
  ),
  'Copy of Add Bills-Water'!D:D>0,
  NOT(ISBLANK('Copy of Add Bills-Water'!C:C))
)

• 逻辑：先通过VLOOKUP匹配拼接后的用量+费用键，再用FILTER排除无效行（费用为0、用量为空的记录）。

方案2：用QUERY内连接替代TEXTJOIN正则匹配 #

若偏好使用QUERY，可通过虚拟表拼接实现内连接，避免字符长度限制：

=QUERY(
  {'Copy of Add Bills-Water'!A1:E1000, 'Copy of BillHistory'!A1:A9916},
  "SELECT Col1, Col2, Col5, Col6
   WHERE Col4 > 0
     AND EXISTS(
       SELECT 1
       FROM 'Copy of BillHistory'!1:9916
       WHERE Col3 = Col3
         AND Col4 = Col4
         AND Col4 > 0
     )",
  1
)

• 逻辑：通过EXISTS子查询判断当前行的用量、费用是否在旧表中有匹配记录，直接返回关联的新旧ID及所需字段。

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