Bạn đã bao giờ gặp vấn đề muốn tìm vị trí của một giá trị trong một dãy dữ liệu trên Google Sheets mà không biết cách làm sao? Hôm nay, tôi sẽ chia sẻ với bạn một bí quyết nhỏ, đó là cách sử dụng hàm Match trong Querry Where Clause trên Google Sheets.
Hàm Match và công dụng của nó
Hàm Match là một trong những hàm hữu ích trên Google Sheets. Nó giúp bạn tìm vị trí của một chuỗi, ngày tháng hoặc giá trị số trong một dãy dữ liệu có một hàng hoặc một cột.
Vị trí trả về bởi hàm Match có thể được sử dụng làm biểu thức trong các hàm khác. Trong trường hợp này, chúng ta sẽ sử dụng Match trong Querry Where Clause.
Hãy xem qua ví dụ dưới đây để hiểu rõ hơn về cách sử dụng hàm Match trong Querry Where Clause trên Google Sheets.
Đây là một bảng “Attendance” mẫu, trong đó hàng đầu tiên chứa một vài tên (nhân viên hoặc sinh viên) và dưới đó là trạng thái có mặt/vắng mặt/ngày nghỉ (attendance).
Giả sử bạn muốn lọc dữ liệu cho “Scott”, nhưng chỉ là những ngày anh ta vắng mặt. Dưới đây là cách sử dụng hàm Match trong Querry Where Clause để giải quyết vấn đề này.
Trong trường hợp này, chúng ta chỉ cần sử dụng một hàm Match.
Sử dụng hàm Match trong Querry Where Clause
Trước khi sử dụng hàm Match trong Querry Where Clause, hãy xem cách chúng ta thường sử dụng công thức trong trường hợp này.
Sử dụng công thức Querry dưới đây, bạn có thể lọc dữ liệu cho những ngày vắng mặt của “Scott”.
=query( A1:J, "Select * where H='A'",1 )
Ở đây, chúng ta không có cơ hội để sử dụng hàm Match trong Where clause. Hãy xem xét một công thức thay thế cho công thức trên.
Trước tiên, hãy đếm các cột từ trái sang phải trong dữ liệu cho đến khi gặp “Scott” ở hàng số 1. Bạn sẽ có số 8 (cột H), phải không?
Bằng cách sử dụng dữ liệu như là một biểu thức (bằng cách bao quanh dữ liệu bằng Dấu Ngoặc Nhọn), chúng ta có thể sử dụng số cột trong Where clause.
=query( {A1:J}, "Select * where Col8='A'",1 )
Điều này cho phép chúng ta sử dụng công thức Match dưới đây. Làm cách nào?
=match("Scott",A1:J1,0)
Công thức trên sẽ trả về số cột 8 tương ứng với tên “Scott”. Chúng ta có thể thay thế số 8 trong công thức Truy vấn trên bằng công thức Match phía trên.
=query( {A1:J}, "Select * where Col"&match("Scott",A1:J1,0)&"='A'",1 )
Lưu ý: Nếu bạn chỉ muốn cột ngày, hãy thay thế “Select *” bằng “Select Col1”.
Nếu điều kiện Match, tức là “Scott”, là một tham chiếu ô, giả sử trong ô K1, sau đó sử dụng công thức sau.
=query( {A1:J}, "Select * where Col"&match(K1,A1:J1,0)&"='A'",1 )
Sử dụng nhiều hàm Match trong Querry Where Clause
Giả sử bạn muốn kiểm tra xem hai nhân viên của bạn có vắng mặt cùng một ngày hay không. Sau đó, bạn có thể sử dụng nhiều hàm Match trong Querry Where Clause trên Google Sheets.
Ở đây, tôi sẽ lấy ví dụ với nhân viên “Jeff” và “Scott”.
=query( {A1:J}, "Select * where Col"&match("Scott",A1:J1,0)&"='A' and Col"&match("Jeff",A1:J1,0)&"='A'",1 )
Tôi đã sử dụng hai công thức Match trong Where clause cùng với toán tử logic AND của Query.
Công thức thay thế (Filter và Query Itself)
Nếu bạn chỉ muốn lọc dữ liệu cho một nhân viên, ví dụ “Scott”, bạn có thể sử dụng công thức thay thế. Không nghi ngờ gì, Filter là công cụ tốt nhất.
=filter( A1:J8, filter( A1:J8,A1:J1="Scott" )="A" )
Công thức Filter bên trong lọc cột thứ 8 dựa trên tên “Scott”.
Công thức Filter bên ngoài lọc bảng nếu cột thứ 8 chứa chữ cái “A”.
Để biết thêm chi tiết, bạn có thể tìm hiểu thêm tại đây – Two-way Filter in Google Sheets [Dynamic Vertical and Horizontal Filter].
Để nâng cao kỹ năng của bạn trên Google Sheets, tôi muốn chia sẻ thêm một công thức nữa với bạn. Đó là sử dụng Query với Transpose.
=Query( {transpose(query(transpose(A1:J),"Select * where Col1='Scott'")),A1:J}, "Select Col2 where Col1='A'" )
Giải thích (Query Transpose)
Dấu Ngoặc Nhọn, tức là transpose(A1:J), thay đổi hướng dữ liệu để tên nhân viên sẽ nằm ở cột đầu tiên trong dữ liệu được đổi vị trí.
Công thức Query chọn hàng chứa tên “Scott” trong cột đầu tiên.
=query(transpose(A1:J),"Select * where Col1='Scott'")
Tôi đã transpose lại dữ liệu này một lần nữa. Vì vậy, chúng ta có cột thứ 8.
=transpose(query(transpose(A1:J),"Select * where Col1='Scott'"))
Thêm toàn bộ dữ liệu như là các cột liền kề bên phải của cột thứ 8.
{transpose(query(transpose(A1:J),"Select * where Col1='Scott'")),A1:J}
Query ngoài lọc cột đầu tiên nếu nó chứa “A”.
Bởi vì điều này (cột đầu tiên chứa tên và tám cột khác), tôi đã bắt đầu mệnh đề Select trong Query bên ngoài bằng “Select Col2” thay vì “Select Col1”.
Nó sẽ chỉ trả về cột ngày.
Nếu bạn muốn chọn tất cả các cột, bạn có thể chỉ định mệnh đề Where trong Query bên ngoài như “Select Col2, Col3, Col4…
Đó là tất cả về cách sử dụng hàm Match trong Querry Where Clause trên Google Sheets.
Cảm ơn bạn đã dành thời gian đọc bài viết. Hy vọng bạn tìm thấy điều này hữu ích!
