如何在大数组中高效查找作业行关联的工序数据？

场景概述 #

使用Microsoft Excel 365和SQL Server 2014，需将分存于两张表的作业明细与对应工序数据，转换为指定格式的Excel表格。当前遍历作业明细时重复查询工序数据的方案，在作业行量大、工序数多（20-30道）时速度极慢，需优化查找逻辑，同时要为工序单元格按工时和完成状态设置条件格式。

作业明细表 #

作业工序表 #

VBA数据结构 #

作业明细存储结构：

Public Type JobStatusDetailLine
    JobNum As String
    AssemblySeq As Integer
    PartNum As String
    ...
End Type

Public JobStatusDetailArr() As JobStatusDetailLine

工序数据存储结构：

Public Type JobStatusOperations
    JobNum As String
    AssemblySeq As Integer
    OpSeq As Integer
    OpCode As String
    Hours As Double
    ...
End Type

Public JobStatusOperationArr() As JobStatusOperations

期望输出表格 #

优化方案 #

1. 预构建工序数据字典索引 #

使用Scripting.Dictionary将工序数据按作业编号|装配编号的组合键分组，遍历作业明细时直接通过键快速定位对应工序，避免重复遍历整个工序数组。

Dim opDict As New Scripting.Dictionary
Dim i As Long

' 加载所有工序到字典
For i = LBound(JobStatusOperationArr) To UBound(JobStatusOperationArr)
    Dim key As String
    key = JobStatusOperationArr(i).JobNum & "|" & JobStatusOperationArr(i).AssemblySeq
    
    If Not opDict.Exists(key) Then
        opDict.Add key, New Collection
    End If
    opDict(key).Add JobStatusOperationArr(i)
Next i

2. 批量写入Excel，减少交互 #

先将所有输出数据存入二维数组，最后一次性写入Excel，避免逐单元格操作的性能损耗。同时预先统计最大工序数，确定输出列数。

' 统计最大工序数
Dim maxOps As Integer
maxOps = 0
For Each key In opDict.Keys
    If opDict(key).Count > maxOps Then maxOps = opDict(key).Count
Next key

' 初始化输出数组
Dim outputArr() As Variant
ReDim outputArr(1 To UBound(JobStatusDetailArr) - LBound(JobStatusDetailArr) + 1, _
                1 To 2 + maxOps)

' 填充数据到数组
Dim rowIdx As Long
rowIdx = 1
For i = LBound(JobStatusDetailArr) To UBound(JobStatusDetailArr)
    outputArr(rowIdx, 1) = JobStatusDetailArr(i).JobNum
    outputArr(rowIdx, 2) = JobStatusDetailArr(i).AssemblySeq
    
    Dim currentKey As String
    currentKey = JobStatusDetailArr(i).JobNum & "|" & JobStatusDetailArr(i).AssemblySeq
    
    If opDict.Exists(currentKey) Then
        Dim ops As Collection
        Set ops = opDict(currentKey)
        Dim j As Long
        For j = 1 To ops.Count
            outputArr(rowIdx, 2 + j) = ops(j).OpCode
            ' 可同步将工时存入隐藏列，用于后续条件格式
        Next j
        ' 填充剩余工序列为"-"
        For j = ops.Count + 1 To maxOps
            outputArr(rowIdx, 2 + j) = "-"
        Next j
    Else
        ' 无工序的行填充"-"
        For j = 1 To maxOps
            outputArr(rowIdx, 2 + j) = "-"
        Next j
    End If
    rowIdx = rowIdx + 1
Next i

' 一次性写入Excel
Range("A2").Resize(UBound(outputArr, 1), UBound(outputArr, 2)).Value = outputArr

3. 条件格式批量设置 #

数据写入完成后，统一为工序单元格设置条件格式，基于隐藏列的工时和完成状态值判断：

With Range("C2").Resize(UBound(outputArr, 1), maxOps)
    ' 条件1：工时>2时填充黄色
    .FormatConditions.Add Type:=xlExpression, Formula1:="=OFFSET(J2,0,COLUMN()-3)>2"
    .FormatConditions(.FormatConditions.Count).SetFirstPriority
    .FormatConditions(1).Interior.Color = RGB(255, 255, 0)
    
    ' 条件2：工序未完成时填充红色（假设状态存于K列）
    .FormatConditions.Add Type:=xlExpression, Formula1:="=OFFSET(K2,0,COLUMN()-3)=""未完成"""
    .FormatConditions(2).Interior.Color = RGB(255, 0, 0)
End With

4. 数据库层面预转换（可选） #

若SQL Server查询效率足够，可直接用PIVOT将工序转列为指定格式，一次性查询后导入Excel，减少VBA处理量：

WITH RankedOps AS (
    SELECT 
        JobNum,
        AssemblySeq,
        OpCode,
        Hours,
        ROW_NUMBER() OVER(PARTITION BY JobNum, AssemblySeq ORDER BY OpSeq) AS OpRank
    FROM 作业工序表
)
SELECT 
    d.JobNum,
    d.AssemblySeq AS 工序编号,
    MAX(CASE WHEN OpRank=1 THEN OpCode ELSE '-' END) AS Op1,
    MAX(CASE WHEN OpRank=2 THEN OpCode ELSE '-' END) AS Op2,
    MAX(CASE WHEN OpRank=3 THEN OpCode ELSE '-' END) AS Op3,
    -- 按需扩展到最大工序数
    MAX(CASE WHEN OpRank=30 THEN OpCode ELSE '-' END) AS Op30
FROM 作业明细表 d
LEFT JOIN RankedOps o ON d.JobNum = o.JobNum AND d.AssemblySeq = o.AssemblySeq
GROUP BY d.JobNum, d.AssemblySeq

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