Hàm Query trong Google Sheet
Dữ liệu sử dụng trong bài viết này:
Mở tại đây: Click
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
Xem nhanh
Hàm query cơ bản trong Google Sheet
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”)
Xem ngay: Địa chỉ học Excel tại Hà Nội
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
Riêng với việc dùng hàm query với dữ liệu ngày tháng, mời các bạn xem bài viết này:
Sử dụng query với dữ liệu ngày tháng trong Google Sheet
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
Hướng dẫn sử dụng Google Spreadsheet từ A đến Z
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ả:
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
Blog Học Excel online đã mở rất nhiều các khóa học đào tạo về học Excel cơ bản cho đến nâng cao với các chuyên gia hàng đầu tại Việt Nam. Chúng tôi sẽ mang đến cho bạn những buổi học bổ ích, hiểu quả có thể sử dụng thành thạo Excel để áp dụng trong công việc. Ngoài ra, chúng tôi đang có nhiều chương trình khuyến mại lớn cho các học viên đăng kí trong tháng này, vậy còn trừng trừ gì nữa mà không đăng kí ngay thôi. Thông tin chi tiết xem tại:
