Hướng dẫn chi tiết cách sử dụng hàm Query trong Google Sheet

1. Khái quát chung về hàm Query trong trang tính

Trước tiên, chúng ta hãy cùng tìm hiểu chung về hàm Query trong trang tính Google.

1.1. Hàm Query trong Google Sheet là gì?

Trong Google Sheet, hàm Query là hàm tổng hợp chức năng của các hàm khác như Filter (lọc), Average (trung bình), Sum (tổng)... Hàm Query cho phép bạn thực hiện các truy vấn được viết bằng ngôn ngữ giống SQL có tên là API Google Visualization. Các truy vấn này cho phép bạn thực hiện tìm kiếm, lọc và định dạng dữ liệu trong trang tính với sự linh hoạt tối đa.

1.2. Ưu điểm của hàm Query

Hàm Query Google Sheet có nhiều ưu điểm nổi bật có thể kể ra như:

• Dữ liệu có thể được cập nhật theo thời gian thực, việc làm mới bảng tính Google của bạn nhanh chóng hơn.
• Kết quả Query có thể sử dụng được cho biểu đồ hoặc các ứng dụng khác của Google như Google Docs hoặc Google Slides.
• Cập nhật dữ liệu liền mạch trên các ứng dụng Google.
• Tùy ý nhập các hàng và cột dựa trên các điều kiện và tiêu chí đã chọn.
• Không mất thời gian để viết các công thức riêng lẻ cho từng cột, hạn chế lỗi xảy ra như khi thao tác sao chép và dán thủ công.
• Các hàm Query đã viết có thể tái sử dụng nhiều lần.

2. Cú pháp hàm Query

Hàm Query trong Google Sheet có cú pháp như sau:

=QUERY(data, query_string, [headers])

Trong đó:

• data: là phạm vi ô (bảng dữ liệu) bạn muốn phân tích
• query_string: chuỗi các truy vấn mà bạn muốn chạy
• headers: số lượng hàng tiêu đề trong dữ liệu của bạn (với các tham số tùy chọn)

Chúng ta hãy cùng xem một ví dụ mẫu của hàm Query dưới đây:

=QUERY(A2:D345,"SELECT C, E",2)

Trong ví dụ trên, A2:D345 là phạm vi dữ liệu đang cần phân tích. Chuỗi query_string đang lấy từ cột C và E từ dữ liệu trang tính. Số 2 cuối công thức thể hiện tập dữ liệu chứa hai hàng tiêu đề. Đối số là tùy chọn nên Google trang tính sẽ tự động xác định đối số đó nếu bạn không nhập.

3. Cách sử dụng hàm Query trong Google Sheet

Giả sử bạn đang có trang dữ liệu ban đầu như ảnh:

Để chọn phạm vi dữ liệu và điều hướng chúng đến menu trên cùng, bạn chọn Data >>> Named ranges.

Bên phải màn hình sẽ xuất hiện một khung để bạn nhập tên cho bảng dữ liệu của mình.

Bây giờ, chúng ta sẽ bắt đầu thực hành các chức năng Google Sheet Query.

3.1. Truy vấn dữ liệu các cột

Để thực hiện truy vấn các cột cụ thể từ dữ liệu bảng tính, bạn click chọn một ô trống ở bên phải của bảng và nhập cú pháp hàm Query như sau:

=QUERY(countries,"SELECT A, B",1)

Trong đó, A và B tham chiếu đến các tham chiếu cột của bảng ban đầu trong câu lệnh Query.

Ở câu lệnh trên, chúng ta đang chọn cột A và B. Dựa vào bảng dữ liệu ban đầu, kết quả truy xuất sẽ hiển thị các cột Rank và Country.

3.2. Truy xuất dữ liệu các cột
Câu lệnh SELECT kết hợp với hàm Query trong Google Sheet được dùng để truy xuất dữ liệu tất cả các cột. Bạn chọn một ô trống trong bảng dữ liệu và nhập cú pháp hàm Query theo named range (dải ô được đặt tên).

=QUERY(countries,"SELECT *",1)

Nếu bạn không sử dụng named ranges, hàm của bạn sẽ được viết như thế này:

=QUERY(A1:D234,"SELECT *",1)

Bạn thực hiện Enter sau khi nhập công thức hàm Query Google Sheet. Kết quả có được là toàn bộ bảng dữ liệu, vì câu lệnh SELECT truy xuất tất cả các cột trong bảng countries.

3.3. Lọc dữ liệu theo điều kiện
Để lọc dữ liệu đáp ứng theo điều kiện đã được chỉ định, bạn sử dụng từ khóa WHERE trong hàm Query.

Dựa trên dữ liệu của ví dụ trước đó, chúng ta sẽ chọn các quốc gia có dân số lớn hơn 10 triệu. Hàm Query trong Google Sheet được viết như sau:

=QUERY(countries,"SELECT B, D WHERE D > 10000000",1) 

Kết quả thu được:

3.4. Sắp xếp dữ liệu

Bạn có thể sử dụng từ khóa ORDER BY để sắp xếp dữ liệu dữ liệu theo thứ tự tăng hoặc giảm dần bằng hàm Query. Từ khóa ORDER BY xuất hiện sau SELECT và WHERE. Chúng ta thực hiện sắp xếp dữ liệu dân số theo thứ tự giá trị từ lớn nhất đến nhỏ nhất.

=QUERY(countries,"SELECT B, C, D ORDER BY D DESC",1)

Bảng kết quả thu được như sau:

Nếu bạn muốn sắp xếp dữ liệu theo quốc gia theo thứ tự tăng dần, công thức hàm Query thay đổi là:

=QUERY(countries,"SELECT B, C, D ORDER BY B ASC",1)

Bảng kết quả chúng ta thu được:

3.5. Thực hiện các phép toán số học

Bạn có thể thực hiện các phép toán số học tiêu chuẩn trên các cột số bằng hàm Google Sheet Query. Ví dụ từ bảng dữ liệu, bạn hãy xác định tỷ lệ phần trăm dân số của mỗi quốc gia trên tổng dân số thế giới. Bạn chia cột dân số cho tổng dân số thế giới (7.162.119.434), sau đó nhân giá trị đó với 100 để tính tỷ lệ. Hàm công thức được viết là:
=QUERY(countries,"SELECT B, C, (D / 7162119434) * 100",1)

Từ ảnh trên bạn có thể thấy cột thứ ba trong bảng kết quả hiển thị tỷ lệ phần trăm tương ứng của mỗi quốc gia. Bạn có thể định dạng ký tự hiển thị của cột tỷ lệ phần trăm.

3.6. Đổi tên tiêu đề cột

Bạn có thể dễ dàng đổi tên các tiêu đề cột bằng cách đặt từ khóa LABEL trong câu lệnh Query. Ví dụ: bạn có thể thay đổi tiêu đề của cột đầu ra (cột thứ ba) trong bảng dữ liệu truy xuất tỷ lệ phần trăm dân số.

Công thức bạn cần viết là:

=QUERY(countries,"SELECT B, C, (D / 7162119434) * 100 LABEL (D / 7162119434) * 100 'Percentage'",1)

Lúc này, tiêu đề cột ở bảng kết quả được đổi thành Percentage:

3.7. Sử dụng các hàm tổng hợp

Ngoài các phép tính số học, bạn có thể sử dụng các hàm khác trong Query, bao gồm average, min và max. Từ ví dụ trên, chúng ta sẽ thực hiện tính giá trị trung bình, giá trị tối đa và tối thiểu của cột dữ liệu “countries”. Bằng cách sử dụng các hàm tổng hợp trong Query, chúng ta có công thức được viết như sau:

=QUERY(countries,"SELECT min(D), max(D), avg(D)",1)

Hàm Query trả kết quả là tập hợp các giá trị tối thiểu, tối đa và giá trị trung bình của tập dữ liệu ban đầu. Kết quả chúng ta có được như sau:

4. Lời Kết

Hàm Query trong Google Sheet được xem là chức năng linh hoạt nhất của bảng tính. Cú pháp đặc biệt của Google Sheet Query hỗ trợ hàng chục thao tác xử lý dữ liệu khác nhau. ICANTECH mong rằng với những chia sẻ ở trên, bạn đã hiểu cũng như biết cách áp dụng hàm Query Google Sheet để tăng hiệu suất công việc.

Cảm ơn bạn đã đọc bài viết, nếu bạn đang quan tâm đến học lập trình thì hãy tham khảo ngay các khóa học lập trình dưới đây tại ICANTECH nhé

• Khóa học lập trình Python
• Khóa học lập trình Web

Nguồn ảnh: ICANTECH.