Công thức Excel: Lịch biểu thời gian biểu đồ Gantt
Công thức Excel: Lịch biểu thời gian biểu đồ Gantt

Công thức chung
=IF(start<end,AND(A$1>=start,time<=end),OR(A$1>=start,A$1<end))
Tóm lược

Để xây dựng biểu đồ Gantt nhằm hiển thị lịch trình thời gian, bạn có thể sử dụng Định dạng có điều kiện với công thức dựa trên các hàm AND và OR. Trong ví dụ được hiển thị, công thức áp dụng cho D5 là:

=IF($B5<$C5,AND(D$4>=$B5,D$4<=$C5),OR(D$4>=$B5,D$4<$C5))

Ghi chú tài liệu tham khảo là hỗn hợp để khóa các hàng và cột nếu cần nhằm kiểm tra chính xác từng ô trong lưới.

Giải trình

Lưu ý: đây là một ví dụ tuyệt vời về một công thức khó hiểu vì các tham chiếu ô khó hiểu. Ý chính của logic được sử dụng là: nếu thời gian trong hàng 4 nằm giữa thời gian bắt đầu và kết thúc, công thức sẽ trả về TRUE và kích hoạt điền màu xanh lam thông qua định dạng có điều kiện. Việc triển khai thực tế phức tạp hơn một chút, vì công thức bên dưới cũng tính đến khả năng thời gian bắt đầu và kết thúc vượt quá nửa đêm. Nếu điều này liên quan đến tình huống của bạn, bạn có thể chỉ sử dụng biểu thức AND được giải thích bên dưới.

Tiêu đề lịch (hàng 4) là một chuỗi thời gian Excel hợp lệ, được định dạng với định dạng số tùy chỉnh “hứ”. Điều này giúp bạn có thể thiết lập quy tắc định dạng có điều kiện so sánh thời gian được liên kết với mỗi cột trong hàng 4 với thời gian được nhập trong cột B và C.

Mỗi lần trong hàng 4 cần được kiểm tra để xem nó có nằm trong thời gian bắt đầu và kết thúc trong cột B và C, cho mỗi hàng dữ liệu trong lịch trình hay không. Logic được sử dụng để áp dụng định dạng có điều kiện phụ thuộc vào thời gian bắt đầu và kết thúc. Khi thời gian bắt đầu nhỏ hơn thời gian kết thúc (trường hợp bình thường), hàm AND được sử dụng để kích hoạt định dạng có điều kiện. Khi thời gian bắt đầu lớn hơn thời gian kết thúc (thời gian qua nửa đêm), hàm OR được sử dụng để thay thế.

Để xử lý sự khác biệt này ở mức cao, trước tiên, hàm IF được sử dụng để kiểm tra từng cặp lần:

=IF($B5<$C5

Khi thời gian bắt đầu sớm hơn thời gian kết thúc, kiểm tra ở trên trả về TRUE và IF trả về phần AND của công thức:

AND(D$4>=$B5,D$4<=$C5)

Các Hàm AND được cấu hình với hai điều kiện. Các điều kiện đầu tiên kiểm tra xem thời gian của cột lớn hơn hoặc bằng thời gian bắt đầu:

D$4>=$B5

Điều kiện thứ hai kiểm tra xem thời gian của cột nhỏ hơn hoặc bằng thời gian kết thúc:

D$4<=$C5

Khi cả hai điều kiện trả về TRUE, công thức trả về TRUE và kích hoạt tô màu xanh lam cho các ô trong lưới lịch.

Khi thời gian bắt đầu là lớn hơn so với thời gian kết thúc (thời gian qua nửa đêm), IF trả về một biểu thức được xây dựng bằng OR:

OR(D$4>=$B5,D$4<$C5)

Đây, Hàm HOẶC được cấu hình với hai điều kiện. Điều kiện đầu tiên giống như điều kiện được sử dụng trong AND ở trên – nó kiểm tra xem thời gian của cột lớn hơn hoặc bằng thời gian bắt đầu hay không:

D$4>=$B5

Điều kiện thứ hai được thay đổi nhanh chóng để kiểm tra xem thời gian của cột có nhỏ hơn thời gian kết thúc hay không:

D$4<$C5

Khi nào hoặc điều kiện trả về TRUE, HOẶC trả về TRUE và kích hoạt định dạng có điều kiện.

Lưu ý: cả hai điều kiện sử dụng tài liệu tham khảo hỗn hợp để đảm bảo rằng các tham chiếu cập nhật chính xác khi áp dụng định dạng có điều kiện cho lưới.

https://exceljet.net/formula/gantt-chart-time-schedule