Cách sử dụng hàm QUERY trong Google Sheet để lọc dữ liệu – https://vh2.com.vn

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. Cùng tìm hiểu cách dùng hàm QUERY nhé!

Dưới đây là video hướng dẫn cách sử dụng hàm QUERY trong Google Sheet cực đơn giản:

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 ước .+ headers : Bạn hoàn toà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ổng thể dữ liệu từ ô A1 đến ô B4 .

– Ứng dụng hàm QUERY

Ứng dụng can đảm và 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.

Bảng dữ liệu có sẵn

+ 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ổng thể dữ liệu từ ô A1 đến ô D8 .

Lấy dữ liệu từ những cột gồm Họ Tên Gới tính Điểm trung bình

+ 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 .

Chỉ lấy dữ liệu cột Họ và Tên

– 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 toàn bộ dữ liệu từ ô A1 đến ô D8 với điều kiện kèm theo Giới tính là ” Nữ ” .

Lấy ra những bạn có giới tính Nữ của lớp

+ 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 toàn bộ dữ liệu từ ô A1 đến ô D8 với điều kiện kèm theo Giới tính là ” Nữ ” và Điểm trung bình > = 8 .

Bảng điểm trung bình của học viên lớp 12 / A

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.

Để triển khai, 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.

Câu lệnh Query truy vấnNhư câu truy vấn SQL thường thì, hàm Query chọn những cột để hiển thị ( SELECT * để lấy ra tổng thể những cột ) và xác lập những điều kiện kèm theo để tìm kiếm ( WHERE E > = 5 tương tự với điều kiện kèm theo Đ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.

Query tích hợp dữ liệu từ nhiều sheet thành 1 sheetĐầ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 ) .

Tạo sheet Tổng hợpCâ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.

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 list những bạn có Điểm trung bình = 8.2 .

QUERY tích hợp với toán tử so sánh bằng

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 để phối hợp 2 điều kiện kèm theo để lọc ra học viên có giới tính Nam và điểm trung bình > = 5 .

QUERY phối 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 viên có điểm trung bình = 10 hoặc điểm trung bình = 3.7 .

QUERY tích 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 tác dụng ” Đậu “, ngược lại cho hiệu quả ” Rớt ” .

QUERY tích hợp với hàm IFĐể câu lệnh tự động hóa update cho những dòng dữ liệu bên dưới bạn hoàn toàn có thể dùng hàm ArrayFormula ( ) như sau :

Hàm Query phối 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 phối 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 link 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 := 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 kèm theo học viên có Điểm trung bình > = 5 .

Hàm QUERY phối 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 viên có mã số cho sẵn .

Hàm QUERY phối hợp với hàm VLOOKUP

XEM NGAY những bộ phần mềm chính hãng XỊN SÒ tại Thế Giới Di Động:

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 tiên phong của tác dụng .+ Ví dụ : = QUERY ( A1 : E7 ; ” SELECT * OFFSET 5 ” )+ Ý nghĩa : Bỏ qua 5 dòng dữ liệu tiên phong ( không tính cột tiêu đề ) và khởi đầu lấy dữ liệu từ dòng thứ 6 ( tương tự với hàng thứ 7 của sheet ) .

QUERY tích hợp Offset

– 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 viên có mã là A_001 .

QUERY phối hợp Contains

– Like: Lọc dữ liệu

+ Tìm ra những 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 những bạn có họ là Nguyễn .

QUERY tích 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 viên theo tứ tự nghịch từ Z -> A .

QUERY tích 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 phối hợp Group by

– Limit: Giới hạn kết quả trả về

+ Hạn chế số lượng tác dụng trả về .+ Ví dụ : = QUERY ( A1 : E7 ; ” SELECT * Limit 3 ” )+ Ý nghĩa : Giới hạn chỉ lấy 3 tác dụng tiên phong .

QUERY phối 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 viên ‘, E ‘ Điểm trung bình ‘ ” )+ Ý nghĩa : Đặt lại tên cột TEN thành Tên học viên, cột DIEM_TB thành Điểm trung bình .

QUERY phối 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 tích 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 những 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 do gây ra những 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é

Sửa lỗi ERROR

– Lỗi #VALUE

Lỗi # VALUE ! trong Google Sheet thường có nhiều nguyên do. Phần lớn thường gặp là do quy trình nhập công thức hoặc do những ô đang tham chiếu bị lỗi .

Sửa lỗi VALUECá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ị tương thích để hàm hoạt động giải trí .Để hiểu hơn về nguyên do và cách khắc phục của # N / A bạn hoàn toàn có thể xem chi tiết cụ thể N / A là gì ? Từ viết tắt và những ý nghĩa của ký hiệu N / A

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 thực tế những 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, những bạn hoàn toàn có thể lấy dữ liệu để thực hành thực tế : 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 khoanh vùng phạm vi vùng dữ liệu ( hoàn toàn có thể vô hiệu những hàng và cột không mong ước ) .+ 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ư tích hợp dữ liệu nhiều sheet lại với nhau ! Chúc những bạn triển khai thành công xuất sắc !

Source: https://vh2.com.vn
Category : Tin Học