Cách đếm các giá trị khác biệt và duy nhất trong Excel

Trong hướng dẫn này, Blog Học Excel Online sẽ hướng dẫn các bạn cách đếm các giá trị duy nhất trong Excel bằng công thức và cách đếm tự động các giá trị khác biệt trong bảng. Ngoài ra, Học Excel Online cũng sẽ đề cập đến một số công thức ví dụ để đếm các tên, văn bản, số, giá trị phân biệt chữ hoa và chữ thường,….

Khi làm việc với một tập dữ liệu lớn trong Excel, bạn có thể sẽ cần phải biết có bao nhiêu giá trị trùng lặp và duy nhất ở đó. Và đôi khi, bạn có thể muốn chỉ đếm riêng các giá trị riêng biệt (khác nhau).

Trước tiên hãy nhắc lại một số định nghĩa cần biết:

Giá trị duy nhất – đây là các giá trị xuất hiện trong danh sách duy nhất một lần.
Giá trị khác biệt – tất cả các giá trị khác nhau trong danh sách, nghĩa là giá trị duy nhất cộng với lần xuất hiện lần đầu tiên đại diện cho các giá trị trùng lặp.
Ảnh chụp màn hình sau đây thể hiện sự khác biệt:

Và bây giờ, hãy xem cách bạn có thể đếm các giá trị duy nhất và khác biệt trong Excel sử dụng các công thức.

Tổng hợp kiến thức về Excel cơ bản

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

Dưới đây là một nhiệm vụ phổ biến mà tất cả người dùng Excel chắc chắn đều phải thực hiện. Bạn có một danh sách các dữ liệu và bạn cần phải tìm ra số lượng các giá trị duy nhất trong danh sách đó. Làm thế nào để bạn làm điều đó? Bạn sẽ tìm ra một vài công thức để đếm các giá trị duy nhất trong số các công thức dưới đây:

Đếm các giá trị duy nhất trong một cột

Giả sử bạn có một cột tên trong bảng tính Excel của mình và bạn cần đếm các tên duy nhất trong cột đó. Giải pháp là sử dụng hàm SUM kết hợp với IF và COUNTIF:

=SUM(IF(COUNTIF(range, range)=1,1,0))
Chú thích. Đây là một công thức mảng, vì vậy hãy chắc chắn nhấn Ctrl + Shift + Enter để hoàn tất công thức. Một khi bạn làm điều đó, Excel sẽ tự động đóng khung công thức trong {dấu ngoặc nhọn} như trong ảnh chụp màn hình bên dưới.

Trong ví dụ này, chúng ta đang đếm tên duy nhất trong phạm vi A2: A10, vì vậy công thức của chúng ta có dạng sau:

=SUM(IF(COUNTIF(A2:A10,A2:A10)=1,1,0))

Ngoài ra, trong bài viết này, Học Excel Online cũng đưa đến cho các bạn một số công thức mở rộng dựa trên công thức giá trị duy nhất của Excel để áp dụng vào nhiều trường hợp khác nhau. Do đó, bạn cần hiểu đầy đủ về cách thức hoạt động cũng như sự thay đổi giữa công thức gốc và công thức mở rộng.

Cách hoạt động của công thức đếm các giá trị duy nhất trong Excel

Học Excel văn phòng Online với các chuyên gia

Như bạn thấy, 3 chức năng khác nhau được sử dụng trong công thức tìm giá trị duy nhất là – SUM, IF và COUNTIF. Nhìn một cách cụ thể ta có:

Hàm COUNTIF đếm số lần mỗi giá trị xuất hiện trong phạm vi được chỉ định.
Trong ví dụ này, COUNTIF(A2:A10,A2:A10) trả về mảng {1;2;2;1;2;2;2;1;2}.

Hàm IF đánh giá mỗi giá trị trong mảng trả về bởi COUNTIF, giữ lại cả những giá trị xuất hiện lần đầu đại diện cho những giá trị trung lặp, và thay thế tất cả các giá trị khác bằng số không.
Vì vậy, hàm IF(COUNTIF(A2:A10,A2:A10)=1,1,0) trở thành IF(1;2;2;1;2;2;2;1;2) = 1,1,0, Trong đó 1 là một giá trị duy nhất và 0 là một giá trị trùng lặp.

Cuối cùng, hàm SUM thêm các giá trị trong mảng trả về bởi IF và cho ra tổng số các giá trị duy nhất, đó là chính xác những gì chúng ta muốn.
Mẹo. Để xem một phần cụ thể trong công thức giá trị duy nhất của Excel, bạn hãy chọn phần đó trong thanh công thức và nhấn phím F9.

Đếm các giá trị duy nhất dạng văn bản trong Excel

Nếu danh sách Excel chứa cả số và giá trị văn bản, và bạn chỉ muốn đếm các giá trị văn bản duy nhất, bạn cần thêm hàm ISTEXT vào công thức mảng được thảo luận ở trên:

=SUM(IF(ISTEXT(A2:A10)*COUNTIF(A2:A10,A2:A10)=1,1,0))

Như bạn đã biết, hàm Excel ISTEXT trả về TRUE nếu một giá trị được đánh giá là văn bản, FALSE nếu không phải văn bản. Dấu sao (*) hoạt động như toán tử AND trong các công thức mảng, hàm IF trả về 1 chỉ khi một giá trị có cả văn bản và giá trị đó là duy nhất, 0 nếu không thỏa mãn điều kiện. Và sau khi chức năng SUM lọc thêm cả 1 lần xuất hiện đại diện cho những giá trị trùng lặp, bạn sẽ nhận được một số giá trị văn bản duy nhất trong phạm vi định sẵn.

Đừng quên nhấn Ctrl + Shift + Enter để nhập đúng công thức mảng và bạn sẽ nhận được một kết quả tương tự như sau:

Như bạn thấy trong ảnh chụp màn hình ở trên, công thức trả về tổng số giá trị văn bản duy nhất, trừ ô trống, số, giá trị logic của TRUE và FALSE, và lỗi.

Đếm các giá trị số duy nhất trong Excel

Để đếm các giá trị số duy nhất trong một danh sách dữ liệu, hãy sử dụng công thức mảng như chúng ta đã sử dụng để đếm các giá trị văn bản duy nhất với sự khác biệt duy nhất là bạn sử dụng ISNUMBER thay vì ISTEXT trong công thức giá trị duy nhất:

=SUM(IF(ISNUMBER(A2:A10)*COUNTIF(A2:A10,A2:A10)=1,1,0))

Đếm các giá trị duy nhất phân biệt chữ hoa chữ thường trong Excel

Nếu bảng của bạn có chứa dữ liệu phân biệt chữ hoa chữ thường, cách đơn giản nhất để đếm các giá trị duy nhất là tạo ra một cột trợ giúp với công thức mảng sau để xác định các mục trùng lặp và duy nhất:

=IF(SUM((–EXACT($A$2:$A$10,A2)))=1,”Unique”,”Dupe”)

Và sau đó, sử dụng một hàm COUNTIF đơn giản để tính các giá trị duy nhất:

=COUNTIF(B2:B10, “unique”)

Đếm các giá trị khác biệt trong Excel (giá trị duy nhất và 1 xuất hiện đại diện cho các giá trị trùng lặp)

Để có được một số các giá trị khác biệt trong một danh sách, ta sử dụng công thức sau:

=SUM(1/COUNTIF(range, range))
Hãy nhớ rằng, đó là một công thức mảng, và do đó bạn nên nhấn phím tắt Ctrl + Shift + Enter thay vì phím Enter như thông thường.

Ngoài ra, bạn có thể sử dụng chức năng SUMPRODUCT và hoàn thành công thức theo cách thông thường bằng cách nhấn phím Enter:

=SUMPRODUCT(1/COUNTIF(range, range))
Ví dụ: để đếm các giá trị khác biệt trong phạm vi A2: A10, bạn có thể chọn:

=SUM(1/COUNTIF(A2:A10,A2:A10))

Hoặc

=SUMPRODUCT(1/COUNTIF(A2:A10,A2:A10))

Cách hoạt động của công thức giá trị khác biệt của Excel

Như bạn đã biết, chúng ta sử dụng hàm COUNTIF để tìm ra số lần mỗi giá trị xuất hiện trong phạm vi được chỉ định. Trong ví dụ trên, kết quả của hàm COUNTIF là mảng sau: {2;2;3;1;2;2;3;1;3}.

Sau đó, một số hoạt động phân chia được thực hiện, trong đó mỗi giá trị của mảng được sử dụng như một số chia với 1 là cổ tức. Điều này sẽ biến tất cả các giá trị trùng lặp thành các số phân số tương ứng với số lần xuất hiện trùng lặp. Ví dụ, nếu một giá trị xuất hiện 2 lần trong danh sách, nó tạo ra 2 mục trong mảng với một giá trị là 0.5 (1/2=0.5). Và nếu giá trị xuất hiện 3 lần, nó sẽ tạo ra 3 mục trong mảng với giá trị là 0.3(3). Trong ví dụ của chúng ta, kết quả của 1/COUNTIF(A2:A10,A2:A10)) là mảng {0.5;0.5;0.3(3);1;0.5;0.5;0.3(3);1;0.3(3)}.

Khi một trong các hàm này cộng thêm các giá trị trong mảng, tổng của tất cả các số phân số cho mỗi mục riêng lẻ luôn là 1, cho dù có bao nhiêu lần xuất hiện của mục đó tồn tại trong danh sách. Và bởi vì tất cả các giá trị duy nhất xuất hiện trong mảng là lần đại diện duy nhất (1/1=1), kết quả cuối cùng được trả về bởi công thức là tổng số các giá trị khác nhau trong danh sách.

Xem thêm: học Excel kế toán bán hàng

Công thức đếm các giá trị khác biệt theo những cách khác nhau

Cũng như trường hợp đếm các giá trị duy nhất trong Excel, bạn có thể sử dụng các biến thể của công thức đếm số Excel cơ bản để xử lý các loại giá trị cụ thể như số, văn bản và các giá trị phân biệt chữ hoa và chữ thường.

Hãy nhớ rằng tất cả các công thức dưới đây là các công thức mảng và yêu cầu nhấn Ctrl + Shift + Enter khi hoàn tất công thức.

Đếm các giá trị khác biệt bỏ qua ô trống

Nếu một cột mà bạn muốn đếm các giá trị khác biệt có chứa các ô trống, bạn nên thêm một hàm IF để kiểm tra phạm vi quy định cho khoảng trống (công thức Excel cơ bản được thảo luận ở trên sẽ trả về lỗi #DIV/0 trong trường hợp này):

=SUM(IF(range<>””,1/COUNTIF(range, range), 0))
Ví dụ: đếm các giá trị khác biệt trong dải A2: A10, sử dụng công thức mảng sau:

=SUM(IF(A2:A10<>””,1/COUNTIF(A2:A10, A2:A10), 0))

Công thức đếm các giá trị văn bản khác biệt

Để đếm các giá trị văn bản khác biệt trong một cột, chúng ta sẽ sử dụng phương pháp tương tự mà đã sử dụng để loại trừ các ô trống.

Như bạn có thể dễ dàng đoán được, chúng ta chỉ đơn giản là thêm hàm ISTEXT vào công thức giá trị khác biệt trong Excel:

=SUM(IF(ISTEXT(range),1/COUNTIF(range, range),””))
Và đây là một ví dụ về công thức thực tế:

=SUM(IF(ISTEXT(A2:A10),1/COUNTIF(A2:A10, A2:A10),””))

Công thức đếm các giá trị số khác biệt

Để đếm các giá trị số khác biệt (số, ngày tháng và thời gian), ta sử dụng hàm ISNUMBER:

=SUM(IF(ISNUMBER(range),1/COUNTIF(range, range),””))
Ví dụ, đếm tất cả các số khác nhau trong phạm vi A2: A10, sử dụng công thức sau:

=SUM(IF(ISNUMBER(A2:A10),1/COUNTIF(A2:A10, A2:A10),””))

Đếm các giá trị khác biệt phân biệt chữ hoa chữ thường trong Excel

Tương tự như đếm các giá trị duy nhất có phân biệt chữ hoa chữ thường, cách đơn giản nhất để đếm các giá trị khác biệt phân biệt chữ hoa chữ thường là thêm cột trợ giúp bằng công thức mảng xác định các giá trị duy nhất kể cả lần xuất hiện trùng lặp đầu tiên. Công thức này về cơ bản giống như công thức mà chúng ta đã sử dụng để tính các giá trị duy nhất phân biệt chữ hoa chữ thường, với một thay đổi nhỏ trong một tham chiếu ô nhưng lại tạo ra sự khác biệt lớn:

=IF(SUM((–EXACT($A$2:$A2,$A2)))=1,”Distinct”,””)

Tất nhiên, vẫn phải nhớ rằng tất cả các công thức mảng trong Excel yêu cầu nhấn Ctrl + Shift + Enter.

Sau khi công thức trên kết thúc, hãy viết một công thức COUNTIF thông thường như =COUNTIF(B2:B10, “distinct”) để đếm các giá trị khác biệt:

Nếu bạn không thể thêm một cột trợ giúp vào bảng tính thì có thể sử dụng công thức mảng phức tạp sau để đếm các giá trị khác biệt phân biệt chữ hoa chữ thường mà không cần tạo cột bổ sung:

=SUM(IFERROR(1/IF($A$2:$A$10<>””, FREQUENCY(IF(EXACT($A$2:$A$10, TRANSPOSE($A$2:$A$10)), MATCH(ROW($A$2:$A$10), ROW($A$2:$A$10)), “”), MATCH(ROW($A$2:$A$10), ROW($A$2:$A$10))), 0), 0))

Các hàm thông dụng trong excel

Đếm các hàng duy nhất và khác biệt trong Excel

Đếm các hàng duy nhất / khác biệt trong Excel giống như việc đếm các giá trị duy nhất và riêng biệt, với sự khác biệt duy nhất là bạn sử dụng hàm COUNTIFS thay vì COUNTIF, hàm này cho phép bạn chỉ định một vài cột để kiểm tra các giá trị duy nhất.

Ví dụ: để đếm các tên duy nhất hoặc khác biệt dựa trên các giá trị trong cột A (First Name) và B (Last Name), hãy sử dụng một trong các công thức sau:

Công thức đếm các hàng duy nhất:

=SUM(IF(COUNTIFS(A2:A10,A2:A10, B2:B10,B2:B10)=1,1,0))

Công thức đếm các hàng khác biệt:

=SUM(1/COUNTIFS(A2:A10,A2:A10,B2:B10,B2:B10))

Đương nhiên, không phải khi đếm các hàng duy nhất bạn chỉ có thể đếm trong giới hạn 2 cột như ở trong ví dụ, hàm COUNTIFS có thể xử lý 127 phạm vi / cặp tiêu chuẩn.
Trong bài viết này, Học Excel Online đã giới thiệu cho các bạn cách đếm các giá trị duy nhất và khác biệt trong Excel. Hy vọng bài viết có thể đem lại cho các bạn cái nhìn tổng quan cũng như những công thức hữu ích cho công việc của bạn. Cảm ơn các bạn đã theo dõi bài viết này.

Đừng bở lỡ: Thủ thuật Excel nâng cao