如何聚合大型Excel文件行？能否用VBA或跨表无原文件打开实现多条件求和？

Absolutely, you can handle both of your needs with VBA—let’s walk through each part clearly.

1. Using VBA to Implement Multi-Conditional Sum Aggregation #

For grouping rows by 4-5 condition columns and summing 6-7 value columns, using a Dictionary object is super efficient (it avoids redundant loops to match rows). Here's a practical, customizable example:

Sub AggregateWithConditions()
    Dim wsSource As Worksheet, wsResult As Worksheet
    Dim lastRow As Long, i As Long, j As Long
    Dim conditionKey As String
    Dim sumDict As Object
    
    ' Set your source worksheet (where raw data lives)
    Set wsSource = ThisWorkbook.Worksheets("RawData")
    ' Create or reuse a result worksheet for aggregated data
    On Error Resume Next
    Set wsResult = ThisWorkbook.Worksheets("AggregatedResult")
    On Error GoTo 0
    If wsResult Is Nothing Then
        Set wsResult = ThisWorkbook.Worksheets.Add
        wsResult.Name = "AggregatedResult"
    End If
    
    ' Initialize dictionary for grouping
    Set sumDict = CreateObject("Scripting.Dictionary")
    sumDict.CompareMode = vbTextCompare ' Case-insensitive matching
    
    lastRow = wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row
    
    ' Loop through data rows (skip header row, assuming row 1 is headers)
    For i = 2 To lastRow
        ' Combine condition columns into a unique key (adjust columns as needed)
        ' Example: condition columns = A, B, C, D (4 columns)
        conditionKey = wsSource.Cells(i, "A").Value & "|" & _
                       wsSource.Cells(i, "B").Value & "|" & _
                       wsSource.Cells(i, "C").Value & "|" & _
                       wsSource.Cells(i, "D").Value
        
        ' Add new key with initial sum values if it doesn't exist
        If Not sumDict.Exists(conditionKey) Then
            Dim sumArr() As Variant
            ' Initialize array for sum columns (E-K: 7 columns)
            ReDim sumArr(1 To 7)
            For j = 1 To 7
                sumArr(j) = wsSource.Cells(i, "E").Offset(0, j - 1).Value
            Next j
            sumDict.Add conditionKey, sumArr
        Else
            ' Accumulate sums for existing key
            sumArr = sumDict(conditionKey)
            For j = 1 To 7
                sumArr(j) = sumArr(j) + wsSource.Cells(i, "E").Offset(0, j - 1).Value
            Next j
            sumDict(conditionKey) = sumArr
        End If
    Next i
    
    ' Write aggregated data to result sheet
    wsResult.Cells.Clear
    ' Add custom headers (adjust to match your column names)
    wsResult.Cells(1, "A").Value = "Condition1"
    wsResult.Cells(1, "B").Value = "Condition2"
    wsResult.Cells(1, "C").Value = "Condition3"
    wsResult.Cells(1, "D").Value = "Condition4"
    wsResult.Cells(1, "E").Value = "Sum1"
    wsResult.Cells(1, "F").Value = "Sum2"
    wsResult.Cells(1, "G").Value = "Sum3"
    wsResult.Cells(1, "H").Value = "Sum4"
    wsResult.Cells(1, "I").Value = "Sum5"
    wsResult.Cells(1, "J").Value = "Sum6"
    wsResult.Cells(1, "K").Value = "Sum7"
    
    Dim key As Variant, rowNum As Long
    rowNum = 2
    For Each key In sumDict.Keys
        ' Split key back into individual condition values
        Dim conditions() As String
        conditions = Split(key, "|")
        For j = 1 To UBound(conditions) + 1
            wsResult.Cells(rowNum, j).Value = conditions(j - 1)
        Next j
        ' Write summed values
        sumArr = sumDict(key)
        For j = 1 To UBound(sumArr)
            wsResult.Cells(rowNum, "E").Offset(0, j - 1).Value = sumArr(j)
        Next j
        rowNum = rowNum + 1
    Next key
    
    ' Clean up formatting
    wsResult.Rows(1).Font.Bold = True
    wsResult.UsedRange.Columns.AutoFit
    
    Set sumDict = Nothing
    MsgBox "Aggregation done!", vbInformation
End Sub

Just tweak the column references (like "A" or "E") and header names to match your actual file structure.

2. Aggregating Data Without Opening the Original File #

Yes! You can use ADO (ActiveX Data Objects) to connect directly to the Excel file and run SQL aggregation queries—no need to open the large file at all. This is way faster for massive datasets.

Here’s how to do it:

Sub AggregateWithoutOpeningSource()
    Dim conn As Object, rs As Object
    Dim sqlQuery As String
    Dim sourceFilePath As String
    Dim wsResult As Worksheet
    
    ' Set path to your large Excel file (replace with your actual path)
    sourceFilePath = "C:\YourFolder\LargeDataFile.xlsx"
    
    ' Create or reuse result worksheet
    On Error Resume Next
    Set wsResult = ThisWorkbook.Worksheets("AggregatedFromClosedFile")
    On Error GoTo 0
    If wsResult Is Nothing Then
        Set wsResult = ThisWorkbook.Worksheets.Add
        wsResult.Name = "AggregatedFromClosedFile"
    End If
    
    ' Initialize ADO objects
    Set conn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
    
    ' Connection string (adjust for Excel version)
    ' For .xlsx (Excel 2007+)
    conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                            "Data Source=" & sourceFilePath & ";" & _
                            "Extended Properties=""Excel 12.0 Xml;HDR=YES;"""
    ' For .xls (Excel 97-2003)
    ' conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    '                         "Data Source=" & sourceFilePath & ";" & _
    '                         "Extended Properties=""Excel 8.0;HDR=YES;"""
    
    conn.Open
    
    ' Build SQL query: GROUP BY conditions, SUM values
    ' Replace with your actual column names and source sheet name (add $ at the end)
    sqlQuery = "SELECT Condition1, Condition2, Condition3, Condition4," & _
               "SUM(Value1), SUM(Value2), SUM(Value3), SUM(Value4), SUM(Value5), SUM(Value6), SUM(Value7) " & _
               "FROM [RawData$] " & _
               "GROUP BY Condition1, Condition2, Condition3, Condition4"
    
    rs.Open sqlQuery, conn
    
    ' Write results to worksheet
    wsResult.Cells.Clear
    wsResult.Range("A1").CopyFromRecordset rs
    
    ' Add headers (CopyFromRecordset skips them)
    Dim i As Long
    For i = 0 To rs.Fields.Count - 1
        wsResult.Cells(1, i + 1).Value = rs.Fields(i).Name
    Next i
    
    ' Format for readability
    wsResult.Rows(1).Font.Bold = True
    wsResult.UsedRange.Columns.AutoFit
    
    ' Clean up
    rs.Close
    conn.Close
    Set rs = Nothing
    Set conn = Nothing
    
    MsgBox "Aggregation from closed file finished!", vbInformation
End Sub

Quick notes:

• Replace Condition1, Value1, etc., with your actual column names from the source file.
• Replace [RawData$] with your source worksheet name (don’t forget the $ at the end).
• The ACE OLEDB provider is included with most Office installations—if you don’t have it, it’s a free standard component from Microsoft.

Both methods work great: the dictionary approach is flexible if you need extra VBA data manipulation, while the ADO method is perfect for avoiding slow file opens and leveraging SQL’s efficient grouping.

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