Hướng dẫn tạo thanh tìm kiếm tự động trong Excel 2024 trở lên cho người mới

Nếu bạn có một danh mục hàng hóa lớn trong Excel và muốn tạo thanh tìm kiếm gõ đến đâu lọc đến đó, có thể kết hợp FILTER, LET, TEXTSPLIT và TextBox ActiveX.

Bài này hướng dẫn theo cách đơn giản, phù hợp với người mới.

1. Chuẩn bị dữ liệu

Giả sử dữ liệu nằm trong sheet:

DanhMuc

Dữ liệu từ:

A1:AW5000

Trong đó:

Cột B: tên chính hoặc thông tin chính
Cột C: thông tin phụ
Kết quả trả về toàn bộ dữ liệu từ A đến AW
Ô trung gian nhận từ khóa tìm kiếm là B1 trên sheet tìm kiếm

2. Công thức tìm kiếm đơn giản nhất

Tại ô muốn hiển thị kết quả, nhập:

=LET(q,TRIM($B$1),data,DanhMuc!A2:AW5000,text,DanhMuc!B2:B5000&" "&DanhMuc!C2:C5000,words,TEXTSPLIT(q," ",,TRUE),match,BYROW(text,LAMBDA(r,AND(ISNUMBER(SEARCH(words,r))))),IF(q="",DanhMuc!A1:AW1,VSTACK(DanhMuc!A1:AW1,FILTER(data,match,"Không tìm thấy sản phẩm"))))

Công thức này:

lấy từ khóa trong B1
tách thành nhiều từ
tìm trong cột B và C
yêu cầu đủ các từ khóa
trả về toàn bộ dữ liệu A:AW

Đây là công thức nhẹ và dễ hiểu nhất.

3. Công thức tìm rộng và xếp kết quả

Nếu muốn chỉ cần khớp một phần từ khóa và ưu tiên kết quả phù hợp hơn:

=LET(
q,LOWER(TRIM($B$1)),
data,DanhMuc!A2:AW5000,
ten,LOWER(TRIM(DanhMuc!B2:B5000)),
txt,LOWER(DanhMuc!B2:B5000&" "&DanhMuc!C2:C5000),
words,TEXTSPLIT(q," ",,TRUE),
hits,--ISNUMBER(SEARCH(words,txt)),
score,MMULT(hits,SEQUENCE(COLUMNS(words),1,1,0)),
exact,--(ten=q),
phrase,--ISNUMBER(SEARCH(q,txt)),
ok,score>0,
fdata,FILTER(data,ok),
fexact,FILTER(exact,ok),
fphrase,FILTER(phrase,ok),
fscore,FILTER(score,ok),
IF(
q="",
DanhMuc!A1:AW1,
IFERROR(
VSTACK(
DanhMuc!A1:AW1,
SORTBY(
fdata,
fexact,-1,
fphrase,-1,
fscore,-1
)
),
"Không tìm thấy sản phẩm"
)
)
)

Công thức ưu tiên:

tên trùng chính xác
cụm từ trùng
số từ khóa khớp nhiều hơn

4. Công thức xếp hạng mạnh nhất

Nếu muốn kết quả phù hợp nhất luôn nằm trên đầu:

=LET(
q,LOWER(TRIM($B$1)),
data,DanhMuc!A2:AW5000,
ten,LOWER(TRIM(DanhMuc!B2:B5000)),
phu,LOWER(TRIM(DanhMuc!C2:C5000)),
txt,ten&" "&phu,
words,TEXTSPLIT(q," ",,TRUE),

hitTen,--ISNUMBER(SEARCH(words,ten)),
hitTxt,--ISNUMBER(SEARCH(words,txt)),

soTuTen,MMULT(hitTen,SEQUENCE(COLUMNS(words),1,1,0)),
soTuTong,MMULT(hitTxt,SEQUENCE(COLUMNS(words),1,1,0)),
tongTu,COLUMNS(words),

khopTen,--(ten=q),
khopPhu,--(phu=q),
batDauTen,--(LEFT(ten,LEN(q))=q),
cumTen,--ISNUMBER(SEARCH(q,ten)),
cumTong,--ISNUMBER(SEARCH(q,txt)),
duTu,--(soTuTong=tongTu),

score,
khopTen*1000000+
khopPhu*900000+
batDauTen*500000+
cumTen*200000+
cumTong*100000+
duTu*50000+
soTuTen*5000+
soTuTong*1000,

ok,soTuTong>0,

fdata,FILTER(data,ok),
fscore,FILTER(score,ok),
ften,FILTER(ten,ok),

IF(
q="",
DanhMuc!A1:AW1,
IFERROR(
VSTACK(
DanhMuc!A1:AW1,
SORTBY(
fdata,
fscore,-1,
LEN(ften),1
)
),
"Không tìm thấy sản phẩm"
)
)
)

Công thức này chấm điểm kết quả theo:

tên cột B trùng hoàn toàn
cột C trùng hoàn toàn
tên bắt đầu bằng từ khóa
cụm từ nằm trong tên
có đủ từ khóa
số từ khóa khớp

Chất lượng tìm kiếm tốt nhất nhưng nặng hơn.

5. Công thức tối ưu cho bảng nhiều cột

Nếu bảng có nhiều cột như A:AW, nên dùng:

=LET(
q,LOWER(TRIM($B$1)),
data,DanhMuc!A2:AW5000,
ten,LOWER(TRIM(DanhMuc!B2:B5000)),
txt,LOWER(DanhMuc!B2:B5000&" "&DanhMuc!C2:C5000),
words,TEXTSPLIT(q," ",,TRUE),
hit,--ISNUMBER(SEARCH(words,txt)),
soTu,MMULT(hit,SEQUENCE(COLUMNS(words),1,1,0)),
tongTu,COLUMNS(words),
exact,--(ten=q),
phrase,--ISNUMBER(SEARCH(q,ten)),
duTu,--(soTu=tongTu),
score,exact*10000+phrase*3000+duTu*1000+soTu*100,
ok,soTu>0,
idx,FILTER(SEQUENCE(ROWS(data)),ok),
fscore,FILTER(score,ok),
sortedIdx,SORTBY(idx,fscore,-1),
IF(
q="",
DanhMuc!A1:AW1,
IFERROR(
VSTACK(
DanhMuc!A1:AW1,
INDEX(data,sortedIdx,SEQUENCE(1,COLUMNS(data)))
),
"Không tìm thấy sản phẩm"
)
)
)

Điểm khác biệt là công thức này sắp xếp số thứ tự dòng trước, sau đó mới lấy dữ liệu.

Với bảng khoảng 5.000 dòng và 49 cột, đây là lựa chọn cân bằng tốt giữa tốc độ và chất lượng tìm kiếm.

6. So sánh các công thức

Công thức	Dễ dùng	Tìm rộng	Xếp hạng	Tốc độ
Công thức 1	Rất dễ	Không	Không	Nhanh
Công thức 2	Dễ	Có	Khá	Khá nhanh
Công thức 3	Trung bình	Có	Rất tốt	Chậm hơn
Công thức 4	Trung bình	Có	Tốt	Tốt

Với bảng lớn, nên ưu tiên công thức 4.

7. Tạo ô tìm kiếm gõ đến đâu chạy đến đó

Nếu nhập trực tiếp vào B1, Excel thường phải bấm Enter mới nhận dữ liệu.

Muốn gõ từng ký tự và kết quả chạy ngay, cần dùng TextBox ActiveX.

8. Bật tab Developer

Vào:

File/Tệp → Options/Tùy chọn → Customize Ribbon/Tùy chỉnh Dải băng → tích Developer/Nhà phát triển

9. Tạo TextBox

Vào:

Developer/Nhà phát triển → Insert/Chèn → ActiveX Controls/Điều khiển ActiveX → Text Box/Hộp văn bản

Kéo chuột để vẽ ô tìm kiếm.

10. Đổi tên TextBox

Bật:

Developer/Nhà phát triển → Design Mode/Chế độ thiết kế

Nhấp chuột phải TextBox:

Right-click/Nhấp chuột phải → Properties/Thuộc tính

Tại:

(Name)

đổi thành:

TimKiemTuDong

Kiểm tra thêm:

Enabled = True
Locked = False

11. Gắn VBA cho TextBox

Nhấp chuột phải TextBox:

Right-click/Nhấp chuột phải → View Code/Xem mã

Nếu sheet chưa khóa, có thể dùng:

Private Sub TimKiemTuDong_Change()

    Me.Range("B1").Value = Me.TimKiemTuDong.Text
    Me.Calculate

End Sub

Quay lại Excel bằng:

Alt + Q

Sau đó tắt:

Developer/Nhà phát triển → Design Mode/Chế độ thiết kế

Từ lúc này, gõ đến đâu kết quả cập nhật đến đó.

12. Đặt các sheet khác chạy thủ công

Vào:

Formulas/Công thức → Calculation Options/Tùy chọn tính toán → Manual/Thủ công

Nhờ VBA, sheet tìm kiếm vẫn tự tính khi gõ, còn các sheet khác không tự chạy.

13. Khóa toàn bộ sheet nhưng vẫn cho phép nhập TextBox

Nếu muốn người dùng không sửa được bất kỳ ô nào, nhưng vẫn sử dụng được thanh tìm kiếm, nên để VBA tự:

mở khóa sheet
ghi từ khóa vào B1
tính kết quả
khóa sheet lại

Trước tiên:

Review/Xem lại → Unprotect Sheet/Bỏ bảo vệ trang tính

Sau đó:

Developer/Nhà phát triển → Design Mode/Chế độ thiết kế → Right-click/Nhấp chuột phải TextBox → View Code/Xem mã

Thay code cũ bằng:

Private Sub TimKiemTuDong_Change()

    On Error GoTo KetThuc

    Application.EnableEvents = False

    'Mở khóa sheet
    Me.Unprotect Password:="1234"

    'Ghi nội dung TextBox vào B1
    Me.Range("B1").Value = Me.TimKiemTuDong.Text

    'Tính lại riêng sheet
    Me.Calculate

    'Khóa lại sheet nhưng vẫn cho dùng TextBox
    Me.Protect Password:="1234", _
               DrawingObjects:=False, _
               Contents:=True, _
               Scenarios:=True, _
               UserInterfaceOnly:=True

KetThuc:
    Application.EnableEvents = True

End Sub

Bạn có thể thay:

bằng mật khẩu khác.

Khóa toàn bộ các ô

Nhấn:

Ctrl + A

sau đó:

Right-click/Nhấp chuột phải → Format Cells/Định dạng ô → Protection/Bảo vệ

Đảm bảo:

Locked/Khóa = ✓

Sau đó không cần tự bấm Protect Sheet/Bảo vệ trang tính.

Khi bắt đầu gõ vào TextBox, VBA sẽ tự khóa lại sheet.

Kết quả:

các ô không sửa được
công thức không bị thay đổi
dữ liệu được bảo vệ
TextBox vẫn nhập bình thường
thanh tìm kiếm vẫn chạy theo từng ký tự

14. Lưu file đúng định dạng

Vào:

File/Tệp → Save As/Lưu dưới dạng

Chọn:

Excel Macro-Enabled Workbook/Sổ làm việc Excel hỗ trợ macro (*.xlsm)

Nếu lưu .xlsx, VBA sẽ bị mất.

15. Excel nào dùng được?

Các công thức trong bài sử dụng những hàm mới như:

LET
FILTER
TEXTSPLIT
BYROW
LAMBDA
VSTACK
SORTBY
SEQUENCE

Vì vậy phù hợp nhất với:

Excel 2024
Microsoft 365

Excel 2019 không dùng được đầy đủ các công thức này.

Excel 2021 hỗ trợ một phần nhưng thiếu một số hàm quan trọng như TEXTSPLIT, BYROW, VSTACK.

Nếu dùng Excel 2019, nên xây dựng thanh tìm kiếm chủ yếu bằng VBA.

Kết luận

Nếu mới bắt đầu, nên làm theo thứ tự:

Bước 1: dùng công thức 1 để kiểm tra tìm kiếm.
Bước 2: tạo TextBox ActiveX.
Bước 3: đặt tên TimKiemTuDong.
Bước 4: gắn VBA để gõ đến đâu tìm đến đó.
Bước 5: chuyển sang công thức 4 nếu cần xếp hạng kết quả.
Bước 6: đặt Manual/Thủ công nếu workbook có nhiều sheet nặng.
Bước 7: nếu cần bảo vệ file, dùng VBA mở khóa → tìm kiếm → khóa lại tự động.

Với danh mục lớn, công thức 4 + TextBox ActiveX + VBA + chế độ Manual là giải pháp cân bằng tốt giữa tốc độ, khả năng tìm kiếm và bảo vệ dữ liệu.

#Excel2024 #VBAExcel #ExcelFILTER #ThanhTimKiemExcel #ExcelTips

Related posts:

Hướng dẫn tạo thanh tìm kiếm tự động trong Excel 2024 trở lên (cho người mới)

Hướng dẫn tạo thanh tìm kiếm tự động trong Excel 2024 trở lên cho người mới

1. Chuẩn bị dữ liệu

2. Công thức tìm kiếm đơn giản nhất

3. Công thức tìm rộng và xếp kết quả

4. Công thức xếp hạng mạnh nhất

5. Công thức tối ưu cho bảng nhiều cột

6. So sánh các công thức

7. Tạo ô tìm kiếm gõ đến đâu chạy đến đó

8. Bật tab Developer

9. Tạo TextBox

10. Đổi tên TextBox

11. Gắn VBA cho TextBox

12. Đặt các sheet khác chạy thủ công

13. Khóa toàn bộ sheet nhưng vẫn cho phép nhập TextBox

Khóa toàn bộ các ô

14. Lưu file đúng định dạng

15. Excel nào dùng được?

Kết luận

Đăng nhận xét

Hướng dẫn chi tiết Ghost windows 10 bằng USB, đĩa Boot

Ghost Windows 10, 8.1, 7 trực tiếp trên HĐH (ổ cứng) không cần đĩa

Hướng dẫn đăng ký và cài đặt line trên máy tính (PC, Laptop..)

Nokia PC Suite - Đồng bộ dữ liệu điện thoại Nokia và máy tính

Biểu mẫu liên hệ

Discuss