Google Sheets Data Validation: Kiểm Soát Dữ Liệu Nhập [2027]

Trả lời nhanh: Data Validation (Xác thực dữ liệu) là tính năng của Google Sheets cho phép đặt quy tắc cho từng ô hoặc vùng: chỉ được chọn trong một danh sách, chỉ được nhập số trong khoảng cho trước, ngày phải sau ngày khác, hoặc phải khớp một công thức bạn tự viết. Quy tắc nằm ở menu Dữ liệu, Xác thực dữ liệu. Làm đúng một lần ở đầu file thì phần lớn lỗi gõ sai như "Hoàn thành" thành "hoan thanh" biến mất, và các công thức SUMIFS, COUNTIF phía sau không còn bỏ sót dòng.

Vấn đề thường thấy ở những file dùng chung: một người gõ "Đã giao", người khác gõ "da giao", người thứ ba gõ "Đã giao " có thêm dấu cách. Với mắt người đó là cùng một thứ, còn với công thức đó là ba giá trị khác nhau. Bài này đi từng loại quy tắc hay dùng, kèm công thức dán là chạy, và nói rõ chỗ Data Validation không làm được để bạn khỏi tin quá mức vào nó. Mọi số liệu ví dụ trong bài đều là giả định.

Data Validation nằm ở đâu và hoạt động thế nào

Bôi đen ô hoặc vùng cần kiểm soát, ví dụ cột C từ C2 đến C1000. Vào menu Dữ liệu, chọn Xác thực dữ liệu, rồi bấm Thêm quy tắc. Một bảng bên phải hiện ra với ba phần chính:

• Áp dụng cho phạm vi: vùng ô chịu quy tắc. Nên chọn dư xuống vài trăm dòng, hoặc dùng cả cột nếu không có gì khác nằm bên dưới.
• Tiêu chí: kiểu quy tắc. Các nhóm thường dùng gồm danh sách thả xuống, số, ngày, văn bản, hộp kiểm và công thức tùy chỉnh.
• Nếu dữ liệu không hợp lệ: chọn hiển thị cảnh báo (vẫn cho lưu) hoặc từ chối dữ liệu đầu vào (chặn luôn).

Bạn cũng có thể bật văn bản trợ giúp, một dòng chữ hiện ra khi người dùng bấm vào ô, ví dụ "Chọn trạng thái trong danh sách". Dòng hướng dẫn ngắn như vậy làm giảm số lần người khác phải hỏi bạn "ô này nhập gì".

Danh sách thả xuống: nhập tay hoặc lấy từ một vùng

Danh sách thả xuống là quy tắc được dùng nhiều nhất, vì nó loại bỏ hẳn lỗi gõ sai. Có hai cách tạo.

Cách 1: nhập thẳng các mục

Chọn tiêu chí Trình đơn thả xuống, rồi gõ từng mục vào các ô trong bảng quy tắc, ví dụ: Mới, Đang xử lý, Hoàn thành, Hủy. Mỗi mục có thể gán một màu riêng để cột trạng thái nhìn là biết ngay. Cách này nhanh và hợp với danh sách ngắn, ít đổi.

Cách 2: lấy từ một vùng ô

Chọn tiêu chí danh sách thả xuống lấy từ một phạm vi, rồi trỏ tới vùng chứa các mục. Giả sử bạn tạo sheet tên Danhmuc, gõ các trạng thái vào A2:A20, thì ô nguồn là:

Muốn danh sách tự dài ra khi bạn thêm mục mới, khai báo vùng mở như Danhmuc!A2:A thay vì chốt cứng A20. Khi cần thêm một trạng thái, bạn chỉ gõ thêm vào cuối cột A của sheet Danhmuc, mọi ô dùng danh sách đó tự cập nhật. Cách này đáng dùng cho danh sách dài hoặc dùng ở nhiều cột: sửa một chỗ, cả file đổi theo.

Nếu danh sách nằm ở một file Google Sheets khác, quy tắc không trỏ trực tiếp sang file đó được. Cách làm là nhập dữ liệu sang một sheet phụ trong file hiện tại bằng IMPORTRANGE rồi lấy phạm vi từ sheet phụ đó.

Muốn danh sách ở ô con thay đổi theo lựa chọn ở ô cha (chọn tỉnh rồi chọn huyện), cần dropdown phụ thuộc. Phần đó dài hơn một quy tắc, nên có bài riêng: Cách Tạo Dropdown List Phụ Thuộc Trong Google Sheets.

Kiểm soát số và ngày

Với cột số lượng, đơn giá hay phần trăm, hai loại lỗi hay gặp là gõ chữ vào ô số và gõ sai bậc (thừa một số 0). Chọn tiêu chí Số rồi đặt điều kiện: lớn hơn 0, hoặc nằm giữa hai giá trị. Ví dụ cột số lượng cho phép từ 1 đến 1000, cột chiết khấu cho phép từ 0 đến 0,3 (định dạng ô theo phần trăm, tức 0% đến 30%). Con số 1000 và 30% ở đây chỉ để minh họa, bạn đặt theo thực tế của mình.

Quy tắc "Số" không phân biệt số nguyên hay số lẻ. Nếu cột số lượng phải là số nguyên, dùng công thức tùy chỉnh (phần sau):

Với ngày, chọn tiêu chí Ngày: là ngày hợp lệ, trước một ngày, sau một ngày hoặc nằm giữa hai ngày. Quy tắc "là ngày hợp lệ" chặn kiểu gõ "30/02" hay "hôm qua". Muốn so với một ô khác, ví dụ ngày giao hàng không được sớm hơn ngày đặt hàng, dùng công thức tùy chỉnh. Giả sử ngày đặt ở cột D, ngày giao ở cột E, áp dụng quy tắc cho E2:E1000 với công thức:

Nếu ô D còn trống, công thức này vẫn có thể cho qua, vì ô trống được hiểu là số 0. Muốn chặt hơn thì viết =AND(D2<>"",E2>=D2): ô giao hàng chỉ nhận ngày khi ô đặt hàng đã có dữ liệu.

Công thức tùy chỉnh: chống trùng, mã, số điện thoại

Chọn tiêu chí Công thức tùy chỉnh là và nhập công thức trả về TRUE (hợp lệ) hoặc FALSE (không hợp lệ). Hai điều cần nhớ. Thứ nhất, viết công thức cho ô đầu tiên của vùng áp dụng, các tham chiếu tương đối sẽ tự dịch cho các ô còn lại như khi kéo công thức. Thứ hai, dùng dấu $ cho phần cần cố định, thường là vùng đối chiếu.

Không cho nhập trùng

Áp dụng cho cột mã đơn A2:A1000:

COUNTIF đếm xem giá trị ở A2 xuất hiện bao nhiêu lần trong cả cột. Nếu đúng một lần (chính nó) thì hợp lệ, nếu từ hai lần trở lên thì bị từ chối. Cách này chặn trùng ngay lúc gõ. Với danh sách đã có sẵn dữ liệu trùng, xem cách tô màu dữ liệu trùng lặp và xóa dữ liệu trùng lặp.

Mã có định dạng cố định

Giả sử mã đơn luôn có dạng "DH" cộng bốn chữ số, như DH0123:

Dấu ^ và $ buộc cả chuỗi phải khớp từ đầu đến cuối. Nếu thiếu hai dấu này, chuỗi "xDH0123y" cũng lọt qua vì nó có chứa một đoạn khớp.

Số điện thoại 10 chữ số bắt đầu bằng 0

Hàm TO_TEXT đổi ô thành chữ để REGEXMATCH đọc được. Điều kiện để quy tắc hợp lý là cột số điện thoại phải được định dạng văn bản thuần (Định dạng, Số, Văn bản thuần túy) trước khi nhập, nếu không Google Sheets hiểu "0912345678" là số và cắt mất số 0 ở đầu. Mẫu trên chỉ là ví dụ cho số 10 chữ số, số cố định hay số có mã vùng sẽ cần mẫu khác. Muốn chuẩn hóa dữ liệu đã nhập sẵn các kiểu khác nhau (có dấu cách, dấu chấm, +84), xem cách lọc khách hàng trùng số điện thoại.

Email và đường dẫn

Với email và URL không cần tự viết công thức. Chọn tiêu chí Văn bản rồi chọn "là email hợp lệ" hoặc "là URL hợp lệ". Quy tắc này chỉ kiểm tra hình thức (có dấu @, có tên miền), không biết địa chỉ đó có tồn tại thật hay không.

Cảnh báo hay từ chối: chọn kiểu nào

Phần "Nếu dữ liệu không hợp lệ" nghe nhỏ nhưng quyết định trải nghiệm của cả nhóm.

• Từ chối dữ liệu đầu vào: giá trị sai bị chặn, người nhập phải sửa. Hợp với cột bắt buộc đúng tuyệt đối như trạng thái, mã đơn, loại hàng, vì một giá trị lạ sẽ làm hỏng công thức phía sau.
• Hiển thị cảnh báo: giá trị vẫn được lưu, ô được đánh dấu. Hợp với cột có ngoại lệ chính đáng, ví dụ số lượng vượt mức thông thường nhưng có lý do, hoặc ghi chú tự do có gợi ý định dạng.

Một quy tắc dễ áp dụng: dùng từ chối cho những cột mà công thức hoặc báo cáo đọc trực tiếp, dùng cảnh báo cho những cột để con người đọc. Đừng bật từ chối tràn lan, vì người dùng bị chặn mà không hiểu vì sao sẽ tìm cách lách bằng cách dán dữ liệu vào, và lúc đó bạn mất cả kiểm soát lẫn thiện cảm.

Những việc Data Validation không làm được

Biết giới hạn trước thì đỡ tin sai chỗ.

• Không bắt buộc ô phải có dữ liệu. Quy tắc chỉ kiểm tra khi có người nhập. Một ô để trống vẫn hợp lệ, và xóa nội dung ô cũng không bị chặn. Muốn nhắc các ô còn thiếu, dùng định dạng có điều kiện để tô màu ô trống, ví dụ công thức =AND($A2<>"",$B2="") tô cột B khi dòng đã có mã mà chưa có tên.
• Không kiểm tra dữ liệu có sẵn. Quy tắc áp dụng cho những gì nhập từ lúc tạo. Dữ liệu cũ đã sai vẫn nằm đó. Cách rà là dùng định dạng có điều kiện với đúng công thức của quy tắc (đảo điều kiện) để tô các ô không đạt.
• Không chắc chặn được dữ liệu dán vào. Dán từ nơi khác có thể lách qua quy tắc, và dán kèm cả định dạng sẽ ghi đè quy tắc của ô đích. Sau khi dán một lượng lớn dữ liệu, hãy kiểm lại bằng cách tô màu như trên.
• Không chặn người có quyền sửa. Ai có quyền chỉnh sửa file vẫn xóa hoặc đổi được chính quy tắc. Muốn khóa, dùng Dữ liệu, Bảo vệ trang tính và dải ô cho vùng cần giữ, chỉ cho một vài người sửa.
• Không chống được thông tin sai nhưng đúng hình thức. Email đúng cú pháp vẫn có thể là email người khác, số điện thoại đủ 10 số vẫn có thể là số bịa. Quy tắc chỉ chặn lỗi gõ, không xác minh sự thật.

Ví dụ tổng hợp: bảng đơn hàng

Giả sử bạn dựng bảng đơn hàng dùng chung cho cả nhóm, hàng 1 là tiêu đề và dữ liệu bắt đầu từ hàng 2. Các cột và quy tắc có thể đặt như sau:

Trong ví dụ này số điện thoại dùng cảnh báo thay vì từ chối, vì thực tế có khách chỉ để lại số cố định hoặc số nước ngoài. Còn các cột còn lại là dữ liệu mà công thức đọc trực tiếp nên dùng từ chối. Làm xong, thử gõ bốn lỗi cố ý để kiểm tra: mã trùng, ngày giao trước ngày đặt, số lượng 2,5, và trạng thái gõ tay. Quy tắc nào không chặn thì sửa lại ngay lúc đó, đừng chờ đến khi cả nhóm đã nhập vài trăm dòng.

Một thói quen giúp quy tắc sống lâu: đặt các danh sách (trạng thái, loại hàng, nhân viên phụ trách) trong sheet Danhmuc và ghi chú ngắn ở đầu sheet đó "sửa danh sách ở đây". Khi quy trình đổi, bạn chỉ đổi ở một chỗ.

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

Data Validation trong Google Sheets nằm ở đâu?

Chọn ô hoặc vùng cần kiểm soát, vào menu Dữ liệu, chọn Xác thực dữ liệu, rồi bấm Thêm quy tắc. Tại đó bạn chọn phạm vi áp dụng, tiêu chí (danh sách thả xuống, số, ngày, văn bản, công thức tùy chỉnh) và cách xử lý khi dữ liệu không hợp lệ: hiển thị cảnh báo hoặc từ chối dữ liệu đầu vào.

Khác nhau giữa Hiển thị cảnh báo và Từ chối dữ liệu đầu vào là gì?

Hiển thị cảnh báo vẫn cho lưu giá trị sai nhưng đánh dấu ô để người dùng nhìn thấy. Từ chối dữ liệu đầu vào chặn luôn giá trị không hợp lệ khi gõ. Cột có thể có ngoại lệ hợp lý nên dùng cảnh báo, cột bắt buộc đúng như mã đơn hay trạng thái nên dùng từ chối.

Vì sao công thức tùy chỉnh trong Data Validation viết cho ô đầu tiên mà áp dụng cho cả vùng?

Công thức được viết theo ô đầu tiên của vùng áp dụng, và các tham chiếu tương đối tự dịch chuyển cho từng ô còn lại, giống khi kéo công thức. Ví dụ áp dụng cho A2:A1000 với công thức =COUNTIF($A$2:$A,A2)=1, ô A3 sẽ tự kiểm tra A3, ô A4 kiểm tra A4. Dấu $ dùng để cố định vùng đối chiếu.

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

Không chắc. Dữ liệu dán vào có thể lách qua quy tắc, và dán kèm cả định dạng sẽ ghi đè quy tắc của ô đích. Với cột quan trọng, hãy kiểm tra lại sau khi dán, thêm định dạng có điều kiện bằng cùng công thức để tô màu ô sai, và dùng Bảo vệ trang tính cho vùng không ai được sửa.