Hàm Query trong Google Sheet

Chia sẻ bài viết này:
  • 28
  •  
  • 1
  •  
  •  
  •  
  •   
  •   

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

01-su-dung-ham-query-trong-google-sheet

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ả:

ham-query-trong-google-sheet

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

Bài tập, các ví dụ khác

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:


Chia sẻ bài viết này:
  • 28
  •  
  • 1
  •  
  •  
  •  
  •   
  •