Sử dụng công thức để định dạng có điều kiện trong Excel

Thêm định dạng có điều kiện trong Excel cho phép bạn áp dụng các tùy chọn định dạng khác nhau cho một ô hoặc phạm vi ô đáp ứng các điều kiện cụ thể mà bạn đã đặt. Các tùy chọn định dạng chỉ được áp dụng khi các ô được chọn đáp ứng các điều kiện đã đặt này.

Các tùy chọn định dạng có thể được áp dụng bao gồm thay đổi màu phông chữ và màu nền, kiểu phông chữ, đường viền ô và thêm định dạng số vào dữ liệu.

Excel đã có một số tùy chọn tích hợp cho các điều kiện thường được sử dụng như tìm số lớn hơn hoặc nhỏ hơn giá trị nhất định hoặc tìm số nằm trên hoặc dưới giá trị trung bình.

Ngoài các tùy chọn được đặt trước này, bạn cũng có thể tạo các quy tắc định dạng có điều kiện tùy chỉnh bằng cách sử dụng công thức Excel để kiểm tra các điều kiện do người dùng chỉ định.

01 trên 04

Áp dụng nhiều quy tắc trong Excel

Có thể áp dụng nhiều quy tắc cho cùng một dữ liệu để kiểm tra các điều kiện khác nhau. Ví dụ: dữ liệu ngân sách có thể có các điều kiện áp dụng các thay đổi định dạng khi các cấp nhất định, chẳng hạn như 50%, 75% và 100% của tổng ngân sách được chi tiêu.

Trong những trường hợp như vậy, Excel trước tiên sẽ xác định nếu các quy tắc khác nhau xung đột, và nếu có, chương trình sẽ tuân theo thứ tự ưu tiên được thiết lập để xác định quy tắc định dạng có điều kiện nào được áp dụng cho dữ liệu.

Tìm kiếm dữ liệu vượt quá 25% và tăng 50%

Trong ví dụ sau, hai quy tắc định dạng có điều kiện tùy chỉnh sẽ được áp dụng cho phạm vi ô B2 đến B5.

Quy tắc đầu tiên kiểm tra xem liệu dữ liệu trong ô A2 đến A5 lớn hơn giá trị tương ứng trong B2 đến B5 hơn 25%.
Quy tắc thứ hai kiểm tra xem liệu cùng một dữ liệu trong A2: A5 vượt quá giá trị tương ứng trong B2: B5 hơn 50%.

Như có thể thấy trong hình trên, nếu một trong các điều kiện trên là đúng, màu nền của ô hoặc ô trong phạm vi B1: B4 sẽ thay đổi.

Đối với dữ liệu có chênh lệch lớn hơn 25%, màu nền của ô sẽ chuyển sang màu xanh lục.
Nếu chênh lệch lớn hơn 50% thì màu nền của ô sẽ chuyển sang màu đỏ.

Các quy tắc được sử dụng để hoàn thành nhiệm vụ này sẽ được nhập bằng định dạng có điều kiện Quy tắc định dạng mới hộp thoại. Bắt đầu bằng cách nhập dữ liệu mẫu vào các ô từ A1 đến C5 như trong hình trên

bên trong bước thứ ba của hướng dẫn sẽ thêm công thức vào các ô C2: C4 cho thấy sự khác biệt phần trăm chính xác giữa các giá trị trong ô A2: A5 và B2: B5 để kiểm tra độ chính xác của các quy tắc định dạng có điều kiện.

02 trên 04

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

Như đã đề cập, các quy tắc định dạng có điều kiện kiểm tra hai điều kiện sẽ được nhập bằng định dạng có điều kiện Quy tắc định dạng mới hộp thoại. Hãy bắt đầu bằng cách đặt định dạng có điều kiện tùy chỉnh của chúng tôi:

Định dạng có điều kiện để tìm lớn hơn 25 Phần trăm tăng

= (A2-B2) / A2> 25%

Điểm nổi bật ô B2 đến B5 trong bảng tính.
Nhấp vào Tab trang chủ của ruy băng.
Nhấp vào Định dạng có điều kiện biểu tượng trong ruy băng để mở menu thả xuống.
Chọn Quy tắc mới để mở Quy tắc định dạng mới hộp thoại.
Từ Phong cách: hộp, chọn Cổ điển Tùy chọn.
Trong menu thả xuống thứ hai, nhấp vào tùy chọn cuối cùngSử dụng công thức để xác định ô cần định dạng.
Nhập công thức được cung cấp ở trên vào khoảng trống được cung cấp.
Nhấp vào Định dạng bằng: thả xuống rồi chọn Tô màu đỏ nhạt với văn bản màu đỏ đậm Tùy chọn.
Nhấp chuột được để đóng các hộp thoại và trở lại trang tính.
Tại thời điểm này, màu nền của ô B3 và B5 nên có màu xanh lá cây.

Định dạng có điều kiện để tìm thấy hơn 50 phần trăm tăng

= (A2-B2) / A2> 50%

Điểm nổi bật ô B2 đến B5 trong bảng tính.

Nhấp vào Tab trang chủ của ruy băng.
Nhấp vào Định dạng có điều kiện biểu tượng trong ruy băng để mở menu thả xuống.
Chọn Quy tắc mới để mở Quy tắc định dạng mới hộp thoại.
Từ Phong cách: hộp, chọn Cổ điển Tùy chọn.
Trong menu thả xuống thứ hai, nhấp vào tùy chọn cuối cùngSử dụng công thức để xác định ô cần định dạng.
Nhập công thức được cung cấp ở trên vào khoảng trống được cung cấp.
Nhấp vào Định dạng bằng: thả xuống rồi chọn Màu xanh lục nhạt lấp đầy với văn bản màu xanh lục đậm Tùy chọn.
Nhấp chuột được để đóng các hộp thoại và trở lại trang tính.

Màu nền của ô B3 vẫn nên có màu xanh lá cây cho biết sự khác biệt phần trăm giữa các con số trong tế bào A3 và B3 lớn hơn 25% nhưng nhỏ hơn hoặc bằng 50%. Màu nền của ô B5 nên thay đổi thành màu đỏ cho biết sự khác biệt phần trăm giữa các số trong tế bào A5 và B5 lớn hơn 50%.

03 trên 04

Kiểm tra quy tắc định dạng có điều kiện

Để kiểm tra xem các quy tắc định dạng có điều kiện đã nhập có chính xác không, chúng tôi có thể nhập công thức vào ô C2: C5 sẽ tính toán sự khác biệt phần trăm chính xác giữa các số trong phạm vi A2: A5 và B2: B5.

= (A2-B2) / A2

Bấm vào ô C2 để biến nó thành ô hiện hoạt.
Nhập công thức trên và nhấn Đi vào trên bàn phím.
Câu trả lời 10% sẽ xuất hiện trong ô C2, cho biết số trong ô A2 lớn hơn 10% so với số lượng ô B2.
Có thể cần thay đổi định dạng ô C2 để hiển thị câu trả lời dưới dạng phần trăm.
Sử dụng điền vào tay cầm để sao chép công thức từ ô C2 đến ô C3 đến C5.
Câu trả lời cho ô C3 đến C5 phải là 30%, 25% và 60%.

Câu trả lời trong các ô này cho thấy rằng các quy tắc định dạng có điều kiện được tạo là chính xác vì sự khác biệt giữa tế bào A3 và B3 lớn hơn 25% và sự khác biệt giữa tế bào A5 và B5 lớn hơn 50%.

Ô B4 không thay đổi màu sắc vì sự khác biệt giữa ô A4 và B 4 bằng 25 phần trăm và quy tắc định dạng có điều kiện của chúng tôi đã chỉ định rằng phần trăm lớn hơn 25 phần trăm được yêu cầu cho màu nền chuyển sang màu xanh lá cây.

04/04

Thứ tự ưu tiên cho định dạng có điều kiện

Khi nhiều quy tắc được áp dụng cho cùng một phạm vi dữ liệu, Excel trước tiên sẽ xác định xem các quy tắc có xung đột hay không. Các quy tắc xung đột là những quy tắc mà các tùy chọn định dạng được chọn cho mỗi quy tắc không thể áp dụng cho cùng một dữ liệu.

Trong ví dụ được sử dụng trong hướng dẫn này, các quy tắc xung đột vì cả hai quy tắc đều sử dụng cùng một tùy chọn định dạng - đó là thay đổi màu nền của ô.

Trong trường hợp quy tắc thứ hai là đúng (chênh lệch về giá trị lớn hơn 50% giữa hai ô) thì quy tắc đầu tiên (sự khác biệt về giá trị lớn hơn 25%) cũng đúng.

Thứ tự ưu tiên của Excel

Vì một ô không thể có cả nền màu đỏ và xanh lá cây cùng một lúc, Excel cần biết quy tắc định dạng có điều kiện nào nên áp dụng.

Quy tắc nào được áp dụng được xác định theo thứ tự ưu tiên của Excel, quy định rằng quy tắc cao hơn trong danh sách trong hộp thoại Trình quản lý quy tắc định dạng có điều kiện có ưu tiên.

Như được hiển thị trong hình trên, quy tắc thứ hai được sử dụng trong hướng dẫn này cao hơn trong danh sách và, do đó, được ưu tiên hơn quy tắc đầu tiên. Kết quả là, màu nền của ô B5 được đổi thành màu đỏ.

Theo mặc định, các quy tắc mới được thêm vào đầu danh sách và do đó, có mức độ ưu tiên cao hơn. Để thay đổi thứ tự ưu tiên, hãy sử dụng Lên và xuống các nút mũi tên trong hộp thoại như được xác định trong hình trên.

Áp dụng các quy tắc không xung đột

Nếu hai hoặc nhiều quy tắc định dạng có điều kiện không xung đột cả hai được áp dụng khi điều kiện mỗi quy tắc được kiểm tra trở thành sự thật.

Nếu quy tắc định dạng có điều kiện đầu tiên trong ví dụ của chúng tôi đã định dạng phạm vi ô B2: B5 với đường viền màu xanh lá cây thay vì màu nền xanh lục, hai quy tắc định dạng có điều kiện sẽ không xung đột vì cả hai định dạng đều có thể được áp dụng mà không can thiệp vào nhau.

Kết quả là, ô B5 sẽ có cả viền màu lục và màu nền đỏ, vì sự khác biệt giữa các số trong tế bào A5 và B5 lớn hơn cả 25 và 50 phần trăm.

Định dạng có điều kiện so với định dạng thông thường

Trong trường hợp xung đột giữa các quy tắc định dạng có điều kiện và tùy chọn định dạng được áp dụng theo cách thủ công, quy tắc định dạng có điều kiện luôn được ưu tiên và sẽ được áp dụng thay cho bất kỳ tùy chọn định dạng được thêm theo cách thủ công nào.

Nếu màu nền màu vàng ban đầu được áp dụng cho ô B2 đến B5 trong ví dụ, khi các quy tắc định dạng có điều kiện được thêm vào, chỉ ô B2 và B 4 sẽ ở lại màu vàng. Bởi vì các quy tắc định dạng có điều kiện được áp dụng cho các ô B3 và B5, màu nền của chúng sẽ thay đổi từ vàng sang xanh lá cây và đỏ tương ứng.