Công thức chung
(=SUM(--(FREQUENCY(IF(criteria,values),values)>0)))
Tóm lược
Để đếm các giá trị số duy nhất trong một phạm vi, bạn có thể sử dụng công thức dựa trên các hàm FREQUENCY, SUM và IF. Trong ví dụ được hiển thị, số nhân viên xuất hiện trong phạm vi B5: B14. Công thức trong G6 là:
=SUM(--(FREQUENCY(IF(C5:C14="A",B5:B14),B5:B14)>0))
trả về 2, vì có 2 id nhân viên duy nhất trong tòa nhà A.
Lưu ý: đây là công thức mảng và phải được nhập bằng control + shift + enter, trừ khi bạn đang sử dụng Excel 365.
Giải trình
Lưu ý: Trước Excel 365, Excel không có chức năng chuyên dụng để đếm các giá trị duy nhất. Công thức này hiển thị một cách để đếm các giá trị duy nhất, miễn là chúng là số. Nếu bạn có giá trị văn bản hoặc kết hợp văn bản và số, bạn sẽ cần sử dụng một công thức phức tạp hơn.
Hàm FREQUENCY trong Excel trả về phân phối tần số, là bảng tóm tắt có chứa tần suất của các giá trị số, được tổ chức theo "thùng". Chúng tôi sử dụng nó ở đây như một cách quay vòng để đếm các giá trị số duy nhất. Để áp dụng tiêu chí, chúng tôi sử dụng hàm IF.
Làm việc từ trong ra ngoài, trước tiên chúng tôi lọc các giá trị bằng hàm IF:
IF(C5:C14="A",B5:B14) // filter on building A
Kết quả của thao tác này là một mảng như sau:
(905;905;905;905;773;773;FALSE;FALSE;FALSE;FALSE)
Lưu ý rằng tất cả các id trong tòa nhà B bây giờ là FALSE. Mảng này được phân phối trực tiếp đến chức năng TẦN như data_array . Đối với bins_array , chúng tôi tự cung cấp id:
FREQUENCY((905;905;905;905;773;773;FALSE;FALSE;FALSE;FALSE),(905;905;905;905;773;773;801;963;963;963))
Với cấu hình này, FREQUENCY trả về mảng bên dưới:
(4;0;0;0;2;0;0;0;0;0;0)
Kết quả là một chút khó hiểu, nhưng ý nghĩa là 905 xuất hiện bốn lần, và 773 xuất hiện hai lần. Các giá trị FALSE sẽ tự động bị bỏ qua.
FREQUENCY có một tính năng đặc biệt tự động trả về số 0 cho bất kỳ số nào đã xuất hiện trong mảng dữ liệu, đó là lý do tại sao các giá trị bằng 0 khi gặp một số. Đây là tính năng cho phép cách tiếp cận này hoạt động.
Tiếp theo, mỗi giá trị này được kiểm tra để lớn hơn 0:
(4;0;0;0;2;0;0;0;0;0;0)>0
Kết quả là một mảng như thế này:
(TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE)
Mỗi TRUE trong danh sách đại diện cho một số duy nhất trong danh sách và chúng ta chỉ cần cộng các giá trị TRUE với SUM. Tuy nhiên, SUM sẽ không cộng các giá trị logic trong một mảng, vì vậy trước tiên chúng ta cần ép các giá trị thành 1 hoặc 0. Điều này được thực hiện với âm kép (-). Kết quả là một mảng chỉ gồm 1 hoặc 0:
(1;0;0;0;1;0;0;0;0;0;0)
Cuối cùng, SUM cộng các giá trị này lên và trả về tổng, trong trường hợp này là 2.
Nhiều tiêu chí
Bạn có thể mở rộng công thức để xử lý nhiều tiêu chí như sau:
(=SUM(--(FREQUENCY(IF((criteria1)*(criteria2),values),values)>0)))
