Bạn đã biết về hàm Query rồi đúng không? Đây là một mẹo tuyệt vời cho bạn! Tôi sẽ giải quyết vấn đề của bạn khi sử dụng multiple CONTAINS trong WHERE Clause trong Query của Google Sheets.
Ngoài việc tổng hợp, hàm Query của Google Sheets còn có khả năng tìm kiếm chuỗi. Đối với việc tìm kiếm chuỗi, có các toán tử so sánh đơn giản và năm toán tử so sánh chuỗi phức tạp. Chúng là CONTAINS, MATCH, LIKE, STARTS WITH và ENDS WITH.
Một số bạn có thể đã biết về việc sử dụng CONTAINS và không sử dụng trong Query. Đây là một ví dụ.
Ví dụ về việc sử dụng CONTAINS trong Where Clause trong ô D1: Trích xuất tất cả “Mã sản phẩm” chứa chuỗi “AB”.
=QUERY(A1:A,"Select * Where A Contains 'AB' ",1)Ví dụ về việc không sử dụng CONTAINS trong ô E1: Trích xuất tất cả “Mã sản phẩm” không chứa chuỗi “AB”.
=QUERY(A1:A,"Select * Where not A Contains 'AB' ",1)Mục đích của toán tử so sánh chuỗi này là so sánh một tiêu chí dựa trên một phần của chuỗi. Bằng cách sử dụng toán tử logic NOT trong Query, bạn có thể đảo ngược kết quả so sánh. Tôi đã thể hiện điều đó trong ví dụ công thức thứ hai ở trên.
Mục đích của multiple CONTAINS trong WHERE clause của Query là sử dụng nhiều chuỗi con để so sánh. Bạn muốn tìm hiểu vấn đề đó, đó là chủ đề chính của bài viết này.
Nhiều Điều kiện trong CONTAINS trong WHERE Clause trong Query
Bạn có thể sử dụng nhiều điều kiện trong CONTAINS cũng như NOT CONTAINS trong WHERE Clause của Query trong Google Sheets. Vậy làm cách nào để viết multiple CONTAINS trong Query?
Đây là câu trả lời! Nếu bạn có một vài từ khóa tương đối (hoặc không) phù hợp, hãy sử dụng multiple CONTAINS trong Query. Nếu số lượng từ khóa tương đối (hoặc không) phù hợp là lớn, có một tùy chọn tốt hơn. Đầu tiên, hãy xem cách sử dụng multiple CONTAINS.
=QUERY(A1:A,"Select * Where A Contains 'AB' or A Contains 'DJ' ",1)Bạn có thể thay thế CONTAINS trong WHERE Clause bằng MATCHES trong WHERE Clause trong Google Sheets Query.
Điều đó có nghĩa là khi bạn muốn sử dụng nhiều tiêu chí trong CONTAINS trong Where Clause, bạn có thể sử dụng toán tử MATCH.
Cách thay thế CONTAINS bằng MATCHES trong Google Sheets Query
Tôi đã đưa ra hai công thức với một tiêu chí đơn. Tôi sẽ viết lại hai công thức đó bằng cách sử dụng MATCHES trong WHERE Clause của Query.
Matches trong Where Clause (ví dụ công thức trong ô D1):
=QUERY(A1:A,"Select * Where A Matches '.*AB.*' ",1)Không phù hợp (ví dụ công thức trong ô E1):
=QUERY(A1:A,"Select * Where not A Matches '.*AB.*' ",1)Khi thay thế CONTAINS bằng MATCHES, hãy bao bọc tiêu chí bằng .*. Kiểm tra hai công thức Query của tôi ở trên để biết cách sử dụng chỉ đó.
Như tôi đã đề cập ở trên, multiple CONTAINS trong hàm Query là có thể sử dụng thông qua toán tử MATCHES cho việc so sánh chuỗi.
Trong các công thức Query ví dụ trên, tiêu chí/điều kiện nằm trong công thức. Nếu tiêu chí nằm trong một ô, thì làm cách nào để tham chiếu đến ô đó trong Query? Xem mẹo hữu ích đó trong hướng dẫn Google Sheets trước đây của tôi tại đây – Cách sử dụng tham chiếu ô trong hàm Query của Google Sheets.
Multiple CONTAINS Sử dụng MATCHES trong WHERE Clause trong Query của Google Sheets
Trong các ví dụ trước đó, tôi đã trích xuất “Mã sản phẩm” chứa/không chứa chuỗi ‘AB’. Bây giờ tôi sẽ viết công thức Query để tìm nhiều chuỗi con phù hợp/không phù hợp.
Hãy xem các công thức sau:
Ô D1:
=QUERY(A1:A,"Select * Where A Matches '.*AB.*|.*DJ.*' ",1)Ô E1:
=QUERY(A1:A,"Select * Where not A Matches '.*AB.*|.*DJ.*' ",1)Khi thay thế một tiêu chí CONTAINS đơn bằng nhiều tiêu chí MATCHES, ngoài dấu .* xung quanh tiêu chí, hãy bao gồm dấu | ngăn cách giữa các chuỗi. Điều đó giúp chúng ta dễ dàng bao gồm nhiều tiêu chí phù hợp (không phù hợp) trong một Query duy nhất.
MATCHES dựa trên so sánh chuỗi là câu trả lời của tôi cho việc sử dụng multiple CONTAINS trong WHERE Clause trong Query của Google Sheets.
Điều kiện Matches như một Phạm vi trong Hàm Query của Google Sheets
Giả định các điều kiện (trong công thức Query cuối cùng ở trên) được nhập dưới dạng một phạm vi. Vậy làm cách nào để viết công thức Query chính xác?
Tiêu chí/điều kiện trong ô B2:B3
| – | A | B |
|---|---|---|
| 1 | Mã sản phẩm | Tiêu chí |
| 2 | AB | |
| 3 | DJ |
Chúng ta nên định dạng các tiêu chí này thành '.*AB.*|.*DJ.*' để sử dụng nó trong không gian khớp chuỗi biểu thức chính quy của Google Sheets Query. Làm thế nào?
Hãy sử dụng hàm TextJoin.
=ArrayFormula("'"&textjoin(".*|",1,".*"&B2:B3)&".*'")Sử dụng công thức này trong tiêu chí Matches.
=Query(A1:A,"Select A where not A matches "&ArrayFormula("'"&textjoin(".*|",1,".*"&B2:B3)&".*'"))Trước khi kết thúc, một mẹo thêm. Bạn có biết làm thế nào để sử dụng CONTAINS và not CONTAINS trong một Query duy nhất?
Chứa và Không chứa trong Một Công thức Query duy nhất
Đối với một tiêu chí đơn chứa và không chứa, bạn có thể sử dụng CONTAINS trong Where Clause. Đối với một tiêu chí đơn cũng như nhiều tiêu chí (phù hợp/không phù hợp) sử dụng MATCHES.
- Điều kiện Chứa và Không chứa Đơn (Bạn có thể thay thế CONTAINS trong công thức này bằng MATCHES trong WHERE Clause):
=QUERY(A1:A,"Select * Where A Contains 'ABC' and Not A Contains 101",1)- Nhiều Tiêu chí Phù hợp và Không phù hợp (Nhiều Chứa và Không chứa) :
=QUERY(A1:A,"Select * Where A Matches '.*AB.*|.*DJ.*' and Not A Matches '.*101.*|.*102.*' ",1)Áp dụng các công thức này vào Google Sheets của bạn và kiểm tra kết quả. Đó là tất cả. Chúc bạn vui vẻ!
