Thủ thuật

Hướng dẫn cách kết hợp hàm QUERY và VLOOKUP trong Google Sheet đơn giản và dễ thực hiện nhất

Trương Nguyễn Anh Thư

29-04-2023, 10:26 pm

Trong Google Sheet, hàm QUERY sẽ giúp các bạn thực hiện tra cứu và lọc dữ liệu theo định dạng. Còn hàm VLOOKUP sẽ các bạn giúp tìm giá trị chỉ định và kéo dữ liệu phù hợp sang cột khác. Nếu các bạn biết cách sử dụng kết hợp 2 hàm QUERY và VLOOKUP này sẽ mang lại nhiều lợi ích thú vị, giảm đi á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 mình tìm hiểu qua bài viết dưới đây nhé!

Hướng dẫn cách sử dụng hàm QUERY trong Google Sheet

Hàm QUERY có nghĩa 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ư là tra cứu, lọc hay kết hợp các dữ liệu từ nhiều sheet tạo thành một sheet,... bằng cách sử dụng các lệnh cơ sở như 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 hoàn chỉnh).

Hướng dẫn cách sử dụng hàm QUERY

Công thức, như sau:

=QUERY(data; query; headers)

Trong đó:

+ data: Nghĩa là phạm vi vùng dữ liệu (ví dụ: 'Nhom A'!A3:D13).

+ query: Nghĩa là câu truy vấn để xuất dữ liệu mong muốn.

+ headers: Các bạn có thể điền giá trị hoặc để trống.

Thường se 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 cả tên cột tiêu đề).

Nếu các bạn bỏ trống thì mặc định headers sẽ là 1.

Ví dụ minh họa:

Các bạn hãy á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 như hình trên

Bảng sẽ gồm một trang tính (được gọi là “Nhóm A”) bao gồm danh sách những cộng tác viên (CTV) làm việc cho nhóm của A. Bảng dữ liệu gồm các trường như sau: 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, các bạn có thể lọc ra danh sách số CTV có Số lượng bài viết >= 10 trong nhóm của A.

Để thực hiện, câu lệnh QUERY truy vấn lúc này sẽ là như sau:

=QUERY(A3:D13;"SELECT * WHERE D >= 10";1)

Trong đó:

+ A3:D13: là vùng chứa dữ liệu của Nhóm A.

+ "SELECT * WHERE E >= 10": là 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à lấy luôn tên cột tiêu đề dữ liệu đầu trong bảng.

Câu lệnh Query dùng để truy vấn

Hướng dẫn cách sử dụng hàm VLOOKUP trong Google Sheet

Hàm VLOOKUP có nghĩa là gì?

Hàm VLOOKUP được sử dụng trong Google Sheet nhằm mục đích đó là 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 được dùng phổ biến trong các nghiệp vụ báo cáo văn phòng phức tạp.

Hướng dẫn cách sử dụng hàm VLOOKUP

Công thức, như sau:

=VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])

Trong đó:

+ Lookup_value: đó là giá trị cần tìm kiếm.

+ Table_array: đó là hai hoặc nhiều cột dữ liệu.

+ Col_index_num: đó là số cột để kéo dữ liệu.

+ Range_lookup: là 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:

Các bạn hãy 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ị ccác bạn cần tìm kiếm sẽ là Chức vụ CTV ô D3; mảng để dò tìm giá trị là mảng H2:I4. Khi các bạn 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 nhé.

Ví dụ về hàm VLOOKUP trong tìm và chỉ định giá trị tương ứng như hình trên

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

Khi các bạn kết hợp hàm VLOOKUP và hàm QUERY bạn 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 được 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: là giá trị cần tìm kiếm.

+ A3:D13: là vùng chứa dữ liệu của nguồn dữ liệu cần tìm kiếm.

+ SELECT *: là lấy tất cả dữ liệu của cột.

+ 4: là lấy cột thứ 4 trong bảng dữ liệu nguồn.

+ FALSE: Range_lookup: là 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

Các bạn hãy sử dụng hàm VLOOKUP kết hợp với hàm 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ê một cách đầy đủ.

Công thức cần nhập, như sau:

=VLOOKUP(F10;QUERY(A2:D12;"SELECT *");4;FALSE)

Trong đó:

+ QUERY(A2:D12;"SELECT *"): là lọc dữ liệu từ ô A2 đến D12.

+ VLOOKUP: là 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 như hình trên

Một số ví dụ khi áp dụng kết hợp giữa hàm QUERY và hàm VLOOKUP

Cách 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 Teamcủa A theo ngày đã đăng ký.

Bảng dữ liệu sẽ quy định phòng Traning cho Team A

Công việc yêu cầu các bạn 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 đây.

Bảng dữ liệu danh sách của team A

Câu lệnh kết hợp hàm QUERY với VLOOKUP lúc này sẽ là:

=ArrayFormula(VLOOKUP(F3:F12;QUERY('Thông tin'!$A$2:$B$4;"select *");2;false))

Trong đó:

+ ArrayFormula: là á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: là 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: là 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 của 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 nhé.

Lọc dữ liệu các CTV tham gia Training có ở phòng 1

Cách 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 những thông tin quy định, trong đó sẽ có quy định về tiền nhuận bút của các CTV theo chức vụ tại của Team A.

Bảng dữ liệu quy định nhuận bút theo chức vụ như hình trên

Công việc yêu cầu các bạn 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 của team A

Câu lệnh kết hợp hàm QUERY với VLOOKUP lúc này sẽ là:

=ArrayFormula(VLOOKUP(E3:E12;QUERY('Thông tin'!$A$7:$B$9;"select *");2;false))

Trong đó:

+ ArrayFormula: là á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: là 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: là 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 như hình trên

Cách 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 đó sẽ 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 như hình trên

Công việc yêu cầu các bạn 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 như hình trên

Câu lệnh kết hợp hàm QUERY với VLOOKUP lúc này sẽ là:

=ifna(arrayFormula(VLOOKUP(F3:F12;query('Thông tin'!$A$12:$B$18;"select * ");2;false));"Không đủ số lượng")

Trong đó:

+ ifna: là 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: là 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: là 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 như hình trên

Những lỗi thường gặp khi kết hợp hàm QUERY và hàm VLOOKUP

Nếu các bạn 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 sẽ 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.

Lỗi #REF

Lỗi #REF thường sẽ xảy ra khi công thức xác định dải ô tham chiếu không hợp lệ.

Lỗi #ERROR

Nếu các bạn 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 bị lỗi.

Lỗi #VALUE

Lỗi #VALUE sẽ 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.

Một số lưu ý khi bạn kết hợp hàm QUERY và hàm VLOOKUP

- Thứ nhất, sử dụng chữ viết hoa hay chữ thường cho hàm QUERY và VLOOKUP đều được.

- Thứ hai, 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.

- Thứ ba, đố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.

Một số bài tập ví dụ về kết hợp hàm QUERY và hàm VLOOKUP thường gặp

Cho bảng dữ liệu sau, 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 như trên

Đề bài yêu cầucác bạn 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 như hình.

Bảng dữ liệu quy định Discount và yêu cầu câu hỏi như hình trên

Câu lệnh kết hợp hàm QUERY với VLOOKUP lúc này sẽ là:

=ArrayFormula(vlookup(B2:B33;query('Câu hỏi'!A3:B7;"select * ");2;false))

Trong đó:

+ ArrayFormula: Là á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: là 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: là 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ả hoàn tất

Các 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ì dùng để 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 các bạn có thể dễ dàng truy suất và gán dữ liệu tương ứng với giá trị mà bạn 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 về số liệu ở các file. Ngoài ra, còn giúp tăng cao hiệu quả công việc và tránh nhầm lẫn.

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àonữa không?

Trên Google Sheet, hàm VLOOKUP khá là phổ biến và có nhiều ứng dụng. Ngoài trường hợp các bạn kết hợp với hàm QUERY, hàm VLOOKUP thì 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 khác.

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 nữa không?

Ngoài việc kết hợp với hàm VLOOKUP, hàm QUERY thì 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 loại 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à: Lệnh SELECT, điều kiện WHERE, phép toán tử so sánh, AND/ OR, IF, SUM, IMPORTRANCE,...vv

Như vậy, bài viết trên mình đã hướng dẫn bạ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!