Hướng dẫn tạo các quy tắc Xác thực dữ liệu (Data validation) tùy chỉnh trong Excel và khắc phục một số lỗi thường gặp

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.

1. Chọn một hoặc nhiều ô để xác thực.
2. 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).
3. 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.
4. 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.

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) <=1

Ví dụ: để đảm bảo rằng chỉ các id số thứ tự duy nhất được nhập vào các ô từ A2 đến A6, hãy tạo quy tắc tùy chỉnh với công thức xác thực dữ liệu sau:

= COUNTIF($A$2:$A$6, A2) <=1

Khi một giá trị duy nhất được nhập, công thức trả về TRUE và quá trình xác thực thành công. Nếu giá trị tương tự đã tồn tại trong phạm vi được chỉ định (số lượng lớn hơn 1), COUNTIF trả về FALSE và đầu vào không xác thực được.

Lưu ý

• chúng ta khóa phạm vi bằng tham chiếu ô tuyệt đối (A $ 2: $ A $ 6) và sử dụng tham chiếu tương đối cho ô trên cùng (A2) để có công thức điều chỉnh đúng cho từng ô trong phạm vi đã xác thực.

Hãy cẩn thận khi chọn tham chiếu cho công thức, bởi nếu tham chiếu đối tượng không có trong vùng tham chiếu thì kết quả sẽ báo lỗi #REF. Lúc này chúng ta phải tốn thời gian dò lại công thức để sửa lỗi #REF!.

• Công thức này không phân biệt chữ hoa và chữ thường.

Hướng dẫn tạo công thức xác thực tùy chỉnh cho ngày và giờ

Công cụ xác thực ngày tháng có sẵn trong Excel cung cấp khá nhiều tiêu chí được xác định trước để hạn chế người dùng chỉ nhập các ngày giữa hai ngày bạn chỉ định, lớn hơn, nhỏ hơn hoặc bằng một ngày nhất định.

Nếu bạn muốn kiểm soát nhiều hơn việc xác thực dữ liệu trong trang tính của mình, bạn có thể sao chép chức năng có sẵn với quy tắc tùy chỉnh hoặc viết công thức của riêng bạn để mở rộng hơn nữa các tính năng hiện có.

Chỉ cho phép nhập ngày trong khoản thời gian xác định

Để giới hạn mục nhập vào một ngày trong một phạm vi được chỉ định, bạn có thể sử dụng quy tắc Ngày được xác định trước với tiêu chí "Between" hoặc tạo quy tắc xác thực tùy chỉnh với công thức bên dưới:

= AND(cell >= start_date), cell <= end_date)

Trong đó

• Cell là ô trên cùng trong dải ô đã được xác thực và
• ngày bắt đầu và ngày kết thúc là ngày hợp lệ được cung cấp thông qua hàm DATE hoặc tham chiếu đến các ô chứa ngày.

Ví dụ: để chỉ cho phép các ngày trong tháng 7 của năm 2017, hãy sử dụng công thức sau:

=AND (C2 >= DATE(2017,7,1), C2 <= DATE(2017,7,31))

Hoặc, nhập ngày bắt đầu và ngày kết thúc vào một số ô (ví dụ như F1 và F2) và tham chiếu các ô đó trong công thức của bạn:

=AND(C2 >= $F$1, C2 <= $F$2)

Chỉ cho phép nhập các ngày trong tuần hoặc cuối tuần

Để hạn chế người dùng chỉ được phép nhập các ngày trong tuần hoặc cuối tuần, hãy định cấu hình quy tắc xác thực tùy chỉnh dựa trên hàm WEEKDAY .

Với đối số return_type được đặt thành 2, WEEKDAY trả về một số nguyên trong khoảng từ 1 (Thứ Hai) đến 7 (Chủ Nhật). Vì vậy, đối với các ngày trong tuần (Thứ Hai đến Thứ Sáu), kết quả của công thức phải nhỏ hơn 6 và đối với các ngày cuối tuần (Thứ Bảy và Chủ Nhật) lớn hơn 5.

Chỉ cho phép nhập ngày làm việc :

= WEEKDAY ( ô , 2) <6

Chỉ cho phép các ngày cuối tuần :

= WEEKDAY ( ô , 2)> 5

Ví dụ: để chỉ cho phép nhập ngày làm việc trong các ô C2: C6, hãy sử dụng công thức sau:

=WEEKDAY(C2,2)<6

Chỉ cho phép nhập ngày trong quá khứ, hoặc ngày trong tương lai

Trong nhiều trường hợp, bạn có thể muốn sử dụng ngày hôm nay làm mốc bắt đầu của phạm vi ngày được phép, để người dùng chỉ có thể nhập ngày tương lai, hoặc ngày trong quá khứ.

Để lấy ngày hiện tại, hãy sử dụng hàm TODAY, sau đó thêm số ngày mong muốn vào đó để tính ngày kết thúc.

Ví dụ: để giới hạn mục nhập dữ liệu trong 6 ngày kể từ bây giờ (7 ngày kể cả hôm nay), chúng ta sẽ sử dụng quy tắc Ngày tích hợp với tiêu chí dựa trên công thức:

1. Chọn Date trong Allow
2. Chọn Between trong Data
3. Trong hộp Start date , hãy nhập =TODAY()
4. Trong hộp End date, hãy nhập=TODAY() + 6

Theo cách tương tự, bạn có thể hạn chế người dùng nhập ngày trước hoặc sau ngày hôm nay. Để thực hiện, bạn hãy chọn less than hoặc greater than trong hộp Dữ liệu, rồi nhập công thức =TODAY() tương ứng cho ngày kết thúc hoặc ngày bắt đầu .

Xác thực thời gian dựa trên thời gian hiện tại

Để xác thực dữ liệu dựa trên thời gian hiện tại, hãy sử dụng quy tắc Thời gian được xác định trước với công thức xác thực dữ liệu của riêng bạn:

1. Trong hộp Allow box, chọn Time.
2. Trong hộp Dữ liệu , hãy chọn nhỏ hơn để chỉ cho phép thời gian trước thời điểm hiện tại hoặc lớn hơn để cho phép thời gian sau thời điểm hiện tại.
3. Trong hộp Thời gian kết thúc hoặc Thời gian bắt đầu (tùy thuộc vào tiêu chí bạn đã chọn ở bước trước), hãy nhập một trong các công thức sau.

Để xác thực ngày và giờ dựa trên ngày và giờ hiện tại:

= NOW()

Để xác thực thời gian dựa trên thời gian hiện tại:

= TIME( HOUR(NOW()), MINUTE(NOW()), SECOND(NOW()))

Ảnh chụp màn hình bên dưới hiển thị quy tắc chỉ cho phép lớn hơn thời gian hiện tại.

Cách khắc phục một số lỗi xác thực dữ liệu thường gặp trong Excel

Nếu quy tắc xác thực dữ liệu dựa trên công thức của bạn không hoạt động như mong đợi, có 3 điểm chính cần kiểm tra:

• Công thức xác thực dữ liệu có chính xác hay không
• Công thức xác thực có tham chiếu đến ô không chứa giá trị hay không
• Các tham chiếu có được sử dụng chính xác không

1. Kiểm tra công thức xác thực dữ liệu Excel của bạn xem đã chính xác chưa.

Đối với người mới bắt đầu, hãy sao chép công thức xác thực của bạn vào một ô, để đảm bảo rằng công thức đó không trả về lỗi như # N / A, #VALUE hoặc # DIV / 0 !.

Nếu bạn đang tạo quy tắc tùy chỉnh, công thức phải trả về các giá trị logic là TRUE và FALSE hoặc các giá trị 1 và 0 tương ứng với chúng.

Nếu bạn sử dụng tiêu chí dựa trên công thức trong quy tắc tích hợp (giống như chúng tôi đã làm để xác thực thời gian dựa trên thời gian hiện tại ), công thức đó cũng có thể trả về một giá trị số khác.

Trong nhiều trường hợp, nếu bạn chọn hộp Bỏ qua trống khi xác định quy tắc (thường được chọn theo mặc định) và một hoặc nhiều ô được tham chiếu trong công thức của bạn bị trống, bất kỳ giá trị nào sẽ được phép trong ô đã xác thực.

Đây là một ví dụ ở dạng đơn giản nhất:

2. Tham chiếu ô tuyệt đối và tương đối trong công thức xác thực dữ liệu

Khi thiết lập quy tắc xác thực Excel dựa trên công thức, hãy nhớ rằng tất cả các tham chiếu ô trong công thức của bạn đều có liên quan đến ô phía trên bên trái trong phạm vi đã chọn.

Nếu bạn đang tạo quy tắc cho nhiều ô và tiêu chí xác thực của bạn phụ thuộc vào các ô cụ thể, hãy đảm bảo sử dụng tham chiếu ô tuyệt đối (với dấu $ như $ A $ 1), nếu không quy tắc của bạn sẽ chỉ hoạt động chính xác cho ô đầu tiên. Để minh họa rõ hơn điểm này, vui lòng xem xét ví dụ sau.

Giả sử, bạn muốn giới hạn mục nhập dữ liệu trong ô D2 đến D5 thành các số nguyên giữa 1 (giá trị nhỏ nhất) và kết quả của phép chia A2 cho B2. Vì vậy, bạn tính giá trị lớn nhất với công thức đơn giản này =A2/B2, như được hiển thị trong ảnh chụp màn hình bên dưới:

Vấn đề là công thức có vẻ đúng này sẽ không hoạt động cho các ô D3 đến D5 vì các tham chiếu tương đối thay đổi dựa trên vị trí tương đối của các hàng và cột. Vì vậy, đối với ô D3, công thức sẽ thay đổi thành =A3/B3, và đối với D4, nó sẽ trở thành =A4/B4, việc xác thực dữ liệu đều sai!

Để khắc phục lỗi này, ban chỉ cần gõ "$" các sổ cột và hàng để cố định chúng: =$A$2/$B$2. Hoặc, nhấn F4 để chuyển đổi giữa các loại tham chiếu khác nhau.

Trong trường hợp bạn muốn xác thực từng ô dựa trên tiêu chí riêng của từng ô, hãy sử dụng tham chiếu ô tương đối không có dấu $ để nhận công thức điều chỉnh cho từng hàng hoặc / và cột:

Như bạn thấy, không có "công thức đúng tuyệt đối", cùng một công thức có thể đúng hoặc sai tùy thuộc vào tình huống và nhiệm vụ cụ thể của bạn.

Đây là cách sử dụng xác thực dữ liệu trong Excel với các công thức của tùy chỉnh để tạo quy tắc xác thực riêng cho bạn. Bên cạnh đó, để không bỏ lỡ những mẹo và thủ thuật tin học văn phòng hữu ích khác, hãy tham gia Gitiho ngay hôm nay.