Đối với lịch trình cuộc sống bận rộn hàng ngày của chúng ta, lịch là một công cụ cần thiết. Chúng tôi có lịch treo tường trong phòng hoặc lịch bỏ túi trên điện thoại di động hoặc đồng hồ của chúng tôi. Nhưng tạo lịch tương tác trong Microsoft Excel là niềm vui và kết quả thực sự nhẹ nhàng. Bằng cách thay đổi tháng hoặc năm trong lịch tương tác, bạn có thể tự động điều chỉnh lịch như trong hình ảnh động sau. Trong bài viết này, tôi sẽ hướng dẫn bạn cách tạo lịch tương tác trong Excel.
Bạn có thể tải xuống Excel miễn phí sách bài tập ở đâ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 của tôi, tôi sẽ lập lịch tương tác hàng tháng. Đối với quy trình thứ hai của tôi, bạn sẽ thấy các bước tạo lịch hàng năm tương tác trong Excel.
Đối với cả hai quy trình, tôi sẽ sử dụng tập dữ liệu mẫu sau. Ở đây tôi có hai bộ dữ liệu. Trong tập đầu tiên, tôi có danh sách các ngày 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 hàng tháng tương tác trong Excel
Trong cách tiếp cận đầu tiên của tôi, tôi sẽ chỉ cho bạn quy trình tạo lịch hàng tháng tương tác 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, hãy tạo bốn trường để người dùng nhập 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, hãy nhập lịch đó vào trường năm.
Bước 2:
- Thứ hai, chọn ô C5 dưới tiêu đề tháng và chuyển đến Dữ liệu tab của dải băng.
- Sau đó, từ Công cụ Dữ liệu chọn nhóm Xác thực dữ liệu .
Bước 3:
- Thứ ba, trong mục Cho phép thả xuống chọn Danh sách và trong Nguồn nhập vào ô, chọn phạm vi dữ liệu của mười hai tháng từ bảng tập dữ liệu mẫu.
- Sau đó, nhấn OK .
- Do đó, điều này sẽ tạo danh sách thả xuống 12 tháng trong ô C5 , điều này sẽ giúp ích cho quy trình tiếp theo của tôi.
Bước 4:
- Thứ tư, tạo 7 × 7 trong trang tính như hình sau.
- Sau đó, đặt tên cho bảy cột trên cùng của bảng theo tất cả các tên ngày trong tuần, bắt đầu từ Thứ Hai .
Bước 5:
- Thứ năm, để làm cho tiêu đề lịch trở thành một tiêu đề động, hãy kết hợp các giá trị ô của C5 và C4 như cú pháp sau trong ô hợp nhất từ B7:H7 .
=C5&" "&C4
Bước 6:
- Vì vậy, tiêu đề lịch sẽ giống như hình ảnh sau đây sau bước trước.
- Tại đây, tiêu đề sẽ thay đổi, nếu có bất kỳ giá trị nào từ ô C4 hoặc C5
Bước 7:
- Trong bước này, hãy chèn công thức sau của hàm MATCH trong ô G4 .
=MATCH(C5,'Data Set'!$B$17:$B$28,0) - Đây, chức năng MATCH sẽ tìm kiếm giá trị ô của C5 đó là tên của tháng trong B17:B28 phạm vi ô 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 ảnh sau đây.
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 một số.
- Đây, 1 sẽ đại diện cho Thứ Hai, 2 sẽ đại diện cho Thứ Ba, v.v.
- Vì vậy, để làm điều đó, hãy chèn công thức kết hợp sau của hàm WEEKDAY và the Hàm DATE trong ô G5 .
=WEEKDAY(DATE($C$4,$G$4,1),2) - Tại đây, chức năng Ngày sẽ hiển thị ngày đầu tiên của mỗi tháng trong 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 .
- Ngoài ra, tôi muốn bắt đầu tuần từ Thứ Hai, vì vậy, tôi sẽ chèn 2 là 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 của tháng 1 năm 2023 là 7 chỉ Chủ nhật.
Bước 11:
- Trong bước này, tôi sẽ điền các số vào bảng đã tạo trước đó.
- Đối với điều đó, tôi sẽ sử dụng công thức sau của hàm SEQUENCE trong ô B9 .
=SEQUENCE(6,7)
Bước 12:
- Như có 6 cột và 7 các hàng trong bảng, thông qua hàm trên, bảng sẽ lấp đầy các số từ 1-42 nối tiếp nhau.
Bước 13:
- Trong bước này, tôi sẽ chuyển những số này thành ngày tháng bằng cách thêm công thức của hàm DATE với công thức của bước trước.
- Để làm điều đó, hãy sử dụng công thức sau trong ô B9 .
=DATE(C4,G4,1)+SEQUENCE(6,7)
Bước 14:
- Vì vậy, bạn sẽ nhận đượ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:
- Trong trường hợp này, kết quả đầu ra sau khi chèn công thức không có định dạng chính xác.
- Để sửa đổi chúng, hãy chọn phạm vi ô, B9:H14, và chọn Định dạng số trình đơn thả xuống trong Số nhóm.
Bước 16:
- Sau đó, từ trình đơn thả xuống, hãy chọn Ngày Ngắn làm định dạng cho dải ô đã chọn.
Bước 17:
- Do đó, kết quả sau khi thay đổi định dạng sẽ giống như hình sau.
- Ở hình ảnh sau, bạn có thể thấy ngày bắt đầu hoặc ngày của tháng 1 năm 2023 không đúng trong trường hợp này.
- Vì vậy, tôi sẽ sửa đổi nó để ngày bắt đầu của tháng 1 năm 2023 trở thành Chủ nhật, theo giá trị của ô G5 đó là 7 .
Bước 18:
- Vì vậy, hãy chèn công thức sau vào ô B9 , nơi tôi sẽ trừ giá trị ô của 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 ảnh sau.
- Ngoài ra, có một số ngày bổ sung từ các tháng trước và sau của tháng 1 năm 2023.
Bước 20:
- Vì vậy, tôi sẽ trích xuất những ngày thừa đó khỏi lịch.
- Để làm điều đó, hãy 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 sắp xếp theo thứ tự với việc xem xét các ngày và các ngày trong tuần cùng với các 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 hiển thị toàn bộ điều kiện cùng với tháng, ngày và các ngày trong tuần.
Bước 21:
- Do đó, bạn có thể thấy rằng các ngày bổ sung trong 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ẽ chuyển đổi định dạng của ngày từ bảng trong bước này.
- Để làm điều đó, hãy nhấp chuột phải vào chuột sau khi chọn phạm vi dữ liệu B9:H14 .
- Sau đó, từ ngữ cảnh, hãy chọn Định dạng ô .
Bước 23:
- Ngoài ra, bạn sẽ thấy Định dạng ô hộp thoại.
- Trước tiên, hãy chuyển đến Tùy chỉnh trong hộp thoại.
- Sau đó, trong Loại nhãn, nhập dd .
- Cuối cùng, nhấn OK .
Bước 24:
- Vì vậy, bước trước chỉ giữ lại ngày tháng trong bảng.
Bước 25:
- Hơn nữa, tôi sẽ thay đổi màu phông chữ cho các ngày cuối tuần từ đen sang đỏ trên lịch.
Bước 26:
- Do đó, khi bạn thay đổi tên tháng từ trình đơn thả xuống trong ô C5 , ngày tháng cũng sẽ được thay đổi trong phạm vi dữ liệu của lịch hàng tháng như hình ảnh sau.
Bước 27:
- Ngoài ra, các bước tiếp theo của quy trình này sẽ trình bày cách làm nổi bật các ngày lễ cho mỗi tháng.
- Để làm điều đó, trước hết, hãy chọn phạm vi ô, B5:H14 .
- Thứ hai, đi tới Trang chủ của ruy-băng và chọn Định dạng có điều kiện .
- Thirdly, from the dropdown, choose New Rule .
Step 28:
- In this step, you will get the New Formatting Rule hộp thoại.
- Firstly, under Select a Rule Type header, select Use a formula to determine which cells to format .
- Then, in the formula box, insert the following combination formula of the ISNUMBER function and the VLOOKUP function .
=ISNUMBER(VLOOKUP(B9,'Data Set'!$C:$C,1,0)) - Thirdly, press Format to format the cells that will be true for the above formula.
Phân tích Công thức
=ISNUMBER(VLOOKUP(B9,’Data Set’!$C:$C,1,0))
- VLOOKUP(B9,’Data Set’!$C:$C1,0) :Firstly, theVLOOKUP function here looks for a value in the leftmost column of the holiday table from the Data Set worksheet, and then returns the value in the same row from the column you specify.
- ISNUMBER(VLOOKUP(B9,’Data Set’!$C:$C,1,0)) :Then, the ISNUMBER function will check if the value from the previous step is a number and will return TRUE or FALSE as the output.
Step 29:
- Afterward, choosing the desired color and font style for the specified cells click OK to apply the above formula.
Step 30:
- Finally, your monthly calendar will be ready and will look like the following image after Conditional Formatting to highlight holidays.
Step 31:
- Consequently, change the value of cell C5 to see the calendar for other months with all the formatting.
Đọc thêm: How to Create a Weekly Calendar in Excel (3 Suitable Ways)
2. Creating Interactive Yearly Calendar in Excel
In this section of the article, I will show you the step-by-step procedures to create an interactive yearly calendar in Excel. The steps are quite similar to those of making a monthly calendar. But you will notice some differences and changes regarding the creating and formatting process throughout the steps. So, to learn the whole procedure, go through the following steps.
Bước 1:
- First of all, make twelve 7×7 tables like the following image.
- In these tables, enter the days of the week serially starting from Monday.
Bước 2:
- Secondly, type 1 in cell B6 which is positioned just above the first table.
Bước 3:
- Thirdly, after pressing Enter , right-click on cell B6 .
- Then, from the context select Format Cells .
Bước 4:
- Fourthly, you will see the Format Cells hộp thoại.
- Then, go to the Custom tab of the box, and under the Type header, type January .
- Cuối cùng, nhấn OK .
Bước 5:
- Fifthly, after pressing OK you will see January as the header for the first table.
Bước 6:
- Consequently, follow the above steps to name the other boxes as well.
- So, name all the tables as the twelve months in form of numbers from 1-12 .
Bước 7:
- Moreover, color the header of this table as per your choice like the following image.
Bước 8:
- In this step, to create the dates for the first month write the following formula in cell 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),"") - Here, the formula is the same, as I used it in the monthly calendar section to create dates.
- Moreover, I did not create separate fields for years and months in this sheet so, I am merging all those formulas into a single one.
Bước 9:
- Then, after pressing Enter , you will get the dates for January 2023 in a correct sequence without doing any extra formatting.
- Furthermore, change the font color to red for the dates on the weekends after getting the value.
Bước 10:
- Similarly, insert the above formula in the first cell of the tables for all the months to get the respective dates like the following image.
Bước 11:
- In this step, I will highlight the holidays for the first month of the year.
- For that, firstly select cell range B8:H13 .
Step 12:
- Then, go to the Home tab and select Conditional Formatting .
- Thirdly, choose the New Rule command from the dropdown.
Step 13:
- Then, as shown in the monthly calendar section, edit the Edit Formatting Rule box in the same way.
- Here, I will use the same ISNUMBER and VLOOKUP functions’ combination formula from the monthly calendar section.
- Finally, after all the editing press OK .
=ISNUMBER(VLOOKUP(B9,'Data Set'!$C:$C,1,0))
Step 14:
- Consequently, the dates of the month of January will look like the following image in the yearly calendar.
- Here, through Conditional Formatting , all the holidays have been highlighted.
Step 15:
- Similarly, use Conditional Formatting for the other eleven months to highlight their respective holidays.
Step 16:
- Consequently, change the year value in cell B4 to see the respective year’s calendar.
Đọc thêm: How to Make a Calendar in Excel Without Template (2 Examples)
Kết luận
Đó là phần cuối của bài viết này. Tôi hy vọng bạn thấy bài viết này hữu ích. After reading the above description, you will be able to make an interactive calendar in Excel. Vui lòng chia sẻ thêm bất kỳ thắc mắc hoặc đề xuất nào với chúng tôi trong phần nhận xét bên dưới.
The ExcelDemy team is always concerned about your preferences. Therefore, after commenting, please give us some moments to solve your issues, and we will reply to your queries with the best possible solutions ever.
Các bài viết liên quan
- How to Create a Weekly Calendar in Excel (3 Suitable Ways)
- How to Make a Blank Calendar in Excel (Download Free Template)