Cách tạo lịch hàng năm trong Excel (với các bước dễ dàng)

Một Excel lịch có thể khá hữu ích nếu bạn muốn lập kế hoạch trước và có một lịch trình bận rộn. Ghi nhớ điều này, trong bài viết này, chúng ta sẽ đi qua các bước cùng với giải thích về cách tạo lịch hàng năm trong Excel . Điều thú vị là lịch này rất động và tương tác. Điều đó có nghĩa là lịch sẽ tự cập nhật khi bạn thay đổi giá trị của năm. Ngoài ra, chúng ta cũng sẽ thảo luận về cách tạo lịch hàng tháng bằng Excel VBA .

Bạn có thể tải xuống sách bài tập thực hành từ liên kết bên dưới.

4 bước để tạo lịch hàng năm trong Excel

Bây giờ, trước khi chúng ta phân tích kỹ lưỡng về hướng dẫn này, hãy để tôi cho bạn thấy kết quả cuối cùng sẽ như thế nào. Tại đây, chúng tôi có lịch hàng năm, trong đó các ngày thứ Bảy và Chủ nhật (cuối tuần) được hiển thị bằng màu cam nhạt trong khi Ngày lễ xuất hiện với màu cam sáng.

Ở đây, chúng tôi đã sử dụng Microsoft Excel 365 phiên bản, bạn có thể sử dụng bất kỳ phiên bản nào khác tùy theo sự thuận tiện của bạn.

📌 Bước 01:Tạo dàn bài chính

• Trong bước đầu tiên, chúng tôi sẽ tạo phác thảo lịch bằng cách chia Tháng trên 3 cột và 4 hàng.
• Tiếp theo, nhập 7 ngày trong một Tuần và đánh dấu Cuối tuần , trong trường hợp này là Thứ Bảy và Chủ nhật .

Đọc thêm: Cách tạo lịch hàng tuần trong Excel (3 cách phù hợp)

📌 Bước 02:Nhập tên tháng :

• Thứ hai, chuyển đến B4 ô>> nhập số 1 >> nhấn CTRL + 1 các phím trên bàn phím của bạn.

Bây giờ, thao tác này sẽ mở ra Định dạng ô hộp thoại.

• Ở phần đầu, hãy chuyển đến Số >> chọn tab Tùy chỉnh tùy chọn>> trong Loại , hãy nhập “Tháng Giêng” (trong dấu phẩy ngược)>> cuối cùng, chuyển đến Phông chữ tab.

• Tiếp theo, chọn In đậm-Nghiêng Kiểu phông chữ >> chọn một Màu sắc , ở đây chúng tôi đã chọn màu tím>> nhấn OK nút.

• Lần lượt, chọn B4:H4 phạm vi ô>> nhấn CTRL + 1 các phím để điều hướng đến Định dạng ô cửa sổ.
• Sau đây, hãy chuyển đến Căn chỉnh >> trong tab Ngang , chọn Lựa chọn giữa các tâm tùy chọn>> nhấn OK nút.

Cuối cùng, điều này sẽ hiển thị tên tháng Tháng Giêng như thể hiện trong hình ảnh bên dưới.

• Tương tự, lặp lại quy trình tương tự cho các tháng khác và kết quả đầu ra của bạn sẽ giống như hình sau.

Đọc thêm: Cách tạo lịch trong Excel mà không cần mẫu (2 ví dụ)

📌 Bước 03:Sử dụng các hàm Excel để tạo lịch động

• Thứ ba, di chuyển đến ô B6 và nhập công thức được cung cấp bên dưới.

=IF(MONTH(DATE($B$2,$B$4,1)+SEQUENCE(6,7)-WEEKDAY(DATE($B$2,$B$4,1),2))=$B$4,DATE($B$2,$B$4,1)+SEQUENCE(6,7)-WEEKDAY(DATE($B$2,$B$4,1),2),"")

Đây, B2 và B4 ô tham chiếu đến Năm 2022 và Tháng của tháng 1 tương ứng.

📃 Lưu ý: The Hàm SEQUENCE khả dụng trên Excel 365, Excel 2021 và excel cho web, vì vậy hãy đảm bảo có phiên bản tương thích.

• Bây giờ, nhấn ENTER và công thức sẽ điền tất cả các ngày trong Tháng của tháng 1 .

📃 Lưu ý: Hãy đảm bảo thay đổi Ngày định dạng thành chỉ dd trong Định dạng ô bằng cách nhấn CTRL + 1 các phím trên bàn phím của bạn.

Cuối cùng, kết quả sẽ giống như hình dưới đây.

📌 Bước 04:Cho biết các ngày lễ trong lịch :

• Trong bước thứ tư và cuối cùng, hãy lập Danh sách các ngày lễ trong một trang tính mới như được hiển thị bên dưới.

• Sau đó, chọn B6:H11 ô>> chuyển đến Định dạng có điều kiện thả xuống>> chọn Quy tắc mới tùy chọn.

Ngay lập tức, Quy tắc định dạng mới trình hướng dẫn bật lên.

• Tiếp theo, chọn nút Sử dụng công thức để xác định ô cần định dạng tùy chọn.
• Sau đó, trong Mô tả quy tắc nhập công thức sau.

=ISNUMBER(VLOOKUP(B6,Holidays!$C:$C,1,0))

Đây, B6 ô trỏ đến Thứ Hai trong Tháng của tháng 1 trong khi Ngày lễ! $ C:$ C đại diện cho Ngày trong Ngày lễ trang tính.

Phân tích công thức:

• VLOOKUP (B6, Ngày lễ! $ C:$ C, 1,0)) → hàm Vlookup tìm kiếm một giá trị trong cột ngoài cùng bên trái của bảng, sau đó trả về một giá trị trong cùng một hàng từ cột bạn chỉ định. Đây, B6 ( lookup_value đối số) được ánh xạ từ Ngày lễ! $ C:$ C ( table_array đối số) mảng. Tiếp theo, 1 ( col_index_num đối số) đại diện cho số cột của giá trị tra cứu. Cuối cùng, 0 ( range_lookup đối số) đề cập đến Đối sánh chính xác của giá trị tra cứu.
  • Đầu ra → # N / A
• ISNUMBER (VLOOKUP (B6, Ngày lễ! $ C:$ C, 1,0)) → trở thành
  • ISNUMBER (# N / A) → hàm ISNUMBER kiểm tra xem giá trị có phải là số hay không và trả về TRUE hoặc FALSE . Đây, # N / A là giá trị và vì nó không phải là một số nên hàm trả về FALSE .
  • Đầu ra → FALSE

Bây giờ, điều này sẽ làm nổi bật ngày lễ vào ngày 1 tháng 1 có màu cam sáng.

Trong một phong cách tương tự, hãy lặp lại quy trình tương tự cho các tháng khác và đầu ra của bạn sẽ giống như ảnh chụp màn hình được hiển thị bên dưới.

Cách tạo lịch hàng tháng

Điều gì xảy ra nếu bạn muốn tạo lịch hàng tháng ? Phương pháp tiếp theo của chúng tôi đã bao gồm bạn! Ở đây, chúng tôi sẽ sử dụng Mã VBA để tự động hóa nhiệm vụ cho chúng tôi, do đó, chúng ta hãy xem nó hoạt động.

📌 Các bước :

• Trước hết, hãy điều hướng đến Nhà phát triển >> nhấp vào tab Visual Basic nút.

Thao tác này sẽ mở Visual Basic Editor trong một cửa sổ mới.

• Trong bước sau, đi tới Chèn tab>> chọn Mô-đun .

Để dễ tham khảo, bạn có thể sao chép mã từ đây và dán vào cửa sổ như hình dưới đây.

Sub CalendarMaker()
'This code was taken from extendoffice.com
 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

• Đến lượt mình, đóng VBA cửa sổ>> nhấp vào Macro nút.

Thao tác này sẽ mở Macro hộp thoại.

• Sau đó, chọn CalendarMaker macro>> nhấn vào nút Chạy nút.

Bây giờ, thao tác này sẽ mở ra một Hộp nhập liệu >> nhập Tên tháng và Năm như hình bên dưới.

Sau đó, kết quả sẽ giống như hình dưới đây.

Kết luận

Tôi hy vọng bài viết này sẽ giúp bạn hiểu cách tạo lịch hàng năm trong Excel. Nếu bạn có bất kỳ thắc mắc nào, hãy để lại bình luận bên dưới. Ngoài ra, nếu bạn muốn đọc thêm các bài viết như thế này, bạn có thể truy cập trang web ExcelDemy của chúng tôi , một nhà cung cấp giải pháp Excel một cửa.

Bài viết liên quan

• Cách Tạo Lịch Trống trong Excel (Tải xuống Mẫu Miễn phí)