10 cách sử dụng hàm query trong excel 2023

Tháng Một 3, 2024

Hàm QUERY là một công cụ vô cùng hữu ích để lọc và truy vấn dữ liệu và dễ dàng trích xuất thông tin cần thiết từ hàng nghìn dòng dữ liệu chỉ trong vài bước đơn giản. Hãy cùng Genz.edu.vn tìm hiểu 10 cách sử dụng hàm query trong excel để tối ưu hóa công việc 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:

Xem thêm:  Cách sắp xếp điểm từ cao xuống thấp trong excel nhanh chóng

1. Hàm QUERY là gì? Công thức hàm QUERY

– 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: Phạm vi dữ liệu thực hiện truy vấn.
  • query: Câu truy vấn cần thực hiện để xuất dữ liệu mong muốn.
  • headers: Không bắt buộc, có thể điền giá trị hoặc bỏ trống.

+ Thường có 2 giá trị: 0 nghĩa là bỏ qua và không lấy tên cột tiêu đề, 1 nghĩa là lấy tên cột tiêu đề.

+ Nếu bạn không điền giá trị thì headers được mặc định là 1.

– Ví dụ về hàm QUERY: Lấy dữ liệu cột B và C trong vùng dữ liệu từ A1 đến E11.

Bước 1: Nhập công thức =QUERY(A1:E11;”SELECT B, C”).

Nhập công thức lấy dữ liệu cột B và C
Nhập công thức lấy dữ liệu cột B và C

Bước 2: Nhấn Enter và xem kết quả. Lúc này, dữ liệu của cột B, C trong vùng dữ liệu A1:E11 sẽ được hiển thị.

Dữ liệu cột B và C hiển thị
Dữ liệu cột B và C hiển thị

2. 10 cách sử dụng hàm query

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

Xem ngay bài hay nhất:  Cách tính tổng dấu x trong Excel - Học Tốt
Bảng dữ liệu có sẵn
Bảng dữ liệu có sẵn

2.1.1. 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ất cả dữ liệu từ ô A1 đến ô D8.

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

2.1.2. 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
Chỉ lấy dữ liệu cột Họ và Tên

2.2. Hàm QUERY với điều kiện WHERE

2.2.1 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 tất cả dữ liệu từ ô A1 đến ô D8 với điều kiện Giới tính là “Nữ”.

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

2.2.2. 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 tất cả 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.

Lấy ra các bạn có giới tính Nữ và điểm trung bình
Lấy ra các bạn có giới tính Nữ và điểm trung bình >=8

2.3. Hàm Query để lọc, tra cứu dữ liệu

Cho bảng sau:

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

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

Để thực hiện, 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ấn
Câu lệnh Query truy vấn

Như câu 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à xác định các đ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).

2.4. 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 kết hợp dữ liệu từ nhiều sheet thành 1 sheet
Query kết 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ợp
Tạo sheet Tổng hợp

Câ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
Tổng hợp dữ liệu tại Sheet Tổng hợp

2.5. 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
QUERY kết 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,…

2.6 Hàm QUERY kết hợp với AND, OR

2.6.1. Kết hợp với toán tử AND

Công thức: =QUERY(A1:E7;”SELECT * WHERE D = ‘Nam’ AND E>=5″)

Xem ngay bài hay nhất:  Hướng dẫn sử dụng Hàm FORECAST dự đoán giá trị tương lai

Ý 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
QUERY kết hợp với AND

2.6.2. 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
QUERY kết hợp với OR

2.7. 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
QUERY kết hợp với hàm IF

Để câu lệnh tự động cập nhật cho các dòng dữ liệu bên dưới bạn có thể dùng hàm ArrayFormula() như sau:

Hàm Query kết hợp hàm ArrayFormula()
Hàm Query kết hợp hàm ArrayFormula()

2.8. 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 SUM

2.9. 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
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: =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 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 IMPORTRANGE

2.10. 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
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ợ

3.1. 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
QUERY kết hợp Offset

3.2. 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 sinh có mã là A_001.

QUERY kết hợp Contains
QUERY kết hợp Contains

3.3. Like: Lọc dữ liệu

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

QUERY kết hợp Like
QUERY kết hợp Like

3.4. 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
QUERY kết hợp Order by

3.5. 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ị.

Xem ngay bài hay nhất:  Cách tổng hợp kết quả khảo sát bằng excel - Diemayxanh.com

+ 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
QUERY kết hợp Group by

3.6. 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
QUERY kết hợp Limit

3.7. 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
QUERY kết hợp Label

4. Những 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

5.1. 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
Sửa lỗi ERROR

5.2. 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
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é!

5.3. 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. 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ả
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.

Hy vọng rằng những hướng dẫn từ Genz.edu.vn đã giúp bạn nắm vững công thức hàm QUERY, 10 cách sử dụng hàm query trong excel bên cạnh đó giúp bạn tránh khỏi những lỗi khi sử dụng hàm QUERY và một số lưu ý khi sử dụng hàm.

Vì vậy, việc sử dụng hàm QUERY trong Google Trang tính để lọc dữ liệu bây giờ không còn là điều xa lạ đối với bạn nữa. Không chỉ giúp tiết kiệm thời gian tiết kiệm mà hàm QUERY còn mang lại sự chính xác và hiệu quả trong quá trình truy vấn thông tin.
Cảm ơn bạn đã ghé qua!