Cách dùng Hàm đếm các giá trị khác nhau trong Excel (có Ví dụ)

Tìm hiểu các phương pháp và công thức khác nhau để đếm các giá trị Duy nhất và Riêng biệt trong Excel (có Ví dụ).

Đôi khi bạn cần đếm số lượng giá trị duy nhất trong một phạm vi Excel. Ví dụ: khi bạn muốn lập báo cáo về số lượng khách hàng chỉ mua sản phẩm của bạn một lần hoặc số học sinh giỏi theo Nam Nữ hoặc xóa các mục trùng lặp khỏi danh sách đơn đặt hàng và chỉ đếm các mặt hàng duy nhất. Dù là trường hợp nào, sẽ rất hữu ích nếu biết có bao nhiêu giá trị duy nhất [Unique] (hoặc khác biệt [Distinct]) trong một cột.

Mặc dù Excel không có bất kỳ công thức hoặc tùy chọn xác định trước nào để đếm các giá trị duy nhất và khác biệt, nhưng có thể đếm các giá trị duy nhất trong Excel bằng nhiều phương pháp khác nhau. Giá trị duy nhất là giá trị xuất hiện hoặc chỉ xuất hiện một lần trong danh sách hoặc cột trong khi giá trị khác biệt là các giá trị khác nhau trong danh sách là giá trị duy nhất có ít nhất 1 trường hợp giá trị trùng lặp. Trong bài viết này, chúng tôi sẽ chia sẻ với bạn các cách khác nhau để đếm các giá trị duy nhất trong Excel.

Sử dụng Advanced Filter để đếm các giá trị duy nhất trong Excel

Dùng Advanced Filter là một trong những phương pháp dễ dàng nhất để đếm các giá trị duy nhất trong Excel mà không liên quan đến bất kỳ công thức nào. Làm theo các bước sau để đếm các giá trị duy nhất bằng cách sử dụng tính năng Advanced Filter trong Excel.

Đầu tiên, chọn phạm vi ô hoặc chọn bất kỳ ô nào trong phạm vi mà bạn muốn đếm các giá trị duy nhất. Sau đó, chuyển đến tab ‘Data’ và nhấp vào tùy chọn ‘Advanced’ trong nhóm ‘Sort & Filter’.

Khi bạn làm điều đó, một hộp thoại Advanced Filter nhỏ sẽ xuất hiện. Tại đây, bạn có hai lựa chọn (Action) – Lọc danh sách và chỉ để lại các giá trị duy nhất trong phạm vi đã chọn hoặc lọc danh sách và sao chép các giá trị duy nhất sang vị trí khác để nguyên dữ liệu nguồn.

Bây giờ, hãy chọn tùy chọn ‘Copy to another location’. Sau đó, nhấp vào trường ‘Copy to’ và chọn một ô hoặc dải ô trống mà bạn muốn các giá trị duy nhất được lọc.

Sau đó, chọn hộp kiểm ‘Unique Records Only’ và nhấp vào ‘OK’.

Tuy nhiên, nếu bạn muốn thay thế danh sách hiện tại bằng các giá trị duy nhất đã lọc, hãy chọn tùy chọn ‘Filter the list, in-place’ trong Hành động và nhấp vào ‘OK’.

Dù bằng cách nào, bạn sẽ nhận được danh sách các giá trị duy nhất từ ​​phạm vi nguồn như được hiển thị bên dưới.

Để nhận tổng số các giá trị duy nhất này, hãy chọn ô bên dưới danh sách hoặc ô trống và nhập công thức bên dưới:

=COUNTA(D3:D12)

Ngoài ra, chỉ cần chọn danh sách các giá trị duy nhất và kiểm tra thanh trạng thái của bạn ở góc dưới cùng bên phải của cửa sổ Excel (bên cạnh điều khiển thu phóng). Ở đó, bạn sẽ thấy số lượng các giá trị duy nhất như hình dưới đây.

Xóa dữ liệu trùng lặp để nhận giá trị duy nhất

Một cách nhanh chóng và dễ dàng khác để đếm các giá trị duy nhất là loại bỏ các giá trị trùng lặp trong một cột và xem còn lại bao nhiêu giá trị. Đây là cách thực hiện:

Đầu tiên, hãy sao chép dữ liệu vào một trang tính mới hoặc một cột khác nếu bạn không muốn mất dữ liệu gốc vì việc xóa các bản sao sẽ xóa tất cả các dữ liệu trùng lặp và chỉ để lại các giá trị duy nhất.

Sau đó, chọn phạm vi ô hoặc chọn bất kỳ ô nào trong phạm vi đó có chứa các giá trị mà bạn muốn xóa trùng lặp, chuyển đến tab ‘Data’ và chọn nút ‘Remove Duplicates’ trong nhóm Công cụ dữ liệu.

Trong hộp thoại Remove Duplicates, hãy chọn (các) cột mà bạn muốn loại bỏ các giá trị trùng lặp và nhấp vào ‘OK’.

Khi bạn nhấp vào ‘OK’, bạn sẽ thấy một hộp nhắc thông báo cho bạn biết có bao nhiêu giá trị trùng lặp đã được xóa và còn lại bao nhiêu giá trị duy nhất.

Nếu bạn muốn đếm giá trị duy nhất trong một ô, thì bạn sẽ cần sử dụng công thức COUNTA từ phương pháp trên.

Tuy nhiên, vấn đề với phương pháp này là nó không chỉ lọc các giá trị duy nhất (chỉ xảy ra một lần), mà còn loại bỏ các bản sao cho các giá trị xuất hiện nhiều lần và chỉ để lại một trường hợp của các giá trị đó. Nếu bạn chỉ muốn đếm các giá trị duy nhất, thì bạn phải kết hợp nhiều hàm Excel.

Đếm các giá trị riêng biệt bằng công thức trong Excel

Chúng ta đã thấy cách đếm các giá trị duy nhất bằng cách sử dụng các tùy chọn Excel, bây giờ chúng ta hãy xem cách đếm các giá trị duy nhất và khác biệt bằng cách sử dụng công thức. Đôi khi bạn có thể muốn đếm các giá trị riêng biệt (bỏ qua các giá trị trùng lặp) thay vì các giá trị duy nhất. Các giá trị Riêng biệt là tất cả các giá trị khác nhau bao gồm các mục nhập duy nhất trong danh sách. Các giá trị khác biệt cũng được coi là giá trị duy nhất trong một số trường hợp, vì vậy biết cách tìm chúng rất hữu ích.

Không quan trọng giá trị có bao nhiêu bản sao, chỉ một trường hợp của giá trị đó được đưa vào số lượng. Ví dụ: nếu giá trị ‘New York’ được lặp lại 5 lần trong danh sách, thì giá trị đó vẫn chỉ được tính là ‘1’.

Đếm các giá trị riêng biệt bằng cách sử dụng công thức SUMPRODUCT và COUNTIF

Công thức đơn giản nhất để đếm các giá trị riêng biệt là sự kết hợp của SUMPRODUCT và COUNTIF.

Đây là cú pháp để đếm các giá trị duy nhất và khác biệt trong một cột:

=SUMPRODUCT(1/COUNTIF(data,data))

data Phạm vi dữ liệu mà bạn muốn đếm giá trị ở đâu .

Ví dụ: Tìm số lượng các giá trị khác biệt trong danh sách sau:

Để làm điều đó, hãy sử dụng công thức sau:

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

Hãy để chúng tôi chia nhỏ nó cho bạn:

• COUNTIF(A2:A10,A2:A10):Hàm COUNTIF lồng nhau đếm số lần mỗi giá trị xuất hiện trong phạm vi ô (A2: A10) và trả về một mảng các số như sau {1;4;1;4;2;1;4;2;4}:. tức là Giá trị chỉ xuất hiện một lần là 1, giá trị xuất hiện 4 lần là 4, v.v.
• 1/COUNTIF(A2:A10,A2:A10): Sau đó, mảng số kết quả từ công thức COUNTIF được sử dụng làm số chia / số chia cho phép chia với 1l àm tử số của chúng. Vì vậy, 1 được chia cho mỗi giá trị của mảng kết quả của COUNTIF. Kết quả là, bạn sẽ nhận được một mảng kết quả khác {1;0.25;1;0.25;0.5;1;0.25;0.5;0.25}:.

Như bạn có thể thấy, giá trị chỉ xuất hiện một lần (duy nhất) là 1 và các giá trị xuất hiện nhiều lần sẽ trở thành phân số. Ví dụ: giá trị ‘San Francisco’ xuất hiện hai lần, vì vậy khi 1 chia cho 2, bạn sẽ nhận được ‘0,5’.

Cuối cùng, hàm SUMPRODUCT cộng tất cả các số trong mảng và trả về số lượng các giá trị riêng biệt: ‘5’. Số lượng bao gồm tất cả các giá trị khác nhau xuất hiện ít nhất một lần trong danh sách, không bao gồm các giá trị trùng lặp.

Đếm các giá trị riêng biệt BỎ QUA/BAO GỒM các ô trống bằng cách sử dụng công thức SUMPRODUCT và COUNTIF

Tuy nhiên, khi sử dụng công thức trên nếu bất kỳ ô nào trong ô trống, công thức có thể gây ra lỗi # DIV / 0. Đó là bởi vì một ô trống sẽ tạo ra 0 trong mảng kết quả được tạo bởi công thức COUNTIF. Vì vậy, khi 1 chia cho 0, nó sẽ dẫn đến lỗi # DIV / 0 như hình dưới đây.

Bạn có thể giải quyết vấn đề này bằng cách thêm một chuỗi trống (“”) làm tiêu chí cho công thức COUNTIF.

Để bao gồm các ô trống hoặc ô trống trong bộ đếm trong khi đếm các giá trị riêng biệt, hãy thử công thức dưới đây:

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

Như bạn có thể thấy ở trên, khi chúng ta nối một chuỗi rỗng (“”) với dữ liệu trong đối số tiêu chí của hàm COUNTIF, nó sẽ trả về 1 cho một ô trống trong mảng kết quả {1;4;1;4;2;1;1;2;4}:. Vì vậy, công thức, 1/COUNTIF(A2:A10,A2:A10&””)= {1;0.25;1;0.25;0.5;1;1;0.5;0.25}. Do đó, kết quả cuối cùng cho các giá trị khác biệt là 6 cũng bao gồm ô trống trong số.

Để bỏ qua các ô trống hoặc ô trống khỏi số lượng trong khi đếm các giá trị riêng biệt, hãy sử dụng công thức dưới đây:

=SUMPRODUCT((A2:A10<>””)/COUNTIF(A2:A10,A2:A10&””))

Tại đây, A2:A10<>””tạo ra một mảng kết quả TRUE hoặc FALSE. Khi một ô trống hoặc trống trong phạm vi, nó sẽ trả về FALSE. Vì vậy, khi giá trị FALSE được chia cho bất kỳ số nào, nó sẽ trả về 0. Kết quả là bây giờ công thức này (A2: A10 <> ””) / COUNTIF (A2: A10, A2: A10 & ””) trả về mảng này: {1 ; 0,25; 1; 0,25; 0,5; 1; 0; 0,5; 0,25}. Khi tổng hợp lại, bạn sẽ nhận được “5” là số cuối cùng.

Đếm các giá trị riêng biệt bằng cách sử dụng các hàm SUM và COUNTIF

Một cách khác bạn có thể tính toán số lượng giá trị duy nhất trong Excel là sử dụng hàm SUM và COUNTIF cùng nhau. Công thức SUM và COUNTIF hoạt động giống như các hàm SUMPRODUCT và COUNTIF. Sự khác biệt duy nhất là đây là một công thức mảng, vì vậy nó cần được thực thi như vậy bằng cách nhấn Ctrl+ Shift+ Enter sau khi nhập công thức.

Cú pháp:

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

Ví dụ:

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

Công thức này hoạt động theo cách tương tự như phương pháp SUMPRODUCT. 1/COUNTIF(A2:A11,A2:A11) trả về mảng kết quả {1;0.33;1;0.33;0.5;1;0.5;0.5;0.33;0.5} này sau đó được tổng hợp bởi hàm SUM và hiển thị các giá trị riêng biệt được đếm là 6.

Khi bạn nhấn Ctrl+ Shift+ Enter chạy công thức, các dấu ngoặc nhọn sẽ được tự động áp dụng.

Bỏ qua các ô trống khỏi số lượng giá trị riêng biệt

Nếu bạn muốn bỏ qua các ô trống hoặc ô trống khi đếm các giá trị riêng biệt và tránh lỗi DIV/0!, bạn cần nhập công thức sau:

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

Công thức trên sẽ bỏ qua các ô trống khỏi số đếm và trả về số lượng các giá trị riêng biệt như được hiển thị bên dưới.

Chỉ đếm các giá trị văn bản riêng biệt bằng cách sử dụng các hàm SUM và COUNTIF

Nếu bạn có danh sách các giá trị văn bản và số trong một cột và bạn chỉ muốn đếm các giá trị riêng biệt là giá trị văn bản, bạn phải nhúng hàm ISTEXT vào công thức SUM và COUNTIF.

Đây là công thức chung để chỉ đếm các giá trị văn bản riêng biệt:

=SUM(IF(ISTEXT(range),1/COUNTIF(range, range),””))

Ví dụ: chúng tôi chỉ muốn đếm các tên thành phố riêng biệt (giá trị văn bản) bỏ qua các giá trị số trong ví dụ dưới đây. Để làm điều đó, hãy sử dụng công thức sau:

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

Đây là công thức mảng, vì vậy hãy đảm bảo nhấn Ctrl+ Shift+ Enter sau khi viết công thức.

Đầu tiên, hàm ISTEXT kiểm tra từng giá trị trong phạm vi dữ liệu A2: A12 xem nó có phải là văn bản hay không và trả về TRUE nếu một giá trị là văn bản và FALSE đối với các giá trị khác. Tiếp theo, nếu một giá trị là TRUE, thì hàm IF sẽ yêu cầu hàm COUNTIF kiểm tra và đếm số lần giá trị đó xuất hiện trong phạm vi dữ liệu đã cho (A2: A12). Nếu giá trị là FALSE, đầu ra sẽ trống. Thao tác này sẽ trả về một mảng kết quả: {1; 3; ””; 3; 2; 1; ””; ””; 2; 3; 1; 1} sau đó được sử dụng làm ước số với 1 là tử số.

Cuối cùng, hàm SUM thêm tất cả các giá trị từ kết quả của 1/COUNTIF(A2:A12, A2:A12)và hiển thị đầu ra dưới dạng 4 giá trị khác biệt.

Chỉ đếm các giá trị số riêng biệt bằng cách sử dụng các hàm SUM và COUNTIF

Trong trường hợp bạn chỉ muốn đếm các số, ngày và giờ riêng biệt từ danh sách các giá trị thì bạn có thể sử dụng công thức chung bên dưới. Công thức này rất giống với công thức trên ngoại trừ bạn sẽ sử dụng hàm ‘ISNUMBER’ thay vì ISTEXT. Ngày và Giờ được lưu trữ dưới dạng số trong Excel, vì vậy chúng cũng sẽ được đưa vào số đếm.

Cú pháp:

=SUM(IF(ISNUMBER(range),1/COUNTIF(range, range),””))

Để đếm các giá trị số riêng biệt (số, ngày và giờ) , hãy viết công thức dưới đây:

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

Đây là một công thức mảng, vì vậy hãy đảm bảo nhấn Ctrl+ Shift+ Enter sau khi nhập công thức để thực thi nó.

Tại đây, ISNUMBER kiểm tra xem mỗi ô trong phạm vi có chứa giá trị số hay không và trả về TRUE nếu giá trị là số. Nếu một giá trị là TRUE, thì hàm COUNTIF sẽ đếm số lần số đó xuất hiện trong phạm vi. Sau đó, kết quả từ hàm COUNTIF được sử dụng làm mẫu số với 1 là tử số.

Cuối cùng, hàm SUM cộng tất cả các giá trị riêng biệt là số và trả về kết quả.

Đếm các giá trị phân biệt chữ hoa chữ thường bằng các hàm SUM, EXACT và COUNTIF

Nếu bạn muốn đếm các giá trị phân biệt có phân biệt chữ hoa chữ thường, bạn phải tạo cột trợ giúp với sự trợ giúp của hàm EXACT thay vì COUNTIF trong công thức mảng để xác định các giá trị riêng biệt trong một phạm vi. Sau đó, một công thức COUNTIF riêng biệt được sử dụng để đếm các giá trị riêng biệt đó.

Để đếm các giá trị khác biệt có phân biệt chữ hoa chữ thường , hãy sử dụng công thức mẫu dưới đây:

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

Vì đây là công thức mảng nên bạn cần nhấn Ctrl+ Shift+ Enter. Sau khi thực thi công thức, hãy sao chép công thức đó vào phần còn lại của các hàng bằng cách sử dụng chốt điền. Đây là cách công thức này hoạt động:

Hàm EXACT kiểm tra từng giá trị so với mọi giá trị trong phạm vi. Và nếu không tìm thấy giá trị chính xác (với các ký tự trường hợp giống nhau) hoặc nếu đó là trường hợp đầu tiên của giá trị, nó sẽ trả về ‘Distinction’, ngược lại nó trả về một ô trống.

Sau khi bạn có các mục nhập “Distinct” trong cột trợ giúp, bạn có thể nhập công thức COUNIF để đếm các mục nhập đó. Để làm điều đó, hãy nhập công thức dưới đây vào một ô trống:

=COUNTIF(B2:B11,”Distinct”)

Bây giờ, bạn đã có số lượng các giá trị phân biệt có phân biệt chữ hoa chữ thường.

Đếm các giá trị riêng biệt bằng các hàm SUM, FREQUENCY và MATCH

Tổ hợp SUM COUNTIF và SUMPRODUCT COUNTIF hoạt động tốt nhất nếu bạn có tập dữ liệu vừa hoặc nhỏ. Tuy nhiên, nếu bạn có một tập dữ liệu lớn, việc sử dụng những kết hợp đó có thể làm chậm quá trình tính toán của bạn và thậm chí có thể khiến file Excel bị treo nếu máy tính laptop không đủ mạnh.

Vì vậy, nếu bạn có một tập dữ liệu lớn, tốt hơn nên sử dụng các hàm FREQUENCY và MATCH để tìm số lượng các giá trị riêng biệt. Hàm tần số trả về số lần xuất hiện của một giá trị trong tập dữ liệu trong khi hàm MATCH trả về vị trí tương đối của giá trị đó trong phạm vi. Khi kết hợp các hàm này có thể trả về số lượng các giá trị riêng biệt.

Ví dụ:

=SUM(IF(FREQUENCY(MATCH(A2:A11,A2:A11,0),MATCH(A2:A11,A2:A11,0))>0,1))

Công thức này thực hiện tương tự như công thức SUM và COUNTIF. Sự khác biệt duy nhất là chúng tôi đang sử dụng các hàm FREQUENCY và MATCH để thay thế. Ở đây, các hàm MATCH là hai đối số của hàm FREQUENCY.

Hàm MATCH trả về vị trí của mỗi giá trị. Sau đó, hàm FREQUENCY kiểm tra số lần xuất hiện cho mỗi giá trị và trả về 1 cho lần xuất hiện đầu tiên của các giá trị và 0 cho các lần xuất hiện còn lại. Kết quả là, hàm FREQUENCY tạo ra một mảng giá trị. Sau đó, mỗi kết quả của mảng được kiểm tra xem nó có ‘> 0’ hay không và trả về 1 nếu nó là TRUE. Cuối cùng, tất cả các giá trị đó được tổng hợp bởi hàm SUM để cung cấp cho chúng ta số lượng các giá trị riêng biệt.

Đếm các hàng riêng biệt bằng cách sử dụng hàm COUNTIFS

Các công thức trên chỉ có thể giúp bạn tìm số giá trị khác biệt trong một cột, nhưng nếu bạn muốn đếm các hàng giá trị khác nhau trên nhiều cột thì sao? Ví dụ: giả sử bạn có một tập dữ liệu trong đó nhiều sinh viên có cùng tên và bạn chỉ muốn đếm các hồ sơ học sinh đặc biệt (các hàng) trong đó ít nhất một trong các tên (tên hoặc họ) khác nhau.

Đếm các hàng riêng biệt tương tự như đếm các giá trị riêng biệt, điểm khác biệt duy nhất là bạn sẽ sử dụng hàm COUNTIFS thay vì COUNTIF. Hàm COUNTIFS trả về số lượng ô đáp ứng một hoặc nhiều điều kiện.

Để đếm các hàng riêng biệt (học sinh) bằng cách sử dụng các giá trị trong cột A (Tên) và cột B (Họ), hãy sử dụng công thức dưới đây:

=SUM(1/COUNTIFS(A2:A12,A2:A12,B2:B12,B2:B12))

Ở đây, COUNTIFS(A2:A12,A2:A12,B2:B12,B2:B12)đếm số lần mỗi hàng xuất hiện trên hai cột và trả về một mảng kết quả {1;3;1;3;1;1;1;2;2;1;3}trong đó nếu ít nhất một trong các giá trị trong hai cột khác nhau, nó sẽ trả về 1. Sau đó, mảng số từ công thức COUNTIFS được sử dụng làm một bộ chia với 1 làm tử số của chúng. Vì vậy, mảng kết quả từ COUNTIFS sẽ trở thành {1;0.33;1;0.33;1;1;1;0.5;0.5;1;0.33}.

Sau đó, hàm SUM tính tổng mảng và xuất ra kết quả: 8.

Đếm giá trị duy nhất bằng công thức trong Excel

Bạn đã thấy cách tính toán các giá trị riêng biệt, bây giờ hãy để chúng tôi xem cách bạn có thể tìm ra số lượng các giá trị duy nhất chỉ xuất hiện một lần trong danh sách. Cũng giống như số lượng các giá trị riêng biệt, bạn có thể nhận được số lượng các giá trị duy nhất bằng cách sử dụng các tổ hợp hàm Excel khác nhau.

Đếm các giá trị duy nhất bằng cách sử dụng các hàm SUM, IF và COUNTIF

Cách dễ nhất để tính toán số lượng các giá trị duy nhất là sử dụng hàm SUM kết hợp với IF và COUNTIF.

Cú pháp :

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

range Phạm vi dữ liệu của danh sách ở đâu mà bạn muốn đếm số lượng giá trị duy nhất .

Ví dụ:

Giả sử bạn có một cột thành phố và bạn chỉ muốn tìm số thành phố chỉ xuất hiện một lần trong danh sách, thì bạn có thể sử dụng công thức sau:

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

Đây là một công thức mảng, vì vậy hãy nhấn Ctrl+ Shift+ Enter để nhập công thức.

Ở đây, hàm lồng nhau COUNTIF(A2:A13,A2:A13)đếm số lần mỗi giá trị xuất hiện trong phạm vi ô (A2: A13) và trả về một mảng số như sau {1;2;1;2;2;3;3;2;1;3;2;2}:. Hàm IFtrả về 1 nếu một giá trị trong mảng kết quả = 1. Nếu điều kiện là FALSE, nó sẽ trả về 0. Điều này sẽ biến mảng được trả về bởi COUNTIF thành một mảng khác như sau: {1; 0; 1; 0; 0 ; 1; 0; 0; 0; 0; 0; 0}. Sau đó, hàm SUM cộng các giá trị trong mảng và cho chúng ta số lượng là ‘3’.

Công thức trên đếm số lượng giá trị duy nhất từ ​​tất cả các loại giá trị (văn bản, số, ngày, v.v.). Nhưng nếu bạn chỉ muốn đếm các giá trị số hoặc văn bản duy nhất, bạn sẽ cần sử dụng các công thức dưới đây.

Chỉ đếm các giá trị văn bản duy nhất bằng cách sử dụng các hàm SUM, COUNTIF và ISTEXT

Nếu bạn chỉ muốn đếm số lượng giá trị văn bản duy nhất trong danh sách có chứa cả giá trị số và văn bản, bạn có thể sử dụng hàm ISTEXT cùng với các hàm SUM và COUNTIF.

Công thức bên dưới chỉ tính các giá trị văn bản duy nhất:

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

Ngoài ra, hãy nhớ nhấn Ctrl+ Shift+ Enter để hoàn thành nó.

• Số COUNTIF(A2:A13,A2:A13)đếm số lần mỗi giá trị xuất hiện trong phạm vi ô (A2: A13) và trả về một mảng {1;2;1;2;2;1;2;2;1;2;1;1}:.
• Và ISTEXThàm trả về TRUE nếu một giá trị trong danh sách là TEXT, ngược lại, nó trả về FALSE.
• Sau đó, toán tử AND (*) được sử dụng để nhân kết quả của cả hai đối số. Nếu một số (bất kỳ số nào) được nhân với FALSE, nó sẽ trả về 0 và nếu một số được nhân với TRUE sẽ trả về cùng một số. (ví dụ: 1 * TRUE = 1, 2 * FALSE = 0, 2 * TRUE = 2)
• Kết quả là, hàm IF sẽ trả về 1 nếu một giá trị là cả văn bản (TRUE) và duy nhất (1), tức là nếu TRUE * 1 = 1, hàm trả về 1, ngược lại là 0. Điều này sẽ tạo ra một mảng: {1;0;1;0;0;1;0;0;0;0;0;1)trong đó 1 là a văn bản và giá trị duy nhất.

Cuối cùng, hàm SUM tính tổng các giá trị trong mảng được IF trả về và hiển thị số lượng giá trị văn bản duy nhất trong cột: 4.

Đếm các giá trị số duy nhất bằng cách sử dụng các hàm SUM, COUNTIF và ISNUMBER

Để chỉ đếm các giá trị số trong danh sách, hãy thêm hàm ISNUMBER thay vì ISTEXT vào công thức mảng ( Ctrl+ Shift+ Enter):

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

Hàm ISNUMBERtrả về TRUE nếu một giá trị trong danh sách là một số, ngược lại, nó trả về FALSE. Số COUNTIF(A2:A13,A2:A13)đếm số lần mỗi giá trị xuất hiện trong phạm vi ô (A2: A13) và trả về mảng này {3;2;1;3;2;1;1;2;2;1;3;1}:.

Khi kết quả của cả hai hàm được nhân lên, hàm IF chỉ trả về 1 nếu một giá trị là số và là duy nhất, nếu không thì là 0. Cuối cùng, hàm SUM cộng các giá trị được trả về bởi hàm IF và đưa ra số lượng các số duy nhất: 2.

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

Trong trường hợp bạn muốn đếm các giá trị duy nhất có phân biệt chữ hoa chữ thường, bạn phải tạo cột trợ giúp với sự trợ giúp của hàm EXACT thay vì COUNTIF trong công thức mảng để xác định các giá trị duy nhất trong phạm vi. Sau đó, bạn có thể sử dụng công thức COUNTIF để đếm các giá trị duy nhất đó từ cột trợ giúp.

Bạn có thể sử dụng công thức mảng sau để đếm các giá trị duy nhất có phân biệt chữ hoa chữ thường:

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

Hoàn thành công thức bằng cách nhấn Ctrl+ Shift+ Enter cùng nhau. Sau đó, kéo chốt điền để áp dụng công thức cho phần còn lại của cột.

Trong công thức trên, hàm EXACT kiểm tra từng giá trị so với mọi giá trị trong phạm vi. Và nếu không tìm thấy giá trị chính xác (với các ký tự chữ hoa chữ thường giống nhau), nó sẽ trả về giá trị ‘Unique’, ngược lại, nó sẽ trả về ‘Duplicate’.

Công thức trên trả về ‘Unique’ trong Cột Trình trợ giúp nếu giá trị tương ứng là duy nhất phân biệt chữ hoa chữ thường, ngược lại, nó trả về ‘Duplicate’. Như bạn có thể thấy cả giá trị “new york” và “portland” đã xuất hiện trong danh sách, tuy nhiên, các giá trị này trong các trường hợp khác nhau. Vì vậy, chúng trở lại là ‘Duy nhất’ trong cột trợ giúp.

Sau đó, bạn có thể nhập công thức COUNIF để đếm các mục nhập đó. Để làm điều đó, hãy nhập công thức dưới đây vào một ô trống:

=COUNTIF(B2:B12,”Unique”)

Bây giờ, bạn đã có số lượng giá trị duy nhất phân biệt chữ hoa chữ thường trong ô D2.

Đếm các hàng duy nhất bằng cách sử dụng hàm COUNTIFS

Đếm các hàng duy nhất tương tự như đếm các giá trị duy nhất, sự khác biệt duy nhất là bạn sẽ sử dụng hàm COUNTIFS thay vì COUNTIF.

Nếu bạn muốn đếm các hàng duy nhất (học sinh) bằng cách sử dụng các giá trị trong cột A (Họ) và cột B (Họ), hãy sử dụng công thức dưới đây:

=SUM(IF(COUNTIFS(A2:A12,A2:A12,B2:B12,B2:B12)=1,1,0))

Tại đây, COUNTIFS(A2:A12,A2:A12,B2:B12,B2:B12) đếm số lần mỗi hàng xuất hiện trong danh sách và trả về một mảng kết quả {1;3;1;3;1;1;1;2;2;1;3} trong đó nếu ít nhất một trong các giá trị (Tên hoặc Họ) trong hai cột là duy nhất, nó sẽ trả về 1. Sau đó, nếu giá trị từ mảng COUNTIFS bằng 1, hàm IF trả về 1, 0 nếu không. Kết quả là, mảng COUNTIFS trở thành {1;0;1;0;1;1;1;0;0;1;0}. Cuối cùng, hàm SUM cộng mảng và xuất ra kết quả là 6.

Đếm các giá trị riêng biệt bằng Pivot Table

Nếu bạn đang sử dụng Excel 2013 hoặc phiên bản mới hơn (bao gồm cả Office 365), bạn có thể đếm các giá trị riêng biệt bằng cách sử dụng bảng tổng hợp. Để đếm các giá trị riêng biệt bằng cách sử dụng bảng tổng hợp, hãy làm theo các bước sau:

Để bắt đầu, hãy chọn phạm vi ô bạn muốn đưa vào bảng tổng hợp, sau đó chuyển đến tab ‘Insert’ và nhấp vào ‘ivot Tab’ từ nhóm Tables.

Khi hộp thoại Tạo PivotTable xuất hiện, hãy chọn xem bạn muốn đặt bảng tổng hợp của mình trong ‘New Worksheet’ hay ‘Existing Worksheet’. Sau đó, chọn hộp ‘Add this data to the Data Model’ và nhấp vào ‘OK’. Nếu bạn chọn đặt bảng tổng hợp trong trang tính hiện tại, hãy chỉ định một phạm vi trong trường vị trí. Ở đây, chúng tôi đang chọn một trang tính mới.

Bạn sẽ thấy ngăn trường PivotTable ở phía bên phải của trang tính. Bây giờ, hãy kéo trường (cột) có số lượng riêng biệt mà bạn muốn tìm vào vùng ‘Values’. Ở đây, chúng tôi muốn tìm các giá trị riêng biệt cho trường ‘Name’. Bạn có thể sắp xếp dữ liệu của mình trong các trường bảng tổng hợp theo bất kỳ cách nào bạn muốn.

Tiếp theo, nhấp vào mũi tên nhỏ bên cạnh ‘Count of Name’ trong trường Giá trị trong ngăn trường Bảng Pivot và chọn tùy chọn ‘Field Settings ..’.

Trong hộp thoại Value Field Settings, cuộn xuống danh sách trong hộp và chọn tùy chọn ‘Distinct Count’ ở cuối danh sách. Sau đó, nhấp vào ‘OK’.

Điều này sẽ hiển thị số lượng các giá trị duy nhất trong bảng tổng hợp như được hiển thị bên dưới.

Đếm các giá trị duy nhất bằng User Defined Function (UDF)

Một cách khác có thể tính toán các giá trị duy nhất là tạo User Defined Function (UDF) và sử dụng hàm đó để đếm các giá trị duy nhất trong danh sách của bạn. Để làm điều đó, hãy làm theo các bước sau:

Đầu tiên, mở Excel, chuyển đến tab ‘Developer’ và nhấp vào nút ‘Visual Basic’ trong nhóm Code.

Thao tác này sẽ khởi chạy cửa sổ VBA. Bây giờ, nhấp chuột phải vào ‘Microsoft Excel Objects’ trong thanh điều hướng ở bên trái, nhấp vào ‘Insert’, sau đó nhấp vào ‘Module’ trong menu phụ (như được hiển thị bên dưới).

Trong cửa sổ mô-đun mới, sao chép và dán mã UDF sau:

Function CountUnique(DataRange As Range, CountBlanks As Boolean) As Integer Dim CellContent As Variant Dim UniqueValues As New Collection Application.Volatile On Error Resume Next For Each CellContent In DataRange If CountBlanks = True Or IsEmpty(CellContent) = False Then UniqueValues.Add CellContent, CStr(CellContent) End If Next CountUnique = UniqueValues.Count End Function

Trong mã UDF ở trên, chúng tôi đã sử dụng tên hàm là CountUnique. Nhưng bạn có thể sử dụng bất kỳ tên nào bạn muốn cho chức năng của mình.

Sau đó, nhấp vào menu ‘File’ và chọn ‘Save’ hoặc chỉ cần nhấn Ctrl+ S.

Bạn đã tạo chức năng mới của riêng mình được gọi là CountUnique.

Sử dụng UDF để đếm các giá trị duy nhất

Bây giờ, bạn có thể sử dụng hàm ‘CountUnique’ (UDF) mới được tạo để đếm các giá trị duy nhất. Như bạn có thể thấy, khi bạn bắt đầu nhập tên UDF, bạn sẽ thấy nó trong các gợi ý công thức như hình dưới đây.

Để đếm các giá trị duy nhất bằng UDF , hãy sử dụng công thức chung dưới đây:

=COUNTUNIQUE(range, count_blanks)

Giải thích

• range là phạm vi ô (cột) mà bạn muốn tính số lượng các giá trị duy nhất.
• count_blanks chỉ định xem bạn có muốn đưa các ô trống vào bộ đếm hay không. Bạn có thể đặt TRUE hoặc FALSE cho đối số này. ĐÚNG nếu bạn muốn bao gồm các ô trống trong số đếm, nếu không thì là FALSE.

Công thức ví dụ:

=CountUnique(A2:A12,FALSE)

Công thức trên kiểm tra xem một giá trị có phải là duy nhất trong phạm vi A2: A12 hay không và đưa giá trị đó vào số lượng. Đối số FALSE cho biết công thức không bao gồm bất kỳ ô trống nào trong số lượng. Nếu bạn muốn bao gồm các ô trống trong số đếm, hãy đặt đối số thứ hai của hàm là TRUE.

Đếm các giá trị duy nhất và khác biệt bằng cách sử dụng các cột trợ giúp với hàm COUNTIF

Bạn cũng có thể đếm cả giá trị duy nhất và khác biệt bằng cách tạo các cột trợ giúp bằng cách sử dụng hàm COUNTIF. Sau đó, sử dụng một hàm COUNTIF khác để đếm các giá trị trong các mục nhập trong cột trình tạo. Đây là một trong những cách dễ nhất để đếm các giá trị duy nhất và khác biệt.

Đối với các giá trị duy nhất, hãy tạo một cột trợ giúp và nhập công thức này vào ô C2:

=COUNTIF($A$2:$A$12,A2)

Sau đó, kéo chốt điền để sao chép công thức vào các ô còn lại. Ở đây, phạm vi $A$2:$A$12 được làm cho lỗi thời, vì vậy nó không thay đổi khi được sao chép. Đảm bảo chỉ tạo phạm vi là tuyệt đối.

Sau khi áp dụng công thức cho toàn bộ cột (C2: C12), nó sẽ kiểm tra từng giá trị so với mọi giá trị trong cột và trả về số lần mỗi giá trị xuất hiện trong cột.

Đối với các giá trị riêng biệt, hãy tạo một cột trợ giúp và nhập công thức này vào ô D2:

=COUNTIF($A$2:A2,A2)

Ở đây, hãy đảm bảo chỉ viết ô đầu tiên của một phạm vi là tuyệt đối. Sau khi nhập công thức vào ô D2, hãy sao chép công thức vào cột D2: D12 bằng cách kéo chốt điền.

Công thức trên trả về số lần xuất hiện của giá trị trong phạm vi. Ví dụ: công thức trong ô D2 kiểm tra số lần giá trị trong ô A2 lặp lại cho đến ô A12 và trả về giá trị xuất hiện. Công thức trong ô D5 kiểm tra số lần giá trị trong A5 lặp lại từ A2 đến A5 và trả về lần xuất hiện của nó (2 lần), v.v. Kết quả là, công thức trả về 1 chỉ cho lần xuất hiện đầu tiên của giá trị.

Để đếm các giá trị duy nhất , hãy nhập công thức sau:

=COUNTIF(C2:C12,1)

Ở đây, phạm vi là C2:C12và tiêu chí là 1. Công thức trên kiểm tra phạm vi C2: C12 cho giá trị 1 và nếu điều kiện là TRUE, nó trả về 1, 0, ngược lại: {1,0; 1; 0; 0; 1; 0; 0; 1; 0; 1} . Sau đó, mảng được thêm lên và xuất ra dưới dạng tổng số các giá trị duy nhất: 4.

Để đếm các giá trị riêng biệt , hãy nhập công thức sau:

=COUNTIF(D2:D12,1)

Ở đây, phạm vi là D2:D12và tiêu chí là 1. Công thức kiểm tra các giá trị 1 và đếm chúng. Kết quả là, bạn sẽ nhận được số lượng các giá trị riêng biệt.