Excel Color Based On Value: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

Có thể bạn quan tâm: Cách Chuyển Dòng Thành Cột Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

Mở Đầu

Trong môi trường làm việc hiện đại, Excel không chỉ là một công cụ tính toán số liệu mà còn là một nền tảng mạnh mẽ để trực quan hoá dữ liệu. Một trong những tính năng quan trọng giúp người dùng nhanh chóng nhận diện xu hướng, bất thường hay điểm nổi bật trong bảng tính là định dạng có điều kiện (Conditional Formatting), đặc biệt là đánh màu (color) dựa trên giá trị (value). Bài viết này sẽ cung cấp cho bạn một hướng dẫn chi tiết, từ cơ bản đến nâng cao, giúp bạn áp dụng màu sắc một cách thông minh, hiệu quả và chuyên nghiệp.

Có thể bạn quan tâm: Excel Cannot Run The Macro – Nguyên Nhân, Giải Pháp Và Cách Khắc Phục Chi Tiết

1. Tổng Quan Về Conditional Formatting

1.1 Định Nghĩa

Conditional Formatting là công cụ cho phép bạn tự động thay đổi định dạng (màu nền, màu chữ, biểu tượng, thanh dữ liệu…) của các ô trong Excel dựa trên các quy tắc mà bạn thiết lập. Khi dữ liệu trong ô thỏa mãn điều kiện, Excel sẽ tự động áp dụng định dạng tương ứng.

1.2 Lợi Ích Khi Sử Dụng

• Tiết kiệm thời gian: Không cần kiểm tra từng ô một.
• Tăng tính trực quan: Dễ dàng nhận ra xu hướng, ngoại lệ.
• Hỗ trợ quyết định: Giúp người dùng đưa ra quyết định nhanh chóng dựa trên màu sắc.
• Cải thiện chất lượng báo cáo: Bảng tính trở nên chuyên nghiệp, dễ đọc.

Có thể bạn quan tâm: Excel Không Thể Chèn Ô Mới: Nguyên Nhân, Giải Pháp Và Cách Khắc Phục Chi Tiết

2. Các Loại Định Dạng Có Điều Kiện Cơ Bản

3. Cách Tạo Định Dạng Màu Dựa Trên Giá Trị Cơ Bản

3.1 Bước 1: Chọn Dải Ô Cần Định Dạng

• Click và kéo để chọn vùng dữ liệu muốn áp dụng màu.
• Hoặc nhấn Ctrl + A nếu muốn áp dụng cho toàn bộ bảng.

3.2 Bước 2: Mở Conditional Formatting

• Vào Home → Conditional Formatting.

3.3 Bước 3: Chọn Loại Định Dạng

• Color Scales: Tự động tạo dải màu (ví dụ: xanh → vàng → đỏ) dựa trên giá trị min/max.
• New Rule: Tạo quy tắc tùy chỉnh nếu các mẫu sẵn không đáp ứng nhu cầu.

3.4 Bước 4: Định Nghĩa Quy Tắc (Ví dụ: Color Scales)

1. Chọn Color Scales → More Rules để tùy chỉnh.
2. Trong hộp New Formatting Rule, chọn Format all cells based on their values.
3. Chọn 2-Color Scale hoặc 3-Color Scale.
4. Đặt Minimum (giá trị thấp nhất) và Maximum (giá trị cao nhất) cùng màu tương ứng.
5. Nhấn OK.

3.5 Bước 5: Kiểm Tra Kết Quả

• Khi dữ liệu thay đổi, màu sắc sẽ tự động cập nhật.
• Để xem hoặc chỉnh sửa quy tắc, quay lại Conditional Formatting → Manage Rules.

4. Tạo Định Dạng Màu Dựa Trên Giá Trị Tùy Chỉnh (Custom Rules)

4.1 Sử Dụng Công Thức (Formula) Trong Conditional Formatting

Khi các tiêu chí phức tạp hơn, bạn có thể dùng công thức để xác định màu.

Ví Dụ 1: Đánh dấu giá trị > 1000 và < 2000

1. Chọn dải ô (ví dụ A2:A100).
2. Home → Conditional Formatting → New Rule → Use a formula to determine which cells to format.
3. Nhập công thức:
   excel =AND(A2>1000, A2<2000)
4. Nhấn Format, chọn màu nền (ví dụ: màu xanh nhạt), OK.

Ví Dụ 2: Đánh dấu ô có giá trị là trung bình của cột

1. Công thức trung bình: =AVERAGE($A$2:$A$100).
2. Quy tắc:
   excel =A2=AVERAGE($A$2:$A$100)
3. Định dạng màu vàng.

4.2 Sử Dụng Hàm ISEVEN / ISODD Để Đánh Màu Xe Lách

=ISEVEN(ROW())

• Áp dụng cho toàn cột để tạo dải màu xen kẽ, giúp đọc dữ liệu dễ hơn.

4.3 Đánh Màu Dựa Trên Độ Lệch Chuẩn (Standard Deviation)

1. Tính độ lệch chuẩn: =STDEV.P($B$2:$B$100).
2. Quy tắc:
   excel =ABS(B2-AVERAGE($B$2:$B$100))>2STDEV.P($B$2:$B$100)
3. Định dạng màu đỏ để chỉ ra các giá trị ngoại lệ (outliers).

5. Ứng Dụng Thực Tiễn: Các Kịch Bản Phổ Biến

5.1 Bảng Kế Toán – Đánh Dấu Nợ và Có

• Nợ: Nếu giá trị < 0 → màu đỏ.
• Có: Nếu giá trị > 0 → màu xanh.

= A2 < 0 (định dạng màu đỏ)
= A2 > 0 (định dạng màu xanh)

5.2 Theo Dõi KPI – Thang Màu 3 Mức

• Kém (< 60%): Đỏ.
• Trung bình (60% – 80%): Vàng.
• Tốt (> 80%): Xanh.
  Sử dụng 3-Color Scale hoặc 3 quy tắc riêng.

5.3 Quản Lý Dự Án – Đánh Dấu Trễ

• Nếu ngày hoàn thành thực tế > ngày dự kiến → màu đỏ.
• Nếu chênh lệch trong 3 ngày → màu vàng.
• Nếu hoàn thành đúng hạn → màu xanh.

= C2 > D2 (đỏ)
= AND(C2-D2>0, C2-D2<=3) (vàng)
= C2 <= D2 (xanh)

(C = ngày thực tế, D = ngày dự kiến)

5.4 Bảng Điểm Học Sinh – Đánh Giá Độ Khó

Có thể bạn quan tâm: Học Excel Cho Người Mới Bắt Đầu: Hướng Dẫn Toàn Diện Từ Cơ Bản Đến Nâng Cao

• A (≥ 90): Xanh lá.
• B (80‑89): Xanh lục.
• C (70‑79): Vàng.
• D/F (<70): Đỏ.
  Sử dụng IF trong công thức:

=IF(B2>=90,TRUE,FALSE) (xanh)
=IF(AND(B2>=80,B2<90),TRUE,FALSE) (xanh lục)
...

5.5 Quản Lý Kho – Cảnh Báo Hết Hàng

• Khi tồn kho < mức tối thiểu → màu đỏ.
• Khi tồn kho giữa mức tối thiểu và mức tối đa → màu vàng.
• Khi tồn kho > mức tối đa → màu xanh.

= A2 < Minimum_Stock (đỏ)
= AND(A2>=Minimum_Stock, A2<=Maximum_Stock) (vàng)
= A2 > Maximum_Stock (xanh)

6. Kỹ Thuật Nâng Cao

6.1 Sử Dụng VBA Để Tự Động Hóa Định Dạng

Trong một số trường hợp, số lượng quy tắc lớn hoặc yêu cầu phức tạp hơn có thể gây chậm Excel. VBA cho phép bạn:

Sub ColorBasedOnValue() Dim rng As Range, cell As Range Set rng = Range("B2:B100") For Each cell In rng If cell.Value > 1000 Then cell.Interior.Color = RGB(0, 255, 0) ' Xanh ElseIf cell.Value < 0 Then cell.Interior.Color = RGB(255, 0, 0) ' Đỏ Else cell.Interior.Color = RGB(255, 255, 0) ' Vàng End If Next cell
End Sub

• Ưu điểm: Kiểm soát chi tiết, giảm số lượng quy tắc Conditional Formatting.
• Nhược điểm: Cần kiến thức lập trình, không tự động cập nhật khi dữ liệu thay đổi (cần chạy macro).

6️⃣.2 Định Dạng Dựa Trên Dữ Liệu Từ Bảng Pivot

Pivot Table thường được dùng để tổng hợp dữ liệu. Bạn có thể:
1. Sao chép giá trị từ Pivot sang một ô bên ngoài.
2. Áp dụng Conditional Formatting cho ô này.
3. Hoặc sử dụng Pivot Table Styles để thay đổi màu sắc dựa trên tổng/đếm.

6.3 Định Dạng Dựa Trên Định Dạng Số (Number Formatting)

Bạn có thể kết hợp Custom Number Format để hiển thị màu trong cùng một ô:

Red<0General;Green>=0General

• Đặt công thức này trong Format Cells → Number → Custom.
• Khi giá trị < 0, số sẽ hiển thị màu đỏ; ngược lại màu xanh.

6.4 Định Dạng Dựa Trên Thông Tin Từ Các Sheet Khác

Sử dụng công thức tham chiếu tới sheet khác:

=Sheet2!$C$5

Ví dụ: Đánh dấu ô A2 trong Sheet1 nếu giá trị tương ứng trong Sheet2!B2 > 500.
1. Chọn A2.
2. Conditional Formatting → New Rule → Use a formula.
3. Nhập: =Sheet2!B2>500.

6.5 Đánh Dấu Đổi Màu Dựa Trên Định Dạng Văn Bản (Text)

• Ví dụ: Nếu ô chứa từ “Urgent”, màu nền đỏ.

=SEARCH("Urgent",A2)>0

• Lưu ý: SEARCH không phân biệt hoa thường; dùng FIND nếu muốn phân biệt.

7. Tối Ưu Hóa Hiệu Năng Khi Sử Dụng Conditional Formatting

1. Giảm số lượng quy tắc: Hạn chế tạo quá nhiều quy tắc trên cùng một vùng dữ liệu; dùng công thức tổng hợp thay vì nhiều quy tắc riêng.
2. Sử dụng vùng dữ liệu cố định: Thay vì áp dụng trên toàn cột (A:A), chỉ áp dụng trên vùng thực tế (A2:A1000) để giảm tải.
3. Xóa quy tắc không cần thiết: Vào Conditional Formatting → Manage Rules, xóa các quy tắc dư thừa.
4. Sử dụng bảng (Table) thay vì dải ô: Khi dữ liệu mở rộng, bảng tự động áp dụng định dạng cho các hàng mới.
5. Kiểm tra tính tương thích: Một số phiên bản Excel (như Excel 2003) không hỗ trợ các tính năng mới như Data Bars, Color Scales.

8. Thực Hành: Bài Tập Tự Làm

Bài 1: Đánh dấu doanh thu theo mức

• Dữ liệu: Cột B (Doanh thu) từ B2:B50.
• Yêu cầu:
• Doanh thu > 1,000,000 → màu xanh lá.
• 500,000 ≤ Doanh thu ≤ 1,000,000 → màu vàng.
• Doanh thu < 500,000 → màu đỏ.

Giải pháp: Tạo 3 quy tắc Conditional Formatting với công thức:

=B2>1000000 (xanh lá)
=AND(B2>=500000, B2<=1000000) (vàng)
=B2<500000 (đỏ)

Bài 2: Tạo dải màu cho mức độ hài lòng

• Cột D chứa điểm hài lòng (0‑100).
• Sử dụng 3-Color Scale: 0 = Đỏ, 50 = Vàng, 100 = Xanh.

Giải pháp: Chọn D2:D100 → Conditional Formatting → Color Scales → More Rules → 3-Color Scale và thiết lập các màu.

Bài 3: Đánh dấu ngày trễ trong dự án

• Cột E = Ngày dự kiến, Cột F = Ngày thực tế.
• Nếu F > E + 5 ngày → màu đỏ.
• Nếu F ≤ E → màu xanh.
• Nếu chênh lệch 1‑5 ngày → màu vàng.

Công thức:

=F2>E2+5 (đỏ)
=F2<=E2 (xanh)
=AND(F2>E2, F2<=E2+5) (vàng)

Thực hành các bài tập này sẽ giúp bạn nắm vững cách tạo quy tắc màu dựa trên giá trị trong Excel.

9. Những Sai Lầm Thường Gặp Khi Định Dạng Màu Dựa Trên Giá Trị

10. Kết Luận

Việc đánh màu (color) dựa trên giá trị (value) trong Excel không chỉ giúp tăng tính trực quan mà còn hỗ trợ quyết định nhanh chóng và chính xác. Từ những quy tắc đơn giản như Color Scales, Data Bars đến các công thức tùy chỉnh phức tạp, hay thậm chí là tự động hoá bằng VBA, bạn đều có thể tạo ra những bảng tính chuyên nghiệp, dễ đọc và hiệu quả.

Những điểm quan trọng cần nhớ:

1. Xác định mục tiêu: Bạn muốn nhấn mạnh gì? (ngoại lệ, xu hướng, mức độ đạt được…)
2. Chọn loại định dạng phù hợp: Color Scale cho dải giá trị, Icon Sets cho trạng thái, Data Bars cho so sánh.
3. Sử dụng công thức khi các tiêu chí phức tạp hơn.
4. Tối ưu hoá hiệu năng bằng cách giới hạn dải ô và giảm số quy tắc.
5. Kiểm tra và duy trì: Định kỳ rà soát các quy tắc để tránh lỗi và cải thiện báo cáo.

Hy vọng bài viết này đã cung cấp cho bạn một kiến thức toàn diện, từ cơ bản đến nâng cao, để áp dụng Excel color based on value một cách linh hoạt và hiệu quả trong công việc hằng ngày. Hãy bắt đầu thực hành ngay hôm nay và biến những bảng tính khô khan thành công cụ trực quan mạnh mẽ!

Tiến sĩ Lê Thống Nhất

Tiến sĩ Lê Thống Nhất sinh năm 1955 tại Nghệ An, là nhà giáo, nhà báo và doanh nhân trong lĩnh vực giáo dục. Ông từng giảng dạy Toán học tại Trường THPT Chuyên Phan Bội Châu và đảm nhiệm nhiều vai trò trong ngành giáo dục, từ viết sách, biên soạn tài liệu đến sáng lập các dự án hỗ trợ học tập trực tuyến.

Ông là người sáng lập BigSchool – nền tảng giáo dục trực tuyến kết nối giáo viên và học sinh cả nước. Ngoài ra, ông còn nổi tiếng với vai trò tác giả, diễn giả truyền cảm hứng và khách mời trên nhiều chương trình truyền hình, trong đó có “Ai là triệu phú” trên truyền hình VTV.

Trong suốt sự nghiệp, Tiến sĩ Lê Thống Nhất đã nhận nhiều giải thưởng giáo dục và được biết đến như một người luôn đổi mới, ứng dụng công nghệ vào dạy và học, góp phần lan tỏa tri thức và niềm say mê học tập cho thế hệ trẻ.