Excel VBA开发需求：按批次ID与时间戳匹配提取数据

需求概述 #

• 原始数据存储在Raw Data工作表：A列=批次ID（Batch ID），B列=时间戳（Time Stamps，升序排列），C列=状态值
• Batch Number工作表：A1=目标批次ID；A列从A2开始为按1秒递增的时间戳（升序排列）
• 需在Batch Number的B列填充对应值：
  1. 若当前时间戳早于Raw Data中目标批次的所有时间戳，填充!!
  2. 若当前时间戳处于Raw Data中目标批次某两个连续时间戳区间内（含起始，不含结束），填充对应区间起始行的C列状态值

修正后的VBA代码 #

Sub FillBatchStatus()
    Dim wsRaw As Worksheet, wsBatch As Worksheet
    Dim targetBatch As Variant
    Dim rawData As Variant, batchTimes As Variant
    Dim rawRows As Long, batchRows As Long
    Dim i As Long, j As Long
    Dim firstRawTime As Date, lastRawTime As Date
    
    ' 绑定工作表对象
    Set wsRaw = ThisWorkbook.Worksheets("Raw Data")
    Set wsBatch = ThisWorkbook.Worksheets("Batch Number")
    
    ' 获取目标批次ID，校验非空
    targetBatch = wsBatch.Range("A1").Value
    If IsEmpty(targetBatch) Then
        MsgBox "请在Batch Number工作表A1单元格输入目标批次ID", vbExclamation
        Exit Sub
    End If
    
    ' 读取Raw Data全量数据（假设首行为表头）
    rawRows = wsRaw.Cells(wsRaw.Rows.Count, "A").End(xlUp).Row
    rawData = wsRaw.Range("A1:C" & rawRows).Value
    
    ' 筛选目标批次的时间戳与状态值
    Dim filteredTimes As Collection, filteredStatuses As Collection
    Set filteredTimes = New Collection
    Set filteredStatuses = New Collection
    
    For i = 2 To rawRows ' 跳过表头行
        If rawData(i, 1) = targetBatch Then
            filteredTimes.Add rawData(i, 2)
            filteredStatuses.Add rawData(i, 3)
        End If
    Next i
    
    ' 校验是否找到目标批次数据
    If filteredTimes.Count = 0 Then
        MsgBox "Raw Data中未找到批次ID为" & targetBatch & "的数据", vbExclamation
        Exit Sub
    End If
    
    ' 获取Batch Number中待处理的时间戳范围
    batchRows = wsBatch.Cells(wsBatch.Rows.Count, "A").End(xlUp).Row
    If batchRows < 2 Then
        MsgBox "Batch Number工作表A列无需要处理的时间戳数据", vbExclamation
        Exit Sub
    End If
    batchTimes = wsBatch.Range("A2:A" & batchRows).Value
    
    ' 初始化结果数组
    Dim results() As Variant
    ReDim results(1 To UBound(batchTimes, 1), 1 To 1)
    
    ' 提取目标批次的首尾时间戳
    firstRawTime = filteredTimes(1)
    lastRawTime = filteredTimes(filteredTimes.Count)
    
    ' 利用升序特性，用指针法高效匹配时间戳与状态
    j = 1 ' 指向filteredTimes的当前匹配索引
    For i = 1 To UBound(batchTimes, 1)
        Dim currentTime As Date
        currentTime = batchTimes(i, 1)
        
        ' 情况1：早于目标批次所有时间戳
        If currentTime < firstRawTime Then
            results(i, 1) = "!!"
        Else
            ' 移动指针到第一个大于当前时间戳的位置
            Do While j < filteredTimes.Count And filteredTimes(j + 1) <= currentTime
                j = j + 1
            Loop
            ' 情况2：处于区间内（含起始，不含结束）
            results(i, 1) = filteredStatuses(j)
        End If
    Next i
    
    ' 将结果写入Batch Number的B列
    wsBatch.Range("B2:B" & batchRows).Value = results
    
    MsgBox "状态值填充完成", vbInformation
End Sub

代码关键逻辑说明 #

1. 数据预筛选：提前从Raw Data中提取目标批次的时间戳和状态值，避免重复遍历全表，提升效率
2. 高效匹配机制：基于两个表时间戳均为升序的特性，使用指针j跟踪当前匹配的Raw Data行，无需每次从头查找，大幅减少循环次数
3. 区间规则实现：
   • 时间戳早于目标批次第一个时间戳时，直接填充!!
   • 找到最大的Raw Data时间戳≤当前Batch时间戳，取对应状态值，天然满足「含起始，不含结束」的区间要求
4. 异常处理：包含空批次ID、无目标批次数据、无Batch时间戳等场景的提示，避免代码崩溃

测试验证示例 #

测试数据（Raw Data） #

测试数据（Batch Number） #

运行代码后，Batch Number的B列将与预期输出完全一致。

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