求助：带多条件计算Google Sheet中借贷金额差值

问题核心 #

• 贷方记录的ID字段可能包含多个编号（如逗号分隔），每条记录对应一笔贷方金额
• 借方记录的ID字段仅对应单个编号，每条记录对应一笔借方金额
• 需要按单个ID计算：贷方金额总和 - 借方金额总和

分步解法 #

1. 生成所有唯一ID列表

在空白列（比如F列）输入公式，提取贷方和借方中所有不重复的ID：

=UNIQUE(FLATTEN(SPLIT(Sheet1!A2:A, ","), Sheet1!D2:D))

• 替换Sheet1!A2:A为你的贷方ID列范围，Sheet1!D2:D为借方ID列范围
• 如果ID用其他分隔符（如顿号），把公式中的逗号替换成对应符号

2. 计算每个ID的差值

在结果列（比如G列，对应F2的位置）输入公式，批量计算所有ID的差值：

=ARRAYFORMULA(IFERROR(
  VLOOKUP(F2:F, QUERY(SPLIT(FLATTEN(IF(SPLIT(Sheet1!A2:A, ",")="",,Sheet1!A2:A&"|"&Sheet1!B2:B)), "|"), "select Col1, sum(Col2) group by Col1"), 2, 0)
  -
  VLOOKUP(F2:F, QUERY(Sheet1!D2:E, "select Col1, sum(Col2) group by Col1"), 2, 0),
0))

公式说明：

• 第一部分：拆分贷方的多ID记录，按ID分组计算贷方总金额
• 第二部分：按ID分组计算借方总金额
• 用VLOOKUP匹配对应ID的金额，做减法得到差值
• ARRAYFORMULA实现批量计算，IFERROR处理无匹配ID的情况，返回0

注意事项 #

• 确保金额列（贷方B列、借方E列）为数值格式，避免计算错误
• 如果数据范围有变动，同步调整公式中的列范围引用

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