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. Đặt các điều kiện như vậy có thể giúp tổ chức bảng tính của bạn và giúp quét dễ dàng hơn. Các tùy chọn định dạng bạn có thể sử dụng bao gồm thay đổi 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ó các tùy chọn cài sẵn 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 một giá trị cụ thể hoặc tìm số cao hơn hoặc thấp hơn giá trị trung bình. Ngoài các tùy chọn đặ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ông thức Excel.
Những hướng dẫn này áp dụng cho Excel 2019, 2016, 2013, 2010 và Excel cho Microsoft 365.
Áp dụng Nhiều Điều kiện trong Excel
Bạn 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 được đặt áp dụng các thay đổi định dạng khi đạt đến các mức chi tiêu nhất định, chẳng hạn như 50%, 75% và 100%, trong tổng ngân sách.
Trong những trường hợp như vậy, trước tiên, Excel xác định xem các quy tắc khác nhau có xung đột hay không, và nếu có, chương trình sẽ tuân theo một thứ tự ưu tiên đã đặt để xác định quy tắc định dạng có điều kiện nào sẽ áp dụng cho dữ liệu.
Tìm Dữ liệu Vượt quá 25% và 50% Tăng
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 ô A2: A5có lớn hơn giá trị tương ứng trong B2: B5hay không hơn 25%.
- Quy tắc thứ hai kiểm tra xem dữ liệu tương tự trong A2: A5có vượt quá giá trị tương ứng trong B2: B5hay không 50%.
Như có thể thấy trong hình trên, nếu một trong hai điều kiện trên là đúng, màu nền của ô hoặc các ô trong phạm vi B1: B4sẽ thay đổi.
- Đối với dữ liệu có sự khác biệt lớn hơn 25%, màu nền của ô sẽ chuyển thành màu xanh lục.
- Nếu sự khác biệt lớn hơn 50%, màu nền của ô sẽ chuyển thành đỏ.
Các quy tắc được sử dụng để hoàn thành tác vụ này sẽ được nhập bằng hộp thoại Quy tắc Định dạng Mới. Bắt đầu bằng cách nhập dữ liệu mẫu vào các ô A1 đến C5như trong hình trên.
Trong phần cuối cùng của hướng dẫn, chúng tôi sẽ thêm công thức vào các ô C2: C4hiển thị phần trăm chênh lệch chính xác giữa các giá trị trong các ô A2: A5và B2: B5; điều này sẽ cho phép chúng tôi 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 Có Điều kiện
Đầu tiên, chúng tôi sẽ áp dụng định dạng có điều kiện để tìm mức tăng đáng kể từ 25 phần trăm trở lên.
Hàm sẽ như thế này:
=(A2-B2) / A2>25%
- Đánh dấu ô B2thành B5trong trang tính.
- Nhấp vào tab Trang chủcủa dải băng.
- Nhấp vào biểu tượng Định dạng có điều kiệntrong dải băng để mở menu thả xuống.
- Chọn New Rule để mở hộp thoại New Formatting Rule.
-
Dưới Chọn Loại Quy tắc, 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.
- Nhập công thức đã ghi ở trên vào khoảng trống bên dưới Định dạng các giá trị mà công thức này là đúng:
- Nhấp vào nút Định dạng để mở hộp thoại. Nhấp vào tab Fillvà chọn màu.
- Nhấp vào OKđể đóng hộp thoại và quay lại trang tính.
- Màu nền của ô B3và B5sẽ thay đổi thành màu bạn đã chọn.
Bây giờ, chúng tôi sẽ áp dụng định dạng có điều kiện để tìm mức tăng 50 phần trăm trở lên. Công thức sẽ như thế này:
- Lặp lại năm bước đầu tiên ở trên.
- Nhập công thức được cung cấp ở trên vào khoảng trống bên dưới Định dạng các giá trị mà công thức này là đúng:
- Nhấp vào nút Định dạng để mở hộp thoại. Nhấp vào tab Tô màuvà chọn một màu khác với màu bạn đã làm trong các bước trước đó.
- Nhấp vào OKđể đóng hộp thoại và quay lại trang tính.
Màu nền của ô B3phải giữ nguyên cho biết rằng phần trăm chênh lệch giữa các số trong ô A3và B3 lớn hơn 25 phần trăm nhưng nhỏ hơn hoặc bằng 50 phần trăm. Màu nền của ô B5 sẽ thay đổi thành màu mới mà bạn đã chọn cho biết phần trăm chênh lệch giữa các số trong ô A5 vàB5 lớn hơn 50 phần trăm.
Kiểm tra Quy tắc Định dạng có Điều kiện
Để xác minh rằng các quy tắc định dạng có điều kiện đã nhập là chính xác, chúng tôi có thể nhập công thức vào các ô C2: C5sẽ tính toán chênh lệch phần trăm chính xác giữa các số trong phạm vi A2: A5 vàB2: B5.
Công thức trong ô C2 trông giống như sau:
=(A2-B2) / A2
- Nhấp vào ô C2để biến nó thành ô hoạt động.
- Nhập công thức trên và nhấn phím Nhậptrên bàn phím.
- Câu trả lời 10% sẽ xuất hiện trong ô C2, cho biết rằng số trong ô A2lớn hơn 10% so với số trong ô B2.
- Có thể cần 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 điều khiển điền để sao chép công thức từ ô C2sang ô C3sang C5.
- Câu trả lời cho ô C3 đến C5phải là 30%, 25% và 60%.
Câu trả lời trong các ô này cho thấy các quy tắc định dạng có điều kiện là chính xác vì sự khác biệt giữa ô A3và B3lớn hơn 25 phần trăm và sự khác biệt giữa ô A5và B5lớn hơn 50 phần trăm.
Ô B4không đổi màu vì sự khác biệt giữa ô A4và B4bằ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 là bắt buộc để màu nền thay đổi.
Thứ tự ưu tiên cho Định dạng có Điều kiện
Khi bạn áp dụng nhiều quy tắc cho cùng một dải dữ liệu, trước tiên, Excel 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ả hai tùy chọn định dạng không thể được áp dụng cho cùng một dữ liệu.
Trong ví dụ của chúng tôi, các quy tắc xung đột vì cả hai đều sử dụng cùng một tùy chọn định dạng - thay đổi màu ô nền.
Trong trường hợp quy tắc thứ hai đúng (chênh lệch giá trị hơn 50 phần trăm giữa hai ô) thì quy tắc đầu tiên (chênh lệch giá trị lớn hơn 25 phần trăm) cũng đúng.
Vì một ô không thể có cả hai nền màu khác nhau cùng một lúc, nên Excel cần biết nó sẽ áp dụng quy tắc định dạng có điều kiện nào.
Thứ tự ưu tiên của Excel nói 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 sẽ được áp dụng đầ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 cao hơn trong danh sách và do đó, được ưu tiên hơn quy tắc đầu tiên. Do đó, màu nền của ô B5là xanh lục.
Theo mặc định, các quy tắc mới được đưa lên đầu danh sách; để thay đổi thứ tự, sử dụng các nút mũi tên Lên và Xuốngtrong hộp thoại.
Áp dụng 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, thì cả hai đều được áp dụng khi điều kiện mà mỗi quy tắc đang kiểm tra trở thành đúng.
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: B5với đường viền màu cam thay vì màu nền cam, thì 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 ảnh hưởng đến định dạng khác.
Đị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à các 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 vì bất kỳ tùy chọn định dạng nào được thêm theo cách thủ công.
