跳至主要内容

如何在Excel中创建日历?

Author: Tech Support Last Modified: 2025-05-08

Excel日历可以帮助您跟踪重要事件,例如某人何时开始新工作或某事需要交付的时间。它让查看这些日期变得简单明了。在本指南中,我将向您展示如何在Excel中创建月历和年历。我们将探讨使用模板进行快速设置,以及如何从头开始制作一个日历,以满足那些希望获得更多控制权的用户需求。通过这种方式,无论是用于工作还是个人计划,您都可以保持井井有条。

create a calendar

使用Excel日历模板创建年历

使用Kutools for Excel快速创建月历或年历

使用VBA代码创建月历


使用Excel日历模板创建年历

使用此方法时,必须确保您的计算机连接到网络,以便您可以下载日历模板

1. 转到文件选项卡,点击左侧窗格中的新建按钮,并从建议的搜索中点击日历。参见截图:

click New button, and click Calendars

2. 选择您喜欢的日历模板之一,双击它以创建年历。

 select one of the calendar templates

结果

the calendar is inserted


使用Kutools for Excel快速创建月历或年历

Kutools for Excel永久日历工具可以快速在新工作簿中创建自定义的月历或年历,并且每个月历都将包含在一个新的工作表中。

Kutools for Excel 提供了超过 300 种高级功能,简化复杂任务,提升创造力与效率。 通过集成 AI 能力,Kutools 能够精准自动执行任务,让数据管理变得轻松简单。Kutools for Excel 的详细信息...         免费试用...

安装Kutools for Excel后,请点击Kutools Plus > 工作表 > 永久日历。在弹出的永久日历对话框中,请按如下操作:

  • 要创建月历,请通过“”和“”下拉列表指定要创建日历的月份,然后点击创建

specify the months to create a monthly calendar

  • 要创建年历,请通过“”和“”下拉列表指定要创建日历的年份,然后点击创建

 specify the year  create a yearly calendar

结果
  • 月历:

 A monthly calendar is inserted

  • 年历:

A yearly calendar is inserted

提示:要使用此功能,您应该先安装 Kutools for Excel ,请 点击下载并享受30天免费试用 现在。

使用VBA代码创建月历

使用以下VBA代码,您可以快速创建月历。请按如下操作:

1. 按Alt + F11键打开Microsoft Visual Basic for Applications窗口。

2. 将显示一个新窗口。点击插入 > 模块,然后在模块中输入以下代码:

 Sub CalendarMaker()
ActiveSheet.Protect DrawingObjects:=False, Contents:=False, _
Scenarios:=False
Application.ScreenUpdating = False
On Error GoTo MyErrorTrap
Range("a1:g14").Clear
MyInput = InputBox("Type in Month and year for Calendar ")
If MyInput = "" Then Exit Sub
StartDay = DateValue(MyInput)
If Day(StartDay) <> 1 Then
StartDay = DateValue(Month(StartDay) & "/1/" & _
Year(StartDay))
End If
Range("a1").NumberFormat = "mmmm yyyy"
With Range("a1:g1")
.HorizontalAlignment = xlCenterAcrossSelection
.VerticalAlignment = xlCenter
.Font.Size = 18
.Font.Bold = True
.RowHeight = 35
End With
With Range("a2:g2")
.ColumnWidth = 11
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Orientation = xlHorizontal
.Font.Size = 12
.Font.Bold = True
.RowHeight = 20
End With
Range("a2") = "Sunday"
Range("b2") = "Monday"
Range("c2") = "Tuesday"
Range("d2") = "Wednesday"
Range("e2") = "Thursday"
Range("f2") = "Friday"
Range("g2") = "Saturday"
With Range("a3:g8")
.HorizontalAlignment = xlRight
.VerticalAlignment = xlTop
.Font.Size = 18
.Font.Bold = True
.RowHeight = 21
End With
Range("a1").Value = Application.Text(MyInput, "mmmm yyyy")
DayofWeek = Weekday(StartDay)
CurYear = Year(StartDay)
CurMonth = Month(StartDay)
FinalDay = DateSerial(CurYear, CurMonth + 1, 1)
Select Case DayofWeek
Case 1
Range("a3").Value = 1
Case 2
Range("b3").Value = 1
Case 3
Range("c3").Value = 1
Case 4
Range("d3").Value = 1
Case 5
Range("e3").Value = 1
Case 6
Range("f3").Value = 1
Case 7
Range("g3").Value = 1
End Select
For Each cell In Range("a3:g8")
RowCell = cell.Row
ColCell = cell.Column
If cell.Column = 1 And cell.Row = 3 Then
ElseIf cell.Column <> 1 Then
If cell.Offset(0, -1).Value >= 1 Then
cell.Value = cell.Offset(0, -1).Value + 1
If cell.Value > (FinalDay - StartDay) Then
cell.Value = ""
Exit For
End If
End If
ElseIf cell.Row > 3 And cell.Column = 1 Then
cell.Value = cell.Offset(-1, 6).Value + 1
If cell.Value > (FinalDay - StartDay) Then
cell.Value = ""
Exit For
End If
End If
Next
For x = 0 To 5
Range("A4").Offset(x * 2, 0).EntireRow.Insert
With Range("A4:G4").Offset(x * 2, 0)
.RowHeight = 65
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlTop
.WrapText = True
.Font.Size = 10
.Font.Bold = False
.Locked = False
End With
With Range("A3").Offset(x * 2, 0).Resize(2, _
7).Borders(xlLeft)
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
With Range("A3").Offset(x * 2, 0).Resize(2, _
7).Borders(xlRight)
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
Range("A3").Offset(x * 2, 0).Resize(2, 7).BorderAround _
Weight:=xlThick, ColorIndex:=xlAutomatic
Next
If Range("A13").Value = "" Then Range("A13").Offset(0, 0) _
.Resize(2, 8).EntireRow.Delete
ActiveWindow.DisplayGridlines = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, _
Scenarios:=True
ActiveWindow.WindowState = xlMaximized
ActiveWindow.ScrollRow = 1
Application.ScreenUpdating = True
Exit Sub
MyErrorTrap:
MsgBox "You may not have entered your Month and Year correctly." _
& Chr(13) & "Spell the Month correctly" _
& " (or use 3 letter abbreviation)" _
& Chr(13) & "and 4 digits for the Year"
MyInput = InputBox("Type in Month and year for Calendar")
If MyInput = "" Then Exit Sub
Resume
End Sub 

3. 然后点击运行 按钮或按F5键运行应用程序。现在会弹出一个提示框,您可以在空白框中输入月份和年份。

 input the month and the year in the textbox

结果

 a monthly calendar is inserted