Sử dụng Data Validation khi danh sách nguồn nằm trong một Sheet khác

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 0 và nhỏ hơn hoặc bằng 10.
• 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.

//*đề bài*//

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 Allow bạn chọn là List
• Đánh dấu chọn vào Ignore blank và In-cell dropdown
• Tại Source bạ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ểuList
• Đánh dấu chọn vào Ignore blank và 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’!$B$2:$B$10”)

• 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
• $B$2:$B$10 đị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.

Mình không rõ là trên phiên bản 2013, 2016 có hoạt động được không? Bởi hiện tại mình không dùng 2 phiên bản này, vậy nên nếu bạn nào đang sử dụng 1 trong 2 phiên bản này thì thông báo giúp mình phía dưới phần comment nhé.

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 !

CTV: Nhựt Nguyễn – Blogchiasekienthuc.com