跳至主要内容

Kutools for Office — 一套工具，五种功能。事半功倍。

如何快速从Excel列表中找出缺失的日期？

Author Sun Last modified
sample data
假设你正在Excel中维护一条记录或时间表，你发现你的日期列表并不连续——有些日期缺失了，如截图所示。快速识别并填补这些缺失的日期有助于确保数据完整，以便进行分析、报告或记录保存。
本教程介绍了几种在Excel中高效检测并补充缺失日期的方法：
使用条件格式查找缺失的日期
使用公式查找缺失的日期
使用Kutools for Excel查找并填充缺失的日期 good idea3
使用VBA自动识别并插入缺失的日期
使用数据透视表突出显示缺失的日期

使用条件格式查找缺失的日期

一种确定日期列表中空缺的方法是利用Excel的条件格式。此方法直观地突出了日期序列中缺失日期的单元格，使得一眼就能轻松发现不连续之处。

1. 选择包含日期的区域，然后转到 开始 > 条件格式 > 新建规则。参见截图：
click Home > Conditional Formatting > New Rule

2. 在 新建格式规则 对话框中，选择 使用公式确定要设置格式的单元格 在 选择规则类型 部分下。输入以下公式： =A2<>(A1+1) （其中A1是第一个日期，A2是列表中的下一个日期）。参见截图：
specify options in the dialog box

3. 点击 格式 按钮以打开 设置单元格格式 对话框。在 填充 选项卡下，选择一种颜色来突出显示缺失的日期。参见截图：
select a fill color for highlighting the cells

4. 设置完格式后，点击 确定 两次以应用。现在，序列中缺失日期的单元格将被突出显示。
the missing dates are highlighted

注意：列表中的最后一个日期也可能被突出显示，因为此公式检查每个日期之后的内容。这种方法特别适用于快速查看大型数据集，但不会自动填充缺失的日期。

使用公式查找缺失的日期

另一种实用的方法是使用Excel公式直接在表格中帮助识别任何间隔。此方法创建一个新列，指示每个日期后是否缺少一天，非常适合跟踪考勤日志、项目时间表或库存记录。

在日期列表旁边的一个空白列中（例如，如果您的列表从A1开始，则为B1单元格），输入公式： =IF(A2=A1+1,"","下一天缺失")。按下 Enter，然后拖动自动填充手柄向下复制公式至所有日期旁。参见截图：
enter a formula to find missing dates drag and fill the formula to other cells

无论何处看到“下一天缺失”，您就知道列表中存在空缺。这是一种简单、直观的审查缺失日期的方法，尤其在您希望过滤或进一步处理已识别的间隔时非常有用。

注意：与前一种方法一样，该公式会标记最后一个日期后的行（因为没有下一个日期），您可以忽略或清除不需要的部分。

使用Kutools for Excel查找并填充缺失的日期

对于使用Kutools for Excel的用户，有一个内置功能可以快速查找甚至填充缺失的日期或序列号。当您不仅需要定位间隔，还需要自动完成数据以进行准确计算或审核时，这尤其有帮助。

Kutools for Excel 提供了超过 300 种高级功能，简化复杂任务，提升创造力与效率。 通过集成 AI 能力，Kutools 能够精准自动执行任务，让数据管理变得轻松简单。Kutools for Excel 的详细信息...         免费试用...

免费安装 Kutools for Excel后，请按以下步骤操作：

1. 选择要分析的日期列表，然后转到 Kutools > 插入 > 查找缺失的序列号。参见截图：
click Find Missing Sequence Number feature of kutools

2. 在 查找缺失的序列号 对话框中，您可以选择多个选项，例如查找或插入缺失的数字、突出显示或创建标记列。参见截图：
select the operation for dealing the missging dates

3. 确认您的选择后，点击 确定。系统将提示找到的缺失日期数量。参见截图：
a dialog will pop out to tell you the number of missing sequence dates

4. 单击确定以完成。现在，您的列表将显示甚至填充缺失的日期，具体取决于您选择的选项。这种方法对大型数据集很方便，并且最大限度地减少了手动检查或公式误用带来的错误。

插入缺失的序列号 遇到缺失的序列号时插入空白行
Insert missing sequence number Insert blank rows when encountering missing sequence numbers
插入带有后续缺失标记的新列 填充背景色
Insert new column with missing maker Fill background color

此工具可以节省大量时间，尤其对财务报表、考勤数据或任何需要连续日期记录的场景特别有益。为了获得最佳效果，请确保列表按日期排序。

使用VBA自动识别并插入缺失的日期

如果您正在处理长或频繁更新的日期列表，并希望过程完全自动化，可以在Excel中使用自定义VBA宏。此方法扫描您的日期列，找到序列中缺失的日期，并作为新行直接插入您的列表中。

这对于大型数据集、定期报告或当新数据经常添加且需要确保完整性而无需手动检查的情况特别有用。

操作步骤：

  1. 点击开发工具 > Visual Basic以打开VBA编辑器。在弹出的Microsoft Visual Basic for Applications窗口中，点击插入 > 模块，然后将以下代码粘贴到模块窗口中：
Sub InsertMissingDates()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim currentDate As Date, nextDate As Date
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set ws = ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    i = 2
    
    While i < lastRow
        currentDate = ws.Cells(i, 1).Value
        nextDate = ws.Cells(i + 1, 1).Value
        
        If nextDate > currentDate + 1 Then
            ws.Rows(i + 1).Insert Shift:=xlDown
            ws.Cells(i + 1, 1).Value = currentDate + 1
            ws.Cells(i + 1, 1).NumberFormat = "yyyy-mm-dd"
            lastRow = lastRow + 1
        End If
        
        i = i + 1
    Wend
End Sub
  1. 点击 Run button 运行 按钮（或按F5）以执行代码。宏将检查第一列（A列）中的日期列表，并自动将缺失的日期作为新行插入。

实用技巧和注意事项：
– 确保在运行宏之前日期已按升序排列。
– 宏将缺失的日期作为新行插入，因此如有必要请备份您的数据或在副本上测试。
– 如果您的日期不在A列，请调整 ws.Cells(i,1) 到正确的列号。
– 对于非常大的数据集，宏可能需要一些时间才能完成。
– 如果收到错误，请验证日期列中的所有单元格是否为实际的日期值。

使用数据透视表突出显示缺失的日期

如果您不想使用公式或代码，可以利用Excel内置的数据透视表功能，直观地比较实际的日期列表与完整的预期序列。当您想分析或交叉检查考勤日志、交易或每日记录时，这种方法特别适用，其中范围内的每个日期都应该出现。

操作步骤：

  1. 首先，创建一个包含完整预期日期序列的辅助列，覆盖您的起始和结束日期。在一个单元格中输入第一个日期（例如D2），然后拖动填充手柄向下创建日期，直到您的范围完整。
  2. 将原始日期列表和新的辅助日期列表复制到一个新的工作表中，并将它们堆叠在一列中（例如E列）。
  3. 选择合并后的列表，然后转到插入 > 数据透视表。在对话框中，设置表格/范围并选择一个新的工作表作为输出目标。
  4. 在数据透视表字段列表中，将日期字段拖到行区域，并再次拖到值区域，将聚合设置为计数。计数列中仅出现一次的日期表示缺失的日期（即那些仅存在于完整序列中但不存在于实际数据中的日期）。

提示：
– 此方法最适合审查大范围内的缺失条目。
– 为了获得最佳结果，请确保您的日期列表不包含重复项。
– 您可以在数据透视表中筛选或高亮显示以快速发现缺失的日期。
– 高级用户可以将此方法与条件格式结合使用以增加可见性。

优点： 易于可视化；无需公式或VBA；适合报告。
缺点： 不会自动填充缺失的日期，但会突出显示缺失的内容。

演示：查找并在列表中插入缺失的日期

 

最佳Office办公效率工具

🤖 Kutools AI 助手：以智能执行为基础，彻底革新数据分析 |代码生成 |自定义公式创建|数据分析与图表生成 |调用Kutools函数……
热门功能：查找、选中项的背景色或标记重复项 | 删除空行 | 合并列或单元格且不丢失数据 | 四舍五入……
高级LOOKUP：多条件VLookup|多值VLookup|多表查找|模糊查找……
高级下拉列表：快速创建下拉列表 |依赖下拉列表 | 多选下拉列表……
列管理器： 添加指定数量的列 | 移动列 | 切换隐藏列的可见状态 | 比较区域与列……
特色功能：网格聚焦 |设计视图 | 增强编辑栏 | 工作簿及工作表管理器 | 资源库（自动文本） | 日期提取 | 合并数据 | 加密/解密单元格 | 按名单发送电子邮件 | 超级筛选 | 特殊筛选（筛选粗体/倾斜/删除线等）……
15大工具集：12项 文本工具（添加文本、删除特定字符等）|50+种 图表 类型（甘特图等）|40+实用 公式（基于生日计算年龄等）|19项 插入工具（插入二维码、从路径插入图片等）|12项 转换工具（小写金额转大写、汇率转换等）|7项 合并与分割工具（高级合并行、分割单元格等）| ……
Kutools支持多种语言——可选择英语、西班牙语、德语、法语、中文等40多种语言！

通过Kutools for Excel提升您的Excel技能，体验前所未有的高效办公。 Kutools for Excel提供300多项高级功能，助您提升效率并节省时间。 点击此处获取您最需要的功能……

Office Tab为Office带来多标签界面，让您的工作更加轻松

  • 支持在Word、Excel、PowerPoint中进行多标签编辑与阅读。
  • 在同一个窗口的新标签页中打开和创建多个文档，而不是分多个窗口。
  • 可提升50%的工作效率，每天为您减少数百次鼠标点击！

所有Kutools加载项，一键安装

Kutools for Office套件包含Excel、Word、Outlook和PowerPoint的插件，以及Office Tab Pro，非常适合跨Office应用团队使用。

Excel Word Outlook Tabs PowerPoint
  • 全能套装——Excel、Word、Outlook和PowerPoint插件+Office Tab Pro
  • 单一安装包、单一授权——数分钟即可完成设置（支持MSI）
  • 协同更高效——提升Office应用间的整体工作效率
  • 30天全功能试用——无需注册，无需信用卡
  • 超高性价比——比单独购买更实惠