如何在 Excel 中突出顯示週末和假日?
在 Excel 中處理日常記錄表時,通常需要將週末或公共假日與普通工作日進行視覺上的區分,以便於數據審查。這種突出顯示對於考勤表、項目時間表或業務日誌特別有用,因為區分工作日和非工作日有助於視覺管理和後續分析。Excel 提供了多種方法來實現這一點,包括條件格式、自定義輔助公式列或使用 VBA 進行更高級的自動化。選擇合適的解決方案可能取決於表格佈局、新數據添加的頻率以及假日列表是否經常變動。
使用條件格式突出顯示週末和假日
Excel 的條件格式功能讓您可以自動突出顯示日期記錄中的週末(通常是星期六和星期日)和假日,而無需在數據中寫入任何計算。此方法適合經常查看或更新工作表的用戶,並希望在數據或假日更改時自動更新非工作日的顏色提示。
要為週末和假日設置條件格式,請按照以下步驟操作:
1. 選擇您要突出顯示的日期範圍。
2. 轉到「Home」選項卡,點擊「Conditional Formatting」,然後選擇「New Rule」以打開規則編輯器。見截圖:
3. 在「New Formatting Rule」對話框中:
- 從規則類型列表中選擇「Use a formula to determine which cells to format」。
- 在公式輸入框中,輸入以下公式:=NETWORKDAYS($A2,$A2,$F$2:$F$6)=0
- 點擊「Format」按鈕以設置您的突出顯示樣式。
4. 在「Format Cells」窗口中,切換到「Fill」選項卡並選擇週末和假日的背景顏色。該顏色用於快速區分非工作日和普通工作日。
5. 點擊「OK」兩次以關閉對話框。您選擇的突出顯示將自動出現在所選區域內匹配週末或列出假日的任何日期上。假期可以隨時通過編輯假日範圍進行更新,格式也會相應更新。
提示和故障排除:如果突出顯示未出現,請仔細檢查日期格式(公式適用於真實的 Excel 日期);不匹配可能會導致規則失敗。如果應用於多列,請調整公式單元格引用。此外,如果您的數據跨越多年,請根據需要更新假日列表以反映正確的非工作日。
優點: 無需額外列或手動操作;隨著表格增長或假日列表變化,完全動態。
潛在限制: 條件格式每個工作表有最大規則數量(很少達到),過於複雜的公式可能會在大型數據集中降低工作簿性能。

使用 Kutools AI 解鎖 Excel 的魔法
- 智能執行:執行單元格操作、分析數據並創建圖表——所有這些都由簡單的指令驅動。
- 自訂公式:生成量身定制的公式,簡化您的工作流程。
- VBA 編碼:輕鬆編寫和實現 VBA 代碼。
- 公式解釋:輕鬆理解複雜的公式。
- 文本翻譯:打破電子表格中的語言障礙。
Excel 公式 – 使用輔助列標記週末和假日
對於希望對顏色編碼有更多控制權或希望按工作日或非工作日排序/篩選的用戶,添加帶有公式的輔助列是一個可靠的選擇。這種技術清楚地將每一行標記為週末、假日或普通日,可用於篩選、手動格式化或總結您的數據。
此方法特別適用於僅靠顏色編碼不足以滿足需求的情況,或者當您想直接統計週末/假日的總數時。
假設您的日期在 A 列中從第 2 行開始,假日列在 F2:F6 中列出。在日期列旁插入一個新列並輸入標題如“Day Type”。
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. 然後向下拖動填充柄以將公式應用於其他行。一旦填滿,可按「Weekend/Holiday」篩選表格以隔離或手動突出顯示這些行。如果需要著色,也可以基於此列應用條件格式。
提示:如果您的週末天數不同,請更改 WEEKDAY
邏輯(例如,使用 1 表示星期日或您所在地區的特定數字)。始終保持假日列表是最新的。如果您的數據範圍更改,請根據需要重新應用填充/複製。
優點: 輔助列允許更多的自定義篩選和報告。根據需要輕鬆識別、計數或著色。
缺點: 除非與進一步的條件格式一起使用,否則需要在數據中添加額外的列並手動填充顏色。
VBA 代碼 – 自動陰影週末和假日
當您需要經常更新日期範圍,或者希望自動執行突出顯示週末和假日的過程超出公式和條件格式的能力時,使用簡單的 VBA 宏是非常高效的。這對於管理長列表或重複生成工作表的用戶來說非常理想。
使用宏,Excel 將根據您選擇的日期範圍和假日列表立即陰影週末和假日日期。必要時,您可以在代碼中更改突出顯示顏色,並在數據更改時重新運行宏。
1. 點擊 Developer > Visual Basic。在 Microsoft Visual Basic for Applications 窗口中,選擇 Insert > Module。
2. 將以下代碼複製並粘貼到新建的 Module 中:
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 的用戶應在運行前保存並嘗試在備份副本上操作。
更多相關文章:
- Color Alternate Rows For Merged Cells
- 在大量數據中交替格式化行以不同顏色非常有助於我們掃描數據,但有時候,數據中可能會有一些合併單元格。為了像下面的截圖所示那樣交替突出顯示合併單元格的行,應該如何在 Excel 中解決這個問題呢?
- Highlight Approximate Match Lookup
- 在 Excel 中,我們可以使用 Vlookup 函數快速且輕鬆地獲取近似匹配值。但是,您是否曾經嘗試過基於行和列數據獲取近似匹配並從原始數據範圍中突出顯示近似匹配,如下截圖所示?本文將談談如何在 Excel 中解決此任務。
- Highlight Cell If Value Is Greater Than Another Cell
- 比較兩列中的值,例如,如果 B 列中的值大於同一行中 C 列的值,則突出顯示 B 列中的值,如下截圖所示。在本文中,我將介紹一些方法來突出顯示如果值大於另一個單元格中的值的單元格。
- Highlight Rows Based On Drop Down List
- 本文將談談如何根據下拉列表突出顯示行,以下面的截圖為例,當我從 E 列的下拉列表中選擇“In Progress”時,我需要用紅色突出顯示該行;當我選擇“Completed”時,需要用藍色突出顯示該行;當我選擇“Not Started”時,需要用綠色突出顯示該行。
最佳辦公效率工具
🤖 | Kutools AI 助手:基於智能執行方式革新數據分析:智能執行 | 生成代碼 | 創建自訂公式 | 分析數據並生成圖表 | 調用 Kutools 函數… |
熱門功能:查找、標記重複值或識別重複項 | 刪除空行 | 合併列或單元格而不丟失數據 | 四捨五入無需公式 ... | |
高級 LOOKUP: 多條件 VLookup | 多值 VLookup | 多表查找 | 模糊查找 .... | |
高級下拉列表:快速創建下拉列表 | 依賴下拉列表 | 多選下拉列表 .... | |
列管理器: 添加特定數量的列 | 移動列 | 切換隱藏列的可見狀態 | 比較區域和列 ... | |
特色功能:網格聚焦 | 設計檢視 | 增強編輯欄 | 工作簿與工作表管理器 | 資源庫(自動文本) | 日期提取器 | 合併資料 | 加密/解密儲存格 | 按列表發送電子郵件 | 超級篩選 | 特殊篩選(篩選粗體/斜體/刪除線...) ... | |
頂級 15 種工具集: 12 個文本工具(添加文本、刪除特定字符、...) | 50+ 圖表 類型(甘特圖、...) | 40+ 實用 公式(基於生日計算年齡、...) | 19 個插入工具(插入QR碼、根據路徑插入圖片、...) | 12 個轉換工具(金額轉大寫、匯率轉換、...) | 7 個合併與分割工具(高級合併行、分割儲存格、...) | ... 還有更多 |
使用 Kutools for Excel 提升您的 Excel 技巧,體驗前所未有的高效。 Kutools for Excel 提供超過 300 種高級功能來提高生產力並節省時間。 點擊這裡獲取您最需要的功能...
Office Tab 將標籤式界面帶到 Office,讓您的工作更加輕鬆
- 在 Word、Excel、PowerPoint、Publisher、Access、Visio 和 Project 中啟用標籤式編輯和閱讀。
- 在同一窗口的新標籤中打開和創建多個文檔,而不是在新窗口中。
- 將您的生產力提高 50%,每天為您減少數百次鼠標點擊!