Tin Công Nghệ
Game
Đánh Giá
Thủ thuật
Tư Vấn
Khám Phá
Tin Khuyến Mãi
Video Clip
Xây Dựng Cấu Hình Máy Tính
Thủ thuật
Hướng dẫn cách kết hợp hàm QUERY và hàm IF trong Google Sheet chi tiết nhanh chóng
Hàm QUERY là một hàm sẽ giúp bạn lọc dữ liệu hay kết hợp dữ liệu từ nhiều sheet lại thành một sheet. Còn hàm IF sẽ giúp bạn kiểm tra một điều kiện bất kỳ trong bảng dữ liệu được cho. Vậy hàm QUERY khi được kết hợp cùng 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 ra câu trả lời ở bài viết này bạn nhé!
Cách áp dụng hàm QUERY trong Google Sheet
Hàm QUERY trong Google Sheet là một hàm giúp bạn thao tác với một lượng lớn dữ liệu một cách 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,... Một vài ứng dụng mà hàm QUERY có thể hỗ trợ bạn bao gồm: Tra cứu dữ liệ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 của hàm Query:
=QUERY(data; query; headers)
Hàm Query bao gồm ba thành phần:
+ data: Đây là phạm vi vùng dữ liệu.
+ query: Đây là một câu truy vấn để xuất dữ liệu mà bạn mong muốn.
+ headers: Biểu thị số lượng hàng header và tại phần này bạn có thể điền giá trị hoặc để trống.
Ví dụ minh họa cho hàm: Đầu tiên, bạn cần tra cứu dữ liệu hoặc lọc dữ liệu của danh sách các thành viên trong lớp mình đã có đăng ký lịch trực bàn tuyển sinh. Bạn sẽ có hai cách sử dụng hàm 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 như hình trên
+ Tùy chọn theo từng vùng:
=QUERY(A2:H13; "select B, C, D"; 2)
Trong đó:
A2:H13: Tương ứng với dữ liệu - Cũng như phạm vi vùng dữ liệu.
"select": Là hàm QUERY- Được dùng để xuất dữ liệu mà bạn cần xuất trong phạm vi vùng dữ liệu mà bạn đã chọn. Ở đây dùng "select *" tức là đang cần 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 dữ liệu đã được chọn thì bạn sử dụng "select Tên cột/hàng 1, tên cột/hàng 2,... ".
2: Đây là số header, bao gồm hai header đó là ngày tháng và thứ. Bạn có thể chọn cả hai header 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 như hình trên
Các 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 có thể kiểm tra một điều kiện bất kỳ và sẽ trả về cho bạn giá trị mà bạn chỉ định nếu điều kiện đó là TRUE và ngược lại hàm IF sẽ trả về kết quả là một giá trị khác nếu điều kiện của bạn là FALSE.
Công thức của hàm IF:
=IF( logical_test; value_if_true; value_if_false)
Trong đó:
+ logical_test: Biểu thị giá trị kiểm tra hay còn được gọi là điều kiện mà bạn muốn kiểm tra.
+ value_if_true: Đây là giá trị trả về nếu biểu thức logic là đúng.
+ value_if_false: Đây là giá trị trả về nếu biểu thức logic của bạn là sai.
Ví dụ minh họa cho hàm: Bạn cần kiểm tra xem liệu rằng đố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: Biểu thị giá trị bạn muốn kiểm tra. Và đố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ữ”: Biểu thị giá trị trả về nếu đối tượng nghiên cứu của bạn là nữ.
“Sai”: Biểu thị 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 như hình trên
Các cách kết hợp hàm QUERY và hàm IF trong Google Sheet
Hàm IF khi được 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 đã được chọn.
Công thức của hàm IF lồng hàm QUERY:
=if(query(data; query); value_if_true; value_if_false)
Trong đó:
+ query(data;query): Biểu thị vùng dữ liệu mà bạn muốn kiểm tra.
+ value_if_true: Đây là giá trị trả về nếu vùng dữ liệu kiểm tra là đúng.
+ value_if_false: Biểu thị giá trị trả về nếu vùng dữ liệu kiểm tra là sai.
Ví dụ minh họa cho hàm: 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 của học sinh. Với điều kiện là 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 thì là "trung bình".
Công thức của hàm:
=IF(QUERY(A2:A9;"select F")>5,5;"giỏi";"trung bình")
Trong đó:
+ QUERY(A2:F9;"select F") > 5,5: Đây là logical_test - Là đ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 hay cụ thể 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": Đây là value_if_true - Giá trị trả về kết quả nếu điểm trung bình lớn hơn 5,5.
+ "trung bình": Đây là value_if_false - Giá trị trả về kết quả 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 như hình trên
Một số ví dụ kết hợp hàm giữa hai hàm IF và hàm QUERY trong Google Sheet
Kéo dữ liệu từ file khác và lọc dữ liệu đó theo điều kiện so sánh chữ
Bước 1: Mở trang tính gốc có chứa dữ liệu mà bạn muốn kéo sang một file bất kỳ > Sau đó, bạn tiến hành copy URL của file.
Bước 2: Tiếp theo, bạn sử dụng hàm IMPORTRANGE để kéo dữ liệu từ file gốc sang một file mới.
Công thức của hàm:
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1a6n7LRioci5sm1MNQ1pXM9hzWCRnCZfC50iZySTeZkc/edit#gid=449113747";"Class B!A1:F9")
Trong hàm IMPORTRANGE bao gồm:
+ Link URL đã được copy.
+ Class B: Tên sheet ở file gốc mà bạn muốn kéo nó sang một file mới.
+ A1:F9: Biểu thị vùng dữ liệu mà bạn muốn kéo từ file gốc.
Bước 3: Bạn sử dụng hàm IF lồng với hàm QUERY để lọc theo điều kiện so sánh chữ.
Bạn sử dụng hàm IF lồng với hàm 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ề sẽ là 1, nếu học sinh đó không phải là giới tính nữ thì kết quả trả về sẽ là 0.
=IF(QUERY(A3:F10;"select D")="Nữ";"1";"0")
Kéo dữ liệu từ file khác và lọc dữ liệu đó theo điều kiện so sánh số
Bước 1: Bạn tiến hành mở trang tính gốc có chứa dữ liệu mà bạn muốn kéo sang một file bất kỳ > Sau đó, bạn copy URL của file.
Sau khi bạn đã mở được trang tính có chứa dữ liệu mà bạn cần kéo sang file mới >Bạn tiến hành copy URL của file. Và đối với các máy tính thuộc hệ điều hành Windows thì bạn sử dụng phím tắt Ctrl + V, còn đối với Macbook thì bạn sử dụng tổ hợp phím Command + V.
Bước 2: Ở bước tiếp theo, bạn sử dụng hàm IMPORTRANGE để kéo dữ liệu từ file gốc sang một file mới.
Bước 3: Sau đó, bạn sử dụng hàm IF lồng với hàm QUERY để lọc dữ liệu theo điều kiện so sánh số.
Cụ thể, bạn sử dụng hàm IF lồng hàm QUERY để lọc ra những học sinh có 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ề sẽ là "chúc bạn thành công".
Công thức của hàm:
=IF(QUERY(A3:F10;"select F where C='Như'")>8;"chúc các bạn thành công"
Các lỗi thường gặp phải khi kết hợp hàm QUERY và hàm IF trong Google Sheet
Lỗi #N/A
Lỗi #N/A là lỗi sẽ xuất hiện khi hàm không thể tìm thấy giá trị dò tìm hoặc bị thiếu số lượng đối số. Và để khắc phục được lỗi này bạn cần phải kiểm tra lại xem số lượng đối số trong hàm IF hoặc hàm QUERY đã đủ hay chưa.
Lỗi #N/A như hình trên
Lỗi #REF
Lỗi #REF sẽ 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ỗi này 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 #N/A như hình trên
Lỗi #ERROR
Nguyên nhân gây ra lỗi #ERROR thường là khi bạn nhập không đúng cú pháp công thức, thường là sẽ quên dấu " ". Dấu hiệu để nhận biết lỗi này là màu dữ liệu sẽ chuyển sang màu đen như hình minh họa 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 ta thường thấy.
Lỗi #ERROR! như hình trên
Lỗi #VALUE
Lỗi #VALUE là một lỗi 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 hàm IF.
Lỗi #ERROR! như hình trên
Để khắc phục được lỗi #VALUE bạn cần kiểm tra lại xem liệu mình đã nhập đúng cấu trúc của hàm này hay chưa, liệu bạn đã nhập thiếu những phần nào hay thừa dữ liệu nào và tiến hành chỉnh sửa lại cho đúng với cấu trúc của hàm.
Một số lưu ý quan trọng khi kết hợp hàm QUERY và hàm IF
- Bạn cần để ý đến các dấu nháy đơn, nháy kép, dấu ngoặc tròn. Nếu bạn nhập cú pháp hàm 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 trường hợp các dữ liệu chữ khi đặt điều kiện cho hàm IF hoặc QUERY thì bạn bắt buộc phải nhập dấu nháy đơn cho cú pháp hàm thì khi đó hàm mới trả về kết quả.
'select D' là sai, màu chữ là màu đen và ngược lại như hình trên
- Khi tiến hành truy vấn dữ liệu bạn nên lưu ý đến khoảng cách, nếu bạn viết không đúng khoảng cách theo cấu trúc hàm thì hàm sẽ bị lỗi. Và trong trường hợp này, bạn phải mất rất nhiều thời gian để có thể tìm ra vị trí cần sửa lỗi.
Một số bài tập ví dụ về sự kết hợp giữa hai hàm QUERY và hàm IF trong Google Sheet
File dữ liệu ví dụ mình xin để ở đây: NC-MAR-1-25
Bài tập 1: Dựa vào kiến thức đã học, bạn hãy sử dụng hàm IF lồng với hàm 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ề sẽ là "False".
=IF(QUERY(A4:D128;"select D")="Sinh viên";"True";"False")
Bài 1
Bài tập 2: Dựa vào kiến thức đã học, bạn hãy sử dụng hàm IF lồng với hàm QUERY để lọc ra sinh viên có tên là Huệ với điểm trung bình lớn hơn 5, dữ liệu bạn 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 2
Những câu hỏi thường gặp phải khi bạn kết hợp hàm QUERY và hàm IF
Khi nào thì ta sử dụng hàm IF lồng với hàm QUERY để lọc theo điều kiện so sánh chữ?
Hàm IF lồng với hàm QUERY lọc theo điều kiện so sánh chữ được 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…
Hàm QUERY lồng với IF hoặc hàm IF lồng hàm QUERY là gì?
Đây là một thuật ngữ chỉ sự kết hợp giữa hai hàm trong Google Sheet, hay lồng ghép các thao tác tính toán lại với nhau.
Bài viết trên đây là những kiến thức cơ bản về việc kết hợp giữa hàm IF và hàm QUERY, mong rằng thông tin trên sẽ giúp ích cho bạn trong quá trình tính toán dữ liệu bằng Google Sheet!
Mời bạn tham gia group Facebook của Mygear cùng nhau trao đổi, thảo luận các thông tin hot về thị trường công nghệ Việt Nam, tham gia ngay tại: Mygear – Group
Bài viết liên quan
Hướng dẫn giãn dòng, chỉnh khoảng cách dòng trong Microsoft Word cực đơn giản
Bình luận bài viết