Trong bài viết này, Blog Học Excel Online sẽ giới thiệu với bạn cách để tạo 1 hôp thoại xổ xuống cho chức năng Data Validation trong Excel. Ta cũng sẽ tìm hiểu về một số kỹ thuật nâng cao để hiển thị danh sách nhiều lựa chọn Data Validation thông qua hàm Indirect.

Giới thiệu chức năng Data Validation trong Excel

Nói một cách đơn giản, Data Validation là chức năng kiểm duyệt dữ liệu được nhập vào sao cho dữ liệu đúng phải được nhập vào đúng nơi đúng chỗ. Data Validation được sử dụng với nhiều mục đích khác nhau, chẳng hạn như để nhập các ký tự chữ giống nhau trong cùng một cột hoặc xây dựng biểu đồ báo cáo tài chính với các tiêu chí ràng buộc (chẳng hạn theo mốc thời gian hàng tháng, hàng quý, nửa năm, hàng năm, theo từng khu vực hoặc theo từng quốc gia)

Cách để tạo hộp thoại Data Validation xổ xuống trong Excel

Bước 1: Bôi đen một ô tính hoặc vùng dữ liệu cần tạo hộp thoại Data Validation xổ xuống

Bước 2: Chọn thanh tác vụ Data và click vào mục Data Validation

Sau đó hộp thoại Data Validation xuất hiện với rất nhiều lựa chọn cho bạn

Bước 3: Trong tab Settings, chọn List

Bước 4: Tiếp đến bạn điền danh sách các dữ liệu cần kiểm duyệt, ngăn cách nhau bởi dấu phẩy vào sau đó bấm OK

(trong ví dụ dưới đây ta sẽ điền lần lượt tên các tháng vào để người dùng có thể lựa chọn trong danh sách xổ xuống)

Cách để thêm các thông báo dữ liệu cần nhập và cảnh báo lỗi xảy ra vào trong hộp thoại xổ xuống

• Đối với thông báo dữ liệu cần nhập:

Trong cửa sổ hộp thoại Data Validation, Chọn tab Input Message -> Click chọn vào hộp kiểm đầu tiên

Điền vào phần Title nội dung khái quát của thông báo

Điền vào phần Input message nội dung thông báo đầy đủ về loại dữ liệu mà người dùng sẽ lựa chọn để nhập vào

• Đối với cảnh báo lỗi:

Trong cửa sổ hộp thoại Data Validation, Chọn tab Error Alert -> Click chọn vào hộp kiểm đầu tiên

Chọn vào biểu tượng cảnh báo và điền nội dung khái quát của lỗi cũng như nội dung lỗi đầy đủ vào các mục Title và Short Description.

Sau đó bấm OK

Xem thêm: Địa chỉ học Excel tại Hà Nội tốt nhất

Cách để tạo hộp thoại Data Validation xổ xuống cho vùng dữ liệu/vùng dữ liệu đã được đặt tên

Đôi khi không phải lúc nào bạn cũng có thể tạo thủ công hộp thoại dữ liệu xổ xuống trong ô dữ liệu nguồn. Nếu trong quá trình nhập liệu sau này mà bạn cần phải thay đổi hay cập nhập thì việc này sẽ trở nên tương đối khó chịu.

Thay vì đó, bạn nên liên kết đến 1 dải ô mới để dễ dàng cập nhật những thay đổi về nội dung dữ liệu

Bước 1: Chọn 1 ô tính cần tạo hộp kiểm Data Validation

Bước 2: Điền các giá trị của hộp kiểm vào 1 cột khác

Bước 3: Click chọn tab Data Validation -> Setting -> Chọn “List”

Bước 4: Trên phần Source -> Bấm vào nút tô đen vùng dữ liệu và chọn vùng dữ liệu mà bạn cần, sau đó bấm OK để hoàn tất

Đối với vùng dữ liệu đã được đặt tên, việc thiết lập trên trở nên dễ dàng hơn bởi vì nó dễ nhận biết và cũng dễ thao tác hơn:

Bước 1: Chọn dải ô dữ liệu cần tham chiếu

Bước 2: Tìm đến thanh tác vụ Formulas -> Chọn “Define Name”

Bước 3: Dưới mục Name -> Điền tên vùng dữ liệu mà bạn muốn (trong ví dụ này ta chọn “lookup_range”)

Bước 4: Đặt Scope là Workbook, Phần Comment ta sẽ điền một đoạn mô tả ngắn để dễ phân biệt -> Sau đó click OK

Bước 5: Quay lại cửa sổ Data Validation -> Trong mục Source bạn nhập tên vùng dữ liệu vừa tạo (=lookup_range) là xong.

Cách tạo hộp thoại xổ xuống Data Validation có điều kiện ràng buộc

Phần này ta sẽ tìm hiểu cách để tạo 1 hộp thoại linh động với hàm Indirect

Ví dụ tiếp theo ta có dữ liệu được chia thành 2 loại: Đồ điện tử và Sách

Khi người dùng lựa chọn Đồ điện tử (Electronics) trong hộp thoại xổ xuống, sẽ có 1 hộp thoại khác xuất hiện trong đó chứa những danh mục thuộc hàng điện tử, ví dụ như điện thoại di động, laptop, v.v.

Khi người dùng lựa chọn Sách (Books) trong hộp thoại xổ xuống, sẽ có 1 hộp thoại khác xuất hiện trong đó chứa những danh mục các loại sách, ví dụ như sách tiểu thuyết giả tưởng, sách văn học lãng mạn, sách tiểu thuyết thực tế, v.v.

Bố trí bảng tham chiếu:

Bước 1: Bố trí bảng tham chiếu dưới dạng như sau. Tiêu đề các mục nhỏ hơn cần được bố trí sang phải dưới dạng tiêu đề chính

Bước 2: Chọn cả 2 ô trong mục “Shop Category” và đặt tên vùng dữ liệu đó là “Shop_Category”

Bước 3: Làm tương tự với mục Electronics và Books

Bước 4: Chọn ô B1 và click vào phần Data Validation -> List -> Điền tên danh mục đại diện đầu tiên (“Shop_Category” chẳng hạn) và click OK

Bạn vừa hoàn tất việc tạo hộp thoại xổ xuống đầu tiên, tiếp theo ta cần tạo thêm 1 hộp thoại nữa

Bước 5: Chọn ô B2 -> Data Validation -> List -> Under Source -> Bạn cần liên kết với ô B1 bằng công thức hàm Indirect như sau:

Vậy là xong, kể từ giờ nếu bạn lựa chọn Electronics trong hộp thoại đầu tiên, bạn sẽ chỉ thấy các mục nhỏ hơn thuộc đồ điện tử mà thôi.

Làm tương tự đối với Books

Ghi chú cần lưu ý

Phương pháp trên có một số hạn chế như sau:

• Tên vùng dữ liệu mà bạn đặt không được chứa ký tự khoảng trắng ở trong. Chẳng hạn, bạn không nên đặt tên dưới dạng “Mobile Phones” mà thay vào đó phải là “Mobile_Phones”
• Bạn cũng không nên đặt tên dưới dạng FY15, FY16, Q116, Q216, v.v. bởi vì Excel sẽ mặc định rằng chúng là dải ô tính thông thường.

Tìm hiểu thêm: Tổng hợp thủ thuật Excel hay

Cách tìm các ô tính chứa Data Validation trong Excel

Để tìm các ô tính chứa Data Validation, bạn cần truy cập thanh tác vụ Home -> Click chọn Find & Select → Chọn Data Validation.

Khi đó bạn có thể thấy được toàn bộ những ô tính Excel chứa hộp thoại xổ xuống Data Validation.

Cách xóa hộp kiểm Data Validation trong Excel

Rất đơn giản, bạn chỉ cần bôi đen vùng dữ liệu có chứa hộp kiểm Data Validation, sau đó Click vào mục Data Validation -> Chọn tab Settings -> Trong hộp kiểm Allow, chọn “Any Value”

Một cách khác đơn giản hơn đó là sao chép và dán một ô dữ liệu trắng vào phần hộp kiểm Data Validation. Khi bạn click vào lựa chọn đó thì toàn bộ định dạng được kiểm soát bởi Data Validation sẽ bị ghi đè lên bởi định dạng ô trống.

Cách tạo hộp thoại xổ xuống Data Validation bằng Bảng điểu khiển Form của hộp thoại hỗn hợp

Bước 1: Chọn thanh tác vụ Developer -> Insert -> từ bảng điều khiển Form bạn chọn Hộp thoại hỗn hợp Combo Box -> Chèn hộp thoại bằng chuột

Bước 2: Thêm dữ liệu theo hàng dung để tham chiếu

Bước 3: Click chuột phải vào Combo Box vừa được tạo và chọn “Format Control”

Bước 4: Trong tab Control, chọn vùng dữ liệu nhập vào và chọn ô tính được liên kết đến -> Click OK

Cách sử dụng dữ liệu Combo Box

Không giống với hộp kiểm Data Validation, hộp thoại hỗn hợp không phụ thuộc vào bất cứ ô tính nào nên bạn không thể trực tiếp liên kết công thức đến để tham chiếu kết quả của hộp thoại được.

Do tính chất của một hộp thoại độc lập nên ta cần phải đi vòng để thực hiện được yêu cầu này:

Bước 1: Sau khi thiết lập xong cho hộp thoại hỗn hợp Combo box, chọn 1 giá trị bất kì trong danh sách xổ xuống

Bước 2: Bạn sẽ thấy có 1 dãy số trong ô liên kết

Bước 3: Quay lại ô tính chứa dữ liệu tham chiếu và thêm dãy số trên vào đối tượng cần tham chiếu

Bước 4: Chọn 1 ô gần với form và thực hiện việc tham chiếu bằng Vlookup thông qua dãy số trên

Bước 5: Sử dụng công thức Vlookup trong thanh công thức.

Học lập trình VBA trong Excel ở đâu?