Excel表格多条件筛选及操作员订单处理次数统计需求问询

Got it, let's combine your requirements into a single, streamlined VBA macro that works directly in your existing worksheet. This will handle both the operator order counting (in columns Z-AD) and the date-based order summary (in columns X-Y) without needing to create new sheets.

Here's the full code, with comments explaining each part:

Sub OperatorOrderTrackingAndDateSummary()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim dataRange As Range
    Dim uniqueOperatorEntries As Collection
    Dim uniqueDateEntries As Collection
    Dim i As Long
    Dim key As String
    Dim dateVal As Date
    Dim outputRowZ As Long, outputRowX As Long
    
    ' Set the worksheet to work with (change "Sheet1" to your actual sheet name if needed)
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    ' Define the full data range (assuming headers are in row 1, data from row 2 to last row, columns A-M)
    Set dataRange = ws.Range("A2:M" & lastRow)
    
    ' Initialize collections to track unique entries
    Set uniqueOperatorEntries = New Collection
    Set uniqueDateEntries = New Collection
    
    ' First pass: capture all unique combinations
    For i = 1 To dataRange.Rows.Count
        ' Strip time from date (only keep the date part)
        dateVal = Int(dataRange.Cells(i, 1).Value) ' Column A = DATE
        Dim operatorName As String: operatorName = dataRange.Cells(i, 5).Value ' Column E = OPERATOR
        Dim line As String: line = dataRange.Cells(i, 4).Value ' Column D = LINE
        Dim orderNum As String: orderNum = dataRange.Cells(i, 6).Value ' Column F = ORDER NUM.
        
        ' Key for operator-line-date-order (to track unique orders per operator/line/date)
        key = operatorName & "|" & line & "|" & Format(dateVal, "mm/dd/yyyy") & "|" & orderNum
        On Error Resume Next ' Ignore duplicates
        uniqueOperatorEntries.Add key, key
        On Error GoTo 0
        
        ' Key for date-order (to track unique orders per date)
        key = Format(dateVal, "mm/dd/yyyy") & "|" & orderNum
        On Error Resume Next
        uniqueDateEntries.Add key, key
        On Error GoTo 0
    Next i
    
    ' --------------------------
    ' Task 1: Operator Order Count (Columns Z-AD)
    ' --------------------------
    outputRowZ = 2 ' Start below header row
    ' Add headers
    ws.Range("Z1").Value = "OPERATOR"
    ws.Range("AA1").Value = "LINE"
    ws.Range("AB1").Value = "DATE"
    ws.Range("AC1").Value = "ORDER NUM."
    ws.Range("AD1").Value = "PROCESS COUNT"
    
    ' Use a dictionary to count occurrences of each unique operator-line-date-order
    Dim operatorCountDict As Object
    Set operatorCountDict = CreateObject("Scripting.Dictionary")
    
    ' Second pass: count how many times each unique entry appears
    For i = 1 To dataRange.Rows.Count
        dateVal = Int(dataRange.Cells(i, 1).Value)
        operatorName = dataRange.Cells(i, 5).Value
        line = dataRange.Cells(i, 4).Value
        orderNum = dataRange.Cells(i, 6).Value
        key = operatorName & "|" & line & "|" & Format(dateVal, "mm/dd/yyyy") & "|" & orderNum
        
        If operatorCountDict.Exists(key) Then
            operatorCountDict(key) = operatorCountDict(key) + 1
        Else
            operatorCountDict(key) = 1
        End If
    Next i
    
    ' Write the results to columns Z-AD
    For Each key In operatorCountDict.Keys
        Dim parts As Variant
        parts = Split(key, "|")
        
        ws.Cells(outputRowZ, "Z").Value = parts(0) ' Operator name
        ws.Cells(outputRowZ, "AA").Value = parts(1) ' Production line
        ws.Cells(outputRowZ, "AB").Value = CDate(parts(2)) ' Date (formatted without time)
        ws.Cells(outputRowZ, "AC").Value = parts(3) ' Order number
        ws.Cells(outputRowZ, "AD").Value = operatorCountDict(key) ' Number of times processed
        
        outputRowZ = outputRowZ + 1
    Next key
    
    ' Format the date column for readability
    ws.Range("AB2:AB" & outputRowZ - 1).NumberFormat = "mm/dd/yyyy"
    
    ' --------------------------
    ' Task 2: Date-Based Order Summary (Columns X-Y)
    ' --------------------------
    outputRowX = 2 ' Start below header row
    ' Add headers
    ws.Range("X1").Value = "DATE"
    ws.Range("Y1").Value = "UNIQUE ORDERS COUNT"
    
    Dim dateCountDict As Object
    Set dateCountDict = CreateObject("Scripting.Dictionary")
    
    ' Count unique orders per date
    For Each key In uniqueDateEntries
        parts = Split(key, "|")
        Dim dateKey As String: dateKey = parts(0)
        
        If dateCountDict.Exists(dateKey) Then
            dateCountDict(dateKey) = dateCountDict(dateKey) + 1
        Else
            dateCountDict(dateKey) = 1
        End If
    Next key
    
    ' Write the results to columns X-Y
    For Each dateKey In dateCountDict.Keys
        ws.Cells(outputRowX, "X").Value = CDate(dateKey)
        ws.Cells(outputRowX, "Y").Value = dateCountDict(dateKey)
        outputRowX = outputRowX + 1
    Next dateKey
    
    ' Format the date column
    ws.Range("X2:X" & outputRowX - 1).NumberFormat = "mm/dd/yyyy"
    
    ' Auto-fit columns to make the data easier to read
    ws.Range("X:AD").EntireColumn.AutoFit
    
    MsgBox "Done! Check columns X-Y for date-wise unique order counts, and Z-AD for operator order details.", vbInformation
End Sub

How to use this: #

1. Open your Excel workbook, go to the Developer tab (enable it if you don't see it: File > Options > Customize Ribbon > Check Developer).
2. Click Visual Basic (or press Alt+F11).
3. Right-click your workbook in the Project Explorer > Insert > Module.
4. Paste the code above into the module.
5. Adjust the worksheet name (Set ws = ThisWorkbook.Worksheets("Sheet1")) to match your actual sheet name if needed.
6. Adjust column indices if your data is in different columns (e.g., if ORDER NUM is column G instead of F, change dataRange.Cells(i,6) to dataRange.Cells(i,7)).
7. Press F5 to run the macro, or go back to Excel and run it from the Developer tab's Macros menu.

Key features that meet your requirements: #

• Ignores the time component of dates using Int(dateVal) to focus on the date only.
• Prioritizes order number filtering by including it in the unique keys for both summaries.
• Works entirely in your existing worksheet (no new sheets created).
• Provides two clear outputs:
  • Columns X-Y: Each date with the count of unique orders processed that day.
  • Columns Z-AD: A detailed breakdown of each operator, line, date, order number, and how many times that combination was processed.

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