Data Validation là một tính năng cho phép chúng ta kiểm tra quy tắc hợp lệ khi nhập dữ liệu trong Excel.
Tính năng này thường được sử dụng trong quá trình nhập liệu, đặc biệt là khi cần nhập các dãy số theo một quy luật nhất định. Chẳng hạn như:
- Điểm là số tự nhiên lớn hơn hoặc bằng
0và nhỏ hơn hoặc bằng10. - Số điện thoại có 10 kí tự số.
- Số chứng minh nhân dân có 9 kí tự số.
- Số căn cước công dân có 12 kí tự số…
Khi bạn nhập sai (ví dụ điểm số mà bạn lại nhập vào là 11) thì một hộp thoại thông báo sẽ xuất hiện ngay lập tức, khi đó bạn chỉ việc sửa lại cho đúng.
Chúng ta có tất cả 7 quy tắc nhập liệu trong Excel, đó là: Any value, Whole number, Decimal, List, Date, Time, Text length và Custom. Trong đó List là một trong những quy tắc được sử dụng nhiều nhất.
Tuy nhiên, cái gì cũng có 2 mặt của nó. Quy tắc nhập liệu này có một hạn chế (đối với các phiên bản Microsoft Excel cũ) là không thể sử dụng danh sách nguồn nếu danh sách đó nằm trong một WorkSheet khác.
Okay, bây giờ ta sẽ làm một ví dụ nhé. Ví dụ như ta có Workbook với 2 Worksheet được đặt tên lần lượt là 1 và 2
- Worksheet 1 là nơi sử dụng Data Validation.
- Worksheet 2 là nơi chứa danh sách nguồn
Đề bài là yêu cầu tạo Data Validation cho khối ô C3:C6 với quy tắc nhập liệu là List. Cụ thể chỉ có thể chọn các Huyện/ TP trực thuộc tỉnh có trong danh sách:
#1. Phương pháp 1: Đặt tên cho danh sách nguồn
+ Bước 1: Chọn Worksheet 2 => chọn khối ô B2:B10 => đặt tên cho khối ô này bằng cách nhập tên bất kì vào ô Name Box
Trong phạm vi của bài viết này mình sẽ sẽ đặt tên là Huyen => sau đó nhấn phím Enter trên bàn phím.
+ Bước 2: Chọn Worksheet 1 => chọn khối ô C3:C6
+ Bước 3: Chọn Data => chọn tiếp Data Validation
+ Bước 4: Hộp thoại Data Validation xuất hiện như hình bên dưới:
- Tại
Allowbạn chọn làList - Đánh dấu chọn vào
Ignore blankvàIn-cell dropdown - Tại
Sourcebạn nhập=Huyen
=> Sau đó chọn OK
Cuối cùng ta được kết quả như hình bên dưới:
#2. Phương pháp 2: Sử dụng hàm INDIRECT(ref_text, [a1])
Mấu chốt của phương pháp này là chúng ta sẽ sử dụng hàm INDIRECT(ref_text, [a1]) trong Excel để tham chiếu đến ô, khối ô cần sử dụng.
2.1: Các bước thực hiện như sau:
+ Bước 1: Chọn Worksheet 1 => chọn khối ô C3:C6
+ Bước 2: Chọn Data => chọn Data Validation
+ Bước 3: Hộp thoại Data Validation xuất hiện:
- Tại Allow bạn chọn kiểu
List - Đánh dấu chọn vào
Ignore blankvàIn-cell dropdown - Tại Source nhập
=INDIRECT(“’2’!$B$2:$B$10”)
=> Sau đó bạn chọn OK
2.2. Giải thích công thức =INDIRECT(“’2’!B2:B10”)
- INDIRECT(…) là hàm tham chiếu đến ô/khối ô, Worksheet, Workbook
- “ dấu nháy kép là bắt buộc trong trường hợp này vì
’2’!$B$2:$B$10được xem như dữ liệu kiểu chuỗi. - ‘ dấu nháy đơn trong trường hợp này có thể bỏ qua. Tuy nhiên khi tên của Worksheet có khoảng trắng hoặc là tiếng Việt có dấu thì không được bỏ
- 2 là trên của Worksheet chứa danh sách nguồn
- ! là kí tự bắt buộc khi tham chiếu đến một ô, khối ô trong một Worksheet khác
- B2:B10 địa chỉ tuyệt đối của khối ô chứa danh sách nguồn (sau khi chọn ô/ khối ô thì bạn hãy nhấn phím F4 trên bàn phím).
#3. Phương pháp 3: “Quét khối”
+ Bước 1, Bước 2, Bước 3 thực hiện tương tự như Phương pháp 2 bên trên. Tuy nhiên ở Bước 3 thay vì nhập chúng ta sẽ “quét khối” trực tiếp. Cách này có thể áp dụng khi “khối quét” trong một phạm vi nhỏ hẹp.
Trong hộp thoại Data Validation chọn 
+ Bước 4: Chọn Worksheet 2 => “quét khối” khối ô B2:B10 => chọn 
=> sau đó chọn OK là xong.
#4. Lời kết
Okay, như vậy là mình đã hướng dẫn xong cho bạn cách sử dụng Data Validation khi danh sách nguồn nằm trong một Sheet khác ha.
Phương pháp 1 (Đặt tên cho danh sách nguồn) và Phương pháp 2 (Sử dụng hàm INDIRECT(ref_text, [a1])) bạn có thể áp dụng cho mọi phiên bản của Microsoft Excel (2003, 2007, 2010, 2013, 2016, 2019 và 365).
Còn phương pháp 3 (“Quét khối”) thì chỉ hoạt động trên phiên bản Microsoft Excel 2019 và các phiên bản mới hơn.
Hi vọng là bài viết này sẽ hữu ích với bạn. Xin chào tạm biệt và hẹn gặp lại các bạn trong những bài viết tiếp theo !
