Xác thực dữ liệu Excel: thêm, sử dụng, sao chép và xóa xác thực dữ liệu trong Excel

Trong excel, Data Validation là một tính năng hữu ích mà bạn có thể sử dụng để giới hạn những gì người dùng có thể nhập vào một ô. Ví dụ: tính năng xác thực dữ liệu có thể giúp bạn giới hạn độ dài của chuỗi văn bản hoặc văn bản bắt đầu / kết thúc bằng các ký tự cụ thể hoặc các giá trị duy nhất được nhập, v.v.

Hướng dẫn này, chúng tôi sẽ nói về cách thêm, sử dụng và xóa xác thực dữ liệu trong Excel, một số thao tác cơ bản và nâng cao của tính năng này cũng sẽ được trình bày chi tiết.

---

1. Xác thực dữ liệu trong Excel là gì?

Sản phẩm Xác nhận dữ liệu tính năng này có thể giúp bạn hạn chế nội dung đầu vào trong trang tính của mình. Thông thường, bạn có thể tạo một số quy tắc xác thực để ngăn chặn hoặc chỉ cho phép một số loại dữ liệu được nhập vào danh sách các ô đã chọn.

Một số cách sử dụng cơ bản của tính năng Xác thực dữ liệu:

1. Giá trị bất kỳ: không xác thực được thực hiện, bạn có thể nhập bất kỳ thứ gì vào các ô được chỉ định. 2. Toàn bộ giá trị: chỉ cho phép các số nguyên. 3. Số thập phân: cho phép nhập số nguyên cũng như số thập phân. 4. Danh sách: chỉ các giá trị từ danh sách xác định trước mới được phép nhập hoặc chọn. Các giá trị được hiển thị trong danh sách thả xuống. 5. Ngày: chỉ cho phép ngày. 6. thời gian: chỉ thời gian được phép. 7. Độ dài văn bản: chỉ cho phép nhập độ dài xác định của văn bản. 8. Tùy chỉnh: tạo quy tắc công thức tùy chỉnh để xác thực đầu vào của người dùng.

---

2. Làm thế nào để thêm xác thực dữ liệu trong Excel?

Trong trang tính Excel, bạn có thể thêm xác thực dữ liệu bằng các bước sau:

1. Chọn danh sách các ô mà bạn muốn đặt xác thực dữ liệu, sau đó bấm Ngày > Xác nhận dữ liệu > Xác nhận dữ liệu, xem ảnh chụp màn hình:

2. Trong Xác nhận dữ liệu hộp thoại, bên dưới Cài đặt , vui lòng tạo quy tắc xác nhận của riêng bạn. trong hộp tiêu chí, bạn có thể cung cấp bất kỳ loại nào sau đây:

• Các giá trị: Nhập trực tiếp số vào các ô tiêu chí;
• Tham chiếu ô: Tham chiếu một ô trong trang tính hoặc một trang tính khác;
• Công thức: Tạo các công thức phức tạp hơn dưới dạng điều kiện.

Ví dụ: tôi sẽ tạo một quy tắc chỉ cho phép nhập các số nguyên từ 100 đến 1000, ở đây đặt tiêu chí như hình minh họa bên dưới:

3. Sau khi định cấu hình các điều kiện, bạn có thể đi tới Đầu vào tin nhắn or Cảnh báo lỗi để đặt thông báo đầu vào hoặc cảnh báo lỗi cho các ô xác thực như bạn muốn. (Nếu bạn không muốn đặt cảnh báo, vui lòng nhấp vào OK để kết thúc trực tiếp.)

3.1) Thêm thông báo đầu vào (tùy chọn):

Bạn có thể tạo một thông báo xuất hiện khi chọn một ô chứa xác thực dữ liệu. Thông báo này giúp nhắc nhở người dùng những gì họ có thể nhập vào ô.

Tới Đầu vào tin nhắn và thực hiện như sau:

Kiểm tra Hiển thị thông báo đầu vào khi ô được chọn Tùy chọn; Nhập tiêu đề và thông báo nhắc nhở mà bạn muốn vào các trường tương ứng; Nhấp chuột OK để đóng hộp thoại này.

Bây giờ, khi bạn chọn một ô đã được xác thực, một hộp thông báo sẽ được hiển thị như sau:

3.2) Tạo thông báo lỗi có ý nghĩa (tùy chọn):

Ngoài việc tạo thông báo đầu vào, bạn cũng có thể hiển thị cảnh báo lỗi khi dữ liệu không hợp lệ được nhập vào ô có xác thực dữ liệu.

Tới Cảnh báo lỗi tab của Xác nhận dữ liệu hộp thoại, vui lòng làm như sau:

Kiểm tra Hiển thị cảnh báo lỗi sau khi nhập dữ liệu không hợp lệ Tùy chọn; Trong tạp chí Phong cách danh sách thả xuống, chọn một loại cảnh báo mong muốn mà bạn cần: Dừng (mặc định): Loại cảnh báo này ngăn người dùng nhập dữ liệu không hợp lệ. Cảnh báo: Cảnh báo người dùng rằng dữ liệu không hợp lệ, nhưng không ngăn cản việc nhập dữ liệu đó. Thông tin: Chỉ thông báo cho người dùng về một mục nhập dữ liệu không hợp lệ. Nhập tiêu đề và thông báo cảnh báo mà bạn muốn vào các trường tương ứng; Nhấp chuột OK để đóng hộp thoại.

Và bây giờ, khi nhập một giá trị không hợp lệ, hộp cảnh báo thông báo sẽ xuất hiện như hình ảnh chụp màn hình bên dưới:

Dừng tùy chọn: Bạn có thể nhấp vào Thử lại để nhập một giá trị khác hoặc Hủy bỏ để xóa mục nhập.

Cảnh báo tùy chọn: Nhấp vào Có nhập mục nhập không hợp lệ, Không để sửa đổi nó, hoặc Hủy bỏ để xóa mục nhập.

Thông tin tùy chọn: Nhấp vào OK nhập mục nhập không hợp lệ hoặc Hủy bỏ để xóa mục nhập.

Chú thích: Nếu bạn không đặt thông báo tùy chỉnh của riêng mình trong Cảnh báo lỗi hộp, một mặc định Dừng hộp nhắc nhở cảnh báo sẽ được hiển thị như hình dưới đây:

---

3. Các ví dụ cơ bản để xác thực dữ liệu

Khi sử dụng tính năng Xác thực dữ liệu này, có 8 tùy chọn tích hợp được cung cấp cho bạn để thiết lập xác thực dữ liệu. Chẳng hạn như: bất kỳ giá trị nào, số nguyên và số thập phân, ngày và giờ, danh sách, độ dài văn bản và công thức tùy chỉnh. Trong phần này chúng ta sẽ thảo luận về cách sử dụng một số tùy chọn có sẵn trong Excel?

3.1 Xác thực dữ liệu cho số nguyên và số thập phân

1. Chọn danh sách các ô mà bạn muốn chỉ cho phép số nguyên hoặc số thập phân, sau đó bấm Ngày > Xác nhận dữ liệu > Xác nhận dữ liệu.

2. Trong Xác nhận dữ liệu hộp thoại, bên dưới Cài đặt , hãy thực hiện các thao tác sau:

• Chọn mục tương ứng Số nguyên or Số Thập Phân trong Cho phép thả cái hộp xuống.
• Và sau đó, chọn một trong những tiêu chí bạn cần trong Ngày (Trong ví dụ này, tôi chọn giữa Tùy chọn).
• Lời khuyên: Các tiêu chí chứa: giữa, không giữa, bằng, không bằng, lớn hơn, nhỏ hơn, lớn hơn hoặc bằng, nhỏ hơn hoặc bằng.
• Tiếp theo, nhập Tối thiểu và tối đa giá trị bạn cần (tôi muốn các số từ 0 đến 1 00).
• Cuối cùng, hãy nhấp vào OK .

3. Bây giờ, chỉ các số nguyên từ 0 đến 100 mới được phép nhập vào các ô đã chọn của bạn.

---

3.2 Xác thực dữ liệu cho ngày và giờ

Để xác thực ngày hoặc giờ cụ thể được nhập, thật dễ dàng bằng cách sử dụng Xác nhận dữ liệu, vui lòng làm như sau:

1. Chọn danh sách các ô mà bạn muốn chỉ cho phép các ngày hoặc giờ cụ thể, sau đó bấm Ngày > Xác nhận dữ liệu > Xác nhận dữ liệu.

2. Trong Xác nhận dữ liệu hộp thoại, bên dưới Cài đặt , hãy thực hiện các thao tác sau:

• Chọn mục tương ứng Ngày or Thời gian trong Cho phép thả cái hộp xuống.
• Và sau đó, chọn một trong những tiêu chí bạn cần trong Ngày hộp (Ở đây tôi chọn lớn hơn Tùy chọn).
• Lời khuyên: Các tiêu chí chứa: giữa, không giữa, bằng, không bằng, lớn hơn, nhỏ hơn, lớn hơn hoặc bằng, nhỏ hơn hoặc bằng.
• Tiếp theo, nhập Ngày bắt đầu bạn cần (tôi muốn những ngày lớn hơn 8/20/2021).
• Cuối cùng, hãy nhấp vào OK .

3. Bây giờ, chỉ những ngày lớn hơn 8/20/2021 mới được phép nhập vào các ô đã chọn của bạn.

---

3.3 Xác thực dữ liệu cho độ dài văn bản

Nếu bạn cần giới hạn số lượng ký tự có thể được nhập vào một ô. Ví dụ: để giới hạn nội dung không quá 10 ký tự cho một phạm vi cụ thể, điều này Xác nhận dữ liệu cũng có thể giúp bạn một việc.

1. Chọn danh sách các ô mà bạn muốn giới hạn độ dài văn bản, sau đó bấm Ngày > Xác nhận dữ liệu > Xác nhận dữ liệu.

2. Trong Xác nhận dữ liệu hộp thoại, bên dưới Cài đặt , hãy thực hiện các thao tác sau:

• Chọn Độ dài văn bản từ Cho phép thả cái hộp xuống.
• Và sau đó, chọn một trong những tiêu chí bạn cần trong Ngày (Trong ví dụ này, tôi chọn ít hơn Tùy chọn).
• Lời khuyên: Các tiêu chí chứa: giữa, không giữa, bằng, không bằng, lớn hơn, nhỏ hơn, lớn hơn hoặc bằng, nhỏ hơn hoặc bằng.
• Tiếp theo, nhập tối đa số bạn cần giới hạn (tôi muốn độ dài văn bản không quá 10 ký tự).
• Cuối cùng, hãy nhấp vào OK .

3. Bây giờ, các ô được chọn chỉ cho phép nhập chuỗi văn bản nhỏ hơn 10 ký tự.

---

3.4 Danh sách xác thực dữ liệu (danh sách thả xuống)

Với sức mạnh này Xác nhận dữ liệu tính năng này, bạn cũng có thể tạo danh sách thả xuống trong các ô một cách nhanh chóng và dễ dàng. Vui lòng làm như sau:

1. Chọn các ô mục tiêu để chèn danh sách thả xuống, sau đó bấm Ngày > Xác nhận dữ liệu > Xác nhận dữ liệu.

2. Trong Xác nhận dữ liệu hộp thoại, bên dưới Cài đặt , hãy thực hiện các thao tác sau:

• Chọn Danh sách từ Cho phép danh sách thả xuống.
• Trong tạp chí nguồn hộp văn bản, nhập các mục danh sách được phân tách trực tiếp bằng dấu phẩy. Ví dụ: để giới hạn đầu vào của người dùng ở ba lựa chọn, hãy nhập Chưa bắt đầu, Đang tiến hành, Đã hoàn thành hoặc bạn có thể chọn danh sách các ô chứa các giá trị để chèn trình đơn thả xuống dựa trên.
• Cuối cùng, hãy nhấp vào OK .

3. Bây giờ, danh sách thả xuống đã được tạo thành các ô như ảnh chụp màn hình bên dưới:

Nhấp để biết thêm thông tin chi tiết của danh sách thả xuống…

---

4. Quy tắc tùy chỉnh nâng cao để xác thực dữ liệu

Phần này, tôi sẽ giới thiệu cách thực hiện một số quy tắc xác thực dữ liệu tùy chỉnh nâng cao để giải quyết các loại vấn đề của bạn, chẳng hạn như: tạo công thức xác thực để chỉ cho phép số hoặc chuỗi văn bản, chỉ các giá trị duy nhất, chỉ số điện thoại, địa chỉ email được chỉ định, v.v. .

4.1 Xác thực dữ liệu chỉ cho phép số hoặc văn bản

 Chỉ cho phép nhập các số với chức năng Xác thực dữ liệu

Để chỉ cho phép các số trong một phạm vi ô, vui lòng thực hiện như sau:

1. Chọn một dải ô mà bạn chỉ muốn nhập số.

2. Nhấp chuột Ngày > Xác nhận dữ liệu > Xác nhận dữ liệu, trong cửa sổ bật ra Xác nhận dữ liệu hộp thoại, bên dưới Cài đặt , vui lòng thực hiện các thao tác sau:

• Chọn Tuỳ chỉnh từ Cho phép danh sách thả xuống.
• Và sau đó, nhập công thức này: = ISNUMBER (A2) vào Công thức hộp văn bản. (A2 là ô đầu tiên của dải ô đã chọn mà bạn muốn giới hạn)
• Nhấp chuột OK để đóng hộp thoại này.

3. Từ bây giờ, chỉ có thể nhập số vào các ô đã chọn.

Chú thích: Đây THÁNG NĂM hàm 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ố, ngày và giờ.

---

 Chỉ cho phép nhập các chuỗi văn bản bằng chức năng Xác thực dữ liệu

Để hạn chế các mục nhập ô chỉ thành văn bản, bạn có thể sử dụng Xác nhận dữ liệu tính năng với một công thức tùy chỉnh dựa trên ISTEXT chức năng, vui lòng làm như sau:

1. Chọn một dải ô mà bạn chỉ muốn nhập chuỗi văn bản.

2. Nhấp chuột Ngày > Xác nhận dữ liệu > Xác nhận dữ liệu, trong cửa sổ bật ra Xác nhận dữ liệu hộp thoại, bên dưới Cài đặt , vui lòng thực hiện các thao tác sau:

• Chọn Tuỳ chỉnh từ Cho phép danh sách thả xuống.
• Và sau đó, nhập công thức này: = ISTEXT (A2) vào Công thức hộp văn bản. (A2 là ô đầu tiên của dải ô đã chọn mà bạn muốn giới hạn)
• Nhấp chuột OK để đóng hộp thoại này.

3. Bây giờ, khi nhập dữ liệu vào các ô cụ thể, chỉ có thể cho phép dữ liệu định dạng văn bản.

---

4.2 Xác thực dữ liệu chỉ cho phép các giá trị chữ và số

Đối với một số mục đích, bạn chỉ muốn cho phép nhập bảng chữ cái và giá trị số, nhưng hạn chế các ký tự đặc biệt như ~,%, $, dấu cách, v.v., phần này sẽ giới thiệu một số thủ thuật cho bạn.

 Chỉ cho phép các giá trị chữ và số với chức năng Xác thực dữ liệu

Để ngăn các ký tự đặc biệt nhưng chỉ cho phép các giá trị chữ và số, bạn nên tạo một công thức tùy chỉnh vào Xác nhận dữ liệu , vui lòng làm như sau:

1. Chọn một dải ô mà bạn chỉ muốn nhập các giá trị chữ và số.

2. Nhấp chuột Ngày > Xác nhận dữ liệu > Xác nhận dữ liệu, trong cửa sổ bật ra Xác nhận dữ liệu hộp thoại, bên dưới Cài đặt , vui lòng thực hiện các thao tác sau:

• Chọn Tuỳ chỉnh từ Cho phép danh sách thả xuống.
• Và sau đó, nhập công thức dưới đây vào Công thức hộp văn bản.
• =IF(A2="",TRUE,IF(ISERROR(SUMPRODUCT(SEARCH(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),"0123456789abcdefghijklmnopqrstuvwxyz"))),FALSE,TRUE))
• Nhấp chuột OK để đóng hộp thoại này.

Chú thích: Trong các công thức trên, A2 là ô đầu tiên của dải ô đã chọn mà bạn muốn giới hạn.

3. Bây giờ, chỉ các bảng chữ cái và giá trị số mới được phép nhập và các ký tự đặc biệt sẽ bị hạn chế khi nhập như hình minh họa bên dưới:

---

 Chỉ cho phép các giá trị chữ và số với một tính năng tuyệt vời

Có thể công thức trên là phức tạp để chúng ta hiểu và ghi nhớ, ở đây, tôi sẽ giới thiệu một tính năng tiện dụng - Ngăn chặn đánh máy of Kutools cho Excel, với tính năng này, bạn có thể nhanh chóng giải quyết công việc này một cách dễ dàng.

Sau khi cài đặt Kutools cho Excel, hãy làm như sau:

1. Chọn một dải ô mà bạn chỉ muốn nhập các giá trị chữ và số.

2. Sau đó nhấn vào Kutools > Ngăn chặn đánh máy > Ngăn chặn đánh máy, xem ảnh chụp màn hình:

3. Trong cửa sổ bật ra Ngăn chặn đánh máy hộp thoại, chọn Ngăn nhập các ký tự đặc biệt tùy chọn, xem ảnh chụp màn hình:

4. Sau đó nhấn vào Ok và trong các hộp nhắc sau, hãy nhấp vào Có > OK để kết thúc hoạt động. Bây giờ, trong các ô đã chọn, chỉ các bảng chữ cái và giá trị số mới được phép, hãy xem ảnh chụp màn hình:

---

4.3 Xác thực dữ liệu cho phép văn bản bắt đầu hoặc kết thúc 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 hoặc kết thúc bằng một ký tự hoặc chuỗi con cụ thể, bạn có thể sử dụng xác thực dữ liệu bằng công thức tùy chỉnh dựa trên hàm EXACT, LEFT, RIGHT hoặc COUNTIF.

 Cho phép văn bản bắt đầu hoặc kết thúc bằng các ký tự cụ thể chỉ với một điều kiện

Ví dụ: tôi muốn văn bản phải bắt đầu hoặc kết thúc bằng “CN” khi nhập chuỗi văn bản vào các ô cụ thể, vui lòng làm như sau:

1. Chọn một dải ô chỉ cho phép văn bản bắt đầu hoặc kết thúc bằng các ký tự nhất định.

2. Sau đó nhấn vào Ngày > Xác nhận dữ liệu > Xác nhận dữ liệu, trong cửa sổ bật ra Xác nhận dữ liệu hộp thoại, bên dưới Cài đặt , vui lòng thực hiện các thao tác sau:

• Chọn Tuỳ chỉnh từ Cho phép danh sách thả xuống.
• Và sau đó, nhập công thức dưới đây vào Công thức hộp văn bản.
• Begin with: =EXACT(LEFT(A2,2),"CN")
  End with: =EXACT(RIGHT(A2,2),"CN")
• Nhấp chuột OK để đóng hộp thoại này.

Chú thích: Trong các công thức trên, A2 là ô đầu tiên của dải ô đã chọn, số 2 là số ký tự bạn đã chỉ định, CN là văn bản bạn muốn bắt đầu hoặc kết thúc.

3. Kể từ bây giờ, chỉ chuỗi văn bản bắt đầu hoặc kết thúc bằng các ký tự được chỉ định mới có thể được nhập vào các ô đã chọn. Nếu không, một cảnh báo cảnh báo sẽ xuất hiện để nhắc nhở bạn như hình ảnh chụp màn hình bên dưới:

Chú thích: Dấu hoa thị * là một ký tự đại diện khớp với một hoặc nhiều ký tự.

---

 Cho phép văn bản bắt đầu hoặc kết thúc bằng các ký tự cụ thể với nhiều tiêu chí (HOẶC logic)

Ví dụ: nếu bạn muốn văn bản phải bắt đầu hoặc kết thúc bằng “CN” hoặc “UK” như ảnh chụp màn hình bên dưới, bạn cần thêm một phiên bản khác của EXACT bằng cách sử dụng dấu cộng (+). Vui lòng thực hiện theo các bước sau:

1. Chọn một dải ô chỉ cho phép văn bản bắt đầu hoặc kết thúc với nhiều tiêu chí.

2. Sau đó nhấn vào Ngày > Xác nhận dữ liệu > Xác nhận dữ liệu, trong cửa sổ bật ra Xác nhận dữ liệu hộp thoại, bên dưới Cài đặt , vui lòng thực hiện các thao tác sau:

• Chọn Tuỳ chỉnh từ Cho phép danh sách thả xuống.
• Và sau đó, nhập công thức dưới đây vào Công thức hộp văn bản.
• Begin with: =EXACT(LEFT(A2,2),"CN")+EXACT(LEFT(A2,2),"UK")
  End with: =EXACT(RIGHT(A2,2),"CN")+EXACT(RIGHT(A2,2),"UK")
• Nhấp chuột OK để đóng hộp thoại này.

Chú thích: Trong các công thức trên, A2 là ô đầu tiên của dải ô đã chọn, số 2 là số ký tự bạn đã chỉ định, CN và UK là các văn bản cụ thể mà bạn muốn bắt đầu hoặc kết thúc.

3. Bây giờ, chỉ chuỗi văn bản bắt đầu hoặc kết thúc bằng các ký tự được chỉ định mới có thể được nhập vào các ô đã chọn.

Chú thích: Dấu hoa thị * là một ký tự đại diện khớp với một hoặc nhiều ký tự.

---

4.4 Các mục cho phép xác thực dữ liệu phải chứa / không được chứa văn bản cụ thể

Phần này, tôi sẽ nói về cách áp dụng Data Validation để cho phép các giá trị phải chứa hoặc không được chứa một chuỗi con cụ thể hoặc một trong nhiều chuỗi con trong Excel.

 Cho phép mục nhập phải chứa một hoặc một trong nhiều văn bản cụ thể

Cho phép mục nhập phải chứa một văn bản cụ thể

Ví dụ: để cho phép các mục nhập có chứa một chuỗi văn bản cụ thể, tất cả các giá trị đã nhập phải chứa văn bản “KTE” như hình minh họa bên dưới, bạn có thể áp dụng xác thực dữ liệu bằng một công thức tùy chỉnh dựa trên các hàm FIND và ISNUMBER. Vui lòng làm như sau:

1. Chọn một phạm vi ô chỉ cho phép các văn bản có chứa văn bản nhất định.

2. Sau đó nhấn vào Ngày > Xác nhận dữ liệu > Xác nhận dữ liệu, trong cửa sổ bật ra Xác nhận dữ liệu hộp thoại, bên dưới Cài đặt , vui lòng thực hiện các thao tác sau:

• Chọn Tuỳ chỉnh từ danh sách thả xuống Cho phép.
• Và sau đó, nhập một trong các công thức dưới đây vào Công thức hộp văn bản.
• =ISNUMBER(FIND("KTE",A2))             (Case sensitive)
  =ISNUMBER(SEARCH("KTE",A2))         (Non case sensitive)
• Nhấp chuột OK để đóng hộp thoại này.

Chú thích: Trong các công thức trên, A2 là ô đầu tiên của dải ô đã chọn, văn bản KTE là chuỗi văn bản mà các mục nhập phải chứa.

3. Bây giờ, khi giá trị đã nhập không chứa văn bản được thiết kế, một hộp nhắc cảnh báo sẽ bật ra.

---

Cho phép mục nhập phải chứa một trong nhiều văn bản cụ thể

Công thức trên chỉ hoạt động với một chuỗi văn bản, nếu bạn cần cho phép bất kỳ một trong nhiều chuỗi văn bản nào trong các ô như hình minh họa sau đây, bạn nên sử dụng các hàm SUMPRODUCT, FIND và ISNUMBER cùng nhau để tạo công thức.

1. Chọn một dải ô chỉ cho phép các văn bản chứa bất kỳ một trong nhiều mục.

2. Sau đó nhấn vào Ngày > Xác nhận dữ liệu > Xác nhận dữ liệu, trong cửa sổ bật ra Xác nhận dữ liệu hộp thoại, bên dưới Cài đặt , vui lòng thực hiện các thao tác sau:

• Chọn Tuỳ chỉnh từ Cho phép danh sách thả xuống.
• Sau đó, nhập một trong các công thức dưới đây khi bạn cần vào Công thức hộp văn bản.
• =SUMPRODUCT(--ISNUMBER(FIND($C$2:$C$4,A2)))>0                        (Case sensitive)
  =SUMPRODUCT(--ISNUMBER(SEARCH($C$2:$C$4,A2)))>0                   (Non case sensitive)
• Và sau đó, nhấp vào OK để đóng hộp thoại.

Chú thích: Trong các công thức trên, A2 là ô đầu tiên của dải ô đã chọn, C2: C4 là danh sách các giá trị bạn muốn cho phép các mục nhập chứa bất kỳ giá trị nào trong số chúng.

3. Và bây giờ, chỉ có thể nhập các mục nhập chứa bất kỳ giá trị nào trong danh sách cụ thể.

---

 Các mục nhập cho phép không được chứa một hoặc một trong nhiều văn bản cụ thể

Các mục nhập cho phép không được chứa một văn bản cụ thể

Để xác thực các mục nhập không được chứa văn bản cụ thể, ví dụ: để cho phép các giá trị không được chứa văn bản “KTE” trong một ô, bạn có thể sử dụng hàm ISERROR và FIND để tạo quy tắc xác thực dữ liệu. Vui lòng làm như sau:

1. Chọn một phạm vi ô chỉ cho phép các văn bản không chứa văn bản nhất định.

2. Sau đó nhấn vào Ngày > Xác nhận dữ liệu > Xác nhận dữ liệu, trong cửa sổ bật ra Xác nhận dữ liệu hộp thoại, bên dưới Cài đặt , vui lòng thực hiện các thao tác sau:

• Chọn Tuỳ chỉnh từ Cho phép danh sách thả xuống.
• Và sau đó, nhập một trong các công thức dưới đây vào Công thức hộp văn bản.
• =ISERROR(FIND("KTE",A2))                  (Case sensitive)
  =ISERROR(SEARCH("KTE",A2))                  (Non case sensitive)
• Nhấp chuột OK để đóng hộp thoại này.

Chú thích: Trong các công thức trên, A2 là ô đầu tiên của dải ô đã chọn, văn bản KTE là chuỗi văn bản mà các mục nhập không được chứa.

3. Bây giờ, các mục nhập có chứa văn bản cụ thể sẽ bị ngăn không cho nhập.

---

Cho phép mục nhập không được chứa một trong nhiều văn bản cụ thể

Để ngăn một trong nhiều chuỗi văn bản trong danh sách được nhập như hình minh họa bên dưới, bạn nên làm như sau:

1. Chọn một dải ô mà bạn muốn ngăn một số văn bản.

2. Sau đó nhấn vào Ngày > Xác nhận dữ liệu > Xác nhận dữ liệu, trong cửa sổ bật ra Xác nhận dữ liệu hộp thoại, bên dưới Cài đặt , vui lòng thực hiện các thao tác sau:

• Chọn Tuỳ chỉnh từ Cho phép danh sách thả xuống.
• Sau đó, nhập công thức dưới đây vào Công thức hộp văn bản.
• =SUMPRODUCT(--ISNUMBER(FIND($C$2:$C$4,A2)))=0                     (Case sensitive)
  =SUMPRODUCT(--ISNUMBER(SEARCH($C$2:$C$4,A2)))=0                 (Non case sensitive)
• Và sau đó, nhấp vào OK để đóng hộp thoại.

Chú thích: Trong các công thức trên, A2 là ô đầu tiên của dải ô đã chọn, C2: C4 là danh sách các giá trị bạn muốn ngăn chặn nếu các mục nhập chứa bất kỳ giá trị nào trong số chúng.

3. Kể từ bây giờ, các mục nhập có chứa bất kỳ một trong các văn bản cụ thể sẽ bị ngăn không cho nhập.

---

4.5 Xác thực dữ liệu chỉ cho phép các giá trị duy nhất

Nếu bạn muốn ngăn dữ liệu trùng lặp được nhập vào một phạm vi ô, phần này sẽ giới thiệu một số phương pháp nhanh chóng để giải quyết công việc này trong Excel.

 Chỉ cho phép các giá trị duy nhất với chức năng Xác thực dữ liệu

Thông thường, tính năng Xác thực dữ liệu với công thức tùy chỉnh dựa trên hàm COUNTIF có thể giúp bạn, vui lòng thực hiện theo các bước sau:

1. Chọn các ô hoặc cột mà bạn chỉ muốn nhập các giá trị duy nhất.

2. Sau đó nhấn vào Ngày > Xác nhận dữ liệu > Xác nhận dữ liệu, trong cửa sổ bật ra Xác nhận dữ liệu hộp thoại, bên dưới Cài đặt , hãy thực hiện các thao tác sau:

• Chọn Tuỳ chỉnh từ Cho phép danh sách thả xuống.
• Và sau đó, nhập công thức dưới đây vào Công thức hộp văn bản.
• =COUNTIF($A$2:$A$9,A2)=1
• Nhấp chuột OK để đóng hộp thoại này.

Chú thích: Trong công thức trên, A2: A9 là phạm vi ô mà bạn muốn chỉ cho phép các giá trị duy nhất và A2 là ô đầu tiên của dải ô đã chọn.

3. Giờ đây, chỉ các giá trị duy nhất mới được phép nhập và thông báo cảnh báo sẽ bật ra khi nhập dữ liệu trùng lặp, hãy xem ảnh chụp màn hình:

---

 Chỉ cho phép các giá trị duy nhất với mã VBA

Mã VBA sau đây cũng có thể giúp bạn ngăn việc nhập các giá trị trùng lặp, vui lòng thực hiện như sau:

1. Nhấp chuột phải vào tab trang tính mà bạn muốn chỉ cho phép các giá trị duy nhất và chọn Mã Chế độ xem từ menu ngữ cảnh, trong cửa sổ bật ra Microsoft Visual Basic cho các ứng dụng cửa sổ, vui lòng sao chép và dán mã sau vào Mô-đun trống:

Mã VBA: Chỉ cho phép các giá trị duy nhất trong một dải ô:

Private Sub Worksheet_Change(ByVal Target As Range)
'Updateby Extendoffice
  Dim xRg As Range, iLong, fLong As Long
  If Not Intersect(Target, Me.[A1:A100]) Is Nothing Then
     Application.EnableEvents = False
     For Each xRg In Target
     With xRg
         If (.Value <> "") Then
          If WorksheetFunction.CountIf(Me.[A:A], .Value) > 1 Then
            iLong = .Interior.ColorIndex
            fLong = .Font.ColorIndex
            .Interior.ColorIndex = 3
            .Font.ColorIndex = 6
            MsgBox "Duplicate Entry !", vbCritical, "Kutools for Excel"
            .ClearContents
            .Interior.ColorIndex = iLong
            .Font.ColorIndex = fLong
          End If
       End If
     End With
     Next
     Application.EnableEvents = True
  End If
End Sub

Chú thích: Trong đoạn mã trên, A1: A100 và A: A là các ô trong cột mà bạn muốn ngăn trùng lặp, vui lòng thay đổi chúng theo nhu cầu của bạn.

2. Sau đó, lưu và đóng mã này, bây giờ, khi nhập giá trị trùng lặp vào ô A1: A100, một hộp nhắc nhở cảnh báo sẽ xuất hiện như hình ảnh chụp màn hình bên dưới:

---

 Chỉ cho phép các giá trị duy nhất với một tính năng tiện dụng

Nếu bạn có Kutools cho Excel, Với khả Ngăn chặn trùng lặp , bạn có thể thiết lập xác thực dữ liệu để ngăn chặn các bản sao cho một phạm vi ô chỉ với một vài cú nhấp chuột.

Sau khi cài đặt Kutools cho Excel, hãy làm như sau:

1. Chọn phạm vi ô mà bạn muốn ngăn các giá trị trùng lặp nhưng chỉ cho phép dữ liệu duy nhất.

2. Sau đó nhấn vào Kutools > Ngăn chặn đánh máy > Ngăn chặn trùng lặp, xem ảnh chụp màn hình:

3. Và một thông báo cảnh báo sẽ hiện ra để nhắc nhở bạn Xác thực dữ liệu sẽ bị xóa nếu áp dụng tính năng này, hãy nhấp vào Có và trong hộp nhắc sau, hãy nhấp vào OK, xem ảnh chụp màn hình:

4. Bây giờ, khi bạn nhập một số dữ liệu trùng lặp vào các ô đã chỉ định của mình, một hộp nhắc sẽ hiển thị để nhắc bạn rằng dữ liệu trùng lặp không hợp lệ, hãy xem ảnh chụp màn hình:

---

4.6 Xác thực dữ liệu chỉ cho phép viết hoa / viết thường / viết hoa phù hợp

Xác thực dữ liệu này là một tính năng mạnh mẽ, nó cũng có thể giúp cho phép người dùng chỉ nhập các mục nhập chữ hoa, chữ thường hoặc chữ hoa phù hợp trong một phạm vi ô. Vui lòng thực hiện theo các bước sau:

1. Chọn phạm vi ô mà bạn chỉ muốn nhập văn bản chữ hoa, chữ thường hoặc chữ hoa thường.

2. Sau đó nhấn vào Ngày > Xác nhận dữ liệu > Xác nhận dữ liệu, trong cửa sổ bật ra Xác nhận dữ liệu hộp thoại, bên dưới Cài đặt , hãy thực hiện các thao tác sau:

• Chọn Tuỳ chỉnh từ Cho phép danh sách thả xuống.
• Và sau đó, nhập một trong các công thức dưới đây mà bạn cần vào Công thức hộp văn bản.
• =AND(EXACT(A2,UPPER(A2)),ISTEXT(A2))                   (only allow uppercase text)
  =AND(EXACT(A2,LOWER(A2)),ISTEXT(A2))                 (only allow lowercase text)
  =AND(EXACT(A2,PROPER(A2)),ISTEXT(A2))               (only allow proper case text)
• Nhấp chuột OK để đóng hộp thoại này.

Chú thích: Trong công thức trên, A2 là ô đầu tiên của cột bạn muốn sử dụng.

3. Bây giờ, chỉ những mục nhập tuân theo quy tắc bạn đã tạo mới được chấp nhận.

---

4.7 Xác thực dữ liệu cho phép các giá trị tồn tại / không tồn tại trong danh sách khác

Để cho phép các giá trị tồn tại hoặc không tồn tại trong một danh sách khác được nhập vào một dải ô có thể là một vấn đề nhức nhối đối với hầu hết chúng ta. Trên thực tế, bạn có thể sử dụng tính năng xác thực dữ liệu với một công thức đơn giản dựa trên hàm COUNTIF để đối phó với nó.

Ví dụ: tôi chỉ muốn các giá trị trong phạm vi C2: C4 được nhập vào một phạm vi ô như ảnh chụp màn hình bên dưới, để giải quyết công việc này, vui lòng thực hiện như sau:

1. Chọn phạm vi ô mà bạn muốn áp dụng xác thực dữ liệu.

2. Sau đó nhấn vào Ngày > Xác nhận dữ liệu > Xác nhận dữ liệu, trong cửa sổ bật ra Xác nhận dữ liệu hộp thoại, bên dưới Cài đặt , hãy thực hiện các thao tác sau:

• Chọn Tuỳ chỉnh từ Cho phép danh sách thả xuống.
• Và sau đó, nhập một trong các công thức dưới đây mà bạn cần vào Công thức hộp văn bản.
• =COUNTIF($C$2:$C$4,A2)>0                (only allow values exist in another column)
  =COUNTIF($C$2:$C$4,A2)=0                (prevent values exist in another column)
• Nhấp chuột OK để đóng hộp thoại này.

Chú thích: Trong công thức trên, A2 là ô đầu tiên của cột bạn muốn sử dụng, C2: C4 là danh sách các giá trị bạn muốn ngăn chặn hoặc cho phép nếu các mục nhập là một trong số chúng.

3. Bây giờ, các mục nhập chỉ tuân theo quy tắc bạn đã tạo mới có thể được nhập, những mục khác sẽ bị ngăn chặn.

---

4.8 Xác thực dữ liệu chỉ buộc nhập định dạng số điện thoại

Khi bạn nhập thông tin của nhân viên công ty, một cột cần phải nhập số điện thoại, để đảm bảo nhập số điện thoại nhanh chóng và chính xác, trong trường hợp này, bạn có thể thiết lập xác thực dữ liệu cho số điện thoại. Ví dụ: tôi chỉ muốn số điện thoại ở định dạng này (123) 456-7890 được phép nhập vào trang tính, phần này sẽ giới thiệu hai thủ thuật nhanh để giải quyết công việc này.

 Chỉ buộc định dạng số điện thoại với chức năng Xác thực dữ liệu

Để chỉ cho phép nhập định dạng số điện thoại cụ thể, vui lòng thực hiện như sau:

1. Chọn danh sách các ô mà bạn muốn nhập định dạng số điện thoại cụ thể, sau đó bấm chuột phải, chọn Format Cells từ menu ngữ cảnh, xem ảnh chụp màn hình:

2. Trong Format Cells hộp thoại, bên dưới Con số tab, chọn Tuỳ chỉnh ở bên trái Phân loại hộp danh sách, và sau đó nhập định dạng số điện thoại bạn cần vào hộp văn bản Loại, ví dụ: tôi sẽ sử dụng (###) ### - #### định dạng, xem ảnh chụp màn hình:

3. Sau đó nhấn vào OK để đóng hộp thoại.

4. Sau khi định dạng các ô, hãy tiếp tục chọn các ô, sau đó mở Xác nhận dữ liệu hộp thoại bằng cách nhấp chuột Ngày > Xác nhận dữ liệu > Xác nhận dữ liệu, trong hộp thoại bật ra, bên dưới Cài đặt , hãy thực hiện các thao tác sau:

• Chọn Tuỳ chỉnh từ Cho phép danh sách thả xuống.
• Và sau đó, nhập công thức này = VÀ (ISNUMBER (A2), LEN (A2) = 10) vào hộp văn bản Công thức.
• Nhấp chuột OK để đóng hộp thoại này.

Chú thích: Trong công thức trên, A2 là ô đầu tiên của cột mà bạn muốn xác thực số điện thoại.

5. Bây giờ, khi nhập một số có 10 chữ số, nó sẽ tự động được chuyển đổi sang định dạng số điện thoại cụ thể khi bạn cần, hãy xem ảnh chụp màn hình:

Chú thích: Nếu số đã nhập không phải là 10 chữ số, một hộp thông báo cảnh báo sẽ bật ra để nhắc bạn, hãy xem ảnh chụp màn hình:

---

 Chỉ buộc định dạng số điện thoại với một tính năng hữu ích

Kutools cho Excel'S Xác thực số điện thoại tính năng này cũng có thể giúp bạn buộc chỉ nhập định dạng số điện thoại chỉ với vài cú nhấp chuột.

Sau khi cài đặt Kutools cho Excel, hãy làm như sau:

1. Chọn danh sách các ô chỉ cho phép số điện thoại cụ thể, sau đó, nhấp vào Kutools > Ngăn chặn đánh máy > Xác thực số điện thoại, xem ảnh chụp màn hình:

2. Trong Số điện thoại hộp thoại, chọn định dạng số điện thoại cụ thể mà bạn cần hoặc bạn có thể tạo định dạng của riêng mình bằng cách nhấp vào Thêm nút, xem ảnh chụp màn hình:

3. Sau khi chọn hoặc đặt định dạng số điện thoại, hãy nhấp vào OKBây giờ, chỉ có thể nhập số điện thoại có định dạng cụ thể, nếu không, một thông báo cảnh báo sẽ xuất hiện để nhắc nhở bạn, hãy xem ảnh chụp màn hình:

---

4.9 Xác thực dữ liệu chỉ buộc nhập các địa chỉ Email

Giả sử, bạn cần nhập nhiều địa chỉ email vào một cột của trang tính, để ngăn việc nhập một số định dạng địa chỉ email không chính xác, thông thường, bạn có thể đặt quy tắc xác thực dữ liệu để chỉ cho phép định dạng địa chỉ email.

 Chỉ buộc định dạng địa chỉ Email với chức năng Xác thực dữ liệu

Bằng cách sử dụng tính năng Xác thực dữ liệu với công thức tùy chỉnh, bạn có thể tạo quy tắc để ngăn việc nhập địa chỉ email không hợp lệ một cách nhanh chóng, vui lòng thực hiện như sau:

1. Chọn các ô mà bạn chỉ muốn nhập địa chỉ email, sau đó nhấp vào Ngày > Xác nhận dữ liệu > Xác nhận dữ liệu.

2. Trong cửa sổ bật ra Xác nhận dữ liệu hộp thoại, bên dưới Cài đặt , hãy thực hiện các thao tác sau:

• Chọn Tuỳ chỉnh từ Cho phép danh sách thả xuống.
• Và sau đó, nhập công thức này = ISNUMBER (MATCH ("*@*.?*", A2,0)) vào Công thức hộp văn bản.
• Nhấp chuột OK để đóng hộp thoại này.

Chú thích: Trong công thức trên, A2 là ô đầu tiên của cột bạn muốn sử dụng.

3. Bây giờ, nếu văn bản đã nhập không phải là định dạng địa chỉ email, một hộp thông báo cảnh báo sẽ bật ra để nhắc bạn, hãy xem ảnh chụp màn hình:

---

 Chỉ buộc định dạng địa chỉ Email với một tính năng tiện dụng

Kutools cho Excel hỗ trợ một tính năng tuyệt vời - Xác thực địa chỉ email, với tiện ích này, bạn có thể ngăn chặn các địa chỉ email không hợp lệ chỉ với một cú nhấp chuột.

Sau khi cài đặt Kutools cho Excel, vui lòng làm như sau:

1. Chọn các ô mà bạn chỉ cho phép nhập địa chỉ email, sau đó nhấp vào Kutools > Ngăn chặn đánh máy > Xác thực địa chỉ email. Xem ảnh chụp màn hình:

2. Và sau đó, chỉ có định dạng địa chỉ email mới cho phép được nhập, nếu không, một hộp thông báo cảnh báo sẽ bật ra để nhắc bạn, hãy xem ảnh chụp màn hình:

---

4.10 Xác thực dữ liệu chỉ buộc nhập các địa chỉ IP

Phần này, tôi sẽ giới thiệu một số thủ thuật nhanh chóng để thiết lập xác thực dữ liệu để chỉ chấp nhận địa chỉ IP trong một dải ô.

 Chỉ buộc định dạng địa chỉ IP với chức năng Xác thực dữ liệu

Chỉ cho phép các địa chỉ IP được nhập vào một dải ô cụ thể, vui lòng thực hiện như sau:

1. Chọn các ô mà bạn chỉ muốn nhập địa chỉ IP, sau đó nhấp vào Ngày > Xác nhận dữ liệu > Xác nhận dữ liệu.

2. Trong cửa sổ bật ra Xác nhận dữ liệu hộp thoại, bên dưới Cài đặt , hãy thực hiện các thao tác sau:

• Chọn Tuỳ chỉnh từ Cho phép danh sách thả xuống.
• Và sau đó, nhập công thức dưới đây vào Công thức hộp văn bản.
• =AND((LEN(A2)-LEN(SUBSTITUTE(A2,".","")))=3,ISNUMBER(SUBSTITUTE(A2,".","")+0))
• Nhấp chuột OK để đóng hộp thoại này.

Lưu ý: Trong công thức trên, A2 là ô đầu tiên của cột bạn muốn sử dụng.

3. Bây giờ, nếu bạn nhập địa chỉ IP không hợp lệ vào ô, một hộp thông báo cảnh báo sẽ bật ra để nhắc bạn như hình minh họa bên dưới:

---

 Chỉ buộc định dạng địa chỉ IP với mã VBA

Ở đây, mã VBA sau đây cũng có thể giúp chỉ cho phép nhập địa chỉ IP và hạn chế nhập khác, vui lòng thực hiện như sau:

1. Nhấp chuột phải vào tab trang tính và nhấp vào Mã Chế độ xem từ menu ngữ cảnh, trong phần mở đầu Microsoft Visual Basic cho các ứng dụng cửa sổ, sao chép mã VBA bên dưới vào đó.

Mã VBA: xác thực các ô để chỉ chấp nhận địa chỉ IP

Private Sub Worksheet_Change(ByVal Target As Range)
'Update by ExtendOffice
Dim xArrIp() As String
Dim xIntIP1, xIntIP2, xIntIP3, xIntIP4 As Integer
If Intersect(Target, Range("A2:A10")) Is Nothing Then
    Exit Sub
Else
    If Target = "" Then
        Exit Sub
    End If
    xArrIp = Split(Target.Text, ".")
    If UBound(xArrIp) <> 3 Then
        GoTo EIP
    Else
    xIntIP1 = CInt(xArrIp(0))
    xIntIP2 = CInt(xArrIp(1))
    xIntIP3 = CInt(xArrIp(2))
    xIntIP4 = CInt(xArrIp(3))
    If (xIntIP1 < 1) Or (xIntIP1 > 255) _
    Or (xIntIP2 < 1) Or (xIntIP2 > 255) _
    Or (xIntIP3 < 1) Or (xIntIP3 > 255) _
    Or (xIntIP4 < 1) Or (xIntIP4 > 255) Then
    GoTo EIP
     End If
    End If
End If
Exit Sub
EIP:
    MsgBox "Please enter correct IP address"
    Target = ""
End Sub

Chú thích: Trong đoạn mã trên, A2: A10 là dải ô bạn muốn chỉ chấp nhận địa chỉ IP.

2. Sau đó, lưu và đóng mã này, bây giờ, chỉ những địa chỉ IP chính xác mới cho phép nhập vào các ô cụ thể.

---

 Chỉ buộc định dạng địa chỉ IP với một tính năng dễ dàng

Nếu bạn có Kutools cho Excel được cài đặt trong sổ làm việc của bạn, Xác thực địa chỉ IP tính năng này cũng có thể giúp bạn giải quyết công việc này.

Sau khi cài đặt Kutools cho Excel, hãy làm như sau:

1. Chọn các ô mà bạn chỉ cho phép nhập địa chỉ IP, sau đó nhấp vào Kutools > Ngăn chặn đánh máy > Xác thực địa chỉ IP. Xem ảnh chụp màn hình:

2. Sau khi áp dụng tính năng này, bây giờ, chỉ địa chỉ IP cho phép được nhập, nếu không, một hộp thông báo cảnh báo sẽ bật ra để nhắc nhở bạn, hãy xem ảnh chụp màn hình:

---

4.11 Xác thực dữ liệu hạn chế các giá trị vượt quá tổng giá trị

Giả sử, bạn có báo cáo chi phí hàng tháng và tổng ngân sách là 18000 đô la, bây giờ, bạn cần tổng số tiền trong danh sách chi phí không vượt quá tổng số tiền đặt trước là 18000 đô la như hình minh họa bên dưới. Trong trường hợp này, bạn có thể tạo quy tắc xác thực dữ liệu bằng cách sử dụng hàm SUM để ngăn tổng giá trị vượt quá tổng giá trị đặt trước.

1. Chọn danh sách các ô mà bạn muốn giới hạn các giá trị.

2. Sau đó nhấn vào Ngày > Xác nhận dữ liệu > Xác nhận dữ liệu, trong cửa sổ bật ra Xác nhận dữ liệu hộp thoại, bên dưới Cài đặt , hãy thực hiện các thao tác sau:

• Chọn Tuỳ chỉnh từ Cho phép danh sách thả xuống.
• Và sau đó, nhập công thức dưới đây vào Công thức hộp văn bản.
• =SUM($B$2:$B$7)<=18000
• Nhấp chuột OK để đóng hộp thoại này.

Chú thích: Trong công thức trên, B2: B7 là phạm vi ô bạn muốn giới hạn mục nhập.

3. Bây giờ, khi nhập các giá trị trong phạm vi B2: B7, nếu tổng các giá trị nhỏ hơn $ 18000, quá trình xác thực sẽ được thông qua. Nếu bất kỳ giá trị nào làm cho tổng số tiền vượt quá $ 18000, một hộp thông báo cảnh báo sẽ bật ra để nhắc nhở bạn.

---

4.12 Xác thực dữ liệu hạn chế mục nhập ô dựa trên một ô khác

Khi bạn muốn giới hạn các mục nhập dữ liệu trong danh sách ô dựa trên giá trị trong ô khác, tính năng Xác thực dữ liệu cũng có thể giúp giải quyết công việc này. Ví dụ: nếu ô C1 là văn bản “Có”, phạm vi A2: A9 được phép nhập bất kỳ thứ gì, nhưng nếu ô C1 là một văn bản khác, thì không có gì được phép nhập trong phạm vi A2: A9 như ảnh chụp màn hình bên dưới được hiển thị :

Để giải quyết vấn đề này, vui lòng làm như sau:

1. Chọn danh sách các ô mà bạn muốn giới hạn các giá trị.

2. Sau đó nhấn vào Ngày > Xác nhận dữ liệu > Xác nhận dữ liệu, trong cửa sổ bật ra Xác nhận dữ liệu hộp thoại, bên dưới Cài đặt , hãy thực hiện các thao tác sau:

• Chọn Tuỳ chỉnh từ Cho phép danh sách thả xuống.
• Và sau đó, nhập công thức dưới đây vào Công thức hộp văn bản.
• =$C$1="Yes"
• Nhấp chuột OK để đóng hộp thoại này.

Chú thích: Trong công thức trên, C1 là ô chứa văn bản cụ thể mà bạn muốn sử dụng và văn bản “Có”Là văn bản bạn muốn giới hạn các ô dựa trên, vui lòng thay đổi chúng theo nhu cầu của bạn.

3. Bây giờ, nếu ô C1 có văn bản “Có”, bất kỳ thứ gì có thể được nhập vào phạm vi A2: A9, nếu ô C1 có văn bản khác, bạn sẽ không thể nhập bất kỳ giá trị nào, hãy xem bản trình diễn bên dưới:

---

4.13 Xác thực dữ liệu chỉ cho phép nhập các ngày trong tuần hoặc cuối tuần

Nếu bạn chỉ cần nhập các ngày trong tuần (từ Thứ Hai đến Thứ Sáu) hoặc các ngày cuối tuần (Thứ Bảy và Chủ Nhật) vào danh sách các ô, Xác nhận dữ liệu cũng có thể giúp bạn, vui lòng thực hiện theo các bước sau:

1. Chọn danh sách các ô mà bạn muốn nhập các ngày trong tuần hoặc các ngày trong tuần.

2. Sau đó nhấn vào Ngày > Xác nhận dữ liệu > Xác nhận dữ liệu, trong cửa sổ bật ra Xác nhận dữ liệu hộp thoại, bên dưới Cài đặt , hãy thực hiện các thao tác sau:

• Chọn Tuỳ chỉnh từ Cho phép danh sách thả xuống.
• Và sau đó, nhập một trong các công thức dưới đây vào Công thức hộp văn bản khi bạn cần.
• =WEEKDAY(A2,2)<6                      (allow only weekdays)
  =WEEKDAY(A2,2)>5                      (allow only weekends)
• Nhấp chuột OK để đóng hộp thoại này.

Chú thích: Trong công thức trên, A2 là ô đầu tiên của cột bạn muốn sử dụng.

3. Bây giờ, bạn chỉ có thể nhập ngày trong tuần hoặc ngày cuối tuần vào các ô cụ thể dựa trên nhu cầu của bạn.

---

4.14 Xác thực dữ liệu cho phép ngày nhập dựa trên ngày hôm nay

Đôi khi, bạn có thể chỉ cần cho phép nhập ngày lớn hơn hoặc ít hơn ngày hôm nay vào danh sách ô. Các Xác nhận dữ liệu tính năng với TODAY chức năng có thể giúp bạn một việc. Vui lòng làm như sau:

1. Chọn danh sách các ô mà bạn chỉ muốn nhập ngày trong tương lai (ngày lớn hơn hôm nay).

2. Sau đó nhấn vào Ngày > Xác nhận dữ liệu > Xác nhận dữ liệu, trong cửa sổ bật ra Xác nhận dữ liệu hộp thoại, bên dưới Cài đặt , hãy thực hiện các thao tác sau:

• Chọn Tuỳ chỉnh từ Cho phép danh sách thả xuống.
• Và sau đó, nhập công thức dưới đây vào Công thức hộp văn bản.
• =A2>Today()
• Nhấp chuột OK để đóng hộp thoại này.

Chú thích: Trong công thức trên, A2 là ô đầu tiên của cột bạn muốn sử dụng.

3. Bây giờ, chỉ những ngày lớn hơn ngày hôm nay mới có thể được nhập vào các ô, nếu không, một hộp thông báo cảnh báo sẽ bật ra để nhắc nhở bạn, hãy xem ảnh chụp màn hình:

---

4.15 Xác thực dữ liệu cho phép thời gian đã nhập dựa trên thời gian hiện tại

Ví dụ: nếu bạn muốn xác thực dữ liệu dựa trên thời gian hiện tại, chỉ thời gian trước hoặc sau thời điểm hiện tại mới có thể được nhập vào các ô. Bạn có thể tạo công thức xác thực dữ liệu của riêng mình, vui lòng làm như sau:

1. Chọn danh sách các ô mà bạn chỉ muốn nhập thời gian trước hoặc sau thời gian hiện tại.

2. Sau đó nhấn vào Ngày > Xác nhận dữ liệu > Xác nhận dữ liệu, trong cửa sổ bật ra Xác nhận dữ liệu hộp thoại, bên dưới Cài đặt , hãy thực hiện các thao tác sau:

• Chọn Thời gian từ Cho phép danh sách thả xuống.
• Sau đó chọn ít 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 gian hiện tại khi bạn cần từ Ngày thả xuống.
• Và sau đó, trong Thời gian kết thúc or Thời gian bắt đầu , nhập công thức dưới đây:
• =TIME(HOUR(NOW()),MINUTE(NOW()),SECOND(NOW()))
• Nhấp chuột OK để đóng hộp thoại này.

Chú thích: Trong công thức trên, A2 là ô đầu tiên của cột bạn muốn sử dụng.

3. Bây giờ, chỉ thời gian trước hoặc sau thời điểm hiện tại mới có thể được nhập vào các ô cụ thể.

---

4.16 Xác thực dữ liệu vào ngày cụ thể hoặc năm hiện tại

Để chỉ cho phép nhập các ngày trong một năm nhất định hoặc năm hiện tại, bạn có thể sử dụng xác thực dữ liệu bằng công thức tùy chỉnh dựa trên hàm YEAR.

1. Chọn danh sách các ô mà bạn chỉ muốn nhập các ngày trong một năm nhất định.

2. Sau đó nhấn vào Ngày > Xác nhận dữ liệu > Xác nhận dữ liệu, trong cửa sổ bật ra Xác nhận dữ liệu hộp thoại, bên dưới Cài đặt , hãy thực hiện các thao tác sau:

• Chọn Tuỳ chỉnh từ Cho phép danh sách thả xuống.
• Và sau đó, nhập công thức dưới đây vào Công thức hộp văn bản.
• =YEAR(A2)=2020
• Nhấp chuột OK để đóng hộp thoại này.

Chú thích: Trong công thức trên, A2 là ô đầu tiên của cột bạn muốn sử dụng, 2020 là số năm bạn muốn hạn chế.

3. Và sau đó, chỉ có thể nhập các ngày trong năm 2020, nếu không, một hộp thông báo cảnh báo sẽ bật ra như hình minh họa bên dưới:

---

4.17 Xác thực dữ liệu vào ngày trong tuần hoặc tháng hiện tại

Nếu bạn muốn cho phép người dùng có thể nhập ngày của tuần hoặc tháng hiện tại vào các ô cụ thể, phần này sẽ giới thiệu một số công thức để xử lý tác vụ này trong Excel.

 Cho phép nhập ngày của tuần hiện tại

1. Chọn danh sách các ô mà bạn chỉ muốn nhập các ngày trong tuần hiện tại.

2. Sau đó nhấn vào Ngày > Xác nhận dữ liệu > Xác nhận dữ liệu, trong cửa sổ bật ra Xác nhận dữ liệu hộp thoại, bên dưới Cài đặt , hãy thực hiện các thao tác sau:

• Chọn Ngày từ Cho phép danh sách thả xuống.
• Và sau đó, chọn giữa từ Ngày thả xuống.
• Trong tạp chí Ngày bắt đầu hộp văn bản, nhập công thức này: = TODAY () - WEEKDAY (TODAY (), 3)
• Trong tạp chí Ngày kết thúc hộp văn bản, nhập công thức này: = TODAY () - WEEKDAY (TODAY (), 3) +6
• Cuối cùng, hãy nhấp vào OK .

3. Sau đó, chỉ có thể nhập các ngày trong tuần hiện tại, các ngày khác sẽ bị ngăn chặn như hình minh họa bên dưới:

---

 Cho phép nhập ngày của tháng hiện tại

Để chỉ cho phép nhập các ngày của tháng hiện tại, vui lòng thực hiện như sau:

1. Chọn danh sách các ô mà bạn chỉ muốn nhập các ngày trong tháng hiện tại.

2. Sau đó nhấn vào Ngày > Xác nhận dữ liệu > Xác nhận dữ liệu, trong cửa sổ bật ra Xác nhận dữ liệu hộp thoại, bên dưới Cài đặt , hãy thực hiện các thao tác sau:

• Chọn Ngày từ Cho phép danh sách thả xuống.
• Và sau đó, chọn giữa Ngày thả xuống.
• Trong tạp chí Ngày bắt đầu hộp văn bản, nhập công thức này: = DATE (YEAR (TODAY ()), MONTH (TODAY ()), 1)
• Trong tạp chí Ngày kết thúc hộp văn bản, nhập công thức này: = DATE (YEAR (TODAY ()), MONTH (TODAY ()), DAY (DATE (YEAR (TODAY ()), MONTH (TODAY ()) + 1,1) -1))
• Cuối cùng, hãy nhấp vào OK .

3. Từ bây giờ, chỉ những ngày của tháng hiện tại mới được phép nhập vào các ô đã chọn.

---

5. Làm thế nào để chỉnh sửa xác thực dữ liệu trong Excel?

Để chỉnh sửa hoặc thay đổi quy tắc xác thực dữ liệu hiện có, vui lòng thực hiện theo các bước sau:

1. Chọn bất kỳ ô nào có quy tắc xác thực dữ liệu.

2. Sau đó nhấn vào Ngày > Xác nhận dữ liệu > Xác nhận dữ liệu đi đến Xác nhận dữ liệu trong hộp thoại, trong hộp, hãy chỉnh sửa hoặc thay đổi các quy tắc theo nhu cầu của bạn, sau đó chọn Áp dụng những thay đổi này cho tất cả các ô khác có cùng cài đặt để áp dụng quy tắc mới này cho tất cả các ô khác có tiêu chí xác thực ban đầu. Xem ảnh chụp màn hình:

3. Nhấp chuột OK để lưu các thay đổi.

---

6. Làm thế nào để tìm và chọn các ô có xác thực dữ liệu trong Excel?

Nếu bạn đã tạo nhiều quy tắc xác thực dữ liệu trong trang tính của mình, bây giờ, bạn cần tìm và chọn các ô đã áp dụng các quy tắc xác thực dữ liệu, Đi đặc biệt lệnh có thể giúp bạn chọn tất cả các loại xác thực dữ liệu hoặc loại xác thực dữ liệu cụ thể.

1. Kích hoạt trang tính mà bạn muốn tìm và chọn các ô có xác thực dữ liệu.

2. Sau đó nhấn vào Trang Chủ > Tìm & Chọn > Đi đặc biệt, xem ảnh chụp màn hình:

3. Trong Đi đặc biệt hộp thoại, chọn Xác nhận dữ liệu > Tất cả, xem ảnh chụp màn hình:

4. Và tất cả các ô có xác thực dữ liệu đã được chọn cùng một lúc trong trang tính hiện tại.

---

7. Làm thế nào để sao chép quy tắc xác thực dữ liệu sang các ô khác?

Giả sử, bạn đã tạo quy tắc xác thực dữ liệu cho danh sách các ô và bây giờ, bạn cần áp dụng quy tắc xác thực dữ liệu tương tự cho các ô khác. Thay vì tạo lại quy tắc, bạn có thể sao chép và dán quy tắc hiện có vào các ô khác một cách nhanh chóng và dễ dàng.

1. Bấm để chọn một ô có quy tắc xác thực bạn muốn sử dụng, sau đó bấm Ctrl + C để sao chép nó.

2. Sau đó, chọn các ô bạn muốn xác thực, để chọn nhiều ô không liền kề, hãy nhấn và giữ Ctrl trong khi chọn các ô.

3. Và sau đó, nhấp chuột phải vào lựa chọn, chọn Dán đặc biệt tùy chọn, xem ảnh chụp màn hình:

4. Trong Dán đặc biệt hộp thoại, chọn THẨM ĐỊNH tùy chọn, xem ảnh chụp màn hình:

5. Nhấp chuột OK , bây giờ quy tắc xác thực được sao chép vào các ô mới.

---

8. Làm thế nào để sử dụng xác thực dữ liệu để khoanh tròn các mục nhập không hợp lệ trong Excel?

Đôi khi, bạn có thể cần tạo quy tắc xác thực dữ liệu cho dữ liệu hiện có, trong trường hợp này, một số dữ liệu không hợp lệ có thể xuất hiện trong phạm vi ô. Làm thế nào để kiểm tra dữ liệu không hợp lệ và sửa đổi chúng? Trong Excel, bạn có thể sử dụng Dữ liệu không hợp lệ trong vòng kết nối tính năng đánh dấu dữ liệu không hợp lệ bằng một vòng tròn màu đỏ.

Để khoanh tròn dữ liệu không hợp lệ mà bạn cần, bạn nên áp dụng Xác nhận dữ liệu để đặt quy tắc cho phạm vi dữ liệu. Vui lòng thực hiện theo các bước sau:

1. Chọn phạm vi dữ liệu mà bạn muốn khoanh tròn dữ liệu không hợp lệ.

2. Sau đó nhấn vào Ngày > Xác nhận dữ liệu > Xác nhận dữ liệu, Trong Xác nhận dữ liệu hộp thoại, đặt quy tắc xác thực theo nhu cầu của bạn, ví dụ: ở đây, tôi sẽ xác thực các giá trị lớn hơn 500, xem ảnh chụp màn hình:

3. Sau đó nhấn vào OK để đóng hộp thoại. Sau khi đặt quy tắc xác thực dữ liệu, hãy nhấp vào Ngày > Xác nhận dữ liệu > Dữ liệu không hợp lệ trong vòng kết nối, thì tất cả các giá trị không hợp lệ nhỏ hơn 500 đã được khoanh tròn bằng hình bầu dục màu đỏ. Xem ảnh chụp màn hình:

Ghi chú:

• 1. Ngay sau khi bạn sửa một dữ liệu không hợp lệ, vòng tròn màu đỏ sẽ tự động biến mất.
• 2. Điều này Dữ liệu không hợp lệ trong vòng kết nối đối tượng địa lý chỉ có thể khoanh tròn tối đa 255 ô. Khi bạn lưu sổ làm việc hiện tại, tất cả các vòng tròn màu đỏ sẽ bị xóa.
• 3. Những vòng tròn này không thể in được.
• 4. Bạn cũng có thể xóa các vòng tròn màu đỏ bằng cách nhấp vào Ngày > Xác nhận dữ liệu > Xóa vòng kết nối xác thực.

---

9. Làm thế nào để loại bỏ xác thực dữ liệu trong Excel?

Để xóa các quy tắc xác thực dữ liệu khỏi một phạm vi ô, trang tính hiện tại hoặc toàn bộ sổ làm việc, các phương pháp sau có thể giúp bạn.

 Xóa xác thực dữ liệu trong phạm vi đã chọn bằng chức năng xác thực dữ liệu

1. Chọn các ô có xác thực dữ liệu mà bạn muốn xóa.

2. Sau đó nhấn vào Ngày > Xác nhận dữ liệu > Xác nhận dữ liệu, trong hộp thoại bật ra, bên dưới Cài đặt tab, nhấp vào Làm sạch tất cả nút, xem ảnh chụp màn hình:

3. Sau đó nhấn vào OK để đóng hộp thoại này. Và quy tắc xác thực dữ liệu áp dụng cho phạm vi đã chọn đã bị xóa ngay lập tức.

Lời khuyên: Để xóa xác thực dữ liệu khỏi trang tính hiện tại, trước tiên vui lòng chọn toàn bộ trang tính, sau đó áp dụng các bước trên.

---

 Xóa xác thực dữ liệu trong phạm vi đã chọn bằng một tính năng tiện dụng

Nếu bạn có Kutools cho ExcelCủa nó, Xóa các hạn chế xác thực dữ liệu tính năng cũng có thể giúp loại bỏ các quy tắc xác thực dữ liệu khỏi phạm vi đã chọn hoặc toàn bộ trang tính.

Sau khi cài đặt Kutools cho Excel, hãy làm như sau:

1. Chọn phạm vi ô hoặc toàn bộ trang tính chứa xác thực dữ liệu mà bạn muốn xóa.

2. Sau đó nhấn vào Kutools > Ngăn chặn đánh máy > Xóa các hạn chế xác thực dữ liệu, xem ảnh chụp màn hình:

3. Trong hộp lời nhắc hiện ra, hãy nhấp vào OKvà quy tắc xác thực dữ liệu đã được xóa khi bạn cần.

---

 Xóa xác thực dữ liệu khỏi tất cả các trang tính có mã VBA

Để xóa các quy tắc xác thực dữ liệu khỏi toàn bộ sổ làm việc, các phương pháp trên sẽ tốn thời gian nếu có nhiều trang tính, ở đây, đoạn mã dưới đây có thể giúp bạn giải quyết công việc này một cách nhanh chóng.

1. Giữ ALT + F11 phím để mở Microsoft Visual Basic cho các ứng dụng cửa sổ.

2. Sau đó nhấp vào Chèn > Mô-đunvà dán macro sau vào Mô-đun cửa sổ.

Mã VBA: Xóa quy tắc xác thực dữ liệu trong tất cả các trang tính:

Sub RemoveDataValidation()
'Updateby Extendoffice
  Dim xwsh As Worksheet
  For Each xwsh In ActiveWorkbook.Worksheets
    xwsh.Cells.Validation.Delete
  Next xwsh
End Sub

3. Sau đó nhấn F5 để chạy mã này và tất cả các quy tắc xác thực dữ liệu đã bị xóa khỏi toàn bộ sổ làm việc ngay lập tức.