Skip to main content

如何在 Excel 中創建日曆?

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

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