Tạo Danh Sách Tự Động Listbox Trong Excel Cơ Bản, Nâng Cao

Danh sách tự động hạn chế số mục cho một trường đầu vào, tránh lỗi chính tả và làm cho dữ liệu nhập nhanh hơn. Nên hôm nay mình sẻ hướng dẫn các bạn tạo danh sách tự động listbox trong excel cơ bản, nâng cao.

Tạo Listbox cơ bản trong Excel.

Trước tiên bạn phải chuẩn bị File cần thiết để tạo danh sách tự động Listbox trong excel.

Bạn cần bôi đen phần cần tạo Listbox -> kích chọn Data -> chọn Data Validation.

Trong mục Data Validation -> Settings -> Allow (ta chọn List) -> Source (kích vào vô này) -> bôi đen toàn bộ thành phần để trở thành Listbox -> ok.

Kết quả của tạo Listbox cơ bản trong Excel.

Chú ý: cách tạo Listbox cơ bản này có nhược điểm là khi bạn thêm một thành phần nữa vào danh sách thì khi  tìm kiếm sẽ không hề được hiển thị trong danh sách trỏ xuống.

Tạo Listbox nâng cao.

Ta vẫn bôi đen phần cần tạo Listbox -> Data -> Data Validation.

Tại mục Data Validation -> Settings -> Allow (ta chọn List) -> Source (trong ô source ta sẽ thay bằng một hàm offset).

• Đầu tiên mình sẽ gõ: =offset mở ngoặc tích vào ô đầu tiên của danh sách phần để trở thành Listbox rồi phẩy 0 phẩy 0.

• Phẩy Counta mở ngoặc tích hết vào cột của phần để trở thành Listbox đóng ngoặc trừ 1 đóng ngoặc.

• Hàm Hoàn chỉnh:=OFFSET(Sheet2!$B$2,0,0,COUNTA(Sheet2!$B:$B)-1)

Kết quả tạo Listbox nâng cao.

Ưu điểm của cách tạo Listbox nâng cao là khi bạn thêm bất cứ một thành phần gì trong cột để trở thành Listbox thì đều được hiện lên ở Listbox.

Tạo Listbox nâng cao thông minh.

Khi tạo xong Listbox với hộp danh sách quá dài gây khó khăn trong lúc tìm kiếm thì một Listbox nâng cao thông minh là một lựa chọn tối ưu cho vấn đề này.
Phần này khá dài nên mình sẽ chia ra thành nhiều bước cho bạn dễ hình dung và thực hiện.
B1: Đầu tiên ta cần Define Name phần để trở thành Listbox: kích vào ô đầu ở phần để trở thành Listbox -> Formulas -> Define Name.

B2: Ở phần Name thì đặt tên theo tên phần để trở thành Listbox -> Refers To(dùng hàm offset).

• Hàm offset: =offset mở ngoặc tích vào ô đầu tiên của danh sách phần để trở thành Listbox rồi phẩy 0 phẩy 0 phẩy Counta mở ngoặc tích hết vào cột của phần để trở thành Listbox đóng ngoặc trừ 1 phẩy 1 đóng ngoặc -> ok.

Chú ý: trong phần để trở thành Listbox phải được xếp theo chiều tăng hoặc giảm dần theo bảng chữ cái. Cách thực hiện:
kích vào ô tiêu đề -> Data -> Filter -> chọn.

B3: Quay lại Sheet1 để Define một cái Name mới. kích vào vô đầu tiên của cột cần Listbox -> Formulas -> Define Name.

B4: trong Name (đặt tên) -> trong Refers dùng hàm offset.

• Hàm offset:  =offset mở ngoặc tích vào ô đầu tiên của danh sách phần để trở thành Listbox phẩy Match mở ngoặt kích vào vô đầu tiên của cột cần Listbox và bấm F4 ba lần để phía trong ngoặc trở thành (Sheet1!E5) không còn dấu $.

Tiếp tục gõ: &"*" phẩy danh sách tỉnh mình vừa tạo phẩy 0 đóng ngoặc lại trừ 1 phẩy 0 phẩy Countif mở ngoặc danh sách tỉnh mình vừa tạo phẩy kích vào vô đầu tiên của cột cần Listbox bấm F4 ba lần để phía trong ngoặc trở thành (Sheet1!E5) không còn dấu $ mở ngoặc kép * đóng ngoặc kép đóng ngoặc phẩy 1 đóng ngoặc -> ok.

B5: Vào Data Validation -> trong Allow chọn List -> trong Source bấm f3 chọn tên bạn vừa Define -> ok.

B6: Vào Data Validation -> Error Alert -> bỏ dấu tích -> ok.

B7: để các ô phía dưới có danh sách trỏ xuống bạn đặt chuột ngay góc của ô rồi kéo xuống.

Kết quả tạo Listbox nâng cao thông minh.

Cảm ơn bạn đã chọn xem bài viết của chúng tôi. Chúc bạn thực hành thành công.