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.
Kể từ Excel 2007, 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.
Áp dụng nhiều quy tắc
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% - trong 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.
Ví dụ: Tìm kiếm dữ liệu vượt quá 25% và 50% tăng với định dạng có điều kiện
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 ô từ B2 đến B5.
- quy tắc đầu tiên kiểm tra xem dữ liệu trong các ô từ A2 đến A5 có lớn hơn giá trị tương ứng trong B2 đến B5 không quá 25%.
- quy tắc thứ hai kiểm tra xem liệu cùng một dữ liệu trong A2: A5 có vượt quá giá trị tương ứng trong B2: B5 hơn 50% hay không.
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 lam.
- 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,
= (A2-B2) / A2> 25% = (A2-B2) / A2> 50%sẽ được nhập bằng cách sử dụng định dạng có điều kiện Hộp thoại Quy tắc Định dạng Mới .
Nhập dữ liệu hướng dẫn
- Nhập dữ liệu vào ô A1 đến C5 như trong hình trên
Lưu ý: Bước 3 của hướng dẫn sẽ thêm công thức vào ô C2: C4 thể hiện sự khác biệt phần trăm chính xác giữa các giá trị trong các ô A2: A5 và B2: B5 để kiểm tra tính chính xác của các quy tắc định dạng có điều kiện.
Đặt quy tắc định dạng condtional
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 cách sử dụng định dạng có điều kiện Hộp thoại Quy tắc định dạng mới.
Đặt định dạng có điều kiện để tìm mức tăng lớn hơn 25%
- Đánh dấu các ô từ B2 đến B5 trong trang tính.
- Nhấp vào tab Trang chủ của ruy-băng.
- Nhấp vào biểu tượng Định dạng có điều kiện trong ruy-băng để mở menu thả xuống.
- Chọn Quy tắc mới để mở hộp thoại Quy tắc định dạng mới như được thấy trong hình trên.
- Trong nửa trên của hộp thoại, hãy nhấp vào tùy chọn cuối cùng: Sử dụng công thức để xác định ô cần định dạng.
- Trong nửa dưới của hộp thoại, nhấp vào các giá trị Định dạng trong đó công thức này là đúng: dòng.
- Nhập công thức : = (A2-B2) / A2> 25% vào khoảng trống được cung cấp
- Nhấn vào nút Format để mở hộp thoại Format Cells.
- Trong hộp thoại này, nhấp vào tab Fill và chọn màu tô màu xanh.
- Bấm OK hai lần để đóng các hộp thoại và trở về trang tính.
- Tại thời điểm này, màu nền của các ô B3 và B5 phải có màu xanh dương.
Đặt định dạng có điều kiện để tìm mức tăng lớn hơn 50%
- Với các ô B2 đến B5 vẫn được chọn, lặp lại các bước từ 1 đến 6 ở trên.
- Nhập công thức: = (A2-B2) / A2> 50% trong khoảng trống được cung cấp.
- Nhấn vào nút Format để mở hộp thoại Format Cells.
- Nhấp vào tab Fill và chọn màu tô đỏ.
- Bấm OK hai lần để đóng các hộp thoại và trở về trang tính .
- Màu nền của ô B3 vẫn có màu xanh cho biết sự khác biệt phần trăm giữa các số trong ô 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 sẽ chuyển thành màu đỏ cho biết sự khác biệt phần trăm giữa các số trong ô A5 và B5 lớn hơn 50%.
Kiểm tra quy tắc định dạng có điều kiện
Tính% chênh lệch
Để kiểm tra rằng các quy tắc định dạng có điều kiện được nhập là chính xác, chúng ta có thể nhập công thức vào các ô 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 khoảng A2: A5 và B2: B5.
- Bấm vào ô C2 để biến nó thành ô hiện hoạt.
- Nhập công thức = (A2-B2) / A2 và nhấn phím Enter 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ố trong ô B2.
- Có thể cần phải thay đổi định dạng trên ô C2 để hiển thị câu trả lời dưới dạng phần trăm.
- Sử dụng công cụ fill handle để sao chép công thức từ ô C2 sang ô C3 đến C5.
- Câu trả lời cho các ô C3 đến C5 phải là: 30%, 25% và 60%.
- Các 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 các ô A3 và B3 lớn hơn 25% và sự khác biệt giữa các ô A5 và B5 lớn hơn 50%.
- Ô B4 không thay đổi màu vì chênh lệch giữa các ô A4 và B4 bằng 25% 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% được yêu cầu để màu nền chuyển sang màu xanh lam.
Thứ tự ưu tiên cho các quy tắc định dạng có điều kiện
Áp dụng các quy tắc định dạng có điều kiện xung đột
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 (sự khác biệt 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à màu xanh 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ư thể hiện trong hình trên, quy tắc thứ hai được sử dụng trong hướng dẫn này (= (A2-B2) / A2> 50%) 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, sử dụng các nút mũi tên Lên và Xuống 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 ta (= (A2-B2) / A2> 25%) đã định dạng phạm vi ô B2: B5 với viền màu xanh thay vì màu nền xanh, hai quy tắc định dạng có điều kiện sẽ không xung đột cả hai định dạng có thể được áp dụng mà không can thiệp vào các định dạng khác.
Kết quả là, ô B5 sẽ có cả viền màu xanh và màu nền đỏ, vì sự khác biệt giữa các số trong ô 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ột màu nền màu vàng ban đầu được áp dụng cho các ô 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ỉ các ô B2 và B4 sẽ giữ màu vàng.
Bởi vì các quy tắc định dạng có điều kiện được nhập á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 dương và đỏ tương ứng.