Excel公式优化：动态获取邻近含"SPEED"行的G列单元格

需求回顾 #

当前B22单元格公式为：
=IF(ISBLANK(G22),"",IF(A22="SPEED",0,(86400*(G22-(G3)))))
需要将固定引用的G3替换为A3到当前行范围内，离当前行最近的A列值为"SPEED"对应的G列单元格，完整逻辑：

• 若G22为空，返回空值
• 若G22不为空：
  • 若A22等于"SPEED"，返回0
  • 否则计算86400*(G22 - 最近SPEED对应G列值)

新版Excel（支持XLOOKUP）解决方案 #

替换后的完整公式：
=IF(ISBLANK(G22),"",IF(A22="SPEED",0,86400*(G22-XLOOKUP("SPEED",A$3:A22,G$3:G22,"",0,-1))))

关键部分解释

• A$3:A22：锁定起始行3，下拉公式时范围会自动扩展到当前行上方区域
• -1参数：从当前行往上反向查找，确保返回离当前行最近的"SPEED"匹配项
• 若范围内无"SPEED"，公式返回空值，可根据实际需求调整返回内容

旧版Excel（无XLOOKUP）兼容方案 #

使用LOOKUP函数替代，公式如下：
=IF(ISBLANK(G22),"",IF(A22="SPEED",0,86400*(G22-LOOKUP(2,1/(A$3:A22="SPEED"),G$3:G22))))

关键部分解释

• 1/(A$3:A22="SPEED")：生成数组，匹配"SPEED"的位置为1，不匹配则返回错误值
• LOOKUP(2,...)：自动忽略错误值，定位到最后一个（即最近的）匹配项对应的G列值

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