Một lịch Excel có thể rất hữu ích nếu bạn thích lập kế hoạch trước và có lịch trình bận rộn. Nhớ điều này, trong bài viết này, chúng tôi sẽ đi qua các bước cùng với giải thích về cách tạo lịch năm trong Excel. Thú vị là lịch này là động và tương tác. Nghĩa là lịch tự cập nhật khi bạn thay đổi giá trị năm. Ngoài ra, chúng tôi cũng sẽ thảo luận cách tạo lịch tháng bằng VBA của Excel.
Bạn có thể tải sổ làm việc thực hành từ liên kết dưới đây.
4 bước tạo lịch năm trong Excel
Bây giờ, trước khi chúng ta đi sâu vào hướng dẫn này, hãy để tôi cho bạn thấy đầu ra cuối cùng sẽ trông như thế nào. Ở đây, chúng ta có lịch năm, nơi Thứ Bảy và Chủ Nhật (cuối tuần) hiển thị bằng cam nhạt trong khi Ngày lễ (Holidays) xuất hiện bằng cam sáng.
Ở đây, chúng tôi đã dùng phiên bản Microsoft Excel 365, bạn có thể dùng bất kỳ phiên bản nào khác theo sự tiện lợi.
📌 Bước 01: Tạo khung chính
- Ở bước đầu tiên, chúng ta sẽ tạo khung của lịch bằng cách chia các Tháng (Months) thành 3 cột và 4 hàng.
- Tiếp theo, nhập 7 ngày trong một Tuần (Week) và làm nổi bật Cuối tuần (Weekends), trong trường hợp này là Thứ Bảy và Chủ Nhật.
Đọc thêm: Cách tạo lịch tuần trong Excel (3 cách phù hợp)
📌 Bước 02: Nhập tên tháng:
- Thứ hai, vào ô B4 >> gõ số 1 >> nhấn phím CTRL + 1 trên bàn phím.
Bây giờ, điều này mở hộp thoại Format Cells (Định dạng ô).
- Ban đầu, chuyển đến tab Number (Số) >> chọn tùy chọn Custom (Tùy chỉnh) >> trong trường Type (Kiểu), nhập “January” (trong ngoặc kép) >> cuối cùng, chuyển đến tab Font (Phông chữ).
- Tiếp theo, chọn Bold-Italic Font style (Kiểu phông) >> chọn một Color (Màu), ở đây chúng tôi chọn tím >> nhấp vào nút OK.
- Kế tiếp, chọn phạm vi ô B4:H4 >> nhấn phím CTRL + 1 để điều hướng đến cửa sổ Format Cells.
- Sau đó, tiến đến tab Alignment (Căn chỉnh) >> trong phần Horizontal (Ngang), chọn tùy chọn Center Across Selection (Căn giữa lựa chọn) >> nhấn nút OK.
Cuối cùng, điều này sẽ hiển thị tên tháng January như hình dưới.
- Tương tự, 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ẽ trông như hình sau.
Đọc thêm: Cách tạo lịch trong Excel không dùng mẫu (2 ví dụ)
📌 Bước 03: Sử dụng hàm Excel để tạo lịch động
- Thứ ba, di chuyển đến ô B6 và nhập công thức dưới đây.
=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 Một (January) tương ứng.
📃 Lưu ý: Hàm SEQUENCE có sẵn trên Excel 365, Excel 2021, và Excel cho web, vì vậy hãy đảm bảo bạn có phiên bản tương thích.
- Bây giờ, nhấn phím ENTER và công thức sẽ điền tất cả các ngày cho Tháng của Tháng Một.
📃 Lưu ý: Vui lòng đảm bảo thay đổi định dạng Date (Ngày) thành chỉ dd trong cửa sổ Format Cells bằng cách nhấn phím CTRL + 1 trên bàn phím.
Cuối cùng, kết quả nên trông như hình dưới.
📌 Bước 04: Đánh dấu ngày lễ trong lịch:
- Ở bước tư và cuối cùng, tạo Danh sách ngày lễ (List of Holidays) trong một trang tính mới như dưới đây.
- Sau đó, chọn ô B6:H11 >> vào menu thả xuống Conditional Formatting (Định dạng có điều kiện) >> chọn tùy chọn New Rule (Quy tắc mới).
Ngay lập tức, trình hướng dẫn New Formatting Rule (Quy tắc định dạng mới) xuất hiện.
- Tiếp theo, chọn tùy chọn Use a formula to determine which cells to format (Sử dụng công thức xác định ô cần định dạng).
- Sau đó, trong Rule Description (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 (Monday) trong Tháng của Tháng Một (January) trong khi Holidays!$C:$C đại diện cho Ngày (Dates) trong trang Holidays.
Phân tích công thức:
- VLOOKUP(B6,Holidays!$C:$C,1,0)) → hàm VLOOKUP tìm giá trị trong cột trái nhất của bảng, sau đó trả về giá trị cùng hàng từ cột bạn chỉ định. Ở đây, B6 ( lookup_value tham số) được ánh xạ từ mảng Holidays!$C:$C (table_array tham số). Tiếp theo, 1 (col_index_num tham số) đại diện cho số cột của giá trị tra cứu. Cuối cùng, 0 (range_lookup tham số) đề cập đến Khớp chính xác (Exact match) của giá trị tra cứu.
- Kết quả → #N/A
- ISNUMBER(VLOOKUP(B6,Holidays!$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à tham số value, và vì nó không phải số nên hàm trả về FALSE.
- Kết quả → FALSE
Bây giờ, điều này sẽ làm nổi bật ngày lễ Ngày 1 Tháng Một (January 1st) bằng màu cam sáng.
theo phong cách tương tự, 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ẽ trông như các ảnh chụp màn hình dưới đây.
Cách tạo lịch tháng
Điều gì xảy ra nếu bạn muốn tạo lịch tháng? Phương pháp tiếp theo của chúng tôi sẽ giúp bạn! Ở đây, chúng ta sẽ dùng Mã VBA để tự động hóa nhiệm vụ cho chúng ta, vì vậy hãy xem nó hoạt động như thế nào.
📌 Các bước:
- Thứ nhất và quan trọng nhất, điều hướng đến tab Developer (Nhà phát triển) >> nhấp nút Visual Basic.
Điều này mở Visual Basic Editor (Trình soạn thảo Visual Basic) trong cửa sổ mới.
- Ở bước tiếp theo, vào tab Insert (Chèn) >> chọn Module (Mô-đun).
Để tiện 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.
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
- Kế tiếp, đóng cửa sổ VBA >> nhấp nút Macros (Macro).
Điều này mở hộp thoại Macros.
- Sau đó, chọn macro CalendarMaker >> nhấn nút Run (Chạy).
Bây giờ, điều này mở hộp nhập liệu (Input box) >> nhập Tên tháng (Month Name) và Năm (Year) như dưới đây.
Kế tiếp, kết quả sẽ trông như hình dưới.
Kết luận
Tôi hy vọng bài viết này giúp bạn hiểu cách tạo lịch năm trong Excel. Nếu bạn có bất kỳ thắc mắc nào, vui lòng để lại bình luận dưới đây. Ngoài ra, nếu bạn muốn đọc thêm bài viết như thế này, bạn có thể truy cập trang web ExcelDemy, nhà cung cấp giải pháp Excel một chỗ.