如何在Excel中突出显示周末和节假日?
在Excel中处理日常记录表时,通常需要直观地区分周末或公共假日与普通工作日,以便于数据审查。这种高亮显示对于考勤表、项目时间表或业务日志尤其有用,因为区分工作日和非工作日有助于视觉管理和后续分析。Excel提供了多种实现方法,包括条件格式、自定义辅助公式列,或者使用VBA进行更高级的自动化。选择合适的解决方案可能取决于表格布局、新数据添加的频率以及节假日列表是否经常变化。
使用条件格式突出显示周末和节假日
Excel的条件格式功能可以自动突出显示日期记录中的周末(通常是周六和周日)和节假日,而无需在数据中写入任何计算公式。此方法适用于频繁查看或更新工作表的用户,并且当数据或节假日发生变化时,非工作日的颜色提示会即时更新。
要设置周末和节假日的条件格式,请按照以下说明操作:
1. 选择要突出显示的日期范围。
2. 转到“开始”选项卡,点击“条件格式”,然后选择“新建规则”以打开规则编辑器。见截图:
3. 在“新建格式规则”对话框中:
- 从规则类型列表中选择“使用公式确定要设置格式的单元格”。
- 在公式输入框中,输入以下公式:=NETWORKDAYS($A2,$A2,$F$2:$F$6)=0
- 点击“格式”按钮以设置高亮样式。
4. 在“设置单元格格式”窗口中,切换到“填充”选项卡并为周末和节假日选择背景颜色。此颜色可快速区分非工作日和普通工作日。
5. 单击两次“确定”关闭对话框。所选区域内符合周末或列出的节假日的日期将自动显示您选择的高亮显示。您可以随时通过编辑节假日范围来更新节假日,格式也会相应更新。
提示和故障排除:如果高亮显示未出现,请仔细检查日期格式(公式适用于真实的Excel日期);不匹配可能导致规则失败。如果应用于多列,请调整公式单元格引用。此外,如果您的数据跨越多年,请根据需要更新节假日列表以反映正确的非工作日。
优点: 无需额外列或手动操作;随着表格增长或节假日列表的变化完全动态更新。
潜在限制: 每个工作表的条件格式规则数量有上限(很少达到),过于复杂的公式可能会在大数据集中降低工作簿性能。

使用 Kutools AI 解锁 Excel 魔法
- 智能执行:执行单元格操作、分析数据和创建图表——所有这些都由简单命令驱动。
- 自定义公式:生成量身定制的公式,优化您的工作流程。
- VBA 编码:轻松编写和实现 VBA 代码。
- 公式解释:轻松理解复杂公式。
- 文本翻译:打破电子表格中的语言障碍。
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. 点击“开发工具”>“Visual Basic”。在Microsoft Visual Basic for Applications窗口中,选择“插入”>“模块”。
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列中的值,如下图所示。在本文中,我将介绍一些在Excel中突出显示单元格值大于另一个单元格的方法。
- 根据下拉列表突出显示行
- 本文将讨论如何根据下拉列表突出显示行,以下面的截图为例,当我从E列的下拉列表中选择“进行中”时,我需要用红色突出显示该行;当我从下拉列表中选择“已完成”时,我需要用蓝色突出显示该行;当我选择“未开始”时,则用绿色突出显示该行。
最佳办公效率工具
🤖 | Kutools AI 助手:基于智能执行、生成代码、创建自定义公式、分析数据并生成图表、调用 Kutools 函数等功能,彻底改变数据分析方式… |
热门功能:查找、高亮或标记重复项 | 删除空行 | 合并不丢失数据的列或单元格 | 四舍五入 ... | |
高级 LOOKUP:多条件 VLookup | 多值 VLookup | 多表查找 | 模糊查找 .... | |
高级下拉列表:快速创建下拉列表 | 从属下拉列表 | 多选下拉列表 .... | |
列管理器: 添加指定数量的列 | 移动列 | 切换隐藏列的可见状态 | 比较区域和列 ... | |
精选功能:网格聚焦 | 设计视图 | 增强编辑栏 | 工作簿与工作表管理器 | 资源库(自动文本) | 日期提取 | 合并数据 | 加密/解密单元格 | 按列表发送电子邮件 | 超级筛选 | 特殊筛选(筛选粗体/斜体/删除线...)... | |
排名前 15 的工具集: 12 种文本 工具(添加文本、删除特定字符等) | 50 多 种图表 类型(甘特图等) | 40 多种实用 公式(基于生日计算年龄等) | 19 种插入 工具(插入二维码、根据路径插入图片等) | 12 种转换 工具(小写金额转大写、汇率转换等) | 7 种合并与分割 工具(高级合并行、分割单元格等) | 还有更多... |
使用 Kutools for Excel 提升您的 Excel 技能,体验前所未有的高效。 Kutools for Excel 提供超过 300 种高级功能来提高生产力并节省时间。 单击此处获取您最需要的功能...
Office Tab 将标签式界面引入 Office,让您的工作更加轻松
- 在 Word、Excel、PowerPoint 中启用标签式编辑和阅读。
- 在同一窗口的新标签页中打开和创建多个文档,而不是在新窗口中进行操作。
- 将您的生产力提升 50%,每天为您减少数百次鼠标点击!