- Hàm QUERY là gì?

Hàm Query trong Google Sheet là hàm cho phép bạn sử dụng các lệnh cơ sở dữ liệu (SQL, ngôn ngữ truy vấn có cấu trúc, mã được sử dụng để giao tiếp với cơ sở dữ liệu) để thao tác với dữ liệu (có thể tra cứu dữ liệu, lọc dữ liệu, kết hợp nhiều dữ liệu từ nhiều sheet thành 1 sheet,...) một cách linh hoạt và dễ dàng.

- Công thức hàm QUERY

Lưu ý: dữ liệu nằm trong Sheet có tên là saor, gồm có 7 cột (A,B,C,D,E,F,G) từ A1:G44. Từ 1 sheet khác, ta tham chiếu đến dữ liệu này với cú pháp saor!A1:G44

Hàm Query với câu lệnh SELECT

Sao chép toàn bộ dữ liệu bảng gốc

Ở trong sheet Query, ô A1, gõ vào công thức sau đây:

=query(saor!A1:G44,“select *”)

Công thức này nghĩa là lựa chọn tất cả các cột

Lựa chọn dữ liệu ở 1 số cột cần thiết

Để lựa chọn 1 số cột cụ thể với hàm query, ví dụ cần các cột A, B, C với dữ liệu trên, ta gõ vào ô A1 công thức sau đây:

=query(saor!A1:G44,“select A,B,C”)

Hàm query với điều kiện where trong Google Sheet

Hàm query với điều kiện where cơ bản

Giả sử bây giờ chúng ta muốn lấy ra dữ liệu của vùng East thôi chẳng hạn, cú pháp của câu lệnh query sẽ như sau

=query(saor!A1:G44,“select * where B = ‘East'”)

Như vậy là, từ câu lệnh select * cơ bản, chúng ta thêm điều kiện lọc dữ liệu cho cột B bằng từ khoá Where

Hàm query với điều kiện phức tạp hơn

Kết hợp với AND, OR

Bây giờ bạn muốn lọc ra dữ liệu cột B = ‘East’ và cột C là Jones thì câu select sẽ cần được viết như sau

=query(saor!A1:G44,“select * where B = ‘East’ and C = ‘Jones'”)

Tiếp theo, bạn muốn lọc ra dữ liệu cột B = ‘East’ hoặc B = ‘West’, thì câu select sẽ như sau

=query(saor!A1:G44,“select * where B = ‘East’ or B = ‘West'”)

Sử dụng hàm query của Google Sheet với dữ liệu ngày tháng

Sử dụng hàm query của Google Sheet với từ khoá contains

Ví dụ: để lấy ra các dòng mà trong cột Item có chữ ‘Set’

=query(saor!A1:G44,“select * where D contains ‘Set'”)

để giới hạn chỉ lấy 3 kết quả đầu tiên:

=query(saor!A1:G44,“select * where D contains ‘Set’ limit 3”)

Hàm query sử dụng cùng where và order by

Để truy suất dữ liệu và sắp xếp dữ liệu kết quả, chúng ta sẽ viết thêm order by vào câu query với ví dụ sau đây: chọn toàn bộ người bán hàng (cột C), và doanh thu (cột G) và sắp xếp theo doanh thu từ lớn nhất tới nhỏ nhất:

=query(saor!A1:G44,“select C, G order by G desc”)

Từ khoá order by G cho chúng ta khả năng sắp xếp theo cột G, desc nghĩa là sắp xếp theo thứ tự nghịch (từ lớn tới nhỏ, từ Z->A)

Nếu muốn sắp xếp theo thứ tự thuận, thì chúng ta dùng từ khoá asc thay cho desc

Lồng ghép tính toán vào trong hàm query

Giả sử chúng ta muốn tính thêm 19% thuế trên Doanh thu ở cột G và đặt tên cột này là Thuế thì cú pháp sẽ như sau:

=query(saor!A1:G44,“select C, G, ( G * 0.19 ) Label ( G * 0.19 ) ‘Thuế’ “)

Các hàm tổng hợp dữ liệu với hàm query trong Google Sheet

Tính min, max, trung bình cộng của cột Total: =query(saor!A1:G44,“select min(G), max(G), avg(G)”)

Sử dụng Group By trong hàm query

Để thực hiện 1 số thao tác tổng hợp dữ liệu như đếm số dòng (số giao dịch), tính tổng doanh thu của mỗi vùng … thì chúng ta có thể thực hiện như sau:

Đếm số giao dịch ứng với mỗi vùng:

=query(saor!A1:G44,“select B, count(C) group by B”)

Tính tổng giao dịch ứng với mỗi vùng:

=query(saor!A1:G44,“select B, sum(G) group by B”)

Tính tổng giao dịch ứng với mỗi vùng, trong mỗi vùng thống kê doanh số của mỗi Item

=query(saor!A1:G44,“select B,D, sum(G) group by B,D”)

=> trong câu lệnh này, cột nằm trước chữ “group by” cần được áp dụng hàm tổng hợp (count, sum, …) và các cột còn lại phải nằm trong select và sau “group by” để không bị lỗi

1 câu lệnh query có thể tạo ra cả 1 báo cáo, ví dụ như sau:

=query(saor!A1:G44,“select B,D, count(G),min(G), max(G),avg(G),sum(G) group by B,D”)

Kết quả:

Hàm query trong Google Sheet

Một số hàm khác được query hỗ trợ

Offset: bỏ qua 1 số dòng đầu tiên của kết quả

Options: 1 số thiết lập khác

Format: định dạng dữ liệu đầu ra của hàm query