Hàm QUERY trong Google Sheets cho phép bạn dễ dàng xử lý dữ liệu của mình cho các mục đích khác nhau một cách hiệu quả. Nó giúp trích xuất thông tin quan trọng từ các bộ 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 ích đáng kể 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 Google Visualization, cho phép xử lý dữ liệu đa dạng.
Lấy cảm hứng từ bài viết này, chúng ta sẽ tìm hiểu cách sử dụng hàm QUERY trong Google Sheets một cách toàn diệ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ác với hầu hết các hàm khác, việc hiểu cách sử dụng nó không thực sự đơn giản chỉ bằng cách xem xét các đối số. Nhưng đừng lo lắng. Bạn sẽ hiểu rõ hơn thông qua các 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ác ô (bảng) mà bạn muốn thực hiện truy vấn trên. Điều quan trọng là tránh dữ liệu có kiểu hỗn hợp trong các cột để đảm bảo kết quả chính xác.
Bạn có thể xem đây là một bảng trong Google Sheets. Ví dụ: A1:F1000.
-
query: Truy vấn mà bạn muốn chạy phải được bao bọc bởi dấu nháy kép. Tuy nhiên, bạn cũng có thể nhập văn bản thích hợp vào một ô và tham chiếu đến nó. -
headers: Số hàng tiêu đề trong dữ liệu của bạn (bảng). Thông thường, sẽ có một hàng tiêu đề hoặc không có hàng tiêu đề. Nếu đối số này bị bỏ qua hoặc được đặt thành -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 này trả về các cột A và C.
Để nắm vững hàm QUERY trong Google Sheets, bạn cần tập trung vào hiểu rõ đối số truy vấn, đó là một chủ đề rộng lớn.
Đơn giản hóa đối số ‘Truy vấn’
Khi bạn đọc một công thức QUERY được viết bởi người khác, phần gây nhầm lẫn nhất sẽ là phần truy vấn trong dấu nháy 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 xử lý dữ liệu và các yếu tố ngôn ngữ.
Cố gắng học hết tất cả trong một hướng dẫn duy nhất không phải là khả thi. Do đó, cách tiếp cận của chúng tôi ở đây là hướng dẫn bạn trong việc hiểu các kỹ thuật cơ bản xử lý dữ liệu bằng cách sử dụng hàm QUERY trong Google Sheets.
Trong quá trình đó, tôi sẽ hướng dẫn bạn tìm hiểu các hướng dẫn nâng cao về chức năng này trên blog của chúng tôi để nắm vững một trong những chức năng mạnh mẽ nhất trong Google Sheets. Let’s go!
Dữ liệu mẫu để kiểm tra Hàm QUERY
Chúng ta sẽ xem xét dữ liệu mẫu được trải dài trên sáu cột và mười sáu hàng trong phạm vi A1:F16. Nhãn trường trong A1:F1 như sau:
A B C D E F
1Đây là tab ‘Sheet1’. Bạn có thể nhận bản sao của toàn bộ dữ liệu bằng cách nhấp vào nút bên dưới.
Chúng ta sẽ sử dụng dữ liệu được tìm thấy trên Sheet mẫu để tìm hiểu hàm QUERY. Trước khi bắt đầu, hãy lướt 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 đề.
Vì vậy, theo dữ liệu Sheet mẫu của tôi, dữ liệu sẽ là A1:F (khoảng mở) hoặc A1:F16 (khoảng đóng), và tiêu đề sẽ là 1. Bên dưới, chúng tôi sẽ tập trung vào đối số truy vấn.
Chọn các cột trong Hàm QUERY (Mệnh đề SELECT)
Phần này đề cập cách chọn các cột trong hàm QUERY.
Các ví dụ về việc sử dụng hàm QUERY trong Google Sheets
Trong dữ liệu mẫu của chúng ta ở trên, bao gồm sáu cột, bạn có thể xử lý và chọn các cột cụ thể bằng cách sử dụng các 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 trật tự khác.
=QUERY(Sheet1!A1:F16, "select A, E", 1)Công thức này được sử dụng để chỉ trả về 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 của bảng nguồn dữ liệu sẽ được trả về theo thứ tự ban đầu của chúng.
Một cách 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 bị 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ề đầu ra với 1 hàng x 6 cột, với tất cả các giá trị là 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 để Kết hợp Các Cột trong Google Sheets
Lọc hàng bằ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.
Giờ thì sao về việc lọc hàng?
Để làm được điều này, chúng ta cần sử dụng FROM (keyword) trong hàm QUERY.
Trong FROM, 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 toán tử logic (AND, OR và NOT) để lọc dữ liệu.
Trong dữ liệu mẫu của chúng ta, để lọc 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 trên lọc các tên có giới tính là “M”.
Công thức dưới đây lọc các tên có tuổi là 12:
=QUERY(Sheet1!A1:F, "Select A where F=12 ", 1)Câu lệnh truy vấn tiếp theo lọc các tên có ngày tham gia là 01/01/2015:
=QUERY(Sheet1!A1:F, "Select A where E=date '2015-01-01'", 1)Ba ví dụ công thức truy vấn trên phục vụ hai mục đích:
-
Chứng minh cách sử dụng từ khóa WHERE trong hàm QUERY của Google Sheets.
-
Cho thấy việc sử dụng các loại giá trị khác nhau (hằng số), chẳng hạn như văn bản, số và ngày, để so sánh.
Trong khi các công thức này cung cấp cái nhìn về việc sử dụng từ khóa WHERE trong hàm QUERY của Google Sheets, để thực hiện xử lý dữ liệu toàn diện, rất đáng khuyến nghị để tìm hiểu thêm các hướng dẫn bổ sung. Vui lòng khám phá các hướng dẫn dưới đây khi rảnh rỗi.
Nhóm và Tổng hợp dữ liệu (Mệnh đề GROUP BY)
Chúng ta đã tìm hiểu cách chọn và lọc các cột. Phần này sẽ đàm phán về cách nhóm và tổng hợp dữ liệu bằng cách sử dụng hàm QUERY.
Để làm điều này, chúng ta sử dụng mệnh đề GROUP BY (từ khóa kết hợp), cũng là một chủ đề toàn diện, giống với mệnh đề WHERE.
Hãy khám phá một số ví dụ cơ bản trước tiên. Sau đó, tôi sẽ hướng dẫn bạn tìm hiểu nơi để tìm các ví dụ sử dụng nâng cao của tính năng này.
Cách sử dụng hàm tổng hợp
Công thức dưới đây 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 tham gia 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 dữ liệu mở, hãy nhớ sử dụng từ khóa WHERE để loại bỏ các hàng trống khỏi kết quả.
=QUERY(Sheet1!A1:F, "Select F, count(F) where F is not null group by F", 1)Khi nhìn vào các ví dụ công thức dưới đây, tôi không đã cho ví dụ về cách lọc cột giá trị Boolean TRUE hoặc FALSE vì tôi không có cột như vậy trong dữ liệu mẫu của mình.
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:
• Khớp chuỗi phân biệt phân biệt chữ hoa / chữ thường trong hàm QUERY. Sử dụng các hàm UPPER () hoặc LOWER () để thực hiện khớp chuỗi không phân biệt chữ hoa / chữ thường.
Ví dụ:
=QUERY(Sheet1!A1:F, "Select A where lower(D)='m' ", 1)Bạn có thể nhận thấy điều gì đó trong các ví dụ công thức dưới mục WHERE.
Tôi đã sử dụng phạm vi mở Sheet1!A1:F thay vì Sheet1!A1:F16 (đóng).
Điều này vì chúng tôi đang 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ả. Để lọc ra các hàng trống ngay lập tức, bạn có thể sử dụng KHÔNG PHẢI NULL.
=QUERY(Sheet1!A1:F, "Select A, E, F where A is not null", 1)Tạo bảng tổng kết dữ liệu (Mệnh đề PIVOT)
Tôi có thể giúp bạn hiểu mệnh đề PIVOT (keyword) với ba ví dụ.
Công thức QUERY Google Sheets dưới đây (trong ô H1 trong ảnh chụp màn hình dưới) nhóm ngày tham gia và giới tính và trả về số lượng.
=QUERY(Sheet1!A1:F16, "Select E, D, count(E) group by E, D", 1)Vì vậy, kết quả chứa ba cột: Ngày tham gia, Giới tính và số lượng Ngày tham gia.
Giờ hãy xem công thức QUERY (trong ô H9 trong ảnh chụp màn hình trên) sử dụng từ khóa PIVOT.
=QUERY(Sheet1!A1:F16, "Select E, count(E) group by E pivot D", 1)Tôi đã bỏ qua cột D trong mệnh đề SELECT và GROUP BY, thay vào đó sử dụng trong mệnh đề PIVOT.
Ở đây cũng, cột đầu tiên trong đầu ra sẽ là Ngày tham gia (cột E). Sau đó sẽ có hai cột nữa vì cột PIVOT D (Giới tính) chứa hai giá trị phân biệt, tức là “M” và “F”. Số lượng (các giá trị đã được tổng hợp) sẽ nằm dưới hai cột này.
Bảng tổng hợp PIVOT rất hữu ích để chuẩn bị biểu đồ cột, đường, cột hoặc bánh (hình ảnh hóa dữ liệu) trong Google Sheets.
Chúng ta có thể sử dụng từ khóa PIVOT mà không cần sử dụng từ khóa GROUP BY trong hàm QUERY không?
Chính xác! Đây là một ví dụ.
=QUERY(Sheet1!A1:F16, "Select count(A) pivot E", 1)Vui lòng tự thử nghiệm công thức này.
Dưới đây là một số tài nguyên bổ sung để khám phá thêm mệnh đề PIVOT trong hàm QUERY trong Google Sheets.
- Cách sử dụng Hàm QUERY Tương tự Bảng Tổng hợp trong Google Sheets
- Cách định dạng Tiêu đề hàng Pivot trong Google Sheets Query
- Cách Pivot Nhiều Cột trong Truy vấn trong Google Sheets
Sắp xếp dữ liệu (Mệnh đề ORDER BY)
Với sự trợ giúp của mệnh đề 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ụ #1 (Order theo mặt hàng là một định danh của cột):
=QUERY(Sheet1!A1:F16, "select A, B, F order by A asc", 1)Công thức trê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 sắp xếp DOB trong cột B theo thứ tự giảm dần, sau đó tên trong cột A cũng theo thứ tự tăng dần.
=QUERY(Sheet1!A1:F16, "select A, B, F order by B desc, A asc", 1)Ví dụ #2 (Truy vấn đế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)Bạn có thể sử dụng hai loại mục trong từ khóa ORDER BY: 1) Định danh cột. 2) Kết quả của các hàm tổng hợp, hàm cá nhân hoặc các toán tử.
Giới hạn số hàng (Mệnh đề LIMIT)
Mục đích của mệnh đề LIMIT (từ khóa) trong hàm QUERY của Google Sheets là giới hạn số hàng được trả về.
Trong công thức chỉ trên, bạn có thể sử dụng LIMIT 3 để hạn chế số hàng được trả về là 3 hàng, không tính 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 (Mệnh đề OFFSET)
Mục đích của mệnh đề OFFSET (từ khóa) là bỏ qua một số hàng đầu tiên đã cho.
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 trước tiên bỏ qua các hàng, sau đó áp dụng giới hạn.
Đặt nhãn và định dạng kết quả truy vấn (Mệnh đề LABEL và FORMAT)
Đôi khi công thức QUERY trả về các tiêu đề (nhãn) ‘xấu’. Vui lòng xem kết quả công thức H1 ở phần trên cùng của ảnh chụp màn hình dưới đây.
Mệnh đề LABEL (từ khóa) giúp đặt chú thích cho một hoặc nhiều cột. Vì vậy, chúng ta có thể đổi nhãn ‘xấu’ thành nhãn 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 Mệnh đề Nhãn trong Truy vấn Google Sheets.
Kết luận
Bài viết này bao quát mọi thứ bạn cần để làm chủ 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 ô bình luận bên dưới.
Tham khảo: Truy vấn hướng dẫn ngôn ngữ (Phiên bản 0.7)
Cảm ơn bạn đã ở lại. Chúc bạn có thời gian vui vẻ!
Liên quan:
