Tạo danh sách tự động Listbox trong Excel cơ bản, nâng cao

0

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

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

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.

file thuc hanh

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.

tao lisbox co ban p2

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.

nhuoc diem cua cach tao lisbox co ban

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.

ham offset

  • 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.

ket qua cua ham offset

Ư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.

define name cua

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.

xep theo ban chu cai

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 $.

ham match

  • 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.

listbox nang cao trong

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.

keo danh sach tro xuong cua

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

Bạn có góp ý hay chia sẽ đừng quên để lại phía dưới phần bình luận. 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.

CHO

Bạn cũng có thể thích