Thêm định dạng có điều kiện vào một ô trong Excel cho phép bạn áp dụng các tùy chọn định dạng khác nhau, chẳng hạn như màu, khi dữ liệu trong ô đó đáp ứng các điều kiện mà bạn đã đặt.
Để làm cho việc sử dụng định dạng có điều kiện trở nên dễ dàng hơn, có sẵn các tùy chọn có sẵn bao gồm các tình huống thường được sử dụng, chẳng hạn như:
Trong trường hợp ngày tháng, các tùy chọn được đặt trước giúp bạn dễ dàng kiểm tra dữ liệu của mình cho các ngày gần ngày hiện tại, chẳng hạn như ngày hôm qua, ngày mai, tuần trước hoặc tháng tiếp theo.
Tuy nhiên, nếu bạn muốn kiểm tra các ngày nằm ngoài các tùy chọn được liệt kê, bạn có thể tùy chỉnh định dạng có điều kiện bằng cách thêm công thức của riêng bạn bằng một hoặc nhiều hàm ngày tháng của Excel.
01 trên 06
Kiểm tra các ngày 30, 60 và 90 ngày quá hạn
Việc tùy chỉnh định dạng có điều kiện bằng cách sử dụng các công thức được thực hiện bằng cách thiết lập một quy tắc mới mà Excel tuân theo khi đánh giá dữ liệu trong một ô.
Ví dụ từng bước ở đây đặt ba quy tắc định dạng có điều kiện mới sẽ kiểm tra xem các ngày được nhập vào một phạm vi ô đã chọn có quá 30 ngày qua, 60 ngày qua hay 90 ngày qua.
Các công thức được sử dụng trong các quy tắc này trừ đi một số ngày nhất định từ ngày hiện tại trong các ô C1 đến C4.
Ngày hiện tại được tính bằng hàm TODAY .
Để hướng dẫn này hoạt động, bạn phải nhập ngày nằm trong các tham số được liệt kê ở trên.
Lưu ý : Excel áp dụng định dạng có điều kiện theo thứ tự, từ trên xuống dưới, rằng các quy tắc được liệt kê trong hộp thoại Trình quản lý quy tắc định dạng có điều kiện như được thấy trong hình trên.
Mặc dù nhiều quy tắc có thể áp dụng cho một số ô, quy tắc đầu tiên đáp ứng điều kiện được áp dụng cho các ô.
02/06
Kiểm tra thời gian 30 ngày quá hạn
- Đánh dấu các ô C1 đến C4 để chọn chúng. Đây là phạm vi mà chúng tôi sẽ áp dụng các quy tắc định dạng có điều kiện
- Nhấp vào tab Trang chủ của trình đơn ribbon .
- Nhấp vào biểu tượng Định dạng có điều kiện để mở menu thả xuống.
- Chọn tùy chọn Quy tắc mới . Thao tác này sẽ mở hộp thoại Quy tắc định dạng mới.
- Nhấp vào tùy chọn Sử dụng công thức để xác định ô nào cần định dạng .
- Nhập công thức sau vào hộp bên dưới các giá trị Định dạng nơi giá trị này là tùy chọn đúng trong nửa dưới của hộp thoại:
= TODAY () - C1> 30
Công thức này kiểm tra xem các ngày trong các ô từ C1 đến C4 có quá 30 ngày - Nhấp vào nút Định dạng để mở hộp thoại Định dạng Ô.
- Nhấp vào tab Điền để xem các tùy chọn tô màu nền.
- Chọn màu tô nền - để phù hợp với ví dụ trong hướng dẫn này, chọn màu xanh lục nhạt.
- Nhấp vào tab Phông chữ để xem các tùy chọn định dạng phông chữ
- Trong phần màu, đặt màu phông chữ thành màu trắng để khớp với hướng dẫn này.
- Bấm OK hai lần để đóng hộp thoại và quay lại trang tính.
- Màu nền của ô C1 đến C4 sẽ thay đổi thành màu tô được chọn, mặc dù không có dữ liệu trong ô.
03/06
Thêm quy tắc cho ngày hơn 60 ngày quá hạn
Sử dụng tùy chọn Quản lý quy tắc
Thay vì lặp lại tất cả các bước trên để thêm hai quy tắc tiếp theo, chúng tôi sẽ sử dụng tùy chọn Quản lý quy tắc cho phép chúng tôi thêm các quy tắc bổ sung cùng một lúc.
- Đánh dấu các ô C1 đến C4, nếu cần.
- Nhấp vào tab Trang chủ của trình đơn ribbon.
- Nhấp vào biểu tượng Định dạng có điều kiện để mở menu thả xuống.
- Chọn tùy chọn Quản lý quy tắc để mở hộp thoại Trình quản lý quy tắc định dạng có điều kiện.
- Nhấp vào tùy chọn Quy tắc mới ở góc trên cùng bên trái của hộp thoại
- Bấm vào các Sử dụng một công thức để xác định ô nào để định dạng tùy chọn từ danh sách ở đầu hộp thoại.
- Nhập công thức sau vào hộp bên dưới các giá trị Định dạng nơi giá trị này là tùy chọn đúng trong nửa dưới của hộp thoại :
= TODAY () - C1> 60Công thức này kiểm tra xem các ngày trong các ô từ C1 đến C4 có lớn hơn 60 ngày trước không.
- Nhấp vào nút Định dạng để mở hộp thoại Định dạng Ô.
- Nhấp vào tab Điền để xem các tùy chọn tô màu nền.
- Chọn màu tô nền; để phù hợp với ví dụ trong hướng dẫn này, chọn màu vàng.
- Nhấn OK hai lần để đóng hộp thoại và quay lại hộp thoại Trình quản lý quy tắc định dạng có điều kiện.
04/06
Thêm quy tắc cho các ngày quá 90 ngày quá hạn
- Lặp lại các bước từ 5 đến 7 ở trên để thêm quy tắc mới.
- Để sử dụng công thức:
= TODAY () - C1> 90 - Chọn màu tô nền; để so khớp ví dụ trong hướng dẫn này, hãy chọn màu cam.
- Đặt màu phông chữ thành trắng để khớp với hướng dẫn này.
- Nhấn OK hai lần để đóng hộp thoại và quay trở lại hộp thoại Trình quản lý quy tắc định dạng có điều kiện
- Nhấn OK lần nữa để đóng hộp thoại này và quay trở lại trang tính .
- Màu nền của ô C1 đến C4 sẽ thay đổi thành màu tô màu cuối cùng được chọn.
05/06
Kiểm tra các quy tắc định dạng có điều kiện
Như có thể thấy trong hình ảnh hướng dẫn, chúng ta có thể kiểm tra các quy tắc định dạng có điều kiện trong các ô từ C1 đến C4 bằng cách nhập các ngày sau:
- Ngày hiện tại — ô sẽ thay đổi thành nền trắng mặc định bằng văn bản màu đen vì không có quy tắc định dạng có điều kiện nào áp dụng.
- 40 ngày trước ngày hiện tại — ô sẽ chuyển sang nền màu xanh nhạt với chữ màu trắng.
(Lưu ý: Nếu bạn gặp khó khăn khi tính toán một ngày như vậy, như đã thấy trong hình trên, công thức = TODAY () - 40 sẽ nhập ngày 40 ngày trước ngày hiện tại.) - 70 ngày trước ngày hiện tại — ô sẽ chuyển sang nền màu vàng có chữ màu trắng.
- 100 ngày trước ngày hiện tại — ô sẽ chuyển sang nền đỏ sậm có chữ màu trắng.
06 trên 06
Quy tắc định dạng có điều kiện thay thế
Nếu trang tính của bạn đã hiển thị ngày hiện tại — và hầu hết các trang tính — một công thức thay thế cho những công thức trên có thể sử dụng tham chiếu ô tới ô nơi ngày hiện tại được hiển thị thay vì sử dụng hàm TODAY.
Ví dụ: nếu ngày được hiển thị trong ô B4, công thức được nhập dưới dạng quy tắc để định dạng theo điều kiện các ngày quá hạn 30 ngày có thể là:
= $ B $ 4> 30
Dấu đô la ($) xung quanh tham chiếu ô B4 ngăn tham chiếu ô thay đổi nếu quy tắc định dạng có điều kiện được sao chép sang các ô khác trong trang tính.
Dấu hiệu đồng đô la tạo ra cái được gọi là tham chiếu ô tuyệt đối .
Nếu dấu hiệu đồng đô la bị bỏ qua và quy tắc định dạng có điều kiện được sao chép, ô đích hoặc ô sẽ rất có thể hiển thị #REF! thông báo lỗi.