Công thức chung
=SUMPRODUCT(--(FREQUENCY(MATCH(data,data,0),ROW(data)-ROW(data.firstcell)+1)>0))
Tóm lược
Để đếm các giá trị văn bản duy nhất trong một phạm vi, bạn có thể sử dụng công thức sử dụng một số hàm: FREQUENCY, MATCH, ROW và SUMPRODUCT. Trong ví dụ được hiển thị, công thức trong F5 là:
=SUMPRODUCT(--(FREQUENCY(MATCH(B5:B14,B5:B14,0),ROW(B5:B14)-ROW(B5)+1)>0))
trả về 4, vì có 4 tên duy nhất trong B5: B14.
Lưu ý: Một cách khác để đếm các giá trị duy nhất là sử dụng hàm COUNTIF. Đây là một công thức đơn giản hơn nhiều, nhưng nó có thể chạy chậm trên các tập dữ liệu lớn. Với Excel 365, bạn có thể sử dụng công thức đơn giản hơn và nhanh hơn dựa trên UNIQUE.
Giải trình
Công thức này phức tạp hơn một công thức tương tự sử dụng FREQUENCY để đếm các giá trị số duy nhất vì FREQUENCY không hoạt động với các giá trị không phải số. Do đó, một phần lớn của công thức chỉ đơn giản là chuyển đổi dữ liệu không phải số thành dữ liệu số mà FREQUENCY có thể xử lý.
Làm việc từ trong ra ngoài, hàm MATCH được sử dụng để lấy vị trí của từng mục xuất hiện trong dữ liệu:
MATCH(B5:B14,B5:B14,0)
Kết quả từ MATCH là một mảng như sau:
(1;1;1;4;4;6;6;6;9;9)
Bởi vì MATCH luôn trả về vị trí của kết quả phù hợp đầu tiên, các giá trị xuất hiện nhiều lần trong dữ liệu sẽ trả về cùng một vị trí. Ví dụ: vì "Jim" xuất hiện 3 lần trong danh sách nên anh ta xuất hiện trong mảng này 3 lần với tư cách là số 1.
Mảng này được đưa vào FREQUENCY làm đối số data_array . Đối số bins_array được xây dựng từ phần này của công thức:
ROW(B5:B14)-ROW(B5)+1)
tạo danh sách tuần tự các số cho từng giá trị trong dữ liệu:
(1;2;3;4;5;6;7;8;9;10)
Tại thời điểm này, FREQUENCY được định cấu hình như sau:
FREQUENCY((1;1;1;4;4;6;6;6;9;9),(1;2;3;4;5;6;7;8;9;10))
FREQUENCY trả về một mảng số cho biết số lượng cho mỗi số trong mảng dữ liệu, được sắp xếp theo bin. Khi một số đã được đếm, FREQUENCY sẽ trả về 0. Đây là một tính năng chính trong hoạt động của công thức này. Kết quả từ FREQUENCY là một mảng như thế này:
(3;0;0;2;0;3;0;0;2;0;0) // output from FREQUENCY
Lưu ý: FREQUENCY luôn trả về một mảng có nhiều mục hơn bins_array .
Bây giờ chúng ta có thể viết lại công thức như sau:
=SUMPRODUCT(--((3;0;0;2;0;3;0;0;2;0;0)>0))
Tiếp theo, chúng tôi kiểm tra các giá trị lớn hơn 0 (> 0), các giá trị này sẽ chuyển các số thành TRUE hoặc FALSE, sau đó sử dụng âm kép (-) để chuyển các giá trị TRUE và FALSE thành 1s và 0s. Bây giờ chúng tôi có:
=SUMPRODUCT((1;0;0;1;0;1;0;0;1;0;0))
Cuối cùng, SUMPRODUCT chỉ cần thêm các số lên và trả về tổng, trong trường hợp này là 4.
Xử lý các ô trống
Các ô trống trong phạm vi sẽ khiến công thức trả về lỗi # N / A. Để xử lý các ô trống, bạn có thể sử dụng công thức mảng phức tạp hơn sử dụng hàm IF để lọc ra các giá trị trống:
(=SUM(IF(FREQUENCY(IF(data"", MATCH(data,data,0)),ROW(data)-ROW(data.firstcell)+1),1)))
Lưu ý: thêm IF làm cho điều này thành một công thức mảng yêu cầu điều khiển-shift-enter.
Để biết thêm thông tin, hãy xem trang này.
Các cách khác để đếm các giá trị duy nhất
Nếu bạn có Excel 365, bạn có thể sử dụng hàm UNIQUE để đếm các giá trị duy nhất với một công thức đơn giản hơn nhiều.
Bảng tổng hợp cũng là một cách tuyệt vời để đếm các giá trị duy nhất.
