如何在 Excel 中快速找出清單中遺漏的日期？

作者孫修改日期2025-07-22

假設您正在 Excel 中維護一份記錄或工時表，並發現您的日期清單不連續——某些日期遺漏了，如截圖所示。快速找出並填補這些缺失的日期，有助於確保您的資料完整，以利後續分析、報表製作或記錄保存。
本教學將介紹幾種在 Excel 中有效偵測並補齊這些缺失日期的方法：
使用使用條件格式找出缺失的日期
使用公式找出缺失的日期
使用 Kutools for Excel 找出並填補缺失的日期
使用 VBA 自動識別並插入缺失的日期
使用資料透視表標示出缺失的日期

使用使用條件格式找出遺漏的日期

找出日期清單缺口的一種方法是利用 Excel 的條件格式功能。此方法會以視覺方式醒目提示序列中遺漏日期的儲存格，讓您一眼輕鬆發現不連續處！

1. 選取包含日期的範圍，然後前往開始 > 使用條件格式 > 新增規則。請參閱下方螢幕截圖：
按一下 [首頁] > [條件式格式設定] > [新增規則]

2. 在新增格式設定規則對話方塊中，於選取規則類型區段下，選擇使用公式來決定要格式化的儲存格，並輸入下列公式：=A2<>(A 1+1)（其中 A1 為清單中的第一個日期，A2 為下一個日期）。請參閱下方螢幕截圖：
在對話方塊中指定選項

3. 按一下格式按鈕，即可開啟設定儲存格格式對話方塊。在填滿索引標籤下，挑選一種顏色醒目提示遺漏的日期！詳情請參閱下方螢幕截圖：
選取填滿色彩以醒目提示儲存格

4. 設定格式後，按兩次確定以套用設定。現在，序列中遺漏日期的位置將會被醒目提示。
遺漏的日期已醒目提示

注意：您清單中的最後一個日期也可能被醒目提示，因為此公式會檢查每個日期後續的內容。此方法特別適合快速檢閱大型資料集，但不會自動補齊遺漏的日期。

使用公式找出遺漏的日期

另一種實用的方法是直接在表格中運用 Excel 公式來識別日期缺口。此方法會新增一個欄位，標示每個日期之後是否缺少一天，非常適合用於追蹤出勤記錄、專案時程或庫存資料。

在日期清單旁邊的空白欄位中（假設您的清單從 A1 開始，則為 B1 儲存格），輸入下列公式：=IF(A2=A 1+1,"","Missing next day")。按下 Enter，再向下拖曳自動填滿控制點，即可將公式快速套用至所有日期旁的儲存格！請參閱螢幕截圖：
拖曳並填入公式至其他儲存格

當您看到「Missing next day」時，就表示清單中存在日期缺口。這種方式簡單直觀，能快速檢視遺漏的日期，尤其適合用於篩選或進一步處理已識別的缺口。

注意：與前一種方法相同，此公式會標記最後一個日期之後的列（因後續無其他日期），您可視情況忽略或清除該標記。

使用 Kutools for Excel 找出並填入遺漏的日期

對於使用 Kutools for Excel 的使用者，內建功能可快速找出並自動填入遺漏的日期或連續數字。當您不僅需要找出資料缺口，還需自動補齊以進行準確計算或稽核時，此功能特別實用！

Kutools for Excel 提供超過 300 項進階功能，簡化複雜作業，提升創造力與效率。整合 AI 功能，Kutools 能精準自動化各項任務，讓資料管理輕鬆無負擔。Kutools for Excel 的詳細資訊…… 免費試用……

安裝免費版 Kutools for Excel 後，請依照下列步驟操作：

1. 選取您要分析的日期清單，然後前往 Kutools > 插入 > 查找缺失的序列。請參閱下方螢幕截圖：
按一下 Kutools 的「尋找遺漏的序列號碼」功能

2. 在查找缺失的序列對話方塊中，您可以選擇多種實用選項，例如找出或插入遺漏的數字、醒目提示遺漏項目，或建立標記欄位！詳情請參閱下方螢幕截圖：
選取處理遺漏日期的操作方式

3. 確認選擇後，請點選確定，系統將立即顯示所找到的遺漏日期數量。詳情請參閱下方螢幕截圖：
將彈出一個對話方塊，告知您遺漏的序列日期數量

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 助手：基於以下內容徹底革新數據分析：智慧執行｜產生程式碼｜建立自訂公式｜分析資料並產生圖表｜呼叫增強函數……
	熱門功能：尋找、醒目提示或標記重複值｜刪除空白行｜合併列或儲存格而不遺失資料｜不使用公式的四捨五入……
	高級 LOOKUP：多重條件 VLookup ｜多重數值 VLookup ｜跨多個工作表 VLookup ｜模糊查找……
	高級下拉列表：快速建立下拉式清單｜相依式下拉式清單｜多選下拉式清單……
	欄位管理員：新增指定數量的欄位｜移動欄位｜切換隱藏欄位的可見狀態｜比較範圍與欄位……
	精選功能：網格聚焦｜設計視圖｜增強編輯欄｜工作簿與工作表管理員｜資源庫（自動文字）｜日期提取｜合併工作表｜加密／解密儲存格｜依清單傳送電子郵件｜超級篩選｜特殊篩選（篩選粗體儲存格／斜體／刪除線……） ......
	頂尖 15 工具組：12 文字工具（添加文本，刪除特定字符，……）｜ 50+ 圖表類型（甘特圖，……）｜ 40+ 實用公式（基於生日計算年齡，……）｜ 19 插入工具（插入二維碼，從路徑插入圖片，……）｜ 12 轉換工具（金額轉大寫，匯率轉換，……）｜ 7 合併和拆分工具（高級合併行，分割儲存格，……）｜……以及更多