Tin Nóng![[Video] Cách kết hợp hàm QUERY và VLOOKUP trong Google Sheet đơn giản](https://bachkhoavietnam.vn/wp-content/uploads/2024/09/video-cach-ket-hop-ham-query-va-vlookup-trong-google-sheet-thumb.jpg)
Trong Google Sheet, hàm QUERY giúp thực hiện tra cứu và lọc dữ liệu theo định dạng. Còn hàm VLOOKUP giúp tìm giá trị chỉ định và kéo dữ liệu phù hợp sang cột khác. Nếu biết cách sử dụng kết hợp 2 hàm QUERY và VLOOKUP sẽ mang lại nhiều lợi ích thú vị, giảm áp lực công việc xử lý dữ liệu tính toán trên máy tính. Hãy cùng tìm hiểu qua bài viết dưới đây nhé!
1. Cách sử dụng hàm QUERY trong Google Sheet
– Hàm QUERY là gì?
Hàm Query được sử dụng trong Google Sheet nhằm thực hiện các thao tác với dữ liệu như tra cứu, lọc hay kết hợp các dữ liệu từ nhiều sheet thành một sheet,… bằng cách 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).
– Cách sử dụng hàm QUERY
Công thức:
=QUERY(data; query; headers)
Trong đó:
+ data: Là phạm vi vùng dữ liệu (ví dụ: ‘Nhom A’!A3:D13).
+ query: Là câu truy vấn để xuất dữ liệu mong muốn.
+ headers: Bạn có thể điền giá trị hoặc để trống.
Thường có 2 giá trị (0 hoặc 1); 0 nghĩa là không lấy tên cột tiêu đề dữ liệu; 1 nghĩa là lấy luôn tên cột tiêu đề).
Nếu bạn bỏ trống thì mặc định headers sẽ là 1.
Ví dụ minh họa:
Áp dụng hàm QUERY để lọc dữ liệu từ bảng tính Thống kê số lượng bài viết Cộng tác viên Tháng 4 dưới đây:
Bảng Thống kê số lượng bài viết của CTV
Bảng gồm một trang tính (được gọi là “Nhóm A”) bao gồm danh sách các cộng tác viên (CTV) làm việc cho nhóm A. Bảng dữ liệu 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 vào bảng số liệu trên, ta lọc ra danh sách số CTV có Số lượng bài viết >= 10 trong nhóm A.
Để thực hiện, câu lệnh QUERY truy vấn lúc này sẽ là:
=QUERY(A3:D13;”SELECT * WHERE D >= 10″;1)
Trong đó:
+ A3:D13: Vùng chứa dữ liệu của Nhóm A.
+ “SELECT * WHERE E >= 10”: Lấy ra dữ liệu của tất cả các cột với điều kiện Số lượng bài viết >= 10.
+ 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
2. Cách sử dụng hàm VLOOKUP trong Google Sheet
Hàm VLOOKUP là gì?
Hàm VLOOKUP được sử dụng trong Google Sheet nhằm mục đích tìm một giá trị được chỉ định trong cột đầu tiên và kéo dữ liệu phù hợp từ cùng một hàng trong một cột khác. Hàm này thường dùng phổ biến trong các nghiệp vụ báo cáo văn phòng phức tạp.
Cách sử dụng hàm VLOOKUP
Công thức:
=VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])
Trong đó:
+ Lookup_value: Giá trị cần tìm kiếm.
+ Table_array: Hai hoặc nhiều cột dữ liệu.
+ Col_index_num: Số cột để kéo dữ liệu.
+ Range_lookup: Xác định tìm kiếm là hoàn toàn chính xác (FALSE) hay chỉ tương đối chính xác (TRUE hoặc bỏ qua).
Ví dụ minh họa:
Sử dụng hàm VLOOKUP để dò tìm KPI số lượng bài viết của mỗi 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 ô D3; mảng để dò tì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ả là (1) Chính thức sẽ có KPI lượng bài viết trong tháng là 10 và (2) Thử việc sẽ là 5000.
Ví dụ về hàm VLOOKUP trong tìm và chỉ định 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 kết hợp hàm QUERY và hàm VLOOKUP trong Google Sheet.
Khi kết hợp hàm VLOOKUP và hàm QUERY ta sẽ sở hữu một hàm có khả năng tra cứu, lọc dữ liệu từ nguồn dữ liệu khác và tìm giá trị tương ứng ở bảng tính được chỉ định để kéo dữ liệu phù hợp về.
– Công thức:
=VLOOKUP(Lookup_value;QUERY(A3:D13;”SELECT *”);4;FALSE)
– Trong đó:
+ Lookup_value: Giá trị cần tìm kiếm.
+ A3:D13: Vùng chứa dữ liệu của nguồn dữ liệu cần tìm kiếm.
+ SELECT *: Lấy tất cả dữ liệu của cột.
+ 4: Lấy cột thứ 4 trong bảng dữ liệu nguồn.
+ FALSE: Range_lookup: Xác định tìm kiếm là hoàn toàn chính xác (FALSE) hay chỉ tương đối chính xác (TRUE hoặc bỏ qua).
– Ví dụ minh họa
Sử dụng hàm VLOOKUP kết hợp 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 đó:
+ QUERY(A2:D12;”SELECT *”): Lọc dữ liệu từ ô A2 đến D12.
+ VLOOKUP: Lấy dữ liệu từ cột số 4 từ nguồn dữ liệu vào cột F10.
Kết hợp hàm QUERY trong hàm VLOOKUP
4. Một số ví dụ á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ụ áp dụng kết hợp giữa hàm QUERY và hàm VLOOKUP.
Kéo dữ liệu từ file 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 thông tin quy định, trong đó có quy định về sắp xếp số phòng traning cho Team A theo ngày đã đăng ký.
Bảng dữ liệu quy định phòng Traning cho Team A
Công việc yêu cầu 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 lúc này:
=ArrayFormula(VLOOKUP(F3:F12;QUERY(‘Thông tin’!$A$2:$B$4;”select *”);2;false))
Trong đó:
+ ArrayFormula: Áp dụng cùng một công thức cho tất cả các hàng trong cột với cùng một định dạng.
+ F3:F12: Vùng chứa các giá trị cần tìm kiếm dữ liệu tương ứng.
+ QUERY(‘Thông tin’!$A$2:$B$4;”select *”);2;false: Tìm kiếm dữ liệu từ file Thông tin, lọc và so sánh các giá trị trong vùng A2:B4 để lấy dữ liệu được yêu cầu.
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 có thể dễ dàng biết được tất cả số lượng thành viên tham gia training theo ngày và theo 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 thông tin 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 nhuận bút theo chức vụ
Công việc yêu cầu 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:
=ArrayFormula(VLOOKUP(E3:E12;QUERY(‘Thông tin’!$A$7:$B$9;”select *”);2;false))
Trong đó:
+ ArrayFormula: Áp dụng cùng một công thức cho tất cả các hàng trong cột với cùng một định dạng.
+ E3:E12: Vùng chứa các giá trị cần tìm kiếm dữ liệu tương ứng.
+ QUERY(‘Thông tin’!$A$7:$B$9;”select *”);2;false: Tìm kiếm dữ liệu từ file Thông tin, lọc và so sánh các giá trị trong vùng A7:B9 để lấy dữ liệu được yêu cầu.
Dựa vào điều kiện chữ điền dữ liệu tiền nhuận bút 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 thông tin 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 thưởng
Công việc yêu cầu 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 team A
Câu lệnh kết hợp hàm QUERY và VLOOKUP lúc này:
=ifna(arrayFormula(VLOOKUP(F3:F12;query(‘Thông tin’!$A$12:$B$18;”select * “);2;false));”Không đủ số lượng”)
Trong đó:
+ ifna: Trả về giá trị bạn chỉ định “Không đủ số lượng” nếu hàm VLOOKUP trả về giá trị #N/A khi không tìm được dữ liệu tương ứng.
+ F3:F12: Vùng chứa các giá trị cần tìm kiếm dữ liệu tương ứng.
+ query(‘Thông tin’!$A$12:$B$18;”select * “);2;false: Tìm kiếm dữ liệu từ file Thông tin, lọc và so sánh các giá trị trong vùng A12:B18 để lấy dữ liệu được yêu cầu.
Dựa vào điều kiện số điền dữ liệu thưởng cho Team 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ể sẽ gặp một số lỗi phổ biến dưới đây:
Lỗi #N/A
Lỗi #N/A xảy ra khi hàm không tìm thấy dữ liệu cần tìm mà công thức yêu cầu.
Ví dụ: Lỗi #NA xảy ra khi nhập nhầm giá trị cần tìm kiếm là “E10” – trống thay vì “F10” – chứa nội dung để đối chiếu.
Khắc phục lỗi này ở ví dụ bằng cách sửa “E10” thành “F10” là giá trị cần tìm kiếm lấy kết quả.
Lỗi #NA
Lỗi #REF
Lỗi #REF thường xảy ra khi công thức xác định dải ô tham chiếu không hợp lệ.
Ở ví dụ bên dưới, dải ô tham chiếu ở công thức là “B2:D12” không chứa giới hạn tham chiếu là cột “MA_SO_CTV” vì thế không tìm được giá trị hợp lệ. Khắc phục bằng cách mở rộng giải ô tham chiếu thành “A2:D12”.
Lỗi #REF
Lỗi #ERROR
Nếu nhập không đúng cú pháp công thức như quên dấu ” ” hoặc dấu “,” thì 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 chính xác để nhận biết 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 vì công thức hàm bạn đang nhập không khớp với kiểu dữ liệu bạn muốn lấy.
Ở ví dụ dưới đây, hàm nhập QUERY có công thức “-” nghĩa là dữ liệu số, trong khi 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
– Sử dụng chữ viết hoa hay chữ thường cho hàm QUERY và VLOOKUP đều được.
– Chọn đúng các phần dữ liệu tham chiếu có chứa điều kiện lọc trong hàm.
– Đối với hàm VLOOKUP chỉ có khả năng tìm một giá trị được chỉ định cụ thể vì thế không thể sử dụng để tìm các giá trị trong khoảng.
7. Một số bài tập ví dụ về kết hợp hàm QUERY và hàm VLOOKUP
Cho bảng dữ liệu dưới đây, bao gồm các phương thức thanh toán, khách hàng và các tỷ giá quy đổi.
Bảng dữ liệu đã cho sẵn
Đề bài yêu cầu gắn quy định về Discount (chiết khấu) cho từng phương thức thanh toán tương ứng vào bảng dữ liệu cho sẵn.
Bảng dữ liệu quy định Discount và yêu cầu câu hỏi
Câu lệnh kết hợp hàm QUERY và VLOOKUP lúc này:
=ArrayFormula(vlookup(B2:B33;query(‘Câu hỏi’!A3:B7;”select * “);2;false))
Trong đó:
+ ArrayFormula: Áp dụng cùng một công thức cho tất cả các hàng trong cột với cùng một định dạng.
+ B2:B33: Vùng chứa các giá trị cần tìm kiếm dữ liệu tương ứng.
+ query(‘Câu hỏi’!A3:B7;”select * “);2;false: Tìm kiếm dữ liệu từ file Câu hỏi, lọc và so sánh các giá trị trong vùng A3:B7 để lấy dữ liệu Discount được yêu cầu phải gắn vào.
Kết quả
8. Những câu hỏi thường gặp khi kết hợp hàm QUERY và hàm VLOOKUP
– Lợi ích của việc sử dụng hàm QUERY kết hợp với hàm VLOOKUP là gì?
Hàm QUERY với chức năng chính là truy vấn và trả về dữ liệu theo yêu cầu, còn hàm VLOOKUP thì thực hiện lệnh tìm giá trị chính xác được chỉ định. Chính vì thế, khi kết hợp hai hàm này ta có thể dễ dàng truy suất và gán dữ liệu tương ứng với giá trị mong muốn từ file này sang file khác. Đảm bảo độ chính xác, tin cậy và cập nhật kịp thời nếu có sự thay đổi số liệu ở các file. Giúp tăng cao hiệu quả công việc và tránh nhầm lẫn.
Lợi ích của việc sử dụng hàm QUERY kết hợp với hàm VLOOKUP
– Ngoài kết hợp với hàm QUERY, hàm VLOOKUP còn có thể kết hợp với những hàm nào?
Trên Google Sheet, hàm VLOOKUP khá phổ biến và có nhiều ứng dụng. Ngoài trường hợp kết hợp với hàm QUERY, hàm VLOOKUP còn có thể kết hợp với các hàm SUM, IF, LEFT/ RIGHT, INDEX, MATCH, AND/ OR, SUMIF và COUNTIF cùng với các dạng điều kiện ngày tháng năm, chữ số hoặc văn bản.
– Ngoài kết hợp với hàm VLOOKUP, hàm QUERY còn 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 với chức năng truy vấn và tham chiếu dữ liệu có thể kết hợp với nhiều hàm hoặc câu lệnh có điều kiện để lấy ra dữ liệu cần thiết như: Lệnh SELECT, điều kiện WHERE, phép toán tử so sánh, AND/ OR, IF, SUM, IMPORTRANCE,…
Các hàm kết hợp VLOOKUP và QUERY
Một số mẫu laptop đang kinh doanh tại Thế Giới Di Động:
Như vậy, bài viết trên đã hướng dẫn bạn cách sử dụng kết hợp hàm QUERY và VLOOKUP cùng nhiều ví dụ và lưu ý chi tiết. Chúc bạn thực hiện thành công!