如何将库存表中上期CLOS BAL设为后续ITEM的OPEN BAL

原始表格 #

需求说明 #

在上述表格中，CLOS BAL的计算公式为：CLOS BAL = OPEN BAL + IN - OUT。当同一ITEM再次出现时，其OPEN BAL应等于该ITEM上一次的CLOS BAL。

实现方案 #

1. 办公软件（Excel/Google Sheets）实现 #

• OPEN BAL列填充：对于重复出现的ITEM，在对应OPEN BAL单元格输入以下公式（以第7行ITEM B为例，对应单元格B7）：

  =LOOKUP(2,1/($A$1:A6=A7),$E$1:E6)
  

  这个公式会查找当前行之前最后一个相同ITEM的CLOS BAL值，自动填入OPEN BAL列，下拉公式即可批量处理所有重复行。
• CLOS BAL列批量计算：在CLOS BAL列第一行（比如E2）输入公式 =B2+C2-D2，下拉到所有行，自动计算每一行的期末余额。

2. 编程实现（Python示例） #

如果是通过代码处理表格数据，可以用字典跟踪每个ITEM的最新期末余额，自动填充OPEN BAL和CLOS BAL：

# 模拟表格数据
data = [
    ["A", 10, 10, 0, 20],
    ["B", 200, 200, 300, 100],
    ["C", 50, 100, 100, 50],
    ["D", 20, 20, 40, 0],
    ["E", 100, 0, 50, 50],
    ["B", None, None, None, None],
    ["D", None, None, None, None],
    ["E", None, None, None, None],
    ["A", None, None, None, None],
    ["C", None, None, None, None]
]

# 跟踪每个ITEM的最新期末余额
item_latest_balance = {}

for row in data:
    item = row[0]
    # 填充OPEN BAL：如果是初始行则保留原值，否则取上一次的期末余额
    if row[1] is not None:
        open_bal = row[1]
    else:
        open_bal = item_latest_balance.get(item, 0)
        row[1] = open_bal
    
    # 计算并填充CLOS BAL（假设IN和OUT为已知值，若为空可根据实际逻辑调整）
    if row[2] is not None and row[3] is not None:
        clos_bal = open_bal + row[2] - row[3]
        row[4] = clos_bal
    
    # 更新ITEM的最新余额
    if row[4] is not None:
        item_latest_balance[item] = row[4]

# 打印处理后的结果
for row in data:
    print(row)

内容的提问来源于stack exchange，提问作者RT Vision