Hộp tìm kiếm bằng hàm QUERY trong Google Sheets: Tự Chế “Ô Search” ngay trong bảng tính mà hhông cần code, không cần add-on , chỉ cần một công thức QUERY và vài phút thiết lập.Nếu bảng dữ liệu của bạn có vài trăm, vài nghìn dòng, việc dò từng dòng bằng mắt hay bấm Ctrl + F chỉ để tìm đúng một sản phẩm là cực hình. Ctrl + F thì chỉ nhảy tới ô chứa từ khóa, không lọc ra danh sách kết quả, cũng chẳng có bộ lọc theo kiểu “chứa”, “bắt đầu bằng” hay “kết thúc bằng”. Còn hộp tìm kiếm dựng bằng hàm QUERY thì khác hẳn: bạn gõ từ khóa, chọn kiểu tìm, Enter một phát là cả danh sách kết quả hiện ra ngay lập tức, giống hệt một công cụ search thu nhỏ nằm gọn trong Google Sheets.
Mục lục.
Trong bài trước Hàm Query trong Google Sheet-Sức mạnh của SQL mình có giải thích về cấu trúc và cách sử dụng mệnh đề trong hàm Query rồi. Còn trong bài này mình sẽ Query từ cơ bản: ngôn ngữ truy vấn của QUERY hoạt động ra sao, mệnh đề WHERE và các toán tử dùng để lọc dữ liệu, rồi tới phần thực chiến ,dựng một hộp tìm kiếm hoàn chỉnh từng bước một. Ví dụ xuyên suốt bài sẽ dùng một bảng danh sách sản phẩm của đèn led Giá Sỉ (tên sản phẩm, loại đèn, giá bán) để bạn dễ hình dung áp dụng vào công việc thật.
QUERY là gì và “ngôn ngữ truy vấn” hoạt động ra sao?
Hàm QUERY trong Google Sheets cho phép bạn viết các câu lệnh truy vấn dữ liệu theo Ngôn ngữ truy vấn của Google Visualization API – nghe tên thì to tát, nhưng về bản chất nó viết gần giống câu lệnh SQL quen thuộc (SELECT, WHERE, ORDER BY…), chỉ khác là được nhét gọn vào trong một chuỗi văn bản.
Cấu trúc tối thiểu của hàm gồm 2 đối số bắt buộc:
- Vùng dữ liệu cần truy vấn – ví dụ A2:C61.
- Câu truy vấn, viết bằng ngôn ngữ truy vấn, luôn đặt trong dấu ngoặc kép — ví dụ “SELECT * WHERE A CONTAINS ‘ốp'”.
💡 Còn một đối số thứ ba (không bắt buộc) cho biết vùng dữ liệu có bao nhiêu dòng tiêu đề, giúp QUERY hiểu đâu là tên cột chứ không phải dữ liệu.
Mệnh đề WHERE – “trái tim” của hộp tìm kiếm
Mệnh đề WHERE dùng để lọc dữ liệu theo điều kiện bạn đặt ra, chỉ trả về những dòng thỏa điều kiện đó. Có thể hiểu ngôn ngữ truy vấn gần giống câu nói thường ngày: cụm “SELECT * WHERE A ends with ‘XYZ'” đơn giản là “lấy tất cả các dòng mà cột A kết thúc bằng chữ XYZ”.
Các toán tử so sánh cơ bản thường dùng trong WHERE:
- = : tương đương
- < : nhỏ hơn
- > : lớn hơn
- <= : nhỏ hơn hoặc bằng
- >= : lớn hơn hoặc bằng
- != hoặc <> : khác với
Muốn kết hợp nhiều điều kiện cùng lúc, bạn dùng thêm các toán tử logic and, or, not (ví dụ: WHERE B > 100000 and C = ‘Đèn LED dây’).
Riêng với văn bản (tìm theo tên sản phẩm, tên khách hàng…), QUERY có hẳn một nhóm toán tử dành riêng để tìm kiếm gần đúng – đây chính là phần cốt lõi tạo nên một hộp tìm kiếm “biết co giãn”:
- contains : trả về các dòng có chứa từ khóa, dù từ khóa nằm ở đầu, giữa hay cuối chuỗi.
- starts with : trả về các dòng bắt đầu bằng từ/cụm từ chỉ định.
- ends with : trả về các dòng kết thúc bằng từ/cụm từ chỉ định.
- matches : toán tử mạnh nhất, hỗ trợ biểu thức chính quy (regex) nên linh hoạt hơn hẳn các toán tử còn lại.
- like : kết hợp với hai ký tự đại diện % (thay cho một chuỗi ký tự bất kỳ) và _ (thay cho đúng một ký tự) để dò theo mẫu.
💡 Lưu ý quan trọng: tất cả các toán tử trên đều phân biệt chữ hoa/chữ thường. Muốn tìm không phân biệt hoa thường, bọc cột cần so sánh trong LOWER() hoặc UPPER(), đồng thời viết từ khóa theo đúng dạng chữ tương ứng – ví dụ WHERE LOWER(A) contains lower(‘Ốp’).
Ví dụ minh họa: hộp tìm kiếm cho danh sách sản phẩm
Giả sử file Google Sheets của Đèn Led Giá Sỉ có bảng dữ liệu ở vùng A2:C60 gồm 3 cột: Tên sản phẩm (A), Loại đèn (B), Giá bán (C). Bạn muốn dựng một khu vực nhỏ để bất kỳ ai mở file cũng tìm được sản phẩm chỉ bằng vài cú gõ.
Khu vực hộp tìm kiếm sẽ gồm: ô B5 chứa danh sách thả xuống các toán tử (contains, starts with…), ô B6 để gõ từ khóa cần tìm. Công thức đặt ở ô E7 (nơi hiển thị kết quả) sẽ là:
=query('Sản phẩm'!A2:C61;"SELECT * WHERE A "&B5&" '"&B6&"'")Ví dụ nếu ô B5 đang chọn contains và ô B6 gõ chữ “ốp trần”, công thức trên sẽ tự ráp thành câu truy vấn thực tế là: SELECT * WHERE A contains ‘ốp trần’ – và trả ngay danh sách các sản phẩm có tên chứa cụm “ốp trần”, ví dụ Đèn ốp trần LED 18W, Đèn ốp trần vuông chống ẩm… Mỗi khi bạn đổi toán tử hoặc từ khóa, kết quả sẽ tự cập nhật ngay lập tức, không cần bấm thêm bất kỳ nút nào.
Bắt tay thực hành dựng hộp tìm kiếm – từng bước một
Giờ thì thực hành thôi. Các bước dưới đây sẽ giúp bạn dựng đúng một hộp tìm kiếm hoàn chỉnh như ví dụ trên, áp dụng được cho bất kỳ bảng dữ liệu nào bạn đang có.
- Chọn một ô để đặt danh sách toán tử, ví dụ ô B5.
- Vào Dữ liệu (Data) → Xác thực dữ liệu (Data validation) trên thanh công cụ.
- Trong cửa sổ hiện ra, ở mục Tiêu chí (Criteria), chọn Danh sách các mục (List of items), rồi nhập các toán tử muốn dùng, ví dụ: contains,starts with,ends with,matches,like – các toán tử cách nhau bằng dấu phẩy và KHÔNG chèn khoảng trắng thừa.
- Bấm Lưu (Save), vậy là ô B5 đã có sẵn danh sách thả xuống để chọn toán tử.
- Chọn một ô trống bên dưới, ví dụ B6, để làm nơi gõ từ khóa tìm kiếm. Có thể tô màu ô này cho dễ nhận biết.
- Chọn ô sẽ hiển thị kết quả, ví dụ A12, rồi bắt đầu gõ =QUERY( và chọn vùng dữ liệu cần tìm (ví dụ : bảng Sản phẩm và vùng dữ liệu A2:C61).
- Sau vùng dữ liệu, thêm dấu chấm phẩy rồi mở dấu ngoặc kép để bắt đầu phần câu truy vấn: “SELECT * WHERE A
- Tiếp tục ráp phần toán tử bằng cách tham chiếu tới ô B5, đặt giữa các dấu &: …WHERE A “&B5&”
- Sau đó thêm dấu cách và từ khóa tìm kiếm, đặt trong cặp dấu nháy đơn rồi tham chiếu tới ô B6: ‘”&B6&”‘
- Đóng ngoặc kép và ngoặc đơn của hàm QUERY, nhấn Enter. Vì ô B6 đang trống nên công thức có thể báo lỗi tạm thời , không sao cả, đây là chuyện bình thường.
- Thử ngay: chọn một toán tử ở B5, gõ từ khóa vào B6, nhấn Enter => kết quả sẽ hiện ra tức thì trong vùng bắt đầu từ A12.
Nâng cấp thêm cho hộp tìm kiếm “xịn” hơn
Sau khi đã có hộp tìm kiếm cơ bản, đây là vài cách nâng cấp đáng thử:
- Tìm không phân biệt hoa/thường: đổi công thức thành WHERE LOWER(A) contains lower(‘”&B6&”‘) để dù khách gõ hoa hay thường vẫn ra kết quả đúng.
- Tìm linh hoạt hơn với LIKE: dùng ký tự % để đại diện cho một chuỗi bất kỳ, ví dụ LIKE ‘%đèn%led%’ sẽ tìm các dòng có cả từ “đèn” và “led” xuất hiện theo đúng thứ tự đó, dù ở giữa còn ký tự nào khác.
- Cho phép chọn cột muốn tìm: thêm một ô thả xuống thứ hai để người dùng chọn tìm theo Tên sản phẩm, Loại đèn hay Giá bán, sau đó ráp tên cột đó vào công thức thay vì cố định cột A.
- Kết hợp nhiều điều kiện: nối thêm and hoặc or trong câu truy vấn nếu muốn lọc theo nhiều tiêu chí cùng lúc, ví dụ vừa lọc theo tên sản phẩm vừa giới hạn khoảng giá.
Mã hàm nâng cấp:
=query('Sản phẩm'!A2:C61;"SELECT * WHERE LOWER(A) "&B5&" '"&B6&"'")Vài lỗi hay gặp và cách né
Quên đặt câu truy vấn trong dấu ngoặc kép, hoặc quên cặp dấu nháy đơn quanh từ khóa tìm kiếm – đây là hai lỗi phổ biến nhất khiến QUERY báo lỗi “Unable to parse query string”.
Toán tử gõ sai chính tả (kể cả sai hoa/thường trong danh sách thả xuống) cũng khiến câu truy vấn không chạy được, vì QUERY phân biệt chữ hoa/chữ thường với tên toán tử.
Vùng dữ liệu chọn thiếu dòng hoặc thiếu cột cũng là nguyên nhân thường gặp khiến kết quả trả về bị thiếu – nên chọn dư vài dòng/cột trống phía dưới và bên phải để phòng khi dữ liệu được bổ sung sau này.
Lời kết tìm kiếm bằng hàm QUERY trong Google Sheets
Ban đầu, cú pháp của QUERY có thể khiến bạn hoa mắt vì phải nhớ chỗ nào để dấu ngoặc kép, chỗ nào để dấu nháy đơn. Nhưng chỉ cần dựng thành công một hộp tìm kiếm hoàn chỉnh một lần, bạn sẽ thấy công thức không hề khó như tưởng tượng – và từ đó có thể áp dụng cho bất kỳ bảng dữ liệu lớn nào, từ danh sách sản phẩm, danh sách khách hàng cho tới đơn hàng, giúp việc tra cứu nhanh gọn như đang gõ trên thanh tìm kiếm của Google vậy.
Xem thêm hướng dẫn hàm Query và sức mạnh của nó tại trang chính thức của Google Sheet
Chúc quý anh chị thành công