Bài viết này sẽ hướng dẫn cho bạn cách tạo quy tắc Xác thực dữ liệu [Data Validation] tùy chỉnh trong Excel. Bạn sẽ tìm thấy một vài ví dụ về công thức xác thực dữ liệu Excel, hoặc chỉ cho phép nhập số hoặc văn bản trong các ô cụ thể, hay những văn bản bắt đầu bằng các ký tự cụ thể, ngăn trùng lặp,... và các khắc phục một số lỗi thường gặp khi sử dụng tính năng Data Validation.
Đây là một hướng dẫn nâng cao, với các công thức và quy tắc xác thực tùy chỉnh. Nếu bạn chưa từng sử dụng tính năng Data Validation của Excel, hoặc muốn xem lại những tính năng cơ bản nhất của nó, vui lòng tham khảo các bài viết bên dưới.
Cách lựa chọn điều kiện nhập dữ liệu trong Data Validation trong Excel cơ bản
Cách tạo quy tắc xác thực tùy chỉnh dựa trên công thức
Microsoft Excel có một số quy tắc xác thực dữ liệu tích hợp cho các số, ngày tháng và văn bản, nhưng chúng chỉ bao gồm các tình huống cơ bản nhất. Nếu bạn muốn xác thực các ô với tiêu chí của riêng mình, hãy tạo quy tắc xác thực tùy chỉnh dựa trên công thức.
Dưới đây là các bước thực hiện.
- Chọn một hoặc nhiều ô để xác thực.
- Mở hộp thoại Xác thực Dữ liệu. Đối với điều này, hãy nhấp vào nút Xác thực Dữ liệu trên tab Dữ liệu , trong nhóm Công cụ Dữ liệu hoặc nhấn tổ hợp phím Alt> D> L [mỗi phím cần được nhấn riêng biệt].
- Trên tab Cài đặt của cửa sổ Data Validation, hãy chọn Custom trong hộp Allow box và nhập công thức xác thực dữ liệu của bạn vào hộp Formula box.
- Bấm OK .
Bạn có thể thêm thông báo nhập tùy chỉnh và cảnh báo Lỗi sẽ hiển thị khi người dùng chọn ô đã xác thực hoặc nhập dữ liệu không hợp lệ tương ứng.
Lưu ý:
- Tất cả các quy tắc xác thực dữ liệu Excel, được tích hợp sẵn và tùy chỉnh, chỉ xác minh dữ liệu mới được nhập vào một ô sau khi tạo quy tắc.
- Dữ liệu được sao chép không được xác thực, cũng không phải là dữ liệu đầu vào trong ô trước khi thực hiện quy tắc.
- Để ghim các mục nhập hiện có không đáp ứng tiêu chí xác thực dữ liệu của bạn, hãy sử dụng tính năng Circle Invalid Data bên dưới mục Data Validation để tìm dữ liệu không hợp lệ đã lọt vào trang tính của bạn trước khi bạn thêm xác thực dữ liệu.
XEM NHANH BÀI VIẾT
Hướng dẫn tạo các quy tắc xác thực dữ liệu cho số và văn bản trong Excel
Chỉ cho phép nhập số trong Excel
Đáng ngạc nhiên là không có quy tắc xác thực dữ liệu Excel nào có sẵn nào phục vụ cho một tình huống rất điển hình là khi bạn cần hạn chế người dùng chỉ nhập số trong các ô cụ thể.
Nhưng bạn có thể dễ dàng thực hiện với một công thức xác thực dữ liệu tùy chỉnh dựa trên hàm ISNUMBER, như sau:
\=ISNUMBER [C2]
Trong đó C2 là ô trên cùng của dải ô bạn muốn xác thực.
Hàm ISNUMBER cho phép bất kỳ giá trị số nào trong các ô đã được xác thực, bao gồm số nguyên, số thập phân, phân số cũng như ngày và giờ, vì chúng được được xem là số trong Excel.
Chỉ cho phép nhập văn bản trong Excel
Nếu muốn làm điều ngược lại - để chỉ cho phép các mục nhập văn bản trong phạm vi ô đã cho, hãy xây dựng quy tắc tùy chỉnh với hàm ISTEXT, ví dụ:
\=ISTEXT [D2]
Trong đó D2 là ô trên cùng của dải ô đã chọn.
Cho phép văn bản bắt đầu bằng [các] ký tự cụ thể
Nếu tất cả các giá trị trong một phạm vi nhất định phải bắt đầu bằng một ký tự hoặc chuỗi con cụ thể, bạn hãy tạo quy tức xác thực dữ liệu Excel tùy chỉnh dựa trên hàm COUNTIF với một ký tự đại diện:
\=COUNTIF [cell,"text*"]
Ví dụ: để đảm bảo rằng tất cả id đơn hàng trong cột A đều bắt đầu bằng tiền tố "AA-", "aa-", "Aa-" hoặc "aA-" [không phân biệt chữ hoa chữ thường], hãy xác định quy tắc tùy chỉnh với công thức này trong data validation
\=COUNTIF[A2,"aa-*"]
Xác thực nhiều tiêu chí với Công thức Data validation tùy chỉnh
Trong trường hợp có 2 hoặc nhiều tiền tố hợp lệ, hãy thêm một số hàm COUNTIF để quy tắc xác thực dữ liệu Excel của bạn hoạt động với logic OR:
\=COUNTIF[A2,"aa-*"] + COUNTIF[A2,"bb-*"]
Công thức xác thực có phân biệt chữ hoa chữ thường
Trong những trường hợp cần phân biệt chữ hoa, chữ thường, Bạn hãy sử dụng hàm EXACT kết hợp với hàm LEFT để tạo công thức xác thực phân biệt chữ hoa chữ thường cho các mục bắt đầu bằng văn bản cụ thể:
\= EXACT [LEFT [ cell , number_of_chars ], text ]
Ví dụ: để chỉ cho phép những mục bắt đầu bằng "AA-" [không cho phép "aa-" và "Aa-"], hãy sử dụng công thức này:
\= EXACT [LEFT [A2,3],"AA-"]
Trong công thức trên, hàm LEFT trích xuất 3 ký tự đầu tiên từ ô A2 và EXACT thực hiện so sánh phân biệt chữ hoa chữ thường với chuỗi con được viết hoa ["AA-" trong ví dụ này]. Nếu hai chuỗi con khớp chính xác, công thức trả về TRUE và quá trình xác nhận được chuyển sang; nếu không thì trả về FALSE và việc xác thực không thành công.
Chỉ cho phép nhập nội dung có bao gồm những ký tự, chữ cho trước
Để cho phép các mục nhập chứa văn bản cụ thể ở bất kỳ vị trí nào trong ô [ở đầu, giữa hoặc cuối], hãy sử dụng hàm ISNUMBER kết hợp với FIND hoặc SEARCH tùy thuộc vào việc bạn muốn đối sánh phân biệt chữ hoa chữ thường hay không phân biệt chữ hoa chữ thường:
Xác thực không phân biệt chữ hoa chữ thường:
ISNUMBER [SEARCH [text, cell]]
Xác thực phân biệt chữ hoa chữ thường:
ISNUMBER[ FIND [text, cell]]
Trên tập dữ liệu mẫu của chúng ta, để chỉ cho phép các mục nhập chứa văn bản "AA" trong các ô A2: A6, hãy sử dụng một trong các công thức sau:
Trường hợp không phân biệt chữ hoa chữ thường:
\= ISNUMBER[ SEARCH ["AA", A2]]
Trường hợp có phân biệt chữ hoa chữ thường.
\= ISNUMBER [FIND ["AA", A2]]
Các công thức trên hoạt động với logic sau:
- Bạn tìm kiếm chuỗi con "AA" trong ô A2 bằng cách sử dụng FIND hoặc SEARCH và cả hai đều trả về vị trí của ký tự đầu tiên trong chuỗi con.
- Nếu văn bản không được tìm thấy, một lỗi sẽ được trả về.
- Đối với bất kỳ giá trị số nào được trả về là kết quả của tìm kiếm, hàm ISNUMBER cho kết quả là TRUE và xác thực dữ liệu thành công.
- Trong trường hợp có lỗi, ISNUMBER trả về FALSE và mục nhập sẽ không được phép trong một ô.
Chỉ cho phép nhập các giá trị duy nhất, không trùng lập trong Excel
Trong các tình huống khi một cột hoặc một dải ô nhất định không được chứa bất kỳ giá trị trùng lập nào, hãy định cấu hình quy tắc xác thực dữ liệu tùy chỉnh để chỉ cho phép nhập các giá trị duy nhất. Để thực hiện, chúng ta sẽ sử dụng công thức COUNTIF cổ điển để xác định các bản trùng lập:
\= COUNTIF[range, topmost_cell]