Excel循环逻辑开发：订单需求匹配入库库存获取到货日期及缺料提示

前置准备 #

• 先将Sheet2的全部入库记录按「A列产品编码」升序、「C列预计到货日期」升序排序，确保入库顺序的正确性

方案一：Excel 365/2021 公式方案 #

直接在Sheet1的G2单元格输入以下公式，下拉填充即可：

=LET(
product,D2,
demand,F2,
in_records,FILTER(Sheet2!C:D,Sheet2!A:A=product,"无库存"),
IF(in_records="无库存","库存不足",
   IF(demand=0,"",
      qty_col,INDEX(in_records,0,2),
      date_col,INDEX(in_records,0,1),
      cum_qty,SCAN(0,qty_col,LAMBDA(a,b,a+b)),
      total_qty,MAX(cum_qty),
      IF(total_qty>=demand,
         INDEX(date_col,XMATCH(TRUE,cum_qty>=demand)),
         TEXT(MAX(date_col),"mm/dd/yyyy")&" 库存不足，已分配"&total_qty&"单位"
      )
   )
)

• 如果不需要显示已分配数量，把最后一行的拼接内容改成TEXT(MAX(date_col),"mm/dd/yyyy")&" 库存不足"即可
• 无需求、无可用库存的场景会自动匹配规则返回对应结果

方案二：全版本Excel兼容VBA方案 #

按以下步骤操作即可：

1. 按Alt+F11打开VBA编辑器，点击「插入」-「模块」
2. 将以下代码粘贴到模块中：

Function GetDeliveryDate(product As String, demand As Double) As String
    Dim ws2 As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim cumQty As Double
    Dim totalQty As Double
    Dim latestDate As Date
    
    Set ws2 = ThisWorkbook.Sheets("Sheet2")
    lastRow = ws2.Cells(ws2.Rows.Count, "A").End(xlUp).Row
    cumQty = 0
    totalQty = 0
    latestDate = DateSerial(1900, 1, 1)
    
    ' 无需求直接返回空
    If demand = 0 Then
        GetDeliveryDate = ""
        Exit Function
    End If
    
    ' 遍历同产品入库记录
    For i = 2 To lastRow
        If ws2.Cells(i, "A").Value = product Then
            totalQty = totalQty + ws2.Cells(i, "D").Value
            If ws2.Cells(i, "C").Value > latestDate Then
                latestDate = ws2.Cells(i, "C").Value
            End If
            If cumQty < demand Then
                cumQty = cumQty + ws2.Cells(i, "D").Value
                ' 满足需求直接返回当前日期
                If cumQty >= demand Then
                    GetDeliveryDate = Format(ws2.Cells(i, "C").Value, "mm/dd/yyyy")
                    Exit Function
                End If
            End If
        End If
    Next i
    
    ' 全部遍历完未满足需求
    If totalQty = 0 Then
        GetDeliveryDate = "库存不足"
    Else
        GetDeliveryDate = Format(latestDate, "mm/dd/yyyy") & " 库存不足，已分配" & totalQty & "单位"
    End If
End Function

3. 关闭VBA编辑器，回到Sheet1，在G2单元格输入=GetDeliveryDate(D2,F2)，下拉填充即可

注意事项 #

• 若不需要显示已分配数量，修改VBA最后返回的拼接内容即可
• 每次更新Sheet2的入库记录后，按F9刷新所有计算结果即可

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