Công thức chung
(=SUM(--(FREQUENCY(IF(criteria,MATCH(vals,vals,0)),ROW(vals)-ROW(vals.first)+1)>0)))
Tóm lược
Để đếm các giá trị văn bản duy nhất trong một phạm vi có tiêu chí, bạn có thể sử dụng công thức mảng dựa trên các hàm FREQUENCY và MATCH. Trong ví dụ được hiển thị, công thức trong G6 là:
(=SUM(--(FREQUENCY(IF(C5:C11=G5,MATCH(B5:B11,B5:B11,0)),ROW(B5:B11)-ROW(B5)+1)>0)))
trả về 3, vì ba người khác nhau đã làm việc trong dự án Omega.
Lưu ý: đây là công thức mảng và phải được nhập bằng control + shift + enter.
Giải trình
Đây là một công thức phức tạp sử dụng FREQUENCY để đếm các giá trị số có nguồn gốc từ hàm MATCH. Làm việc từ trong ra ngoài, hàm MATCH được sử dụng để lấy vị trí của từng giá trị xuất hiện trong dữ liệu:
MATCH(B5:B11,B5:B11,0)
Kết quả từ MATCH là một mảng như sau:
(1;1;3;1;1;6;7)
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 4 lần trong danh sách nên anh ta xuất hiện trong mảng này 4 lần với tư cách là số 1.
Bên ngoài hàm MATCH, hàm IF được sử dụng để áp dụng tiêu chí, trong trường hợp này liên quan đến việc kiểm tra xem dự án có phải là "omega" (từ ô G5) hay không:
IF(C5:C11=G5 // filter on "omega"
Hàm IF hoạt động giống như một bộ lọc, chỉ cho phép các giá trị từ MATCH đi qua nếu chúng được liên kết với "omega". Kết quả là một mảng như thế này:
(FALSE;FALSE;FALSE;1;1;6;7) // after filtering
Mảng lọc được phân phối trực tiếp đến chức năng TẦN như data_array tranh cãi. Tiếp theo, hàm ROW được sử dụng để xây dựng danh sách tuần tự các số cho từng giá trị trong dữ liệu:
ROW(B3:B12)-ROW(B3)+1
Điều này tạo ra một mảng như thế này:
(1;2;3;4;5;6;7;8;9;10)
mà trở thành đối số bins_array trong FILTER. Tại thời điểm này, chúng tôi có:
FREQUENCY((FALSE;FALSE;FALSE;1;1;6;7),(1;2;3;4;5;6;7))
FREQUENCY trả về một mảng số cho biết số lượng cho mỗi giá trị trong mảng dữ liệu, được sắp xếp theo bin. Khi một số đã được đếm, FREQUENCY sẽ trả về 0. Kết quả từ FREQUENCY là một mảng như thế này:
(2;0;0;0;0;1;1;0) // result from FREQUENCY
Lưu ý: FREQUENCY luôn trả về một mảng có nhiều mục hơn bins_array .
Tại thời điểm này, chúng ta có thể viết lại công thức như sau:
=SUM(--((2;0;0;0;0;1;1;0)>0))
Chúng tôi kiểm tra các giá trị lớn hơn 0, các giá trị này sẽ chuyển các số thành TRUE hoặc FALSE:
=SUM(--((TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE)))
Sau đó, chúng tôi sử dụng phủ định kép để buộc các giá trị logic thành 1s và 0s:
=SUM((1;0;0;0;0;1;1;0))
Cuối cùng, hàm SUM trả về 3 là kết quả cuối cùng.
Lưu ý: đây là công thức mảng và phải được nhập bằng Control + Shift + Enter.
Xử lý các ô trống trong phạm vi
Nếu bất kỳ ô nào trong phạm vi trống, bạn sẽ cần điều chỉnh công thức để ngăn các ô trống được chuyển vào hàm MATCH, điều này sẽ gây ra lỗi. Bạn có thể thực hiện việc này bằng cách thêm một hàm IF lồng nhau khác để kiểm tra các ô trống:
(=SUM(--(FREQUENCY(IF(B5:B11"",IF(C5:C11=G5,MATCH(B5:B11,B5:B11,0))),ROW(B5:B11)-ROW(B5)+1)>0)))
Với hai tiêu chí
Nếu bạn có hai tiêu chí, bạn có thể mở rộng logic của công thức bằng cách thêm IF lồng nhau khác:
(=SUM(--(FREQUENCY(IF(c1,IF(c2,MATCH(vals,vals,0))),ROW(vals)-ROW(vals.1st)+1)>0)))
Trong đó c1 = tiêu chí1 , c2 = tiêu chí2 và vals = phạm vi giá trị.
Với logic boolean
Với logic boolean, bạn có thể giảm các IF lồng nhau:
(=SUM(--(FREQUENCY(IF((criteria1)*(criteria2),MATCH(vals,vals,0)),ROW(vals)-ROW(vals.1st)+1)>0)))
Điều này giúp dễ dàng thêm và quản lý các tiêu chí bổ sung.
