基于ID匹配行并排除Shipped，用Excel公式判定订单发货状态

一、Excel公式优化方案 #

1. Excel 365/2021（支持动态数组函数） #

直接用GROUPBY分组计算，一次性输出所有订单ID的状态，无需下拉填充：

=GROUPBY(A:A, B:B, LAMBDA(x, 
  LET(
    filtered, FILTER(x, x<>"Shipped"),
    total, COUNTA(filtered),
    in_stock, COUNTIF(filtered, "In Stock"),
    ratio, in_stock/total,
    SWITCH(
      TRUE,
      total=0, "No Valid Items",
      in_stock=total, "Shippable",
      ratio>0.5, "Shippable but contains OOS Items",
      "Not Shippable"
    )
  ),
  0, 0, TRUE
)

• 逻辑说明：先过滤当前分组中Stock Status为Shipped的记录，统计有效总条数和其中In Stock的数量，再根据占比匹配对应状态；若分组全是Shipped记录，返回"No Valid Items"。

2. 旧版Excel（无动态数组） #

在订单ID对应的状态单元格（比如C2）输入以下公式，下拉填充即可：

=IFERROR(
  LET(
    total, COUNTIFS(A:A, A2, B:B, "<>Shipped"),
    in_stock, COUNTIFS(A:A, A2, B:B, "In Stock"),
    ratio, in_stock/total,
    SWITCH(
      TRUE,
      in_stock=total, "Shippable",
      ratio>0.5, "Shippable but contains OOS Items",
      "Not Shippable"
    )
  ),
  "No Valid Items"
)

• 逻辑说明：用COUNTIFS分别统计当前ID下排除Shipped的总记录数、In Stock的记录数，通过SWITCH判断状态；IFERROR处理全Shipped的异常情况。

二、VBA宏方案（适合批量自动化场景） #

如果需要频繁重复操作，可编写宏实现一键更新：

Sub UpdateOrderStatus()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim idRange As Range
    Dim uniqueIDs As Collection
    Dim id As Variant
    Dim totalItems As Long
    Dim inStockItems As Long
    Dim ratio As Double
    Dim status As String
    
    Set ws = ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    Set idRange = ws.Range("A2:A" & lastRow)
    Set uniqueIDs = New Collection
    
    ' 提取所有唯一订单ID
    On Error Resume Next
    For Each cell In idRange
        uniqueIDs.Add cell.Value, Key:=CStr(cell.Value)
    Next cell
    On Error GoTo 0
    
    ' 遍历ID计算并写入状态
    For Each id In uniqueIDs
        totalItems = Application.WorksheetFunction.CountIfs(ws.Range("A:A"), id, ws.Range("B:B"), "<>Shipped")
        inStockItems = Application.WorksheetFunction.CountIfs(ws.Range("A:A"), id, ws.Range("B:B"), "In Stock")
        
        Select Case True
            Case totalItems = 0
                status = "No Valid Items"
            Case inStockItems = totalItems
                status = "Shippable"
            Case inStockItems / totalItems > 0.5
                status = "Shippable but contains OOS Items"
            Case Else
                status = "Not Shippable"
        End Select
        
        ' 将状态写入C列对应行
        ws.Range("C:C").Replace What:=id, Replacement:=status, LookAt:=xlWhole
    Next id
End Sub

• 使用说明：打开VBA编辑器（Alt+F11），插入模块粘贴代码，回到工作表执行宏即可；注意保存文件为.xlsm格式。

方案选择建议 #

• 优先用公式方案：无需启用宏，操作简单，实时更新数据；Excel 365版本的GROUPBY效率更高，适合大数据量。
• 宏方案适合：需要定期批量处理、或结合其他自动化流程的场景。

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