Data Validation và Conditional Formatting: nhập liệu không sai, số liệu tự nổi bật

Có hai loại vấn đề với dữ liệu: dữ liệu nhập vào sai và dữ liệu đúng nhưng không ai nhìn thấy điều quan trọng.

Excel có hai công cụ giải quyết đúng hai vấn đề đó. Data Validation chặn lỗi ngay khi người ta gõ. Conditional Formatting làm những con số đáng chú ý tự nổi lên mà không cần ai đi dò từng dòng.

Cả hai đều nằm trong nhóm "học 30 phút, dùng cả đời".

Phần 1 — Data Validation: chặn lỗi từ gốc

Nếu file của bạn có người khác nhập liệu vào, bạn sẽ nhận về đủ mọi biến thể: "Miền Bắc", "miền bắc", "MB", "Miên Bắc", "Miền Bắc " (thừa dấu cách). Sau đó bạn ngồi dọn.

Data Validation chuyển việc dọn dẹp thành việc phòng ngừa.

Tạo danh sách xổ xuống (dropdown)

Cách làm chuẩn, không phải cách nhanh:

1. Tạo một sheet riêng tên DanhMuc, liệt kê các giá trị hợp lệ theo cột.
2. Chọn vùng đó → Ctrl + T để chuyển thành Table (để danh sách tự mở rộng khi thêm mục mới).
3. Đặt tên cho vùng: Formulas → Define Name, ví dụ DS_KhuVuc.
4. Chọn vùng cần nhập liệu → Data → Data Validation → Allow: List → ô Source gõ =DS_KhuVuc.

Vì sao không gõ thẳng danh sách vào ô Source (Miền Bắc,Miền Nam,Miền Trung)? Vì khi cần thêm một giá trị, bạn phải mở lại hộp thoại và sửa cho từng vùng. Với danh sách đặt tên, bạn chỉ cần thêm dòng vào bảng danh mục.

Các kiểu kiểm tra khác

Vài quy tắc Custom hay dùng:

=COUNTIF($A$2:$A$500, A2)=1          → không cho nhập trùng
=ISNUMBER(A2)                        → bắt buộc là số
=EXACT(A2, UPPER(A2))                → bắt buộc viết hoa toàn bộ
=A2>=B2                              → ngày kết thúc không được trước ngày bắt đầu
=LEN(A2)=10                          → đúng 10 ký tự (số điện thoại)

Công thức phải viết cho ô đầu tiên của vùng đang chọn, và dùng tham chiếu tương đối cho ô đó (A2), tuyệt đối cho vùng cố định ($A$2:$A$500).

Thông báo hướng dẫn và cảnh báo lỗi

Đây là phần nhiều người bỏ qua nhưng lại quyết định người khác có dùng đúng file của bạn hay không.

Trong hộp thoại Data Validation:

• Tab Input Message: hiện chú thích khi người dùng chọn ô. Ví dụ: "Chọn khu vực từ danh sách. Nếu thiếu khu vực, báo cho người quản lý file."
• Tab Error Alert: chọn kiểu cảnh báo.

Luôn viết nội dung cảnh báo cụ thể. "Giá trị không hợp lệ" khiến người dùng bế tắc; "Số lượng phải là số nguyên từ 1 đến 999" thì họ tự sửa được.

Danh sách phụ thuộc (dependent dropdown)

Bài toán: chọn Tỉnh/Thành ở cột A thì cột B chỉ hiện các Quận/Huyện thuộc tỉnh đó.

Cách làm truyền thống với INDIRECT:

1. Tạo các danh sách con, mỗi danh sách đặt tên trùng đúng với tên tỉnh (tên vùng không được chứa dấu cách — dùng dấu gạch dưới).
2. Ở cột A: Data Validation List trỏ tới danh sách tỉnh.
3. Ở cột B: Data Validation List với Source là =INDIRECT(SUBSTITUTE(A2," ","_")).

Cách làm hiện đại hơn trên Microsoft 365, không cần đặt tên vùng:

=FILTER(BangQuan[Quan], BangQuan[Tinh]=A2)

Đặt công thức này ở một vùng phụ rồi trỏ Data Validation vào vùng tràn của nó (=$H$2#).

Kiểm tra dữ liệu đã nhập sai từ trước

Data Validation chỉ chặn dữ liệu mới. Với dữ liệu đã có sẵn: Data → Data Validation → Circle Invalid Data. Excel khoanh đỏ mọi ô vi phạm quy tắc. Bấm Clear Validation Circles để xóa vòng khoanh.

Phần 2 — Conditional Formatting: để số liệu tự lên tiếng

Nguyên tắc bao trùm: tô màu để trả lời một câu hỏi, không phải để trang trí. Trước khi tạo quy tắc, hãy nói rõ câu hỏi: "Đơn nào quá hạn?", "Sinh viên nào dưới chuẩn?", "Tháng nào giảm so với tháng trước?"

Các quy tắc dựng sẵn

Home → Conditional Formatting:

Ba lời khuyên thực dụng:

• Data Bars hiệu quả hơn Color Scales cho việc so sánh độ lớn, vì mắt người đọc chiều dài chính xác hơn đọc sắc độ.
• Icon Sets nên giới hạn ở 3 biểu tượng. Bộ 5 mức khiến người xem phải tra chú giải.
• Đèn giao thông đỏ–xanh lá gây khó cho khoảng 8% nam giới bị mù màu đỏ–lục. Dùng cặp đỏ–xanh dương, hoặc kèm thêm ký hiệu/chữ.

Tô cả dòng bằng công thức — kỹ thuật quan trọng nhất

Các quy tắc dựng sẵn chỉ tô đúng ô thỏa điều kiện. Trong báo cáo thật, bạn thường muốn tô cả dòng khi một cột nào đó thỏa điều kiện.

Cách làm:

1. Chọn toàn bộ vùng dữ liệu, bắt đầu từ ô trên cùng bên trái (ví dụ A2:F500).
2. Conditional Formatting → New Rule → Use a formula to determine which cells to format.
3. Nhập công thức viết cho dòng đầu tiên của vùng chọn.
4. Chọn định dạng → OK.

Các công thức hay dùng:

=$E2="Quá hạn"                        → tô dòng có trạng thái quá hạn
=$D2<TODAY()                          → tô dòng có hạn đã qua
=AND($D2>=TODAY(), $D2<=TODAY()+7)    → hạn trong vòng 7 ngày tới
=$C2<5                                → tô dòng sinh viên dưới 5 điểm
=COUNTIF($B$2:$B$500,$B2)>1           → tô các dòng có mã trùng
=MOD(ROW(),2)=0                       → tô dòng chẵn (kẻ sọc thủ công)

Điểm quyết định thành bại: dấu $. Viết $E2 (cố định cột, thả dòng) thì quy tắc xét cột E cho mỗi dòng — đúng ý. Viết E2 thì mỗi cột lại xét một cột khác nhau, kết quả tô loang lổ vô nghĩa.

Quy tắc nhớ: cố định cột chứa điều kiện, thả tự do phần dòng.

So sánh với ô nhập liệu

Đặt một ô để người dùng gõ ngưỡng, ví dụ $H$1, rồi viết quy tắc:

=$F2>$H$1

Giờ người xem chỉ cần đổi số trong H1 là toàn bộ bảng tô lại theo ngưỡng mới. Đây là cách rất nhanh để làm một báo cáo "tương tác" mà không cần Slicer hay macro.

Quản lý quy tắc

Conditional Formatting → Manage Rules → Show formatting rules for: This Worksheet.

Trong bảng này bạn thấy toàn bộ quy tắc, thứ tự áp dụng và vùng áp dụng.

• Thứ tự quan trọng: quy tắc ở trên được xét trước. Nếu hai quy tắc cùng chạm một ô, quy tắc trên thắng ở những định dạng trùng nhau.
• Cột Stop If True: tick vào thì khi quy tắc này đúng, các quy tắc phía dưới sẽ bị bỏ qua cho ô đó.
• Kiểm tra cột Applies to khi định dạng trông kỳ lạ — thủ phạm thường là vùng áp dụng bị vỡ thành hàng chục mảnh sau khi ai đó copy-paste.

Dọn dẹp khi định dạng bị nhân bản

Sau nhiều lần copy dòng, một sheet có thể tích tụ hàng trăm quy tắc trùng nhau, làm file nặng và cuộn giật. Cách xử lý: mở Manage Rules, xóa hết, rồi tạo lại đúng vài quy tắc với vùng áp dụng đầy đủ. Từ đó về sau, khi copy dòng hãy dùng Paste Special → Values để không tha theo định dạng.

Kết hợp cả hai: mẫu file nhập liệu chuẩn

Đây là bộ khung nên dùng cho mọi file có nhiều người cùng nhập:

Về bước cuối: mặc định mọi ô trong Excel đều ở trạng thái Locked, nhưng khóa chỉ có hiệu lực sau khi bật Protect Sheet. Vì vậy quy trình đúng là: chọn vùng cho phép nhập → Ctrl + 1 → tab Protection → bỏ tick Locked → sau đó mới Review → Protect Sheet.

Lưu ý về mức bảo vệ: Protect Sheet chống nhầm lẫn, không chống người cố tình. Đừng dùng nó để giấu dữ liệu nhạy cảm.

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

1. Biểu mẫu đăng ký sự kiện. Tạo file có các cột Họ tên, Email, Lớp, Suất ăn, Ngày đăng ký. Thêm dropdown cho Lớp và Suất ăn, chặn email trùng bằng COUNTIF, chặn ngày đăng ký sau hạn chót.

Câu hỏi thường gặp

Vì sao dropdown biến mất sau khi copy-paste đè lên ô?

Vì Paste thay thế toàn bộ thuộc tính của ô, kể cả Data Validation. Cách phòng: hướng dẫn người dùng dán bằng Paste Special → Values, hoặc bảo vệ sheet để chỉ cho phép chọn ô nhập liệu. Sau khi bị mất, chỉ cần chọn lại vùng và áp lại quy tắc.

Data Validation có chặn được dữ liệu dán vào không?

Không. Đây là giới hạn quan trọng cần biết: Data Validation chỉ kiểm tra khi người dùng gõ. Dữ liệu dán vào sẽ đi qua mà không bị chặn. Vì vậy hãy kết hợp với Circle Invalid Data để rà soát định kỳ, hoặc thêm một cột kiểm tra bằng công thức.

Danh sách xổ xuống quá dài, người dùng khó tìm?

Trên Microsoft 365 bản mới, dropdown hỗ trợ gõ để lọc. Trên bản cũ hơn, cách thực dụng là sắp xếp danh sách theo bảng chữ cái và tách thành hai cấp (danh sách phụ thuộc) để mỗi lần chỉ hiện vài chục mục.

Conditional Formatting làm file chậm phải không?

Có, nếu quy tắc quá nhiều hoặc dùng các hàm biến động (OFFSET, INDIRECT, TODAY) trên vùng lớn. Ba cách giảm: giới hạn vùng áp dụng đúng bằng vùng dữ liệu thật (đừng áp cho cả cột), gộp các quy tắc trùng lặp, và tránh INDIRECT khi có cách khác.

Làm sao copy định dạng có điều kiện sang bảng khác?

Dùng Format Painter (chổi quét trong thẻ Home), hoặc copy một ô có sẵn quy tắc rồi Paste Special → Formats. Sau đó mở Manage Rules và kiểm tra lại vùng Applies to.

Tô màu bằng tay và Conditional Formatting khác gì nhau?

Tô tay là ảnh chụp tại một thời điểm: dữ liệu đổi thì màu vẫn đứng yên, và bạn không biết màu đó nghĩa là gì sau ba tháng. Conditional Formatting gắn với quy tắc, luôn cập nhật và tự giải thích được. Trong báo cáo dùng lại nhiều lần, tô tay là món nợ.

Nguồn tham khảo

• Apply data validation to cells – Microsoft Support
• Create a drop-down list – Microsoft Support
• Use conditional formatting to highlight information – Microsoft Support
• Protect a worksheet – Microsoft Support

Kết luận

Data Validation và Conditional Formatting là hai mặt của cùng một mục tiêu: giảm số lần con người phải chú ý.

Một file được thiết kế tốt sẽ tự chặn dữ liệu sai khi nhập, và tự làm nổi bật điều bất thường khi đọc. Người dùng không cần đọc hướng dẫn, và bạn không cần đi dò từng dòng.

Nếu bắt đầu từ một thứ: hãy học kỹ thuật tô cả dòng bằng công thức với dấu $ đúng chỗ. Nó thay đổi hoàn toàn chất lượng các bảng theo dõi của bạn.

Bài viết liên quan

• PivotTable từ A đến Z
• Làm sạch dữ liệu trong Excel
• Chọn đúng biểu đồ Excel và dựng dashboard
• 30 hàm Excel dân văn phòng dùng hằng ngày