如何在 Excel 中找出重疊的日期或時間範圍？

作者Sun修改日期2025-09-04

在 Excel 中，重疊的日期或時間範圍可能引發排程衝突、資源分配問題，甚至影響資料完整性。有效識別這些重疊情形，對於管理輪班表、規劃活動、運作預訂系統或控管專案時程至關重要——這些情境通常都要求各時段不得互相重疊。本文將逐步介紹多種實用方法，協助您輕鬆找出 Excel 中重疊的日期或時間範圍，如下方截圖所示。
尋找重疊日期

使用公式檢查重疊的日期/時間範圍

VBA 程式碼－針對大型資料集自動偵測重疊的日期/時間範圍，或用於生成報告

使用條件格式－直接在工作表中視覺化標示重疊範圍，便於快速辨識

使用公式檢查重疊的日期/時間範圍

當您需要系統性地檢查日期或時間範圍是否重疊時，Excel 公式能提供快速又靈活的解決方案。此方法適用於中小型資料集，或當您希望在每一列直接顯示邏輯結果（TRUE 或 FALSE）以標示是否存在重疊狀態時。

太神奇了！在 Excel 中使用高效 Tabs，就像 Chrome、Edge、Firefox 和 Safari 一樣！
每天為您節省 50% 的時間，並減少數千次滑鼠點擊！

典型使用情境：適用於員工排班、活動預訂、專案階段追蹤或租賃管理等應用，其中每一列皆代表一個包含起始與結束日期（或時間）的區間。

限制：雖然此方法在處理中等規模清單時表現良好，但在應對極大資料集，或需跨多筆記錄生成完整重疊報告的情況下，可能較不理想。

1. 選取所有包含開始日期的儲存格。選取範圍後，點擊名稱方塊（位於編輯欄左側的欄位），並輸入描述性名稱，例如 startdate，再按下 Enter 確認。此步驟可讓您在公式中輕鬆參照整個清單！詳情請參閱下方截圖：
為開始日期定義一個範圍名稱

2. 同樣地，選取結束日期的儲存格，在名稱方塊中輸入名稱（例如）enddate），再按一次 Enter。為範圍命名可大幅提升公式的可讀性與重複使用性！
為結束日期定義一個範圍名稱

3. 按一下與第一筆記錄同列的空白儲存格（例如 C2），作為顯示重疊結果的位置，然後輸入下列公式：

=SUMPRODUCT((A2<enddate)*(B2>=startdate))>1

請將 A2 替換為您記錄中起始日期所在的儲存格，並將 B2 替換為對應的結束日期。enddate 與 startdate 則使用您先前定義的名稱。此公式會檢查目前區間是否與清單中的其他任何區間重疊。按下 Enter 後，向下拖曳填滿控點，即可快速套用至所有需檢查的列！若某列顯示 TRUE，表示該範圍至少與另一範圍重疊；否則即無重疊。

使用公式檢查相對日期範圍是否與其他範圍重疊

請確認 startdate 與 enddate 皆指向包含起始值與結束值的完整排序欄位。若您的欄位位置不同，或所選範圍包含標題列，請依實際需求調整儲存格參照。

重要注意事項與疑難排解：

若出現 #VALUE！錯誤，請確認儲存格名稱與參照正確無誤，且日期欄位未包含文字或格式錯誤的日期/時間資料。
此方法會考量時間區間部分重疊（非完全互斥）的情況。若兩個區間僅在端點相接（即其中一個的結束日期恰好等於另一個的開始日期），通常不視為重疊；但您可透過調整公式中的不等式來變更此判定方式。
針對包含小時與分鐘的時間範圍，只要儲存格統一設定為時間或日期格式，其公式運作方式便與處理日期時完全相同。

VBA 程式碼－針對大型資料集自動偵測重疊的日期/時間範圍，或用於生成報告

若您經常處理大型資料集，並需要更自動化的方式來識別重疊情形（特別是在生成摘要報告或一次性標示所有衝突項目時），使用 VBA 能大幅簡化整個流程。此方法無需手動檢查，適用於數百甚至數千個區間，還可自訂設定，以醒目方式提示或完整列出所有重疊配對。

適用時機：建議進階使用者於管理大型排程資料庫、共用資源，或需產出所有偵測到的重疊紀錄日誌（而非僅逐列顯示 TRUE/FALSE）時使用。

潛在缺點：需啟用巨集並具備基本 VBA 操作知識，且首次執行前務必謹慎備份資料，以防意外覆寫。

1. 按一下開發人員工具 > Visual Basic，開啟 Microsoft Visual Basic for Applications 視窗；接著按一下插入 > 模組，並將下方程式碼貼到模組視窗中：

Sub FindOverlappingDateRanges()
    Dim ws As Worksheet
    Dim i As Long, j As Long
    Dim lastRow As Long
    Dim overlapList As String
    Dim msg As String
    Dim Start1, End1, Start2, End2
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set ws = ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row ' Assumes data starts in row 2
    overlapList = ""
    
    For i = 2 To lastRow
        Start1 = ws.Cells(i, 1).Value
        End1 = ws.Cells(i, 2).Value
        
        If Start1 <> "" And End1 <> "" Then
            For j = 2 To lastRow
                If i <> j Then
                    Start2 = ws.Cells(j, 1).Value
                    End2 = ws.Cells(j, 2).Value
                    
                    If Start2 <> "" And End2 <> "" Then
                        If Start1 < End2 And End1 > Start2 Then
                            overlapList = overlapList & "Row " & i & " overlaps with Row " & j & vbCrLf
                        End If
                    End If
                End If
            Next j
        End If
    Next i
    
    If overlapList <> "" Then
        msg = "The following rows have overlapping date/time ranges:" & vbCrLf & overlapList
    Else
        msg = "No overlapping date/time ranges found."
    End If
    
    MsgBox msg, vbInformation, "KutoolsforExcel"
End Sub

2. 輸入程式碼後，按一下執行或按下 Enter 鍵執行程式碼。此巨集將掃描 A 欄（起始日期）與 B 欄（結束日期）中的日期範圍配對，並回報所有發現的重疊項目，同時顯示一個訊息方塊，列出所有發生衝突的列，方便您立即稽核或進一步調查。

疑難排解：

請確保起始時間與結束日期分別位於 A 欄和 B 欄，並從第 2 列開始填寫（第 1 列為標題列）。若您的資料結構不同，請調整對應的範圍。
所有儲存格皆須包含有效的日期/時間值，且比較範圍內不得留空。
執行或修改 VBA 程式碼前，務必先備份重要檔案，以防資料遺失。

提示：您可以強化 VBA 程式碼，直接在工作表中標示重疊項目，例如為整列上色，或將結果寫入相鄰欄位！

使用條件格式－直接在工作表中視覺化標示重疊範圍，便於快速辨識

使用條件格式是一種實用的方法，能直接在試算表中以視覺化方式標示出重疊的日期或時間區間。此方案特別適用於繁忙的排程表、甘特圖或活動時程等情境，讓您一眼就能掌握哪些記錄存在衝突。

最適合：希望立即在工作表上獲得視覺反饋或色彩提示，卻不想在每列輸入公式或執行程式碼的使用者——非常適合用於互動式資料檢查與簡報展示！

限制：大型資料集可能反應遲緩；此外，雖然會標示重疊項目，但不會提供詳細的配對清單或統計數量。

套用步驟：

選取開始日期範圍（例如，）A2:A100）與結束日期範圍（B2:B100），若兩欄相鄰，也可同時選取。
在常用頁籤上，點選使用條件格式 > 新增規則。
選擇使用公式來決定要格式化哪些儲存格。
將下列公式輸入公式框中（假設您的選取範圍從第 2 列開始）：
```
=SUMPRODUCT(($A2<$B$2:$B$100)*($B2>$A$2:$A$100))>1
```
按一下格式……，選取一種填充顏色以醒目標示重疊範圍，再點選確定套用設定。

套用規則後，所有與範圍內其他區間重疊的列都會自動醒目提示，讓您無需逐一檢查，就能輕鬆發現問題。

提示：請調整 $A$2:$A$100 與 $B$2:$B$100，使其符合您的實際數據範圍，並確保參照與所選範圍的首列一致。

注意事項：若您僅需標示其中一欄（例如僅標示開始日期），仍須使用對應的公式邏輯。請根據您的實際需求，決定是否將端點相接視為重疊。

最佳 Office 生產力工具

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