[Video] Cách kết hợp hàm INDEX và hàm COUNTIF trong Google Sheet

Hàm INDEX giúp lấy giá trị tại một ô nào đó, hàm COUNTIF được sử dụng để đếm số lần lặp lại của một giá trị. Vậy khi kết hợp hàm INDEX và hàm COUNTIF trong Google Sheet sẽ có thể ứng dụng như thế nào? Cùng theo dõi và mở laptop làm theo ví dụ minh họa để có câu trả lời nhé!

1. Cách sử dụng hàm INDEX trong Google Sheet

Hàm INDEX là gì?

Hàm INDEX là hàm trả về mảng, giúp lấy các giá trị tại một ô nào đó giao giữa cột và dòng.

Cách sử dụng hàm INDEX

Công thức:

=INDEX(Array;Row_num;[Column_num])

Trong đó:

– Array: Là vùng ô hoặc một hàng số mảng nào đó bắt buộc.

– Row_num: Là chọn dòng trong mảng từ đó trả về một giá trị.

– Column_num: Là chọn cột trong mảng từ đó trả về một giá trị.

Ví dụ minh họa: Cho bảng dữ liệu Tính lương nhân viên. Dùng hàm INDEX lấy giá trị “Trần Cao Kiến” (dòng 2, cột 2) trong bảng.

Công thức:

=INDEX(A3:F7;2;2)

Hàm INDEX lấy các giá trị tại một ô nào đó giao giữa cột và dòng

Để 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 INDEX trong Google Sheet.

2. Cách sử dụng hàm COUNTIF trong Google Sheet- Hàm COUNTIF là gì?

Hàm COUNTIF là gì?

COUNTIF là hàm đếm có điều kiện trong Google Sheet, được sử dụng để đếm số lần lặp lại của một giá trị dựa vào điều kiện đã cho trước.

Cách sử dụng hàm COUNTIF

Công thức:

=COUNTIF(range;criteria)

Trong đó:

– Range: Là vùng dữ liệu mà bạn muốn đếm, vùng dữ liệu này có thể là kiểu văn bản, kiểu số hay kiểu ngày tháng (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 ô.

Ví dụ minh họa: Dùng hàm COUNTIF đếm số lần sản phẩm bán ra đạt số lượng lớn hơn 500.

Công thức:

=COUNTIF(A1:C7;”>500″)

Dùng hàm COUNTIF đếm số lần sản phẩm bán ra đạt số lượng lớn hơn 500 sản phẩm

Để 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.

3. Cách kết hợp hàm INDEX và hàm COUNTIF trong Google Sheet

Video hướng dẫn cách kết hợp hàm INDEX và hàm COUNTIF trong Google Sheet.

Công thức:

=COUNTIF(range;INDEX(Array;Row_num;[Column_num]))

Giới thiệu các giá trị:

– Range: Vùng dữ liệu mà bạn muốn đếm (Ví dụ: “Sheet1!A1:B5“).

– INDEX(Array;Row_num;[Column_num]): Là criteria, điều kiện để đếm dữ liệu trong vùng dữ liệu đã chọn.

Ví dụ minh họa: Dùng hàm INDEX, hàm COUNTIF và hàm MATCH tính tổng số lượng đơn hàng tai nghe bị hủy.

Công thức:

=COUNTIF(B2:B9;INDEX(G3:G5;MATCH(“Tai nghe”;F3:F5;0);1))

Tính tổng số lượng đơn hàng tai nghe bị hủy

4. Các lỗi thường gặp khi kết hợp hàm INDEX và hàm COUNTIF

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, công thức bạn sử dụng đang chứa đối tượng cần dò tìm, tham chiếu không có sẵn trong vùng dữ liệu cần tra cứu.

Ví dụ: Do dư dấu chấm phẩy (;) nên hàm COUNTIF sau bị dư 1 đối số dẫn tới lỗi #NA.

Cách khắc phục: Bỏ dấu chấm phẩy (;) thừa đi thì để xử lý lỗi #N/A.

Lỗi #NA do dư một dấu chấm phẩy

Lỗi #REF

Lỗi #REF! hiển thị khi công thức tham chiếu đến ô không hợp lệ. Điều này thường xuyên xảy ra khi các ô được truy xuất bị nhập nhầm.

Ví dụ: Công thức sau nhập sai vùng dữ liệu đếm dẫn đến lỗi quay vòng #REF!.

Cách khắc phục: Khắc phục bằng cách sửa vùng dữ liệu muốn lấy dữ liệu thành “B2:B9”.

Lỗi #REF do nhập sai vùng dữ liệu đếm

Lỗi #ERROR

Lỗi này có nghĩa là Google Sheet không hiểu công thức bạn đã nhập vì nó không thể phân tích cú pháp công thức để thực thi.

Ví dụ: Hàm dưới nhập thiếu dấu nháy (“) ở giá trị Tai nghe dẫn đến lỗi không thể phân tích cú pháp công thức #ERROR.

Cách khắc phục: Để khắc phục cần thêm đủ dấu nháy trước giá trị văn bản thành “Tai nghe”.

Lỗi #ERROR do nhập thiếu dấu nháy

Lỗi #VALUE

Lỗi #VALUE là lỗi xảy ra lỗi với cách nhập công thức của bạn. Hoặc đã xảy ra lỗi với các ô bạn đang tham chiếu tới.

Ví dụ: Công thức sau thiếu giá trị row_num tại vị trí hàm INDEX dẫn đến lỗi #VALUE.

Cách khắc phục: Thêm giá trị row_num cho hàm INDEX để khắc phục lỗi.

Lỗi #VALUE do nhập thiếu đối số thứ hai

5. Một số lưu ý khi kết hợp hàm INDEX và hàm COUNTIF

– Khi viết hàm không phân biệt chữ hoa với chữ thường. Ví dụ: index, counif.

– Lưu ý các dữ liệu cần đầy đủ dấu “”,() nếu không sẽ gây ra lỗi hàm.

Những lưu ý khi kết hợp hàm INDEX và hàm COUNTIF

6. Một số bài tập ví dụ về kết hợp hàm INDEX và hàm COUNTIF

Video hướng dẫn một số bài tập ví dụ về kết hợp hàm INDEX và hàm COUNTIF.

Câu 1: Đếm số lần sinh viên đạt điểm cao nhất của tháng 6 đã thi trong năm 2020.

Công thức:

=COUNTIF(B2:B52;INDEX(F2:I7;MATCH(1;I2:I7;0);1))

Giải thích: Dùng hàm MATCH để dò vị trí ô tên sinh viên đạt số điểm cao nhất, lồng hàm INDEX vào hàm MATCH để lấy giá trị tên sinh viên đạt số điểm cao nhất. Lồng hàm COUNTIF vào hàm INDEX để đếm số lần thi thử trong năm 2020 của sinh viên đạt điểm cao nhất của tháng 6.

Số lần sinh viên đạt điểm cao nhất của tháng 6 đã thi trong năm 2020

Câu 2: Đếm số lần sinh viên Lê Anh Tú tham gia thi thử trong năm 2020.

Công thức:

=COUNTIF(C3:C52;INDEX(F3:I7;MATCH(“Lê Anh Tú”;F3:F7;0);2))

Giải thích: Dùng hàm MATCH để dò vị trí ô MSSV của Lê Anh Tú, lồng hàm INDEX vào hàm MATCH để lấy giá trị MSSV của Lê Anh Tú. Lồng hàm COUNTIF vào hàm INDEX để đếm số lần tham gia thi thử trong năm 2020 của sinh viên Lê Anh Tú.

Số lần sinh viên Lê Anh Tú tham gia thi thử trong năm 2020

7. Những câu hỏi thường gặp khi kết hợp hàm INDEX và hàm COUNTIF

Hàm INDEX còn kết hợp được hàm nào?

Hàm INDEX không chỉ trả về các giá trị, thực tế là nó trả về một tham chiếu đến ô có chứa giá trị. Do đó ngoài kết hợp với COUNTIF, hàm INDEX còn có thể kết hợp với các hàm SUM, AVERAGE, MAX, MIN,…

Câu hỏi thường gặp khi kết hợp hàm INDEX và hàm COUNTIF

Lợi ích khi kết hợp hàm COUNTIF với INDEX?

Hàm INDEX là hàm trả về mảng, giúp người dùng lấy các giá trị tại một ô nào đó giao giữa cột và dòng. Khi kết hợp hàm COUNTIF với INDEX người dùng có thể vận dụng rất nhiều trong thống kê, đếm số lượng số ô đáp ứng các tiêu chí mà hàm INDEX đã trả về.

Xem thêm: