Excel中按日期小时时间范围查找时序缺失数据并计算缺失占比
Excel排查小时级时序数据缺失值实操指南
前置规整准备
- 统一原表中Date字段的格式,确保Excel可识别为datetime类型,标准格式参考
yyyy/mm/dd hh:mm,避免文本格式导致的计算错位。 - 按Location维度拆分数据组:不同站点的时间覆盖范围存在差异,后续缺失排查需单独计算,不可跨站点混合匹配时间轴。
生成无间断小时级基准时间轴
排查缺失的核心逻辑是用“应有的连续全量时间点”对比“实际存在的时间点”,差值就是缺失部分:
- 针对单个Location,先提取该组的最早时间、最晚时间作为基准轴的起止范围。
- 生成连续小时序列:
- Excel 365/2021及以上版本,直接用内置
SEQUENCE函数批量生成:
公式参考:=SEQUENCE(INT((最晚时间单元格 - 最早时间单元格)*24)+1, 1, 最早时间单元格, 1/24)
公式说明:1/24代表1小时的时间步长,生成的序列会从最早时间开始,每隔1小时递推直到最晚时间,无任何间断。 - 低版本无
SEQUENCE函数的Excel,手动在辅助列填第一个起始时间,选中单元格下拉填充,在填充选项中选择“按小时序列填充”,拉到对应最晚时间的位置即可生成全量序列。
- Excel 365/2021及以上版本,直接用内置
- 给生成的基准时间列匹配对应的Location值,和原表的分组维度保持对齐。
定位离散/连续型缺失时间点
- 用匹配公式标记缺失位置:在基准时间列旁的辅助列写入判断公式:
=ISNA(MATCH(当前基准时间单元格&当前Location单元格, 原表Date列&原表Location列, 0))
公式返回TRUE即代表原数据中不存在该时间点的记录,为缺失值。 - 筛选所有返回
TRUE的行,可快速识别两类缺失场景:- 离散单点缺失:比如零散缺失2000/1/1 03:00、04:00两个时间点,可直接通过筛选结果定位到具体时间。
- 连续区间缺失:比如整段缺失2020/12/29 01:00到2020/12/31 21:00的记录,由于相邻缺失点的时间差为固定1小时,直接读取筛选结果的首行、末行时间,即可确定整个连续缺失段的起止范围,无需逐行计数。
- 完成缺失轴补全:把筛选出的所有缺失时间点行,按原表的四列结构复制到原数据末尾,对应的Data_1、Data_2字段统一填充为
NA,就构建好了无间断的完整小时级时间轴,可直接导出用于后续R/Python的缺失值插补。
计算缺失值占比
- 单Location缺失占比公式:
=COUNTIF(缺失标记辅助列范围, TRUE)/ 该Location全量基准时间点总数
公式分子为该站点所有缺失的小时点数量,分母为该站点从早到晚应有的总小时数,将单元格格式设置为百分比即可直观展示占比。 - 全局数据集缺失占比:将所有Location的缺失小时数求和,除以所有Location的应总小时数,即可得到全数据集的整体缺失率。
操作提示:如果使用低版本Excel运行匹配类数组公式,写入公式后需按下Ctrl+Shift+Enter完成数组运算的确认,避免出现匹配逻辑报错
内容的提问来源于stack exchange,提问作者FrahChan04
相关产品推荐
相关产品推荐

