Hàm COUNTIF là một trong những hàm dùng để thống kê, đếm số lượng ô đáp ứng điều kiện nào đó. Hàm VLOOKUP là hàm hỗ trợ tìm kiếm dữ liệu trong 1 bảng cho trước. Việc kết hợp hàm COUNTIF với hàm VLOOKUP trong Google Sheet như thế nào? Cùng xem ngay nhé!
Sau đây là video hướng dẫn bạn cách sử dụng hàm COUNTIF kết hợp hàm VLOOKUP cực dễ hiểu, xem ngay nhé:
[Video] Cách kết hợp hàm COUNTIF với hàm VLOOKUP trong Google Sheet
1. Công thức hàm COUNTIF
– Công thức:
=COUNTIF(range;criteria)
– Trong đó:
+ Range: Là vùng dữ liệu mà bạn muốn đếm bắt buộc phải có (ví dụ: “Sheet1!A1:B5”).
+ Criteria: Là điều kiện để đếm dữ liệu trong vùng dữ liệu đã chọn. Có thể là kiểu văn bản, kiểu số, biểu thức hay tham chiếu ô.
Để biết cách sử dụng hàm chi tiết bạn nên tham khảo bài viết Hàm COUNTIF trong Google Sheet ngay nhé!
2. Công thức hàm VLOOKUP
– Công thức:
=VLOOKUP(lookup_value;table_array;col_index_num;[range_lookup])
– Trong đó:
+ lookup_value: (Bắt buộc). Giá trị dùng để dò tìm.
+ table_array: (Bắt buộc). Bảng chứa giá trị cần dò tìm.
+ col_index_num: (Bắt buộc). Thứ tự chính của cột chứa các giá trị dò tìm trên table_array.
+ [range_lookup]: (Không bắt buộc). Là phạm vi mà dữ liệu tìm kiếm, TRUE tương đương với 1 (dò tìm tương đối), FALSE tương đương với 0 (dò tìm tuyệt đối)
Để biết cách sử dụng hàm chi tiết bạn nên tham khảo bài viết Hàm VLOOKUP trong Google Sheet ngay nào!
3. Cách kết hợp hàm COUNTIF và hàm VLOOKUP trong Google Sheet
Cho bảng sau:
Bảng dữ liệu gồm 2 trang tính TOP PAGE và KEYWORD CATEGORY
Bảng gồm hai trang tính, trang tính 1 (“TOP PAGE”) bao gồm thông tin traffic của trang Hỏi đáp , dữ liệu gồm các trường: Top Keyword, URL, Its Volume, Position, Page URL inside, Traffic value, trang tính 2 (“KEYWORD CATEGORY”) bao gồm thông tin hệ bài viết, gồm các trường: URL, Keyword Category.
Dựa vào dữ liệu bảng trên, hãy đếm xem có bao nhiêu Bài viết nằm trong hệ Máy tính.
Bước 1: Dùng hàm VLOOKUP dò mỗi URL nằm trong hệ nào
– Công thức:
=ArrayFormula(VLOOKUP(D2:D;’KEYWORD CATEGORY’!$A$2:$B$989;2;0))
– Giải thích:
Hàm VLOOKUP sẽ tìm giá trị “URL” cho sẵn ở ô D2 và dò xem trong vùng dữ liệu (A2:B989) của sheet Keyword Category có URL nào khớp không. Nếu khớp sẽ cho ra kết quả hệ bài viết tương ứng, ngược lại sẽ cho kết quả #N/A (ý không tìm thấy kết quả phù hợp).
Hàm ArrayFormula sẽ hỗ trợ sao chép công thức VLOOKUP vào các ô bên dưới, không phải thao tác kéo thủ công.
Lưu ý: Để vùng dữ liệu tham chiếu được cố định, nên dùng công thức cố định vùng bằng cách, bôi đen (A2:B989) và nhấn phím F4.
Dùng hàm VLOOKUP dò mỗi URL nằm trong hệ nào
Bước 2: Dùng hàm COUNTIF để đếm chính xác có bao nhiêu link bài viết nằm trong hệ Máy tính
– Công thức:
=COUNTIF(C:C;”Máy tính”)
– Giải thích:
Hàm COUNTIF lúc này đếm xem trong cột Keyword Category có bao nhiêu “Máy tính” và trả về kết quả tương đương với số lượng bài viết nằm trong hệ Máy tính.
Dùng hàm COUNTIF để đếm chính xác có bao nhiêu link bài viết nằm trong hệ Máy tính
Tuy nhiên, có thể kết hợp hàm COUNTIF và hàm VLOOKUP với nhau cùng một lúc mà bạn không phải tốn nhiều bước như trên.
– Công thức:
=ArrayFormula(COUNTIF(VLOOKUP(C:C;’KEYWORD CATEGORY’!$A$2:$B$989;2;0);”Máy tính”))
– Giải thích:
Tương tự, hàm VLOOKUP dùng thuộc tính URL chung giữa 2 sheet để lấy dữ liệu cột Keyword Category bên sheet KEYWORD CATEGORY sang sheet TOP PAGE. Hàm ArrayFormula lúc này hỗ trợ hàm VLOOKUP xuất ra giá trị dạng mảng, và hàm COUNTIF sẽ đếm xem trong vùng dữ liệu vừa lấy có bao nhiêu “Máy tính”.
Hàm COUNTIF kết hợp với hàm VLOOKUP với nhau cùng một lúc
4. Các lỗi thường gặp khi kết hợp hàm COUNTIF và hàm VLOOKUP
– Lỗi #N/A
Lỗi #N/A xuất hiện là do hàm COUNTIF quy định cú pháp gồm 2 đối số, tuy nhiên công thức lúc này đang bị dư đối số thứ 3. Như hình dưới đây là đang dư điều kiện so sánh là “Mạng xã hội”.
Lỗi #N/A xuất hiện là do hàm COUNTIF dư đối số
Cách khắc phục là bạn nhập đúng lượng đối số của hàm COUNTIF (chỉ gồm 2 đối số).
– Lỗi #ERROR!
Lỗi #ERROR xuất hiện khi bạn nhập không đúng cú pháp công thức, thường là sẽ quên dấu ” “. Như hình dưới đây là điều kiện text đang không nằm trong cặp dấu ” “. Dấu hiệu nhận biết là màu dữ liệu sẽ chuyển sang màu đen.
Lỗi #ERROR xuất hiện khi bạn nhập không đúng cú pháp công thức
Cách khắc phục là bạn nên lưu ý kỹ các dấu nháy kép ” ” bắt buộc dùng cho các điều kiện text và màu dữ liệu là màu xanh thì công thức đúng.
– Lỗi #NAME?
Lỗi #NAME? xuất hiện do bạn đã nhập sai tên hàm hoặc vùng dữ liệu. Ở đây hàm ArrayFomular hiện đang sai tên, nguyên nhân có thể do bạn nhập hàm bằng tay và theo thói quen phát âm mà viết sai tên hàm.
Lỗi #NAME? xuất hiện do bạn đã nhập sai tên hàm
Cách khắc phục là hãy sử dụng phím tắt để sử dụng nhanh hàm ArrayFomula bằng nhấn tổ hợp phím Ctrl + Shift + Enter.
5. Một số lưu ý khi kết hợp hàm COUNTIF và hàm VLOOKUP
– Khi sử dụng hàm COUNTIF kết hợp với hàm VLOOKUP thì bạn phải lưu ý các dấu đóng ngoặc ). Nếu không cẩn thận sẽ gây ra lỗi hàm.
– Khi thực hiện đổ dữ liệu từ bảng dữ liệu này sang bảng khác bằng hàm VLOOKUP, cần cố định vùng tham chiếu bằng phím F4. Chú ý vùng tham chiếu đến sheet khác có dạng ‘KEYWORD CATEGORY’!$A$2:$B$989
– Hàm COUNTIF và VLOOKUP đều không phân biệt chữ hoa-thường. Ví dụ “Máy tính” hay “MÁY TÍNH” hoặc “máy tính” thì hàm vẫn trả về đúng kết quả.
Hàm COUNTIF và VLOOKUP đều không phân biệt chữ hoa-thường
6. Một số bài tập ví dụ về kết hợp hàm COUNTIF và hàm VLOOKUP
Bạn nhấn vào bài tập hàm COUNTIF kết hợp hàm VLOOKUP để thực hành các hàm theo yêu cầu dưới đây nhé!
Cho bảng như hình sau:
Danh sách nhân viên của một công ty
Đề 1: Có bao nhiêu người có trình độ Đại học?
Trả lời: =ArrayFormula(COUNTIF(VLOOKUP(A2:A8;$A$12:$C$18;3;0);”Đại học”))
Giải thích: Tại ô D2, hàm VLOOKUP lấy ra dữ liệu cột Trình độ từ Bảng 2, hàm COUNTIF tiếp tục dò tìm trong vùng dữ liệu vừa lấy ra và đếm được có 3 người có trình độ Đại học.
Có 3 người có trình độ Đại học
Đề 2: Có bao nhiêu người có quê quán không phải ở Hồ Chí Minh?
Trả lời: =ArrayFormula(COUNTIF(VLOOKUP(A2:A8;$A$12:$C$18;2;0);”Hồ Chí Minh”))
Giải thích: Tương tự, hàm VLOOKUP cũng lấy ra dữ liệu cột Quê quán từ Bảng 2, hàm COUNTIF tiếp tục dò tìm trong vùng dữ liệu vừa lấy ra và đếm được có 6 người ở khu vực khác Hồ Chí Minh.
Có 6 người có quê quán không phải ở Hồ Chí Minh
7. Câu hỏi thường gặp khi kết hợp hàm COUNTIF và hàm VLOOKUP
Câu hỏi 1: Mình đặt lệnh =ArrayFormula(COUNTIF(vlookup(D:D,’KEYWORD CATEGORY’!$A$2:$B$989,2,0),”máy tính”)). Báo lỗi #ERROR! không phân tích được cú pháp công thức.
Trả lời: Nếu bạn đang sử dụng dấu , thì bị lỗi. Đổi sang dấu ; rồi kiểm tra lại xem nhé!
Câu hỏi 2: Cùng 1 URL, làm thế nào để lấy 1 cột dữ liệu từ bảng dữ liệu trong sheet khác vào cùng 1 sheet tổng?
Trả lời: Sử dụng hàm VLOOKUP để nối dữ liệu giữa các sheet, điều kiện là các bảng dữ liệu phải có chung khóa ngoại (trong ví dụ là 2 bảng đều có chung khóa ngoại URL), sau đó dùng công thức:
=ArrayFormula(vlookup(D2:D;’KEYWORD CATEGORY’!$A$2:$B$989;2;0))
Xem thêm:
Bài viết hướng dẫn bạn cách sử dụng hàm COUNTIF kết hợp hàm VLOOKUP thật đơn giản và dễ hiểu phải không nào. Chúc các bạn thực hành tốt bài tập của mình nhé!
