Tạo hộp tìm kiếm trong Excel – Hướng dẫn từng bước
Tạo hộp tìm kiếm trong Excel sẽ nâng cao chức năng của bảng tính bằng cách giúp lọc và truy cập dữ liệu cụ thể một cách nhanh chóng dễ dàng hơn. Hướng dẫn này bao gồm một số phương pháp để triển khai hộp tìm kiếm, phục vụ cho các phiên bản Excel khác nhau. Cho dù bạn là người mới bắt đầu hay người dùng nâng cao, các bước này sẽ giúp bạn thiết lập hộp tìm kiếm động bằng cách sử dụng các tính năng như hàm LỌC, Định dạng có điều kiện và các công thức khác nhau.
- Dễ dàng tạo hộp tìm kiếm với Chức năng FILTER (có sẵn trong Excel 2019 trở lên, Excel cho Microsoft 365)
- Tạo hộp tìm kiếm bằng cách sử dụng Định dạng có điều kiện (có sẵn trong tất cả các phiên bản Excel)
- Tạo hộp tìm kiếm với kết hợp công thức (có sẵn trong tất cả các phiên bản Excel)
Dễ dàng tạo hộp tìm kiếm với chức năng LỌC
- Chức năng này tự động cập nhật đầu ra khi dữ liệu của bạn thay đổi.
- Hàm FILTER có thể trả về số lượng kết quả bất kỳ, từ một hàng đến hàng nghìn, tùy thuộc vào số lượng mục trong tập dữ liệu của bạn khớp với tiêu chí bạn đã đặt.
Sau đây mình sẽ hướng dẫn các bạn cách sử dụng hàm FILTER để tạo hộp tìm kiếm trong Excel.
Bước 1: Chèn hộp văn bản và định cấu hình thuộc tính
- Tới Nhà phát triển tab, nhấp vào Chèn > THộp mở rộng (Điều khiển ActiveX).
Mẹo: Nếu Nhà phát triển tab không được hiển thị trên ribbon, bạn có thể kích hoạt nó bằng cách làm theo hướng dẫn trong hướng dẫn này: Làm cách nào để hiển thị / hiển thị tab nhà phát triển trong Excel Ribbon?
- Con trỏ sẽ biến thành hình chữ thập, khi đó bạn cần kéo con trỏ để vẽ hộp văn bản tại vị trí trong bảng tính mà bạn muốn đặt hộp văn bản. Sau khi vẽ hộp văn bản, thả chuột.
- Nhấp chuột phải vào hộp văn bản và chọn Bất động sản từ trình đơn ngữ cảnh.
- Trong tạp chí Bất động sản khung, liên kết hộp văn bản với một ô bằng cách nhập tham chiếu ô vào ô được liên kết cánh đồng. Ví dụ: gõ "J2" đảm bảo rằng mọi dữ liệu được nhập vào hộp văn bản sẽ tự động cập nhật trong ô J2 và ngược lại.
- Nhấn vào Chế độ thiết kế theo Nhà phát triển để thoát khỏi Chế độ thiết kế.
Hộp văn bản bây giờ cho phép bạn nhập văn bản.
Bước 2: Áp dụng chức năng FILTER
- Trước khi sử dụng chức năng LỌC, hãy sao chép hàng tiêu đề ban đầu sang một vùng mới. Ở đây tôi đặt dòng tiêu đề dưới hộp tìm kiếm.
Mẹo: Cách tiếp cận này cho phép người dùng nhìn rõ kết quả dưới cùng tiêu đề cột với dữ liệu gốc.
- Chọn ô bên dưới tiêu đề đầu tiên (ví dụ: I5 trong ví dụ này), hãy nhập công thức sau vào đó và nhấn nút đăng ký hạng mục thi phím để nhận kết quả.
=FILTER(Sheet2!$A$5:$G$281,Sheet2!$B$5:$B$281=J2,"No data found")
Như trong ảnh chụp màn hình ở trên, do hộp văn bản hiện không có dữ liệu đầu vào nên công thức sẽ hiển thị kết quả "Không tìm thấy dữ liệu nào"trong I5.
- Trong công thức này:
- Trang 2!$A$5:$G$281: $A$5:$G$281là phạm vi dữ liệu mà bạn muốn lọc trên Sheet2.
- Trang2!$B$5:$B$281=J2: Phần này xác định các tiêu chí được sử dụng để lọc phạm vi. Nó kiểm tra từng ô trong cột B, từ hàng 5 đến hàng 281 trên Sheet2 để xem nó có bằng giá trị trong ô J2 hay không. J2 là ô được liên kết với hộp tìm kiếm.
- Không tìm thấy dữ liệu nào: Nếu hàm FILTER không tìm thấy bất kỳ hàng nào mà giá trị ở cột B bằng giá trị ở ô J2 thì sẽ trả về "Không tìm thấy dữ liệu".
- Phương pháp này là trường hợp không nhạy cảm, nghĩa là nó sẽ khớp với văn bản bất kể bạn nhập chữ hoa hay chữ thường.
Kết quả: Kiểm tra hộp tìm kiếm
Bây giờ chúng ta hãy kiểm tra hộp tìm kiếm. Trong ví dụ này, khi tôi nhập tên khách hàng vào ô tìm kiếm, kết quả tương ứng sẽ được lọc và hiển thị ngay lập tức.
Tạo hộp tìm kiếm bằng Định dạng có điều kiện
Định dạng có điều kiện có thể được sử dụng để đánh dấu dữ liệu phù hợp với cụm từ tìm kiếm, gián tiếp tạo hiệu ứng hộp tìm kiếm. Phương pháp này không lọc dữ liệu nhưng hướng dẫn bạn một cách trực quan đến các ô có liên quan. Phần này sẽ hướng dẫn các bạn cách tạo hộp tìm kiếm bằng Định dạng có điều kiện trong Excel.
Bước 1: Chèn hộp văn bản và định cấu hình thuộc tính
- Tới Nhà phát triển tab, nhấp vào Chèn > THộp mở rộng (Điều khiển ActiveX).
Mẹo: Nếu Nhà phát triển tab không được hiển thị trên ribbon, bạn có thể kích hoạt nó bằng cách làm theo hướng dẫn trong hướng dẫn này: Làm cách nào để hiển thị / hiển thị tab nhà phát triển trong Excel Ribbon?
- Con trỏ sẽ biến thành hình chữ thập, khi đó bạn cần kéo con trỏ để vẽ hộp văn bản tại vị trí trong bảng tính mà bạn muốn đặt hộp văn bản. Sau khi vẽ hộp văn bản, thả chuột.
- Nhấp chuột phải vào hộp văn bản và chọn Bất động sản từ trình đơn ngữ cảnh.
- Trong tạp chí Bất động sản khung, liên kết hộp văn bản với một ô bằng cách nhập tham chiếu ô vào ô được liên kết cánh đồng. Ví dụ: gõ "J3" đảm bảo rằng mọi dữ liệu được nhập vào hộp văn bản sẽ tự động cập nhật trong ô J3 và ngược lại.
- Nhấn vào Chế độ thiết kế theo Nhà phát triển để thoát khỏi Chế độ thiết kế.
Hộp văn bản bây giờ cho phép bạn nhập văn bản.
Bước 2: Áp dụng Định dạng có điều kiện để tìm kiếm dữ liệu
- Chọn toàn bộ phạm vi dữ liệu cần tìm kiếm. Ở đây tôi chọn phạm vi A3:G279.
- Bên dưới Trang chủ tab, nhấp vào Định dạng có điều kiện > Quy tắc mới.
- Trong tạp chí Quy tắc định dạng mới hộp thoại:
- Chọn Sử dụng công thức để xác định ô cần định dạng trong Chọn một loại quy tắc tùy chọn.
- Nhập công thức sau vào ô Định dạng các giá trị trong đó công thức này đúng cái hộp.
=$B3=$J$3
Ở đây, $ B3 đại diện cho ô đầu tiên trong cột bạn muốn khớp với tiêu chí tìm kiếm trong phạm vi đã chọn và $ J $ 3 là ô được liên kết với hộp tìm kiếm. - Nhấn vào Định dạng để chỉ định màu tô cho kết quả tìm kiếm.
- Nhấn vào OK cái nút. Xem ảnh chụp màn hình:
Kết quả
Bây giờ chúng ta hãy kiểm tra hộp tìm kiếm. Trong ví dụ này, khi tôi nhập tên khách hàng vào hộp tìm kiếm, các hàng tương ứng chứa khách hàng này trong cột B sẽ ngay lập tức được đánh dấu bằng màu tô được chỉ định.
Tạo hộp tìm kiếm với các kết hợp công thức
Nếu bạn không sử dụng phiên bản Excel mới nhất và không muốn chỉ đánh dấu các hàng thì phương pháp được mô tả trong phần này có thể hữu ích. Bạn có thể sử dụng kết hợp các công thức Excel để tạo hộp tìm kiếm chức năng trong bất kỳ phiên bản Excel nào. Vui lòng làm theo các bước dưới đây.
Bước 1: Tạo danh sách các giá trị duy nhất từ cột tìm kiếm
- Trong trường hợp này, tôi chọn và sao chép phạm vi B4: B281 sang một bảng tính mới.
- Sau khi dán phạm vi vào một trang tính mới, hãy giữ nguyên dữ liệu đã dán, đi tới Ngày Tab và chọn Loại bỏ các bản sao.
- Trong phần mở đầu Loại bỏ các bản sao hộp thoại, nhấp vào OK .
- A Microsoft Excel sau đó hộp nhắc sẽ bật lên để hiển thị số lượng bản sao đã được xóa. Nhấp chuột OK.
- Sau khi loại bỏ các giá trị trùng lặp, hãy chọn tất cả các giá trị duy nhất trong danh sách, ngoại trừ tiêu đề và gán tên cho phạm vi này bằng cách nhập tên đó vào trường Họ tên hộp. Ở đây tôi đặt tên cho phạm vi là Khách hàng.
Bước 2: Chèn hộp tổ hợp và định cấu hình thuộc tính
- Quay lại bảng tính chứa tập dữ liệu bạn muốn tìm kiếm. đi đến Nhà phát triển tab, nhấp vào Chèn > Hộp tổ hợp (Điều khiển ActiveX).
Mẹo: Nếu Nhà phát triển tab không được hiển thị trên ribbon, bạn có thể kích hoạt nó bằng cách làm theo hướng dẫn trong hướng dẫn này: Làm cách nào để hiển thị / hiển thị tab nhà phát triển trong Excel Ribbon?
- Con trỏ sẽ biến thành hình chữ thập, sau đó bạn cần kéo con trỏ để vẽ hộp tổ hợp tại vị trí trong bảng tính mà bạn muốn đặt hộp tìm kiếm. Sau khi vẽ xong combo box thì thả chuột.
- Nhấp chuột phải vào hộp tổ hợp và chọn Bất động sản từ trình đơn ngữ cảnh.
- Trong tạp chí Bất động sản ngăn:
- Liên kết hộp tổ hợp với một ô bằng cách nhập tham chiếu ô vào ô ô được liên kết cánh đồng. Cô ấy tôi gõ "M2".
Mẹo: Chỉ định trường này để đảm bảo rằng mọi dữ liệu được nhập vào hộp tổ hợp sẽ tự động cập nhật trong ô M2 và ngược lại.
- Trong tạp chí Danh sáchFillRange lĩnh vực, nhập tên phạm vi bạn đã chỉ định cho danh sách duy nhất ở Bước 1.
- Thay đổi trận đấunhập cuộc trường để 2 – fmMatchEntryNone.
- Đóng Bất động sản cửa sổ.
- Liên kết hộp tổ hợp với một ô bằng cách nhập tham chiếu ô vào ô ô được liên kết cánh đồng. Cô ấy tôi gõ "M2".
- Nhấn vào Chế độ thiết kế theo Nhà phát triển để thoát khỏi Chế độ thiết kế.
Bây giờ bạn có thể chọn bất kỳ mục nào từ hộp tổ hợp hoặc nhập văn bản để tìm kiếm.
Bước 3: Áp dụng công thức
- Tạo ba cột trợ giúp liền kề với phạm vi dữ liệu gốc. Xem ảnh chụp màn hình:
- Trong tế bào (H5) dưới tiêu đề của cột trợ giúp đầu tiên, nhập công thức sau và nhấn đăng ký hạng mục thi.
=ROWS($B$5:B5)
Đây B5 là ô chứa tên người quản lý đầu tiên của cột cần tìm kiếm. - Nhấp đúp vào góc dưới bên phải của ô công thức, ô sau sẽ tự động điền công thức tương tự.
- Trong tế bào (I5) dưới tiêu đề cột trợ giúp thứ hai, nhập công thức sau và nhấn đăng ký hạng mục thi. Và sau đó nhấp đúp chuột vào góc dưới bên phải của ô công thức để tự động điền công thức tương tự vào các ô bên dưới.
=IF(ISNUMBER(SEARCH($M$2,B5)),H5,"")
Đây M2 là ô được liên kết với hộp tổ hợp. - Trong tế bào (J5) dưới tiêu đề cột trợ giúp thứ ba, nhập công thức sau và nhấn đăng ký hạng mục thi. Và sau đó nhấp đúp chuột vào góc dưới bên phải của ô công thức để tự động điền công thức tương tự vào các ô bên dưới.
=IFERROR(SMALL($I$5:$I$281,H5),"")
- Sao chép hàng tiêu đề ban đầu sang một khu vực mới. Ở đây tôi đặt dòng tiêu đề dưới hộp tìm kiếm.
- Chọn ô bên dưới tiêu đề đầu tiên (ví dụ: L5 trong ví dụ này), hãy nhập công thức sau vào đó và nhấn phím Enter.
=IFERROR(INDEX($A$5:$G$281,$J5,COLUMNS($L$4:L4)),"")
Đây A5: G281 là toàn bộ phạm vi dữ liệu mà bạn muốn hiển thị trong ô kết quả. - Chọn ô công thức này, kéo Điền vào Xử lý sang phải rồi xuống dưới để áp dụng công thức cho các cột, hàng tương ứng.
Chú ý:
- Vì không có dữ liệu đầu vào trong hộp tìm kiếm nên kết quả của công thức sẽ hiển thị dữ liệu thô.
- Phương pháp này không phân biệt chữ hoa chữ thường, nghĩa là nó sẽ khớp với văn bản bất kể bạn nhập chữ hoa hay chữ thường.
Kết quả
Bây giờ chúng ta hãy kiểm tra hộp tìm kiếm. Trong ví dụ này, khi tôi nhập hoặc chọn tên khách hàng từ combo box, các hàng tương ứng chứa tên khách hàng đó ở cột B sẽ được lọc và hiển thị ngay trong phạm vi kết quả.
Tạo hộp tìm kiếm trong Excel có thể cải thiện đáng kể cách bạn tương tác với dữ liệu của mình, làm cho bảng tính của bạn trở nên năng động và thân thiện hơn với người dùng. Cho dù bạn chọn sự đơn giản của hàm LỌC, sự hỗ trợ trực quan của Định dạng có điều kiện hay tính linh hoạt của các kết hợp công thức, mỗi phương pháp đều cung cấp các công cụ có giá trị để nâng cao khả năng thao tác dữ liệu của bạn. Hãy thử nghiệm những kỹ thuật này để tìm ra kỹ thuật nào phù hợp nhất với nhu cầu và kịch bản dữ liệu cụ thể của bạn. Đối với những người mong muốn tìm hiểu sâu hơn về các khả năng của Excel, trang web của chúng tôi tự hào có rất nhiều hướng dẫn. Khám phá thêm các mẹo và thủ thuật Excel tại đây.
Bài viết liên quan
Hướng dẫn cơ bản về danh sách thả xuống có thể tìm kiếm trong Excel
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 thả xuống có thể tìm kiếm trong Excel.
Tìm kiếm và đánh dấu kết quả tìm kiếm trong Excel
Bài viết này giới thiệu hai cách khác nhau giúp bạn tìm kiếm trong Excel và bôi đen kết quả cùng một lúc.
Tìm giá trị khớp bằng cách tìm kiếm lên trên trong Excel
Thông thường, chúng ta sẽ tìm các giá trị trùng khớp từ trên xuống trong một cột Excel. Làm cách nào để tìm giá trị phù hợp bằng cách tìm kiếm lên trên? Bài viết này sẽ chỉ cho bạn các phương pháp để đạt được nó.
Giá trị tìm kiếm trong tất cả các sổ làm việc Excel đang mở
Bài viết này sẽ chỉ cho bạn các phương pháp tìm kiếm giá trị hoặc văn bản trong sổ làm việc hiện tại cũng như tất cả các sổ làm việc đang mở.
Công cụ năng suất văn phòng tốt nhất
Nâng cao kỹ năng Excel của bạn với Kutools for Excel và trải nghiệm hiệu quả hơn bao giờ hết. Kutools for Excel cung cấp hơn 300 tính năng nâng cao để tăng năng suất và tiết kiệm thời gian. Bấm vào đây để có được tính năng bạn cần nhất...
Tab Office mang lại giao diện Tab cho Office và giúp công việc của bạn trở nên dễ dàng hơn nhiều
- Cho phép chỉnh sửa và đọc theo thẻ trong Word, Excel, PowerPoint, Publisher, Access, Visio và Project.
- Mở và tạo nhiều tài liệu trong các tab mới của cùng một cửa sổ, thay vì trong các cửa sổ mới.
- Tăng 50% năng suất của bạn và giảm hàng trăm cú nhấp chuột cho bạn mỗi ngày!