Làm sạch dữ liệu sau khi đã nhập sai luôn tốn công hơn chặn nó ngay lúc nhập. Data Validation, trong bản tiếng Việt của Excel thường gọi là Xác thực dữ liệu, cho phép bạn quy định mỗi ô chỉ nhận loại giá trị nào: số trong khoảng nào, ngày trong khoảng nào, chọn từ danh sách nào, hay phải thoả một công thức. Ai nhập trái quy định sẽ bị chặn hoặc được nhắc ngay tại chỗ.
Bài này dành cho người dựng bảng nhập liệu dùng chung: bảng nhập kho của cửa hàng nhiều ca, bảng đăng ký của lớp tập huấn, bảng theo dõi đơn hàng của nhóm bán hàng. Bạn sẽ có các loại quy tắc hay dùng, cách nhờ AI viết công thức tuỳ chỉnh, cách làm tương tự trên Google Sheets, và giới hạn của tính năng này để không đặt niềm tin sai chỗ.
Data Validation giải quyết lỗi nào và không giải quyết lỗi nào
Phần lớn lỗi trong bảng văn phòng sinh ra ở bước gõ tay: cùng một nhóm hàng có người viết "Văn phòng phẩm", người viết "VPP", người viết "van phong pham"; cột số lượng có ô ghi "5 thùng"; cột ngày có ô ghi "thứ hai tuần trước". Những lỗi này khiến hàm cộng theo điều kiện bỏ sót, bộ lọc ra kết quả thiếu, và đến cuối tháng ai đó phải ngồi sửa hàng trăm ô. Data Validation chặn đúng nhóm lỗi này: sai kiểu, sai khoảng, sai danh mục.
Nó không chặn được lỗi đúng kiểu nhưng sai sự thật. Nhân viên nhập số lượng 50 thay vì 5 vẫn lọt, vì 50 nằm trong khoảng cho phép. Nó cũng không kiểm tra dữ liệu đã có sẵn trước khi bạn đặt quy tắc; các ô cũ sai vẫn nằm đó cho đến khi bạn dùng chức năng khoanh tròn dữ liệu không hợp lệ để tìm.
Hiểu ranh giới này giúp bạn dùng đúng: Data Validation là hàng rào đầu tiên, giảm mạnh số lỗi vặt, nhưng vẫn cần những phép kiểm định kỳ như tổng kiểm, tìm trùng, tìm giá trị bất thường. Các phép kiểm đó có bài riêng trong cùng danh mục này.
Sáu loại quy tắc hay dùng nhất
Trong Excel, bạn chọn vùng ô, vào thẻ Data, chọn Data Validation, rồi chọn loại ở mục Allow. Bảng dưới liệt kê các loại có sẵn và tình huống văn phòng phù hợp.
| Loại (mục Allow) | Dùng cho | Ví dụ giả định |
|---|---|---|
| Whole number | Số lượng, số người, số buổi | Số thùng nhập kho từ 1 đến 500 |
| Decimal | Đơn giá, khối lượng, tỷ lệ | Chiết khấu từ 0 đến 0,3 |
| List | Danh mục cố định | Nhóm hàng chọn từ danh sách năm nhóm; tên ca chọn Sáng, Chiều, Tối |
| Date | Ngày nhập, ngày giao, ngày sinh | Ngày nhập kho trong năm hiện tại, không sau hôm nay |
| Text length | Mã có độ dài cố định | Mã khách đúng 7 ký tự; số điện thoại đúng 10 ký tự |
| Custom | Mọi điều kiện còn lại, viết bằng công thức | Mã không được trùng; số điện thoại phải bắt đầu bằng số không |
Với loại List, nên lấy danh sách từ một vùng ô trên trang tính riêng, ví dụ trang "Danh mục", thay vì gõ thẳng các lựa chọn cách nhau bằng dấu phẩy. Khi cần thêm nhóm hàng mới, bạn chỉ sửa một chỗ. Nếu định dạng vùng ô danh mục thành Table, danh sách thả xuống sẽ tự mở rộng khi thêm dòng, với điều kiện bạn tham chiếu đúng cách; hãy thử thêm một dòng để chắc chắn trước khi giao bảng cho người khác.
Đặt một quy tắc hoàn chỉnh trong Excel, từng bước
Lấy ví dụ cột số lượng nhập kho ở cột D, từ dòng 2 trở xuống.
- Chọn vùng D2 đến D1000, hoặc cả cột trừ ô tiêu đề. Chọn trước khi bảng có dữ liệu sẽ đỡ việc hơn chọn sau.
- Vào Data, chọn Data Validation. Ở thẻ Settings, mục Allow chọn Whole number, mục Data chọn between, nhập Minimum là 1 và Maximum là 500.
- Chuyển sang thẻ Input Message, viết một câu ngắn hiện ra khi người dùng bấm vào ô, ví dụ "Nhập số thùng, từ 1 đến 500. Hàng lẻ ghi ở cột ghi chú."
- Chuyển sang thẻ Error Alert, chọn kiểu. Stop chặn hẳn, Warning cảnh báo nhưng cho bỏ qua, Information chỉ báo. Với cột số lượng nên chọn Stop; với cột mà ngoại lệ có thể xảy ra, ví dụ đơn giá đặc biệt, chọn Warning.
- Viết thông báo lỗi nói rõ cần làm gì, không chỉ nói "sai". Ví dụ: "Số lượng phải là số nguyên từ 1 đến 500. Nếu lô hàng lớn hơn, báo quản lý kho."
- Bấm OK, rồi thử nhập vài giá trị sai: chữ, số âm, số thập phân, số 600. Mỗi trường hợp đều phải bị chặn hoặc cảnh báo đúng như bạn muốn.
- Với dữ liệu đã có sẵn, vào Data Validation, chọn Circle Invalid Data để Excel khoanh tròn các ô cũ không đạt quy tắc mới.
Tên menu ở bản tiếng Việt có thể khác chút ít tuỳ phiên bản; nếu không thấy, tìm trong thẻ Dữ liệu biểu tượng có dấu tích xanh và dấu cấm đỏ.
Quy tắc tuỳ chỉnh bằng công thức và cách nhờ AI viết
Không cho mã trùng. Chọn vùng A2:A1000, loại Custom, công thức =COUNTIF($A$2:$A$1000,A2)=1. Công thức viết cho ô đầu vùng, Excel tự áp cho các ô còn lại; dấu đô la giữ vùng đếm cố định, còn A2 thay đổi theo từng ô.
Số điện thoại mười chữ số bắt đầu bằng số không. Cột này phải định dạng Text trước, nếu không Excel sẽ xoá số không ở đầu. Công thức gợi ý: =AND(LEN(C2)=10,LEFT(C2,1)="0",ISNUMBER(--C2)). Phần ISNUMBER(--C2) kiểm tra chuỗi chỉ gồm chữ số; nếu chưa rõ cách hoạt động, đó đúng là câu nên hỏi AI.
Ngày giao không trước ngày đặt. Với ngày đặt ở cột E và ngày giao ở cột F, chọn F2:F1000, loại Custom, công thức =F2>=E2. Kiểu quy tắc giữa hai cột này giúp bắt lỗi gõ nhầm năm rất hiệu quả.
Câu lệnh mẫu để nhờ AI. Mô tả bảng bằng tên cột và vài dòng giả định, nói rõ ô đầu vùng, rồi hỏi: "Bảng nhập kho, cột B là mã hàng dạng hai chữ in hoa rồi bốn chữ số, ví dụ VP0012. Viết công thức Custom cho Data Validation áp vào B2:B1000 để chỉ nhận đúng dạng đó, giải thích từng hàm và nói hàm nào có ở Excel 2016." Câu cuối quan trọng vì các hàm mới như REGEXTEST chỉ có ở bản Microsoft 365 gần đây, bản cũ sẽ báo lỗi.
Luôn thử bằng ví dụ sai. Công thức AI viết có thể đúng về cú pháp nhưng sai logic, ví dụ chấp nhận cả chữ thường. Tự gõ năm ví dụ sai và năm ví dụ đúng, ghi lại kết quả. Công thức viết theo dấu phẩy của Excel tiếng Anh; máy định dạng vùng Việt Nam có thể cần đổi sang dấu chấm phẩy.
Làm tương tự trên Google Sheets
Trên Google Sheets, chọn vùng, vào menu Data, chọn Data validation, rồi bấm thêm quy tắc. Các tiêu chí tương tự Excel: danh sách thả xuống, danh sách lấy từ vùng ô, số, ngày, văn bản chứa hoặc là địa chỉ email hợp lệ, và tuỳ chỉnh bằng công thức. Phần nâng cao cho phép chọn hiển thị cảnh báo hay từ chối giá trị nhập, tương đương Warning và Stop bên Excel.
Điểm mạnh của Sheets là công thức tuỳ chỉnh dùng được REGEXMATCH, nên các quy tắc về mẫu chuỗi viết gọn hơn. Ví dụ mã hàng hai chữ in hoa rồi bốn chữ số: =REGEXMATCH(B2,"^[A-Z]{2}[0-9]{4}$"). Danh sách thả xuống trong Sheets hiển thị dạng thẻ màu, giúp người nhập ca tối nhìn là biết đã chọn gì.
Với bảng nhập liệu cho nhiều người ở xa, cân nhắc dùng Google Forms đổ dữ liệu về trang tính thay vì để mọi người gõ thẳng vào bảng. Biểu mẫu kiểm tra từng câu trả lời trước khi ghi, người nhập không chạm được vào dữ liệu của người khác, và bạn giữ được bảng gốc sạch hơn. Cách dựng biểu mẫu đổ về bảng được bàn kỹ ở danh mục Google Sheets của trang.
Những chỗ Data Validation bị lọt và cách bịt
Đây là phần nhiều người không biết cho đến khi gặp sự cố:
- Dán đè xoá quy tắc. Trong Excel, khi người dùng sao chép một ô từ nơi khác rồi dán thường vào ô có quy tắc, quy tắc của ô đích bị thay bằng quy tắc của ô nguồn, tức thường là không có quy tắc nào. Cách giảm: hướng dẫn mọi người dùng dán giá trị, kết hợp khoá trang tính chỉ để mở các ô nhập liệu, và định kỳ chạy Circle Invalid Data.
- Dữ liệu nhập bằng mã hoặc công cụ khác không qua kiểm tra. Macro, Apps Script, hay việc nhập tệp CSV đều có thể ghi giá trị trái quy tắc mà không bị chặn.
- Quy tắc chỉ áp cho vùng đã chọn. Bảng dài thêm sau dòng 1000 thì từ dòng 1001 không còn kiểm tra. Đặt vùng rộng hơn nhu cầu, hoặc dùng Table để quy tắc theo bảng.
- Kiểu Warning có thể bị bỏ qua bằng một cú bấm. Nếu thấy cột nào thường xuyên có ngoại lệ được bấm cho qua, xem lại quy tắc thay vì để người nhập tập thói quen bấm Yes.
- Không bảo vệ được dữ liệu khỏi người có ý xấu. Ai có quyền sửa tệp đều có thể xoá quy tắc. Data Validation là công cụ chống nhầm, không phải công cụ bảo mật; phần phân quyền và khoá trang tính có bài riêng.
Gắn Data Validation vào quy trình chất lượng dữ liệu
Một bảng nhập liệu tốt thường có ba lớp. Lớp đầu là Data Validation và danh sách thả xuống, chặn lỗi vặt ngay khi gõ. Lớp thứ hai là các cột kiểm tra bằng công thức nằm cạnh bảng, đánh dấu những dòng đáng ngờ như số lượng lớn bất thường hay thiếu ngày giao, kết hợp định dạng có điều kiện để tô màu. Lớp thứ ba là phép kiểm định kỳ mỗi tuần hoặc mỗi tháng: đếm dòng, tổng kiểm, tìm trùng.
AI hữu ích ở cả ba lớp, nhưng theo cùng một cách: nó viết công thức và giải thích, bạn áp dụng và thử. Bạn không cần đưa dữ liệu thật vào chatbot để nhờ viết quy tắc; mô tả cấu trúc cột và vài dòng giả định là đủ. Nếu bảng chứa thông tin khách hàng hay nhân sự, cách này vừa an toàn vừa cho kết quả tốt hơn, vì AI tập trung vào logic thay vì bị phân tán bởi dữ liệu.
Cuối cùng, ghi lại các quy tắc đã đặt trong một trang tính "Hướng dẫn": cột nào nhận gì, ai được sửa danh mục, gặp lỗi chặn thì làm gì. Người mới vào ca đọc trang đó hai phút là biết cách nhập, và khi bạn nghỉ phép, bảng vẫn giữ được kỷ luật.
Câu hỏi thường gặp
Data Validation và định dạng có điều kiện khác nhau thế nào?
Data Validation kiểm tra lúc nhập và có thể chặn giá trị sai. Định dạng có điều kiện chỉ tô màu ô theo điều kiện, không chặn gì. Hai tính năng bổ sung cho nhau: một cái phòng lỗi khi gõ, một cái giúp nhìn thấy lỗi đã có trong bảng.
Vì sao danh sách thả xuống không hiện mũi tên?
Thường do bỏ chọn mục In-cell dropdown trong hộp thoại Data Validation, hoặc vùng danh sách nguồn bị trống, bị xoá. Kiểm lại vùng nguồn, đảm bảo nó tham chiếu đúng trang tính và không có ô lỗi. Trên Google Sheets, kiểm lại kiểu hiển thị của quy tắc.
Có làm được danh sách phụ thuộc, chọn nhóm hàng rồi mới hiện mặt hàng thuộc nhóm đó không?
Có. Excel bản mới làm gọn bằng hàm FILTER làm nguồn cho danh sách, bản cũ dùng tên vùng kết hợp hàm INDIRECT. Đây là việc phù hợp để nhờ AI viết công thức, kèm mô tả cấu trúc trang danh mục và phiên bản Excel bạn đang dùng.
Đặt quy tắc rồi mà người khác vẫn nhập được giá trị sai, vì sao?
Phổ biến nhất là dán đè từ nơi khác làm mất quy tắc, kiểu cảnh báo đặt là Warning nên bị bỏ qua, hoặc ô nằm ngoài vùng đã áp quy tắc. Chạy Circle Invalid Data để tìm ô sai, kiểm lại vùng áp dụng và cân nhắc khoá trang tính.
Có nên nhờ AI đọc cả bảng rồi tự đề xuất quy tắc kiểm tra không?
Không cần đưa cả bảng. Bạn chỉ cần mô tả tên cột, kiểu dữ liệu mong muốn và vài dòng giả định, rồi nhờ AI đề xuất quy tắc cho từng cột. Cách này tránh đưa dữ liệu khách hàng ra ngoài và cho ra quy tắc rõ ràng hơn.
Data Validation có làm tệp Excel chạy chậm không?
Với bảng văn phòng thông thường hầu như không đáng kể. Quy tắc tuỳ chỉnh dùng hàm đếm trên vùng rất lớn, như COUNTIF trên hàng chục nghìn dòng, có thể làm chậm khi nhập. Khi đó thu hẹp vùng đếm vừa đủ dùng.