Công thức này là để đếm dữ liệu không trùng trong range: B4:B1317 chứ không phải đếm hàm bạn ah.
và nó hoạt động như sau:
B4:B1317<>"" = tạo 1 mảng có dạng TRUE,FALSE (từ B4:B1317 nếu có cell trống thì =FALSE còn không thì bằng TRUE).(Mảng 1)
COUNTIF(B4:B1317,B4:B1317&"") = tạo 1 mảng có các phần tử từ hàm countif:
Ví dụ: COUNTIF(B4:B1317,B4&"")=1
COUNTIF(B4:B1317,B5&"")=2
COUNTIF(B4:B1317,B6&"")=2
......
đến COUNTIF(B4:B1317,B1317&"")
mảng có dạng như sau: {1,2,2,....} (Mảng 2)
Sau đó lấy mảng 1 chia cho mảng 2:
Mảng 1 = {TRUE,TRUE,TRUE,FALSE,....}
Mảng 2 = {1,2,2,1,....}
Ta được mảng có dạng như sau:
{1,0.5,0.5,0,....}
Dùng Sumproduct cộng các phần tử của mảng trên lại sẽ được đáp số của bài toán.
Để hiểu công thức này, bạn cần hiểu cách hoạt động của hàm COUNTIF và SUMPRODUCT
COUNTIF(Mảng, Mảng) là đếm từng phần tử trong mảng xem xuất hiện bao nhiêu lần trong mảng.
Nếu lấy 1/COUNTIF(Mảng, Mảng) và tổng cộng lại sẽ ra số phần tử không trùng
=SUMPRODUCT(1/COUNTIF(Mảng, Mảng))
Biến tấu công thức này ra để loại trừ các ô trống, sẽ ra công thức mà bạn hỏi.
Bạn tham khảo thêm tại đây:http://www.giaiphapexcel.com/forum/...mảng-SUM(1-COUNTIF(B2-B12-B2-B12))-trong-file
Cho mình hỏi là mình dùng excel 2010 mảng 1 mình kiểm tra thì kết quả ra #Value chứ không phải true false. Hàm 2 thì kiểm tra ok. Liệu máy mình có nhập sai gì không nhỉ?
Để dễ hiểu hơn cái chuyện đếm dữ liệu không tính trùng ở 1 range trong excel thì các bạn cần hiểu được phép logic như thế này.
Giả sử mình có 1 mảng dọc gồm 6 ô. Chứa dữ liệu {1;2;2;5;5;5}
Hướng suy nghĩ để đếm được số dữ liệu trong mảng trên không trùng là: làm cách nào để chuyển nó về thành mảng:{1;0.5;0.5;0.33;0.33;0.33} (Để dùng hàm sum tính tổng của mảng này sẽ ra kết quả cần tìm- Ở đây 1+0.5+0.5+0.33x3=3) nghĩa là các range chứa số {2;2} và {5;5;5} phải thành ô chứa số {0.5;0.5} và {0.33;0.33;0.33}.
Để ý các bạn sẽ thấy các giá trị 0.5 và 0.33 được tính từ công thức =1/(số lần lặp của 2 hoặc 5).
Nếu điều mình trình bày trên các bạn thông suốt thì việc viết hàm không khó nữa. Nó có thể là: =Sum(1/countif(vùng,vùng)) (Với countif(vùng,vùng) là công thức dùng để đếm số lần lặp của phần tử trong mảng).
Hoặc có thể viết: = Sumproduct(1/countif(vùng,vùng)) 2 công thức trên sẽ cho kết quả như nhau. (Vì sao thì các bạn cần tìm hiểu thêm về hàm sumproduct)
------------------------------------------ Nếu trong mảng đếm của chúng ta có xuất hiện ô rỗng (không chứa dữ liệu) thì lúc này chúng ta phải kèm theo điều kiện để tránh bị phép tính 1/0 báo lỗi. (Nói cách khác chúng ta cần loại bỏ các ô rỗng đi là được). Lúc này bạn có thể xài sum, hoặc sum với if để loại ô rỗng hoặc dùng công thức đầu bài. =sum((vùng<>"")*(1/countif(vùng,vùng&"")))
=sum(If(vùng="",0,1/countif(vùng,vùng)))
=sumproduct((vùng<>"")*(1/countif(vùng,vùng&""))) Gỉai thích: {vùng&""} nghĩa là tạo 1 mảng như vùng thêm 1 cell là rỗng.
