如何快速從 Excel 中的列表裡找到缺失的日期？

Author Sun Last modified 2025-08-23

假設您正在 Excel 中維護記錄或時間表，並注意到您的日期列表不連續——有些日期缺失了，如截圖所示。快速識別並填補這些缺失的日期有助於確保數據完整，以便進行分析、報告或記錄保存。
本教程介紹了幾種在 Excel 中有效檢測和補充缺失日期的方法：
使用條件格式查找缺失的日期
使用公式查找缺失的日期
使用 Kutools for Excel 查找並填補缺失的日期
使用 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. 點擊確定完成。現在，您的列表將顯示甚至填補缺失的日期，具體取決於您選擇的選項。這種方法對於大型數據集非常方便，並且最大限度地減少手動檢查或公式放置錯誤導致的錯誤。

插入缺失的序列號碼	遇到缺失序列號碼時插入空白行

插入帶有缺失標記的新列	填充背景色

此工具可以節省大量時間，特別適用於財務報表、出勤數據或任何需要連續日期記錄的場景。為了獲得最佳結果，請確保您的列表按日期排序。

使用 VBA 自動識別並插入缺失的日期

如果您正在處理長時間或經常更新的日期列表，並且希望流程完全自動化，可以在 Excel 中使用自定義 VBA 宏。此方法掃描您的日期列，找到序列中缺失的日期，並將其作為新行直接插入到您的列表中。

這對於大型數據集、定期報告或當新數據定期添加並且需要確保完整性而無需手動檢查時尤其有用。

操作步驟：

點擊開發工具 > 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

點擊運行按鈕（或按 F5）執行代碼。宏將檢查第一列（A 列）中的日期列表，並自動將缺失的日期作為新行插入。

實用提示和注意事項：
– 確保在運行宏之前您的日期已按升序排列。
– 宏將缺失的日期作為新行插入，因此如有必要，請備份您的數據或在副本上測試。
– 如果您的日期不在 A 列，請調整 ws.Cells(i,1) 到正確的列號。
– 如果數據集非常大，宏可能需要一些時間才能完成。
– 如果收到錯誤，請驗證日期列中的所有單元格是否都是實際的日期值。

使用樞紐分析表標記缺失的日期

如果您不想使用公式或代碼，可以利用 Excel 內建的樞紐分析表功能，將實際的日期列表與完整的預期序列進行視覺比較。當您想要分析或交叉檢查應出現每一天的出勤記錄、交易或每日記錄時，此方法特別適用。

操作步驟：

首先，創建一個包含完整預期日期序列的輔助列，覆蓋您的開始和結束日期。在一個單元格中輸入第一個日期（例如 D2），然後拖動填充手柄向下創建日期，直到範圍完成。
將原始日期列表和新的輔助日期列表複製到一個新工作表中，將它們堆疊在一列中（例如 E 列）。
選擇合併的列表，然後前往插入 > 樞紐分析表。在對話框中，設置表格/範圍並選擇一個新工作表作為輸出。
在樞紐分析表字段列表中，將日期字段拖到行區域，並再次拖到值區域，設置聚合為計數。計數列中僅出現一次的日期表示缺失的日期（即那些只存在於完整序列中但不在實際數據中的日期）。

提示：
– 此方法最適合用於審查長時間段內的缺失條目。
– 為了獲得最佳結果，請確保您的日期列表不包含重複項。
– 您可以在樞紐分析表中過濾或高亮顯示以快速找到缺失的日期。
– 高級用戶可以將此方法與條件格式結合使用以提高可視性。

優點： 易於視覺化；不需要公式或 VBA；適合報告。
缺點： 不會自動填補缺失的日期，但會標記缺失的部分。

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

最佳 Office 生產力工具

🤖	Kutools AI 助手：以智能執行為基礎，革新數據分析｜生成程式碼｜創建自訂公式｜分析數據並生成圖表｜調用 Kutools 增強函數…
	熱門功能：查找、選取項目的背景色或標記重複值｜刪除空行｜合併列或單元格且不遺失數據｜四捨五入（免公式）...
	高級 LOOKUP：多條件 VLookup｜多值 VLookup｜多表查找｜模糊查找...
	高級下拉列表：快速創建下拉列表｜依賴型下拉列表｜多選下拉列表...
	列管理器：添加指定數量的列｜移動列｜切換隱藏列的顯示狀態｜比較區域及列...
	精選功能：網格聚焦｜設計檢視｜增強編輯欄｜工作簿及工作表管理器｜資源庫（快捷文本）｜日期提取器｜合併資料｜加密/解密儲存格｜按列表發送電子郵件｜超級篩選｜特殊篩選（篩選粗體/傾斜/刪除線...）...
	15 大工具集：12 項文本工具（添加文本、刪除特定字符…）｜50+ 儀表類型（甘特圖等）｜40+ 實用公式（基於生日計算年齡等）｜19 項插入工具（插入QR码、根據路徑插入圖片等）｜12 項轉換工具（金額轉大寫、匯率轉換等）｜7 項合併與分割工具（高級合併行、分割儲存格等）｜...及更多