Cách dụng hàm Query trong Excel

Nội dung chính

• 1 Ví dụ minh họa
• 2 Hàm QUERY với câu lệnh SELECT
• 3 Hàm QUERY với điều kiện WHERE 
• 4 Hàm QUERY với điều kiện WHERE và ORDER BY
• 5 Hàm QUERY bao hàm các phép tính toán
• 6 Hàm QUERY và tính năng tổng hợp dữ liệu kết hợp với các hàm khác trong Google Sheets
• 6.1 Tính min, max, trung bình cộng của cột Total
• 6.2 Sử dụng Group By trong hàm query
• 6.3 Hàm QUERY hỗ trợ một số hàm khác

Hàm QUERY là một trong những hàm tốt nhất và hữu ích nhất trong Google Trang tính (Google Sheets). Khi được sử dụng hết tiềm năng, nó có thể làm được một số điều thực sự đáng kinh ngạc, nó mang đến tính năng tìm kiếm kiểu cơ sở dữ liệu mạnh mẽ cho bảng tính của bạn, vì vậy bạn có thể tra cứu và lọc dữ liệu của mình ở bất kỳ định dạng nào bạn thích. Hôm nay, Gitiho sẽ hướng dẫn bạn cách sử dụng hàm QUERY và ứng dụng của nó.

Xem thêm: Hướng dẫn sử dụng hàm Query trong Google Sheet

Top khóa học Google Sheets hàng đầu

Top khóa học Google Sheets hàng đầu

Ví dụ minh họa

Giả sử bạn có 1 trang tính chứa dữ liệu như sau:

Bảng dữ liệu trong Trang tính gồm có 7 cột và 44 dòng, từ A1:G44, bảng dữ liệu này mang tên "SAO''. Hãy cùng xem cách sử dụng hàm QUERY với các tính năng khác nhau dưới đây.

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

1. Khi bạn cần sao chép toàn bộ dữ liệu bảng gốc.

Bạn cần tạo 1 sheet bên cạnh, có thể đặt tên là QUERY để thực hiện các thao tác với lệnh này. 

Ở trang tính mới này, hãy nhập công thức sau vào ô A1:

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

Công thức này giúp bạn lựa chọn tất cả các cột và dòng trong bảng SAO

2. Khi bạn chỉ muốn sao chép một số ô dữ liệu cần thiết.

Với mục tiêu này, giả sử bạn cần chọn A,B,C bạn cần sử dụng công thức sau đây:

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

Hàm QUERY với điều kiện WHERE 

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

Ví dụ, bạn chỉ muốn lấy dữ liệu của vùng East thôi, câu lệnh QUERY sẽ như sau:

=query(sao!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

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

• Kết hợp với hàm AND

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(sao!A1:G44,“select * where B = ‘East’ and C = ‘Jones'”)

• Kết hợp với hàm OR

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(sao!A1:G44,“select * where B = ‘East’ or B = ‘West'”)

3. Hàm QUERY với dữ liệu ngày tháng

Vấn đề này, Gitiho đã có 1 bài viết chuyên biệt về Cách sử dụng hàm QUERY với dữ liệu ngày tháng trong Google Sheets hay Google sheets, QUERY, mời bạn đọc tham khảo thêm.

4. Hàm QUERY với từ khoá contains

• Để lấy ra các dòng mà trong cột Item có chữ ‘Set’, bạn cần nhập câu lệnh:

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

• Để giới hạn chỉ lấy 3 kết quả đầu tiên, bạn nên nhập:

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

Xem thêm: Hướng dẫn thêm hàng Total bằng hàm Query trong Google Sheet

Hàm QUERY với điều kiện WHERE và ORDER BY

Vẫn sử dụng ví dụ ở đầu bài viết, nhưng lần này, với yêu cầu là: ''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''

Bạn sẽ thực hiện như thế nào với yêu cầu này? Câu trả lời chính là sử dụng công thức hàm sau:

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

Trong đó,

order by G: Khả năng sắp xếp theo cột G

desc: Sắp xếp theo thứ tự nghịch (từ lớn tới nhỏ, từ Z->A). Trong trường hợp bạn muốn sắp xếp theo thứ tự thuận (ngược lại, từ A->Z) bạn thay desc bằng asc.

Hàm QUERY bao hàm các phép tính toán

Ví dụ, bạn 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(sao!A1:G44,“select C, G, ( G * 0.19 ) Label ( G * 0.19 ) ‘Thuế’ “)

Hàm QUERY và tính năng tổng hợp dữ liệu kết hợp với các hàm khác trong Google Sheets

Tính min, max, trung bình cộng của cột Total

Vẫn ứng dụng ví dụ trên, công thức hàm cần sử dụng như sau:

=query(sao!A1:G44,“select min(G), max(G), avg(G)”)

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

Trong một số trường hợp yêu cầu bạn phải 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 …Công thức trong từng trường hợp dưới đây sẽ giúp ích cho bạn:

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

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

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

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

3. 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 mặt hàng trong cột Item:

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

Lưu ý: 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

4. Tạo ra 1 báo cáo:

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

Bây giờ, hãy cùng nhìn xem kết quả đạt được:

Hàm QUERY hỗ trợ một số hàm khác

Hàm QUERY trong Google Trang tính còn hỗ trợ các hàm khác như:

• 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

Xem thêm: Hướng dẫn tạo hộp tìm kiếm bằng hàm Query trong Google Sheets

Kết luận

Hy vọng qua bài viết trên, các bạn đã nắm được các vận dụng các tính năng của hàm QUERY trong Google Sheets (Google Trang tính). Ngoài ra, để thực hành tốt các kỹ năng tin học văn phòng, hãy các bài blogs chia sẻ kiến thức về hàm VLOOKUP, hàm IF, AND trong Excel và luyên tập thêm các bài tập Excel đính kèm nhé.

Gitiho chúc bạn học tốt!