Thời gian đọc ước tính: 8 phút
Những điểm chính
- Cơ chế hoạt động: Hiểu cách Excel sử dụng công thức logic (True/False) để áp dụng định dạng.
- Địa chỉ tuyệt đối và tương đối: Nắm vững cách dùng ký hiệu
$để khóa cột hoặc hàng khi tô màu dữ liệu. - Tô màu cả hàng: Kỹ thuật mở rộng vùng chọn và dùng công thức cố định cột để làm nổi bật toàn bộ bản ghi.
- Hàm logic: Kết hợp hàm
ANDvàORđể tạo các điều kiện định dạng phức tạp. - Xử lý ô trống: Cách tự động phát hiện và tô màu các dòng dữ liệu bị thiếu thông tin.
Định dạng có điều kiện (Conditional Formatting) trong Excel là một công cụ mạnh mẽ giúp bạn trực quan hóa dữ liệu. Thông thường, chúng ta hay dùng tính năng này để định dạng ô dựa trên chính giá trị của ô đó (ví dụ: tô đỏ các ô có giá trị dưới 50).
Tuy nhiên, thực tế làm việc thường đòi hỏi các yêu cầu phức tạp hơn. Bạn có thể cần tô màu một ô (hoặc cả một hàng) dựa trên giá trị nằm ở một ô khác hoàn toàn.
Bài viết này sẽ hướng dẫn chi tiết cách sử dụng công thức trong Conditional Formatting để giải quyết vấn đề này. Chúng ta sẽ đi từ các ví dụ cơ bản đến nâng cao để bạn có thể áp dụng ngay vào báo cáo của mình.
Bạn có thể tải xuống tệp ví dụ để thực hành song song với bài hướng dẫn này: Tải xuống tệp thực hành Excel.
Định dạng ô dựa trên giá trị văn bản của ô khác
Hãy bắt đầu với một ví dụ đơn giản và phổ biến nhất: Tô màu tên nhân viên nếu họ thuộc một khu vực cụ thể.
Giả sử bạn có một bảng dữ liệu gồm Tên (Cột A), Khu vực (Cột B) và Doanh số (Cột C). Mục tiêu của bạn là tô sáng cột Tên (Cột A), nhưng điều kiện lại phụ thuộc vào việc cột Khu vực (Cột B) có giá trị là “US” hay không.
Đây là trường hợp điển hình của việc định dạng một ô dựa trên giá trị của ô liền kề.
Để thực hiện, bạn hãy làm theo các bước sau:
- Chọn vùng dữ liệu: Quét chọn vùng từ A2:A15. Vì bạn chỉ muốn tô màu tên, hãy chỉ chọn cột chứa tên (bỏ qua tiêu đề).
Chọn phạm vi chứa tên mà bạn muốn tô sáng
- Trên thanh công cụ (Ribbon), chọn thẻ Home.
- Nhấp vào biểu tượng Conditional Formatting.
Nhấp vào biểu tượng định dạng có điều kiện
- Chọn New Rule (Quy tắc mới) từ menu thả xuống. Hộp thoại New Formatting Rule sẽ xuất hiện.
Nhấp vào tùy chọn quy tắc mới
- Trong danh sách các kiểu quy tắc, chọn ‘Use a formula to determine which cells to format’ (Sử dụng công thức để xác định ô cần định dạng).
Chọn tùy chọn sử dụng công thức để xác định ô cần định dạng
- Nhập công thức sau vào ô “Format values where this formula is true”:
=$B2="US"
Nhập công thức vào trường công thức trong định dạng có điều kiện
- Nhấn nút Format.
- Chuyển sang thẻ Fill, chọn màu nền bạn muốn dùng để tô sáng (ví dụ màu vàng hoặc xanh).
- Nhấn OK để đóng hộp thoại Format Cells.
- Nhấn OK một lần nữa để áp dụng quy tắc.
Kết quả nhận được là các ô tên nhân viên tại cột A sẽ được tô màu nếu cột B tương ứng có giá trị là “US”.
Bộ dữ liệu nơi tên đã được tô sáng dựa trên giá trị trong cột khu vực
Trong ví dụ này, chúng ta đã gán cứng chữ “US” vào công thức. Tuy nhiên, bạn hoàn toàn có thể trỏ công thức đến một ô chứa giá trị điều kiện (ví dụ: ô E1 chứa chữ “US”) để linh hoạt thay đổi điều kiện mà không cần sửa công thức.
Vai trò của tham chiếu ô trong công thức
Để thành thạo Conditional Formatting, bạn cần hiểu rõ về tham chiếu ô (Cell References). Đây là yếu tố quyết định công thức của bạn có hoạt động đúng trên toàn bộ vùng dữ liệu hay không.
Trong ví dụ trên, công thức được sử dụng là:=$B2="US"
Ở đây, chúng ta sử dụng $B2. Đây là một dạng tham chiếu hỗn hợp (mixed reference). Dấu $ đặt trước chữ B có ý nghĩa khóa cột B lại.
Khi Excel áp dụng quy tắc này cho ô A2, nó kiểm tra ô B2. Khi Excel di chuyển xuống ô A3 để định dạng, công thức sẽ tự động điều chỉnh thành =$B3="US" (do số dòng không bị khóa). Tuy nhiên, dù bạn có áp dụng định dạng này sang các cột khác bên phải, nó vẫn sẽ luôn nhìn vào cột B vì đã có dấu $ khóa cột.
Nguyên tắc này cực kỳ quan trọng cho phần tiếp theo: Tô màu cả hàng dữ liệu.
Tô màu toàn bộ hàng dựa trên giá trị một ô
Ở ví dụ trước, chúng ta chỉ tô màu cột Tên. Nhưng trong các báo cáo quản trị, việc làm nổi bật toàn bộ dòng dữ liệu (cả Tên, Khu vực và Doanh số) thường giúp người đọc dễ theo dõi hơn.
Cách thực hiện tương tự như trên, chỉ khác biệt ở vùng chọn dữ liệu (Selection Range).
Các bước thực hiện như sau:
- Chọn toàn bộ vùng dữ liệu cần định dạng (từ A2:C15).
- Vào thẻ Home > Conditional Formatting > New Rule.
Nhấp vào biểu tượng định dạng có điều kiện
- Chọn ‘Use a formula to determine which cells to format’.
- Nhập công thức giống hệt ví dụ trước:
=$B2="US"
Nhập công thức và định dạng có điều kiện để tô sáng toàn bộ hàng dựa trên ô khác
- Cài đặt định dạng màu sắc tại nút Format và nhấn OK.
Lúc này, Excel sẽ kiểm tra điều kiện cho từng hàng. Vì chúng ta đã khóa cột B ($B2), nên đối với bất kỳ ô nào trong cùng một hàng (dù là ô A2, B2 hay C2), công thức đều kiểm tra giá trị tại cột B của hàng đó.
Toàn bộ hàng được tô sáng với định dạng có điều kiện dựa trên giá trị của ô khác
Nếu ô tại cột B là “US”, công thức trả về kết quả TRUE cho cả hàng 2, và toàn bộ hàng sẽ được tô màu.
Định dạng dựa trên giá trị số (Numeric Value)
Ngoài so sánh văn bản, bạn thường xuyên phải làm việc với các con số. Ví dụ: Tô màu các đơn hàng có giá trị doanh số lớn hơn 75.000.
Quy trình thực hiện hoàn toàn tương tự, chỉ thay đổi công thức logic.
Các bước thực hiện:
- Chọn vùng dữ liệu A2:C15.
- Mở New Rule trong Conditional Formatting.
- Chọn sử dụng công thức và nhập:
=$C2>75000
Công thức và định dạng có điều kiện để tô sáng hàng dựa trên giá trị doanh số
- Chọn màu xanh (hoặc màu tùy ý) trong phần Format > Fill.
- Nhấn OK.
Kết quả: Bất kỳ hàng nào có giá trị tại cột C (Sales) lớn hơn 75.000 sẽ được tô sáng.
Dữ liệu được tô sáng với định dạng có điều kiện dựa trên giá trị doanh số
Việc sử dụng dấu > (lớn hơn), < (nhỏ hơn), hoặc >= (lớn hơn hoặc bằng) cho phép bạn lọc ra các ngoại lệ hoặc các chỉ số quan trọng trong báo cáo tài chính một cách nhanh chóng.
Sử dụng hàm AND để kết hợp nhiều điều kiện
Trong thực tế, một điều kiện đơn lẻ thường không đủ. Bạn có thể cần tìm những nhân viên thuộc khu vực “US” VÀ có doanh số trên 75.000.
Hàm AND trong Excel cho phép chúng ta kiểm tra nhiều điều kiện cùng lúc. Công thức Conditional Formatting chỉ được kích hoạt (trả về TRUE) khi tất cả các điều kiện bên trong hàm AND đều đúng.
Các bước thiết lập:
- Chọn toàn bộ dữ liệu A2:C15.
- Tạo New Rule và chọn sử dụng công thức.
- Nhập công thức sau:
=AND($B2="US", $C2>75000)
Công thức AND trong định dạng có điều kiện
- Thiết lập màu sắc và nhấn OK.
Công thức này hoạt động như một bộ lọc kép. Excel sẽ xét duyệt từng hàng, nếu cột B là “US” và cột C lớn hơn 75.000, hàng đó sẽ được tô màu.
Công thức And để tô sáng các ô dựa trên ô khác sử dụng định dạng có điều kiện
Kỹ thuật này rất hữu ích để xác định các đối tượng ưu tiên cao thỏa mãn nhiều tiêu chí khắt khe. Nếu bạn muốn tìm hiểu thêm về cách đếm các ô đã được tô màu, hãy tham khảo các bài viết về đếm màu trong Excel.
Sử dụng hàm OR để mở rộng điều kiện
Ngược lại với AND, hàm OR cho phép bạn định dạng nếu bất kỳ điều kiện nào được thỏa mãn.
Ví dụ: Bạn muốn tô màu các hàng thuộc khu vực “US” HOẶC có doanh số trên 75.000 (bất kể khu vực nào). Chỉ cần một trong hai điều kiện đúng, hàng đó sẽ được chọn.
Quy trình thực hiện:
- Chọn vùng dữ liệu A2:C15.
- Tạo New Rule với công thức:
=OR($B2="US", $C2>75000)
Công thức Or trong định dạng có điều kiện
- Thiết lập màu và áp dụng.
Với công thức này, số lượng hàng được tô màu sẽ nhiều hơn so với hàm AND, vì tiêu chí lỏng lẻo hơn.
Công thức Or để tô sáng các ô dựa trên ô khác sử dụng định dạng có điều kiện
Hàm OR thường được dùng để phát hiện các trường hợp cảnh báo diện rộng, nơi mà bất kỳ rủi ro nào (từ cột này hoặc cột kia) đều cần sự chú ý.
Tự động tô màu các hàng có chứa ô trống
Dữ liệu thiếu (missing data) là ác mộng của người làm báo cáo. Trong một bảng dữ liệu lớn, việc sót một vài ô trống là điều khó tránh khỏi.
Bạn có thể dùng Conditional Formatting để tự động “báo động” các dòng bị thiếu dữ liệu. Ví dụ, nếu cột Doanh số (Cột C) bị bỏ trống, hãy tô đỏ cả dòng đó để người nhập liệu dễ dàng nhận thấy.
Cách làm như sau:
- Chọn toàn bộ vùng dữ liệu.
- Tạo quy tắc mới với công thức:
=$C2=""
Định dạng có điều kiện để tô sáng các hàng ô trống
- Chọn màu nổi bật (ví dụ màu cam hoặc đỏ nhạt).
Ký hiệu "" trong Excel đại diện cho giá trị rỗng (blank). Khi công thức phát hiện ô C2 rỗng, nó sẽ kích hoạt định dạng cho cả hàng.
Các hàng có ô trống được tô sáng sử dụng định dạng có điều kiện
Mở rộng kỹ thuật:
- Nếu bạn muốn kiểm tra bất kỳ ô nào trong hàng bị trống (không chỉ cột C), hãy dùng hàm COUNTIF:
=COUNTIF($A2:$C2, "") > 0
Công thức này đếm số lượng ô trống trong phạm vi A2:C2, nếu lớn hơn 0 nghĩa là có ít nhất một ô trống, hàng sẽ được tô màu.
Việc áp dụng Conditional Formatting dựa trên ô khác giúp bảng tính của bạn trở nên thông minh và trực quan hơn rất nhiều. Thay vì phải lọc thủ công, màu sắc sẽ tự động thay đổi ngay khi dữ liệu đầu vào thay đổi.
FAQ
Tại sao công thức Conditional Formatting của tôi không hoạt động?
Lỗi phổ biến nhất là sai địa chỉ tham chiếu. Hãy kiểm tra kỹ dấu $ trong công thức. Nếu bạn muốn tô màu cả hàng, bạn bắt buộc phải khóa cột (ví dụ $A1), nhưng không được khóa dòng (không dùng $A$1). Ngoài ra, hãy đảm bảo vùng áp dụng (Applies to) khớp với dữ liệu bạn muốn định dạng.
Làm cách nào để xóa các quy tắc định dạng đã tạo?
Bạn có thể vào thẻ Home > Conditional Formatting > Clear Rules. Tại đây bạn có thể chọn xóa quy tắc khỏi các ô đang chọn (Selected cells) hoặc xóa toàn bộ quy tắc trên trang tính (Entire sheet).
Tôi có thể dùng biểu tượng (Icon Sets) dựa trên ô khác không?
Mặc định, Icon Sets trong Excel chỉ hoạt động dựa trên giá trị của chính ô đó. Để dùng Icon Sets dựa trên ô khác, bạn cần dùng công thức để sao chép giá trị sang ô mới, hoặc sử dụng các thủ thuật VBA nâng cao hơn. Tuy nhiên, với phương pháp tô màu (Fill) như bài viết hướng dẫn, bạn có thể giải quyết hầu hết các nhu cầu trực quan hóa.
Conditional Formatting có làm chậm file Excel không?
Có, nếu bạn áp dụng quá nhiều quy tắc phức tạp trên một vùng dữ liệu rất lớn (hàng chục nghìn dòng), Excel có thể xử lý chậm hơn. Hãy cố gắng giữ công thức đơn giản và chỉ áp dụng cho vùng dữ liệu cần thiết thay vì chọn cả cột (ví dụ chọn A2:A1000 thay vì chọn cột A:A).
