Bạn làm việc với lượng lớn dữ liệu trên Google Sheet, việc lọc và tìm kiếm dữ liệu theo cách thủ công sẽ tốn rất nhiều thời gian. Hàm QUERY trong Google Sheet giúp bạn có thể tra cứu và lọc dữ liệu theo bất kỳ định dạng nào bạn muốn. Hãy cùng thế giới thủ thuật tìm hiểu cách dùng hàm QUERY trong bài viết này nhé!
Bạn đang đọc: Cách sử dụng hàm QUERY trong Google Sheet để lọc dữ liệu
1. Hàm QUERY là gì? Ứng dụng của hàm QUERY trong Google Sheet
– Hàm QUERY là gì?
Hàm Query trong Google Sheet 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 (có thể tra cứu dữ liệu, lọc dữ liệu, kết hợp nhiều dữ liệu từ nhiều sheet thành 1 sheet,…) một cách linh hoạt và dễ dàng.
– Công thức hàm QUERY
=QUERY(data; query; headers)
Trong đó:
+ data: Là phạm vi vùng dữ liệu (ví dụ: ‘Class A’!A2:E7).
+ query: Là câu truy vấn để xuất dữ liệu mong muốn.
+ headers: Bạn có thể điền giá trị hoặc để trống
- Thường có 2 giá trị (0 hoặc 1); 0 nghĩa là không lấy tên cột tiêu đề dữ liệu; 1 nghĩa là lấy luôn tên cột tiêu đề).
- Nếu bạn bỏ trống thì mặc định headers sẽ là 1.
– Ví dụ về hàm QUERY
=QUERY(A1:B4;”SELECT *”)
Ý nghĩa: Lấy tất cả dữ liệu từ ô A1 đến ô B4.
– Ứng dụng hàm QUERY
Ứng dụng mạnh mẽ trong việc tìm kiếm cơ sở dữ liệu cho Google Sheet.
2. Cách sử dụng hàm QUERY trong Google Sheet
– Hàm QUERY với câu lệnh SELECT cơ bản
Ví dụ:Bạn có vùng dữ liệu từ ô A1 đến ô D8, có thể viết là A1:D8.
+ Lấy tất cả dữ liệu
Công thức: =QUERY(A1:D8;”SELECT *”)
Ý nghĩa công thức: Lấy tất cả dữ liệu từ ô A1 đến ô D8.
+ Lấy cột dữ liệu nhất định
Công thức: =QUERY(A1:D8;”SELECT A, B”)
Ý nghĩa công thức: Lấy dữ liệu của cột A và cột B trong vùng dữ liệu A1:D8.
– Hàm QUERY với điều kiện WHERE
+ Sử dụng 1 điều kiện
Công thức: =QUERY(A1:E8;”SELECT * WHERE D = ‘Nữ’ “)
Ý nghĩa công thức: Lấy tất cả dữ liệu từ ô A1 đến ô D8 với điều kiện Giới tính là “Nữ”.
+ Sử dụng nhiều điều kiện
Công thức: =QUERY(A1:E8;”SELECT * WHERE D = ‘Nữ’ AND E >= 8″)
Ý nghĩa công thức: Lấy tất cả dữ liệu từ ô A1 đến ô D8 với điều kiện Giới tính là “Nữ” và Điểm trung bình >= 8.
– Hàm Query để lọc, tra cứu dữ liệu
Cho bảng sau:
Bảng gồm một trang tính (được gọi là “Class A”) bao gồm danh sách học sinh lớp A. Bảng dữ liệu gồm các trường: Mã học sinh, Họ, Tên, Giới tính và Điểm trung bình của mỗi học sinh.
Dựa vào dữ liệu bảng trên, hãy lọc ra danh sách số học sinh có Điểm trung bình >= 5 trong lớp A.
Để thực hiện, câu lệnh Query truy vấn lúc này sẽ là:
=QUERY(A1:E7;”SELECT * WHERE E >= 5″;1)
Trong đó:
- A1:E7: Vùng chứa dữ liệu của Class A.
- “SELECT * WHERE E >= 5”: Lấy ra dữ liệu của tất cả các cột với điều kiện Điểm trung bình >= 5.
- 1: Lấy luôn tên cột tiêu đề dữ liệu đầu trong bảng.
Như câu truy vấn SQL thông thường, hàm Query chọn các cột để hiển thị (SELECT * để lấy ra tất cả các cột) và xác định các điều kiện để tìm kiếm (WHERE E >= 5 tương đương với điều kiện Điểm trung bình >= 5).
– Hàm Query để kết hợp dữ liệu từ nhiều sheet thành 1 sheet
Trên trang tính Điểm Trung Bình Học Sinh Khối 12, gồm 2 lớp “Class A” tương ứng sheet 1 và “Class B” tương ứng sheet 2. Dựa vào bảng dữ liệu trên, hãy lọc ra danh sách tất cả học sinh khối 12 có Điểm trung bình >= 8.
Đầu tiên tạo 1 sheet mới để tổng hợp dữ liệu 2 lớp lại với nhau. (Đặt tên là sheet Tổng hợp).
Câu lệnh Query truy vấn lúc này:
=QUERY({‘Class A’!A2:E;’Class B’!A2:E};”SELECT * WHERE Col5 >= 8″;0)
Trong đó:
- {‘Class A’!A2:E;’Class B’!A2:E}: Vùng chứa dữ liệu của 2 sheet.
- “SELECT * WHERE Col5 >= 8”: Lấy ra dữ liệu của tất cả các cột với điều kiện Điểm trung bình >= 8. (Col5 tương đương cột E của Class A và Class B)
- 0: không lấy tên cột tiêu đề dữ liệu trong bảng.
– Hàm QUERY kết hợp với toán tử so sánh
Công thức: =QUERY(A1:E7;”SELECT * WHERE E = 8.2″;1)
Ý nghĩa công thức: Sử dụng toán tử bằng để lấy ra danh sách những bạn có Điểm trung bình = 8.2.
Chú ý: Các toán tử so sánh thường được sử dụng: bằng, lớn hơn, bé hơn, lớn hơn hoặc bằng, bé hơn hoặc bằng, khác,…
– Hàm QUERY kết hợp với AND, OR
+ Kết hợp với toán tử AND
Công thức: =QUERY(A1:E7;”SELECT * WHERE D = ‘Nam’ AND E>=5″)
Ý nghĩa công thức: Sử dụng toán tử AND để kết hợp 2 điều kiện để lọc ra học sinh có giới tính Nam và điểm trung bình >= 5.
+ Kết hợp với toán tử OR
Công thức: =QUERY(A1:E7;”SELECT * WHERE E = 10 OR E = 3.7″)
Ý nghĩa công thức: Lấy ra những học sinh có điểm trung bình = 10 hoặc điểm trung bình = 3.7.
– Hàm QUERY kết hợp với hàm IF
Công thức: =IF(QUERY(A2:E7;”SELECT E”) >= 5;”Đậu”;”Rớt”)
Ý nghĩa công thức: Nếu điểm trung bình >= 5 thì cho kết quả “Đậu”, ngược lại cho kết quả “Rớt”.
Để câu lệnh tự động cập nhật cho các dòng dữ liệu bên dưới bạn có thể dùng hàm ArrayFormula() như sau:
Tìm hiểu thêm: Hướng dẫn xoá thư, email trong Gmail một cách nhanh chóng hiệu quả
– Hàm QUERY kết hợp với hàm SUM
Công thức: =SUM(QUERY(A1:E7;”SELECT E “;0))
Ý nghĩa công thức: Tính tổng điểm trung bình của cả lớp.
– Hàm QUERY kết hợp với hàm IMPORTRANGE
Bước 1: Copy đường link của file Điểm Trung Bình Học Sinh.
Bước 2: Tạo 1 trang tính mới bằng và nhập vào công thức dưới đây
Công thức:
=QUERY(IMPORTRANGE(“https://docs.google.com/spreadsheets/d/1a6n7LRioci5sm1MNQ1pXM9hzWCRnCZfC50iZySTeZkc/edit#gid=4491137xx”;”Class A!A:E”);”SELECT * WHERE Col5 >= 5″)
Ý nghĩa công thức: Kéo dữ liệu từ file Điểm Trung Bình sang file mới kèm điều kiện học sinh có Điểm trung bình >= 5.
– Hàm QUERY kết hợp với hàm VLOOKUP
Công thức: =VLOOKUP(H5;QUERY(A4:F11;”SELECT *”);5;FALSE)
Ý nghĩa công thức: Lấy ra ngày sinh của những học sinh có mã số cho sẵn.
3. Một số câu lệnh (hàm) được QUERY hỗ trợ
– Offset: Bỏ qua 1 số dòng đầu tiên
+ Bỏ qua 1 số dòng đầu tiên của kết quả.
+ Ví dụ: =QUERY(A1:E7;”SELECT * OFFSET 5″)
+ Ý nghĩa: Bỏ qua 5 dòng dữ liệu đầu tiên (không tính cột tiêu đề) và bắt đầu lấy dữ liệu từ dòng thứ 6 (tương đương với hàng thứ 7 của sheet).
– Contains: Lọc dữ liệu
+ Lấy ra dữ liệu có từ khóa trùng với dữ liệu trong bảng.
+ Ví dụ: =QUERY(A1:E7;”SELECT * WHERE A CONTAINS ‘A_001′”)
+ Ý nghĩa: Lấy ra đúng dữ liệu của học sinh có mã là A_001.
– Like: Lọc dữ liệu
+ Tìm ra các dòng dữ liệu có chứa từ khóa gần giống với dữ liệu trong bảng.
+ Có 2 ký tự thường được sử dụng với toán tử like:
- Dấu (%): Đại diện cho 0,1 hoặc nhiều ký tự.
- Dấu (-): Đại diện cho 1 ký tự.
+ Ví dụ: =QUERY(A1:E8;”SELECT * WHERE B LIKE ‘Nguyễn %’”)
+ Ý nghĩa: Lấy ra các bạn có họ là Nguyễn.
– Order by: Sắp xếp giá trị
+ Dùng để sắp xếp giá trị trong 1 cột dữ liệu.
+ Có 2 kiểu sắp xếp:
- Tăng dần (ASC).
- Giảm dần (DESC).
+ Ví dụ: =QUERY(A1:E7;”SELECT * Order by C DESC”)
+ Ý nghĩa: Sắp xếp tên học sinh theo tứ tự nghịch từ Z -> A.
– Group by: Nhóm các hàng có cùng giá trị
+ Dùng để nhóm các hàng có cùng giá trị.
+ Ví dụ: =QUERY(A1:E7;”SELECT COUNT(A),D Group by D”)
+ Ý nghĩa: Đếm xem có bao nhiêu nam và bao nhiêu nữ trong lớp.
– Limit: Giới hạn kết quả trả về
+ Hạn chế số lượng kết quả trả về.
+ Ví dụ: =QUERY(A1:E7;”SELECT * Limit 3″)
+ Ý nghĩa: Giới hạn chỉ lấy 3 kết quả đầu tiên.
– Label: Đặt lại nhãn cho tiêu đề cột
+ Đặt lại tên tiêu đề cột.
+ Ví dụ: =QUERY(A1:E7; “SELECT (C), (E) LABEL C ‘Tên học sinh’ , E ‘Điểm trung bình’ “)
+ Ý nghĩa: Đặt lại tên cột TEN thành Tên học sinh, cột DIEM_TB thành Điểm trung bình.
4. Các lưu ý khi sử dụng hàm QUERY trong Google Sheet
– Sử dụng chữ viết hoa hay chữ thường cho hàm QUERY đều được.
– Phân biệt cách dùng Col (+ số thứ tự cột) với cột (A,B,C,D,…):
+ Dùng Col khi kết hợp dữ liệu từ nhiều sheet lại thành 1 sheet.
+ Dùng cột khi truy xuất dữ liệu trong cùng 1 sheet.
5. Một số lỗi thường gặp khi dùng hàm QUERY
Với các bạn mới sử dụng hàm QUERY chưa quen thường hay gặp lỗi #ERROR, lỗi #VALUE hoặc lỗi #N/A, cùng xem nguyên nhân gây ra các lỗi này nhé!
– Lỗi #ERROR
Lỗi do không nhập đúng cú pháp của hàm.
Ví dụ cú pháp bạn nhập vào:
=query({‘Class A’!A2:E7;’Class B’!A2:E8};”select * where Col1 is not null”;0)”)”
Sẽ báo lỗi #ERROR, hãy cùng sửa lỗi lại cho đúng nhé
– Lỗi #VALUE
Lỗi #VALUE! trong Google Sheet thường có nhiều nguyên nhân. Phần lớn thường gặp là do quá trình nhập công thức hoặc do các ô đang tham chiếu bị lỗi.
>>>>>Xem thêm: Xác minh 2 bước cho tài khoản Gmail, Google
Cách sửa lỗi: Hãy kiểm tra kỹ lại công thức xem đã đúng chưa nhé!
– Lỗi #N/A
N/A được hiểu là No Available, tức là không tìm thấy giá trị phù hợp để hàm hoạt động.
Trên đây thế giới thủ thuật đã hướng dẫn bạn sử dụng hàm QUERY trong Google Sheet để lọc dữ liệu một cách hiệu quả cũng như kết hợp dữ liệu nhiều sheet lại với nhau! Chúc các bạn thực hiện thành công!
- Cách copy công thức nhanh trong Google Sheet đặt cho cả cột
- Hướng dẫn sử dụng bộ lọc trên Google Sheets để lọc dữ liệu đơn giản
