Buzz
Trong Google Sheet, hàm QUERY hỗ trợ thực hiện tra cứu và lọc dữ liệu theo điều kiện được đặt ra, trong khi đó hàm VLOOKUP giúp tìm kiếm giá trị trong một phạm vi dữ liệu và trả về giá trị tương ứng từ hàng khác. Kết hợp sử dụng hai hàm này có thể giúp tối ưu hóa quá trình xử lý dữ liệu trong bảng tính, giảm thiểu thời gian và công sức. Hãy khám phá chi tiết qua bài viết dưới đây!
1. Sử dụng hàm QUERY trong Google Sheet
- Hàm QUERY là gì?
Hàm Query trong Google Sheet được sử dụng để thực hiện các thao tác liên quan đến dữ liệu như tra cứu, lọc, hoặc kết hợp dữ liệu từ nhiều bảng tính thành một, bằng cách sử dụng các lệnh tương tự như SQL (Structured Query Language - Ngôn ngữ truy vấn có cấu trúc).
- Cách sử dụng hàm QUERY
Công thức:
Điều quan trọng cần lưu ý:
Ví dụ minh họa sau đây:
Sử dụng hàm QUERY để lọc dữ liệu từ bảng tính Bảng Thống kê số lượng bài viết của CTV trong Tháng 4 như sau:
Bảng Thống kê số lượng bài viết của CTV
Bảng này bao gồm một trang tính (được gọi là “Nhóm A”) với danh sách các cộng tác viên (CTV) làm việc cho nhóm A. Dữ liệu bao gồm các trường: Mã CTV, Họ, Tên và Số lượng bài viết của mỗi CTV trong tháng.
Dựa trên bảng số liệu trên, ta có danh sách các CTV có Số lượng bài viết >= 10 trong nhóm A.
Để thực hiện điều này, câu lệnh QUERY sẽ như sau:
Một số điều cần lưu ý:
Câu lệnh Query để truy vấn
2. Hướng dẫn sử dụng hàm VLOOKUP trong Google Sheet
Ý nghĩa của hàm VLOOKUP là gì?
Hàm VLOOKUP được áp dụng trong Google Sheet để tìm kiếm một giá trị cụ thể trong cột đầu tiên và lấy dữ liệu tương ứng từ cùng một hàng trong một cột khác. Thường được sử dụng trong các tình huống phức tạp của báo cáo văn phòng.
Cách thức sử dụng hàm VLOOKUP
Công thức:
Trong phạm vi này:
Ví dụ minh họa:
Sử dụng hàm VLOOKUP để tìm kiếm KPI về số lượng bài viết của từng CTV để đánh giá chất lượng công việc theo vị trí chức vụ. Giá trị cần tìm kiếm là Chức vụ CTV trong ô D3; mảng để tìm kiếm giá trị là mảng H2:I4. Khi nhập công thức =VLOOKUP(D3,$H$2:$I$4,2,0).
Kết quả nhận được là (1) Chính thức sẽ có KPI về số lượng bài viết trong tháng là 10 và (2) Thử việc sẽ là 5000.
Ví dụ về cách sử dụng hàm VLOOKUP để tìm kiếm và lấy giá trị tương ứng
3. Cách kết hợp hàm QUERY và hàm VLOOKUP trong Google Sheet
Video hướng dẫn cách sử dụng kết hợp hàm QUERY và hàm VLOOKUP trong Google Sheet.
Khi sử dụng cùng lúc hàm VLOOKUP và hàm QUERY, chúng ta có thể tạo ra một công cụ mạnh mẽ cho việc tra cứu, lọc dữ liệu từ nguồn dữ liệu khác và tìm giá trị tương ứng trong bảng tính được chỉ định để kéo dữ liệu phù hợp về.
- Công thức:
- Trong phạm vi này:
- Ví dụ minh họa
Sử dụng hàm VLOOKUP kết hợp với QUERY để lọc dữ liệu thống kê số lượng bài viết theo mã số CTV từ nguồn dữ liệu thống kê đầy đủ.
Công thức cần nhập:
=VLOOKUP(F10;QUERY(A2:D12;'SELECT *');4;FALSE)
Trong phạm vi này:
+ QUERY(A2:D12;'SELECT *'): Lọc dữ liệu từ ô A2 đến D12.
+ VLOOKUP: Trích xuất dữ liệu từ cột số 4 của nguồn dữ liệu và đưa vào cột F10.
Kết hợp hàm QUERY trong hàm VLOOKUP
4. Một số ví dụ thực tế áp dụng kết hợp giữa hàm QUERY và hàm VLOOKUP
Video hướng dẫn một số ví dụ thực tế về việc áp dụng kết hợp giữa hàm QUERY và hàm VLOOKUP.
Kéo dữ liệu từ tập tin khác và lọc theo điều kiện ngày, tháng, năm
Ta có bảng dữ liệu các quy định, trong đó có quy định về việc sắp xếp số phòng training cho Team A theo ngày đã đăng ký.
Bảng dữ liệu quy định số phòng training cho Team A
Nhiệm vụ là phải gán số phòng theo quy định vào danh sách các thành viên của Team A đã đăng ký training theo ngày vào bảng dưới.
Bảng dữ liệu danh sách Team A
Câu lệnh kết hợp hàm QUERY và VLOOKUP ở đây:
Trong phạm vi này:
Dựa vào điều kiện ngày, điền dữ liệu số phòng vào danh sách Team A
Kết hợp sử dụng bộ lọc trong Google Sheet giúp dễ dàng biết được số lượng thành viên tham gia training theo ngày và phòng như thế nào.
Lọc dữ liệu các CTV tham gia Training ở phòng 1
Kéo dữ liệu từ file khác và lọc theo điều kiện so sánh chữ
Ta có bảng dữ liệu các quy định, trong đó có quy định về tiền nhuận bút của các CTV theo chức vụ tại Team A.
Bảng dữ liệu quy định tiền nhuận bút theo chức vụ
Nhiệm vụ là gán số tiền nhuận bút/ 1 bài viết theo quy định về chức vụ CTV vào bảng dưới.
Bảng dữ liệu tính nhuận bút cho Team A
Câu lệnh kết hợp hàm QUERY và VLOOKUP lúc này:
Trong đó:
Dựa vào điều kiện số, điền dữ liệu tiền thưởng cho Team A
Kéo dữ liệu từ file khác và lọc theo điều kiện so sánh số
Ta có bảng dữ liệu các quy định, trong đó có quy định về tiền thưởng theo số lượng bài viết của CTV Team A.
Bảng dữ liệu quy định tiền thưởng
Nhiệm vụ là gán số tiền thưởng tương ứng với số lượng bài viết của CTV Team A vào bảng dưới.
Bảng dữ liệu tính thưởng cho nhóm A
Câu lệnh kết hợp hàm QUERY và VLOOKUP lúc này:
Trong đó:
Dựa vào điều kiện số, điền dữ liệu thưởng cho nhóm A
5. Các lỗi thường gặp khi kết hợp hàm QUERY và hàm VLOOKUP
Nếu chưa quen sử dụng kết hợp hàm QUERY và hàm VLOOKUP bạn có thể gặp một số lỗi phổ biến như sau:
Lỗi #N/A
Lỗi #N/A xảy ra khi hàm không thể tìm thấy dữ liệu cần tìm theo yêu cầu của công thức.
Ví dụ: Lỗi #N/A xuất hiện khi nhập sai giá trị cần tìm kiếm là 'E10' - trống thay vì 'F10' - chứa nội dung để so sánh.
Khắc phục lỗi này trong ví dụ bằng cách sửa 'E10' thành 'F10' là giá trị cần tìm kiếm để có kết quả chính xác.
Lỗi #N/A
Lỗi #REF
Lỗi #REF thường xảy ra khi công thức xác định phạm vi ô tham chiếu không hợp lệ.
Trong ví dụ dưới đây, phạm vi ô tham chiếu trong công thức là “B2:D12” không bao gồm cột “MA_SO_CTV” nên không tìm được giá trị hợp lệ. Cách khắc phục là mở rộng phạm vi ô tham chiếu thành “A2:D12”.
Lỗi #REF
Lỗi #ERROR
Nếu nhập thiếu cú pháp đúng như quên dấu ' ' hoặc dấu ',', lỗi #ERROR sẽ xuất hiện. Dữ liệu sẽ chuyển sang màu đen để nhận biết hàm lỗi.
Ví dụ dưới đây viết thiếu dấu ';' phía trước số 4, cú pháp công thức không đúng để lấy cột thông tin thứ 4 nên xuất hiện lỗi #ERROR.
Lỗi #ERROR
Lỗi #VALUE
Lỗi #VALUE xuất hiện khi công thức hàm bạn nhập không phù hợp với kiểu dữ liệu bạn muốn lấy.
Trong ví dụ dưới đây, khi nhập hàm QUERY với công thức “-”, dữ liệu số được yêu cầu, nhưng kiểu dữ liệu tham chiếu lại là nội dung văn bản “MA_SO_CTV”.
Lỗi #VALUE
6. Một số lưu ý khi kết hợp hàm QUERY và hàm VLOOKUP
- Có thể sử dụng chữ in hoa hoặc chữ thường cho hàm QUERY và VLOOKUP.
- Hãy chọn đúng các phần dữ liệu tham chiếu chứa điều kiện lọc trong hàm.
- Lưu ý rằng hàm VLOOKUP chỉ có khả năng tìm kiếm một giá trị được chỉ định cụ thể, không thể sử dụng để tìm các giá trị trong một khoảng.
7. Một số bài tập ví dụ về kết hợp hàm QUERY và hàm VLOOKUP
Trong bảng dữ liệu dưới đây, chúng ta có các thông tin về các phương thức thanh toán, khách hàng và tỷ giá quy đổi.
Bảng dữ liệu đã được cung cấp
Yêu cầu của đề bài là phải áp dụng quy định về chiết khấu cho mỗi phương thức thanh toán tương ứng vào bảng dữ liệu sẵn có.
Thông tin về quy định chiết khấu và yêu cầu của câu hỏi
Câu lệnh kết hợp hàm QUERY và VLOOKUP tại thời điểm này:
Trong đó:
Kết quả
8. Câu hỏi thường gặp khi kết hợp hàm QUERY và hàm VLOOKUP
- Ích lợi của việc sử dụng hàm QUERY kết hợp với hàm VLOOKUP là gì?
Hàm QUERY chủ yếu để truy vấn và trả về dữ liệu theo yêu cầu, còn hàm VLOOKUP dùng để tìm giá trị chính xác. Khi hai hàm này kết hợp, ta có thể dễ dàng truy xuất và gán dữ liệu tương ứng từ file này sang file khác, đảm bảo chính xác và cập nhật nhanh chóng khi có thay đổi số liệu. Điều này giúp tăng hiệu suất làm việc và tránh sai sót.
Ích lợi của việc sử dụng hàm QUERY kết hợp với hàm VLOOKUP
- Ngoài hàm QUERY, hàm VLOOKUP có thể kết hợp với những hàm nào?
Trên Google Sheet, hàm VLOOKUP phổ biến và có nhiều ứng dụng. Ngoài việc kết hợp với hàm QUERY, nó cũng có thể kết hợp với SUM, IF, LEFT/ RIGHT, INDEX, MATCH, AND/ OR, SUMIF và COUNTIF cùng với các điều kiện về ngày tháng, số hoặc văn bản.
- Ngoài hàm VLOOKUP, hàm QUERY có thể kết hợp với những hàm nào?
Ngoài việc kết hợp với hàm VLOOKUP, hàm QUERY có thể kết hợp với nhiều hàm hoặc câu lệnh có điều kiện khác nhau để truy xuất dữ liệu như SELECT, WHERE, các phép toán so sánh, AND/OR, IF, SUM, IMPORTRANGE,...
Các hàm kết hợp VLOOKUP và QUERY
Vậy là, bài viết trên đã hướng dẫn cách sử dụng kết hợp hàm QUERY và VLOOKUP cùng với nhiều ví dụ và lưu ý chi tiết. Chúc bạn thực hiện thành công!
