Hàm đếm các giá trị giống nhau trong Excel

Để đếm các giá trị giống nhau trong 1 cột, hoặc giữa 2 cột, hoặc giữa 2 danh sách trong Excel thì ta sử dụng hàm COUNTIF với công thức chung như sau:

=COUNTIF(phạm_vi_dữ_liệu, "giá_trị_cần_đếm")

Trong đó:

• phạm_vi_dữ_liệu là vùng ô chứa dữ liệu cần đếm.
• giá_trị_cần_đếm là giá trị bạn muốn đếm số lần xuất hiện trong vùng dữ liệu.

Sau đây Kế Toán VN sẽ hướng dẫn chi tiết bằng ví dụ thực tế với bảng dữ liệu mẫu về thông tin người mua hàng như sau:

1. Đếm các giá trị giống nhau trên cùng 1 cột

Ví dụ ta muốn biết mỗi khách hàng đặt bao nhiêu đơn hàng, ta có thể đếm số lần xuất hiện của tên khách hàng trong cột HoTen. Ví dụ ta muốn đếm giá trị “Trần Bình” xuất hiện trong cột B thì ta sẽ sử dụng công thức sau vào ô F2:

=COUNTIF($B:$B,B2) (trong đó B2 là ô chứa tên “Trần Bình” cần đếm số lần xuất hiện trong cột B)

Áp dụng công thức F2 cho toàn bộ cột F, ta sẽ có được 1 cột chứa giá trị số lần xuất hiện tên khách hàng trong cột B:

Xem ngay File mẫu với các hàm đếm giá trị trùng nhau sử dụng ngay

2. Đếm giá trị trùng theo 2 cột trở lên

Để đếm giá trị trùng trong trường hợp theo 2 cột trở lên ta sẽ sử dụng hàm COUNTIFS với cú pháp như sau:

=COUNTIFS(vùng1, điều_kiện1, vùng2, điều_kiện2, …)

Ví dụ: Ta muốn đếm giá trị các đơn hàng trùng giá trị MaKH và SanPham thì ta sẽ sử dụng công thức sau:

=COUNTIFS($A:$A, A2, $C:$C, C2)

Trong đó:

• $A:$A là vùng giá trị chứa MaKH và $C:$C là vùng chứa giá trị SanPham

Trong hàm trên, nếu giá đơn hàng có trùng giá trị MaKH và SanPham thì sẽ trả về kết quả > 2. Ngược lại nếu trả về kết quả = 1 thì đó là giá trị duy nhất:

3. Đếm giá trị trùng giữa 2 danh sách

Trong thực tế, ta rất hay gặp trường hợp như có 2 danh sách và ta muốn biết các giá trị trùng giữa 2 danh sách này. Lúc này ta sẽ phát sinh việc:

• Đánh dấu giá trị trong danh sách 1 có trong danh sách 2.
• Tính tổng giá trị trùng trong 2 danh sách.
• Xem danh sách 1 có nằm trong danh sách 2 không?

Lúc này ta vẫn có thể sử dụng hàm COUNTIF để xử lý dễ dàng. Giả sử ta có 2 danh sách email nằm ở cột A (A1:A31) và cột C (C1:C31) như sau:

1. Đánh dấu các giá trị trong danh sách 1 có trong danh sách 2:

Ta thực hiện như sau:

• Bước 1: Bôi đen vùng dữ liệu email của danh sách 1 (ví dụ: A2:A31)
• Bước 2: Vào Home => chọn Conditional Formatting => New Rule => chọn Format Cells where a formula is true và nhập công thức: =COUNTIF(C2:C31, A2:A31)>0
• Bước 3: Chọn màu cần định dạng rồi chọn > Done

Các giá trị email trong danh sách 1 trùng trong danh sách 2 sẽ được đánh dấu màu bởi Conditional Formatting như sau:

2. Xác định xem danh sách 1 có nằm trong danh sách 2 không?

Để kiểm tra danh sách email 1 có nằm trọn trong danh sách email 2 không? Ta sử dụng hàm COUNTIF sau:

=COUNTIF(C2:C31, A2:A31)>0

Trong đó:

• C2:C31 là danh sách email 2.
• A2:A31 là danh sách email 1.

Kết quả hàm trên sẽ trả về kết quả TRUE/FALSE. Nếu TRUE thì danh sách 1 nằm trong danh sách 2 và ngược lại nếu FALSE thì danh sách 1 không nằm hoàn toàn trong danh sách 2.

3. Đếm số giá trị nằm trong cả danh sách 1 và 2:

Để đếm số email nằm trong cả 2 danh sách 1 và 2 thì ta sử dụng công thức sau:

=ROWS(UNIQUE(FILTER(A2:A31, COUNTIF(C2:C31, A2:A31)>0)))

Trong đó:

• COUNTIF(C2:C31, A2:A31) sẽ đối chiếu từng phần tử trong A2:A31 với danh sách C2:C31 và trả về 1 mảng số đếm cùng với độ dài A2:A31 ví dụ {1;0;2;0;…}
• COUNTIF(C2:C31, A2:A31)>0 để biến mảng đếm trả về kết quả TRUE/FALSE.
• FILTER(A2:A31, …) dùng để lọc những giá trị ở A2:A31 có điều kiện TRUE. Từ đó ta có kết quả là danh sách các phần tử của A cũng xuất hiện ở C (có thể vẫn còn trùng nhau nếu A chứa bản sao).
• UNIQUE( … ) để khử trùng lặp trong danh sách vừa lọc => các giá trị còn lại chính là email trùng nhau giữa 2 danh sách.
• ROWS( … ) để đếm số hàng của mảng đã khử trùng lặp ⇒ cho ra kết quả là số lượng phần tử email giao nhau.

4. Trả về danh sách các giá trị trùng nằm trong cả 2 danh sách:

Để lọc danh sách các giá trị email trùng nhau giữa 2 danh sách thì ta chỉ cần áp dụng công thức đếm giá trị nằm trong 2 danh sách ở trên nhưng bỏ đi hàm ROWS ngoài cùng vì không cần đếm. Hàm FILTER sẽ trả về danh sách các Email trùng nhau:

UNIQUE(FILTER(A2:A31, COUNTIF(C2:C31, A2:A31)>0))

Kết quả sẽ hiển thị như sau:

Trên đây là hướng dẫn chi tiết cách sử dụng hàm COUNTIF để đếm các giá trị trùng nhau trong Excel. Chúc các bạn áp dụng thành công!