Xin chào quý anh chị và các bạn, đã bao giờ mình mở một file Excel và thấy cột “Tình trạng kho” có đủ loại biến thể: “Còn hàng”, “con hang”, “COn Hang”, “Còn”? Đó chính là hậu quả của việc để người dùng tự do gõ tay vào ô tính. Data Validation trong Excel (Xác thực dữ liệu) là tính năng âm thầm nhưng cực kỳ quyền lực của Excel, nó là Công cụ bị đánh giá thấp nhất trong Excel – nhưng cứu cả núi dữ liệu bẩn, cho phép bạn đặt ra “luật chơi” cho từng ô: chỉ được chọn trong danh sách, chỉ được nhập số trong khoảng, chỉ được nhập ngày hợp lệ… Dưới đây là 9 cách dùng Data Validation thực chiến nhất, từ cơ bản đến nâng cao, giúp cho anh chị làm chủ dữ liệu của mình và muốn người khác tuân thủ luật chơi khi nhập dữ liệu.
Mục lục.
Danh sách thả xuống (Dropdown List) cơ bản
Chấm dứt thời đại gõ tay sai chính tả – cho nhân viên chọn chứ đừng cho họ gõ
CÚ PHÁP
Data > Data Validation > Allow: List > Source: =$F$2:$F$6
CÔNG DỤNG
Đây là ứng dụng kinh điển nhất của Data Validation: giới hạn ô chỉ được chọn 1 trong các giá trị có sẵn, thay vì để người dùng tự gõ (và tự sáng tạo ra 5 cách viết khác nhau cho cùng 1 chữ). Nguồn danh sách có thể là 1 vùng ô, hoặc gõ trực tiếp các giá trị cách nhau bằng dấu phẩy.
VÍ DỤ
=$F$2:$F$6 (vung F2:F6 chua: “Den LED am tran”,”Den LED thanh”,”Den LED buld”,”Den LED day”,”Den pha LED”)
KẾT QUẢ
Ô C2 giờ chỉ hiện nút mũi tên thả xuống với đúng 5 loại đèn led trong kho – nhân viên bán hàng không thể gõ nhầm “đèn Lét” hay “đèn ledd” được nữa, dữ liệu đồng bộ tuyệt đối cho các bước lọc/thống kê sau này.
Giới hạn giá trị số trong một khoảng qui định trước
Chặn đứng những con số trên trời – đơn giá âm hay số lượng 99999 không có cửa lọt vào
CÚ PHÁP
Allow: Whole number > between > Minimum / Maximum
CÔNG DỤNG
Dùng khi ô chỉ được phép nhận số nguyên (hoặc số thập phân) nằm trong một khoảng hợp lý, ví dụ số lượng đặt hàng, đơn giá, hoặc phần trăm chiết khấu. Excel sẽ chặn ngay khi gõ số nằm ngoài khoảng và bật thông báo lỗi.
VÍ DỤ
Whole number, between: Minimum = 1, Maximum = 1000
KẾT QUẢ
Cột “Số lượng đặt hàng” chỉ chấp nhận số nguyên từ 1 đến 1000. Nếu nhân viên lỡ tay gõ -5 hoặc 150000 (chắc chắn là gõ nhầm số 0), Excel lập tức báo lỗi và không cho nhập, tránh những đơn hàng ma làm loạn báo cáo doanh số đèn LED.
Giới hạn ngày tháng hợp lệ
Không ai được giao hàng trong quá khứ cả – validation ngày tháng chặn luôn những lỗi gõ nhầm năm
CÚ PHÁP
Allow: Date > between > Start date / End date
CÔNG DỤNG
Áp dụng cho các cột ngày tháng như ngày đặt hàng, ngày giao hàng, ngày bảo hành. Thiết lập này đặc biệt hữu ích để đảm bảo ngày giao hàng luôn sau ngày đặt hàng, hoặc ngày nhập liệu không bị gõ nhầm sang năm 2016 thay vì 2026.
VÍ DỤ
Date, between: Start date = TODAY(), End date = DATE(2027,12,31)
KẾT QUẢ
Ô ngày giao hàng chỉ chấp nhận các ngày từ hôm nay trở đi đến hết 2027. Gõ nhầm ngày trong quá khứ (ví dụ do quên đổi năm) sẽ bị Excel từ chối ngay, giúp lịch giao đèn LED cho khách luôn chính xác.
Giới hạn độ dài văn bản trong excel
Mã sản phẩm phải đúng form, không thừa không thiếu một ký tự nào
CÚ PHÁP
Allow: Text length > between / equal to
CÔNG DỤNG
Dùng cho các cột cần đúng số ký tự cố định hoặc trong một khoảng, điển hình là mã sản phẩm (SKU), mã vạch, số điện thoại. Giúp bắt lỗi gõ thiếu hoặc thừa ký tự ngay khi nhập liệu thay vì phát hiện ra sau khi đã xuất hóa đơn.
VÍ DỤ
Text length, equal to: Length = 8 (ma SKU luon dung 8 ky tu, vi du LED00123 -> sai vi du 9 ky tu)
KẾT QUẢ
Cột “Mã SKU” chỉ chấp nhận chuỗi đúng 8 ký tự. Nếu nhân viên kho gõ thiếu hoặc thừa một số, Excel báo lỗi ngay lập tức thay vì để mã sản phẩm sai lọt vào hệ thống quản lý kho đèn LED.
Ngăn dữ liệu trùng lặp bằng cách kết hợp công thức COUNTIF
Hàm COUNTIF làm bảo vệ cổng, không cho hai mã đơn hàng giống hệt nhau lọt vào cùng một lúc
Data Validation không có sẵn tùy chọn “chống trùng lặp”, nhưng ta có thể tự chế bằng Custom Formula kết hợp COUNTIF: đếm xem giá trị vừa nhập đã xuất hiện bao nhiêu lần trong vùng dữ liệu, nếu lớn hơn 1 (tức đã trùng) thì Excel sẽ chặn không cho nhập.
VÍ DỤ
=COUNTIF($A$2:$A$100,A2)=1
KẾT QUẢ
Cột “Mã đơn hàng” của Đèn Led Giá Sỉ sẽ không bao giờ có 2 dòng trùng mã nữa – gõ lại một mã đơn đã tồn tại, Excel lập tức từ chối, giúp tránh tình trạng ghi nhận doanh thu 2 lần cho cùng một đơn.
Xác thực dữ liệu theo công thức tùy chỉnh (Custom Formula)
Khi 4 loại Allow có sẵn là chưa đủ đô, tự viết luật chơi bằng công thức của riêng mình
Custom Formula là lựa chọn mạnh nhất của Data Validation, vì bất kỳ công thức nào trả về TRUE/FALSE đều dùng được, cho phép kết hợp nhiều điều kiện logic cùng lúc (ví dụ vừa giới hạn khoảng số, vừa yêu cầu là số chẵn, vừa so sánh với một cột khác).
VÍ DỤ
=AND(B2>=50000,MOD(B2,1000)=0) (don gia phai tu 50.000d tro len va la boi so cua 1.000d)
KẾT QUẢ
Cột “Đơn giá” chỉ chấp nhận các số từ 50.000đ trở lên và phải tròn nghìn (ví dụ 85.000đ hợp lệ, nhưng 85.500đ bị từ chối) – giúp bộ phận niêm yết giá đèn LED luôn nhất quán theo quy tắc làm tròn giá của cửa hàng.
Danh sách thả xuống phụ thuộc (Dependent Dropdown) với INDIRECT
Chọn danh mục trước, danh sách con tự động đổi theo – không còn cảnh đèn sân vườn lẫn lộn với đèn âm trần
CÚ PHÁP
Allow: List > Source: =INDIRECT(B2)
CÔNG DỤNG
Khi có nhiều nhóm sản phẩm với danh sách con khác nhau (ví dụ chọn Danh mục = “Đèn LED âm trần” thì ô Model chỉ hiện các model thuộc nhóm đó), ta tạo các Named Range trùng tên với từng danh mục, rồi dùng INDIRECT để dropdown thứ hai tự động tham chiếu đúng named range tương ứng với lựa chọn ở dropdown thứ nhất.
VÍ DỤ
=INDIRECT(B2) (B2 dang chua gia tri la ten mot Named Range, vi du “Den_am_tran”)
KẾT QUẢ
Khi nhân viên chọn “Đèn LED âm trần” ở cột Danh mục, dropdown ở cột Model kế bên tự động chỉ hiện các model thuộc nhóm đèn âm trần (không còn lẫn model đèn pha hay đèn dây) – tiết kiệm thời gian tra cứu và tránh chọn nhầm model không tồn tại trong nhóm.
Thông báo lỗi và gợi ý nhập liệu tùy chỉnh
Thay vì để Excel hù dọa bằng thông báo lỗi khô khan, tự viết lời nhắc dễ thương và dễ hiểu hơn
CÚ PHÁP
Tab Input Message + tab Error Alert trong hộp thoại Data Validation
CÔNG DỤNG
Ngoài việc chặn dữ liệu sai, Data Validation còn cho phép hiển thị một “Input Message” (gợi ý xuất hiện ngay khi bấm vào ô, trước khi gõ) và một “Error Alert” tùy chỉnh (thông báo khi gõ sai, với 3 kiểu: Stop chặn hẳn, Warning cảnh báo nhưng vẫn cho nhập, Information chỉ nhắc nhẹ).
VÍ DỤ
Input Message: “Chon so luong tu 1 den 1000” | Error Alert (Stop): “So luong khong hop le, vui long kiem tra lai!”
KẾT QUẢ
Khi nhân viên bấm vào ô Số lượng, một chú thích nhỏ hiện ra nhắc phạm vi hợp lệ trước khi họ kịp gõ sai; nếu vẫn gõ sai, hộp thoại lỗi bằng tiếng Việt dễ hiểu hiện ra thay vì thông báo mặc định khô khan của Excel.
Khoanh tròn dữ liệu không hợp lệ (Circle Invalid Data)
Công cụ dò lỗi hàng loạt cho những bảng tính đã lỡ nhập ẩu từ trước
CÚ PHÁP
Data > Data Validation > mũi tên thả xuống > Circle Invalid Data
CÔNG DỤNG
Khác với các mục trên (ngăn lỗi khi nhập mới), tính năng này dùng để rà soát dữ liệu CŨ đã có sẵn trong bảng: sau khi thiết lập quy tắc Data Validation cho một vùng, Excel sẽ tự động khoanh tròn đỏ tất cả các ô đang vi phạm quy tắc đó, kể cả khi dữ liệu được nhập từ trước khi áp quy tắc.
VÍ DỤ
Data > Data Validation > Circle Invalid Data (khong can go cong thuc)
KẾT QUẢ
Toàn bộ các ô trong cột Đơn giá đang có giá trị âm hoặc vượt ngưỡng sẽ hiện vòng tròn đỏ ngay lập tức, giúp rà soát nhanh một bảng dữ liệu cũ gồm hàng nghìn dòng mà không cần lọc thủ công từng ô.
Lời kết cuối cùng cho Xác thực dữ liệu – Data Validation trong Excel
Chỉ với 9 chiêu Data Validation ở trên, bạn đã có thể biến một file Excel nhập liệu ẩu thành một hệ thống nhập liệu có kỷ luật, nơi người dùng chỉ có thể chọn đúng chứ khó lòng nhập sai. Mẹo nhỏ: hãy kết hợp Data Validation với định dạng có điều kiện (Conditional Formatting) để vừa chặn lỗi lúc nhập, vừa tô màu cảnh báo cho dữ liệu cũ còn sai sót.