Excel技术求助：计算列中数值组时间差并置于相邻列

一、Excel 365/2021 动态数组公式方案 #

直接在B2单元格输入以下公式，按回车后公式会自动溢出填充：

=LET(
    data,A:A,
    is_num,ISNUMBER(data),
    group,SCAN(0,is_num,LAMBDA(a,b,IF(b,a+1,0))),
    result,BYROW(group,LAMBDA(g,IF(g=0,"",IF(g=1,XLOOKUP(g,group,data,-1,0,-1)-XLOOKUP(g,group,data,1,0,1),"")))),
    result
)

公式说明： #

• ISNUMBER(data)：标记A列单元格是否为数值型时间（Excel里时间本质是数值）
• SCAN函数：给连续的数值型单元格分配组号，非数值型单元格组号为0
• BYROW+XLOOKUP：仅对每组的第一个单元格，找到该组最后一个时间值并计算首尾差值，非组首单元格留空

二、通用VBA宏方案（适配全版本Excel） #

按下Alt+F11打开VBA编辑器，插入模块后粘贴以下代码，运行宏即可自动计算：

Sub CalculateTimeDiff()
    Dim ws As Worksheet
    Dim lastRow As Long, i As Long, startRow As Long
    Set ws = ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    i = 1
    Do While i <= lastRow
        ' 定位当前组的起始行（首个数值型时间）
        Do While i <= lastRow And Not IsNumeric(ws.Cells(i, "A").Value)
            i = i + 1
        Loop
        startRow = i
        ' 定位当前组的结束行（最后一个数值型时间）
        Do While i <= lastRow And IsNumeric(ws.Cells(i, "A").Value)
            i = i + 1
        Loop
        ' 计算差值并写入组首的B列单元格
        If startRow < i Then
            ws.Cells(startRow, "B").Value = ws.Cells(i - 1, "A").Value - ws.Cells(startRow, "A").Value
            ' 可选：将B列格式设为时间，方便查看
            ws.Cells(startRow, "B").NumberFormat = "hh:mm:ss"
        End If
    Loop
End Sub

操作步骤： #

1. 打开目标Excel文件，确保数据在A列
2. 按Alt+F11进入VBA编辑器，右键点击当前工作簿→插入→模块
3. 粘贴代码后按F5运行，或回到Excel界面通过「开发工具」→「宏」选择运行

注意事项 #

• 计算出的时间差值默认是Excel数值格式，可手动设置B列单元格格式为「时间」（如hh:mm:ss）来直观展示
• 如果A列存在文本格式的时间，需先用TIMEVALUE函数转换为数值型时间后再处理

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