Excel纯公式实现连续N列求和取最小值 忽略零值计算最优运动分段

适用场景 #

• 跑步、骑行、驾车等行程的逐公里/逐段配速分析
• 需求：从连续排列的分段耗时序列中，自动提取任意指定长度的连续分段最短耗时（最优成绩）
• 约束：无VBA、原生公式实现，支持长距离（超马、百公里骑行等）场景，切换分段长度无需修改公式结构

数据准备规范 #

• 将逐段（默认逐公里）配速/耗时按顺序放在同一行，从A1单元格开始向右排列，未记录/无效的分段单元格填0
• 单独指定一个单元格（示例用H1）存储待计算的分段长度：比如计算2km最优分段填2，计算5km最优分段填5
• 所有耗时值存为Excel可识别的时间格式：输入0:05:40即可表示5分40秒，单元格可设置为mm:ss格式显示为5:40

核心公式 #

动态数组版本（Excel 365/2021及以上版本，推荐） #

直接在结果单元格输入以下公式即可，无需特殊按键：

=MIN(IF(SUBTOTAL(9,OFFSET(A1,0,SEQUENCE(COLUMNS(A1:ZZ1)-H1+1,1,0),1,H1))=0,"9:59:59",SUBTOTAL(9,OFFSET(A1,0,SEQUENCE(COLUMNS(A1:ZZ1)-H1+1,1,0),1,H1))))

公式逻辑说明：

• 自动计算所有可能的连续N段起始位置，无需手动统计分段数量
• 逐段对连续N个耗时单元格求和，得到每个连续分段的总耗时
• 自动跳过包含0值的无效分段（给无效分段赋值一个远大于正常完赛时间的数值9:59:59，不会被最小值函数选中）
• 直接返回所有有效分段的最小耗时，即对应长度的最优分段成绩
• 默认支持最大702个连续分段（覆盖到ZZ列），足够支撑绝大多数长距离行程分析需求，如需更长范围直接修改公式中A1:ZZ1为实际数据范围即可

兼容版本（Excel 2019及更早版本，无动态数组支持） #

输入以下公式后，必须按Ctrl+Shift+Enter三键确认数组公式生效：

=MIN(IF(SUBTOTAL(9,OFFSET(A1,0,ROW(INDIRECT("1:"&COLUMNS(A1:ZZ1)-H1+1))-1,1,H1))=0,"9:59:59",SUBTOTAL(9,OFFSET(A1,0,ROW(INDIRECT("1:"&COLUMNS(A1:ZZ1)-H1+1))-1,1,H1))))

使用注意事项 #

• 仅需修改H1单元格的分段长度数值，即可自动切换计算2km/3km/4km等任意长度的最优分段，无需调整公式结构
• 结果单元格按需设置格式：短距离跑步配速场景设置为mm:ss，长距离骑行/驾车场景设置为[h]:mm:ss即可正常显示
• 如果需要按列存储逐段配速（即每一行存1公里数据），仅需把公式中OFFSET的偏移参数从列偏移改为行偏移即可
• 针对超大数据量（超过200个分段）场景，可替换为MMULT结构的非易失性公式，避免OFFSET自动重算带来的卡顿

示例验证 #

以给出的6公里配速序列（5:40、6:00、5:45、5:55、6:21、6:30）为例：

• H1填2时，返回最优2km分段成绩为11:40
• H1填3时，返回最优3km分段成绩为17:25
• H1填4时，返回最优4km分段成绩为23:20

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