请求按FIFO原则将付款金额分配至发票的技术支持

问题背景 #

需要将交易明细中的付款/贷项通知单金额，按照**先进先出（FIFO）**原则分配至对应发票，计算每张发票的剩余未结清金额（样本数据的剩余金额列为预期结果）。

样本交易明细（中文翻译） #

FIFO核心逻辑 #

1. 按过账日期排序所有交易，确保先处理最早发生的发票
2. 用后续的付款/贷项通知单依次冲减最早的未结清发票，直到该发票余额为0，再处理下一张发票
3. 付款/贷项通知单金额不足冲减当前发票时，剩余金额留待后续付款继续冲减；金额超出时，剩余部分自动分配到下一张发票

实现方案 #

方案1：Excel公式实现 #

假设数据位于A2:E12（A=过账日期，B=交易类型，C=单据号，D=金额，E=剩余金额），通过辅助列+公式完成计算：

1. 辅助列F：累计未结清发票余额

   • F2单元格：=IF(B2="发票", D2, IF(B2="贷项通知单", -D2, 0))
   • F3及以下单元格：=IF(OR(B3="发票", B3="贷项通知单"), F2+IF(B3="发票", D3, -D3), F2)
   • 下拉填充至所有行

2. 辅助列G：累计已支付金额

   • G2单元格：=IF(OR(B2="付款", B2="贷项通知单"), -D2, 0)
   • G3及以下单元格：=G2+IF(OR(B3="付款", B3="贷项通知单"), -D3, 0)
   • 下拉填充至所有行

3. 计算剩余金额（E列）

   • 发票行公式（Excel 365动态数组）：
     =MAX(D2 - SUMIFS(-$D$2:$D2, $B$2:$B2, "付款", $A$2:$A2, ">="&A2) - SUMIFS(-$D$2:$D2, $B$2:$B2, "贷项通知单", $A$2:$A2, ">="&A2), 0)
   • 普通Excel版本用SUMPRODUCT：
     =MAX(D2 - SUMPRODUCT(--($B$2:$D2="付款"), --($A$2:$A2>=A2), -$D$2:$D2) - SUMPRODUCT(--($B$2:$D2="贷项通知单"), --($A$2:$A2>=A2), -$D$2:$D2), 0)
   • 付款/贷项通知单行直接填入0

方案2：Python代码实现 #

用Pandas库处理，通过队列维护未结清发票，按FIFO规则逐笔冲减：

import pandas as pd

# 加载样本数据（替换为你的数据源路径）
data = {
    "过账日期": ["13-06-23", "04-07-23", "19-07-23", "02-08-23", "21-08-23", "28-08-23", "15-09-23", "21-09-23", "19-10-23", "18-11-23", "16-12-23"],
    "交易类型": ["发票", "发票", "发票", "付款", "发票", "贷项通知单", "付款", "发票", "发票", "发票", "付款"],
    "单据编号": ["2324PSI2802", "2324PSI3778", "2324PSI4407", "2324BR46", "2324PSI5206", "2324PSC02203", "2324BR03956", "2324PSI5763", "2324PSI6090", "2324PSI6490", "2324BR04870"],
    "金额": [176866.8, 79725, 9835.8, -80000, 611, -44295, -70000, 12563, 7028, 722, -50000]
}

df = pd.DataFrame(data)
# 转换日期格式用于排序
df["过账日期"] = pd.to_datetime(df["过账日期"], format="%d-%m-%y")
df = df.sort_values("过账日期").reset_index(drop=True)

# 初始化未结清发票队列
unsettled_invoices = []
remaining_amounts = []

for _, row in df.iterrows():
    if row["交易类型"] == "发票":
        unsettled_invoices.append({
            "单据编号": row["单据编号"],
            "剩余金额": row["金额"]
        })
        remaining_amounts.append(row["金额"])
    elif row["交易类型"] in ["付款", "贷项通知单"]:
        settle_amount = abs(row["金额"])
        # 按FIFO冲减发票
        for inv in unsettled_invoices:
            if settle_amount <= 0:
                break
            if inv["剩余金额"] > 0:
                deduct = min(inv["剩余金额"], settle_amount)
                inv["剩余金额"] -= deduct
                settle_amount -= deduct
        remaining_amounts.append(0)

# 更新DataFrame的剩余金额列
for idx, row in df.iterrows():
    if row["交易类型"] == "发票":
        for inv in unsettled_invoices:
            if inv["单据编号"] == row["单据编号"]:
                df.at[idx, "剩余金额"] = inv["剩余金额"]
                break
    else:
        df.at[idx, "剩余金额"] = 0

# 转换日期回原格式
df["过账日期"] = df["过账日期"].dt.strftime("%d-%m-%y")
print(df[["过账日期", "交易类型", "单据编号", "金额", "剩余金额"]])

运行代码后将输出与样本一致的剩余金额结果。

内容的提问来源于stack exchange，提问作者Arulbhargav Reddy