Tổng hợp +7 cách sử dụng hàm QUERY trong Google Sheet

Công thức hàm QUERY và cách kết hợp với các hàm khác trong Google Sheet

Hàm QUERY là hàm cho phép bạn sử dụng các lệnh cơ sở dữ liệu để thao tác với dữ liệu (như 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 hơn.

Công thức hàm QUERY như sau: =QUERY(data; query; headers)

Trong đó:

• data: Là phạm vi vùng dữ liệu cần thao tác.
• QUERY: Là câu truy vấn để xuất dữ liệu theo mong muốn.
• headers: 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ụ: Để lấy tất cả dữ liệu từ ô A1 đến ô B4, bạn chỉ cần nhập công thức: =QUERY(A1:B4;"")

Hàm QUERY là một công cụ mạnh mẽ để lọc thông tin, giúp bạn làm việc hiệu quả nhanh chóng và chính xác hơn bao giờ hết. Tùy theo nhu cầu cụ thể, bạn sẽ cần kết hợp hàm QUERY với các câu lệnh khác nhau để truy vấn dữ liệu hiệu quả.

Việc kết hợp hàm QUERY với câu lệnh sẽ giúp người dùng trích xuất thông tin nhanh chóng trong bảng dữ liệu mà không cần nhập lại. Đây là một trong những cách sử dụng hàm QUERY trong Google Sheet phổ biến nhất.

Ví dụ: Bạn đang có bảng dữ liệu từ ô A1 đến ô D8.

• Để trích xuất tất cả dữ liệu, bạn nhập công thức: =QUERY(A1:D8, '')

Kết quả trích xuất toàn bộ bảng dữ liệu

• Để lấy cột dữ liệu nhất định (cột A và cột B), bạn nhập công thức: =QUERY(A1:D8, 'A, B')

Kết quả trích xuất các cột dữ liệu nhất định

Việc kết hợp hàm QUERY kèm điều kiện WHERE có bản chất là trích xuất dữ liệu theo điều kiện nhất định. Bạn có thể kết hợp 1 điều kiện hoặc nhiều điều kiện như sau:

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

Giả sử, bạn cần 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ữ'. Công thức như sau: =QUERY(A1:E8, ' WHERE D = “Nữ”)

Kết quả trích xuất dữ liệu theo giới tính nữ ở cột D

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

Giả sử bạn cần trích xuất tất cả dữ liệu từ ô A1 đến ô E8 với có giới tính là 'Nữ' và Điểm trung bình lớn hơn hoặc bằng 8, công thức như sau: 

=QUERY(A1:E8, ' WHERE D = “Nữ” AND E = 8')

Kết quả trích xuất dữ liệu nhiều điều kiện bằng hàm QUERY trong Google Sheet

Trong Sheet Class A, bảng dữ liệu thông tin học sinh nằm trong phạm vi A1:E7 như hình dưới đây. 

Bảng dữ liệu ban đầu

Giả sử bạn cần lọc danh sách học sinh có điểm trung bình từ 5 trở lên, hãy nhập hàm như sau: =QUERY(A1:E7;" WHERE E >= 5";1)

Trong đó:

• A1:E7: Là vùng chứa dữ liệu.
• ' WHERE E >= 5': Lấy thông tin của tất cả các cột đủ điều kiện điể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.

Kết quả lọc danh sách học sinh có điểm trung bình từ 5 trở lên

Trong 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). Giả sử bạn cần lọc danh sách học sinh khối 12 có điểm trung bình ít nhất là 8, hãy tạo một sheet mới là 'Tổng hợp' để tổng hợp dữ liệu từ hai lớp lại với nhau. 

Bảng điểm của hai lớp Class A và Class B

Sau đó nhập hàm QUERY như sau:

=QUERY({'Class A'!A2:E;'Class B'!A2:E}, ' 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'.
• ' 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 8 trở lên. (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.

Nhập hàm như hình để trích xuất dữ liệu từ 2 sheet

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

Giả sử bạn cầ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, hãy sử dụng hàm AND để kết hợp hai điều kiện như sau:

=QUERY(A1:E7, ' WHERE D = 'Nam' AND E = 5')

Kết quả trích xuất dữ liệu khi kết hợp hàm QUERY với hàm AND

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

Để trích xuất 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, bạn sử dụng hàm OR để kết hợp hai điều kiện trong Google Sheet như sau:

=QUERY(A1:E7, ' WHERE E = 10 OR E = 3.7')

Kết quả trích xuất dữ liệu khi kết hợp hàm QUERY với hàm OR

Công thức dưới đây sử dụng hàm QUERY kết hợp với 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'. Công thức như sau:

=IF(QUERY(A2:E7, 'E') = 5, 'Đậu', 'Rớt')

Công thức kết hợp hàm QUERY với hàm IF

Để 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, 'E') = 5, 'Đậu', 'Rớt'))

Trong đó:

• QUERY(A2:E7, 'E'): Lấy ra cột E (Điểm trung bình) từ dữ liệu. Ngoài ra, bạn có thể truy xuất dữ liệu từ sheet khác.
• IF(QUERY(A2:E7, '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.

Nhập hàm như hình để tự động áp dụng cho tất cả dòng có dữ liệu

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, 'E', 0)).

Trong đó:

• QUERY(A1:E7, '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, '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.

Tính tổng điểm theo kết quả truy xuất bằng hàm QUERY và hàm SUM

Lưu ý một số lỗi khi sử dụng hàm QUERY 

Trong quá trình sử dụng hàm QUERY trong Google Sheet, người dùng thường hay gặp các lỗi như lỗi #ERROR, lỗi #VALUE hoặc lỗi #N/A. Hãy cùng xem nguyên nhân để khắc phục các lỗi này nhé!

• Lỗi #ERROR: Do không nhập đúng cú pháp của hàm.

Nhập sai công thức hàm là nguyên nhân báo lỗi  #ERROR

• Lỗi #VALUE: Nguyên nhân thường do quá trình nhập công thức hoặc do các ô đang tham chiếu bị lỗi.
• 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 là toàn bộ khái niệm, cách sử dụng hàm QUERY trong Google Sheet. Chúc bạn thực hiện thành công! Theo dõi Sửa chữa Laptop 24h để cập nhật nhiều thủ thuật hữu ích nhé!