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

To Hữu Chương
To Hữu Chương
Aug 10 2020

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

Cách sử dụng hàm truy vấn trong Google Sheets

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

Cách sử dụng hàm truy vấn trong Google Sheets ví dụ

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:

Cách sử dụng hàm truy vấn trong Google Sheets ví dụ 1

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.

Cách sử dụng hàm truy vấn trong Google Sheets ví dụ 2 

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:

Hàm QUERY trong hàm truy vấn Google Sheets

Đâ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:

Hàm QUERY trong hàm truy vấn Google Sheets 2

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

LỰA CHỌN “country, population”

TỪ “bảng countries”

Top khóa học Google Sheets được học nhiều

Xây dựng bảng lương bằng Google Sheets cơ bản từ A-Z

Tô Văn Thuật

578 học viên

0 (0 đánh giá)

Miễn phí

Xây dựng file thông tin quản lý hồ sơ nhân sự bằng Google Sheets từ A-Z

Tô Văn Thuật

521 học viên

5 (1 đánh giá)

Miễn phí

Google Sheets từ Cơ bản đến Nâng cao, công cụ thay thế Excel

Nguyễn Văn Qúy

379 học viên

5 (5 đánh giá)

499,000đ

799,000đ

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:

Mệnh đề WHERE trong hàm truy vấn Google Sheets

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

Mệnh đề WHERE trong hàm truy vấn Google Sheets 2

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:

Mệnh đề ORDER BY trong hàm truy vấn Google Sheets

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

Mệnh đề ORDER BY trong hàm truy vấn Google Sheets 2

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:

Mệnh đề LIMIT trong hàm truy vấn Google Sheets

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:

Các hàm số học trong hàm truy vấn Google Sheets

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:

Hàm tổng hợp trong hàm truy vấn Google Sheets

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:

Mệnh đề GROUP BY trong hàm truy vấn Google Sheets 1

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

Mệnh đề GROUP BY trong hàm truy vấn Google Sheets

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!

0/5 - (0 bình chọn)

0/5 - (0 bình chọn)

Bài viết liên quan

Hướng dẫn tạo danh sách tùy chọn Drop List trong Google Sheets

Hướng dẫn tạo danh sách tùy chọn Drop List trong Google Sheets

Hướng dẫn cách cố định hàng/cột trong Google Sheets

Hướng dẫn cách cố định hàng/cột trong Google Sheets

Hướng dẫn cách bảo vệ trang tính trong Google Sheets

Hướng dẫn cách bảo vệ trang tính trong Google Sheets

Hướng dẫn cách khắc phục lỗi về ô và dòng trong Google Sheets

Hướng dẫn cách khắc phục lỗi về ô và dòng trong Google Sheets

Tổng hợp các phím tắt thông dụng trong Google Sheets

Tổng hợp các phím tắt thông dụng trong Google Sheets

Hướng dẫn tô màu xen kẽ giữa các hàng trong Google Sheets

Hướng dẫn tô màu xen kẽ giữa các hàng trong Google Sheets

@ 2020 - Bản quyền của Công ty cổ phần công nghệ giáo dục Gitiho Việt Nam
Giấy chứng nhận Đăng ký doanh nghiệp số: 0109077145, cấp bởi Sở kế hoạch và đầu tư TP. Hà Nội