如何依据文件名时间戳规律补全缺失文件条目并自动填充NaN

以下提供两种实现方式，均能满足补全缺失时间戳标注NaN、保留文件大小高亮的需求：

方法一：Python实现（推荐，适合时间跨度大、数据量多的场景） #

实现步骤： #

• 安装必要依赖：pip install pandas openpyxl
• 按如下代码逻辑处理即可：

import pandas as pd

# 1. 读取你已导出的现有文件Excel表，自行替换文件路径和列名
exist_df = pd.read_excel("现有文件名录.xlsx", usecols=["时间戳", "文件大小"])

# 2. 将现有时间戳转为datetime类型便于计算
exist_df["时间戳"] = pd.to_datetime(exist_df["时间戳"], format="%Y%m%d %H.%M.%S")

# 3. 生成覆盖整个时间范围的10分钟间隔完整时间序列
start = exist_df["时间戳"].min()
end = exist_df["时间戳"].max()
full_time = pd.date_range(start=start, end=end, freq="10T")
full_df = pd.DataFrame({"时间戳": full_time})

# 4. 合并两个表，缺失条目自动填充NaN
result_df = pd.merge(full_df, exist_df, on="时间戳", how="left")

# 5. 将时间戳转回原字符串格式，和原文件名格式保持一致
result_df["时间戳"] = result_df["时间戳"].dt.strftime("%Y%m%d %H.%M.%S")

# 6. 导出结果到Excel
result_df.to_excel("补全后结果.xlsx", index=False)

• 导出完成后，直接将你之前设置的按文件大小高亮的条件格式规则，应用到新表的「文件大小」列即可。

方法二：纯Excel实现（无需写代码，适合小数据量场景） #

实现步骤： #

• 第一步：生成完整的10分钟间隔时间戳序列
  新建空白列A作为完整时间戳列，A1单元格输入你数据的最早时间戳（比如20211011 00.00.10），A2单元格输入公式=TEXT(TEXT(A1,"0000-00-00 00\:00\:00")+10/1440,"yyyymmdd hh.mm.ss")，下拉A列直到填充到你数据的最晚时间点。
• 第二步：匹配现有文件信息并补全NaN
  A列旁新建B列命名为「文件大小」，B1单元格输入公式=IFERROR(VLOOKUP(A1, 现有数据区域, 2, FALSE), "NaN")，注意将公式里的「现有数据区域」替换为你原来表格里时间戳列+文件大小列的范围，比如Sheet1!$A:$B，下拉B列完成填充，缺失时间戳对应的B列自动显示NaN。
• 第三步：保留高亮格式
  直接复制你之前设置的按文件大小高亮的条件格式，应用到B列所有单元格即可。

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