Cách sử dùng hàm QUERY trong Google Sheets chi tiết

Hàm QUERY trong Google Sheets giúp bạn truy vấn, lọc và sắp xếp dữ liệu một cách linh hoạt, tương tự như SQL. Với QUERY, bạn có thể thực hiện các phép toán và xử lý dữ liệu trực tiếp trong bảng tính mà không cần phần mềm phức tạp. Bài viết này sẽ hướng dẫn cách sử dụng các câu lệnh cơ bản như SELECT, WHERE, ORDER BY, giúp tối ưu hóa công việc với dữ liệu.

Tìm hiểu chi tiết cách sử dụng hàm QUERY trong Google Sheets

Trong Google Sheets, hàm QUERY được sử dụng để truy vấn và xử lý dữ liệu một cách linh hoạt và hiệu quả. Thay vì phải kết hợp nhiều hàm riêng lẻ, QUERY cho phép người dùng thao tác với dữ liệu chỉ bằng một công thức duy nhất, từ việc lọc, sắp xếp, tìm kiếm cho đến phân tích và tổng hợp thông tin.

Nhờ khả năng thiết lập điều kiện đa dạng, hàm QUERY có thể đảm nhiệm vai trò của nhiều hàm quen thuộc như FILTER, SORT hay VLOOKUP. Không chỉ vậy, QUERY còn hỗ trợ các thao tác nâng cao như chọn cột cần hiển thị, nhóm dữ liệu theo tiêu chí, thực hiện các phép tính như tổng, trung bình, đếm, và sắp xếp kết quả theo thứ tự mong muốn.

Trong hàm QUERY, SELECT là câu lệnh dùng để chọn các cột dữ liệu bạn muốn hiển thị trong kết quả trả về. Thay vì lấy toàn bộ bảng, bạn có thể chỉ định chính xác những cột cần xem, giúp bảng kết quả gọn gàng và dễ hiểu hơn.

Ví dụ 1: Giả sử Sheet 2 chứa bảng dữ liệu với các cột: Ngày, Sản phẩm, Doanh thu, Khu vực. Bạn muốn chỉ hiển thị các cột Sản phẩm và Doanh thu trong Sheet 3, bạn có thể sử dụng công thức QUERY như sau:

=QUERY(Sheet2!A1:D7; "SELECT B, C"; 1)

Giải thích:

• Sheet2!A1:D7: Phạm vi dữ liệu từ ô A1 đến D7 trên Sheet 2.
• "SELECT B, C": Chọn chỉ cột B (Sản phẩm) và cột C (Doanh thu).
• 1: Dữ liệu có tiêu đề ở dòng 1.

Kết hợp hàm hàm QUERY với câu lệnh SELECT để trích dữ liệu từ một trang tính khác

Câu lệnh WHERE trong hàm QUERY của Google Sheets rất quan trọng vì nó cho phép người dùng lọc dữ liệu theo các điều kiện cụ thể. Khi cần trích xuất dữ liệu từ một tập hợp nhất định, đáp ứng các tiêu chí yêu cầu, câu lệnh WHERE là không thể thiếu.

Ví dụ 2: Bạn cần lọc các giao dịch có doanh thu trên 3.000.000, bạn có thể sử dụng công thức QUERY như sau:

=QUERY(A1:D7;"SELECT*WHERE C>3000000";1)

Giải thích:

• Sheet2!A1:D7: Phạm vi dữ liệu từ ô A1 đến D7 trên Sheet 2.
• "SELECT B, C WHERE C > 3000000": Chọn cột B (Sản phẩm) và cột C (Doanh thu), đồng thời lọc chỉ lấy các dòng có Doanh thu lớn hơn 3.000.000.
• 1: Dữ liệu có tiêu đề ở dòng 1.

Sử dụng hàm QUERY để lọc theo điều kiện

Hàm QUERY trong Google Sheets được sử dụng để lọc và tra cứu dữ liệu, thay thế hoặc bổ sung cho các hàm như FILTER, VLOOKUP, và INDEX/MATCH. Với khả năng lọc linh hoạt, QUERY cho phép kết hợp nhiều điều kiện logic phức tạp.

Ví dụ 3: Lọc các sản phẩm có doanh thu dưới 1.000.000, Công thức áp dụng cho yêu cầu này là: =QUERY(A1:D7;"SELECT A,B,C,D WHERE C < 1000000"; 1)

Giải thích:

• A1:D7: Phạm vi dữ liệu từ ô A1 đến D7.
• "SELECT A,B,C,D WHERE C < 1000000": Chọn các cột A, B, C, D và lọc các dòng có Doanh thu (cột C) nhỏ hơn 1.000.000.
• 1: Dữ liệu có tiêu đề ở dòng 1.

Ứng dụng hàm QUERY để lọc và tra cứu dữ liệu hiệu quả trong Google Sheets

Hàm QUERY trong Google Sheets có thể kết hợp dữ liệu từ nhiều sheet thành một sheet tổng hợp. Để làm điều này, bạn sử dụng dấu ngoặc nhọn {} để tạo mảng dữ liệu từ các sheet và áp dụng QUERY lên mảng đó.

Ví dụ 4: Giả sử bạn có dữ liệu từ Đơn vị A (Sheet1) và Đơn vị B (Sheet2). Bạn muốn lọc danh sách các ngày có doanh thu lớn hơn 1.000.000:

Công thức sẽ là:

=QUERY({'Đơn vị A'!A2:C; 'Đơn vị B'!A2:C}, "SELECT * WHERE Col3 >= 1000000", 0)

Giải thích:

• {‘Đơn vị A’!A2:C; ‘Đơn vị B’!A2:C}: Kết nối dữ liệu từ các sheet khác nhau theo chiều dọc (dấu chấm phẩy ;).
• "SELECT *": Chọn tất cả các cột từ các sheet đã kết nối.
• WHERE Col3 >= 1000000: Lọc các dòng có doanh thu (Col3) lớn hơn 1.000.000.
• 0: Dữ liệu không có tiêu đề (giữ tiêu đề của sheet đầu tiên nếu có).

Áp dụng hàm QUERY để kết hợp dữ liệu từ nhiều sheet thành một sheet tổng hợp

Trong hàm QUERY của Google Sheets, các toán tử so sánh đóng vai trò quan trọng trong việc tạo ra các điều kiện lọc trong câu lệnh WHERE. Chúng giúp so sánh giá trị của các ô với một giá trị cụ thể, hoặc giữa các ô với nhau.

Các toán tử so sánh có thể sử dụng với hàm QUERY bao gồm:

• =: So sánh bằng (Equal).
• <>: So sánh không bằng (Not equal).
• >: So sánh lớn hơn (Greater than).
• <: So sánh nhỏ hơn (Less than).
• >=: So sánh lớn hơn hoặc bằng (Greater than or equal).
• <=: So sánh nhỏ hơn hoặc bằng (Less than or equal).

Ví dụ 5: Giả sử bạn có bảng dữ liệu và muốn lọc các giao dịch có doanh thu lớn hơn hoặc bằng 500.000. Bạn có thể sử dụng hàm QUERY với toán tử so sánh >= như sau:

Công thức:

=QUERY(A1:D7;"SELECT * WHERE C >= 500000";1)

Giải thích:

• A1:D7: Phạm vi dữ liệu từ ô A1 đến D7, bao gồm các cột như Ngày, Sản phẩm, Doanh thu, Khu vực.
• "SELECT * WHERE C >= 500000": Chọn tất cả các cột (dấu *) và lọc các dòng có Doanh thu (cột C) lớn hơn hoặc bằng 500.000.
• 1: Dữ liệu có tiêu đề ở dòng 1, nên sẽ không bao gồm dòng tiêu đề trong kết quả.

Áp dụng hàm QUERY kết hợp với toán tử so sánh để lọc dữ liệu theo các điều kiện

Để lọc dữ liệu với nhiều điều kiện trong hàm QUERY của Google Sheets, bạn có thể áp dụng các toán tử logic AND và OR:

• AND: Điều kiện chỉ được thỏa mãn khi tất cả các tiêu chí đã đưa ra đều đúng.
• OR: Điều kiện được thỏa mãn nếu ít nhất một trong các tiêu chí đúng. 

Ví dụ 6: Tìm dữ liệu theo 2 yêu cầu

Tìm các sản phẩm là Laptop và có giá nhỏ hơn 15.000.000 (sử dụng toán tử AND):

Công thức QUERY sẽ như sau: =QUERY(A1:D7;"SELECT * WHERE B = 'Laptop' AND C < 15000000";1)

Giải thích:

• A1:D7: Phạm vi dữ liệu từ ô A1 đến D7, bao gồm các cột như Sản phẩm, Tên sản phẩm, Giá, Khu vực.
• "SELECT * WHERE B = 'Laptop' AND C < 15000000": Lọc các sản phẩm có tên là Laptop và giá nhỏ hơn 15.000.000.
• 1: Dữ liệu có tiêu đề ở dòng 1.

Tìm các sản phẩm là Laptop hoặc có giá nhỏ hơn 15.000.000 (sử dụng toán tử OR):

Công thức QUERY sẽ như sau:

=QUERY(A1:D7; "SELECT * WHERE B = 'Laptop' OR C < 15000000";1)

Giải thích: "SELECT * WHERE B = 'Laptop' OR C < 15000000": Lọc các sản phẩm là Laptop hoặc có giá nhỏ hơn 15.000.000.

Hàm QUERY kết hợp với toán tử AND và OR để lọc dữ liệu theo nhiều điều kiện

Mặc dù hàm QUERY không có câu lệnh IF trực tiếp, bạn có thể kết hợp IF và ISBLANK để kiểm tra và hiển thị thông báo khi không có dữ liệu.

Ví dụ 7: Kiểm tra các sản phẩm có doanh thu dưới 10.000.000. Nếu có, hiển thị danh sách; nếu không, hiển thị thông báo "Không có sản phẩm nào cần bổ sung".

Công thức: =IF(ISBLANK(QUERY(A1:D7, "SELECT A, B, C, D WHERE C < 10000000", 1)), "Không có sản phẩm nào cần bổ sung", QUERY(A1:D7, "SELECT A, B, C, D WHERE C < 10000000", 1))

Giải thích:

• QUERY(A1:D7, "SELECT A, B, C, D WHERE C < 10000000", 1): Lọc các sản phẩm có doanh thu dưới 10.000.000.
• ISBLANK(...): Kiểm tra nếu kết quả của QUERY trống (không có dữ liệu).
• IF(ISBLANK(...), "Không có sản phẩm nào cần bổ sung", QUERY(...)): Nếu không có sản phẩm, hiển thị thông báo. Nếu có, hiển thị kết quả của QUERY.

Kết hợp hàm QUERY với hàm IF để kiểm tra và hiển thị dữ liệu hoặc thông báo

Khi kết hợp hàm QUERY với hàm SUM trong Google Sheets, bạn có thể dễ dàng tạo báo cáo tổng hợp nhanh chóng. Hàm SUM được sử dụng trong câu lệnh SELECT cùng với GROUP BY để tính tổng dữ liệu theo từng nhóm.

Ví dụ 8: Tính tổng doanh thu cho từng khu vực:

Công thức:

=QUERY(A1:D100, "SELECT D, SUM(C) GROUP BY D", 1)

Giải thích:

• A1:D100: Phạm vi dữ liệu từ ô A1 đến D100.
• "SELECT D, SUM(C) GROUP BY D": Tính tổng doanh thu (cột C) cho mỗi khu vực (cột D).
• 1: Dữ liệu có tiêu đề ở dòng 1.

Sử dụng hàm QUERY kết hợp với SUM để tính tổng dữ liệu theo nhóm

Kết hợp QUERY với IMPORTRANGE cho phép bạn truy vấn và xử lý dữ liệu từ một bảng tính Google Sheets khác, giúp tổng hợp dữ liệu từ nhiều nguồn hoặc làm việc với dữ liệu lớn mà không cần sao chép vào bảng tính hiện tại.

Ví dụ 9: Lấy dữ liệu từ bảng tính "Dữ liệu bán hàng" và lọc các giao dịch có doanh thu lớn hơn 1.000.000:

Công thức:

=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/123abcXYZ/edit", "Sheet1!A1:D100"), "SELECT Col1, Col2, Col3, Col4 WHERE Col3 > 10000000", 1)

Giải thích:

• IMPORTRANGE("https://docs.google.com/spreadsheets/d/123abcXYZ/edit", "Sheet1!A1:D100"): Nhập dữ liệu từ Sheet1 trong bảng tính có URL này.
• "SELECT Col1, Col2, Col3, Col4 WHERE Col3 > 10000000": Lọc các giao dịch có doanh thu (Col3) lớn hơn 10.000.000.
• 1: Dữ liệu có tiêu đề ở dòng 1.

Kết hợp hàm QUERY và IMPORTRANGE để truy vấn và xử lý dữ liệu từ nhiều bảng tính 

Mặc dù cả VLOOKUP và QUERY đều có khả năng tra cứu dữ liệu trong Google Sheets, nhưng cách chúng hoạt động lại khác nhau. VLOOKUP tìm kiếm một giá trị trong cột đầu tiên và trả về giá trị tương ứng từ một cột khác, trong khi QUERY có thể thực hiện các tra cứu phức tạp hơn, lọc dữ liệu theo nhiều điều kiện và trả về nhiều hàng hoặc cột. Bạn có thể sử dụng kết quả của một QUERY làm nguồn dữ liệu cho VLOOKUP, hoặc ngược lại.

Ví dụ 10: Lấy tên khu vực của các sản phẩm có sẵn ngày trong bảng.

Công thức:

=VLOOKUP(H2, QUERY(A1:D7, "SELECT *"), 4, FALSE)

Giải thích:

• H2: Giá trị bạn muốn tra cứu, ví dụ là một ngày cụ thể.
• QUERY(A1:D7, "SELECT *"): Truy vấn toàn bộ bảng dữ liệu từ phạm vi A1 đến D7.
• 4: Chỉ định cột trả về giá trị (ở đây là cột thứ 4, tức là cột Khu vực).
• FALSE: Tìm kiếm giá trị chính xác.

Kết hợp hàm QUERY và VLOOKUP để tra cứu và lọc dữ liệu hiệu quả trong Google Sheets

Câu lệnh OFFSET cho phép bạn bỏ qua một số hàng từ đầu kết quả truy vấn, thường được dùng để phân trang hoặc lấy một phần dữ liệu từ vị trí cụ thể.

Ví dụ 11: Lấy các sản phẩm sau 5 sản phẩm đầu tiên:

• Công thức: =QUERY(A1:C7, "SELECT * OFFSET 5")
• Giải thích: Bỏ qua 5 sản phẩm đầu tiên và lấy các sản phẩm tiếp theo.

Chỉ định số hàng muốn bỏ qua trong kết quả truy vấn với câu lệnh OFFSET

Câu lệnh CONTAINS trong WHERE giúp tìm kiếm các ô chứa một chuỗi văn bản cụ thể, cho phép lọc dữ liệu linh hoạt mà không cần khớp chính xác toàn bộ ô.

Cú pháp: WHERE column_id CONTAINS 'text_string'

Ví dụ 12: Tìm tất cả các sản phẩm có tên chứa từ "Laptop":

• Công thức: =QUERY(A1:D7, "SELECT * WHERE B CONTAINS 'Laptop'", 1)
• Giải thích: Lọc các sản phẩm trong cột B có tên chứa từ "Laptop".

Tìm kiếm các giá trị chứa một chuỗi văn bản cụ thể với câu lệnh CONTAINS trong QUERY

Câu lệnh LIKE trong WHERE cho phép tìm kiếm các mẫu chuỗi văn bản, với khả năng sử dụng ký tự đại diện (wildcards), giúp tìm kiếm linh hoạt hơn so với CONTAINS.

Cú pháp: WHERE column_id LIKE 'pattern'

Ký tự đại diện:

• %: Đại diện cho bất kỳ chuỗi ký tự nào (bao gồm chuỗi trống).
• _: Đại diện cho một ký tự bất kỳ.

Ví dụ 13: Lọc các sản phẩm có tên bắt đầu với từ "Laptop":

• Công thức: =QUERY(A1:D7, "SELECT * WHERE B LIKE 'Laptop%'", 1)
• Giải thích: Lọc các sản phẩm trong cột B có tên bắt đầu bằng "Laptop".

Sử dụng câu lệnh LIKE trong QUERY để tìm kiếm theo mẫu chuỗi với ký tự đại diện

Câu lệnh ORDER BY trong QUERY giúp bạn tổ chức kết quả truy vấn theo một hoặc nhiều cột, với tùy chọn sắp xếp theo thứ tự tăng dần hoặc giảm dần.

Cú pháp: ORDER BY column_id [ASC|DESC]

Giải thích:

• ASC: Sắp xếp theo thứ tự tăng dần (mặc định nếu không chỉ định).
• DESC: Sắp xếp theo thứ tự giảm dần.

Ví dụ 14: Để sắp xếp các sản phẩm theo doanh thu từ thấp đến cao:

• Công thức: =QUERY(A1:C7, "SELECT * ORDER BY B ASC", 1)
• Giải thích: Công thức này sẽ sắp xếp các sản phẩm trong cột B (Doanh thu) theo thứ tự tăng dần.

Sắp xếp kết quả truy vấn theo thứ tự tăng hoặc giảm với câu lệnh ORDER BY

GROUP BY trong QUERY giúp nhóm các hàng có giá trị giống nhau trong một hoặc nhiều cột thành một nhóm duy nhất. Khi kết hợp với các hàm tổng hợp như SUM, AVG, COUNT, MAX, MIN, bạn có thể dễ dàng thực hiện phép tính cho từng nhóm dữ liệu.

Cú pháp: GROUP BY column_id_1, column_id_2, …

Ví dụ 15: Nếu bạn muốn tính tổng doanh thu theo khu vực và sản phẩm, công thức sẽ là:

=QUERY(A1:D7, "SELECT D, B, SUM(C) GROUP BY D, B", 1)

Giải thích: Công thức trên nhóm dữ liệu theo khu vực (cột D) và sản phẩm (cột B), sau đó tính tổng doanh thu (cột C) cho từng nhóm được tạo ra.

Nhóm các bản ghi theo cột xác định và áp dụng phép tính tổng hợp với câu lệnh GROUP BY

Câu lệnh LIMIT trong QUERY giúp bạn giới hạn số lượng hàng mà truy vấn trả về, rất hữu ích khi làm việc với các tệp dữ liệu lớn.

Cú pháp: LIMIT N

Ví dụ 16: Lấy 3 sản phẩm có doanh thu cao nhất:

Công thức:

=QUERY(A1:D7, "SELECT B, SUM(C) GROUP BY B ORDER BY SUM(C) DESC LIMIT 3", 1)

Giải thích: Công thức này nhóm các sản phẩm theo tên (cột B), tính tổng doanh thu (cột C), sắp xếp theo doanh thu giảm dần, và chỉ lấy 3 sản phẩm có doanh thu cao nhất.

Hạn chế số lượng kết quả truy vấn trả về với câu lệnh LIMIT

Câu lệnh LABEL trong QUERY giúp bạn thay đổi tên các cột trong kết quả truy vấn, làm cho bảng tính dễ hiểu và chuyên nghiệp hơn khi hiển thị.

Cú pháp: LABEL column_id 'New Label', function(column_id) 'New Function Label'

Ví dụ 17: Tính tổng doanh thu theo khu vực và đổi tên cột tổng doanh thu thành Tổng doanh thu (VND):

Công thức:

=QUERY(A1:D7, "SELECT D, SUM(C) GROUP BY D LABEL SUM(C) 'Tổng Doanh Thu (VNĐ)'", 1)

Giải thích: Công thức này sẽ tính tổng doanh thu trong cột C cho từng khu vực (cột D) và đổi tên cột chứa tổng doanh thu thành Tổng Doanh Thu (VNĐ) để kết quả báo cáo rõ ràng và dễ đọc.

Đổi tên cột trong kết quả truy vấn để làm cho bảng dữ liệu dễ hiểu hơn với câu lệnh LABEL

Để tối ưu hóa hiệu suất và tránh các lỗi không đáng có khi sử dụng hàm QUERY trong Google Sheets, bạn cần lưu ý một số vấn đề quan trọng dưới đây:

• Hiểu rõ kiểu dữ liệu: Đảm bảo mỗi cột chỉ chứa một kiểu dữ liệu duy nhất để tránh lỗi trong truy vấn.
• Sử dụng tiêu đề chính xác: Đặt headers là 1 nếu có tiêu đề, và 0 nếu không có tiêu đề.
• Cẩn thận với dấu nháy: Chuỗi truy vấn phải nằm trong dấu ngoặc kép, còn giá trị văn bản trong mệnh đề WHERE dùng dấu nháy đơn.
• Thứ tự câu lệnh truy vấn: Tuân thủ đúng thứ tự các câu lệnh như SELECT, WHERE, GROUP BY, PIVOT, ORDER BY, LIMIT, OFFSET, LABEL, FORMAT, OPTIONS.
• Tham chiếu cột đúng cách: Dùng chữ cái cột khi truy vấn dải ô, và dùng Col1, Col2, Col3 khi truy vấn kết quả từ hàm khác hoặc mảng.
• Ký tự đặc biệt: Thêm dấu gạch chéo ngược () trước ký tự đặc biệt như %, _, \ khi tìm kiếm trong chuỗi.

Lỗi #ERROR thường xuất hiện khi có vấn đề về cú pháp trong câu truy vấn hoặc kiểu dữ liệu không khớp. Các nguyên nhân phổ biến bao gồm:

• Sai cú pháp truy vấn: Ví dụ, thiếu dấu ngoặc kép quanh câu truy vấn, thiếu dấu ngoặc đơn cho giá trị văn bản trong mệnh đề WHERE, hoặc sai tên câu lệnh như SELLECT thay vì SELECT.
• Sai tham chiếu cột khi dùng IMPORTRANGE: Khi sử dụng IMPORTRANGE, bạn phải dùng Col1, Col2, Col3 thay vì A, B, C.
• Kiểu dữ liệu không khớp: Ví dụ, so sánh một cột số với chuỗi văn bản mà không chuyển đổi đúng kiểu dữ liệu (ví dụ: WHERE C = ‘500’ trong khi cột C là số).

Để khắc phục lỗi #ERROR, bạn cần kiểm tra lại cú pháp câu lệnh, sử dụng công cụ gỡ lỗi truy vấn và đảm bảo kiểu dữ liệu của các cột là phù hợp.

Khắc phục các vấn đề về cú pháp và dữ liệu không phù hợp trong hàm QUERY

Lỗi #VALUE xảy ra khi hàm nhận đối số không hợp lệ hoặc thực hiện phép toán trên giá trị không phải số. Các nguyên nhân phổ biến:

Đối số headers phải là số nguyên (0, 1, 2...), nếu nhập văn bản sẽ gây lỗi.

Sử dụng SUM, AVG trên cột chứa văn bản sẽ gây lỗi.

Để xử lý lỗi #VALUE, bạn cần kiểm tra lại phạm vi dữ liệu và đảm bảo rằng đối số headers được nhập đúng là số nguyên (0, 1, 2,...). Đồng thời, khi sử dụng các hàm tổng hợp như SUM, AVG, bạn cần chắc chắn rằng các cột tính toán chỉ chứa giá trị số, không chứa văn bản. Việc này giúp tránh lỗi phát sinh khi hàm gặp phải các kiểu dữ liệu không phù hợp.

Giải quyết lỗi do đối số không hợp lệ hoặc phép toán trên giá trị không phải số

Lỗi #N/A (Not Available) thường xuất hiện khi truy vấn không thể tìm thấy dữ liệu phù hợp với các yêu cầu đã chỉ định. Những nguyên nhân chính bao gồm:

• Điều kiện trong mệnh đề WHERE không khớp với bất kỳ dữ liệu nào.
• Tham chiếu tới các cột không tồn tại trong phạm vi dữ liệu.

Để sửa lỗi này, bạn cần kiểm tra lại điều kiện trong WHERE và xác nhận rằng phạm vi cột đã chính xác.

Kiểm tra lại điều kiện và phạm vi cột khi không có dữ liệu khớp trong truy vấn

Hàm QUERY trong Google Sheets là một công cụ hữu ích giúp bạn dễ dàng truy vấn, lọc và xử lý dữ liệu hiệu quả. Bằng cách sử dụng các câu lệnh cơ bản như SELECT, WHERE, và ORDER BY, bạn có thể tối ưu hóa quá trình làm việc, nâng cao năng suất và đạt được kết quả chính xác hơn trong việc quản lý dữ liệu.

Khám phá ngay những mẫu máy tính bảng đa dạng tại Siêu Thị Điện Máy - Nội Thất Chợ Lớn! Với nhiều ưu đãi hấp dẫn, bạn sẽ tìm được sản phẩm chất lượng, phù hợp nhu cầu công việc và giải trí.