Highlight các hàng giữa 2 ngày với định dạng có điều kiện trong Excel

2/7/2020

Ý chính: Tìm hiểu để áp dụng định dạng có điều kiện cho toàn bộ hàng dựa vào 2 ngày trong Excel. Highlight các mục trong và ngoài phạm vi ngày.

Trình độ kỹ năng: Nâng cao.

Highlight các mục dựa theo ngày

Xây dựng trên concept các hàng định dạng dựa trên dữ liệu ô, bài viết hôm nay sẽ mô tả cách highlight hàng dựa vào 2 ngày. Bạn có thể định dạng hàng có 1 mục ở giữa hoặc bên ngoài bất kỳ 2 ngày được đưa ra và chúng thể hiện trên table của bạn. Dưới đây là ví dụ một sheet mà định dạng hàng chứa 1 ngày bất kỳ giữa ngày 1/1/2018 và ngày 31/12/2018.

Định dạng ở cả tự động. Điều này có nghĩa là nếu bạn thay đổi Ngày bắt đầu hoặc Ngày kết thúc, table sẽ lập tức cập nhật để thể hiện sự thay đổi.

Giữa hai ngày

Để highlight một hàng có một ô giữa 2 ngày, chúng ta sẽ sử dụng hàm AND.

Hàm AND có đối số cho 2 hoặc nhiều logical tests. Mỗi logical test phải trả về một giá trị True hoặc False. Điều này thường được thực hiện bằng các comparison operators (=, <, >, <>).

Đối số đầu tiên của chúng ta cho thấy ngày trong ô mà chúng ta chọn phải lớn hơn hoặc bằng (>=) ngày bắt đầu. Trong ví dụ chúng ta có C7>=$C$3. (Ký tự đôla biểu thị rằng đây là giá trị tuyệt đối không thay đổi khi chúng ta thay đổi trong worksheet.)

Đối số thứ 2 phải là ngày nhỏ hơn hoặc bằng (<=) ngày kết thúc.

Đặt cùng với nhau, chúng ta có công thức cho ví dụ: =AND (C7>=$C$3, C7<=$C$4)

Áp dụng Định dạng có điều kiện

Bây giờ chúng ta sẽ xem làm cách nào để viết công thức, chúng ta sẽ áp dụng định dạng có điều kiện. Dưới đây là các bước thực hiện.

1. Chọn ô trong mục đẩu tiên trong cột đầu tiên. Trong worksheet ví dụ là cột C.
2. Trong Home tab của Ribbon, chọn drop-down Định dạng có điều kiện và chọn Manage Rules…. Cửa sồ Conditional Formatting Rules Manager sẽ xuất hiện.
3. Chọn New Rule. Cửa sổ New Formatting Rule xuất hiện.
4. Phía dưới Select a Rule Type, chọn Use a formula to determine which cells to format.
5. Phía dưới Format values where this formula is true, bạn viết công thức mà chúng ta đã tạo trong phần trên: =AND ($C7>=$C$3, $C7<=$C$4)
6. Khi điền các giá trị trong lúc viết công thức, Excel có thể tự động đặt các chỉ số tham chiếu tuyệt đối trước C7, nó sẽ trở thành $C$7. Để chắc chắn định dạng có điều kiện được áp dụng cho tất cả các hàng trong bảng, chúng ta cần phải thay đổi chỉ số tham chiếu tuyệt đối. Nói cách khác, chúng ta phải xóa ký hiệu đôla trước hàng chứa số (7) trong công thức. Bạn cũng có thể tự tay xía nó, hoặc bạn có thể nhấn phím F4 hai lần để hoàn thành bước này.

Điều này có nghĩa công thức sẽ luôn sử dụng cột C để so sánh, nhưng hàng chứa số sẽ thay đổi khi công thức được tính trong mỗi hàng của table.

7. Click nút Format… để chọn bất kỳ lựa chọn định dạng nào bạn thích. Bạn có thể thay đổi font chữ, fill và border, …

Bằng cách nhấn OK, bạn sẽ quay trở về Conditional Formatting Rules Manager. Thay đổi duy nhất chúng tôi muốn thực hiện ở đây là bao gồm toàn bộ table, không chỉ bắt đầu từ ô.

Để thực hiện điều đó, click vào icon ở bên phải của trường Applies to (nó có một mũi tên hướng lên) và chọn vùng của table. Trong ví dụ, đó có thể là dữ liệu từ B7 đến G1003 (=$B$7:$G$1003).

Ngoài 2 ngày

Chúng ta đã biết cách nhận dạng số rơi vào giữa hai ngày, vậy làm cách nào khi chúng ta muốn highlight số rơi ngoài 2 ngày? Chúng ta có thể sử dụng hàm OR để chú ý vào ngày rơi vào bên ngoài phạm vi ngày (trước ngày bắt đầu hoặc sau ngày kết thúc).

Hàm OR kiểm tra nếu có bất ký đối số nào là đúng, và nếu một trong số chúng đúng, kết quả sẽ trả về TRUE. Không cần tất cả đối số đều đúng. Chỉ khi tất cả đối số sai thì hàm sẽ trả về FALSE.

Tương tự với cách viết công thức hàm AND, hàm OR thay vì tìm bất kỳ số nhỏ hơn hoặc bằng (<=) ngày bắt đầu OR lớn hơn hoặc bằng (>=) ngày kết thúc.

Vì vậy trong ví dụ của chúng ta, sử dụng =OR($C7<=$C$3, $C7>=$C$4)

Một khi quy tắc được áp dụng, bất kỳ mục nào rơi bên ngoài phạm vi ngày cụ thể sẽ được định dạng để nổi bật.

Nguồn: Excel Campus