Excel如何计算跨列金额移动（排除新增/移除金额）

实现逻辑 #

核心计算规则为：仅统计同ID下跨列转移的金额，排除ID总余额变动对应的新增/移除金额。其中前移指金额从列号更大的列转移到列号更小的列，后移相反。

操作步骤 #

1. 数据整理
   将上周、本周数据分别存入两个Excel工作表，统一结构：A列为ID，B_{E列依次对应列1}列4，表头在第1行，ID为a_{f的数据放在第2}7行。
2. 单行前移金额计算
   选中空白列第2行（比如F2），输入如下公式后下拉填充所有ID行：

=LET(
diff, Sheet2!B2:E2 - Sheet1!B2:E2,
col_num, COLUMN(Sheet2!B2:E2),
in_flow, FILTER(HSTACK(diff, col_num), diff>0, {0,0}),
out_flow, FILTER(HSTACK(diff, col_num), diff<0, {0,0}),
IFERROR(SUM(BYROW(in_flow, LAMBDA(x, SUM(BYROW(out_flow, LAMBDA(y, IF(INDEX(x,2)<INDEX(y,2), MIN(INDEX(x,1), -INDEX(y,1)), 0)))))),0)

对F列求和即可得到总前移金额。
3. 单行后移金额计算
选中另一空白列第2行（比如G2），输入如下公式后下拉填充所有ID行：

=LET(
diff, Sheet2!B2:E2 - Sheet1!B2:E2,
col_num, COLUMN(Sheet2!B2:E2),
in_flow, FILTER(HSTACK(diff, col_num), diff>0, {0,0}),
out_flow, FILTER(HSTACK(diff, col_num), diff<0, {0,0}),
IFERROR(SUM(BYROW(in_flow, LAMBDA(x, SUM(BYROW(out_flow, LAMBDA(y, IF(INDEX(x,2)>INDEX(y,2), MIN(INDEX(x,1), -INDEX(y,1)), 0)))))),0)

对G列求和即可得到总后移金额。

低版本Excel适配 #

如果你的Excel版本不支持LET/LAMBDA等新函数，可通过辅助列实现：

• 逐行计算每列的「本周值-上周值」差值
• 差值为正的是流入列，差值为负的是流出列
• 逐对匹配流入、流出列，按列号大小判断转移方向，取流入金额、流出绝对值的较小值计入对应方向的总金额即可
  用你提供的示例数据验证，最终计算结果为前移总金额700、后移总金额600，和预期完全一致。

内容的提问来源于stack exchange，提问作者Myles Collier