Cách đếm giá trị duy nhất trong Excel: Những điều bạn cần biết

Trong hướng dẫn này, tôi sẽ chỉ bạn cách đếm các giá trị duy nhất trong Excel. Tôi cũng sẽ trình bày các trường hợp đặc biệt, như cách xử lý phân biệt chữ hoa/thường, hoặc cách đếm giá trị duy nhất dựa trên một hay nhiều điều kiện.

Để tiếp tục nâng cao kỹ năng Excel, hãy đăng ký lộ trình kỹ năng Excel Fundamentals của chúng tôi để trở thành chuyên gia.

Giá trị duy nhất và giá trị phân biệt trong Excel

Hai khái niệm này đôi khi bị nhầm lẫn, nhưng giá trị duy nhất và giá trị phân biệt không giống nhau.

• Giá trị phân biệt là các giá trị khác nhau trong một tập dữ liệu, đã loại bỏ trùng lặp. Ví dụ, trong danh sách A, A, B, C, C, D, các giá trị phân biệt là A, B, C, D. 
• Giá trị duy nhất, ngược lại, là các giá trị chỉ xuất hiện một lần trong tập dữ liệu. Vẫn với ví dụ trên, các giá trị duy nhất là B và D (vì A và C xuất hiện nhiều hơn một lần).

Trong nửa đầu bài viết, tôi sẽ tập trung vào các cách khác nhau để đếm giá trị duy nhất trong Excel. Nhưng nếu bạn vào đây do nhầm lẫn và thực sự muốn đếm giá trị phân biệt, phần sau cũng sẽ hướng dẫn bạn cách thực hiện.

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

Có hai cách phổ biến để đếm giá trị duy nhất trong Excel.

Cách đếm giá trị duy nhất trong Excel bằng hàm UNIQUE()

Cách dễ nhất để đếm giá trị duy nhất là dùng hàm UNIQUE() kết hợp với COUNTA(). Hàm UNIQUE() trích xuất tất cả các giá trị duy nhất từ một phạm vi, và COUNTA() đếm có bao nhiêu giá trị duy nhất.

COUNTA(UNIQUE(range, false, true))

Ví dụ, tôi áp dụng công thức sau cho một tập dữ liệu nhỏ và nó trả về số giá trị duy nhất.

=COUNTA(UNIQUE(A2:A8, false, true))

Nếu bạn dùng cú pháp này, =COUNTA(UNIQUE(A2:A8)), không có tham số thứ ba đặt là TRUE, thì kết quả trả về sẽ là số đếm phân biệt, không phải duy nhất. Đặt TRUE ở đối số thứ ba yêu cầu hàm trả về số đếm duy nhất. Cả hai tính năng đều nằm trong UNIQUE(), nên ban đầu có thể hơi gây nhầm lẫn.

Cách đếm giá trị duy nhất trong Excel với SUM() và COUNTIF()

Nếu bạn chưa nắm rõ các sắc thái của UNIQUE(), hoặc nếu bạn không dùng Excel 365, hãy biết rằng ta cũng có thể kết hợp SUM() với IF() và COUNTIF() để đếm các giá trị duy nhất.

=SUM(IF(COUNTIF(range, range)=1,1,0))

Ví dụ, tôi có dữ liệu trong phạm vi A2:A8 và muốn đếm các giá trị duy nhất, nên tôi viết công thức sau:

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

Ở đây COUNTIF() duyệt qua danh sách và kiểm tra mỗi tên xuất hiện bao nhiêu lần. Nếu một tên xuất hiện đúng một lần, nó được tính là 1. Nếu xuất hiện nhiều hơn một lần, nó nhận giá trị lớn hơn. Sau đó, IF() lọc kết quả để giữ nguyên các số 1 nhưng chuyển mọi giá trị khác thành 0. Cuối cùng, SUM() cộng tất cả các số 1 và cho ta tổng số giá trị duy nhất.

Cách đếm các kiểu dữ liệu duy nhất trong Excel

Đôi khi tập dữ liệu chứa nhiều kiểu dữ liệu trộn lẫn và chúng ta cần phân tích riêng. Lúc đầu có thể trông hơi khó, nhưng Excel có thể làm được. Hãy xem hai phương pháp — một để đếm giá trị văn bản duy nhất và một cho số duy nhất.

Cách đếm giá trị văn bản duy nhất trong Excel

Nếu muốn đếm các giá trị văn bản duy nhất, hãy kết hợp các hàm ISTEXT(), COUNTIF() và SUM(). ISTEXT() kiểm tra một giá trị có phải văn bản hay không, COUNTIF() đếm số lần mỗi giá trị xuất hiện, và SUM() cộng tổng các mục văn bản duy nhất. 

Tôi đã áp dụng công thức sau trên dữ liệu mẫu, và ngay lập tức biết được có bao nhiêu giá trị văn bản duy nhất.

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

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

Nếu bạn muốn đếm số thay vì văn bản, hãy dùng cùng công thức nhưng thay ISTEXT() bằng ISNUMBER().

ISNUMBER() chỉ xét các giá trị số, còn COUNTIF() và SUM() xử lý tính duy nhất. Tôi đã chỉnh công thức trên với cùng dữ liệu, và giờ nó chỉ cho biết số lượng giá trị số duy nhất:

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

Cách đếm giá trị duy nhất theo điều kiện trong Excel

Giờ khi đã bao quát các phương pháp cơ bản để đếm giá trị duy nhất, hãy khám phá một số kỹ thuật nâng cao.

Cách đếm giá trị duy nhất theo điều kiện

Để đếm các giá trị duy nhất dựa trên một điều kiện cụ thể, tôi dùng cú pháp này:

=IFERROR(ROWS(UNIQUE(FILTER(range, criteria_range=criteria))), 0)

Trong công thức này, FILTER() lọc phạm vi theo tiêu chí, UNIQUE() loại trùng lặp, ROWS() đếm kết quả, và IFERROR() ngăn lỗi bằng cách trả về Not Found nếu không tìm thấy kết quả phù hợp.

Giả sử tôi có danh sách Nhân viên và Phòng ban, và cần đếm có bao nhiêu nhân viên duy nhất làm việc ở một phòng ban cụ thể. Tôi sẽ dùng công thức sau:

=IFERROR(ROWS(UNIQUE(FILTER(A2:A20, B2:B20=F1))), "Not Found")

Ở đây, A2:A10 đại diện phạm vi tên nhân viên tôi muốn lọc và B2:B10=F1 kiểm tra nhân viên thuộc phòng ban nào, được ghi trong ô F1.

Cách đếm các hàng duy nhất dựa trên nhiều cột

Nếu tôi muốn đếm các nhân viên duy nhất trong một phòng ban cụ thể có mức lương dưới $50.000, tôi dùng công thức này:

=IFERROR(ROWS(UNIQUE(FILTER(A2:A10, (B2:B10=F1) * (C2:C10>F2)))), "Not Found")

Ở đây, A2:A10 là phạm vi tên nhân viên, B2:B10=F1 kiểm tra nhân viên thuộc phòng ban nào, được ghi trong F1, và C2:C10>F2 kiểm tra mức lương có nhỏ hơn số ghi ở F2 hay không.

Cách xử lý đếm duy nhất phân biệt chữ hoa/thường

Theo mặc định, Excel phân biệt chữ hoa/thường. Ví dụ, Apple và APPLE được coi là khác nhau. Để phát hiện các trường hợp như vậy, tôi tạo một cột phụ với công thức sau:

=IF(SUM((--EXACT($A$2:$A$11,A2)))=1,"Unique","Duplicate")

Và bây giờ, để lấy số đếm các giá trị duy nhất, tôi dùng hàm COUNTIF() như sau:

=COUNTIF(B2:B11,"Unique")

Cách đếm giá trị phân biệt

Cuối cùng, trong trường hợp bạn thực sự muốn đếm các giá trị phân biệt, tôi sẽ chỉ bạn vài cách để đếm giá trị phân biệt. Nhưng trước tiên, đây là một bảng để bạn thấy sự khác nhau:

Cách đếm giá trị phân biệt trong Excel bằng COUNTIF() và SUM()

Để đếm giá trị phân biệt trong Excel, bạn có thể kết hợp hàm COUNTIF() và SUM(). COUNTIF() kiểm tra mỗi giá trị xuất hiện bao nhiêu lần trong một phạm vi. Sau đó, SUM() cộng các giá trị do COUNTIF() trả về, cho tổng số mục phân biệt. 

=SUM(1/COUNTIF(range, range))

Giả sử tôi muốn tìm các giá trị phân biệt trong phạm vi A2:A8. Tôi nhập công thức sau:

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

Ở đây, hàm COUNTIF() kiểm tra mỗi giá trị xuất hiện bao nhiêu lần trong danh sách.

1/COUNTIF(A2:A8, A2:A8) chia 1 cho số lần xuất hiện của mỗi giá trị. Ví dụ, nếu một số xuất hiện hai lần, mỗi lần sẽ thành 0,5 (1/2). Nếu xuất hiện ba lần, sẽ là 0,33 (1/3). 

Vì chúng ta không muốn các phân số này, có thể dùng SUM() hoặc SUMPRODUCT() để cộng tất cả các giá trị. Các phân số của bản trùng sẽ cộng lại thành 1, còn các giá trị phân biệt giữ nguyên 1 vì 1/1 = 1:

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

Cách đếm giá trị phân biệt trong Excel bằng PivotTable

Excel 2013 và Excel 2016 có sẵn tính năng đếm giá trị phân biệt trong PivotTable. Để sử dụng, trước tiên bạn tạo PivotTable bằng cách chọn dữ liệu: vào Insert và chọn PivotTable. Một hộp thoại sẽ xuất hiện — trong đó, chọn Existing Worksheet và đánh dấu chọn ô Add this data to the Data Model.

Tiếp theo, kéo cột bạn muốn vào vùng Values. Ở đây, tôi kéo cột Fruits vì tôi muốn đếm các giá trị phân biệt của nó. Sau đó, nhấp vào Value Field Settings từ menu thả xuống. Một cửa sổ sẽ bật lên — tại đó, chọn Distinct Count. Bạn cũng có thể đặt tên tùy chỉnh cho cột này, nhưng không bắt buộc.

Cách đếm giá trị phân biệt trong Excel bằng bộ lọc

Nếu bạn không thích làm việc với công thức, hãy dùng bộ lọc nâng cao.

Trong ví dụ này, tôi có một tập dữ liệu và muốn tìm các giá trị phân biệt bằng bộ lọc nâng cao. Tôi chọn phạm vi cần tìm giá trị, rồi vào thẻ Data và nhấp Advanced. 

Trong hộp thoại Advanced Filter, tôi chọn Copy to another location, và trong trường Copy to, tôi nhập ô đích nơi danh sách sẽ xuất hiện. Sau đó đánh dấu chọn Unique Records Only và nhấp OK.

Khi đã có danh sách các giá trị duy nhất, tôi tính số lượng các giá trị này bằng hàm ROWS():

=ROWS(D2:D5)

Mặc dù tùy chọn trong hình bên dưới ghi là Unique records only, thực tế nó trích xuất các giá trị phân biệt. 

Cách đếm giá trị phân biệt trong Excel bằng macro VBA

Nếu bạn muốn lặp lại việc đếm giá trị phân biệt, hãy dùng VBA (Visual Basic for Applications), nơi chúng ta viết mã một lần và tự động hóa toàn bộ quy trình mỗi khi gọi hàm theo tên.

Cách dùng như sau: nhấn Alt + F11 và vào mục Module trong thẻ Insert. Một module mới sẽ xuất hiện và bạn có thể dán mã vào đây. 

Đây là đoạn mã tôi tạo để đếm giá trị phân biệt:

Function CountUnique(rng As Range) As Integer
    Dim dict As Object
    Set dict = CreateObject("Scripting.Dictionary")
    
    Dim cell As Range
    For Each cell In rng
        If Not dict.exists(cell.Value) And cell.Value <> "" 

Then,
            dict.Add cell.Value, Nothing
        End If
    Next cell
    
    CountUnique = dict.Count
End Function

Sau đó nhấn Ctrl + S để lưu và ALt + Q để đóng trình soạn thảo VBA. Giờ bạn có thể gọi hàm tùy chỉnh này ở bất kỳ đâu trên trang tính và chỉ định phạm vi cần tìm các giá trị phân biệt. Trường hợp của tôi, hàm tên là CountUnique() nên mỗi lần gọi hàm với một phạm vi, nó sẽ trả về số đếm:

=CountUnique(A2:A8)

Tổng kết

Tôi đã trình bày các công thức chính, PivotTable và macro VBA để đếm giá trị duy nhất và phân biệt. Mỗi phương pháp đều có ưu điểm riêng, nhưng bạn nên chọn cách phù hợp nhu cầu và phiên bản Excel của mình.

Nếu bạn muốn rèn luyện kỹ năng Excel, hãy xem lộ trình kỹ năng Excel Fundamentals và khóa Data Preparation in Excel của chúng tôi.