如何在 Excel 中標記週末和假日？

Author: Xiaoyang Last Modified: 2025-08-23

在 Excel 中處理日常記錄表時，通常會希望將週末或公共假期與普通工作日區分開來，以便於資料審查。這樣的高亮顯示對於考勤表、項目時間表或業務日誌特別有用，因為區分工作日和非工作日有助於視覺管理和後續分析。Excel 提供了多種方法來實現這一點，包括條件格式、自訂輔助公式列，或者使用 VBA 進行更高級的自動化。選擇適當的解決方案可能取決於表格佈局、新資料添加的頻率以及節日列表是否經常變更。

使用條件格式標記週末和假日
Excel 公式 - 使用輔助列標記週末和假日
VBA 程式碼 - 自動陰影週末和假日

使用條件格式標記週末和假日

Excel 的條件格式功能讓您可以自動標記週末（通常是星期六和星期日）以及日期記錄中的假日——無需在數據中寫入任何計算。這種方法適用於需要頻繁查看或更新其工作表的用戶，並且當數據或假日發生變化時，顏色提示會立即更新以反映非工作日的狀態。

要設置用於標記週末和假日的條件格式，請按照以下步驟操作：

1. 選擇您想要標記的日期範圍。

2. 轉到「Home」選項卡，點擊「Conditional Formatting」，然後選擇「New Rule」以打開規則編輯器。見截圖：

click Home > Conditional Formatting > New Rule

3. 在“新建格式規則”對話框中：

從規則類型列表中選擇“使用公式確定要設定格式的單元格”。
在公式輸入框中，輸入以下公式：=NETWORKDAYS($A2,$A2,$F$2:$F$6)=0
點擊“Format”按鈕以設置您的高亮樣式。

注意：在此公式中，$A2 指的是您選擇的第一個日期單元格（如果起始單元格在其他位置，請相應調整）。$F$2:$F$6 應包含您的假日列表（根據實際假日範圍進行編輯）。NETWORKDAYS 僅將周一至周五視為工作日。如果您使用不同的週末安排或只想包含特定天數，則可能需要自定義公式。

specify the options in the dialog box

4. 在“Format Cells”窗口中，切換到“Fill”標籤並選擇一個背景顏色以區分非工作日與普通工作日，方便快速視覺參考。

under the Fill tab, specify a color for highlighting the rows

5. 點擊“OK”兩次以關閉對話框。所選範圍內匹配週末或列出的假日日期將自動出現高亮顯示。隨時可以通過編輯假日範圍來更新假日，格式也會相應更新。

the rows contain the weekends and holidays are shaded

提示和故障排除：如果高亮未出現，請仔細檢查日期格式（該公式適用於真正的 Excel 日期）；不匹配可能會導致規則失敗。如果應用於多列，請調整公式的單元格引用。此外，如果您的數據跨越多年，請根據需要更新您的假日列表以反映正確的非工作日。

優勢： 不需要額外的列或手動操作；隨著表格增長或假日列表變更，完全動態。
潛在限制： 條件格式每個工作表有最大規則數量的限制（很少達到），過於複雜的公式可能會減慢大型數據集中的工作簿性能。

使用 Kutools AI 解鎖 Excel 的魔法

智能執行：執行單元格操作、分析數據並創建圖表——所有這些都由簡單的指令驅動。
自訂公式：生成量身定制的公式，簡化您的工作流程。
VBA 編碼：輕鬆編寫和實現 VBA 代碼。
公式解釋：輕鬆理解複雜的公式。
文本翻譯：打破電子表格中的語言障礙。

通過人工智能工具增強您的 Excel 能力。立即下載，體驗前所未有的效率！

Excel 公式 – 使用輔助列標記週末和假日

對於那些希望對色彩編碼擁有更多控制權或希望按工作日或非工作日排序/篩選的用戶來說，添加帶有公式的輔助列是一個可靠選擇。此技術清楚地將每一行標記為週末、假日或普通日子，可用於篩選、手動格式化或總結您的數據。
當僅靠顏色編碼不足，或想直接計算週末/假日的總數時，此方法尤其有用。

假設您的日期位於 A 列，從第 2 行開始，假日列於 F2:F6。在日期列旁插入一個新列，並輸入像“日類型”這樣的標題。

1. 在 B2 單元格（輔助列）中，輸入以下公式：

=IF(OR(WEEKDAY(A2,2)>=6,COUNTIF($F$2:$F$6,A2)>0),"Weekend/Holiday","Workday")

此公式對每個日期進行兩個檢查：
- WEEKDAY(A2,2)>=6：將星期六（6）和星期日（7）視為週末（如果您的週末不同，請相應調整）。
- COUNTIF($F$2:$F$6,A2)>0：檢查日期是否與假日列表中的某一天匹配。

2. 然後拖動填充柄向下應用公式到其他行。一旦填充完畢，您可以通過“週末/假日”篩選表格，以隔離或手動高亮這些行。如果需要顏色，您也可以基於此列應用條件格式。

提示：如果您的週末天數不同，請更改 WEEKDAY 邏輯（例如，使用 1 代表星期日或特定數字代表您的地區）。始終保持假日列表的最新狀態。如果您的數據範圍發生變化，請根據需要重新應用填充/複製。

優勢： 輔助列允許更多的自訂篩選和報告。根據需要輕鬆識別、計數或上色。
缺點： 除非與進一步的條件格式一起使用，否則需要在數據中添加額外的列並進行手動填色。

VBA 程式碼 – 自動陰影週末和假日

當您需要經常更新日期範圍，或者希望自動化突出顯示週末和假日的過程超出公式和條件格式所能做的時候，使用簡單的 VBA 宏非常有效。這對於管理長列表或重複生成工作表的用戶來說是理想的選擇。

利用宏，Excel 將根據您選擇的日期範圍和假日列表即時陰影週末和假日日期。您可以根據需要更改代碼中的高亮顏色，並在數據更改時重新運行宏。

1. 點擊 Developer > Visual Basic。在 Microsoft Visual Basic for Applications 窗口中，選擇 Insert > Module。

2. 複製並將以下代碼粘貼到新建的模塊中：

Sub ShadeWeekendsAndHolidays()
    Dim rngDates As Range
    Dim rngHolidays As Range
    Dim cell As Range
    Dim xTitleId As String
    
    xTitleId = "KutoolsforExcel"
    On Error Resume Next
    Set rngDates = Application.InputBox("Select the range with dates:", xTitleId, Selection.Address, Type:=8)
    Set rngHolidays = Application.InputBox("Select the range with holiday dates:", xTitleId, , Type:=8)
    On Error GoTo 0
    
    If rngDates Is Nothing Then Exit Sub
    If rngHolidays Is Nothing Then Exit Sub
    
    Application.ScreenUpdating = False
    
    For Each cell In rngDates
        If IsDate(cell.Value) Then
            If Weekday(cell.Value, vbMonday) >= 6 Or Not IsError(Application.Match(CDbl(cell.Value), rngHolidays, 0)) Then
                cell.Interior.Color = RGB(255, 199, 206) ' Light red fill; adjust as needed
            Else
                cell.Interior.ColorIndex = xlNone ' Remove fill from regular days
            End If
        End If
    Next cell
    
    Application.ScreenUpdating = True
End Sub

3. 按 F5 鍵運行此代碼，將提示您選擇日期單元格，然後選擇假日單元格列表。遵循屏幕上的指示。

該宏用填充顏色標記週末（星期六/星期日）和假日（根據您的列表）。如果需要，可以在代碼中調整 RGB(255,199,206) 以使用您偏好的顏色。

提示：
如果不小心選擇了錯誤的範圍，只需重新運行宏即可。所選範圍內所有不匹配單元格的現有填充將被重置。
確保您的日期和假日使用正確的 Excel 日期值（而非文本）。

優勢： 節省重複任務的時間並提供極大的靈活性。無需手動重新應用格式。
缺點： 必須為您的工作簿啟用宏；不熟悉 VBA 的用戶應該在運行前保存並嘗試備份副本。

更多相關文章：

合併單元格的交替行著色
在大數據中以不同顏色格式化交替行對於我們掃描數據非常有幫助，但有時數據中可能存在一些合併單元格。如下圖所示，為了以不同顏色突出顯示合併單元格的交替行，您如何在 Excel 中解決這個問題呢？

高亮近似匹配查找
在 Excel 中，我們可以使用 Vlookup 函數快速輕鬆地獲取近似匹配的值。但是，您是否嘗試過基於行列數據獲取近似匹配，並從原始數據範圍中高亮近似匹配，如下圖所示？本文將討論如何在 Excel 中解決此任務。

如果值大於另一個單元格則高亮單元格
比較兩列中的值，例如，如果 B 列中的值大於同一行 C 列中的值，則高亮 B 列中的值，如下圖所示。在本文中，我將介紹一些方法，如果值大於另一個單元格，則高亮單元格。

根據下拉列表高亮行
本文將討論如何根據下拉列表高亮行，以下圖為例，當我從 E 列的下拉列表中選擇“In Progress”，我需要用紅色高亮該行；當我選擇“Completed”，我需要用藍色高亮該行；而當我選擇“Not Started”，則用綠色高亮該行。

最佳 Office 生產力工具

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