Chuyển tới nội dung chính

Bài 10: Định Dạng Có Điều Kiện (Conditional Formatting)

Giai đoạn 3 – Nâng cao · Mục tiêu: Tự động thay đổi diện mạo ô dựa trên giá trị hoặc công thức, tạo bảng tính trực quan và chuyên nghiệp.

1. Conditional Formatting là gì?​

Conditional Formatting cho phép Excel tự động thay đổi định dạng (màu nền, màu chữ, font chữ, viền) của ô dựa trên:

  • Giá trị của chính ô đó.
  • Công thức liên quan đến ô đó hoặc ô khác.

Vị trí: Tab Home → nhóm Styles → Conditional Formatting.

2. Các quy tắc cơ bản (Highlight Cells Rules)​

Quy tắcMô tảVí dụ
Greater ThanTô màu ô có giá trị lớn hơn XDoanh thu > 100 triệu → tô xanh
Less ThanTô màu ô có giá trị nhỏ hơn XTồn kho < 10 → tô đỏ
BetweenTô màu ô trong khoảngĐiểm 5-7 → tô vàng
Equal ToTô màu ô bằng giá trị cụ thểTrạng thái = "Hoàn thành" → tô xanh lá
Text That ContainsTô màu ô chứa textChứa "Khẩn cấp" → tô đỏ đậm
Duplicate ValuesTô màu ô trùng lặpPhát hiện mã nhân viên trùng

Cách tạo​

  1. Chọn vùng dữ liệu.
  2. Home → Conditional Formatting → Highlight Cells Rules → chọn quy tắc.
  3. Nhập giá trị và chọn kiểu định dạng.

3. Quy tắc Top/Bottom​

Quy tắcMô tả
Top 10 ItemsTô màu N giá trị lớn nhất
Bottom 10 ItemsTô màu N giá trị nhỏ nhất
Top 10%Tô màu N% giá trị lớn nhất
Above AverageTô màu giá trị trên trung bình
Below AverageTô màu giá trị dưới trung bình

4. Bộ quy tắc trực quan (Visual Sets)​

4.1. Data Bars (Thanh độ dài)​

Hiển thị thanh ngang bên trong ô, độ dài tỷ lệ với giá trị → trực quan hóa nhanh không cần biểu đồ.

Conditional Formatting → Data Bars → chọn kiểu (Gradient Fill hoặc Solid Fill).

4.2. Color Scales (Thang độ màu)​

Tô nền ô theo thang màu liên tục từ thấp đến cao:

  • Xanh lá (cao) → Vàng (trung bình) → Đỏ (thấp)
  • Hoặc các bảng màu khác tùy chọn.

Conditional Formatting → Color Scales → chọn bảng màu.

4.3. Icon Sets (Bộ biểu tượng)​

Gắn biểu tượng vào ô dựa trên giá trị:

  • (Đèn giao thông)
  • (Mũi tên xu hướng)
  • (Xếp hạng sao)
  • (Hoàn thành/Chưa hoàn thành)

5. Conditional Formatting bằng Công thức (Custom Formula)​

Đây là tính năng mạnh mẽ nhất, cho phép định dạng ô dựa trên bất kỳ logic nào.

Cách tạo​

  1. Conditional Formatting → New Rule → Use a formula to determine which cells to format.
  2. Nhập công thức (kết quả phải là TRUE hoặc FALSE).
  3. Click Format → chọn định dạng.

Ví dụ 1: Tô đỏ toàn bộ dòng đơn hàng quá hạn​

Chọn toàn bộ vùng dữ liệu (ví dụ: A2:F100), nhập công thức:

=$E2<TODAY()

→ Nếu cột E (ngày hạn) < ngày hôm nay → tô đỏ cả dòng.

:::info Lưu ý quan trọng

  • Dùng $E2 (khóa cột, thả dòng) để công thức áp dụng đúng cho mỗi dòng.
  • Không khóa dòng ($E$2) vì muốn kiểm tra mỗi dòng riêng. :::

Ví dụ 2: Highlight dòng chẵn (tạo hiệu ứng zebra)​

=MOD(ROW(),2)=0

Ví dụ 3: Tô màu ô dựa trên giá trị ô khác​

=AND($B2="VIP", $D2>10000000)

→ Tô nổi bật dòng khách hàng VIP có doanh thu > 10 triệu.

6. Quản lý quy tắc​

  • Conditional Formatting → Manage Rules → Xem, sửa, xóa, sắp xếp thứ tự ưu tiên.
  • Quy tắc ở trên cùng có ưu tiên cao nhất.
  • Tick "Stop If True" để dừng kiểm tra các quy tắc phía dưới khi đã khớp.

Bài tập thực hành​

Cho bảng Báo cáo doanh thu (Nhân viên, Phòng ban, Doanh thu, Mục tiêu, Ngày hạn):

  1. Tô đỏ ô Doanh thu < 50% Mục tiêu, tô xanh ô Doanh thu ≥ 100% Mục tiêu.
  2. Thêm Data Bars cho cột Doanh thu.
  3. Thêm Icon Sets () cho cột tỷ lệ hoàn thành.
  4. Dùng công thức để tô đỏ toàn bộ dòng có ngày hạn đã qua.

Bài trước: Bài 9 – Công Cụ Quản Lý Dữ Liệu · Bài tiếp: Bài 11 – Pivot Table