Hướng dẫn cách sử dụng hàm truy vấn trong Google Sheets

Bạn đã biết đến hàm truy vấn trong Google Sheets chưa? Đây 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 một cách linh hoạt và dễ dàng hơn. Tuy đây không phải là một hàm dễ sử dụng nhưng nhiều ý kiến cho rằng: đây là 1 trong những hàm mạnh mẽ nhất của Google Sheets.

Cùng Gitiho khám phá loại hàm thú vị này nhé.

Đầu tiên, cùng đến với 1 ví dụ:

Tiếp theo, hãy chọn toàn bộ bảng này (Mẹo chuyên nghiệp: nhấp vào vị trí nào đó trong bảng của bạn và nhấn Ctrl + A (trên PC) hoặc Cmd + A (trên Mac) để đánh dấu toàn bộ bảng):

Chuyển đến trình đơn Data > Named ranges… và nhấp vào menu này. Một ngăn mới sẽ hiển thị ở phía bên phải của bảng tính của bạn như sau:

Trong hộp nhập liệu đầu tiên, hãy nhập từ “countries”. Đặt tên để bạn có thể tham khảo nó dễ hơn.

 

Trong Google Sheets, chúng ta sử dụng hàm QUERY và viết mã SQL giả lập bên trong hàm này. Như ở ví dụ này, chúng ta sẽ nhập vào ô G1.

= QUERY (countries,"mã SQL ở đây giữa các dấu ngoặc kép",1)

Được rồi, bây giờ chúng ta đã thiết lập xong, hãy bắt đầu viết mã SQL!

Chọn tất cả dữ liệu bằng hàm QUERY trên Google Sheets

Mã SQL SELECT * truy xuất tất cả các cột từ bảng dữ liệu.

Ở bên phải của bảng, hãy nhập hàm QUERY sau vào ô G1:

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

Kết quả từ truy vấn này sẽ trả về bảng dữ liệu đầy đủ như ban đầu, vì lệnh trong hàm là SELECT * lấy tất cả các cột từ bảng countries:

Đây chính là mã SQL mà chúng ta đang nhắc đến, nguyên tắc của nó là:

LỰA CHỌN *

TỪ “bảng countries”

Chỉ định chọn các cột cụ thể

Điều gì sẽ xảy ra nếu chúng ta không muốn chọn mọi cột mà chỉ chọn một số cột nhất định? Bạn chỉ cần sửa đổi hàm QUERY trên Google Sheets là được:

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

Bạn có thể thấy, hàm lần này sẽ chỉ chọn cột B và D; do đó, đầu ra của sẽ hiển thị như sau:

Và tương tự, nguyên tắc của nó là:

LỰA CHỌN “country, population”

TỪ “bảng countries”

Mệnh đề WHERE

Mệnh đề WHERE xác định một điều kiện phải được thỏa mãn. Nó sẽ giúp bạn lọc dữ liệu và sẽ đứng sau mệnh đề SELECT.

Để xem mệnh đề này hoạt động như thế nào nhé: bắt đầu sửa đổi hàm QUERY trên ví dụ trên (giả sử là ở ô G2) để chỉ chọn các quốc gia có dân số lớn hơn 100 triệu, hàm sẽ là:

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

Đầu ra sẽ hiển thị như sau:

Nguyên tắc của mã SQL trong trường hợp này là:

CHỌN “country, population”

TỪ “bảng countries”

TẠI “population > 100000000”

Hãy xem một ví dụ khác về mệnh đề WHERE, lần này chỉ chọn các quốc gia Châu Âu. Vậy thì sửa đổi công thức của bạn thành:

=QUERY(countries,"SELECT B, C, D WHERE C = 'Europe' ",1)

Đầu ra của chúng ta sẽ là:

Nguyên tắc của hàm sẽ là:

 CHỌN “country, continent, population”

TỪ “bảng countries”

TẠI “continent = Europe”

Mệnh đề ORDER BY

Mệnh đề ORDER BY sẽ giúp bạn sắp xếp dữ liệu; chỉ định (các) cột và hướng (tăng dần hoặc giảm dần). Nó đứng sau mệnh đề SELECT và WHERE.

Hãy sắp xếp dữ liệu ở ví dụ trên theo thứ tự dân số từ nhỏ nhất đến lớn nhất. Sửa đổi công thức của bạn để thêm mệnh đề ORDER BY sau, chỉ định hướng tăng dần với ASC:

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

Đầu ra sẽ hiển thị như sau:

Nguyên tắc của lệnh này là:

CHỌN “country, continent, population”

TỪ “bảng countries”

TGITIHO LỆNH “population ASC”

Sửa đổi công thức của bạn trong ô G1 để sắp xếp dữ liệu theo quốc gia theo thứ tự giảm dần, Z - A:

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

Đầu ra sẽ là:

Nguyên tắc của lệnh là:

CHỌN “country, continent, population”

TỪ “bảng countries”

TGITIHO LỆNH “country DESC”

Mệnh đề LIMIT

Mệnh đề LIMIT hạn chế số lượng kết quả trả về. Nó đứng sau các mệnh đề SELECT, WHERE và ORDER BY.

Hãy thêm mệnh đề LIMIT vào công thức của chúng ta trong G1 để hệ thống chỉ trả lại 10 kết quả:

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

Hệ thống sẽ chỉ trả về 10 kết quả từ bảng dữ liệu:

Nguyên tắc của mã sẽ là:

CHỌN “country, continent, population”

TỪ “bảng countries”

TGITIHO LỆNH “population ASC”

GIỚI HẠN “10”

Các hàm số học

Chúng ta có thể thực hiện các phép toán tiêu chuẩn trên các cột số. Cho dễ hình dung, chúng ta hãy thử với ví dụ trên, giả sử yêu cầu là tính xem mỗi quốc gia chiếm bao nhiêu phần trăm tổng dân số thế giới (7,16 tỷ người).

Bạn sẽ cần chia cột dân số cho tổng (7,162,119,434) và nhân với 100 để tính phần trăm. Vì vậy, hãy sửa đổi công thức trở thành:

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

Chúng ta sẽ có đầu ra như sau:

Lưu ý – ví dụ trên đã được áp dụng định dạng cho cột đầu ra để chỉ hiển thị 2 chữ số thập phân.

Mã SQL sẽ theo nguyên tắc:

CHỌN “country, continent, (population / 7162119434) * 100 “

TỪ “bảng countries”

Mệnh đề LABEL:

Tiêu đề cho cột số học khá xấu phải không? Không sao, chúng ta có thể đổi tên nó bằng mệnh đề LABEL (tuy nhiên, hãy cẩn thận vì đây không phải là một phần của cú pháp SQL).

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

Nguyên tắc của mã này là:

CHỌN “country, continent, (population / 7162119434) * 100”

TỪ “bảng countries”

Hàm tổng hợp

Chúng ta có thể sử dụng các hàm khác trong tính toán của mình, ví dụ min, max và trung bình. Ví dụ như để tính toán dân số tối thiểu, tối đa và trung bình trong tập dữ liệu quốc gia của bảng trên, bạn hãy sử dụng các hàm tổng hợp trong truy vấn như sau:

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

Đầu ra trả về ba giá trị - các tập hợp tối đa, tối thiểu và trung bình của tập dữ liệu, như sau:

Nguyên tắc của mã SQL này là:

CHỌN “tối đa (dân số), tối thiểu (dân số), trung bình (dân số)

TỪ “bảng countries”

Mệnh đề GROUP BY

Đây là khái niệm khó hiểu nhất trong cả bài viết tuy nhiên, nếu bạn đã từng sử dụng bảng tổng hợp trong Excel thì bạn sẽ không thấy khó khăn khi theo dõi mệnh đề này.

Mệnh đề GROUP BY được sử dụng với các hàm tổng hợp để tóm tắt dữ liệu thành các nhóm, giống như cách một bảng tổng hợp làm. Thử giả sử nhé: bạn muốn tóm tắt dữ liệu trên ví dụ theo châu lục và đếm xem có bao nhiêu quốc gia trên mỗi châu lục. Thay đổi công thức truy vấn để bao gồm mệnh đề GROUP BY và sử dụng hàm tổng hợp COUNT để đếm có bao nhiêu quốc gia, như sau:

=QUERY(countries,"SELECT C, count(B) GROUP BY C",1)

Lưu ý, mọi cột trong mệnh đề SELECT (tức là trước GROUP BY) phải được tổng hợp (ví dụ: đếm, tối thiểu, tối đa) hoặc xuất hiện sau mệnh đề GROUP BY (ví dụ: cột C trong trường hợp này).

Đầu ra cho truy vấn này:

Nguyên tắc của mã lúc này là:

CHỌN lục địa, số lượng (quốc gia)

TỪ các quốc gia

NHÓM TGITIHO châu lục

Hãy xem một ví dụ phức tạp hơn, kết hợp nhiều loại mệnh đề khác nhau. Sửa đổi công thức trong G1 như sau:

=QUERY(countries,"SELECT C, count(B), min(D), max(D), avg(D) GROUP BY C ORDER BY avg(D) DESC LIMIT 3",1)

Để dễ đọc hơn, hãy xem dòng lệnh được chia nhỏ sau đây:

=QUERY(countries,

"SELECT C, count(B), min(D), max(D), avg(D)

GROUP BY C

ORDER BY avg(D) DESC

LIMIT 3",1)

Lệnh này đã giúp chúng ta tóm tắt dữ liệu cho từng châu lục, sắp xếp theo dân số trung bình cao nhất đến thấp nhất và cuối cùng giới hạn kết quả chỉ ở top 3.

Đầu ra của truy vấn này là:

Nguyên tắc ở đây là:

CHỌN lục địa, số lượng (quốc gia), tối thiểu (dân số), tối đa (dân số), trung bình (dân số)

TỪ các quốc gia

NHÓM TGITIHO châu lục

TGITIHO LỆNH trung bình (dân số)

GIỚI HẠN: 3

Bài viết đã giới thiệu một cách tổng quan cùng ví dụ về ứng dụng của hàm truy vấn khi làm việc với Google Sheets. Gitiho biết khuôn khổ bài viết này vẫn là chưa đủ để thể hiện hết khả năng mà hàm này có thể; tuy nhiên, khóa học Google Sheets của chúng mình sẽ cung cấp mọi thông tin mà bạn cần!

Hãy để lại ý kiến của bạn dưới phần bình luận nhé, hẹn gặp lại bạn trong những bài viết tiếp theo!