Hướng dẫn cơ bản về danh sách thả xuống có thể tìm kiếm trong Excel
Việc tạo danh sách thả xuống trong Excel giúp đơn giản hóa việc nhập dữ liệu và giảm thiểu sai sót. Nhưng với bộ dữ liệu lớn hơn, việc cuộn qua các danh sách dài sẽ trở nên cồng kềnh. Sẽ không dễ dàng hơn nếu chỉ cần gõ và nhanh chóng xác định vị trí mặt hàng của bạn phải không? MỘT "danh sách thả xuống có thể tìm kiếm" mang đến sự tiện lợi này. Hướng dẫn này sẽ hướng dẫn bạn bốn phương pháp để thiết lập danh sách như vậy trong Excel.
- Danh sách thả xuống có thể tìm kiếm trong Excel 365
- Tạo danh sách thả xuống có thể tìm kiếm (dành cho Excel 2019 trở lên)
- Tạo danh sách thả xuống có thể tìm kiếm dễ dàng (cho tất cả các phiên bản Excel)
- Tạo danh sách thả xuống có thể tìm kiếm bằng Combo box và VBA (phức tạp hơn)
Danh sách thả xuống có thể tìm kiếm trong Excel 365
Excel 365 đã giới thiệu một tính năng được nhiều người mong đợi cho danh sách thả xuống xác thực dữ liệu của nó: khả năng tìm kiếm trong danh sách. Với chức năng tìm kiếm, người dùng có thể nhanh chóng định vị và chọn các mục một cách hiệu quả hơn. Sau khi chèn danh sách thả xuống như thông thường, bạn chỉ cần nhấp vào ô có danh sách thả xuống và bắt đầu nhập. Danh sách sẽ ngay lập tức lọc để khớp với văn bản đã nhập.
Trong trường hợp này, tôi gõ San trong ô và danh sách thả xuống sẽ lọc ra các thành phố bắt đầu bằng cụm từ tìm kiếm San, Chẳng hạn như San Francisco và thành phố San Diego. Sau đó, bạn có thể chọn kết quả bằng chuột hoặc sử dụng các phím mũi tên và nhấn Enter.
- Sản phẩm tìm kiếm được bắt đầu từ chữ cái đầu tiên của mỗi từ trong danh sách thả xuống. Nếu bạn nhập một ký tự không khớp với ký tự bắt đầu của bất kỳ từ nào, danh sách sẽ không hiển thị các mục trùng khớp.
- Tính năng này chỉ có trong phiên bản Excel 365 mới nhất.
- Nếu phiên bản Excel của bạn không hỗ trợ tính năng này thì ở đây chúng tôi khuyên bạn nên sử dụng Danh sách thả xuống có thể tìm kiếm tính năng của Kutools cho Excel. Không có giới hạn phiên bản Excel và sau khi được bật, bạn có thể dễ dàng tìm kiếm mục mong muốn trong danh sách thả xuống bằng cách chỉ cần nhập văn bản liên quan. Xem các bước chi tiết.
Tạo danh sách thả xuống có thể tìm kiếm (dành cho Excel 2019 trở lên)
Nếu bạn đang sử dụng phiên bản Excel 2019 trở lên, phương pháp trong phần này cũng có thể được sử dụng để tạo danh sách thả xuống có thể tìm kiếm được trong Excel.
Giả sử bạn đã tạo danh sách thả xuống trong ô A2 của Trang tính 2 (hình ảnh bên phải) bằng cách sử dụng dữ liệu trong phạm vi A2:A8 của Trang tính 1 (hình ảnh bên trái), hãy làm theo các bước sau để danh sách có thể tìm kiếm được.
Bước 1. Tạo cột trợ giúp liệt kê các mục tìm kiếm
Ở đây chúng tôi cần một cột trợ giúp để liệt kê các mục phù hợp với dữ liệu nguồn của bạn. Trong trường hợp này, tôi sẽ tạo cột trợ giúp trong cột D of Sheet1.
- Chọn ô đầu tiên D1 vào cột D và nhập tiêu đề cột, chẳng hạn như "Kết quả tìm kiếm" trong trường hợp này.
- Nhập công thức sau vào ô D2 và nhấn đăng ký hạng mục thi.
=FILTER(A2:A8,ISNUMBER(SEARCH(Sheet2!A2,A2:A8)),"Not Found")
- Trong công thức này, A2: A8 là phạm vi dữ liệu nguồn. Trang tính 2! A2 là vị trí của danh sách thả xuống, nghĩa là danh sách thả xuống nằm ở ô A2 của Sheet2. Hãy thay đổi chúng theo dữ liệu của riêng bạn.
- Nếu không có mục nào được chọn từ danh sách thả xuống trong A2 của Trang 2, công thức sẽ hiển thị tất cả các mục từ dữ liệu nguồn, như được mô tả trong hình trên. Ngược lại, nếu một mục được chọn, D2 sẽ hiển thị mục đó dưới dạng kết quả của công thức.
Bước 2: Cấu hình lại danh sách thả xuống
- Chọn ô danh sách xổ xuống (trong trường hợp này tôi chọn ô A2 của Sheet2), sau đó vào Select Ngày > Xác nhận dữ liệu > Xác nhận dữ liệu.
- Trong tạp chí Xác nhận dữ liệu hộp thoại, bạn cần cấu hình như sau.
- Bên dưới Cài đặt tab, nhấp vào nút trong nguồn cái hộp.
- Sản phẩm Xác nhận dữ liệu hộp thoại sẽ chuyển hướng đến Trang tính 1, chọn ô (ví dụ: D2) có công thức từ Bước 1, thêm ô # và nhấp vào Đóng .
- Tới Cảnh báo lỗi tab, bỏ chọn Hiển thị cảnh báo lỗi sau khi nhập dữ liệu không hợp lệ hộp kiểm và cuối cùng nhấp vào OK để lưu các thay đổi.
Kết quả
Danh sách thả xuống trong ô A2 của Sheet2 hiện có thể tìm kiếm được. Nhập văn bản vào ô, nhấp vào mũi tên thả xuống để mở rộng danh sách thả xuống và bạn sẽ thấy danh sách được lọc ngay lập tức để khớp với văn bản đã nhập.
- Phương pháp này chỉ có sẵn cho Excel 2019 và các phiên bản mới hơn.
- Phương pháp này chỉ hoạt động trên một ô danh sách thả xuống tại một thời điểm. Để làm cho danh sách thả xuống có thể tìm kiếm được từ các ô từ A3 đến A8 trong Trang tính 2, các bước nói trên phải được lặp lại cho từng ô.
- Khi bạn nhập văn bản vào ô danh sách thả xuống, danh sách thả xuống không tự động mở rộng, bạn cần nhấp vào mũi tên thả xuống để mở rộng thủ công.
Tạo danh sách thả xuống có thể tìm kiếm dễ dàng (cho tất cả các phiên bản Excel)
Do những hạn chế khác nhau của các phương pháp trên, đây là một công cụ rất hiệu quả dành cho bạn - Kutools cho Excel's Làm cho danh sách thả xuống có thể tìm kiếm được, tự động bật lêntính năng. Tính năng này có sẵn trong tất cả các phiên bản Excel và cho phép bạn dễ dàng tìm kiếm mục mong muốn trong danh sách thả xuống với một thiết lập đơn giản.
Sau tải xuống và cài đặt Kutools cho Excel, lựa chọn Kutools > Danh sách thả xuống > Làm cho danh sách thả xuống có thể tìm kiếm được, tự động bật lên để kích hoạt tính năng này. bên trong Làm cho danh sách thả xuống có thể tìm kiếm được hộp thoại, bạn cần:
- Chọn phạm vi chứa danh sách thả xuống cần đặt làm danh sách thả xuống có thể tìm kiếm.
- Nhấp chuột OK để hoàn thành cài đặt.
Kết quả
Khi bạn bấm vào ô danh sách thả xuống trong phạm vi đã chỉ định, hộp danh sách sẽ xuất hiện ở bên phải. Nhập văn bản để lọc danh sách ngay lập tức, sau đó chọn một mục hoặc sử dụng các phím mũi tên và nhấn đăng ký hạng mục thi để thêm nó vào ô.
- Tính năng này hỗ trợ tìm kiếm từ bất kỳ vị trí nào trong các từ. Điều này có nghĩa là ngay cả khi bạn nhập ký tự ở giữa hoặc cuối từ, các mục phù hợp vẫn sẽ được tìm thấy và hiển thị, mang lại trải nghiệm tìm kiếm toàn diện và thân thiện hơn với người dùng.
- Để biết thêm về tính năng này, vui lòng truy cập trang này.
- Để áp dụng tính năng này, vui lòng tải xuống và cài đặt Kutools cho Excel đầu tiên.
Tạo danh sách thả xuống có thể tìm kiếm bằng Combo box và VBA (phức tạp hơn)
Nếu bạn chỉ muốn tạo danh sách thả xuống có thể tìm kiếm mà không chỉ định loại danh sách thả xuống cụ thể. Phần này cung cấp một cách tiếp cận khác: sử dụng Combo box có mã VBA để hoàn thành nhiệm vụ.
Giả sử bạn có danh sách tên quốc gia trong cột A như trong ảnh chụp màn hình bên dưới và bây giờ bạn muốn sử dụng chúng làm dữ liệu nguồn của danh sách tìm kiếm thả xuống, bạn có thể làm như sau để hoàn thành.
Bạn cần chèn hộp Tổ hợp thay vì danh sách thả xuống xác thực dữ liệu trong trang tính của mình.
- Nếu Nhà phát triển tab không hiển thị trên ruy-băng, bạn có thể bật Nhà phát triển tab như sau.
- Trong Excel 2010 hoặc phiên bản mới hơn, hãy bấm vào Tập tin > Các lựa chọn. Và trong Tùy chọn Excel hộp thoại, nhấn Tuy Biên ruy-băng trong ngăn bên trái. Đi tới hộp danh sách Tùy chỉnh ruy-băng, chọn Nhà phát triển và sau đó bấm vào OK cái nút. Xem ảnh chụp màn hình:
- Trong Excel 2007, bấm Office nút> Tùy chọn Excel. Trong Tùy chọn Excel hộp thoại, nhấn Phổ biến trong ngăn bên trái, chọn Hiển thị tab Nhà phát triển trong Ribbon và cuối cùng nhấp vào OK .
- Sau khi hiển thị Nhà phát triển tab, nhấp vào Nhà phát triển > Chèn > Hộp kết hợp.
- Vẽ một Combo box trong bảng tính, nhấp chuột phải vào nó rồi chọn Bất động sản từ menu chuột phải.
- Trong tạp chí Bất động sản hộp thoại, bạn cần:
- Chọn Sai trong Tự động chọn từ cánh đồng;
- Chỉ định một ô trong ô được liên kết cánh đồng. Trong trường hợp này, chúng tôi nhập A12;
- Chọn 2-fmMatchEntryKhông có trong trận đấunhập cuộc cánh đồng;
- Kiểu Danh sách thả xuống vào Danh sáchFillRange cánh đồng;
- Đóng Bất động sản hộp thoại. Xem ảnh chụp màn hình:
- Bây giờ hãy tắt chế độ thiết kế bằng cách nhấp vào Nhà phát triển > Chế độ thiết kế.
- Chọn một ô trống chẳng hạn như C2, nhập công thức bên dưới và nhấn đăng ký hạng mục thi. Họ kéo Xử lý Tự động điền của nó xuống ô C9 để tự động điền vào các ô có cùng công thức. Xem ảnh chụp màn hình:
=--ISNUMBER(IFERROR(SEARCH($A$12,A2,1),""))
Chú ý:- $ A $ 12 là ô mà bạn đã chỉ định nó là ô được liên kết trong bước 4;
- Sau khi hoàn thành các bước trên, bây giờ bạn có thể kiểm tra: nhập chữ cái C vào hộp tổ hợp, sau đó bạn có thể thấy rằng các ô công thức tham chiếu đến các ô chứa ký tự C được điền số 1.
- Chọn ô D2, nhập công thức bên dưới và nhấn đăng ký hạng mục thi. Sau đó kéo Xử lý Tự động điền của nó xuống ô D9.
=IF(C2=1,COUNTIF($C$2:C2,1),"")
- Chọn ô E2, nhập công thức bên dưới và nhấn đăng ký hạng mục thi. Sau đó, kéo Tay cầm Tự động điền của nó xuống E9 để áp dụng cùng một công thức.
=IFERROR(INDEX($A$2:$A$9,MATCH(ROWS($D$2:D2),$D$2:$D$9,0)),"")
- Bây giờ bạn cần tạo một phạm vi tên. Xin vui lòng bấm vào Công thức > Xác định tên.
- Trong tạp chí Tên mới hộp thoại, loại Danh sách thả xuống trong Họ tên , hãy nhập công thức bên dưới vào Đề cập đến và sau đó bấm vào OK .
=$E$2:INDEX($E$2:$E$9,MAX($D$2:$D$9),1)
- Bây giờ, hãy bật chế độ thiết kế bằng cách nhấp vào Nhà phát triển > Chế độ thiết kế. Sau đó nhấp đúp vào hộp Tổ hợp để mở Microsoft Visual Basic cho các ứng dụng cửa sổ.
- Sao chép và dán mã VBA bên dưới vào trình chỉnh sửa Mã.
Mã VBA: làm cho danh sách thả xuống có thể tìm kiếm được
Private Sub ComboBox1_GotFocus() ComboBox1.ListFillRange = "DropDownList" Me.ComboBox1.DropDown End Sub
- Nhấn Khác + Q chìa khóa để đóng Microsoft Visual Basic cho các ứng dụng cửa sổ.
Kể từ bây giờ, khi một ký tự được nhập vào hộp tổ hợp, nó sẽ thực hiện tìm kiếm mờ và sau đó liệt kê các giá trị có liên quan trong danh sách.
Các bài liên quan:
Tự động điền khi nhập trong Excel danh sách thả xuống
Nếu bạn có danh sách thả xuống xác thực dữ liệu với các giá trị lớn, bạn cần cuộn xuống danh sách chỉ để tìm giá trị phù hợp hoặc nhập trực tiếp toàn bộ từ vào hộp danh sách. Nếu có phương pháp cho phép tự động hoàn thành khi nhập ký tự đầu tiên trong danh sách thả xuống, mọi thứ sẽ trở nên dễ dàng hơn. Hướng dẫn này cung cấp phương pháp để giải quyết vấn đề.
Tạo danh sách thả xuống từ một sổ làm việc khác trong Excel
Khá dễ dàng để tạo danh sách sổ xuống xác thực dữ liệu giữa các trang tính trong sổ làm việc. Nhưng nếu dữ liệu danh sách bạn cần để xác thực dữ liệu nằm trong một sổ làm việc khác, bạn sẽ làm gì? Trong hướng dẫn này, bạn sẽ học cách tạo danh sách drop fown từ một sổ làm việc khác trong Excel một cách chi tiết.
Tạo danh sách thả xuống có thể tìm kiếm trong Excel
Đối với một danh sách thả xuống với nhiều giá trị, việc tìm một giá trị thích hợp không phải là một công việc dễ dàng. Trước đây, chúng tôi đã giới thiệu một phương pháp tự động hoàn thành danh sách thả xuống khi nhập ký tự đầu tiên vào hộp thả xuống. Bên cạnh chức năng tự động hoàn thành, bạn cũng có thể làm cho danh sách thả xuống có thể tìm kiếm được để nâng cao hiệu quả làm việc trong việc tìm kiếm các giá trị thích hợp trong danh sách thả xuống. Để làm cho danh sách thả xuống có thể tìm kiếm được, hãy thử phương pháp trong hướng dẫn này.
Tự động điền các ô khác khi chọn giá trị trong danh sách thả xuống của Excel
Giả sử bạn đã tạo một danh sách thả xuống dựa trên các giá trị trong phạm vi ô B8: B14. Khi bạn chọn bất kỳ giá trị nào trong danh sách thả xuống, bạn muốn các giá trị tương ứng trong phạm vi ô C8: C14 được tự động điền vào một ô đã chọn. Để giải quyết vấn đề, các phương pháp trong hướng dẫn này sẽ giúp bạn.
Các công cụ năng suất văn phòng tốt nhất
Kutools cho Excel - Giúp bạn nổi bật giữa đám đông
Kutools cho Excel tự hào có hơn 300 tính năng, Đảm bảo rằng những gì bạn cần chỉ là một cú nhấp chuột...
Tab Office - Bật tính năng Đọc và Chỉnh sửa theo Tab trong Microsoft Office (bao gồm Excel)
- Một giây để chuyển đổi giữa hàng chục tài liệu đang mở!
- Giảm hàng trăm cú click chuột cho bạn mỗi ngày, tạm biệt bàn tay chuột.
- Tăng năng suất của bạn lên 50% khi xem và chỉnh sửa nhiều tài liệu.
- Mang các tab hiệu quả đến Office (bao gồm Excel), giống như Chrome, Edge và Firefox.