Sử dụng hàm query để tra cứu, lọc dữ liệu và kết nối các sheet, file dữ liệu trong googlesheet

Googlesheet không chỉ là công cụ tạo ra bảng tính mạnh mẽ tương tự Exel, mà còn có nhiều tính năng nổi trội giúp cải thiện tốt hiệu suất làm việc của bạn thông qua sử dụng hàm Query, đây là một hàm giúp truy vấn dữ liệu nhằm truy xuất dữ liệu hay tìm kiếm, lọc dữ liệu từ một data phức tạp và lọc những dữ liệu cần thiết trong googlesheet hoặc liên kết dữ liệu giữa sheet này sang sheet khác hay từ file googlesheet này sang file googlesheet khác.

Cấu trúc hàm Query như sau:

QUERY(dữ_liệu; truy_vấn; [tiêu_đề])

Tham khảo thêm: Hướng dẫn sử dụng hàm Query của Google

Dữ liệu: Có thể là một vùng ô dạng: A1:H1000 hoặc vùng cột A:H

Truy vấn: Là một truy vấn được thực hiện bằng hàm truy vấn Query tương tự như SQL được tích hợp của Google

Tiêu đề: Có thể có hoặc không cũng được.

Ví dụ sử dụng hàm Query trong googlesheet để liên kết dữ liệu từ sheet này sang sheet khác

Với bảng ví dụ trên vùng dữ liệu là A4:F217 

Thông thường để chuyển dữ liệu từ sheet này qua sheet khác trên googlesheet bạn có thể dùng công thức mảng như sau: ={VD!A4:F217}

Với VD: là tên sheet chứa dữ liệu trong googlesheet.

Với ví dụ trên khi dùng hàm Query sẽ như sau:

=query(VD!A4:F217;“select *”)

Dữ liệu: VD!A4:F217

Truy vấn: “select *” Nghĩa là chọn tất cả dữ liệu

Vậy giữa công thức mảng và Query ở ví dụ trên là như nhau thì khi truy vấn hay lọc dữ liệu chỉ cần dùng công thức mảng cho đơn giản, dùng Query làm gì cho phức tạp trong Googlesheet ?

Câu trả lời là với hàm Query bạn có thể lọc được dữ liệu theo ý muốn từ sheet này sang sheet khác như sau.

Với bảng dữ liệu từ VD!A4:F217 bạn chỉ muốn lọc cột B; cột D; cột F thì sử dụng hàm mảng sẽ phải thực hiện hàm trên 3 cột nhưng với hàm Query thì bạn thực hiện như sau với truy vấn Select B,D,F

=query(VD!A4:F217;“select B,D,F”)

 

Như vậy với hàm Query có thể giúp lọc được các cột cần thiết trong googlesheet bằng truy vấn select với các cột muốn lọc ra.

Sử dụng hàm Query để lọc các hàng ở sheet dữ liệu sang sheet khác.

Để làm được điều này thì bạn sẽ dùng truy vấn Where kết hợp với Select để lọc hàng và cột một cách đơn giản, cấu trúc như sau:

=QUERY(dữ_liệu; “select Cột chọn where điều kiện“)

Cột chọn: Các cột chọn để lọc kết quả ví dụ: A,B,C hoặc * là chọn tất cả các cột

Điều kiện: Điều kiện lọc với các cột thỏa mãn VD: F=’Hà Nội’

Vậy với ví dụ trên lọc các cột và với cột F chỉ lấy Hà Nội thì sử dụng như sau:

=query(VD!A4:F217;“select B,D,F where F=’Hà Nội'”)

Sử dụng hàm Query lọc với nhiều điều kiện hơn sử dụng and hoặc or nhằm lọc hoặc truy vấn các dữ liệu theo ý muốn

Để truy vấn nhiều điều kiện hơn thì có thể kết hợp AND hoặc OR với Where.

AND: điều kiện và

Or: Điều kiện hoặc

Cũng ví dụ trên để lọc cột chi nhánh (cột F) là Hà Nội và cột  với số tiền (cột D) lớn hơn 5000 như sau:

=query(VD!A4:F217;“select B,D,F where F=’Hà Nội’ and D>5000″)

Với Or điều kiện hoặc bạn có thể sử dụng tương tự

Ngoài ra với hàm Query trong Googlesheet còn có các tính năng nâng cao khi truy xuất hoặc lọc dữ liệu như sau:

Lọc giá trị lớn nhất hoặc giá trị thấp nhất theo nhóm dữ liệu bằng Max hoặc Min, Sum, Avg… với Group By.

Tham khảo tại đây.

Dùng Select và Order by để sắp xếp dữ liệu theo thứ tự nhất định khi truy xuất hoặc lọc với Query trong googlesheet

Order by desc : Sắp xếp từ lớn tới bé

Order by asc : Sắp xếp từ bé đến lớn

VD: =query(VD!A4:F217;“select B,D,F Order by desc”) sắp xếp theo cột F từ lớn tới bé hoặc Z đến A

Lồng ghép tính toán trong Query để tự động tính toán số liệu khi truy xuất hay lọc dữ liệu qua sheet khác.

VD: =query(VD!A4:F217;“select B,D*0.1,F”)

Ở đây có thể hiểu là cột D sẽ nhân với 0.1 ở kết quả hiển thị.

Tạo một cột tính toán mới trong Query của Googlesheet để trình bày số liệu tổng hợp một cách tự động

VD: bạn tạo một cột mới có giá trị là số tiền nhân cho 10% tức là 0.1 như sau:

=query(VD!A4:F217;“select B,D,F,D*0.1“)

D*0.1 tức là tạo một cột mới với giá trị D*0.1 ở bảng dữ liệu.

Đổi tên cột mới hoặc cột sẵn có tạo thành từ hàm Query trong Googlesheet

Để đổi tên cột sử dụng LABEL với cú pháp như sau:

LABEL Cột NAME

Với Cột là cột cần đổi tên và NAME là tên mới của cột, với VD trên đổi tên cột D*0.1 thành cột thuế VAT thì thực hiện như sau:

=query(VD!A4:F217;“select B,D,F,D*0.1 label (D*0.1) ‘VAT’“)

 

Ngoài ra hàm Query có thể kết hợp với hàm Imporange để có thể lọc dữ liệu có điều kiện từ file Googlsheet này sang file googlesheet khác.