Nội dung
Giả sử chúng ta cần nhanh chóng và tốn ít công sức nhất để tạo một lịch hàng năm tự động hiển thị ngày của các giai đoạn dự án (hoặc kỳ nghỉ của nhân viên, hoặc đào tạo, v.v.)
Phôi
Hãy bắt đầu với khoảng trống:
Như bạn có thể thấy, mọi thứ đều đơn giản ở đây:
- Hàng là tháng, cột là ngày.
- Ô A2 chứa năm lịch đang được tạo. Trong các ô A4: A15 - số phụ của tháng. Chúng ta sẽ cần cả hai muộn hơn một chút để tạo ngày trong lịch.
- Ở bên phải của bảng là tên của các giai đoạn với ngày bắt đầu và ngày kết thúc. Bạn có thể cung cấp trước các ô trống cho các giai đoạn mới được thêm vào trong tương lai.
Điền vào lịch với ngày tháng và ẩn chúng
Bây giờ chúng ta hãy điền vào lịch của chúng tôi với ngày. Chọn ô C4 đầu tiên và nhập hàm vào đó NGÀY (NGÀY), tạo ra một ngày từ một năm, tháng và số ngày:
Sau khi nhập công thức, nó phải được sao chép vào toàn bộ phạm vi từ ngày 1 tháng 31 đến ngày 4 tháng 15 (CXNUMX: AGXNUMX). Vì các ô hẹp, thay vì ngày được tạo, chúng ta sẽ thấy dấu thăng (#). Tuy nhiên, khi bạn di chuột qua bất kỳ ô nào như vậy, bạn có thể thấy nội dung thực tế của nó trong chú giải công cụ:
Để giữ cho các lưới không bị che khuất, chúng ta có thể ẩn chúng bằng một định dạng tùy chỉnh thông minh. Để làm điều này, hãy chọn tất cả các ngày, mở cửa sổ Định dạng ô và trên tab Con số (số) chọn tùy chọn Tất cả các định dạng (Tập quán). Sau đó trong lĩnh vực Một loại nhập ba dấu chấm phẩy liên tiếp (không có dấu cách!) và nhấn OK. Nội dung của các ô sẽ bị ẩn và các lưới sẽ biến mất, mặc dù trên thực tế, ngày tháng trong các ô sẽ vẫn còn - đây chỉ là khả năng hiển thị.
Đánh dấu sân khấu
Bây giờ, bằng cách sử dụng định dạng có điều kiện, hãy thêm đánh dấu cột mốc vào các ô có ngày bị ẩn. Chọn tất cả các ngày trong phạm vi C4: AG15 và chọn trên tab Trang chủ - Định dạng có điều kiện - Tạo quy tắc (Trang chủ - Định dạng có điều kiện - Tạo Quy tắc). Trong cửa sổ mở ra, hãy chọn tùy chọn Sử dụng công thức để xác định ô cần định dạng (Sử dụng công thức để trì hoãn định dạng ô nào) và nhập công thức:
Công thức này kiểm tra mọi ô ngày từ C4 đến cuối năm để xem liệu nó có nằm giữa đầu và cuối của mỗi mốc hay không. Đầu ra sẽ chỉ là 4 khi cả hai điều kiện được chọn trong ngoặc đơn (C4>=$AJ$13:$AJ$4) và (C4<=$AK$13:$AK$1) tạo ra giá trị TRUE lô-gic, mà Excel hiểu là 0 ( tốt , FALSE giống như 4, tất nhiên). Ngoài ra, hãy đặc biệt chú ý đến thực tế là các tham chiếu đến ô ban đầu CXNUMX là tương đối (không có $) và các phạm vi của các giai đoạn - tuyệt đối (có hai $).
Sau khi nhấp vào OK chúng ta sẽ thấy các mốc quan trọng trong lịch của mình:
Đánh dấu các giao lộ
Nếu ngày của một số giai đoạn trùng nhau (những độc giả chú ý chắc hẳn đã nhận thấy thời điểm này cho giai đoạn 1 và 6!), Thì tốt hơn là đánh dấu xung đột này trong biểu đồ của chúng tôi bằng một màu khác bằng cách sử dụng quy tắc định dạng có điều kiện khác. Nó thực tế là XNUMX-XNUMX tương tự như trước đó, ngoại trừ việc chúng tôi đang tìm kiếm các ô được bao gồm trong nhiều hơn một giai đoạn:
Sau khi nhấp vào OK quy tắc như vậy sẽ làm nổi bật rõ ràng sự trùng lặp của các ngày trong lịch của chúng tôi:
Loại bỏ những ngày thừa trong tháng
Tất nhiên, không phải tất cả các tháng đều có 31 ngày, vì vậy những ngày thừa của tháng XNUMX, tháng XNUMX, tháng XNUMX, v.v. Sẽ rất tốt nếu bạn đánh dấu trực quan là không liên quan. Hàm số NGÀY, tạo thành lịch của chúng tôi, trong các ô như vậy sẽ tự động dịch ngày thành tháng tiếp theo, tức là ngày 30 tháng 2016 năm 1 sẽ trở thành ngày XNUMX tháng XNUMX. Nghĩa là, số tháng cho các ô bổ sung như vậy sẽ không bằng số tháng trong cột A . Điều này có thể được sử dụng khi tạo quy tắc định dạng có điều kiện để chọn các ô như vậy:
Thêm một ngày cuối tuần
Theo tùy chọn, bạn có thể thêm vào lịch và các ngày cuối tuần của chúng tôi. Để làm điều này, bạn có thể sử dụng hàm NGÀY (TUẦN), sẽ tính toán số ngày trong tuần (1-Thứ Hai, 2 Thứ Ba… 7-CN) cho mỗi ngày và đánh dấu những ngày rơi vào Thứ Bảy và Chủ Nhật:
Để hiển thị chính xác, đừng quên định cấu hình chính xác thứ tự chính xác của các quy tắc trong cửa sổ. Trang chủ - Định dạng có điều kiện - Quản lý quy tắc (Trang chủ - Định dạng có điều kiện - Quản lý quy tắc), bởi vì các quy tắc và điền hoạt động chính xác theo trình tự logic mà bạn sẽ hình thành trong hộp thoại này:
- Video hướng dẫn sử dụng định dạng có điều kiện trong Excel
- Cách tạo lịch trình dự án (Biểu đồ Gantt) bằng cách sử dụng định dạng có điều kiện
- Cách tạo dòng thời gian dự án trong Excel