Cách sử dụng hàm QUERY trong Google Sheet nhanh chóng

Cách sử dụng hàm QUERY trong Google Sheet, Excel là một kỹ năng quan trọng trong xử lý dữ liệu. Điều này cho phép người dùng truy vấn và lọc dữ liệu trong Excel hay Google Sheet cụ thể, giúp tối ưu hóa quá trình làm việc với dữ liệu. Dưới đây là một số cách dùng hàm QUERY trong Google Sheet, Excel cơ bản mà Sforum đã tổng hợp được gửi đến bạn đọc.

Hàm QUERY là gì?

Hàm QUERY trong Excel, Google Sheet giúp bạn linh hoạt và thuận tiện khi sử dụng các lệnh cơ sở dữ liệu như SQL (ngôn ngữ truy vấn có cấu trúc) để tương tác với dữ liệu. Điều này cho phép bạn thực hiện các thao tác như tra cứu, lọc, và hợp nhất dữ liệu từ nhiều bảng (Sheet) khác nhau thành một bảng duy nhất một cách dễ dàng.

Cách sử dụng hàm QUERY trong Google Sheet đơn giản

Khám phá cách sử dụng hàm QUERY trong Google Sheet, Excel có thể làm thay đổi hoàn toàn cách bạn tương tác với dữ liệu. Đây không chỉ là một công cụ mạnh mẽ để lọc thông tin, mà còn là chìa khóa mở ra thế giới của những truy vấn linh hoạt và hiệu quả.

Cách kết hợp hàm QUERY với câu lệnh Select

Ví dụ: Nếu bạn có một phạm vi dữ liệu từ ô A1 đến ô D8, bạn có thể sử dụng công thức sau để trích xuất thông tin. Đây là một trong những cách dùng hàm QUERY phổ biến nhất hiện nay.

• Lấy tất cả dữ liệu:

Công thức: =QUERY(A1:D8, 'SELECT *')

Giải thích: Trích xuất 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')

Giải thích: Trích xuất dữ liệu từ cột A và cột B trong phạm vi từ A1 đến D8.

Cách sử dụng hàm QUERY có điều kiện Where

Cách dùng hàm QUERY trong Google Sheet, Excel để thực hiện lệnh có điều kiện Where chi tiết sau đây.

• Sử dụng 1 điều kiện:

Công thức: =QUERY(A1:E8, 'SELECT * WHERE D = “Nữ”)

Ý nghĩa: Trích xuất tất cả dữ liệu từ ô A1 đến ô E8 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: Trích xuất tất cả dữ liệu từ ô A1 đến ô E8 với điều kiện giới tính là 'Nữ' và Điểm trung bình lớn hơn hoặc bằng 8.

Cách dùng hàm QUERY lọc và tra cứu dữ liệu

Trong 'Class A', một bảng trên trang tính chứa thông tin về học sinh, bao gồm “Mã học sinh, Họ, Tên, Giới tính và Điểm trung bình” của từng học sinh. Để lọc danh sách số học sinh có điểm trung bình ít nhất là 5, sử dụng hàm QUERY với câu lệnh như sau: =QUERY(A1:E7, 'SELECT * WHERE E = 5', 1)

Trong công thức trên:

• A1:E7: Là vùng chứa dữ liệu của Class A.
• 'SELECT * WHERE E = 5': Lấy ra thông tin của tất cả các cột với điều kiện diểm trung bình ít nhất là 5.
• 1: Kèm theo tên cột tiêu đề của dữ liệu đầu tiên trong bảng.

Cách dùng hàm QUERY với câu lệnh này sẽ thực hiện chọn lọc dữ liệu dựa trên điều kiện và định dạng kết quả theo cấu trúc truy vấn SQL, trong đó SELECT * chọn tất cả các cột và WHERE E = 5 áp dụng điều kiện Điểm trung bình ít nhất là 5.

Cách dùng hàm QUERY kết hợp nhiều Sheet thành 1

Trên trang tính 'Điểm Trung Bình Học Sinh Khối 12', có hai lớp là 'Class A' (sheet 1) và 'Class B' (sheet 2). Để lọc danh sách học sinh khối 12 có điểm trung bình ít nhất là 8, tạo một sheet mới được đặt tên là 'Tổng hợp' để tổng hợp dữ liệu từ hai lớp lại với nhau. Câu lệnh cách sử dụng hàm QUERY trong Excel, Google Sheet truy vấn để thực hiện điều này là:

=QUERY({'Class A'!A2:E;'Class B'!A2:E}, 'SELECT * WHERE Col5 = 8', 0)

Trong công thức trên:

• {'Class A'!A2:E;'Class B'!A2:E}: Là vùng chứa dữ liệu của cả hai sheet 'Class A' và 'Class B'.
• 'SELECT * WHERE Col5 = 8': Trích xuất thông tin từ tất cả các cột với điều kiện điểm trung bình ít nhất là 8. (Col5 tương đương với cột E của 'Class A' và 'Class B').
• 0: Loại bỏ các cột tiêu đề có trong bảng tính.

Câu lệnh QUERY này tổng hợp dữ liệu từ cả hai sheet vào sheet 'Tổng hợp' và chỉ trả về các học sinh có điểm trung bình ít nhất là 8.

Cách sử dụng hàm QUERY cùng toán tử so sánh

Công thức dưới đây là cách dùng hàm QUERY trong Google Sheet sử dụng toán tử để trích xuất danh sách những học sinh có điểm trung bình bằng 8.2:

=QUERY(A1:E7, 'SELECT * WHERE E = 8.2', 1)

Giải thích ý nghĩa của công thức:

• A1:E7: Là vùng chứa dữ liệu cần truy vấn.
• 'SELECT * WHERE E = 8.2': Trích xuất các thông tin của tất cả các cột với điều kiện điểm trung bình bằng 8.2.
• 1: Bao gồm tên cột tiêu đề dữ liệu đầu tiên trong kết quả.

Chú ý rằng các toán tử so sánh thường được sử dụng bao gồm 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. Trong trường hợp này, công thức chọn ra những học sinh có điểm trung bình chính xác bằng 8.2.

Cách dùng hàm QUERY với hàm AND, OR

• Kết hợp hàm QUERY với hàm AND trong Excel, Google Sheet

Công thức: =QUERY(A1:E7, 'SELECT * WHERE D = 'Nam' AND E = 5')

Ý nghĩa: Sử dụng hàm AND để kết hợp hai điều kiện, lọc ra danh sách học sinh có giới tính là nam và điểm trung bình ít nhất là 5.

• Kết hợp với hàm OR trong Excel

Công thức: =QUERY(A1:E7, 'SELECT * WHERE E = 10 OR E = 3.7')

Ý nghĩa: Lấy ra thông tin của những học sinh có điểm trung bình bằng 10 hoặc bằng 3.7, sử dụng hàm OR để kết hợp hai điều kiện.

Cách sử dụng hàm QUERY với hàm If

Công thức dưới đây sử dụng hàm IF để đánh giá nếu điểm trung bình = 5 thì trả về 'Đậu', ngược lại trả về 'Rớt'. Để tự động cập nhật cho các dòng dữ liệu bên dưới, bạn có thể sử dụng hàm ArrayFormula():=ArrayFormula(IF(QUERY(A2:E7, 'SELECT E') = 5, 'Đậu', 'Rớt'))

Ý nghĩa công thức:

• QUERY(A2:E7, 'SELECT E'): Lấy ra cột E (Điểm trung bình) từ dữ liệu.
• IF(QUERY(A2:E7, 'SELECT E') = 5, 'Đậu', 'Rớt'): Sử dụng hàm IF để kiểm tra điều kiện, nếu Điểm trung bình ít nhất là 5, thì trả về 'Đậu', ngược lại trả về 'Rớt'.
• ArrayFormula(): Hàm này giúp công thức tự động mở rộng để áp dụng cho nhiều dòng dữ liệu bên dưới trong bảng tính.

Cách dùng hàm QUERY với hàm Sum

Công thức cách dùng hàm QUERY trong Google Sheet dưới đây giúp tính tổng điểm trung bình của cả lớp thông qua việc sử dụng hàm SUM và truy vấn dữ liệu từ cột E: =SUM(QUERY(A1:E7, 'SELECT E', 0))

 

Ý nghĩa công thức:

• QUERY(A1:E7, 'SELECT E', 0): Truy vấn dữ liệu từ cột E (Điểm trung bình) của phạm vi dữ liệu A1:E7 và không hiển thị tên cột tiêu đề (0).
• SUM(QUERY(A1:E7, 'SELECT E', 0)): Tính tổng của các giá trị thu được từ truy vấn, tức là tổng điểm trung bình của cả lớp.

Cách dùng hàm QUERY với hàm Importrange

Dưới đây là từng bước cách kết hợp hàm QUERY với hàm IMPORTRANGE trong Excel:

• Bước 1: Sao chép đường link của tệp “Điểm Trung Bình Học Sinh”.

• Bước 2: Tạo một trang tính mới và nhập công thức sau đây:

=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ừ tệp điểm trung bình sang tệp mới, lọc ra thông tin với điều kiện điểm trung bình ít nhất là 5.

Cách sử dụng hàm QUERY với hàm Vlookup

Công thức cách dùng hàm QUERY trong Excel dưới đây sử dụng hàm VLOOKUP để trích xuất ngày sinh của học sinh dựa trên mã số đã được chỉ định:

=VLOOKUP(H5, QUERY(A4:F11, 'SELECT *'), 5, FALSE)

Ý nghĩa công thức:

• H5: Là mã số học sinh được cung cấp.
• QUERY(A4:F11, 'SELECT *'): Truy vấn tất cả dữ liệu từ A4 đến F11.
• VLOOKUP(H5, QUERY(A4:F11, 'SELECT *'), 5, FALSE): Tìm kiếm mã số trong dữ liệu và trả về ngày sinh của học sinh tương ứng (cột 5), với chế độ tìm kiếm chính xác (FALSE).

Các câu lệnh, hàm được QUERY hỗ trợ thực hiện câu lệnh

Từ việc truy vấn đơn giản đến xử lý phức tạp, hàm QUERY không chỉ là một công cụ lọc dữ liệu mà còn là trình thông dịch với những câu lệnh. Hãy khám phá những câu lệnh và hàm được hỗ trợ bởi QUERY, giúp bạn dễ dàng hơn trong thế giới dữ liệu của mình.

Câu lệnh Offset trong QUERY

Cách sử dụng hàm QUERY với câu lệnh Offset dùng để bỏ qua một số dòng đầu tiên của kết quả. Ví dụ: =QUERY(A1:E7, 'SELECT * OFFSET 5'). Có ý nghĩa cụ thể là 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 trang tính).

Câu lệnh Contains trong QUERY

Trích xuất 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''). Có ý nghĩa là trích xuất chính xác dữ liệu của học sinh có mã là A_001 từ bảng, sử dụng điều kiện 'WHERE A CONTAINS 'A_001''.

 

 

Câu lệnh Like trong QUERY

Cách dùng hàm QUERY với câu lệnh Like để tìm ra 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 lệnh Like mà bạn cần phải biết:

• Dấu (%): Đại diện cho 0, 1 hoặc nhiều ký tự.
• Dấu (_): Là đại diện cho một ký tự duy nhất.

Ví dụ: =QUERY(A1:E8, 'SELECT * WHERE B LIKE 'Nguyễn %'')

Ý nghĩa: Trích xuất các dòng dữ liệu trong đó Họ bắt đầu bằng 'Nguyễn'. Sử dụng toán tử LIKE với dấu (%) để đại diện cho bất kỳ chuỗi ký tự nào sau 'Nguyễn'.

Câu lệnh Order by trong QUERY

Cách dùng hàm QUERY để sắp xếp giá trị trong một cột dữ liệu với 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 thứ tự giảm dần từ Z đến A, sử dụng toán tử ORDER BY và chỉ định cột C.

 

 

Câu lệnh Group by trong QUERY

Cách dùng hàm QUERY trong Google Sheet với câu lệnh Group by để 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 cụ thể là sử dụng để đếm số lượng nam và nữ trong lớp bằng cách nhóm các hàng dựa trên giá trị trong cột D (giới tính).

Câu lệnh Limit trong QUERY

Hạn chế số lượng kết quả trả về. Ví dụ: =QUERY(A1:E7, 'SELECT * LIMIT 3'). Ý nghĩa cụ thể là chỉ lấy 3 kết quả đầu tiên từ tập dữ liệu, sử dụng toán tử LIMIT.

 

 

Câu lệnh Label trong QUERY

Thay đổ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''). Câu lệnh này có ý nghĩa là hay đổi tên của cột C thành “Tên học sinh” và tên của cột E thành “Điểm trung bình”.

Một số lưu ý khi sử dụng hàm QUERY trong Google Sheet

Bên cạnh việc tìm hiểu cách sử dụng hàm QUERY và tìm hiểu về các câu lệnh. Thì chắc chắn chúng ta cũng không nên bỏ qua một số lưu ý khi sử dụng QUERY.

• Không ràng buộc việc sử dụng chữ in hoa hay chữ thường trong hàm QUERY.
• Phân biệt cách sử dụng Col (+ số thứ tự cột) và cột (A, B, C, D,...)
• Sử dụng Col khi kết hợp dữ liệu từ nhiều sheet thành một sheet.
• Sử dụng cột khi truy xuất dữ liệu trong cùng một sheet.

 

 

Những lỗi thường gặp khi dùng hàm QUERY

Bên cạnh đó, bạn cũng có thể gặp một số lỗi phổ biến dưới đây như:

• Lỗi #ERROR: Lỗi này xuất hiện khi người dùng không nhập đúng cú pháp của hàm.
• Lỗi #VALUE: Trên Google Sheet, lỗi #VALUE! thường xuất hiện với nhiều nguyên nhân khác nhau. Thông thường, lỗi này phát sinh do quá trình nhập công thức không chính xác hoặc do các ô đang tham chiếu chứa lỗi.
• Lỗi #N/A: Ký hiệu 'N/A' có thể hiểu là 'No Available', tức là không tìm thấy giá trị phù hợp để hàm hoạt động.

Hiện nay, trên thị trường không chỉ có một đơn vị cung cấp những gói dịch vụ về Microsoft Office. Nhưng nếu bạn đang băn khoăn không biết địa chỉ nào là uy tín, chất lượng, thì chắc chắn không nên bỏ qua Sforum.

[Product_Listing categoryid='1149' propertyid=' customlink='https://cellphones.com.vn/phu-kien/may-tinh-laptop/phan-mem/microsoft.html' title='Danh sách phần mềm Microsoft giá tốt tại CellphoneS']

Trên đây là những chia sẻ xoay quanh cách sử dụng hàm QUERY trong Google Sheet, Excel để thao tác các loại dữ liệu mà chắc chắn rất cần thiết trong cuộc sống. Hy vọng bạn sẽ thực hiện thành công với các cách dùng hàm QUERY này. Hãy thường xuyên theo dõi Sforum để không bỏ lỡ những bài viết hay về Microsoft Office nhé!

• Xem thêm bài viết chuyên mục: Thủ thuật Excel