Excel中如何逐次扣减同物品不同单元格数量并跟踪库存

规则示例截图

现有表格规则 #

• 收货记录写在G列，同时填写H列的实际收货数量，关联对应OrderID
• 发货记录写在A列，对应发货数量填在B列

目标效果 #

每次录入发货记录时，B列的发货数量自动从H列对应同物品的最早一批非零收货余量中逐次扣减，扣完上一批再扣下一批
对应逻辑参考C++链表写法的伪代码如下：

item = $A2;
While(item != blank){
 If(QuantityReceived > 0 && item == ItemReceived)
  QuantityReceived--;  // 默认单次发货数量为1，每次扣1
 else {
  ItemReceived = ItemReceived -> next;
  QuantityReceived = QuantityReceived -> next;  
 }
ItemReceived = $G2;
QuantityReceived = $H2;
item = item -> next;
}

你要的就是库存按收货批次先进先出扣减的逻辑，不用硬套链表写法，两种现成方法直接用就行：

方案1：公式实现（适合365/2021及以上版本Excel，零代码） #

不用写脚本，用累计发货量匹配批次就能实现自动扣减，完全符合「先扣最早收货的非零余量，扣完再扣下一批」的要求：

1. 在G、H列（收货记录列）旁边加个I列当辅助列，用来统计每个收货批次已经扣掉的发货量，I2单元格输入下面公式，下拉填充整列就行：

=MAX(0, MIN(H2, SUMIF(A:A,G2,B:B) - SUM(I$1:I1)))

2. 每个批次的实际剩余库存直接算H2-I2就行。只要你在A列录入发货物品、B列填发货数量，公式会自动从最早的同物品收货批次开始扣，前一批扣完自动顺延到下一批。
   如果你的发货数量固定是1，把公式里的SUMIF(A:A,G2,B:B)换成COUNTIF(A:A,G2)即可。

方案2：VBA自动扣减（适合需要直接把余量写进单元格，不想留公式的场景） #

如果不想保留公式，希望录完发货记录H列余量自动更新成固定值，用工作表Change事件就能实现。按Alt+F11打开VBA编辑器，左侧双击你要使用的工作表名称，把下面代码粘进去保存就行：

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim shipArea As Range, cel As Range
    Dim item As String, qty As Long, deduct As Long
    Dim lastRecvRow As Long, i As Long
    
    ' 只监控A、B列的发货录入操作
    Set shipArea = Intersect(Target, Me.Range("A:B"))
    If shipArea Is Nothing Then Exit Sub
    
    Application.EnableEvents = False
    For Each cel In Intersect(shipArea, Me.Columns(1)).Cells
        If cel.Value <> "" Then
            item = cel.Value
            ' 取对应B列的发货数量，固定发1的话直接把右边改成1
            qty = cel.Offset(0, 1).Value
            lastRecvRow = Me.Cells(Me.Rows.Count, "G").End(xlUp).Row
            
            ' 从第2行开始遍历收货记录，找同物品的非零余量扣减
            For i = 2 To lastRecvRow
                If qty <= 0 Then Exit For
                If Me.Cells(i, "G").Value = item And Me.Cells(i, "H").Value > 0 Then
                    deduct = Application.Min(qty, Me.Cells(i, "H").Value)
                    Me.Cells(i, "H").Value = Me.Cells(i, "H").Value - deduct
                    qty = qty - deduct
                End If
            Next i
        End If
    Next cel
    Application.EnableEvents = True
End Sub

提醒：用VBA的话操作前先备份表格，扣减是直接改单元格值，没法撤销。

内容的提问来源于stack exchange，提问作者Vittorio A.