Excel中仅针对可见单元格优化VLOOKUP对比性能的方案咨询

针对你遇到的公式性能问题，这里提供三个实用方案，从低性能消耗到公式优化依次说明：

方案一：Power Query（推荐，适合大数据集） #

Power Query是Excel内置的高效数据处理工具，完全规避公式重复计算的性能问题，还能自动化流程：

1. 导入筛选后的可见数据：
   • 对Main data和Main data control工作表，选中表头及可见数据区域（可通过「开始→查找和选择→定位条件→可见单元格」快速选中）。
   • 点击「数据→从表格/区域」，勾选「我的表格有标题」，进入Power Query编辑器。
2. 合并查询并对比差异：
   • 新建空白查询，点击「合并查询→将查询作为新查询合并」，选择两个导入的表，以A列（唯一标识）为合并键，选择「完全外部」合并类型。
   • 展开合并后的Control表字段，添加自定义列对比对应字段，例如：

     if [Main data.Department] = [Main data control.Department] then null else [Main data.Department]
     

     重复此操作处理所有需要检查的字段。
3. 加载结果到Change表：
   • 清理不需要的列，点击「关闭并上载」，将结果加载到Change工作表。后续只需刷新查询即可，无需手动维护公式。

方案二：VBA宏批量处理（精准控制可见单元格） #

既然你已经用宏复制A列内容，可以扩展宏完成对比填充，直接操作单元格，性能远优于公式：

Sub UpdateChangeSheet()
    Dim wsMain As Worksheet, wsControl As Worksheet, wsChange As Worksheet
    Dim rngMainVis As Range, rngControl As Range
    Dim cell As Range, matchCell As Range
    Dim checkCols As Integer, i As Integer
    
    ' 初始化工作表对象
    Set wsMain = ThisWorkbook.Worksheets("Main data")
    Set wsControl = ThisWorkbook.Worksheets("Main data control")
    Set wsChange = ThisWorkbook.Worksheets("Change")
    
    ' 获取Main data中A列的可见数据（跳过表头）
    Set rngMainVis = wsMain.Range("A2:A" & wsMain.Cells(wsMain.Rows.Count, "A").End(xlUp).Row).SpecialCells(xlCellTypeVisible)
    ' 获取Main data control的可见数据区域（用于匹配）
    Set rngControl = wsControl.Range("A1:Z" & wsControl.Cells(wsControl.Rows.Count, "A").End(xlUp).Row).SpecialCells(xlCellTypeVisible)
    
    ' 遍历Change表的A列数据
    checkCols = 8 ' 当前需要检查的列数，新增列时修改此值即可
    For Each cell In wsChange.Range("A2:A" & wsChange.Cells(wsChange.Rows.Count, "A").End(xlUp).Row)
        ' 在Control表的可见区域匹配当前A列值
        Set matchCell = rngControl.Columns(1).Find(cell.Value, LookIn:=xlValues, LookAt:=xlWhole)
        
        If Not matchCell Is Nothing Then
            ' 对比每一列，有差异则写入Main data的内容
            For i = 1 To checkCols
                If wsMain.Cells(cell.Row - 1, i + 1).Value <> matchCell.Offset(0, i).Value Then
                    wsChange.Cells(cell.Row, i + 1).Value = wsMain.Cells(cell.Row - 1, i + 1).Value
                Else
                    wsChange.Cells(cell.Row, i + 1).Value = ""
                End If
            Next i
        Else
            ' Control表无匹配项，直接写入Main data内容
            For i = 1 To checkCols
                wsChange.Cells(cell.Row, i + 1).Value = wsMain.Cells(cell.Row - 1, i + 1).Value
            Next i
        End If
    Next cell
    
    MsgBox "差异更新完成！"
End Sub

• 使用说明：运行宏前确保Main data和Main data control已按日期筛选完成；新增检查列时，只需修改checkCols的值。

方案三：公式优化（适合不想用工具/宏的场景） #

如果坚持用公式，通过减少重复查找来提升性能：

1. 用辅助列减少重复查询：
   • 在Change表新增辅助列（如J列），输入公式：

     =XLOOKUP(A2,'Main data control'!A:A,'Main data control'!B:I,"",0)
     

     （Excel 365支持动态数组，一次返回所有需要对比的字段；非365版本可拆分多个辅助列，每个列用INDEX+MATCH）
2. 简化对比公式：
   • B列公式修改为：

     =IF(XLOOKUP(A2,'Main data'!A:A,'Main data'!B:B,"",0)=J2,"",XLOOKUP(A2,'Main data'!A:A,'Main data'!B:B,"",0))
     

     其他列同理，将J2替换为对应辅助列单元格。这样每个行仅执行2次查询，而非每列2次。

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