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.

Video hướng dẫn thực hiện

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 thực hành excel

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

tao listbox cơ bản

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.

tạo listbox cơ bản p2

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

kết quả của tạo listbox cơ bản

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.

nhược điểm của cách tạo listbox cơ bản

Tạo Listbox nâng cao.

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

tao listbox nâng cao

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.

hàm offset trong excle

  • 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 counta trong excel

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

kết quả của  tạo hàm 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.

ưu điểm của listbox nâng cao

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 của listbox

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.

define name danh sách tỉnh

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.

xếp theo bản chữ cái trong excel

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.

define name mới

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

hàm match trong excel

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

tạo listbox thông minh

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 nâng cao trong excel

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

listbox trong excel

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éo danh sách trỏ xuống của listbox

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

kết quả tạo listbox 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 ĐIỂM BÀI VIẾT NÀY

vote data

Bình luận bài viết

0989.717.035
ZALO CHAT
TOP