Wiki

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

Xác thực dữ liệu là một tính năng cho phép chúng ta kiểm tra quy tắc xác thực 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 dữ liệu, đặc biệt khi cần nhập các dãy số theo một quy tắc nhất định. Nhu la:

  • Điểm là một 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 chữ số.
  • Số Căn cước công dân có 12 chữ số…

Khi bạn nhập sai (ví dụ: điểm bạn nhập là 11), một hộp thoại thông báo sẽ xuất hiện ngay lập tức, sau đó bạn chỉ cần sửa lại.

Chúng tôi có tất cả 7 quy tắc nhập liệu trong Excel, đó là: Giá trị bất kỳ, Số nguyên, Số thập phân, Danh sách, Ngày, Giờ, Độ dài văn bản Phong tục. 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ó hai mặt của nó. Quy tắc nhập này có một hạn chế (đối với các phiên bản Microsoft Excel cũ hơn) là không thể sử dụng danh sách nguồn nếu danh sách nằm trong một Trang tính khác.

// * Chủ đề * //

Được rồi, bây giờ chúng ta hãy làm một ví dụ. Ví dụ, chúng tôi có một Workbook với 2 Worksheets có tên là 1 và 2. tương ứng

  • Bảng 1 là nơi xác thực dữ liệu được sử dụng.
  • Bảng 2 là nơi danh sách nguồn được lưu trữ

Chủ đề là một yêu cầu tạo Data Validation cho khối ô C3:C6 với quy tắc đầu vào là Danh sách. Cụ thể, bạn chỉ có thể chọn Huyện/ TP trực thuộc tỉnh bao gồm trong danh sách:

su-dung-data-validation-khi-name-sach-sach-nguon-nam-trong-mot-sheet-other (1)

#đầu tiên. 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 bài viết này tôi sẽ đặt tên là Huyen => sau đó bấm phím Enter trên bàn phím.

su-dung-data-validation-khi-danh-sach-manu-nam-trong-mot-sheet-other (2)

+ Bước 2: Chọn Worksheet 1 => chọn khối ô C3:C6

su-dung-data-validation-khi-danh-sach-nguon-nam-trong-mot-sheet-other (3)

+ Bước 3: Chọn Data => chọn tiếp theo Data Validation

su-dung-data-validation-khi-danh-sach-nguon-nam-trong-mot-sheet-other (4)

+ Bước 4: Hộp thoại Data Validation xuất hiện như hình dưới đây:

  • Ở trong Allow bạn chọn là List
  • Đánh dấu Ignore blankIn-cell dropdown
  • Ở trong Source bạn nhập =Huyen

=> Sau đó chọn OK

su-dung-data-validation-when-list-sach-nguon-nam-trong-mot-sheet-other (5)

Cuối cùng chúng ta nhận được kết quả như hình bên dưới:

su-dung-data-validation-when-list-sach-nguon-nam-trong-mot-sheet-other (6)

Một số bài viết liên quan:

# 2. Phương pháp 2: Sử dụng INDIRECT (ref_text, [a1])

Chìa khóa 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 ô hoặc khối ô sẽ 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

su-dung-data-validation-khi-danh-sach-nguon-nam-trong-mot-sheet-other (7)

+ Bước 2: Chọn Data => chọn Data Validation

su-dung-data-validation-khi-name-sach-sach-nguon-nam-trong-mot-sheet-other (8)

+ Bước 3: Hộp thoại Data Validation xuất hiện:

  • Ở trong Cho phép bạn chọn phong cáchList
  • Đánh dấu Ignore blankIn-cell dropdown
  • Ở trong Nguồn nhập khẩu =INDIRECT(“’2’!$B$2:$B$10”)

=> Sau đó bạn chọn OK

su-dung-data-validation-khi-name-sach-manu-nam-trong-mot-sheet-other (9)

2.2. Giải thích công thức = INDIRECT (“’2′! $ B $ 2: $ B $ 10”)

su-dung-data-validation-when-name-sach-nguon-nam-trong-mot-sheet-other (10)

  • GIÁN TIẾP(…) là một hàm tham chiếu đến một ô / ô, Trang tính, Sổ làm việc
  • dấu ngoặc kép được yêu cầu trong trường hợp này bởi vì ’2’!$B$2:$B$10 được coi là dữ liệu chuỗi.
  • dấu ngoặc kép trong trường hợp này có thể được bỏ qua. Tuy nhiên, khi tên của Worksheet có dấu cách hoặc là tiếng Việt có dấu thì không thể bỏ được.
  • 2 là phần trên cùng của Trang tính có chứa danh sách nguồn
  • ! là một ký tự bắt buộc khi tham chiếu đến một ô hoặc khối ô trong một Trang tính 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, 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 làm giống như Phương pháp 2 ở trên. Tuy nhiên trong Bước 3 thay vì nhập chúng tôi sẽ “quét khối” trực tiếp. Phương pháp này có thể được áp dụng khi “khối quét” nằm trong một khu vực nhỏ.

Trong hộp thoại Data Validation chọn

su-dung-data-validation-khi-danh-sach-nguon-nam-trong-mot-sheet-other (12)

+ Bước 4: Chọn Worksheet 2 => “Chặn quét” khối ô B2:B10 => chọn

=> sau đó chọn OK làm xong.

su-dung-data-validation-khi-danh-sach-nguon-nam-trong-mot-sheet-other (14)

#4. Phần kết

Được rồi, vậy là tôi đã hướng dẫn bạn cách sử dụng Xác thực dữ liệu khi danh sách nguồn nằm trong Trang tính khác.

Phương pháp 1 (Đặt tên cho danh sách nguồn) và Phương pháp 2 (Sử dụng INDIRECT (ref_text, [a1])) bạn có thể áp dụng cho bất kỳ phiên bản nào của Microsoft Excel (2003, 2007, 2010, 2013, 2016, 2019 và 365).

Phương pháp 3 (“Quét khối”) chỉ hoạt động trên Microsoft Excel 2019 và các phiên bản mới hơn.

Tôi không chắc liệu nó có hoạt động trên phiên bản 2013, 2016 hay không? Vì hiện tại mình không sử dụng 2 phiên bản này nên nếu bạn nào đang sử dụng 1 trong 2 phiên bản này thì hãy thông báo cho mình ở phần bình luận bên dưới nhé.

Hy vọng 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 – phanmemdownload.com

Ghi chú: Bài viết này có hữu ích cho bạn không? Đừng quên đánh giá bài viết, thích và chia sẻ nó với bạn bè và gia đình của bạn!

Related Articles

Trả lời

Email của bạn sẽ không được hiển thị công khai. Các trường bắt buộc được đánh dấu *

Back to top button