[Video] Cách kết hợp hàm QUERY và hàm IF trong Google Sheet chi tiết

Hòa Phước21/02/2023

Hàm QUERY sẽ giúp bạn lọc dữ liệu hay kết hợp dữ liệu từ nhiều sheet thành một sheet, còn hàm IF giúp bạn kiểm tra một điều kiện bất kỳ trong bảng dữ liệu. Vậy hàm QUERY khi kết hợp với hàm IF có phải sẽ giúp chúng ta lọc dữ liệu theo một điều kiện nào đó không? Cùng tìm câu trả lời ở bài viết này nhé!

1. Cách sử dụng hàm QUERY trong Google Sheet

Hàm QUERY trong Google Sheet sẽ giúp bạn thao tác với một lượng lớn dữ liệu cực linh hoạt và dễ dàng dựa trên các lệnh cơ sở dữ liệu như: SQL, ngôn ngữ truy vấn có cấu trúc,... Các thao tác mà hàm QUERY có thể hỗ trợ bạn gồm: Tra cứu, lọc dữ liệu hoặc kết hợp dữ liệu từ nhiều sheet thành một sheet,...

Công thức hàm Query:

=QUERY(data; query; headers)

Hàm Query gồm có ba thành phần:

+ data: Phạm vi vùng dữ liệu.

+ query: Câu truy vấn để xuất dữ liệu mong muốn.

+ headers: Số lượng hàng header, tại phần này bạn có thể điền giá trị hoặc để trống.

Ví dụ minh họa: Bạn cần tra cứu hoặc lọc dữ liệu của danh sách các thành viên trong lớp mình đăng ký lịch trực bàn tuyển sinh. Bạn sẽ có hai cách sử dụng QUERY.

+ Chọn tất cả:

=QUERY(A2:H13; "select *"; 2)

Chọn xuất toàn bộ dữ liệu trong phạm vi được chọn

+ Tùy chọn từng vùng:

=QUERY(A2:H13; "select B, C, D"; 2)

Trong đó:

A2:H13: Tương ứng với data - Phạm vi vùng dữ liệu.

"select": Là QUERY- Dùng để xuất dữ liệu mà bạn cần trong phạm vi vùng dữ liệu đã chọn. Ở đây dùng "select *" tức là đang xuất toàn bộ dữ liệu trong phạm vi vùng dữ liệu được chọn. Nếu bạn muốn xuất một phần dữ liệu trong phạm vi đã được chọn thì sử dụng "select Tên cột/hàng 1, tên cột/hàng 2,... ".

2: Là header. Gồm có hai header đó là ngày tháng và thứ. Bạn có thể chọn cả hai hoặc chọn 1 header.

Chọn xuất một phần dữ liệu trong phạm vi vùng dữ liệu đã chọn

Bạn cũng có thể tìm hiểu cụ thể hơn về hàm QUERY thông qua bài viết: Cách sử dụng hàm QUERY trong Google Sheet để lọc dữ liệu.

2. Cách sử dụng hàm IF trong Google Sheet

Hàm IF là một hàm logic giúp người dùng kiểm tra một điều kiện bất kỳ và trả về giá trị mà bạn chỉ định nếu điều kiện đó TRUE và ngược lại hàm IF sẽ trả về một giá trị khác nếu điều kiện của bạn là FALSE.

Công thức hàm IF:

=IF( logical_test; value_if_true; value_if_false)

Trong đó:

+ logical_test: Là giá trị kiểm tra hay còn gọi là điều kiện mà bạn muốn kiểm tra.

+ value_if_true: Là giá trị trả về nếu biểu thức logic đúng.

+ value_if_false: Là giá trị trả về nếu biểu thức logic sai.

Ví dụ minh họa: Bạn cần kiểm tra xem đối tượng nghiên cứu của mình có thuộc giới tính nữ hay không?

= IF(C4 = 2; "Nữ"; "Sai")

Trong đó:

C4: Là giá trị bạn muốn kiểm tra. Đối tượng nghiên cứu của bạn là nữ khi giá trị mã hóa ở giới tính là 2.

“Nữ”: Là giá trị trả về nếu đối tượng nghiên cứu của bạn là nữ.

“Sai”: Là giá trị trả về nếu đối tượng nghiên cứu của bạn không phải là nữ.

Ví dụ về hàm IF

3. Cách kết hợp hàm QUERY và hàm IF trong Google Sheet

Video hướng dẫn cách kết hợp hàm QUERY và hàm IF trong Google Sheet.

Hàm IF khi kết hợp với hàm QUERY sẽ giúp bạn kiểm tra một điều kiện bất kỳ tại một vùng dữ liệu cụ thể trong phạm vi vùng dữ liệu đã chọn.

Công thức hàm IF lồng QUERY:

=if(query(data; query); value_if_true; value_if_false)

Trong đó:

+ query(data;query): Là vùng dữ liệu mà bạn muốn kiểm tra.

+ value_if_true: Là giá trị trả về nếu vùng dữ liệu kiểm tra đúng.

+ value_if_false: Là giá trị trả về nếu vùng dữ liệu kiểm tra sai.

Ví dụ minh họa: Bạn muốn kiểm tra học lực của học sinh dựa trên điểm trung bình. Với điều kiện nếu điểm trung bình > 5,5 là giỏi và ngược lại nếu điểm trung bình không lớn hơn 5,5 là "trung bình".

Công thức:

=IF(QUERY(A2:A9;"select F")>5,5;"giỏi";"trung bình")

Trong đó:

+ QUERY(A2:F9;"select F") > 5,5: Là logical_test - Điều kiện mà bạn muốn kiểm tra. Bạn muốn kiểm tra xem vùng dữ liệu F tức là cột điểm trung bình của học sinh có lớn hơn 5,5 hay không.

+ "giỏi": Là value_if_true - Giá trị trả về nếu điểm trung bình lớn hơn 5,5.

+ "trung bình": Là value_if_false - Giá trị trả về nếu biểu thức logic sai, tức là điểm trung bình nhỏ hơn 5,5.

Hàm IF kết hợp với hàm QUERY

4. Một số ví dụ kết hợp giữa hàm IF và hàm QUERY

Video hướng dẫn một số ví dụ kết hợp giữa hàm IF và hàm QUERY.

Kéo dữ liệu từ file khác và lọc theo điều kiện so sánh chữ

Bước 1: Mở trang tính gốc chứa dữ liệu bạn muốn kéo sang một file bất kỳ > Copy URL của file.

Sử dụng phím tắt Ctrl + V đối với windows, Command + V đối với MacBook để copy URL

Bước 2: Sử dụng hàm IMPORTRANGE để kéo dữ liệu từ file gốc sang file mới.

Công thức:

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1a6n7LRioci5sm1MNQ1pXM9hzWCRnCZfC50iZySTeZkc/edit#gid=449113747";"Class B!A1:F9")

Trong hàm IMPORTRANGE gốm có:

+ Link URL đã copy.

+ Class B: Tên sheet ở file gốc mà bạn muốn kéo sang file mới.

+ A1:F9: Vùng dữ liệu bạn muốn kéo từ file gốc.

Hàm IMPORTRANGE

Để hiểu rõ hơn về cách dùng hàm IMPORTRANGE bạn hãy tìm hiểu thêm tại bài viết: [Video] Cách sử dụng hàm IMPORTRANGE trong Google Sheet để lấy dữ liệu.

Bước 3: Sử dụng hàm IF lồng QUERY để lọc theo điều kiện so sánh chữ.

Sử dụng hàm IF lồng QUERY để lọc và mã hóa giới tính học sinh với điều kiện nếu học sinh đó là nữ thì kết quả trả về là 1, nếu học sinh đó không phải giới tính nữ thì kết quả trả về là 0.

=IF(QUERY(A3:F10;"select D")="Nữ";"1";"0")

Lọc theo điều kiện so sánh chữ

Kéo dữ liệu từ file khác và lọc theo điều kiện so sánh số

Bước 1: Mở trang tính gốc chứa dữ liệu bạn muốn kéo sang một file bất kỳ > Copy URL của file.

Sau khi mở được trang tính có chứa dữ liệu bạn cần kéo sang file mới > Copy URL của file. Đối với các máy tính thuộc hệ điều hành Windows thì sử dụng phím tắt Ctrl + V, còn đối với MacBook thì sử dụng Command + V.

Đây là file gốc bạn muốn kéo dữ liệu sang một file mới

Bước 2: Sử dụng hàm IMPORTRANGE để kéo dữ liệu từ file gốc sang file mới.

Dùng hàm IMPORTRANCE để kéo dữ liệu từ file gốc sang.

Bước 3: Sử dụng hàm IF lồng QUERY để lọc theo điều kiện so sánh số.

Sử dụng hàm IF lồng QUERY để lọc ra những học sinh tên như với điều kiện có điểm trung bình lớn hơn 8. Nếu đúng thì kết quả trả về "chúc bạn thành công".

Công thức:

=IF(QUERY(A3:F10;"select F where C='Như'")>8;"chúc các bạn thành công"

Lọc theo điều kiện so sánh số

5. Các lỗi thường gặp khi kết hợp hàm QUERY và hàm IF

Lỗi #N/A

Lỗi #N/A xuất hiện khi hàm không tìm thấy giá trị dò tìm hoặc bị thiếu số lượng đối số. Để khắc phục được lỗi này bạn cần kiểm tra lại xem số lượng đối số trong hàm IF hoặc QUERY đã đủ hay chưa.

Lỗi #N/A

Lỗi #REF

Lỗi #REF xuất hiện khi file của bạn chưa được cấp quyền truy cập. Cách khắc phục là bạn cần phải nhấp vào ô và chọn "Allow Access" (cho phép truy cập) thì dữ liệu sẽ xuất hiện.

Lỗi #REF

Lỗi #ERROR

Khi bạn nhập không đúng cú pháp công thức, thường là sẽ quên dấu " " và đây chính là nguyên nhân gây ra lỗi #ERROR. Thường khi bạn gặp lỗi này thì màu dữ liệu sẽ chuyển sang màu đen như hình và sau khi bạn khắc phục được lỗi thì màu chữ sẽ chuyển thành màu xanh lá cây thường thấy.

Lỗi #ERROR!

Lỗi #VALUE

Lỗi #VALUE xảy ra khi bạn nhập sai cấu trúc của hàm khiến cho hệ thống không thể phân tích được cú pháp của hàm QUERY hoặc IF.

Lỗi #VALUE

Để khắc phục được lỗi này bạn cần kiểm tra lại xem mình đã nhập đúng cấu trúc của hàm hay chưa, thiếu những phần nào hay thừa dữ liệu nào và chỉnh sửa lại cho đúng với cấu trúc của hàm.

6. Một số lưu ý khi kết hợp hàm QUERY và hàm IF

- Để ý các dấu nháy đơn, nháy kép, ngoặc tròn. Nếu bạn bị thiếu dấu thì hàm IF và hàm QUERY của bạn sẽ gặp lỗi.

- Đối với các dữ liệu chữ khi đặt điều kiện cho hàm IF hoặc QUERY thì bắt buộc phải có dấu nháy đơn.

'select D' là sai, màu chữ là màu đen và ngược lại

- Khi truy vấn dữ liệu bạn nên lưu ý khoảng cách, nếu bạn viết không đúng khoảng cách theo cấu trúc thì hàm sẽ bị lỗi.

7. Một số bài tập ví dụ về kết hợp hàm QUERY và hàm IF

File dữ liệu ví dụ: NC-MAR-1-25

Bài tập 1: Sử dụng hàm IF lồng QUERY để lọc ra những đáp viên với điều kiện có nghề nghiệp là "Sinh viên". Nếu đúng thì kết quả trả về là "True", còn nếu không phải là sinh viên thì kết quả trả về là "False".

=IF(QUERY(A4:D128;"select D")="Sinh viên";"True";"False")

Bài tập 1

Bài tập 2: Sử dụng hàm IF lồng QUERY để lọc ra sinh viên tên Huệ với điểm trung bình lớn hơn 5, dữ liệu sử dụng ở file Điểm Trung Bình Học Sinh. Nếu đúng thì kết quả trả về "Học lực khá".

=IF(QUERY(A2:E7;"select E where C='Huệ'")>5;"Học lực khá")

Bài tập 2

8. Những câu hỏi thường gặp khi kết hợp hàm QUERY và hàm IF

Khi nào thì sử dụng hàm IF lồng hàm QUERY để lọc theo điều kiện so sánh chữ?

Hàm IF lồng hàm QUERY lọc theo điều kiện so sánh chữ dùng cho các dữ liệu định tính như: Giới tính nam hay nữ; kết quả học tập của sinh viên: giỏi, khá, trung bình, yếu, nghề nghiệp…

Dữ liệu định tính