如何在Excel中創(chuàng)建每月/每年的日歷？

在某些時候，您需要在Excel中創(chuàng)建特定的月或年日歷，如何快速解決呢？ 本教程介紹了在Excel中快速創(chuàng)建每月或每年日歷的技巧。

通過Excel模板創(chuàng)建每月或每年的日歷

通過VBA創(chuàng)建月歷

通過萬年歷輕松創(chuàng)建月度或年度日歷

通過Excel模板創(chuàng)建每月或每年的日歷驚人的！ 在 Excel 中使用高效的選項卡，如 Chrome、Firefox 和 Safari！每天節(jié)省50％的時間，并減少數(shù)千次鼠標單擊！

在Excel中，您可以使用日歷模板來創(chuàng)建每月或每年的日歷。

1.在Excel /中，單擊 文件 > 全新，在Excel 2007中，單擊 辦公按鈕 > 全新，然后在彈出窗口的右側(cè)部分中輸入 日歷 進入搜索引擎。 看截圖：

在Excel /中

在Excel 2007中

2。 按 輸入，然后窗口中會列出多種類型的日歷。 選擇所需的一種日歷，然后單擊 下載（或創(chuàng)建） 在右窗格中。 看截圖：

現(xiàn)在，在新工作簿中創(chuàng)建了一個日歷。 看截圖：

通過VBA創(chuàng)建月歷

有時，您需要為指定的月份創(chuàng)建一個月的日歷，例如2015年XNUMX月。使用上述方法很難找到這樣的日歷模板。 在這里，我介紹了VBA代碼，以幫助您創(chuàng)建特定的每月日歷。

1。 按 Alt + F11鍵 打開鑰匙 Microsoft Visual Basic應用程序 窗口中，單擊 插頁 > 模塊，然后將以下VBA代碼復制并粘貼到窗口中。

VBA：創(chuàng)建每月日歷。

Sub CalendarMaker()Unprotect sheet if had previous calendar to prevent error.ActiveSheet.Protect DrawingObjects:=False, Contents:=False, _Scenarios:=FalsePrevent screen flashing while drawing calendar.Application.ScreenUpdating = FalseSet up error trapping.On Error GoTo MyErrorTrapClear area a1:g14 including any previous calendar.Range("a1:g14").ClearUse InputBox to get desired month and year and set variableMyInput.MyInput = InputBox("Type in Month and year for Calendar ")Allow user to end macro with Cancel in InputBox.If MyInput = "" Then Exit SubGet the date value of the beginning of inputted month.StartDay = Datevalue(MyInput)Check if valid date but not the first of the month-- if so, reset StartDay to first day of month.If Day(StartDay) <> 1 ThenStartDay = Datevalue(Month(StartDay) & "/1/" & _Year(StartDay))End IfPrepare cell for Month and Year as fully spelled out.Range("a1").NumberFormat = "mmmm yyyy"Center the Month and Year label across a1:g1 with appropriatesize, height and bolding.With Range("a1:g1").HorizontalAlignment = xlCenterAcrossSelection.VerticalAlignment = xlCenter.Font.Size = 18.Font.Bold = True.RowHeight = 35End WithPrepare a2:g2 for day of week labels with centering, size,height and bolding.With Range("a2:g2").ColumnWidth = 11.VerticalAlignment = xlCenter.HorizontalAlignment = xlCenter.VerticalAlignment = xlCenter.Orientation = xlHorizontal.Font.Size = 12.Font.Bold = True.RowHeight = 20End WithPut days of week in a2:g2.Range("a2") = "Sunday"Range("b2") = "Monday"Range("c2") = "Tuesday"Range("d2") = "Wednesday"Range("e2") = "Thursday"Range("f2") = "Friday"Range("g2") = "Saturday"Prepare a3:g7 for dates with left/top alignment, size, heightand bolding.With Range("a3:g8").HorizontalAlignment = xlRight.VerticalAlignment = xlTop.Font.Size = 18.Font.Bold = True.RowHeight = 21End WithPut inputted month and year fully spelling out into "a1".Range("a1").Value = Application.Text(MyInput, "mmmm yyyy")Set variable and get which day of the week the month starts.DayofWeek = WeekDay(StartDay)Set variables to identify the year and month as separatevariables.CurYear = Year(StartDay)CurMonth = Month(StartDay)Set variable and calculate the first day of the next month.FinalDay = DateSerial(CurYear, CurMonth + 1, 1)Place a "1" in cell position of the first day of the chosenmonth based on DayofWeek.Select Case DayofWeekCase 1Range("a3").Value = 1Case 2Range("b3").Value = 1Case 3Range("c3").Value = 1Case 4Range("d3").Value = 1Case 5Range("e3").Value = 1Case 6Range("f3").Value = 1Case 7Range("g3").Value = 1End SelectLoop through range a3:g8 incrementing each cell after the "1"cell.For Each cell In Range("a3:g8")RowCell = cell.RowColCell = cell.ColumnDo if "1" is in first column.If cell.Column = 1 And cell.Row = 3 ThenDo if current cell is not in 1st column.ElseIf cell.Column <> 1 ThenIf cell.Offset(0, -1).Value >= 1 Thencell.Value = cell.Offset(0, -1).Value + 1Stop when the last day of the month has beenentered.If cell.Value > (FinalDay - StartDay) Thencell.Value = ""Exit loop when calendar has correct number ofdays shown.Exit ForEnd IfEnd IfDo only if current cell is not in Row 3 and is in Column 1.ElseIf cell.Row > 3 And cell.Column = 1 Thencell.Value = cell.Offset(-1, 6).Value + 1Stop when the last day of the month has been entered.If cell.Value > (FinalDay - StartDay) Thencell.Value = ""Exit loop when calendar has correct number of daysshown.Exit ForEnd IfEnd IfNextCreate Entry cells, format them centered, wrap text, and borderaround days.For x = 0 To 5Range("A4").Offset(x * 2, 0).EntireRow.InsertWith Range("A4:G4").Offset(x * 2, 0).RowHeight = 65.HorizontalAlignment = xlCenter.VerticalAlignment = xlTop.WrapText = True.Font.Size = 10.Font.Bold = FalseUnlock these cells to be able to enter text later aftersheet is protected..Locked = FalseEnd WithPut border around the block of dates.With Range("A3").Offset(x * 2, 0).Resize(2, _7).Borders(xlLeft).Weight = xlThick.ColorIndex = xlAutomaticEnd WithWith Range("A3").Offset(x * 2, 0).Resize(2, _7).Borders(xlRight).Weight = xlThick.ColorIndex = xlAutomaticEnd WithRange("A3").Offset(x * 2, 0).Resize(2, 7).BorderAround _Weight:=xlThick, ColorIndex:=xlAutomaticNextIf Range("A13").Value = "" Then Range("A13").Offset(0, 0) _.Resize(2, 8).EntireRow.DeleteTurn off gridlines.ActiveWindow.DisplayGridlines = FalseProtect sheet to prevent overwriting the dates.ActiveSheet.Protect DrawingObjects:=True, Contents:=True, _Scenarios:=TrueResize window to show all of calendar (may have to be adjustedfor video configuration).ActiveWindow.WindowState = xlMaximizedActiveWindow.ScrollRow = 1Allow screen to redraw with calendar showing.Application.ScreenUpdating = TruePrevent going to error trap unless error found by exiting Subhere.Exit SubError causes msgbox to indicate the problem, provides new input box,and resumes at the line that caused the error.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 SubResumeEnd SubCopy

VBA來自此網(wǎng)站 https://support.microsoft.com/en-us/kb/150774

2。 按 F5 鍵或 運行 按鈕，并彈出一個對話框，提醒您鍵入創(chuàng)建日歷所需的特定月份，請參見屏幕截圖：

3。 點擊 OK。 現(xiàn)在，將在活動工作表中創(chuàng)建一個2015年XNUMX月的日歷。

但是在上述方法中，存在一些局限性，例如，如果要一次創(chuàng)建從一月到五月的日歷，則需要使用上述兩種方法五次創(chuàng)建日歷。 現(xiàn)在，我介紹一個方便的實用程序來快速輕松地解決它

通過萬年歷輕松創(chuàng)建月度或年度日歷

Perpetual Calendar 是的強大工具之一 Kutools for Excel，它可以幫助您一次在Excel中快速創(chuàng)建每月或每年的日歷。

Kutools for Excel, 與超過 300 方便的功能，使您的工作更加輕松。功能齊全60天

1。 點擊 企業(yè) > 工作表 > Perpetual Calendar。 看截圖：

2.在彈出的對話框中，指定要創(chuàng)建日歷的月份持續(xù)時間，然后單擊 創(chuàng)建。 看截圖：

然后使用五個日歷工作表創(chuàng)建一個新的工作簿。 看截圖：

提示：

如果只想創(chuàng)建特定的月份日歷，則只需在對話框的“從”和“到”文本框中選擇相同的月份。

單擊此處以了解更多關于萬年歷的信息

最佳辦公生產(chǎn)力工具

將小時轉(zhuǎn)化為分鐘 Kutools for Excel!

準備好增強您的 Excel 任務了嗎？ 利用的力量 Kutools for Excel - 您終極的節(jié)省時間的工具。 簡化復雜的任務并像專業(yè)人士一樣瀏覽數(shù)據(jù)。 以閃電般的速度體驗 Excel！

為什么需要 Kutools for Excel