Buzz
Bạn có thao tác với dữ liệu lớn trên Google Sheet, việc tìm kiếm và lọc dữ liệu thủ công mất nhiều thời gian. Hàm QUERY trên Google Sheet cho phép bạn tra cứu và lọc dữ liệu theo định dạng bạn muốn. Cùng tìm hiểu cách dùng hàm QUERY nhé!
Video hướng dẫn cách sử dụng hàm QUERY trong Google Sheet đơn giản:
1. Hàm QUERY và ứng dụng trong Google Sheet
- Hàm QUERY là gì?
Hàm Query trong Google Sheet 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 (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
Tính chất của hàm QUERY:
- Ví dụ về hàm QUERY
=QUERY(A1:B4;'SELECT *')
Ý nghĩa: Trích xuất tất cả dữ liệu từ ô A1 đến ô B4.
- Ứng dụng của hàm QUERY
Hỗ trợ mạnh mẽ trong việc tìm kiếm dữ liệu trong Google Sheet.
2. Cách dùng hàm QUERY trên Google Sheet
- Hàm QUERY kết hợp 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.
Bảng dữ liệu có sẵn
+ Trích xuất tất cả dữ liệu
Công thức: =QUERY(A1:D8;'SELECT *')
Ý nghĩa công thức: Trích xuất tất cả dữ liệu từ ô A1 đến ô D8.
Trích xuất dữ liệu từ các cột như Họ Tên, Giới tính, Điểm trung bình
+ Trích xuất từng cột dữ liệu
Công thức: =QUERY(A1:D8;'SELECT A, B')
Ý nghĩa công thức: Trích xuất dữ liệu từ cột A và cột B trong vùng dữ liệu A1:D8.
Chỉ trích xuất dữ liệu từ cột Họ và Tên
- Hàm QUERY kết hợp điều kiện WHERE
+ Áp dụng 1 điều kiện
Công thức: =QUERY(A1:E8;'SELECT * WHERE D = 'Nữ' ')
Ý nghĩa công thức: Trích xuất dữ liệu từ ô A1 đến ô D8 với điều kiện Giới tính là 'Nữ'.
Trích xuất các bạn có giới tính Nữ trong lớp
+ Kết hợp 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: Trích xuất 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.
Trích xuất các bạn có giới tính Nữ và điểm trung bình >= 8
- Hàm Query dùng để lọc và tra cứu dữ liệu
Cho bảng dưới đây:
Bảng điểm trung bình của học sinh lớp 12/A
Bảng này gồm một trang tính (được gọi là “Lớp A”) chứa danh sách học sinh lớp A. Bảng dữ liệu bao 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.
Từ dữ liệu bảng trên, lọc ra danh sách số học sinh có Điểm trung bình >= 5 trong lớp A.
Câu lệnh Query truy vấn cần thực hiện là:
Trong đó:
Câu lệnh Query truy vấn
Tương tự như 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à áp dụng đ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 để tổng 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, có 2 lớp “Lớp A” tương ứng sheet 1 và “Lớp B” tương ứng sheet 2. Từ bảng dữ liệu trên, lọc ra danh sách tất cả học sinh khối 12 có Điểm trung bình >= 8.
Query tổng hợp dữ liệu từ nhiều sheet thành 1 sheet
Bắt đầu tạo 1 sheet mới để tổng hợp dữ liệu từ 2 lớp. (Đặt tên là sheet Tổng hợp).
Tạo sheet Tổng hợp
Câu lệnh Query truy vấn cần thực hiện lúc này:
Trong đó:
Tổng hợp dữ liệu tại Sheet Tổng hợp
- 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.
QUERY kết hợp với toán tử so sánh bằng
- 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.
QUERY kết hợp với AND
+ 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.
QUERY kết hợp với OR
- 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'.
QUERY kết hợp với hàm IF
Hàm Query kết hợp hàm ArrayFormula()
- 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 SUM
- 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.
Sao chép liên kết của cả trang tính
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:
Ý 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 IMPORTRANGE
- 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.
Hàm QUERY kết hợp với hàm VLOOKUP
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).
QUERY kết hợp Offset
- Contains: Lọc dữ liệu
+ Lấy dữ liệu trùng khớp với dữ liệu trong bảng.
+ Ví dụ: =QUERY(A1:E7;'SELECT * WHERE A CONTAINS 'A_001'')
+ Ý nghĩa: Lấy dữ liệu của học sinh có mã A_001.
QUERY kết hợp Contains
- Like: Lọc dữ liệu
+ Tìm các dòng dữ liệu 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.
QUERY kết hợp Like
- 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.
QUERY kết hợp Order by
- 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.
QUERY kết hợp Group by
- 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.
QUERY kết hợp Limit
- 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.
QUERY kết hợp Label
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:
Sẽ báo lỗi #ERROR, hãy cùng sửa lỗi lại cho đúng nhé
Sửa lỗi ERROR
- 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.
Sửa lỗi VALUE
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.
6. Bài tập sử dụng hàm QUERY
Để hiểu rõ hơn về cách sử dụng hàm QUERY trong Google Sheet ở phía trên, bạn hãy thực hành các câu lệnh trên máy tính của mình nhé! Dưới đây là bảng dữ liệu của mình tự tạo, các bạn có thể lấy dữ liệu để thực hành: Bài tập Điểm Trung Bình Học Sinh
7. Các câu hỏi thường gặp khi sử dụng hàm QUERY
- QUERY hoạt động trên Google Sheet như thế nào?
Trả lời: Hãy tưởng tượng bạn gọi 'Cho mình 1 ly trà đào cam sả?'. Nhân viên lúc này sẽ hiểu yêu cầu của bạn và thực hiện đơn hàng. QUERY trong Google Sheet cũng thực hiện tương tự. Bạn sẽ dùng ngôn ngữ QUERY để gửi yêu cầu bạn muốn, lúc này câu truy vấn sẽ được thực hiện và trả về kết quả như bạn mong muốn.
Cho mình 1 ly trà đào cam xả?
- Lợi ích của việc sử dụng hàm QUERY kết hợp với hàm IMPORTRANGE là gì?
Trả lời: Về chức năng chính của hàm IMPORTRANGE trong Google Sheet là trích xuất tất cả dữ liệu từ một bảng tính của tệp này sang bảng tính của 1 tệp khác. Và khi bạn kết hợp hàm QUERY với IMPORTRANGE sẽ mang lại các lợi ích:
+ Kiểm soát phạm vi vùng dữ liệu (có thể loại bỏ các hàng và cột không mong muốn).
+ Sắp xếp và lọc dữ liệu.
Trên đây là hướng dẫn bạn sử dụng hàm QUERY trong Google Sheet để lọc dữ liệu 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!
