如何用Openpyxl高效处理Excel？35万行数据匹配优化需求

Alright, let's fix that slow performance issue for your 350k-row Excel task. Row-by-row cell operations are the biggest bottleneck here—so we need to shift to bulk processing approaches that minimize interactions with the Excel worksheet itself. Here are your best options, ranked by ease and speed:

1. Use Excel Built-in Functions (Fastest, No Code Needed) #

This is the quickest solution if you don't need to automate this workflow repeatedly. We'll use XLOOKUP combined with a check for the first column of your Dictionary sheet:

• In the first cell of the column next to your Data (say, cell B1), enter this formula:

  =IFERROR(XLOOKUP(A1, Dictionary!B:Z, Dictionary!A:A, "", 0), IF(ISNUMBER(MATCH(A1, Dictionary!A:A, 0)), "", ""))
  

• Breakdown of the formula:
  • XLOOKUP(A1, Dictionary!B:Z, Dictionary!A:A, "", 0): Looks for the value in A1 across columns B to Z of the Dictionary sheet. If found, it returns the corresponding value from column A.
  • The IFERROR wraps this to handle cases where the value isn't in columns B-Z. We then check if the value exists in Dictionary's column A with MATCH—if it does, we return an empty string; if not, also empty (per your requirements).
• Once you enter the formula, double-click the fill handle at the bottom-right of cell B1 to auto-fill it down all 350k rows. After that, you can copy the column and paste it as values if you don't want the formulas to stay.

2. Optimized VBA Code (If Automation Is Required) #

If you need to run this process regularly, a VBA script using arrays and dictionaries will blow away your old row-by-row code. The key here is reading all data into memory first, processing it there, then writing back once—no repeated cell access.

Here's the optimized script:

Sub FastDictionaryMatch()
    Dim wsData As Worksheet, wsDict As Worksheet
    Dim dataArr As Variant, dictArr As Variant
    Dim matchMap As Object, firstColSet As Object
    Dim i As Long, j As Long
    
    ' Disable Excel features that slow down scripts
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
        .Calculation = xlCalculationManual
    End With
    
    ' Set up worksheet references
    Set wsData = ThisWorkbook.Worksheets("Data")
    Set wsDict = ThisWorkbook.Worksheets("Dictionary")
    
    ' Initialize dictionaries for fast lookups
    Set matchMap = CreateObject("Scripting.Dictionary")
    Set firstColSet = CreateObject("Scripting.Dictionary")
    
    ' Load entire Dictionary sheet into memory
    dictArr = wsDict.UsedRange.Value
    
    ' Populate first column set (to check if value is in Dictionary's first column)
    For i = LBound(dictArr, 1) To UBound(dictArr, 1)
        firstColSet(dictArr(i, 1)) = True
    Next i
    
    ' Populate match map: key = value from Dictionary's columns 2+, value = corresponding column A value
    For i = LBound(dictArr, 1) To UBound(dictArr, 1)
        For j = 2 To UBound(dictArr, 2)
            ' Only add if the key doesn't exist (keep first occurrence)
            If Not matchMap.Exists(dictArr(i, j)) Then
                matchMap(dictArr(i, j)) = dictArr(i, 1)
            End If
            ' Remove the If check above if you want to keep the LAST occurrence instead
        Next j
    Next i
    
    ' Load Data column into memory
    dataArr = wsData.Range("A1:A" & wsData.Cells(wsData.Rows.Count, "A").End(xlUp).Row).Value
    ' Expand array to hold results in the second column
    ReDim Preserve dataArr(1 To UBound(dataArr, 1), 1 To 2)
    
    ' Process each value in the Data array
    For i = LBound(dataArr, 1) To UBound(dataArr, 1)
        If matchMap.Exists(dataArr(i, 1)) Then
            ' Value found in Dictionary's non-first columns: write the corresponding A column value
            dataArr(i, 2) = matchMap(dataArr(i, 1))
        Else
            ' Value either in Dictionary's first column or not present: leave empty
            dataArr(i, 2) = ""
        End If
    Next i
    
    ' Write the processed array back to the Data sheet in one go
    wsData.Range("A1:B" & UBound(dataArr, 1)).Value = dataArr
    
    ' Restore Excel settings
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .Calculation = xlCalculationAutomatic
    End With
    
    ' Clean up objects
    Set matchMap = Nothing
    Set firstColSet = Nothing
End Sub

• Why this works:
  • Arrays let us work with all data in memory instead of reading/writing each cell individually (cell IO is 1000x slower than memory operations).
  • Scripting.Dictionary provides O(1) lookup time, so checking if a value exists takes almost no time even for 350k rows.

3. Power Query (Best for Repeatable, No-Code Bulk Processing) #

Power Query is Excel's built-in tool for handling large datasets efficiently. It's perfect if you want a visual, maintainable workflow without formulas or VBA:

1. Go to the Data tab > Get Data > From File > From Excel Workbook (select your current workbook).
2. Select both the Data and Dictionary tables, then click Load To > Only Create Connections.
3. First, prep the Dictionary table:
   • Open the Dictionary query in the Power Query Editor: Data > Queries & Connections > Right-click Dictionary > Edit.
   • Select the first column, then go to Transform > Unpivot Columns > Unpivot Other Columns. This turns all non-first columns into rows with a Value column.
   • Remove duplicates from the Value column (right-click Value > Remove Duplicates) to avoid multiple matches.
   • Close and load the query back as a connection.
4. Merge the Data and preped Dictionary queries:
   • Go to Data > Get Data > Combine Queries > Merge.
   • Select the Data query as the first table, pick your data column. Select the preped Dictionary query as the second table, pick the Value column. Choose Left Outer join.
5. Expand the merged column to get the first column value from Dictionary.
6. Add a custom column: if the expanded value is not null, use it; else, leave it blank.
7. Close and load the result back to Excel (replace your original Data table or create a new one).

This approach handles large datasets seamlessly because Power Query processes data in bulk, not row-by-row.

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