Cách đếm các giá trị duy nhất trong Excel

Trong nhiều trường hợp, bạn có một danh sách hoặc phạm vi với nhiều dữ liệu phức tạp, bạn cần đếm xem có bao nhiêu giá trị hoặc dữ liệu không trùng lập trong một bảng tính Excel.

Trong trường hợp này, thông thường chúng ta cần xây dựng các cột trợ giúp với nhiều thao tác phức tạp. Tuy nhiên, với bài viết này, chúng tôi sẽ giới thiệu một số công thức đơn giản, giúp bạn nhanh chóng đếm các giá trị duy nhất trong một dải ô Excel.

Đếm giá trị duy nhất trong Excel với hàm SUMPRODUCT và COUNTIF 

Công thức đếm giá trị duy nhất trong Excel với hàm SUMPRODUCT và COUNTIF

Giả sử bạn có một danh sách những người dùng đã nhận được SMS từ Rachel vào một ngày cụ thể và danh sách đó nằm trong phạm vi A1: A14.

Chúng ta muốn biết có bao nhiêu người đã nhận được tin nhắn. Như bạn thấy trong danh sách này, có tổng cộng tổng cộng 14 tin nhắn SMS, nhưng Rachel đã gửi 2 tin nhắn cho Ingrid, James và Arthur. Như vậy thật sự chỉ có 11 người dùng được nhận tin nhắn mà thôi. Chúng ta sẽ sử dụng một công thức bao gồm hàm SUMPRODUCT và COUNTIF trong Excel

Đây là công thức: 

= SUMPRODUCT (1 / COUNTIF (A1: A14,  A1: A14 ))

Xem thêm: Cách tính tổng nhiều điều kiện trong Excel với hàm SUMIF và hàm SUMIFS

Cách thức hoạt động của công thức 

Công thức này hoạt động như thế nào? Chúng ta hãy cùng tách nhỏ nó ra nhé.

Để bắt đầu, chúng ta sẽ xem COUNTIF làm gì.

= COUNTIF (A1: A14,  A1: A14 )

Phần này đếm xem mỗi người dùng có bao nhiêu trong danh sách. Nó là một công thức mảng trong Excel. Nó phải được hiểu như sau:

= COUNTIF (A1,  A1: A14 ) = 2

= COUNTIF (A2,  A1: A14 )  = 1

....

= COUNTIF (A14,  A1: A14 )  = 2

Đưa kết quả của công thức này dưới dạng một mảng.

= COUNTIF (A1: A14,  A1: A14 )  = {2, 1, 2; 1; 1; 1; 2; 2; 1; 2; 1; 1; 1; 2}
 

Để xem mảng, chọn một ô, nhấn F2 + F9.
 

Như vậy:

1 / COUNTIF (A1: A14,  A1: A14 )  = {0,5; 1; 0,5; 1; 1; 1; 0,5; 0,5; 1; 0,5; 1; 1; 1; 0,5}
 

Và do đó:

= SUMPRODUCT ( 1 / COUNTIF (A1: A14,  A1: A14 ) ) = {0,5 + 1 + 0,5 + 1 + 1 + 1 + 0,5 + 0,5 + 0,5 + 1 + 1 + 1 + 1 + 0,5} = 11

Đếm giá trị duy nhất trong Excel với hàm FREQUENCY và MATCH

Ở phần trên, chúng ta đã tìm hiểu cách đếm số lượng giá trị duy nhất trong một phạm vi. Thủ thuật đó về cơ bản là sử dụng hàm SUMPRODUCT và COUNTIF trong Excel

Bây giở, chúng ta sẽ tìm hiểu một cách thú vị khác, sử dụng công thức bao gồm hàm FREQUENCY và MATCH trong Excel

Để bạn dễ dàng đối chiếu so sánh, và chọn công thức phù hợp nhất với mình, chúng ta sẽ sử dụng lại ví dụ bên trên.

Dưới đây là công thức mà chúng ta sẽ sử dụng trong phần này:

{= SUM (IF (FREQUENCY (MATCH (Name_list, Name_list, 0), ROW (Name_list))> 0,1,0))}
 

Công thức này là một công thức mảng hoặc ma trận trong Excel. Hãy xem cách nó hoạt động từng bước.

Để xem toàn bộ kết quả của từng công thức, bạn cần nhấn F2 + F9, một lần trong ô.

1. =MATCH(Name_list,Name_list,0)

Để hiểu một công thức tương đối dài này, điều chính là bắt đầu với cạnh nhỏ hơn, thường ở giữa nhiều hơn. Trong trường hợp này, điều đầu tiên cần hiểu là điều gì tạo nên MATCH (Name_list, Name_list, 0) . Đầu tiên hãy kiểm tra MATCH nếu bạn có bất kỳ nghi ngờ nào về cách hoạt động của hàm.

Công thức mảng này sẽ cho chúng ta biết đối với từng tên, chúng xuất hiện ở vị trí nào trên danh sách.

=MATCH(Name_list,Name_list,0) = {1; 2; 3; 4; 5; 6; 1; 3; 9; 10; 11; 12; 13; 10}

Như được hiển thị trong kết quả, chỉ có các bản sao có số lượng khác với vị trí của nó. Có thể thấy rằng Arthur, một trong những cái tên được lặp lại như trong A1 và A7, nhận được giá trị 1 trong cả hai trường hợp, cho chúng ta biết rằng giá trị đầu tiên trong danh sách mà  Arthur  xuất hiện nằm ở vị trí đầu tiên.

2. =ROW(Name_list)

Hàm ROW là hàm Excel tạo ra một ma trận với tất cả các số từ 1 đến 14, có 14 giá trị trong phạm vi của chúng ta.

=ROW(Name_list) = {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14}
 

3. =FREQUENCY(MATCH(Name_list,Name_list,0),ROW(Name_list))
 

Hàm tần số trong Excel tính toán tần suất mà một giá trị xuất hiện trong một phạm vi giá trị và trả về một dãy số dọc.

Trong trường hợp của chúng tôi, về cơ bản nó đếm số lần mỗi giá trị xuất hiện trong danh sách.

= FREQUENCY (MATCH (Name_list, Name_list, 0), ROW (Name_list)) =
{2, 1, 2; 1; 1; 1; 0; 0; 1; 2; 1; 1; 1; 0; 0}
 

Như có thể thấy trong ma trận thu được ở bước 1, có 2 giá trị bằng 1, 2 bằng 3 và 2 bằng 10, điều này rõ ràng chỉ ra rằng cả Arthur, Ingrid và James đều nhận hơn 1 tin nhắn.
 

4. = IF (FREQUENCY (MATCH (Name_list, Name_list, 0), ROW (Name_list))> 0,1,0)
 

Hàm IF sẽ cung cấp giá trị 1 cho tất cả các tên không trùng lặp và các tên trùng lặp (chỉ chúng, không phải giá trị ban đầu) sẽ được cho là 0.

= IF (FREQUENCY (MATCH (Tên_danh_sách, Tên_danh_sách, 0), ROW (Tên_sách))> 0,1,0) = {1; 1; 1; 1; 1; 1; 0; 0; 1; 1; 1; 1; 1; 0; 0}
 

5. = SUM (IF (FREQUENCY (MATCH (Name_list, Name_list, 0), ROW (Name_list))> 0,1,0))
 

Cuối cùng, thêm tất cả các giá trị của ma trận kết quả,

= SUM (IF (FREQUENCY (MATCH (Name_list, Name_list, 0), ROW (Name_list))> 0,1,0)) = 11
 

Xem thêm: Hướng dẫn cách xử lý lỗi định dạng số trong Excel đơn giản nhanh chóng

Tổng kết

Hy vọng với bài viết này, bạn đã biết được cách đếm các giá trị duy nhất trong Excel. Cách thứ nhất ngắn gọn hơn, nhưng cách thức hai giới thiệu cho bạn các ứng dụng công thức mảng và ma trận, giúp bạn nâng cao kỹ năng của mình hơn nữa.

Bên cạnh đó, để không bỏ lỡ những mẹo và thủ thuật tin học văn phòng hữu ích khác, hãy tham gia Gitiho ngay hôm nay.