Excel如何实现每1000行对日期时间值递增10分钟

需求规则 #

• 目标列存储格式为DD-MM-YYYY HH:MM:SS的日期时间值
• 每累计满1000行，日期时间值统一递增10分钟
• 单次递增完成后，后续1000行内数值保持恒定，到达下一个1000行节点时再次触发递增

历史方案问题说明 #

1. TIME函数方案
   原使用公式：

   =TIME(HOUR($L$1),MINUTE($L$1),SECOND($L$1)+INT((ROW()-1/1000)))
   

   缺陷：TIME函数仅能生成时间值，无法携带日期部分，遇到跨天的时间递增场景会直接返回错误结果，不满足日期时间组合值的计算要求。
2. MOD函数方案
   原使用公式：

   =x1+IF(MOD(ROW()-ROW(A$1),1000)=0,10,0)
   

   缺陷有两点：
   • Excel中日期时间以序列值存储，整数1代表1天，直接加10等同于递增10天，而非10分钟
   • 公式逻辑仅在整千行节点触发一次递增，其余行引用上一行值时，若上一行值未被识别为有效日期格式，就会抛出#VALUE!错误，处理15-06-2022 01:00:00时的报错就是格式识别+计算逻辑共同导致的。

正确实现公式 #

Excel内10分钟对应的日期时间序列值为10/(24*60)（即1/144），不需要逐行累加，直接按行所属区块计算固定偏移量即可，不会出现累计误差或格式错误。
假设你的起始日期时间存放在X1单元格，从第1行开始填充数据，在目标列首个单元格输入以下公式后下拉填充即可：

=$X$1 + INT((ROW()-1)/1000) * 10/(24*60)

公式逻辑说明 #

• ROW()-1：计算当前行相对于首行的偏移量，首行偏移量为0
• INT((ROW()-1)/1000)：判断当前行所属的1000行区块，第1-1000行返回0，第1001-2000行返回1，第2001-3000行返回2，以此类推
• 区块序号乘以10分钟对应的序列值，再和起始时间相加，会自动处理跨日、跨月、跨年的日期进位，不会出现计算错误
  如果你的有效数据不是从第1行开始，比如首个有效日期在X2单元格，只需将公式调整为对应起始行即可：

=$X$2 + INT((ROW()-ROW($X$2))/1000) * 10/(24*60)

若公式填充后显示为纯数字，手动将单元格格式设置为DD-MM-YYYY HH:MM:SS即可，不影响实际计算结果。

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