Công thức Excel: Lưới lịch động -

Mục lục

Tóm lược

Bạn có thể thiết lập lưới lịch động trên trang tính Excel với một loạt công thức, như được giải thích trong bài viết này. Trong ví dụ được hiển thị, công thức trong B6 là:

=start-CHOOSE(WEEKDAY(start),0,1,2,3,4,5,6)

trong đó "bắt đầu" là dải ô được đặt tên K5 và chứa ngày 1 tháng 9 năm 2018.

Giải trình

Lưu ý: Ví dụ này giả định ngày bắt đầu sẽ được cung cấp là ngày đầu tiên của tháng. Xem bên dưới để biết công thức sẽ tự động trả về ngày đầu tiên của tháng hiện tại.

Với cách bố trí lưới như hình, vấn đề chính là tính toán ngày trong ô đầu tiên trong lịch (B6). Điều này được thực hiện với công thức này:

=start-CHOOSE(WEEKDAY(start),0,1,2,3,4,5,6)

Công thức này tính ra Chủ nhật trước ngày đầu tiên của tháng bằng cách sử dụng hàm CHOOSE để "lùi" số ngày phù hợp với Chủ nhật trước đó. CHOOSE hoạt động hoàn hảo trong tình huống này, vì nó cho phép các giá trị tùy ý cho mỗi ngày trong tuần. Chúng tôi sử dụng tính năng này để lùi về 0 ngày khi ngày đầu tiên của tháng là Chủ nhật. Thông tin chi tiết về vấn đề này được cung cấp tại đây.

Với ngày đầu tiên được thiết lập ở B6, các công thức khác trong lưới chỉ cần tăng ngày trước đó lên một, bắt đầu bằng công thức trong C6:

=IF(B6"",B6,$H5)+1

Công thức này kiểm tra ô ngay bên trái để tìm một giá trị. Nếu không có giá trị nào được tìm thấy, nó sẽ kéo một giá trị từ cột H ở hàng trên. Lưu ý $ H5 là một tham chiếu hỗn hợp, để khóa cột vì công thức được sao chép trong toàn bộ lưới. Công thức giống nhau được sử dụng trong tất cả các ô ngoại trừ B6.

Quy tắc định dạng có điều kiện

Lịch sử dụng các công thức định dạng có điều kiện thay đổi định dạng để tô bóng cho các tháng trước đó và trong tương lai, và để đánh dấu ngày hiện tại. Cả hai quy tắc đều được áp dụng cho toàn bộ lưới. Đối với tháng trước và tháng tiếp theo, công thức là:

=MONTH(B6)MONTH(start)

Đối với ngày hiện tại, công thức là:

=B6=TODAY()

Để biết thêm chi tiết, hãy xem: Định dạng có điều kiện với công thức (10 ví dụ)

Tiêu đề lịch

Tiêu đề lịch - tháng và năm - được tính bằng công thức này trong ô B4:

=start

Được định dạng với định dạng số tùy chỉnh "mmmm yyyy". Để căn giữa tiêu đề phía trên lịch, phạm vi B4: H4 có căn chỉnh theo chiều ngang được đặt thành "căn giữa trên vùng lựa chọn". Đây là một tùy chọn tốt hơn so với hợp nhất các ô, vì nó không làm thay đổi cấu trúc lưới trong trang tính.

Lịch vạn niên với ngày hiện tại

Để tạo lịch cập nhật tự động dựa trên ngày hiện tại, bạn có thể sử dụng công thức như sau trong K5:

=EOMONTH(TODAY(),-1)+1

Công thức này lấy ngày hiện tại bằng hàm TODAY, sau đó lấy ngày đầu tiên của tháng hiện tại bằng hàm EOMONTH. Thay TODAY () bằng bất kỳ ngày nào đã cho để tạo lịch vào một tháng khác. Thêm chi tiết về cách hoạt động của EOMONTH tại đây.