Google Sheets QUERY là một trong những chức năng mạnh mẽ nhất của GSuite. Nó rất linh hoạt và có thể được áp dụng cho các vấn đề đơn giản và phức tạp.
Hướng dẫn này sẽ đưa bạn đi qua chức năng Google Sheets QUERY, nơi áp dụng nó, cú pháp cơ bản và nhiều hơn nữa. Đừng lo: chúng ta sẽ đi từng bước, bắt đầu từ các ví dụ đơn giản đến các ví dụ phức tạp hơn.
Google Sheets QUERY là gì?
Chức năng Google Sheets QUERY giúp bạn áp dụng một truy vấn vào một bảng dữ liệu trong Google Sheets. Ví dụ, tôi thường sử dụng nó để trích xuất các tập con dữ liệu từ các tập dữ liệu chính. Điều này cho phép tôi phân tích các khu vực quan tâm cụ thể và có được hiểu biết phân tích tốt hơn.
Một truy vấn có thể được coi như một bộ lọc, một bảng tổng hợp và nhiều hơn thế. Nếu bạn đã làm việc với cơ sở dữ liệu bằng cách sử dụng SQL, bạn sẽ thấy rằng định dạng truy vấn của Google Sheets QUERY khá giống nhau. Trong thực tế, chức năng Google Sheets QUERY chủ yếu liên quan đến việc sử dụng các truy vấn kiểu SQL để áp dụng chúng vào một tập dữ liệu cụ thể.
Cú pháp của chức năng Google Sheets QUERY
Cú pháp của chức năng QUERY như sau:
=QUERY(data, query_string, [headers])Data: phạm vi các ô chứa dữ liệu của bạnQuery_string: chứa truy vấn bạn muốn thực thi trên dữ liệu của bạnHeaders: số hàng tiêu đề ở phía trên tập dữ liệu của bạn. Tham số này là tùy chọn.
Chức năng QUERY đọc truy vấn được cung cấp trong query_string, áp dụng nó vào dữ liệu được cung cấp và trả về bảng kết quả.
Lưu ý: query_string không chứa bất cứ thứ gì ngoài một truy vấn.
Một cách thú vị, định dạng truy vấn trong Google Sheets tương tự như định dạng truy vấn SQL thông thường. Một truy vấn thường bao gồm một hoặc nhiều mệnh đề và cũng có thể chứa các hàm tổng hợp tùy chọn và/hoặc các phép tính số học.
Các mệnh đề và QUERY
Một mệnh đề là một phần của truy vấn cho phép bạn lọc dữ liệu được cung cấp. Một số mệnh đề cho phép bạn tùy chỉnh cách dữ liệu của bạn sẽ được truy vấn.
Ví dụ, mệnh đề SELECT cho phép bạn chọn một tập con cột từ tập dữ liệu của bạn. Mệnh đề WHERE của các truy vấn Google Sheets bổ sung mệnh đề SELECT bằng cách lọc các cột đã chọn theo một điều kiện.
Đây là các mệnh đề truy vấn có thể được áp dụng cho dữ liệu của bạn. Lưu ý rằng chúng phải được sử dụng theo thứ tự sau đây:
- Select
- Where
- Group by
- Pivot
- Order By
- Limit
- Offset
- Label
- Format
- Options
Hãy nhớ rằng bạn không thể chỉ định “group by” trước “where” hoặc “select”. Thứ tự trên là thứ tự bạn sẽ phải sử dụng với chức năng Query.
Các hàm tổng hợp và QUERY
Các hàm tổng hợp là các hàm thực hiện tính toán trên các giá trị. Các hàm này thường trả về một giá trị duy nhất sau khi tính toán. Dưới đây là một số ví dụ về các hàm tổng hợp:
- SUM – Tổng tất cả các giá trị trong một cột cụ thể (hoặc một phạm vi cột con)
- COUNT – Đếm số dòng trong một cột cụ thể (hoặc một phạm vi cột con)
- AVG – Tính trung bình các giá trị trong một cột cụ thể (hoặc một phạm vi cột con)
- MIN – Tìm giá trị nhỏ nhất trong một cột cụ thể (hoặc một phạm vi cột con)
- MAX – Tìm giá trị lớn nhất trong một cột cụ thể (hoặc một phạm vi cột con)
Các hàm tổng hợp thường được sử dụng kết hợp với mệnh đề GROUP BY (chúng ta sẽ xem xét nó trong bài hướng dẫn này).
Lưu ý: Trừ hàm COUNT, tất cả các hàm tổng hợp bỏ qua các giá trị null.
Phép tính số và QUERY
Các phép tính số là các biểu thức được tạo thành từ một hằng số, một biến hoặc một hàm tiêu chuẩn, cùng với các toán tử như cộng (+), trừ (-), nhân (*), chia (/) hoặc và dư (%). Chúng cũng có thể bao gồm các toán tử so sánh như >, <, =, <=, >=.
Những toán tử này được sử dụng để thực hiện các phép tính toán trên dữ liệu được lựa chọn từ tập dữ liệu chính của bạn.
10 ví dụ về truy vấn Google Sheets
Để chỉ bạn cách sử dụng chức năng QUERY của Google Sheets, chúng tôi sẽ áp dụng các truy vấn khác nhau vào tập dữ liệu mẫu sau:
1. Một truy vấn đơn giản sử dụng mệnh đề SELECT
Bắt đầu với một công thức truy vấn đơn giản trong Google Sheets. Chúng tôi muốn chọn và hiển thị chỉ một cột từ dữ liệu.
=QUERY(A:F,"SELECT A",1)Công thức trên áp dụng truy vấn “SELECT A” vào bảng dữ liệu A:F.
Trong SQL, công thức này tương đương với truy vấn “SELECT Name FROM Dataset” (nếu các cột tham chiếu A:F được đặt tên là Dataset).
Mệnh đề SELECT cho biết cho chức năng biết các cột nào của tập dữ liệu chính sẽ được xem xét khi trích xuất dữ liệu. Biểu thức truy vấn được viết bằng ngôn ngữ truy vấn của API Visualisation Google (tương đối tương tự với biểu thức truy vấn được sử dụng trong SQL).
Đây là kết quả mà công thức trên trả về:
Công thức đã trả về cột ‘Name’ của tập dữ liệu vì cột A đề cập đến nó.
Tương tự, nếu bạn chỉ muốn xem cột ‘Address’, công thức QUERY của bạn sẽ là:
=QUERY(A:F,"SELECT D",1)Sử dụng phạm vi đã đặt tên trong chức năng QUERY
Bạn cũng có thể sử dụng các phạm vi đã đặt tên trong truy vấn của bạn. Ví dụ, bạn có thể đặt tên phạm vi dữ liệu A:F là ‘Dataset’ bằng cách chọn cột A đến F và gõ tên ‘Dataset’ vào ô ‘Tên’ (xem bên dưới), sau đó nhấn Enter:
Sau đó, bạn có thể sử dụng tên phạm vi trong chức năng QUERY như sau:
=QUERY(Dataset,"SELECT D",1)Lưu ý: Công thức QUERY của Google Sheets chỉ trả về bảng dữ liệu, nhưng không bao gồm định dạng.
Truy cập bảng mẫu bằng cách nhấp vào liên kết sau:
2. Sử dụng chức năng QUERY Google Sheets để chọn nhiều cột
Bạn cũng có thể sử dụng mệnh đề SELECT để hiển thị nhiều hơn một cột. Ví dụ, nếu bạn muốn hiển thị cả cột ‘Name’ và ‘Address’, công thức QUERY của bạn sẽ như sau:
=QUERY(Dataset,"SELECT A,D",1)Ký hiệu (*) có thể được sử dụng trong mệnh đề SELECT để hiển thị tất cả các cột. Ví dụ, bạn có thể sử dụng mệnh đề SELECT để hiển thị tất cả cột trong tập dữ liệu của mình như sau:
=QUERY(Dataset,"SELECT *",1)3. Lọc và trích xuất dữ liệu bằng cách sử dụng SELECT với mệnh đề WHERE
Nếu bạn muốn chỉ định một số điều kiện mà một hàng phải thỏa mãn để được hiển thị trong một truy vấn, bạn có thể thêm mệnh đề WHERE vào truy vấn của bạn như sau:
=QUERY(Dataset,"SELECT * WHERE B='Manufacturing'",1)Khi được thêm vào mệnh đề SELECT, mệnh đề WHERE cho phép bạn lọc dữ liệu trả về bằng cách sử dụng một hoặc nhiều điều kiện. Ví dụ, nếu bạn chỉ muốn trích xuất dữ liệu của nhân viên từ phòng ‘Manufacturing’, hãy sử dụng chức năng QUERY như sau:
=QUERY(Dataset,"SELECT * WHERE B='Manufacturing'",1)Bạn cũng có thể sử dụng các “toán tử so sánh” trong mệnh đề WHERE. Ví dụ, để trích xuất dữ liệu cho những nhân viên đã làm việc hơn 30 giờ, bạn có thể viết một truy vấn như sau:
=QUERY(Dataset,"SELECT * WHERE E>30",1)Bạn cũng có thể sử dụng các ký tự đại diện Google Sheets nếu bạn muốn điều kiện phải là ký tự chứ không phải từ nguyên.
Sử dụng SELECT WHERE với AND và OR
Thêm nhiều tiêu chí tìm kiếm vào truy vấn của bạn bằng cách sử dụng các toán tử logic như OR và AND. Ví dụ, bạn có thể mở rộng truy vấn của bạn để bao gồm cả nhân viên từ ‘NY’ và ‘NJ’ như sau:
=QUERY(Dataset,"SELECT * WHERE D contains 'NJ' or D contains 'NY'",1)Tương tự, để chèn hai tiêu chí tìm kiếm phải thỏa đáng cả hai để một hàng được xem xét, bạn có thể sử dụng toán tử AND. Nếu bạn muốn hiển thị dữ liệu của nhân viên sống ở ‘NJ’ và thuộc phòng ‘Manufacturing’, bạn có thể sử dụng công thức sau:
=QUERY(Dataset,"SELECT * WHERE D contains 'NJ' and B='Manufacturing'",1)4. Sử dụng mệnh đề ORDER BY trong QUERY Google Sheets
Mệnh đề ORDER BY thường được sử dụng kết hợp với các mệnh đề SELECT và WHERE. Nó cho phép bạn sắp xếp các dữ liệu được chọn theo thứ tự tăng dần hoặc giảm dần. Bạn có thể chỉ định cột bạn muốn sắp xếp dữ liệu của mình và chọn thứ tự tăng dần hoặc giảm dần.
Ví dụ, giả sử bạn muốn hiển thị dữ liệu của tất cả các nhân viên trong phòng ‘Manufacturing’ theo thứ tự abc. Bạn có thể viết công thức QUERY của mình như sau:
=QUERY(Dataset,"SELECT * WHERE B='Manufacturing' ORDER BY A ASC",1)Tương tự, để hiển thị dữ liệu theo thứ tự giảm dần của ‘Giờ làm việc’, công thức QUERY của bạn sẽ như sau:
=QUERY(Dataset,"SELECT * WHERE B='Manufacturing' ORDER BY E DESC",1)Mệnh đề LIMIT
Nếu bạn có một tập dữ liệu với nhiều điểm dữ liệu giống nhau, bạn có thể sử dụng mệnh đề LIMIT để hiển thị một số lượng mục. Ví dụ:
=QUERY(Dataset,"SELECT * WHERE B='Manufacturing' ORDER BY E DESC LIMIT 5",1)5. Sử dụng QUERY để nhóm dữ liệu được chọn bằng mệnh đề GROUP BY
Mệnh đề GROUP BY là một trong những khái niệm truy vấn khó nhất để làm chủ. Để làm chủ mệnh đề này, bạn phải thực hành. Mệnh đề GROUP BY cho phép bạn nhóm dữ liệu của mình thành các nhóm, tương tự như một bảng tổng hợp.
Mệnh đề GROUP BY thường được kết hợp với các hàm tổng hợp như SUM, MAX và MIN, cùng với các mệnh đề SELECT và WHERE.
Ví dụ, một ứng dụng thông thường của chức năng này là đếm số dòng tương ứng với một danh mục cụ thể. Ở đây, các dòng dữ liệu được nhóm theo danh mục, sau đó số dòng cho mỗi nhóm hoặc mỗi danh mục được đếm.
Giả sử bạn muốn nhóm dữ liệu của mình theo ‘Phòng ban’ để xem có bao nhiêu nhân viên trong tập dữ liệu của bạn cho mỗi phòng ban. Trong trường hợp này, truy vấn của bạn sẽ như sau:
=QUERY(A1:F9,"SELECT B, COUNT(B) GROUP BY B",1)Giả sử tôi muốn hiển thị cột B (Phòng ban) và cũng nhóm cột theo Phòng ban. Tôi đã chỉ định rằng cho mỗi nhóm phòng ban, tôi muốn hiển thị số lượng hàng (bằng cách sử dụng hàm tổng hợp COUNT(B)).
Tương tự, nếu tôi muốn hiển thị tổng số giờ làm việc của nhân viên trong mỗi phòng ban, tôi có thể sử dụng truy vấn sau:
=QUERY(A1:F9,"SELECT B,SUM(E) GROUP BY B")Đi xa hơn
Nhờ vào các ví dụ từng bước, bạn nên có cái nhìn sâu hơn về cách sử dụng truy vấn Google Sheets để trích xuất thông tin từ một tập dữ liệu cụ thể. Tôi hy vọng hướng dẫn này đã hữu ích cho bạn.
Nếu bạn có bất kỳ câu hỏi hoặc ý kiến nào về bài viết bạn vừa đọc, hãy để lại bình luận!
