Hàm SUBTOTAL và VLOOKUP là những hàm thông dụng được dùng để xử lý dữ liệu trong Google Sheet trên laptop, thế bạn đã biết cách kết hợp hai hàm này như thế nào chưa nhỉ? Nếu chưa hãy tìm hiểu cách dùng kết hợp hai hàm này dưới bài viết nhé!
1. Cách sử dụng hàm SUBTOTAL trong Google Sheet
Hàm SUBTOTAL là gì?
Là hàm trả về tổng phụ cho một dải ô dọc sử dụng một hàm kết hợp đã chỉ định.
Cách sử dụng hàm SUBTOTAL
Công thức:
SUBTOTAL(mã_hàm; dải_ô1; [dải_ô2; …])
Trong đó:
mã_hàm: Hàm sử dụng trong phép kết hợp tổng phụ.
– 1 là hàm AVERAGE.
– 2 là hàm COUNT.
– 3 là hàm COUNTA.
– 4 là hàm MAX.
– 5 là hàm MIN.
– 6 là hàm PRODUCT.
– 7 là hàm STDEV.
– 8 là hàm STDEVP.
– 9 là hàm SUM.
– 10 là hàm VAR.
– 11 là hàm VARP.
Ví dụ minh họa:
Cho bảng dữ liệu như sau:
Bảng dữ liệu mẫu
Tính điểm tổng trung bình 3 môn của bạn Phạm Văn A.
=SUBTOTAL(1;C2:E2)
Ví dụ minh họa hàm SUBTOTAL
2. Cách sử dụng hàm VLOOKUP trong Google Sheet
Hàm VLOOKUP là gì?
Hàm VLOOKUP tìm kiếm một giá trị trong cột trong cùng bên trái của bảng hoặc mảng giá trị, sau đó trả về giá trị theo cột từ bảng mà bạn đã chỉ định trước.
Cách sử dụng hàm VLOOKUP
Công thức:
VLOOKUP(khóa_tìm_kiếm; dải_ô; chỉ_mục; [được_sắp_xếp])
Ví dụ minh họa: Sử dụng bảng dữ liệu trên, xác định điểm toán thống kê của bạn Phạm Văn A.
=VLOOKUP(H2;B2:E7;3;FALSE)
Ví dụ hàm VLOOKUP
Để biết cách sử dụng hàm VLOOKUP chi tiết hơn, bạn nên tham khảo bài viết [Video] Cách sử dụng hàm VLOOKUP trong Google Sheet này nhé!
3. Cách kết hợp hàm SUBTOTAL và VLOOKUP trong Google Sheet
Video hướng dẫn cách kết hợp hàm SUBTOTAL và VLOOKUP trong Google Sheet.
Công thức:
SUBTOTAL(mã_hàm; VLOOKUP(khóa_tìm_kiếm; dải_ô; chỉ_mục; [được_sắp_xếp]))
Giới thiệu các giá trị:
Hàm VLOOKUP: Trả về địa chỉ ô khớp với khóa_tìm_kiếm tương ứng.
Ví dụ minh họa: Thêm một bảng dữ liệu mới như trong hình, so sánh và tìm điểm tin học cơ sở cao nhất của nhóm sinh viên do giảng viên Nguyễn A phụ trách.
=SUBTOTAL(4; VLOOKUP(H2;B2:E7;4;FALSE); VLOOKUP(H5;B2:E7;4;FALSE); VLOOKUP(H6;B2:E7;4;FALSE))
Giải thích: Các hàm VLOOKUP trả về giá trị là điểm môn tin học cơ sở do giảng viên Nguyễn A phụ trách, hàm SUBTOTAL có mã_hàm là 4, nghĩa là hàm SUBTOTAL sẽ tìm giá trị cao nhất trong những kết quả điểm trả về.
So sánh và tìm điểm tin học cơ sở cao nhất
4. Các lỗi thường gặp khi kết hợp hàm SUBTOTAL và hàm VLOOKUP
– Lỗi #N/A
Đây là lỗi do không tìm thấy giá trị tương ứng với điều kiện xét để trả về, vì vậy bạn hãy kiểm tra lại phần điều kiện của mình xem đúng chưa nhé!
Ví dụ: So sánh và tìm điểm tin học cơ sở cao nhất của nhóm sinh viên do giảng viên Nguyễn A phụ trách.
Công thức sai:
=SUBTOTAL( VLOOKUP(G2;B2:E7;3;FALSE); VLOOKUP(G5;B2:E7;3;FALSE); VLOOKUP(G6;B2:E7;3;FALSE))
Công thức đúng:
=SUBTOTAL(4; VLOOKUP(H2;B2:E7;3;FALSE); VLOOKUP(H5;B2:E7;3;FALSE); VLOOKUP(H6;B2:E7;3;FALSE))
Lỗi #N/A
– Lỗi #REF
Đây là lỗi do dữ liệu do dữ liệu đưa vô nằm ngoài vùng dữ liệu đã chọn, bạn kiểm tra lại dữ liệu đang sử dụng và dữ liệu gọi ra nhé!
Ví dụ: Tìm điểm tin học cơ sở cao nhất của nhóm sinh viên do giảng viên Nguyễn A phụ trách.
Công thức sai:
=SUBTOTAL( VLOOKUP(H2;B2:E7;9;FALSE); VLOOKUP(H5;B2:E7;9;FALSE); VLOOKUP(H6;B2:E7;9;FALSE))
Công thức đúng:
=SUBTOTAL(4; VLOOKUP(H2;B2:E7;3;FALSE); VLOOKUP(H5;B2:E7;3;FALSE); VLOOKUP(H6;B2:E7;3;FALSE))
Lỗi #REF
– Lỗi #ERROR
Đây là lỗi do cú pháp bạn nhập chưa khớp với cú pháp quy định của hàm, bạn hãy kiểm tra lại cú pháp của mình lại để sửa lỗi này nhé!
Ví dụ: Tìm điểm tin học cơ sở cao nhất của nhóm sinh viên do giảng viên Nguyễn A phụ trách.
Công thức sai:
=SUBTOTAL( VLOOKUP(H2;B2:E7;9;FALSE); VLOOKUP(H5;B2:E7;9;FALSE); VLOOKUP(H6;B2:E7;9;FALSE))
Công thức đúng:
=SUBTOTAL(4; VLOOKUP(H2;B2:E7;3;FALSE); VLOOKUP(H5;B2:E7;3;FALSE); VLOOKUP(H6;B2:E7;3;FALSE))
Lỗi #ERROR
– Lỗi #VALUE
Đây là lỗi do đối số bạn đưa vào sai hình thức của công thức hàm, bạn kiểm tra lại hình thức đối số bạn đưa vào đúng chưa nhé!
Ví dụ: Tìm điểm tin học cơ sở cao nhất của nhóm sinh viên do giảng viên Nguyễn A phụ trách.
Công thức sai:
=SUBTOTAL(4; VLOOKUP(H2;B2:E7;3;FALSE); VLOOKUP(H5;B2:E7;3;FALSE); VLOOKUP(H6;B2:E73;3;FALSE);6)
Công thức đúng:
=SUBTOTAL(4; VLOOKUP(H2;B2:E7;3;FALSE); VLOOKUP(H5;B2:E7;3;FALSE); VLOOKUP(H6;B2:E7;3;FALSE))
Lỗi #VALUE
5. Một số lưu ý khi kết hợp hàm SUBTOTAL và hàm VLOOKUP
– Hàm SUBTOTAL và hàm VLOOKUP không phân biệt hàm chữ hoa, thường. Vì vậy, khi gọi hàm SUBTOTAL = subtotal và VLOOKUP = vlookup.
– Tùy theo kiểu dữ liệu và công thức hàm mà bạn cần phải cẩn thận dấu “”””, () để tránh gây lỗi khi sử dụng hàm.
– Truy vấn dữ liệu phải đúng giá trị cần tìm, nếu không thì hàm bạn đang dùng sẽ lỗi.
Một số lưu ý khi kết hợp hàm SUBTOTAL và hàm VLOOKUP
– Dùng hàm SUBTOTAL sẽ giúp ngăn chặn tình trạng tính hai lần với các công thức SUM đơn giản.
– Các ô trong bất kỳ đối số dải_ô nào được chỉ định mà có chứa các lệnh gọi SUBTOTAL sẽ được bỏ qua để tránh tình trạng tính hai lần.
6. Một số bài tập ví dụ về kết hợp hàm SUBTOTAL và hàm VLOOKUP
Video hướng dẫn một số bài tập ví dụ về kết hợp hàm SUBTOTAL và hàm VLOOKUP.
Cho bảng dữ liệu như trong hình:
Bảng dữ liệu bài tập mẫu
Câu 1: Tính điểm trung bình môn kế toán của những sinh viên do thầy Lê Hoành F hướng dẫn.
=SUBTOTAL(1; VLOOKUP(G13;A2:E14;4;FALSE); VLOOKUP(G14;A2:E14;4;FALSE))
Lời giải câu 1
Câu 2: Tìm sinh viên có điểm toán thống kê cao nhất do cô Nguyễn A phụ trách.
=SUBTOTAL(4; VLOOKUP(G2;A2:E14;3;FALSE); VLOOKUP(G3;A2:E14;3;FALSE); VLOOKUP(G4;A2:E14;3;FALSE))
Lời giải câu 2
7. Những câu hỏi thường gặp khi kết hợp hàm SUBTOTAL và hàm VLOOKUP
Hàm SUBTOTAL còn kết hợp với hàm nào?
Trả lời: Ngoài hàm VLOOKUP, hàm SUBTOTAL còn có thể kết hợp với nhiều hàm khác như: LEFT/RIGHT, INDEX, IF, HLOOKUP,… Tùy theo từng mục đích mà lựa chọn dùng hàm kết hợp phù hợp.
Lợi ích khi kết hợp hàm SUBTOTAL và VLOOKUP?
Trả lời: Giúp bạn ngăn chặn việc tính 2 lần với những ô có dùng các công thức SUM đơn giản, nhờ vậy mà kết quả tính ra chính xác hơn. Ngoài ra, việc kết hợp này cũng giúp bạn chọn được dữ liệu dễ dàng và linh hoạt hơn trong việc tính toán mà không cần phải gọi quá nhiều hàm phức tạp.
Một số câu hỏi thường gặp
Xem thêm:
Phần hướng dẫn cách kết hợp hàm SUBTOTAL với VLOOKUP trong Google Sheet đã xong rồi, hy vọng bài viết sẽ hữu ích với bạn và cảm ơn bạn đã theo dõi hết bài nhé!
