使用条件对其他工作簿中的值求和

Sum Value from other workbook with criteria

提问人:Boscha 提问时间:9/14/2023 最后编辑:YaroslavmBoscha 更新时间:9/18/2023 访问量:32

问:

我想对工作表 A 中工作簿 A 的值求和,但问题是 1 列中工作簿 B 中的名称不会拆分。我使用这个公式来匹配标准并使用拆分,但只有带有“Dian”的单元格才会计算。

=SUM(INDEX(
  IMPORTRANGE("Workbook A", K10 & "!I:I"),
  MATCH(
    1,
    (G12=IMPORTRANGE("Workbook A", K$10 & "!H:H"))*
    (REGEXMATCH(IMPORTRANGE("Workbook A", K$10&"!G:G"),TEXTJOIN("|", TRUE, SPLIT(H12, ", ")))
    ),
    0
  )
))

有什么帮助吗?enter image description here

google-sheets google-sheets-formula index-match

评论


答:

1赞 Tedinoz 9/18/2023 #1

试试这个公式:

=query({
    ARRAYFORMULA(REGEXREPLACE(TRIM(SPLIT(FLATTEN(QUERY(QUERY({
      query({importrange("https://docs.google.com/spreadsheets/d/1phYtxKeqe89llDTNjS0JYJsag7l8VumDeBfQF9Z9uHU","SheetA!A1:C")},"select Col2 where Col2 is not null label Col2 ''")&"×",
      query({importrange("https://docs.google.com/spreadsheets/d/1phYtxKeqe89llDTNjS0JYJsag7l8VumDeBfQF9Z9uHU","SheetA!A1:C")},"select Col1 where Col1 is not null label Col1 ''")&",",
      {SEQUENCE(ROWS(query({importrange("https://docs.google.com/spreadsheets/d/1phYtxKeqe89llDTNjS0JYJsag7l8VumDeBfQF9Z9uHU","SheetA!A1:C")},"select Col2  where Col2 is not null label Col2 ''")),1,2)},
      query({importrange("https://docs.google.com/spreadsheets/d/1phYtxKeqe89llDTNjS0JYJsag7l8VumDeBfQF9Z9uHU","SheetA!A1:C")},"select Col3 where Col3 is not null label Col3 ''")}, 
    "select max(Col2) where Col2 <> ',' group by Col3 pivot Col1"),,9^9)), "×")), ",$", )),
    query({importrange("https://docs.google.com/spreadsheets/d/1phYtxKeqe89llDTNjS0JYJsag7l8VumDeBfQF9Z9uHU","SheetA!A1:C")},"select Sum(Col3)  where Col1 is not null group by Col2 label sum(Col3) ''")
 },
 "Select Col1, Col2, Col3 label Col1 'Category', Col2 'Name', Col3 'Total'")

逻辑

该公式基于如何在 Google 表格中创建逗号分隔聚合?中的答案。在这种情况下,数据是给定的,答案使用两个现有列中的数据 - 。{A2:A&"×", B2:B&",", ROW(A2:A)}

在这种情况下,存在几个显着差异:

  • 数据是从另一个电子表格导入的IMPORTRANGE
    • 因此,每列都使用公式IMPORTRANGE
    • 例如:
      • A2:A&"×"成为importrange("https://docs.google.com/spreadsheets/d/1phYtxKeqe89llDTNjS0JYJsag7l8VumDeBfQF9Z9uHU","SheetA!A1:C")},"select Col2 where Col2 is not null label Col2 ''")&"×"
      • ROW(B2:B)成为{SEQUENCE(ROWS(query({importrange("https://docs.google.com/spreadsheets/d/1phYtxKeqe89llDTNjS0JYJsag7l8VumDeBfQF9Z9uHU","SheetA!A1:C")}
  • 还有第三列要加
    • 使用整个列 A:C 的查询进行解析,并带有一个参数来对列 C 求和并按列 B 排序:IMPORTRANGE
      select Sum(Col3) where Col1 is not null group by Col2

结果

snapshot

这应用了@player0在如何在 Google 表格中创建逗号分隔聚合中的答案中描述的逻辑。