Hướng dẫn chi tiết cách tạo ô tìm kiếm trong Excel từ A-Z

Tạo hộp tìm kiếm trong Excel giúp tăng cường 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ể dễ dàng hơn và nhanh chóng. Cách tìm kiếm dữ liệu này sẽ nâng cao hơn khi sử dụng hàm VLOOKUP hay công cụ FILTER, FIND. Hãy cùng Phúc Anh theo dõi ngay sau đây nhé!

Tạo ô tìm kiếm với chức năng FILTER trong Excel

Hàm FILTER cung cấp một cách đơn giản để tìm kiếm và lọc dữ liệu động. Lợi ích của việc sử dụng hàm FILTER là:

• 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ề bất kỳ số lượng kết quả nào, 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.

Cài đặt hộp văn bản và cấu hình thuộc tính để sử dụng

- Vào tab Developer, nhấp vào Insert => Text Box (ActiveX Control)

• Tham khảo: Làm thế nào để hiển thị tab Developer trên thanh công cụ Excel?

Bước 1: Khi con trỏ biến thành hình chữ thập, kéo con trỏ để tạo hộp văn bản tại vị trí bạn muốn trong bảng tính.

Bước 2: Sau khi hoàn tất việc vẽ, thả chuột để kết thúc.

Bước 3: Nhấp chuột phải vào hộp văn bản và chọn "Properties" từ danh sách thả xuống.

Bước 4: Trong mục Properties, tìm trường "LinkedCell" và nhập tham chiếu ô mà bạn muốn liên kết, ví dụ: "J2". Điều này sẽ đảm bảo rằng dữ liệu nhập vào hộp văn bản sẽ tự động cập nhật trong ô J2 và ngược lại.

Bước 5: Nhấn vào "Design Mode" trong tab Developer để thoát khỏi chế độ thiết kế.

Sử dụng hàm FILTER tạo ô tìm kiếm 

- Sao chép hàng tiêu đề: Trước tiên, hãy sao chép hàng tiêu đề từ bảng gốc vào một vùng mới.

- Nhập công thức: Chọn ô ngay dưới tiêu đề đầu tiên (ví dụ: I5 trong trường hợp này). Nhập công thức vào ô đó và nhấn Enter để nhận kết quả. 

=FILTER(Sheet2!$A$5:$G$281,Sheet2!$B$5:$B$281=J2,"No data found")

- Nếu không có dữ liệu đầu vào, như trong ảnh chụp màn hình, ô I5 sẽ hiển thị "No data found".

- Giải thích công thức:

• Sheet2!$A$5:$G$281: Đây là phạm vi dữ liệu mà bạn muốn lọc trên Sheet2.
• Sheet2!$B$5:$B$281=J2: Phần này xác định tiêu chí lọc. Nó kiểm tra từng ô trong cột B từ hàng 5 đến 281 trên Sheet2 để xem có bằng giá trị trong ô J2 hay không. Ô J2 là nơi liên kết với hộp tìm kiếm.

- Kết quả không tìm thấy dữ liệu: Nếu hàm FILTER không tìm thấy hàng nào có giá trị trong cột B trùng với giá trị trong ô J2, nó sẽ trả về thông báo "No data found".

Chú ý về phân biệt chữ hoa chữ thường: Phương pháp này không phân biệt chữ hoa và chữ thường, nghĩa là nó sẽ khớp với văn bản bất kể cách viết.

- Kiểm tra hộp tìm kiếm: Bây giờ, hãy thử nghiệm với hộp tìm kiếm. Khi bạn 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 ô tìm kiếm bằng Conditional Formatting - Định dạng có điều kiện

Cài đặt hộp văn bản và cấu hình thuộc tính

Cài đặt hộp văn bản và cấu hình tương tự hướng dẫn ở trên. Tuy nhiên với ví dụ này, ta sử dụng ô tham chiếu là J3.

Sử dụng Định dạng có điều kiện để tìm kiếm dữ liệu

- Chọn phạm vi dữ liệu: Đầu tiên, hãy chọn toàn bộ vùng dữ liệu mà bạn muốn tìm kiếm. Trong ví dụ này, ta chọn từ A3 đến G279.

- Truy cập Định dạng có điều kiện: Trong tab Home, nhấn vào Conditional Formatting => New Rule

- Thiết lập quy tắc: Trong hộp thoại New Formatting Rule, chọn tùy chọn "Use a formula to determine which cells to format".

- Nhập công thức: Điền công thức vào ô "Format values where this formula is true". 

=$B3=$J$3

Ở đây, $B3 là ô đầu tiên trong cột mà bạn muốn so sánh với tiêu chí tìm kiếm, trong khi $J$3 là ô liên kết với hộp tìm kiếm.

- Chọn định dạng: Nhấn vào nút Format để chọn màu tô cho các kết quả tìm kiếm.

- Hoàn tất: Nhấn nút OK để lưu lại quy tắc.

- Kiểm tra kết quả: Bây giờ, hãy thử nghiệm với hộp tìm kiếm. Khi bạn nhập tên khách hàng vào ô tìm kiếm, các hàng tương ứng có chứa tên này trong cột B sẽ được tô sáng ngay lập tức với màu đã chọn.

Lưu ý: Phương pháp này không phân biệt chữ hoa và chữ thường, vì vậy nó sẽ khớp với văn bản bất kể cách viết.

Tạo ô tìm kiếm bằng kết hợp công thức toán họ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ỉ tô sáng các hàng, 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.

Hướng dẫn xử lý dữ liệu trùng lặp trong Excel

- Sao chép dữ liệu: Trong trường hợp này, ta chọn và sao chép phạm vi B4:B281 sang một bảng tính mới.

- Dán dữ liệu: Sau khi dán dữ liệu vào bảng tính mới, hãy giữ nguyên các giá trị đã dán.

- Xóa mục trùng lặp: Chuyển đến tab Data và chọn tùy chọn Remove Duplicates. 

Hộp thông báo của Microsoft Excel sẽ hiện ra, cho biết số lượng bản sao đã bị xóa. Nhấn OK để xác nhận.

- Gán tên cho phạm vi: Sau khi loại bỏ các mục trùng lặp, hãy chọn tất cả các giá trị duy nhất trong danh sách (không bao gồm tiêu đề) và gán tên cho phạm vi bằng cách nhập tên vào hộp Name. Ở đây, tôi đã đặt tên cho phạm vi là "Customer".

Chèn ô kết hợp và cấu hình thuộc tính

Ở cách tạo ô tìm kiếm này, chúng ta cần tạo 1 ô kết hợp.

- Quay lại trang tính chứa tập dữ liệu bạn muốn tìm kiếm. Đi tới tab Developer, nhấp vào Insert => Combo Box (ActiveX Control).

- Con trỏ sẽ chuyển sang hình chữ thập. Bạn cần kéo con trỏ để tạo hộp kết hợp tại vị trí mong muốn trong bảng tính. Sau khi hoàn thành việc vẽ hộp, hãy thả chuột ra.

- Nhấp chuột phải vào ô kết hợp và chọn "Properties" từ danh sách thả xuống.

- Trong mục Properties:

** Liên kết hộp kết hợp với một ô bằng cách nhập tham chiếu ô vào trường LinkedCell. Ở đây, ta nhập "M2".

Mẹo: Việc chỉ định trường này sẽ đảm bảo rằng mọi dữ liệu được nhập vào hộp kết hợp sẽ tự động cập nhật trong ô M2 và ngược lại.

** Trong trường ListFillRange, nhập tên phạm vi mà bạn đã chỉ định cho danh sách đã xử lý trùng lặp. Thay đổi trường MatchEntry thành 2 – fmMatchEntryNone. Cuối cùng, đóng ngăn Properties.

- Nhấn vào "Design Mode" trong tab Developer để thoát khỏi chế độ thiết kế.

Sử dụng công thức tạo ô tìm kiếm
- Tạo ba cột hỗ trợ nằm cạnh nhau với phạm vi dữ liệu gốc.

- Trong ô H5, dưới tiêu đề cột hỗ trợ đầu tiên, nhập công thức và nhấn Enter.

=ROWS($B$5:B5)

Ở đây, B5 là ô chứa tên khách hàng đầu tiên trong 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 để tự động sao chép công thức vào ô tiếp theo.

- Trong ô I5, bên dưới tiêu đề cột hỗ trợ thứ hai, nhập công thức và nhấn Enter. Nhấp đúp vào góc dưới bên phải của ô này để tự động điền các ô bên dưới bằng cùng một công thức.

=IF(ISNUMBER(SEARCH($M$2,B5)),H5,"") 

- Trong ô J5, bên dưới tiêu đề cột hỗ trợ thứ ba, thực hiện tương tự: nhập công thức và nhấn Enter. Nhấp đúp vào góc dưới bên phải để sao chép công thức xuống các ô phía dưới.

=IFERROR(SMALL($I$5:$I$281,H5),"") 

- Sao chép hàng tiêu đề gốc sang một vùng mới.

- Chọn ô bên dưới tiêu đề đầu tiên (ví dụ: L5) và nhập công thức vào đó, sau đó nhấn Enter.

=IFERROR(INDEX($A$5:$G$281,$J5,COLUMNS($L$4:L4)),"")

Kéo thanh điền sang phải và xuống dưới để áp dụng công thức cho các cột và hàng tương ứng.

Ghi chú:

** Do 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ể cách nhập.

Kết quả: Khi nhập hoặc chọn tên khách hàng từ hộp kết hợp, các hàng tương ứng chứa tên khách hàng đó trong cột B sẽ được lọc và hiển thị ngay lập tức trong phạm vi kết quả.

Trên đây mình đã hướng dẫn chi tiết cho bạn cách tạo ô tìm kiếm trong Excel từ A đến Z. Chúc các bạn thực hiện thành công và hãy tiếp tục theo dõi các mẹo vặt văn phòng được cập nhật liên tục từ Phúc Anh nhé