Đối với lịch trình bận rộn hàng ngày của chúng ta, lịch là công cụ bắt buộc. Chúng ta có lịch treo tường trong phòng hoặc lịch túi trên điện thoại, đồng hồ. Nhưng tạo một lịch tương tác (interactive calendar) trong Microsoft Excel rất thú vị, và kết quả thực sự dễ chịu. Bằng cách thay đổi tháng hoặc năm trong một lịch tương tác, bạn có thể tự động điều chỉnh lịch như trong animation sau. Trong bài viết này, tôi sẽ chỉ cho bạn cách tạo lịch tương tác trong Excel.
Bạn có thể tải xuống miễn phí workbook Excel tại đây và tự thực hành.
2 Cách dễ dàng để Tạo Lịch tương tác trong Excel
Trong bài viết này, bạn sẽ thấy hai cách tiếp cận khác nhau để tạo lịch tương tác trong Excel. Trong phương pháp đầu tiên, tôi sẽ tạo một lịch tương tác hàng tháng. Với thủ tục thứ hai, bạn sẽ thấy các bước tạo lịch tương tác hàng năm trong Excel.
Đối với cả hai thủ tục, tôi sẽ sử dụng tập dữ liệu mẫu sau. Ở đây tôi có hai tập dữ liệu. Trong tập đầu tiên, tôi có danh sách ngày nghỉ lễ liên bang cho năm 2023. Và trong tập thứ hai, bạn sẽ thấy danh sách các tháng trong năm.
1. Tạo Lịch tương tác hàng tháng trong Excel
Trong cách tiếp cận đầu tiên, tôi sẽ chỉ cho bạn quy trình tạo lịch tương tác hàng tháng trong Excel. Bằng cách áp dụng một số công thức và định dạng trong Excel, bạn có thể hoàn thành nhiệm vụ này. Để tạo lịch, hãy làm theo các bước dưới đây.
Bước 1:
- Trước hết, trong một trang tính mới, tạo bốn trường cho đầu vào người dùng và đặt tên chúng như hình sau.
- Vì tôi đang tạo lịch hàng tháng cho năm 2023, gõ năm đó vào trường năm.
Bước 2:
- Thứ hai, chọn ô C5 dưới tiêu đề tháng và chuyển đến tab Data (Dữ liệu) của Ribbon.
- Sau đó, từ nhóm Data Tools (Công cụ dữ liệu) chọn Data Validation (Xác thực dữ liệu).
Bước 3:
- Thứ ba, dưới menu thả xuống Allow (Cho phép) chọn List (Danh sách), và trong hộp nhập Source (Nguồn), chọn phạm vi dữ liệu của 12 tháng từ trang tính tập dữ liệu mẫu.
- Sau đó, nhấn OK.
- Kết quả, điều này sẽ tạo một danh sách thả xuống 12 tháng trong ô C5, sẽ giúp cho thủ tục tiếp theo của tôi.
Bước 4:
- Thứ tư, tạo một bảng 7×7 trong trang tính như hình sau.
- Sau đó, đặt tên cho 7 cột hàng đầu của bảng theo tên các ngày trong tuần, bắt đầu từ Thứ Hai (Monday).
Bước 5:
- Thứ năm, để làm tiêu đề lịch động, nối giá trị ô C5 và C4 như cú pháp sau trong ô hợp nhất từ B7:H7.
=C5&" "&C4
Bước 6:
- Vậy tiêu đề lịch sẽ trông như hình sau sau bước trước.
- Ở đây, tiêu đề sẽ thay đổi, nếu bất kỳ giá trị nào từ ô C4 hoặc C5 thay đổi.
Bước 7:
- Trong bước này, chèn công thức sau của hàm MATCH vào ô G4.
=MATCH(C5,'Data Set'!$B$17:$B$28,0)- Ở đây, hàm MATCH sẽ tìm giá trị ô C5 đó là tên tháng trong phạm vi ô B17:B28 của tập dữ liệu mẫu và sẽ trả về vị trí tương đối của tháng đó trong phạm vi đó.
Bước 8:
- Sau đó, sau khi nhấn Enter, bạn sẽ tìm thấy số tháng của ô C5 trong G4 giống như hình sau.
Bước 9:
- Trong bước này, tôi sẽ xác định ngày đầu tiên cho mỗi tháng trong năm dưới dạng số.
- Ở đây, 1 đại diện cho Thứ Hai, 2 đại diện cho Thứ Ba, và cứ thế.
- Vì vậy, để làm điều đó, chèn công thức kết hợp sau của hàm WEEKDAY và hàm DATE vào ô G5.
=WEEKDAY(DATE($C$4,$G$4,1),2)- Ở đây, hàm Date sẽ hiển thị ngày đầu tiên của mỗi tháng của một năm cụ thể.
- Sau đó, công thức kết hợp sẽ trả về ngày đầu tiên cho mỗi tháng thông qua hàm WEEKDAY.
- Cũng, tôi muốn bắt đầu tuần từ Thứ Hai nên tôi sẽ chèn 2 làm kiểu trả về trong công thức.
Bước 10:
- Sau đó, công thức sẽ hiển thị số tương ứng với ngày đầu tiên cho Tháng 1 năm 2023 là 7 đại diện cho Chủ Nhật.
Bước 11:
- Trong bước này, tôi sẽ điền số vào bảng đã tạo trước đó.
- Với điều đó, tôi sẽ dùng công thức sau của hàm SEQUENCE trong ô B9.
=SEQUENCE(6,7)
Bước 12:
- Vì có 6 cột và 7 hàng trong bảng, thông qua hàm trên, bảng sẽ được điền số từ 1-42 theo thứ tự.
Bước 13:
- Trong bước này, tôi sẽ chuyển các số này thành ngày bằng cách thêm công thức hàm DATE với công thức của bước trước.
- Để làm điều đó, dùng công thức sau trong ô B9.
=DATE(C4,G4,1)+SEQUENCE(6,7)
Bước 14:
- Vậy, bạn sẽ có kết quả như hình sau qua bảng bằng cách nhấn Enter sau khi chèn công thức.
Bước 15:
- Vì kết quả đầu ra sau khi chèn công thức, trong trường hợp này, không ở đúng định dạng.
- Để sửa chúng, chọn phạm vi ô B9:H14 và chọn menu thả xuống Number Format (Định dạng số) trong nhóm Number (Số).
Bước 16:
- Sau đó, từ menu thả xuống, chọn Short Date (Ngày ngắn) làm định dạng cho phạm vi ô được chọn.
Bước 17:
- Kết quả, kết quả sau khi thay đổi định dạng sẽ trông như hình sau.
- Ở đây, bạn có thể thấy từ hình sau, ngày bắt đầu hoặc ngày của Tháng 1, 2023 không đúng trong trường hợp này.
- Vì vậy, tôi sẽ sửa nó, sao cho ngày bắt đầu của Tháng 1 năm 2023 thành Chủ Nhật, theo giá trị ô G5 là 7.
Bước 18:
- Vậy, chèn công thức sau vào ô B9, nơi tôi sẽ trừ giá trị ô G5 từ giá trị trước của công thức này.
=DATE(C4,G4,1)+SEQUENCE(6,7)-G5
Bước 19:
- Trong bước này, bạn có thể thấy ngày bắt đầu cho Tháng 1, 01-01-2023, là Chủ Nhật từ hình sau.
- Cũng, có một số ngày thừa từ tháng trước và tháng sau của Tháng 1 năm 2023.
Bước 20:
- Vậy, tôi sẽ loại bỏ những ngày thừa đó khỏi lịch.
- Để làm điều đó, chèn công thức kết hợp sau vào ô B9.
=IF(MONTH(DATE(C4,G4,1)+SEQUENCE(6,7)-G5)=G4,DATE(C4,G4,1)+SEQUENCE(6,7)-G5,"")Phân tích công thức
=IF(MONTH(DATE(C4,G4,1)+SEQUENCE(6,7)-G5)=G4,DATE(C4,G4,1)+SEQUENCE(6,7)-G5,"")
- DATE(C4,G4,1)+SEQUENCE(6,7)-G5)=G4,DATE(C4,G4,1)+SEQUENCE(6,7)-G5: Phần này của công thức thể hiện rằng các ngày trong lịch được tổ chức theo thứ tự với sự xem xét ngày và ngày trong tuần cùng với giá trị từ ô C4, G4, và G5.
- IF(MONTH(DATE(C4,G4,1)+SEQUENCE(6,7)-G5)=G4,DATE(C4,G4,1)+SEQUENCE(6,7)-G5,""): Phần này chỉ ra toàn bộ điều kiện cùng với tháng, ngày và ngày trong tuần.
Bước 21:
- Kết quả, bạn có thể thấy các ngày thừa từ lịch hàng tháng đã biến mất sau bước trước.
Bước 22:
- Hơn nữa, tôi sẽ thay đổi định dạng các ngày từ bảng trong bước này.
- Để làm điều đó, chuột phải vào chuột sau khi chọn phạm vi dữ liệu B9:H14.
- Sau đó, từ menu ngữ cảnh, chọn Format Cells (Định dạng ô).
Bước 23:
- Thêm vào đó, bạn sẽ thấy hộp thoại Format Cells.
- Trước hết, chuyển đến tab Custom (Tùy chỉnh) trong hộp thoại.
- Sau đó, dưới nhãn Type (Kiểu), gõ dd.
- Cuối cùng, nhấn OK.
Bước 24:
- Vậy, bước trước chỉ giữ lại ngày trong bảng như kết quả.
Bước 25:
- Hơn nữa, tôi sẽ đổi màu chữ cho cuối tuần từ đen sang đỏ trong lịch.
Bước 26:
- Kết quả, khi bạn thay đổi tên tháng từ danh sách thả xuống trong ô C5, các ngày cũng sẽ thay đổi trong phạm vi dữ liệu của lịch hàng tháng như hình sau.
Bước 27:
- Thêm vào đó, các bước tiếp theo của thủ tục này sẽ trình bày cách làm nổi bật ngày nghỉ lễ cho mỗi tháng.
- Để làm điều đó, trước hết, chọn phạm vi ô, B5:H14.
- Thứ hai, chuyển đến tab Home của Ribbon và chọn Conditional Formatting (Định dạng có điều kiện).
- Thứ ba, từ menu thả xuống, chọn New Rule (Quy tắc mới).
Bước 28:
- Trong bước này, bạn sẽ nhận được hộp thoại New Formatting Rule (Quy tắc định dạng mới).
- Trước hết, dưới tiêu đề Select a Rule Type (Chọn loại quy tắc), 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 hộp công thức, chèn công thức kết hợp sau của hàm ISNUMBER và hàm VLOOKUP.
=ISNUMBER(VLOOKUP(B9,'Data Set'!$C:$C,1,0))- Thứ ba, nhấn Format (Định dạng) để định dạng các ô sẽ đúng cho công thức trên.
Phân tích công thức
=ISNUMBER(VLOOKUP(B9,'Data Set'!$C:$C,1,0))
- VLOOKUP(B9,'Data Set'!$C:$C,1,0): Trước hết, hàm VLOOKUP ở đây tìm giá trị trong cột ngoài cùng bên trái của bảng ngày nghỉ từ trang tính Data Set, và trả về giá trị trong cùng hàng từ cột bạn chỉ định.
- ISNUMBER(VLOOKUP(B9,'Data Set'!$C:$C,1,0)): Sau đó, hàm ISNUMBER sẽ kiểm tra xem giá trị từ bước trước có phải là số và trả về TRUE hoặc FALSE làm đầu ra.
Bước 29:
- Sau đó, chọn màu và kiểu font mong muốn cho các ô chỉ định, nhấp OK để áp dụng công thức trên.
Bước 30:
- Cuối cùng, lịch hàng tháng của bạn sẽ sẵn sàng và trông như hình sau sau Định dạng có điều kiện (Conditional Formatting) để làm nổi bật ngày nghỉ lễ.
Bước 31:
- Kết quả, thay đổi giá trị ô C5 để xem lịch cho các tháng khác với tất cả định dạng.
Đọc thêm: Cách Tạo Lịch hàng tuần trong Excel (3 Cách phù hợp)
2. Tạo Lịch tương tác hàng năm trong Excel
Trong phần này của bài viết, tôi sẽ chỉ cho bạn quy trình từng bước để tạo lịch tương tác hàng năm trong Excel. Các bước khá giống với việc tạo lịch hàng tháng. Nhưng bạn sẽ nhận thấy một số khác biệt và thay đổi liên quan đến quy trình tạo và định dạng xuyên suốt các bước. Vì vậy, để học toàn bộ quy trình, hãy xem qua các bước sau.
Bước 1:
- Trước hết, tạo mười hai bảng 7×7 như hình sau.
- Trong các bảng này, nhập các ngày trong tuần theo thứ tự bắt đầu từ Thứ Hai.
Bước 2:
- Thứ hai, gõ 1 vào ô B6 nằm ngay trên bảng đầu tiên.
Bước 3:
- Thứ ba, sau khi nhấn Enter, chuột phải vào ô B6.
- Sau đó, từ menu ngữ cảnh chọn Format Cells (Định dạng ô).
Bước 4:
- Thứ tư, bạn sẽ thấy hộp thoại Format Cells.
- Sau đó, chuyển đến tab Custom (Tùy chỉnh) của hộp, và dưới tiêu đề Type (Kiểu), gõ January.
- Cuối cùng, nhấn OK.
Bước 5:
- Thứ năm, sau khi nhấn OK bạn sẽ thấy January làm tiêu đề cho bảng đầu tiên.
Bước 6:
- Kết quả, làm theo các bước trên để đặt tên cho các hộp khác cũng vậy.
- Vậy, đặt tên tất cả các bảng như 12 tháng dưới dạng số từ 1-12.
Bước 7:
- Hơn nữa, tô màu tiêu đề của bảng này theo ý thích như hình sau.
Bước 8:
- Trong bước này, để tạo ngày cho tháng đầu tiên, hãy viết công thức sau vào ô B8.
=IF(MONTH(DATE($B$4,B6,1)+SEQUENCE(6,7)-WEEKDAY(DATE($B$4,B6,1),2))=B6,DATE($B$4,B6,1)+SEQUENCE(6,7)-WEEKDAY(DATE($B$4,B6,1),2),"")- Ở đây, công thức giống hệt như tôi đã dùng trong phần lịch hàng tháng để tạo ngày.
- Hơn nữa, tôi không tạo các trường riêng cho năm và tháng trong trang tính này nên tôi gộp tất cả các công thức đó thành một công thức duy nhất.
Bước 9:
- Sau đó, sau khi nhấn Enter, bạn sẽ nhận được ngày cho Tháng 1 năm 2023 theo đúng trình tự mà không cần làm bất kỳ định dạng thêm nào.
- Hơn nữa, đổi màu chữ thành đỏ cho các ngày ở cuối tuần sau khi nhận giá trị.
Bước 10:
- Tương tự, chèn công thức trên vào ô đầu tiên của các bảng cho tất cả các tháng để lấy ngày tương ứng như hình sau.
Bước 11:
- Trong bước này, tôi sẽ làm nổi bật ngày nghỉ lễ cho tháng đầu tiên của năm.
- Với điều đó, trước hết chọn phạm vi ô B8:H13.
Bước 12:
- Sau đó, chuyển đến tab Home và chọn Conditional Formatting (Định dạng có điều kiện).
- Thứ ba, chọn lệnh New Rule (Quy tắc mới) từ menu thả xuống.
Bước 13:
- Sau đó, như đã chỉ ra trong phần lịch hàng tháng, chỉnh sửa hộp Edit Formatting Rule (Chỉnh sửa quy tắc định dạng) theo cùng cách.
- Ở đây, tôi sẽ dùng cùng công thức kết hợp của ISNUMBER và VLOOKUP từ phần lịch hàng tháng.
- Cuối cùng, sau tất cả chỉnh sửa nhấn OK.
=ISNUMBER(VLOOKUP(B9,'Data Set'!$C:$C,1,0))
Bước 14:
- Kết quả, ngày của tháng Tháng 1 sẽ trông như hình sau trong lịch hàng năm.
- Ở đây, thông qua Định dạng có điều kiện (Conditional Formatting), tất cả ngày nghỉ lễ đã được làm nổi bật.
Bước 15:
- Tương tự, dùng Định dạng có điều kiện (Conditional Formatting) cho mười một tháng còn lại để làm nổi bật ngày nghỉ lễ tương ứng của chúng.
Bước 16:
- Kết quả, thay đổi giá trị ô B4 để xem lịch của năm tương ứng.
Đọc thêm: Cách Tạo Lịch trong Excel Không Dùng Mẫu (2 Ví dụ)
Kết luận
Đó là kết thúc bài viết này. Tôi hy vọng bạn thấy bài viết này hữu ích. Sau khi đọc mô tả trên, bạn sẽ có thể tạo lịch tương tác trong Excel. Vui lòng chia sẻ bất kỳ thắc mắc hoặc đề xuất thêm với chúng tôi trong phần bình luận bên dưới.
Đội ngũ ExcelDemy luôn quan tâm đến sở thích của bạn. Do đó, sau khi bình luận, vui lòng cho chúng tôi một chút thời gian để giải quyết vấn đề của bạn, và chúng tôi sẽ trả lời các thắc mắc của bạn với những giải pháp tốt nhất có thể.
Các bài viết liên quan
- Cách Tạo Lịch hàng tuần trong Excel (3 Cách phù hợp)
- Cách Tạo Lịch trống trong Excel (Tải mẫu miễn phí)