Cách Sử Dụng Conditional Formatting Dựa Vào Cột Khác Trong Excel: Hướng Dẫn Chi Tiết

Table of Contents

Thời gian đọc ước tính: 12 phút

Những điểm chính

Cơ chế hoạt động: Conditional Formatting (Định dạng có điều kiện) cho phép thay đổi màu sắc ô dựa trên kết quả ĐÚNG/SAI của công thức.
Công thức tùy chỉnh: Sử dụng tùy chọn “Use a formula to determine which cells to format” để tạo quy tắc phụ thuộc vào dữ liệu cột khác.
Tham chiếu hỗn hợp: Hiểu rõ cách dùng dấu $ (ví dụ: $B2 vs B2) để khóa cột hoặc hàng nhằm đảm bảo định dạng áp dụng chính xác.
So sánh tuyệt đối: Kỹ thuật so sánh danh sách dữ liệu với một ô mục tiêu cố định (như doanh số so với chỉ tiêu chung).
Tự động cập nhật: Định dạng sẽ tự động thay đổi ngay lập tức khi dữ liệu đầu vào được chỉnh sửa.

Conditional Formatting là một trong những công cụ trực quan hóa dữ liệu mạnh mẽ nhất trong Excel. Thông thường, người dùng áp dụng định dạng cho chính ô chứa dữ liệu (ví dụ: tô đỏ các ô có giá trị dưới 50). Tuy nhiên, trong phân tích tài chính và quản trị dữ liệu, nhu cầu thường phức tạp hơn: bạn cần tô màu một ô (ví dụ: Tên nhân viên) dựa trên giá trị nằm ở một ô khác (ví dụ: Doanh số bán hàng).

Bài viết này sẽ hướng dẫn chi tiết cách thiết lập các quy tắc định dạng có điều kiện dựa trên giá trị của cột khác hoặc một ô cố định, giúp bảng tính của bạn trở nên trực quan và chuyên nghiệp hơn.

Dữ liệu mẫu với Tên được tô màu bằng Conditional Formatting

Áp Dụng Conditional Formatting Dựa Trên Cột Khác

Hãy xem xét một ví dụ thực tế: Bạn có danh sách học sinh và điểm số tương ứng trong hai cột riêng biệt. Mục tiêu là tô màu tên của những học sinh có điểm số dưới 35. Việc này giúp giáo viên hoặc người quản lý nhanh chóng nhận diện các trường hợp cần lưu ý mà không cần dò từng dòng số liệu.

Bộ dữ liệu Điểm số và Tên học sinh

Quy trình thực hiện từng bước

Để thiết lập quy tắc này, hãy thực hiện chính xác theo 9 bước sau:

Chọn vùng dữ liệu cần định dạng: Bôi đen phạm vi các ô chứa tên học sinh (ví dụ: từ A2 đến A14). Lưu ý: Chỉ chọn phần dữ liệu tên, không chọn tiêu đề cột.

Chọn tên bạn muốn tô màu
Truy cập thẻ Home: Tại thanh công cụ (Ribbon) phía trên cùng, nhấp vào tab Home.

Nhấp vào thẻ Home
Mở Conditional Formatting: Tìm nhóm công cụ Styles và nhấp vào biểu tượng Conditional Formatting.

Nhấp vào Conditional formatting
Tạo quy tắc mới: Trong menu thả xuống, chọn New Rule (Quy tắc mới).

Nhấp vào tùy chọn New Rule
Chọn loại quy tắc: Hộp thoại “New Formatting Rule” xuất hiện. Tại danh sách “Select a Rule Type”, chọn dòng cuối cùng: 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 Use a Formula to determine which cells to format
Nhập công thức logic: Tại ô “Format values where this formula is true”, nhập công thức sau:
=$B2<35

Nhập công thức để tô màu tên
Thiết lập định dạng: Nhấp vào nút Format để mở bảng tùy chỉnh giao diện.
Chọn màu sắc: Chuyển sang thẻ Fill và chọn màu nền mong muốn (ví dụ: màu vàng hoặc đỏ nhạt). Bạn cũng có thể chỉnh sửa phông chữ (đậm, nghiêng) tại thẻ Font.

Chọn màu để định dạng
Hoàn tất: Nhấp OK ở bảng Format Cells, sau đó nhấp OK tiếp ở bảng New Formatting Rule.

Kết quả nhận được là các ô tên học sinh tương ứng với điểm số dưới 35 sẽ tự động chuyển sang màu vàng.

Tên được tô màu dựa trên điểm số

Tính năng này hoạt động hoàn toàn động (dynamic). Nếu bạn sửa điểm số của một học sinh từ 30 lên 40, màu vàng tại tên học sinh đó sẽ ngay lập tức biến mất. Ngược lại, nếu điểm số giảm xuống dưới mức 35, màu sắc sẽ xuất hiện.

Giải thích cơ chế hoạt động của công thức

Công thức =$B2<35 là chìa khóa của phương pháp này. Khi bạn áp dụng Conditional Formatting cho vùng A2:A14, Excel sẽ kiểm tra công thức này cho từng ô riêng biệt theo thứ tự:

Tại ô A2: Excel kiểm tra xem ô B2 có nhỏ hơn 35 không (=$B2<35). Nếu ĐÚNG (TRUE), ô A2 được tô màu.
Tại ô A3: Excel tự động điều chỉnh tham chiếu dòng, kiểm tra B3 (=$B3<35).
Tại ô A4: Excel kiểm tra B4 (=$B4<35).

Tại sao phải có dấu $ trước chữ B?
Dấu $ trong $B2 tạo ra một tham chiếu hỗn hợp (Mixed Reference). Nó khóa cố định cột B nhưng cho phép số hàng (2, 3, 4…) thay đổi khi Excel quét xuống danh sách. Điều này đảm bảo rằng dù bạn đang định dạng ở cột A, Excel vẫn luôn nhìn sang cột B để lấy giá trị so sánh.

Tô Màu Cả Cột Dựa Trên Giá Trị Của Một Ô Cố Định

Một kịch bản phổ biến khác trong quản lý kinh doanh là so sánh hiệu suất của toàn bộ nhân viên với một mục tiêu chung duy nhất (Target). Ví dụ: Bạn muốn tô màu tên các Đại diện Kinh doanh (Sales Rep) có doanh số vượt mức chỉ tiêu được ghi tại ô D2.

Dữ liệu Doanh số với Mục tiêu trong một ô

Quy trình thực hiện tương tự như ví dụ trước nhưng có sự khác biệt quan trọng trong cách viết công thức.

Các bước thực hiện

Chọn vùng dữ liệu: Bôi đen cột tên nhân viên (ví dụ: A2:A10).
Mở công cụ: Vào Home > Conditional Formatting > New Rule.
Chọn loại quy tắc: Chọn Use a formula to determine which cells to format.

Nhấp vào tùy chọn New Rule
Nhập công thức so sánh: Nhập công thức sau vào ô quy tắc:
=$B2>=$D$2

Nhập công thức trong hộp thoại Conditional Formatting Rule
Định dạng: Nhấp nút Format, chọn màu xanh (tượng trưng cho đạt chỉ tiêu) và nhấn OK.
Xác nhận: Nhấn OK để áp dụng.

Excel sẽ so sánh doanh số của từng nhân viên (Cột B) với giá trị mục tiêu tại ô D2. Bất kỳ ai có doanh số cao hơn hoặc bằng mục tiêu sẽ được tô sáng.

Tên có giá trị thấp hơn giá trị trong ô được tô màu

Phân tích công thức tham chiếu tuyệt đối

Trong công thức =$B2>=$D$2, chúng ta sử dụng hai loại tham chiếu khác nhau:

$B2: Tham chiếu cột cố định, dòng thay đổi. Dùng để lấy giá trị doanh số của từng nhân viên tương ứng.
$D$2: Tham chiếu tuyệt đối (Absolute Reference). Dấu $ xuất hiện trước cả chữ cái cột (D) và số hàng (2).

Tại sao phải dùng $D$2?
Khi Excel xử lý quy tắc định dạng cho các ô tiếp theo (A3, A4, A5…), nó cần so sánh doanh số của nhân viên đó với duy nhất ô D2. Nếu không có dấu $ (ví dụ chỉ viết D2), khi xuống dòng A3, Excel sẽ trượt tham chiếu xuống D3 (ô trống hoặc sai dữ liệu), dẫn đến kết quả sai lệch.

Việc cố định $D$2 đảm bảo tính nhất quán của mốc so sánh cho toàn bộ tập dữ liệu.

Mở Rộng: Tô Màu Toàn Bộ Hàng (Highlight Entire Row)

Mặc dù việc tô màu một ô đơn lẻ rất hữu ích, nhưng trong các bảng dữ liệu lớn (như bảng điều khiển Forex hoặc báo cáo tài chính), việc tô màu toàn bộ hàng giúp người đọc dễ dàng theo dõi thông tin ngang qua nhiều cột.

Để thực hiện điều này, bạn chỉ cần thay đổi bước Chọn vùng dữ liệu:

Thay vì chỉ chọn cột A (A2:A14), hãy chọn toàn bộ bảng dữ liệu (ví dụ: A2:C14).
Sử dụng lại cùng một công thức: =$B2<35.
Thiết lập định dạng màu sắc như bình thường.

Do chúng ta đã khóa cột B bằng dấu $ ($B2), công thức này sẽ hoạt động đúng cho cả cột A, cột B và cột C. Đối với bất kỳ ô nào trong hàng 2 (A2, B2, C2), Excel đều kiểm tra giá trị của B2. Nếu B2 < 35, cả ba ô A2, B2, C2 đều sẽ đổi màu, tạo hiệu ứng tô sáng toàn bộ hàng.

Ứng Dụng Nâng Cao: Sử Dụng Hàm Logic (AND/OR)

Trong thực tế phân tích dữ liệu, điều kiện thường không đơn giản chỉ là “lớn hơn” hoặc “nhỏ hơn”. Bạn có thể cần kết hợp nhiều tiêu chí. Conditional Formatting hỗ trợ tốt các hàm logic ngay trong phần công thức.

Ví dụ với hàm AND (Và)

Giả sử bạn muốn tô màu tên nhân viên nếu họ thỏa mãn 2 điều kiện:

Doanh số (Cột B) > 50,000
Số năm kinh nghiệm (Cột C) > 2 năm

Công thức sẽ là:
=AND($B2>50000, $C2>2)

Chỉ khi cả hai điều kiện đều đúng, định dạng mới được áp dụng.

Ví dụ với hàm OR (Hoặc)

Bạn muốn cảnh báo nếu học sinh gặp vấn đề:

Điểm thi (Cột B) < 35
HOẶC Số ngày nghỉ (Cột C) > 5

Công thức sẽ là:
=OR($B2<35, $C2>5)

Nếu một trong hai (hoặc cả hai) điều kiện xảy ra, hàng đó sẽ được tô màu cảnh báo.

5 Lỗi Thường Gặp Khi Sử Dụng Conditional Formatting Theo Công Thức

Khi áp dụng kỹ thuật này, người dùng thường gặp phải một số lỗi khiến định dạng không hiển thị hoặc hiển thị sai vị trí. Dưới đây là danh sách kiểm tra nhanh:

Quên dấu $ cố định cột: Nếu bạn viết B2<35 thay vì $B2<35, khi áp dụng cho cả hàng, các ô ở cột bên phải sẽ tham chiếu sai sang cột C, D… dẫn đến việc tô màu lộn xộn.
Chọn cả tiêu đề cột: Không nên chọn dòng tiêu đề (Header) khi tạo quy tắc, vì Excel sẽ áp dụng công thức cho dòng tiêu đề và làm lệch dữ liệu đi một hàng. Luôn bắt đầu chọn từ dòng dữ liệu đầu tiên (thường là hàng 2).
Dữ liệu không đồng nhất: Đảm bảo các con số là định dạng số (Number), không phải dạng văn bản (Text). Một ô chứa ” 35″ (có dấu cách) hoặc định dạng text sẽ không thể so sánh toán học chính xác.
Dấu ngoặc kép cho văn bản: Khi so sánh dữ liệu dạng chữ, bạn bắt buộc phải dùng dấu ngoặc kép. Ví dụ: =$C2="Đạt". Nếu thiếu ngoặc kép, Excel sẽ không hiểu.
Sai phạm vi áp dụng (Applies to): Sau khi tạo quy tắc, bạn có thể kiểm tra lại trong phần Manage Rules. Đảm bảo mục “Applies to” bao phủ đúng vùng dữ liệu bạn mong muốn.

Kết Hợp Với Các Công Cụ Khác

Việc nắm vững Conditional Formatting dựa trên công thức mở ra khả năng kết hợp với các tính năng khác của Excel để tạo ra các Dashboard (bảng điều khiển) chuyên nghiệp:

Data Validation: Tạo danh sách thả xuống (Drop-down list) để chọn tiêu chí. Ví dụ: Ô D2 là danh sách chọn “Phòng Ban”. Conditional Formatting sẽ tô màu toàn bộ nhân viên thuộc phòng ban được chọn tại D2.
Filter & Sort: Sau khi tô màu, bạn có thể lọc danh sách theo màu sắc (Filter by Color) để trích xuất nhanh danh sách những người đạt chỉ tiêu hoặc cần cảnh báo.

Hiểu rõ cách dữ liệu tương tác với nhau thông qua tham chiếu ô là nền tảng của việc xây dựng các mô hình tài chính và báo cáo quản trị hiệu quả.

FAQ: Câu Hỏi Thường Gặp

Tôi có thể tô màu dựa trên dữ liệu văn bản (Text) thay vì số không?
Hoàn toàn được. Ví dụ, để tô màu các đơn hàng có trạng thái là “Đã giao”, bạn dùng công thức: =$C2="Đã giao". Lưu ý văn bản phải đặt trong dấu ngoặc kép.

Làm sao để xóa quy tắc Conditional Formatting?
Bạn chọn vùng dữ liệu, vào Conditional Formatting > Clear Rules. Bạn có thể chọn xóa quy tắc khỏi các ô đang chọn (Selected cells) hoặc toàn bộ trang tính (Entire Sheet).

Conditional Formatting có làm chậm file Excel không?
Với các tập dữ liệu nhỏ và vừa (dưới 10.000 dòng), ảnh hưởng là không đáng kể. Tuy nhiên, nếu bạn áp dụng hàng nghìn quy tắc phức tạp trên file dữ liệu lớn (Big Data), tốc độ tính toán có thể bị giảm. Nên sử dụng hợp lý và dọn dẹp các quy tắc thừa.

Tôi có thể dùng biểu tượng (Icon Sets) dựa trên cột khác không?
Mặc định Icon Sets chỉ dựa trên giá trị của chính ô đó. Để dùng Icon dựa trên cột khác, bạn cần dùng thủ thuật: Gán giá trị cột đó sang ô cần hiện icon bằng công thức đơn giản, sau đó áp dụng Icon Sets và chọn tùy chọn “Show Icon Only” (Chỉ hiện biểu tượng, ẩn số).

Làm sao để sửa công thức đã nhập sai?
Vào Conditional Formatting > Manage Rules. Chọn quy tắc cần sửa và nhấn Edit Rule. Tại đây bạn có thể sửa lại công thức logic.

Cách Sử Dụng Conditional Formatting Dựa Vào Cột Khác Trong Excel: Hướng Dẫn Chi Tiết

Những điểm chính