Chức năng QUERY cho phép bạn lấy thông tin từ một phạm vi hoặc toàn bộ trang dữ liệu bằng cách sử dụng các lệnh truy vấn linh hoạt. Học cách sử dụng chức năng QUERY của Google Trang tính cho phép bạn truy cập vào một công cụ tra cứu mạnh mẽ.
Nếu bạn đã từng viết các truy vấn SQL để lấy dữ liệu ra khỏi cơ sở dữ liệu, thì bạn sẽ nhận ra hàm QUERY. Nếu bạn không có kinh nghiệm về cơ sở dữ liệu, hàm QUERY vẫn rất dễ học.
Chức năng QUERY là gì?
Hàm có ba tham số chính:
=QUERY (dữ liệu, truy vấn, tiêu đề)
Các thông số này khá đơn giản.
- Dữ liệu: Phạm vi ô chứa dữ liệu nguồn
- Query: Câu lệnh tìm kiếm mô tả cách trích xuất những gì bạn muốn từ dữ liệu nguồn
- Headers: Đối số tùy chọn cho phép bạn kết hợp nhiều tiêu đề trong phạm vi nguồn thành một tiêu đề duy nhất trong trang đích
Tính linh hoạt và sức mạnh của hàm QUERY đến từ đối số Truy vấn, như bạn sẽ thấy bên dưới.
Cách tạo Công thức QUERY Đơn giản
Công thức QUERY đặc biệt hữu ích khi bạn có một tập dữ liệu rất lớn mà từ đó bạn cần trích xuất và lọc dữ liệu.
Các ví dụ sau đây sử dụng số liệu thống kê về kết quả học tập tại trường trung học phổ thông SAT của Hoa Kỳ. Trong ví dụ đầu tiên này, bạn sẽ học cách viết một công thức QUERY đơn giản trả về tất cả các trường trung học và dữ liệu của họ trong đó tên trường là "New York".
-
Tạo một trang tính mới để đặt kết quả truy vấn. Trong ô phía trên bên trái, nhập =Query (. Khi làm điều này, bạn sẽ thấy một cửa sổ bật lên với các đối số yêu cầu, ví dụ và thông tin hữu ích về hàm.
Image -
Tiếp theo, giả sử bạn có dữ liệu nguồn trong Sheet1, hãy điền vào hàm như sau:
=Truy vấn (Sheet1! A1: F460, "CHỌN B, C, D, E, F MÀ B THÍCH '% New York%'")
Công thức này bao gồm các đối số sau:
- Phạm vi ô: Phạm vi dữ liệu từ A1 đến F460 trong Trang tính1
- Câu lệnh SELECT: Câu lệnh SELECT gọi bất kỳ dữ liệu nào trong các cột B, C, D, E và F trong đó cột B chứa văn bản có từ "New York "trong đó.
Image Ký tự "%" là một ký tự đại diện mà bạn có thể sử dụng để tìm kiếm các phần của chuỗi hoặc số trong bất kỳ tập dữ liệu nào. Bỏ "%" ra trước chuỗi sẽ trả về bất kỳ tên trường nào bắt đầu bằng văn bản "New York".
-
Nếu bạn muốn tìm tên của một trường chính xác từ danh sách, bạn có thể nhập truy vấn:
=Truy vấn (Sheet1! A1: F460, "SELECT B, C, D, E, F WHERE B='New York Harbour High School'")
Sử dụng toán tử =sẽ tìm thấy kết quả khớp chính xác và có thể được sử dụng để tìm văn bản hoặc số phù hợp trong bất kỳ cột nào.
Image
Vì chức năng QUERY của Google Trang tính rất dễ hiểu và dễ sử dụng, bạn có thể lấy bất kỳ dữ liệu nào ra khỏi bất kỳ tập dữ liệu lớn nào bằng cách sử dụng các câu lệnh truy vấn đơn giản như các câu lệnh ở trên.
Sử dụng Hàm QUERY Với Toán tử So sánh
Toán tử so sánh cho phép bạn sử dụng hàm QUERY để lọc ra dữ liệu không đáp ứng điều kiện.
Bạn có quyền truy cập vào tất cả các toán tử sau trong một hàm QUERY:
- =: Giá trị khớp với giá trị tìm kiếm
- <: Giá trị nhỏ hơn giá trị tìm kiếm
- >: Giá trị lớn hơn giá trị tìm kiếm
- <=: Giá trị nhỏ hơn hoặc bằng giá trị tìm kiếm
- >=: Giá trị lớn hơn hoặc bằng giá trị tìm kiếm
- và!=: Giá trị tìm kiếm và giá trị nguồn không bằng nhau
Sử dụng cùng một tập dữ liệu mẫu SAT ở trên, hãy cùng xem cách xem những trường nào có điểm toán trung bình trên 500 điểm.
-
Trong ô phía trên bên trái của trang tính trống, điền vào hàm QUERY như sau:
=Truy vấn (Sheet1! A1: F460, "CHỌN B, C, D, E, F WHERE E > 500")
Công thức này yêu cầu bất kỳ dữ liệu nào trong đó cột E chứa giá trị lớn hơn 500.
Image -
Bạn cũng có thể bao gồm các toán tử logic như VÀ và HOẶC để tìm kiếm nhiều điều kiện. Ví dụ: để lấy điểm chỉ cho các trường có hơn 600 thí sinh dự thi và điểm trung bình của bài đọc từ 400 đến 600, bạn sẽ nhập hàm QUERY sau:
=Truy vấn (Sheet1! A1: F460, "CHỌN B, C, D, E, F TRONG ĐÓ C > 600 VÀ D > 400 VÀ D < 600")
Image - Toán tử so sánh và lôgic cung cấp cho bạn nhiều cách khác nhau để lấy dữ liệu từ bảng tính nguồn. Chúng cho phép bạn lọc ra các phần thông tin quan trọng từ các tập dữ liệu thậm chí rất lớn.
Công dụng Nâng cao của Hàm QUERY
Có một số tính năng khác mà bạn có thể thêm vào chức năng QUERY bằng một số lệnh bổ sung. Các lệnh này cho phép bạn tổng hợp các giá trị, đếm giá trị, dữ liệu thứ tự và tìm các giá trị lớn nhất.
-
Sử dụng GROUP trong hàm QUERY cho phép bạn tổng hợp các giá trị trong nhiều hàng. Ví dụ: bạn có thể tính điểm kiểm tra trung bình cho từng học sinh bằng cách sử dụng chức năng GROUP. Để thực hiện việc này, hãy nhập:
=Truy vấn (Sheet1! A1: B24, "CHỌN A, AVG (B) GROUP BY A")
Image -
Sử dụng COUNT trong hàm QUERY, bạn có thể đếm số trường có điểm trung bình viết trên 500 bằng cách sử dụng hàm QUERY sau:
=QUERY (Sheet1! A2: F460, "CHỌN B, ĐẾM (F) NHÓM THEO B")
Image -
Sử dụng ORDER BY trong hàm QUERY, bạn có thể tìm các trường có điểm trung bình môn toán tối đa và sắp xếp danh sách theo điểm đó.
=QUERY (Sheet1! A2: F460, "CHỌN NHÓM B, MAX (E) THEO LỆNH B THEO TỐI ĐA (E)")
Image
