Làm chủ Xác Thực Dữ Liệu-Data Validation Trong Excel

xac thuc du lieu Data Validation Trong Excel

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.

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ÁPData > 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.
danh sách thả xuống - Data Validation Trong Excel

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ÁPAllow: Whole number > between > Minimum / Maximum
CÔNG DỤNGDù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 giá trị ô trong excel

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ÁPAllow: 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 ngày tháng hợp lệ trong excel

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ÁPAllow: Text length > between / equal to
CÔNG DỤNGDù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.
giới hạn độ dài văn bản trong excel

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
CÚ PHÁPAllow: Custom > Formula: =COUNTIF($A$2:$A$100,A2)=1
CÔNG DỤNGData 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.
không cho trùng mã đơn hàng trong excel

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
CÚ PHÁPAllow: Custom > Formula: =AND(dieu_kien_1,dieu_kien_2,…)
CÔNG DỤNGCustom 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.
công thức tùy chỉnh trong xác thực dữ liệu

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ÁPAllow: List > Source: =INDIRECT(B2)
CÔNG DỤNGKhi 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.
danh sách phụ thuộc trong excel bằng data validation và Indirect

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ÁPTab Input Message + tab Error Alert trong hộp thoại Data Validation
CÔNG DỤNGNgoà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ÁPData > Data Validation > mũi tên thả xuống > Circle Invalid Data
CÔNG DỤNGKhá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.

Tải file Excel mẫu Data Validation đi kèm bài viết này để thực hành trực tiếp 9 kỹ thuật trên với dữ liệu đèn LED có sẵn.

Tham khảo thêm các chủ đề khác như Google Sheet và Google AppSheet trên blog để nâng cao tay nghề số hóa văn phòng của mình anh/chị nhé.

Tham khảo nguồn chính thức từ Microsoft Excel .

Để lại một bình luận

Email của bạn sẽ không được hiển thị công khai. Các trường bắt buộc được đánh dấu *