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

Bạn đang muốn đếm dữ liệu theo điều kiện nhưng không muốn nhập thủ công giá trị cần đếm? Việc kết hợp hàm INDEX và hàm COUNTIF trong Google Sheets sẽ giúp bạn xử lý dữ liệu thông minh, linh hoạt và chuyên nghiệp hơn. Bài viết này sẽ hướng dẫn các bạn chi tiết cách kết hợp hàm INDEX và hàm COUNTIF trong Google Sheets từ cơ bản đến nâng cao, cùng tham khảo ngay nhé. 

Tổng quan chung về hàm INDEX trong Google Sheets

Hàm INDEX là một trong những hàm tra cứu dữ liệu quan trọng và linh hoạt nhất trong Google Sheets. Hàm INDEX có nhiệm vụ trả về giá trị của một ô trong một vùng dữ liệu dựa trên vị trí hàng và cột mà bạn chỉ định. Nói một cách đơn giản, nếu bảng tính là một ma trận gồm nhiều hàng và cột, thì hàm INDEX chính là công cụ giúp chỉ đúng vị trí và lấy ra giá trị ở đó.

Khác với việc nhập cố định địa chỉ ô như A2 hay C5, hàm INDEX hoạt động dựa trên vị trí tương đối trong một vùng dữ liệu. Nhờ vậy, công thức sẽ không bị lỗi khi bạn chèn thêm hàng, xóa cột hoặc thay đổi cấu trúc bảng. 

Trong thực tế, hàm INDEX thường được sử dụng để:

• Truy xuất dữ liệu theo vị trí cụ thể.
• Kết hợp với MATCH để tạo công thức tra cứu nâng cao.
• Làm nền tảng khi xây dựng công thức động.
• Hỗ trợ xây dựng các công thức nâng cao như cách kết hợp hàm INDEX và hàm COUNTIF trong Google Sheets để lọc và trả về dữ liệu theo điều kiện.

Cú pháp và các tham số của hàm INDEX

Để sử dụng hiệu quả, bạn cần hiểu rõ cấu trúc của hàm INDEX như sau:

=INDEX(array, row_num, [column_num])

Trong đó:

• array: Là vùng dữ liệu (mảng) mà bạn muốn truy xuất thông tin. Ví dụ: A2:C10.
• row_num: Là số thứ tự hàng trong vùng dữ liệu, được tính từ trên xuống.
• column_num (tùy chọn): Là số thứ tự cột trong vùng dữ liệu, được tính từ trái sang phải.

Hướng dẫn dùng INDEX qua ví dụ thực tế

Giả sử bạn có một bảng danh sách nhân viên và bạn muốn lấy mức lương của một nhân viên dựa trên vị trí trong bảng.

*Yêu cầu: Bạn muốn lấy mức lương của nhân viên ở hàng thứ 3 trong vùng dữ liệu.

Công thức:

=INDEX(A2:C5;3;3)

Trong đó:

• A2:C5 là vùng dữ liệu chứa thông tin nhân viên.
• Số 3 đầu tiên đại diện cho hàng thứ 3 trong vùng, tức là dòng chứa nhân viên NV03.
• Số 3 thứ hai đại diện cho cột thứ 3 trong vùng, tức là cột Lương.

Kết quả:

Tổng quan chung về hàm COUNTIF trong Google Sheets

Nếu hàm INDEX có nhiệm vụ truy xuất dữ liệu theo vị trí, thì hàm COUNTIF lại có nhiệm vụ đếm số lượng ô thỏa mãn một điều kiện cụ thể trong một vùng dữ liệu. Hàm COUNTIF xuất hiện trong hầu hết các báo cáo thống kê như báo cáo doanh thu, quản lý nhân sự, kiểm kê hàng hóa hoặc theo dõi trạng thái công việc. Nhờ khả năng đếm có điều kiện, hàm COUNTIF giúp người dùng nhanh chóng tổng hợp dữ liệu mà không cần lọc thủ công.

Về bản chất, COUNTIF hoạt động như một bộ lọc đếm. Hàm sẽ quét toàn bộ vùng dữ liệu mà bạn chỉ định, sau đó đếm những ô đáp ứng đúng tiêu chí mà bạn đưa ra. Điều kiện có thể là:

• Số (ví dụ: >10, <100, =50)
• Văn bản (ví dụ: “Hoàn thành”, “Táo”)
• Ký tự đại diện như dấu sao (*) hoặc dấu hỏi (?)
• Tham chiếu đến một ô khác

Đối với người mới bắt đầu, bạn cần lưu ý một số điểm quan trọng:

• Điều kiện dạng văn bản phải đặt trong dấu ngoặc kép.
• Điều kiện so sánh như >50 cũng phải đặt trong dấu ngoặc kép.
• Hàm COUNTIF không xét đến việc viết hoa hay viết thường.

Cú pháp và các tham số của hàm COUNTIF

Để áp dụng đúng công thức, bạn cần hiểu chính xác cấu trúc của hàm COUNTIF như sau:

=COUNTIF(range, criterion)

Trong đó:

• range: Là khu vực ô chứa dữ liệu được dùng để đếm.Ví dụ: A2:A20.
• criterion: Là điều kiện để một ô được tính vào kết quả.

Hướng dẫn dùng COUNTIF qua ví dụ thực tế

Giả sử bạn có một bảng danh sách đơn hàng và bạn muốn đếm xem có bao nhiêu đơn hàng đã hoàn thành.

*Yêu cầu: Bạn cần đếm số lượng đơn hàng có trạng thái là “Hoàn thành”.

Công thức:

=COUNTIF(B2:B6;”Hoàn thành”)

Trong đó:

• B2:B6 là vùng dữ liệu mà hàm sẽ kiểm tra.
• “Hoàn thành” là điều kiện để đếm.

Kết quả:

Lợi ích khi kết hợp hàm INDEX và hàm COUNTIF trong Google Sheets

Trong thực tế làm việc, dữ liệu thường xuyên thay đổi. Bảng tính có thể thêm cột, thêm hàng hoặc thay đổi vị trí trường dữ liệu. Nếu bạn chỉ sử dụng hàm COUNTIF với điều kiện cố định, mỗi lần cấu trúc bảng thay đổi, bạn sẽ phải chỉnh sửa lại công thức. Khi bạn hiểu cách kết hợp hàm INDEX và hàm COUNTIF trong Google Sheets, bạn sẽ xây dựng được công thức linh hoạt, tự động thích nghi với dữ liệu mà không cần sửa thủ công.

Dưới đây là một số lợi ích nổi bật mà khi kết hợp hàm INDEX và hàm COUNTIF trong Google Sheets, người dùng sẽ nhận được.

• Xây dựng báo cáo động, dễ tùy chỉnh

Khi bạn sử dụng hàm INDEX để xác định vùng hoặc điều kiện cần đếm, hàm COUNTIF sẽ thực hiện phần thống kê dựa trên dữ liệu mà hàm INDEX cung cấp. Do đó, bạn không cần nhập điều kiện cố định, công thức có thể thay đổi theo lựa chọn của người dùng, báo cáo có thể tự động cập nhật khi dữ liệu nguồn thay đổi. Lúc này, hàm INDEX sẽ xác định đúng cột của tháng được chọn và hàm COUNTIF sẽ đếm số đơn hàng đạt chỉ tiêu trong tháng đó. 

• Giảm sai sót khi nhập điều kiện thủ công

Khi bạn nhập điều kiện trực tiếp vào hàm COUNTIF, nguy cơ nhập sai chính tả hoặc sai định dạng rất cao. Sai sót nhỏ cũng có thể khiến kết quả thống kê không chính xác. Khi bạn áp dụng cách kết hợp hàm INDEX và hàm COUNTIF trong Google Sheets, điều kiện đếm sẽ được lấy trực tiếp từ một bảng dữ liệu chuẩn. Nhờ vậy, bạn giảm thiểu lỗi nhập liệu, công thức trở nên nhất quán và dữ liệu thống kê đáng tin cậy hơn.

• Xử lý hiệu quả vùng dữ liệu biến đổi

Trong nhiều trường hợp, vùng dữ liệu cần đếm không cố định. Ví dụ, bạn cần đếm số đơn hàng theo từng tháng, đếm số nhân viên theo từng phòng ban. Nếu vùng dữ liệu thay đổi theo lựa chọn của người dùng, hàm COUNTIF sẽ không đủ linh hoạt. Khi đó, hàm INDEX sẽ đóng vai trò xác định đúng vùng hoặc đúng giá trị cần đếm.

• Tăng tính chuyên nghiệp cho Dashboard và báo cáo

Khi bạn xây dựng Dashboard, bạn thường muốn người dùng chỉ cần chọn một tiêu chí, và toàn bộ số liệu sẽ tự động cập nhật. Khi bạn hiểu rõ cách kết hợp hàm INDEX và hàm COUNTIF trong Google Sheets, bạn có thể nâng cao kỹ năng phân tích, tăng tốc độ xử lý công việc,…

Khi nào nên kết hợp hàm INDEX và hàm COUNTIF trong Google Sheets?

Thực tế, nếu bạn chỉ cần đếm dữ liệu đơn giản và điều kiện luôn cố định, thì hàm COUNTIF là đủ. Tuy nhiên, khi dữ liệu thay đổi theo vị trí, hoặc nằm ở nhiều bảng khác nhau, việc chỉ dùng hàm COUNTIF sẽ hạn chế. Lúc này, bạn nên áp dụng cách kết hợp hàm INDEX và hàm COUNTIF trong Google Sheets để công thức xử lý linh hoạt bài toán. 

Dưới đây là những trường hợp phổ biến mà bạn nên sử dụng hàm INDEX và COUNTIF.

• Khi điều kiện đếm thay đổi theo lựa chọn của người dùng

Trong các báo cáo động hoặc dashboard, người dùng thường chọn tiêu chí từ dropdown như tháng, sản phẩm hoặc kỳ thi. Lúc này, hàm INDEX sẽ xác định đúng cột hoặc giá trị tương ứng với lựa chọn. Hàm COUNTIF sẽ đếm dữ liệu thỏa điều kiện trong vùng đó. Cách làm này giúp bạn tránh phải viết nhiều hàm IF lồng nhau và giúp bảng tính dễ quản lý hơn.

• Khi vùng dữ liệu cần đếm thay đổi

Nếu dữ liệu liên tục mở rộng theo tháng, theo tuần hoặc theo giai đoạn, việc cố định vùng đếm bằng COUNTIF sẽ khiến bạn phải sửa công thức nhiều lần. Khi kết hợp hàm INDEX và hàm COUNTIF trong Google Sheets, hàm INDEX sẽ tự động xác định đúng vùng hoặc cột cần đếm. Nhờ vậy, công thức vẫn hoạt động chính xác ngay cả khi bạn thêm dữ liệu mới.

• Khi điều kiện đếm nằm ở bảng khác

Trong nhiều trường hợp, điều kiện không nằm trực tiếp trong bảng cần đếm. Ví dụ, bảng A chứa giá chuẩn sản phẩm, bảng B chứa dữ liệu bán hàng. Bạn có thể dùng hàm INDEX để lấy giá từ bảng A, sau đó dùng hàm COUNTIF để đếm số giao dịch tương ứng trong bảng B. Cách này giúp giảm sai sót khi nhập điều kiện thủ công.

• Khi cần kiểm tra dữ liệu trùng lặp theo khu vực

Nếu bạn cần kiểm tra mã nhân viên hoặc mã sản phẩm có bị trùng trong một khu vực cụ thể. Lúc này, bạn có thể dùng hàm INDEX để xác định đúng vùng dữ liệu cần kiểm tra, dùng hàm COUNTIF để đếm số lần xuất hiện. Nếu kết quả lớn hơn 1, bạn sẽ phát hiện trùng lặp ngay lập tức.

• Khi xây dựng dashboard và báo cáo tự động

Trong các hệ thống báo cáo chuyên nghiệp, điều kiện đếm thường phụ thuộc vào lựa chọn như tháng, phòng ban hoặc sản phẩm. Khi đó, cách kết hợp hàm INDEX và hàm COUNTIF trong Google Sheets sẽ giúp bạn tạo báo cáo tự động. Người dùng chỉ cần chọn tiêu chí, còn công thức sẽ tự động xử lý dữ liệu phía sau.

Cú pháp kết hợp hàm INDEX và hàm COUNTIF trong Google Sheets

Về bản chất, khi thực hiện kết hợp hàm INDEX và hàm COUNTIF trong Google Sheets, bạn sẽ đặt hàm INDEX vào vị trí đối số điều kiện của COUNTIF. Khi đó, INDEX sẽ trả về một giá trị cụ thể và COUNTIF sẽ dùng giá trị đó làm tiêu chí để đếm.

Công thức cơ bản của hàm INDEX và hàm COUNTIF kết hợp với nhau trong Google Sheets như sau:

=COUNTIF(dãy_ô, INDEX(vùng_dữ_liệu, hàng, [cột]))

Trong đó:

• dãy_ô: là vùng dữ liệu mà bạn muốn thực hiện phép đếm. COUNTIF sẽ quét toàn bộ dãy ô này và kiểm tra từng giá trị có thỏa điều kiện hay không.
• INDEX(vùng_dữ_liệu, hàng, [cột]): Hàm INDEX sẽ trả về một giá trị tại vị trí xác định trong một bảng dữ liệu khác.
  • vùng_dữ_liệu là bảng chứa giá trị bạn muốn lấy làm điều kiện.
  • hàng là số thứ tự dòng trong vùng đó.
  • cột là số thứ tự cột trong vùng (có thể bỏ qua nếu là mảng một chiều).

Giá trị mà INDEX trả về sẽ trở thành điều kiện đếm cho COUNTIF.

Cách kết hợp hàm INDEX và hàm COUNTIF trong Google Sheets| Ví dụ cơ bản

Để hiểu rõ hơn về cách sử dụng hàm INDEX và hàm COUNTIF kết hợp với nhau trong Google Sheets, bạn có thể tham khảo nội dung sau đây:

Ví dụ 1 – Đếm giá trị dựa trên vị trí hàng và cột cố định

Ví dụ, bạn có hai bảng dữ liệu như sau:

Bảng 1: Danh sách sản phẩm chuẩn

Bảng 2: Nhật ký bán hàng

*Yêu cầu: Bạn cần đếm số lần sản phẩm ở hàng thứ 1, cột thứ 2 của Bảng 1 xuất hiện trong bảng bán hàng.

Công thức:

=COUNTIF(D2:D7; INDEX(A2:B5;1;2))

Trong đó:

• INDEX(A2:B5;1;2) sẽ trả về giá trị ở hàng 1, cột 2 của bảng sản phẩm, tức là “Táo”.
• COUNTIF(D2:D7, …) sẽ đếm số lần “Táo” xuất hiện trong bảng bán hàng.

Kết quả:

Ví dụ 2 – Sử dụng INDEX để đếm dữ liệu theo điều kiện động

Ví dụ, bạn có bảng dữ liệu cơ bản như sau:

Ô F1 chứa số cột tháng được chọn (ví dụ: nhập 2 tương ứng Tháng 2).

*Yêu cầu: Đếm số nhân viên có điểm ≥ 8 trong tháng được chọn tại ô F1.

Công thức:

=COUNTIF(INDEX(B2:D5;0;F1);”>=8″)

Trong đó:

• INDEX(B2:D5;0;F1) sẽ trả về toàn bộ cột tương ứng với tháng được chọn. Nếu F1 = 2, INDEX sẽ trả về cột Tháng 2.
• COUNTIF(…;”>=8″) sẽ đếm số giá trị lớn hơn hoặc bằng 8 trong cột đó.

Kết quả:

Ví dụ 3 – Kiểm tra tính duy nhất hoặc trùng lặp của một phần tử bất kỳ

Ví dụ, bạn có bảng dữ liệu như sau:

Ô C1 chứa vị trí hàng cần kiểm tra (ví dụ: 2).

*Yêu cầu: Kiểm tra xem mã nhân viên ở vị trí hàng được chọn có bị trùng trong danh sách hay không.

Công thức:

=COUNTIF(A2:A8; INDEX(A2:A8;C1))

Trong đó:

• INDEX(A2:A8;C1) sẽ lấy mã nhân viên tại vị trí được chỉ định.
• COUNTIF sẽ đếm số lần mã đó xuất hiện trong toàn bộ danh sách.
• Nếu kết quả lớn hơn 1, mã đó bị trùng.

Kết quả:

Lỗi và cách khắc phục khi kết hợp hàm INDEX và hàm COUNTIF trong Google Sheets

Khi bạn thực hiện kết hợp hàm INDEX  và hàm COUNTIF trong Google Sheets, việc xuất hiện lỗi là điều rất phổ biến. Nguyên nhân thường đến từ việc nhập sai cú pháp, tham chiếu nhầm vùng dữ liệu hoặc thiếu đối số trong công thức.

Lỗi #N/A

Lỗi #N/A thường xuất hiện khi công thức không tìm thấy dữ liệu phù hợp với điều kiện mà bạn yêu cầu. Khi bạn sử dụng hàm INDEX để lấy giá trị làm điều kiện cho hàm COUNTIF, nếu giá trị đó không tồn tại trong vùng đếm, Google Sheets sẽ trả về lỗi #N/A.

Nguyên nhân phổ biến:

• Bạn nhập dư dấu chấm phẩy (;) hoặc dấu phẩy (,) khiến COUNTIF có thêm một đối số không hợp lệ.
• Giá trị mà hàm INDEX trả về không tồn tại trong vùng dữ liệu mà COUNTIF đang đếm.
• Vùng tham chiếu của INDEX hoặc COUNTIF không đúng.

Cách khắc phục như sau:

• Kiểm tra xem hàm COUNTIF có đúng 2 đối số hay không.
• Kiểm tra xem hàm INDEX có trả về giá trị hợp lệ hay không.
• Xóa dấu phân cách dư thừa trong công thức.

Lỗi #REF

Lỗi #REF! xuất hiện khi công thức tham chiếu đến một ô hoặc một vùng dữ liệu không hợp lệ. Lỗi này thường xảy ra khi bạn xóa cột, xóa hàng hoặc nhập sai phạm vi dữ liệu. Khi thực hiện kết hợp hàm INDEX và hàm COUNTIF trong Google Sheets, lỗi này thường liên quan trực tiếp đến đối số row_num hoặc column_num trong hàm INDEX.

Nguyên nhân phổ biến:

• Bạn nhập sai vùng dữ liệu đếm trong COUNTIF.
• Bạn chỉ định row_num hoặc column_num vượt quá phạm vi bảng dữ liệu.
• Bạn đã loại bỏ vùng dữ liệu mà công thức đang sử dụng.

Cách khắc phục như sau:

• Kiểm tra số hàng và số cột thực tế của bảng dữ liệu.
• Đảm bảo row_num và column_num không vượt quá giới hạn bảng.
• Kiểm tra lại vùng dữ liệu trong hàm COUNTIF để chắc chắn rằng vùng đó tồn tại.

Lỗi #ERROR

Lỗi #ERROR thường xuất hiện khi Google Sheets không thể phân tích cú pháp công thức. Điều này có nghĩa là công thức bạn nhập không đúng định dạng. Khi áp dụng cách kết hợp hàm INDEX và hàm COUNTIF trong Google Sheets, lỗi này thường đến từ việc thiếu dấu ngoặc, thiếu dấu nháy kép hoặc sai ký hiệu phân cách.

Nguyên nhân phổ biến:

• Thiếu dấu nháy kép khi nhập điều kiện văn bản.
• Thiếu dấu đóng ngoặc.
• Sử dụng sai dấu phân cách đối số.

Cách khắc phục như sau:

• Đặt nội dung văn bản nằm trong dấu ngoặc kép
• Kiểm tra số lượng dấu ngoặc mở và đóng có cân bằng hay không.
• Kiểm tra lại dấu phân cách đối số theo cài đặt ngôn ngữ của Google Sheets.

Lỗi #VALUE

Lỗi #VALUE xảy ra khi công thức nhận đối số không hợp lệ về kiểu dữ liệu hoặc thiếu đối số bắt buộc. Trong quá trình kết hợp hàm INDEX và hàm COUNTIF trong Google Sheets, lỗi này thường liên quan đến việc nhập thiếu row_num hoặc column_num.

Nguyên nhân phổ biến: 

• Thiếu đối số row_num trong hàm INDEX.
• Sử dụng văn bản thay cho số ở vị trí yêu cầu số thứ tự hàng hoặc cột.
• Vùng dữ liệu không phù hợp với cấu trúc hàm INDEX.

Cách khắc phục như sau:

• Bổ sung đầy đủ đối số cho hàm INDEX.
• Đảm bảo row_num và column_num là số hợp lệ.

Lưu ý quan trọng khi kết hợp hàm INDEX và hàm COUNTIF trong Google Sheets

Khi kết hợp hàm INDEX và hàm COUNTIF trong Google Sheets, các bạn cần lưu ý một số vấn đề như sau:

• Thứ nhất là đồng nhất về định dạng dữ liệu

Hàm COUNTIF rất nhạy cảm về định dạng. Do đó, nếu hàm INDEX trả về kết quả là một chuỗi văn bản nhưng vùng dữ liệu cần đếm lại đang ở dạng số, hoặc kết quả trả về sẽ luôn bằng 0. Bạn cần đảm bảo giá trị trích xuất từ hàm INDEX và vùng quét của hàm COUNTIF có cùng kiểu dữ liệu.

• Thứ hai là lưu ý về ký tự khoảng trắng

Nếu ô mà hàm INDEX trích xuất có chứa khoảng trắng thừa (ví dụ: “Táo ” thay vì “Táo”), hàm COUNTIF sẽ không tìm thấy sự trùng khớp trong danh sách.

• Thứ ba là về hiệu suất xử lý vùng dữ liệu lớn

Cả hai hàm INDEX và COUNTIF đều hoạt động nhanh, nhưng khi bạn kết hợp chúng và kéo công thức cho hàng ngàn dòng, Google Sheets có thể bị chậm.

FAQ – Câu hỏi thường gặp

Dưới đây là một số câu hỏi thường gặp của người dùng khi tìm hiểu về cách kết hợp hàm INDEX và hàm COUNTIF trong Google Sheets.

1 – Tại sao hàm INDEX trả về đúng giá trị nhưng hàm COUNTIF lại báo kết quả bằng 0?

Lỗi này thường do định dạng dữ liệu không khớp. Nếu hàm INDEX lấy ra một số ở dạng văn bản (text), còn vùng đếm lại chứa số thực thụ (number), hàm COUNTIF sẽ không nhận diện được. Bạn nên kiểm tra lại định dạng hoặc dùng hàm VALUE() hoặc TO_TEXT() để đồng nhất chúng.

2 – Có thể dùng hàm INDEX để tạo vùng đếm (range) động cho hàm COUNTIF được không?

Có. Bạn có thể dùng công thức dạng COUNTIF(A1:INDEX(A:A, 10), “Điều kiện”). Trong trường hợp này, hàm INDEX không trả về một giá trị mà trả về một tham chiếu ô, giúp giới hạn vùng đếm một cách linh hoạt thay vì cố định.

3 – Làm thế nào để đếm mà không phân biệt chữ hoa, chữ thường?

Mặc định, cả hai hàm INDEX và COUNTIF trong Google Sheets đều không phân biệt chữ hoa chữ thường (ví dụ: “TÁO” hay “táo” đều được tính là một). Nếu bạn muốn đếm chính xác từng kiểu chữ, bạn sẽ cần kết hợp hàm EXACT hoặc QUERY thay vì COUNTIF đơn thuần.

4 – Có thể dùng INDEX để đếm với nhiều điều kiện (COUNTIFS) không?

Có. Bạn hoàn toàn có thể thay thế bất kỳ tiêu chí nào trong hàm COUNTIFS bằng một hàm INDEX. 

Ví dụ: =COUNTIFS(Vùng_1, INDEX(Điều_kiện_1), Vùng_2, INDEX(Điều_kiện_2))

Lời kết

Qua bài viết này, bạn đã phần nào hiểu được việc kết hợp hàm INDEX và hàm COUNTIF trong Google Sheets từ cơ bản đến nâng cao. Hy vọng rằng, với nội dung này sẽ giúp bạn dễ dàng làm chủ các bài toán thực tế phức tạp trong Google Sheets khi cần áp dụng hai hàm này.

Ngoài ra, nếu bạn muốn tham khảo thêm nhiều cách dùng khác của các hàm trong Google Sheets, đừng bỏ lỡ những nội dung tiếp theo của GCS Việt Nam qua các kênh dưới đây nhé. 

• Fanpage: GCS – Google Cloud Solutions 
• Hotline: 024.9999.7777