提问人:Boscha 提问时间:9/14/2023 最后编辑:YaroslavmBoscha 更新时间:9/18/2023 访问量:32
使用条件对其他工作簿中的值求和
Sum Value from other workbook with criteria
问:
我想对工作表 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
)
))
答:
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
- 使用整个列 A:C 的查询进行解析,并带有一个参数来对列 C 求和并按列 B 排序:
结果
这应用了@player0在如何在 Google 表格中创建逗号分隔聚合中的答案中描述的逻辑。
评论