Google Sheets多标识（订单号/发票号）利润计算函数优化求助

核心需求 #

处理订单号、发票号均可能为合并格式（用/分隔）的场景，按出现顺序匹配对应贷方金额，利润=借方金额-匹配到的贷方金额总和。

公式解决方案（单个单元格） #

假设：

• 贷方数据：发票号列K，贷方金额列L
• 借方数据：订单号列C，借方金额列G，利润列从H3开始输入

在H3输入以下公式，下拉填充至所有行：

=G3 - SUM(IFERROR(INDEX(FLATTEN(IF(SPLIT(K:K,"/")<>"", L:L, "")), MATCH(SPLIT(C3,"/")&SEQUENCE(1,COUNTA(SPLIT(C3,"/"))), FLATTEN(SPLIT(K:K,"/"))&COUNTIFS(FLATTEN(SPLIT(K:K,"/")), FLATTEN(SPLIT(K:K,"/")), ROW(FLATTEN(SPLIT(K:K,"/"))), "<="&ROW(FLATTEN(SPLIT(K:K,"/")))), 0)), 0))

公式说明 #

1. 扁平化贷方数据：

   • FLATTEN(SPLIT(K:K,"/"))：将所有合并的发票号拆分为单个编号，形成连续的编号列表
   • FLATTEN(IF(SPLIT(K:K,"/")<>"", L:L, ""))：对应每个拆分后的发票号，生成对应的贷方金额列表（合并发票号拆分后，每个子编号共享原行的贷方金额）

2. 匹配逻辑：

   • SPLIT(C3,"/")：拆分当前行的订单号为单个编号
   • SEQUENCE(1,COUNTA(SPLIT(C3,"/")))：生成1到拆分数量的序列，标记每个编号在当前订单中的出现顺序
   • COUNTIFS(...)：计算扁平化发票号列表中每个编号的累计出现次数（例如A001第1次出现标记为1，第2次标记为2）
   • MATCH(...)：将订单拆分后的「编号+出现顺序」与扁平化发票号的「编号+累计出现次数」匹配，找到对应贷方金额的位置
   • INDEX(...)：取出匹配到的贷方金额，求和后用借方金额减去该总和得到利润

3. 错误处理：IFERROR(..., 0)确保无匹配项时按0计算，避免公式报错

批量数组公式（一次性处理所有行） #

若需批量计算，在H3输入以下数组公式（无需下拉，自动填充所有行）：

=ARRAYFORMULA(IF(C3:C="", "", G3:G - SUMIF(ROW(FLATTEN(SPLIT(K:K,"/"))), MATCH(FLATTEN(SPLIT(C3:C,"/"))&COUNTIFS(FLATTEN(SPLIT(C3:C,"/")), FLATTEN(SPLIT(C3:C,"/")), ROW(FLATTEN(SPLIT(C3:C,"/"))), "<="&ROW(FLATTEN(SPLIT(C3:C,"/")))), FLATTEN(SPLIT(K:K,"/"))&COUNTIFS(FLATTEN(SPLIT(K:K,"/")), FLATTEN(SPLIT(K:K,"/")), ROW(FLATTEN(SPLIT(K:K,"/"))), "<="&ROW(FLATTEN(SPLIT(K:K,"/")))), 0), FLATTEN(IF(SPLIT(K:K,"/")<>"", L:L, "")))))

全局先进先出匹配方案（脚本实现） #

若需实现全局先进先出匹配（即每个订单的编号优先匹配未被之前订单使用过的贷方记录），公式无法满足，可借助Apps Script实现，核心逻辑：

1. 遍历所有借方订单，拆分每个订单的编号列表
2. 遍历贷方数据，拆分每个发票号的编号列表并记录对应金额
3. 按顺序将订单编号与贷方编号一一匹配，标记已匹配的贷方记录
4. 计算每个订单的贷方金额总和，最终得出利润

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