Hàm QUERY trong Google Sheets giúp bạn hiệu quả thao tác dữ liệu cho nhiều mục đích dễ dàng hơn bao giờ hết. Nó hỗ trợ trích xuất thông tin cần thiết từ tập dữ liệu phức tạp và tạo báo cáo, biểu đồ, và nhiều hơn nữa.
Một lợi thế đáng chú ý của hàm này là khả năng thực hiện các truy vấn trong ngôn ngữ truy vấn API Visualization của Google, cho phép thực hiện các thao tác dữ liệu đa dạng.
Về bản chất, hàm QUERY đứng như một chức năng tối thượng để thao tác dữ liệu trong Google Sheets, và bài hướng dẫn này bao quát tất cả những gì cần để nắm bắt nó.
Hàm QUERY: Cú Pháp và Đối số
Hãy bắt đầu với cú pháp của hàm QUERY trong Google Sheets.
Không giống như hầu hết các hàm khác, hiểu cách sử dụng nó không phải lúc nào cũng dễ dàng chỉ bằng cách xem xét các đối số. Nhưng đừng lo, bạn sẽ hiểu rõ hơn thông qua ví dụ và hướng dẫn từng bước của tôi.
Cú pháp:
QUERY(data, query, [headers])Đối số:
-
data: Phạm vi của các ô (bảng) trên đó thực hiện truy vấn. Để đảm bảo kết quả chính xác, tránh sự kết hợp của các loại dữ liệu trong các cột. -
query: Truy vấn bạn muốn thực hiện phải được bao quanh bởi ký tự ngoặc kép. Tùy chọn khác, bạn có thể nhập văn bản thích hợp trong một ô và tham chiếu đến nó. -
headers: Số hàng tiêu đề trong dữ liệu (bảng) của bạn. Thông thường, chỉ có một hàng tiêu đề hoặc không có hàng tiêu đề nào. Nếu đối số này bị bỏ qua hoặc được đặt là -1, giá trị tiêu đề sẽ được đoán dựa trên nội dung của dữ liệu nguồn.
Ví dụ:
=QUERY(A1:F1000, "Select A, C", 1)Trong ví dụ này, A1:F1000 đại diện cho dữ liệu, “Select A, C” là truy vấn và 1 chỉ định tiêu đề.
Công thức trả về các cột A và C.
Để nắm vững hàm QUERY trong Google Sheets, bạn phải tập trung vào việc hiểu đối số truy vấn, một chủ đề rộng lớn.
Đơn Giản Hóa Đối Số ‘Query’
Khi bạn đọc một công thức QUERY được viết bởi ai đó, phần khó hiểu nhất sẽ là đoạn truy vấn nằm trong dấu ngoặc kép. Điều này bởi vì nó chứa các mệnh đề ngôn ngữ (từ khóa), các hàm thao tác dữ liệu và các yếu tố ngôn ngữ.
Cố gắng học tất cả trong một hướng dẫn là không thực tế. Do đó, cách tiếp cận của chúng tôi ở đây là hướng dẫn bạn cách tìm các kỹ thuật thao tác dữ liệu cơ bản sử dụng hàm QUERY trong Google Sheets.
Trong quá trình đó, tôi sẽ chỉ bạn nơi tìm hiểu về các hướng dẫn nâng cao khác trên blog này để nắm vững một trong những chức năng mạnh mẽ nhất trong Google Sheets. Đến đây thôi!
Dữ Liệu Mẫu để Kiểm Tra Hàm QUERY
Chúng ta sẽ xem xét tập dữ liệu mẫu bao gồm sáu cột và mười sáu hàng trong phạm vi A1: F16. Các nhãn trường trong A1: F1 là như sau:
A B C D E F
Đó là trên tab ‘Sheet1’. Bạn có thể tải xuống toàn bộ dữ liệu bằng cách nhấp vào nút bên dưới.
Dữ liệu mẫu
Chúng ta sẽ sử dụng dữ liệu được tìm thấy trong Bảng mẫu để tìm hiểu cách sử dụng hàm QUERY. Trước khi bắt đầu, hãy cuộn lên và xem lại cú pháp.
Có ba đối số trong cú pháp. Đối số đầu tiên và cuối cùng là dữ liệu và tiêu đề, tương ứng.
Vì vậy, theo mẫu Sheet của tôi, dữ liệu sẽ là A1: F (phạm vi mở) hoặc A1: F16 (phạm vi đóng), và tiêu đề sẽ là 1. Dưới đây, chúng tôi sẽ tập trung vào đối số truy vấn.
Lựa Chọn Cột Trong Hàm QUERY (Mệnh Đề SELECT)
Phần này sẽ giới thiệu cách chọn các cột trong hàm QUERY.
Các cột cụ thể có thể được thao tác và chọn bằng cách sử dụng mệnh đề SELECT trong hàm QUERY.
Ví dụ Về Việc Sử Dụng Hàm QUERY Trong Google Sheets
Trong dữ liệu mẫu, bao gồm sáu cột, bạn có thể thao tác và chọn các cột cụ thể bằng cách sử dụng mệnh đề SELECT (từ khóa) trong hàm QUERY.
=QUERY(Sheet1!A1:F16, "select A, B, F, C, D, E", 1)Công thức trên trả về các cột theo một thứ tự khác.
=QUERY(Sheet1!A1:F16, "select A, E", 1)Công thức này được sử dụng để trả về chỉ các cột A và E.
Nếu bạn thay thế “select A, E” bằng “select *”, tất cả các cột trong bảng nguồn dữ liệu sẽ được trả về theo thứ tự ban đầu của chúng.
Một cách thay thế khác để trả về tất cả các cột theo thứ tự ban đầu là công thức sau:
=QUERY(Sheet1!A1:F16,, 1)Ở đây, đối số truy vấn được bỏ qua. Trong công thức này, chỉ dữ liệu và tiêu đề được chỉ định.
Nếu bạn chỉ định 16 trong tiêu đề thay vì 1, công thức sẽ trả về kết quả là 1 hàng x 6 cột, với tất cả các giá trị là một hàng tiêu đề. Để biết thêm thông tin, bạn có thể đọc về nó ở đây: Công Thức Mảng Linh Hoạt Để Gộp Các Cột Trong Google Sheets.
Lựa Chọn Kết Quả từ Các Hàm Tổng Hợp, Hàm Kỹ Thuật Số Và Toán Tử Số
Trên, chúng ta đã khám phá việc lựa chọn các trường/cột hiện có trong bảng mà không có bất kỳ thao tác nào. Dưới đây, hãy xem cách xử lý các giá trị trong các cột và trả về kết quả.
Hàm Kỹ Thuật Số Năm Trong Mệnh Đề SELECT:
=QUERY(Sheet1!A1:F16, "select A, year(E)", 1)Công thức này trả về các tên trong cột A và năm gia nhập trong cột E. Cột E trong bảng dữ liệu nguồn chứa ngày gia nhập.
Hàm Tổng Hợp Avg Trong Mệnh Đề SELECT:
Bạn có thể sử dụng công thức này khi bạn muốn trả lại trung bình các giá trị trong cột F, chứa tuổi.
=QUERY(Sheet1!A1:F16, "select avg(F)", 1)Hàm Kỹ Thuật Số Năm Và Toán Tử Trừ Trong Mệnh Đề SELECT:
Để tìm tuổi tại thời điểm gia nhập, chúng ta có thể trừ năm sinh từ năm gia nhập. Dưới đây là một ví dụ.
=QUERY(Sheet1!A1:F16, "select A, year(E)-year(B)", 1)Lưu ý: Chúng tôi chỉ xem xét các năm. Do đó, tuổi sẽ không hoàn toàn chính xác do vấn đề tháng/năm đầy đủ.
Bạn có thể tìm hiểu thêm về các hàm kỹ thuật số, toán tử số và hàm tổng hợp trong một phần sau của hướng dẫn này.
Định Danh Cột
Như các ví dụ trên đã chứng minh, định danh cột thường là các chữ cái viết hoa chỉ gồm một hoặc hai ký tự, chẳng hạn A, B, C, …. AA, AB, v.v.
Tuy nhiên, trong một số tình huống, chúng ta có thể cần sử dụng các định danh như Col1, Col2, v.v. Bạn có biết chính xác khi nào điều này cần thiết?
Nếu dữ liệu của bạn nằm trong một phạm vi vật lý như A1:F16, các định danh cột sẽ là những chữ cái viết hoa tiêu chuẩn – A, B, C, v.v.
Tuy nhiên, khi làm việc với biểu thức, ví dụ như kết quả của các công thức khác, cột đầu tiên trong phạm vi trở thành Col1, cột thứ hai trở thành Col2 và cứ tiếp tục như vậy.
Trong khi thảo luận về việc chọn cột trong hàm QUERY của Google Sheets, công thức dưới đây minh họa cách chèn một cột giữa A và B, được điền với dấu gạch ngang:
=QUERY(Sheet1!A1:F16, "Select A, '-', B", 1)Lọc Dòng Sử Dụng Hàm QUERY Trong Google Sheets (Mệnh Đề WHERE)
Trên, chúng ta đã khám phá cách sử dụng từ khóa SELECT trong hàm QUERY của Google Sheets để chọn các cột.
Bây giờ, chúng ta hãy tìm hiểu về việc lọc dòng.
Để làm được điều này, chúng ta cần sử dụng mệnh đề WHERE (từ khóa).
Trong mệnh đề WHERE, chúng ta phải sử dụng các toán tử so sánh (đơn giản hoặc phức tạp) hoặc các toán tử logic (AND, OR & NOT) để lọc.
Trên dữ liệu mẫu của chúng ta, để lọc theo giới tính “M”, chúng ta có thể sử dụng công thức sau:
=QUERY(Sheet1!A1:F, "Select A, D where D='M' ", 1)Công thức dưới đây lọc tên với tuổi là 12:
=QUERY(Sheet1!A1:F, "Select A where F=12 ", 1)Truy vấn tiếp theo lọc tên với ngày gia nhập là 01/01/2015:
=QUERY(Sheet1!A1:F, "Select A where E=date '2015-01-01'", 1)Ba ví dụ về công thức Query trên có hai mục đích:
-
Hướng dẫn cách sử dụng từ khóa WHERE trong hàm QUERY của Google Sheets.
-
Min rõ việc sử dụng các loại giá trị khác nhau (chữ, số, ngày) để so sánh.
Mặc dù những công thức này cung cấp thông tin về việc sử dụng từ khóa WHERE trong hàm QUERY của Google Sheets, để thực hiện thao tác thao tác dữ liệu toàn diện, hướng dẫn bổ sung ĐƯỢC ĐỀ NGHỊ RẤT NHIỀU. Vui lòng khám phá các hướng dẫn dưới đây khi rảnh rỗi.
Ở trên, tôi không đưa ra ví dụ về cách lọc một cột giá trị Boolean TRUE hoặc FALSE vì tôi không có một cột như vậy trong dữ liệu mẫu của tôi.
Nếu cột G chứa TRUE hoặc FALSE, sau đó sử dụng WHERE G = TRUE hoặc WHERE G = FALSE.
Quan trọng:
So sánh chuỗi là phân biệt chữ hoa và chữ thường trong hàm QUERY. Sử dụng các hàm kỹ thuật với chuỗi chữ hoa HOẶC chữ thường để thực hiện so khớp chuỗi không phân biệt chữ hoa/thường.
Ví dụ:
=QUERY(Sheet1!A1:F, "Select A where lower(D)='m' ", 1)Bạn có thể đã nhận thấy một điều trong các ví dụ công thức dưới từ khóa WHERE.
Tôi đã sử dụng phạm vi Sheet1!A1:F thay vì Sheet1!A1:F16 (phạm vi đã đóng).
Điều này bởi vì chúng tôi đã lọc dựa trên các điều kiện có thể loại bỏ các hàng trống khỏi kết quả. Để loại bỏ các hàng trống ngay tức thì, bạn có thể sử dụng KHÔNG NULL như sau.
=QUERY(Sheet1!A1:F, "Select A, E, F where A is not null", 1)Nhóm và Tổng Hợp (Từ khóa GROUP BY)
Chúng ta đã học cách chọn và lọc cột. Phần này sẽ đào sâu vào cách nhóm và tổng hợp dữ liệu bằng cách sử dụng hàm QUERY.
Để làm được điều này, chúng ta sử dụng từ khóa GROUP BY (từ khóa kết hợp), cũng là một chủ đề tổng quan, tương tự như từ khóa WHERE.
Hãy khám phá một số ví dụ cơ bản trước tiên. Sau đó, tôi sẽ chỉ bạn nơi tìm hiểu về cách sử dụng tiên tiến của chức năng này.
Điểm quan trọng:
Đây là những điều cần lưu ý quan trọng khi sử dụng từ khóa GROUP BY kết hợp trong hàm QUERY của Google Sheets:
-
Nó tổng hợp các giá trị qua các hàng.
-
Một hàng duy nhất được tạo ra cho mỗi sự kết hợp riêng biệt các giá trị trong mệnh đề GROUP BY.
-
Dữ liệu được sắp xếp tự động theo các cột nhóm.
-
Tất cả các định danh cột được chỉ định trong mệnh đề SELECT phải là một phần của mệnh đề GROUP BY. Tuy nhiên, bạn cũng có thể bao gồm các định danh cột chưa được chỉ định.
Ví dụ Sử Dụng Hàm Tổng Hợp
Công thức sau nhóm dữ liệu theo tuổi và trả về số lượng.
=QUERY(Sheet1!A1:F16, "Select F, count(F) group by F", 1)Và công thức này nhóm dữ liệu theo tuổi và ngày gia nhập và trả về số lượng.
=QUERY(Sheet1!A1:F16, "Select F, E, count(F) group by F, E", 1)Tôi đã sử dụng hàm tổng hợp đếm trong các công thức trên. Chúng ta có thể sử dụng nhiều hàm tổng hợp khác. Tôi sẽ bàn về điều đó sau.
Nếu bạn sử dụng một phạm vi mở, hãy nhớ sử dụng từ khóa WHERE để lọc bỏ các hàng trống trong việc tổng hợp.
=QUERY(Sheet1!A1:F, "Select F, count(F) where F is not null group by F", 1)Ví dụ Sử Dụng Hàm Kỹ Thuật Số
=QUERY(A1:F, "Select year(E), count(E) where E is not null group by year(E)", 1)Nó nhóm dữ liệu theo năm gia nhập và trả về số lượng.
Công thức sử dụng hàm kỹ thuật số YEAR() để trích xuất năm từ ngày gia nhập trong cột E.
Ví dụ Sử Dụng Toán Tử Số
=QUERY(A1:F, "Select year(now())-year(E), count(E) where E is not null group by year(now())-year(E)", 1)Bạn có thể thấy việc sử dụng toán tử trừ trong công thức trên. Nó nhóm theo số năm kể từ khi gia nhập và trả về số lượng.
Chúng ta phải sử dụng các mệnh đề ngôn ngữ truy vấn (từ khóa hoặc kết hợp từ khóa) theo một trật tự cụ thể và chúng tôi đang học chúng theo trật tự đó trong hướng dẫn này. Bạn có thể đọc thêm về điều đó ở đây: Trật Tự Mệnh Đề Chính Xác Trong Query Google Sheets?
Nhóm và Tổng Hợp: Tài Nguyên
-
Làm thế nào để Sử Dụng Hàm QUERY Tương Tự Bảng Pivot trong Google Sheets.
-
Làm thế nào để Định Dạng Hàng Đầu Tiên trong Truy Vấn trong Google Sheets.
-
Làm thế nào để Pivot Nhiều Cột trong Query trong Google Sheets.
Sắp Xếp Dữ Liệu (Mệnh Đề SORTBY)
Bằng cách sử dụng từ khóa ORDER BY (kết hợp từ khóa), chúng ta có thể sắp xếp các hàng trong dữ liệu nguồn theo giá trị trong các cột đã chỉ định.
Ví dụ số 1 (Sắp xếp một mục là định danh cột):
=QUERY(Sheet1!A1:F16, "select A, B, F order by A asc", 1)Nó sắp xếp các tên trong cột A theo thứ tự tăng dần. Công thức sau đây sắp xếp ngày sinh trong cột B theo thứ tự giảm dần và sau đó sắp xếp các tên trong cột A theo thứ tự tăng dần.
=QUERY(Sheet1!A1:F16, "select A, B, F order by B desc, A asc", 1)Ví dụ số 2 (Sắp xếp một mục là kết quả của hàm tổng hợp):
=QUERY(Sheet1!A1:F, "Select F, count(F) where F is not null group by F order by count(F) desc", 1)Lưu ý: Bạn có thể sử dụng hai loại mục trong từ khóa ORDER BY: 1) Các định danh cột. 2) Kết quả của các hàm tổng hợp, hàm kỹ thuật số hoặc toán tử.
Giới Hạn Số Lượng Hàng (Mệnh Đề LIMIT)
Mục đích của từ khóa LIMIT trong hàm QUERY của Google Sheets là giới hạn số lượng hàng trả về.
Trong công thức trên, chúng ta có thể sử dụng LIMIT 3 để hạn chế số hàng trả về là 3 hàng, loại trừ tiêu đề.
Ví dụ:
=QUERY(Sheet1!A1:F, "Select F, count(F) where F is not null group by F order by count(F) desc limit 3", 1)Bỏ Qua Các Hàng Đầu (Mệnh Đề OFFSET)
Mục đích của từ khóa OFFSET là bỏ qua một số hàng đầu tiên.
Ví dụ 1:
=QUERY(Sheet1!A1:F, "Select * offset 3", 1)Ví dụ 2:
=QUERY(Sheet1!A1:F, "Select F, count(F) where F is not null group by F order by count(F) desc limit 3 offset 1", 1)Trong ví dụ thứ hai ở trên, chúng ta cũng đã sử dụng từ khóa LIMIT. Truy vấn đầu tiên lùi các hàng và sau đó áp dụng giới hạn.
Đánh Nhãn Và Định Dạng Kết Quả Truy Vấn (Mệnh Đề LABEL và FORMAT)
Đôi khi, công thức truy vấn có thể trả lại tiêu đề (nhãn) “xấu xí”. Hãy xem công thức kết quả trên cùng của tôi ở phần trên cùng của ảnh chụp màn hình dưới đây.
Công thức LABEL (từ khóa) giúp đặt nhãn cho một hoặc nhiều cột. Vì vậy, chúng ta có thể đổi nhãn “xấu xí” như sau.
=QUERY(Sheet1!A1:F16, "select A, year(E)-year(B) label year(E)-year(B) 'Age at the time of Joining'", 1)Nó sẽ thay thế nhãn difference(year(Date of Joining)year(DOB)) bằng Age at the time of Joining.
Liên quan: Hiểu Rõ Về Từ Khóa LABEL Trong Bing Google.
Kết Luận
Bài viết này bao quát tất cả những gì bạn cần để nắm vững hàm QUERY trong Google Sheets. Hãy để lại bất kỳ thắc mắc hoặc câu hỏi nào trong phần bình luận dưới đây.
Tham khảo: Query Language Reference (Phiên Bản 0.7)
Cảm ơn bạn đã ở lại. Chúc bạn có những trải nghiệm thú vị.
