基于INDEX MATCH与CHOOSECOLS的Google Sheets数组公式优化需求

我有两个Google表格：

• 存储指标与日期的源数据表格
• 数据倒置分组后的目标表格

需要在目标表格中，对A列里以“+”分隔的指定指标求和。当前使用的公式需要根据求和指标的数量手动调整，想优化成通用公式，且支持一次性作用于整个数组，不用逐个单元格输入。

当前使用的公式：

=INDEX(IMPORTRANGE("1jNsgbu-IdkxF_vS6SxXR15rJiFZMpPsktcy6lV37_as","Sheet1!$A$2:DN"),MATCH(B$1,IMPORTRANGE("1jNsgbu-IdkxF_vS6SxXR15rJiFZMpPsktcy6lV37_as","Sheet1!$A$2:A"),0),MATCH(CHOOSECOLS(SPLIT($A4,"+"),1),IMPORTRANGE("1jNsgbu-IdkxF_vS6SxXR15rJiFZMpPsktcy6lV37_as","Sheet1!1:1"),0))+INDEX(IMPORTRANGE("1jNsgbu-IdkxF_vS6SxXR15rJiFZMpPsktcy6lV37_as","Sheet1!$A$2:DN"),MATCH(B$1,IMPORTRANGE("1jNsgbu-IdkxF_vS6SxXR15rJiFZMpPsktcy6lV37_as","Sheet1!$A$2:A"),0),MATCH(CHOOSECOLS(SPLIT($A4,"+"),2),IMPORTRANGE("1jNsgbu-IdkxF_vS6SxXR15rJiFZMpPsktcy6lV37_as","Sheet1!1:1"),0))+INDEX(IMPORTRANGE("1jNsgbu-IdkxF_vS6SxXR15rJiFZMpPsktcy6lV37_as","Sheet1!$A$2:DN"),MATCH(B$1,IMPORTRANGE("1jNsgbu-IdkxF_vS6SxXR15rJiFZMpPsktcy6lV37_as","Sheet1!$A$2:A"),0),MATCH(CHOOSECOLS(SPLIT($A4,"+"),3),IMPORTRANGE("1jNsgbu-IdkxF_vS6SxXR15rJiFZMpPsktcy6lV37_as","Sheet1!1:1"),0))

可以结合SUMPRODUCT、XLOOKUP、SPLIT和ARRAYFORMULA实现通用求和，同时支持数组批量计算。

高效版（推荐：先命名源数据） #

1. 预先导入并命名源数据：
   在目标表格空白单元格输入=IMPORTRANGE("1jNsgbu-IdkxF_vS6SxXR15rJiFZMpPsktcy6lV37_as","Sheet1!A1:DN")，完成授权后，通过「数据」→「命名区域」将该区域命名为源数据，避免重复调用IMPORTRANGE提升效率。

2. 通用数组公式：

=ARRAYFORMULA(
  IFERROR(
    SUMPRODUCT(
      XLOOKUP(B1:B, INDIRECT("源数据!A2:A"), INDIRECT("源数据!B2:DN")),
      --(TRANSPOSE(SPLIT(A4:A, "+"))=TRANSPOSE(INDIRECT("源数据!1:1")))
    )
  )
)

直接嵌套版（无需命名区域） #

如果不想设置命名区域，可直接使用嵌套IMPORTRANGE的版本：

=ARRAYFORMULA(
  IFERROR(
    SUMPRODUCT(
      XLOOKUP(B1:B, IMPORTRANGE("1jNsgbu-IdkxF_vS6SxXR15rJiFZMpPsktcy6lV37_as","Sheet1!A2:A"), IMPORTRANGE("1jNsgbu-IdkxF_vS6SxXR15rJiFZMpPsktcy6lV37_as","Sheet1!B2:DN")),
      --(TRANSPOSE(SPLIT(A4:A, "+"))=TRANSPOSE(IMPORTRANGE("1jNsgbu-IdkxF_vS6SxXR15rJiFZMpPsktcy6lV37_as","Sheet1!1:1")))
    )
  )
)

公式逻辑说明 #

• SPLIT(A4:A, "+")：将A列的分组指标按“+”拆分，得到单个指标列表
• TRANSPOSE(SPLIT(...))=TRANSPOSE(...)：生成指标匹配矩阵，匹配的指标列标记为1，不匹配为0
• XLOOKUP(B1:B, ...)：按B列日期匹配源数据中对应行的所有指标值
• SUMPRODUCT：将匹配矩阵与对应行的指标值相乘后求和，自动完成多指标累加
• ARRAYFORMULA：让公式一次性作用于整个目标区域，无需逐个单元格填充
• IFERROR：处理空值或匹配失败的情况，返回空白而非错误值

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