Đếm dữ liệu có điều kiện với hàm SUMPRODUCT (1 người xem)

[luckily176]

Mình có dữ liệu excel ở cột A (Năm) và cột B (tên khách hàng,có trùng lặp) như sau:
Cột A Cột B
1 2009 cty a
2 2009 cty b
3 2009 cty b
4 2009 cty c
5 2009 cty a
6 2009 cty d
7 2010 cty a
8 2010 cty c
9 2010 cty c

Mình muốn tính mỗi năm có bao nhiêu khách hàng mà không trùng lặp để ra kết quả năm 2009 là 4KH, năm 2010 là 2KH
Mình chỉ muốn dùng công thức để tính chứ k dùng các công cụ khác trên Excel. Dùng SUMPRODUCT kết hợp với COUNTIFS thì ntn ah?
Mọi người giúp mình với
CẢM ƠN

 

[ndu96081631]

Mình có dữ liệu excel ở cột A (Năm) và cột B (tên khách hàng,có trùng lặp) như sau:
Cột A Cột B
1 2009 cty a
2 2009 cty b
3 2009 cty b
4 2009 cty c
5 2009 cty a
6 2009 cty d
7 2010 cty a
8 2010 cty c
9 2010 cty c

Mình muốn tính mỗi năm có bao nhiêu khách hàng mà không trùng lặp để ra kết quả năm 2009 là 4KH, năm 2010 là 2KH
Mình chỉ muốn dùng công thức để tính chứ k dùng các công cụ khác trên Excel. Dùng SUMPRODUCT kết hợp với COUNTIFS thì ntn ah?
Mọi người giúp mình với
CẢM ƠN

- Gõ vào E1 số 2009
- Gõ vào E2 số 2010
- Công thức cho F1 là:

=COUNT(1/FREQUENCY(IF($A$1:$A$9=$E1,MATCH($B$1:$B$9,$B$1:$B$9,0)),IF($A$1:$A$9=$E1,MATCH($B$1:$B$9,$B$1:$B$9,0))))

Đây là công thức mảng nên gõ xong phải bấm tổ hợp phím Ctrl + Shift + Enter để kết thúc
- Kéo fill xuống F2
-----------------------
Dạng bài toán này gọi là ĐẾM DUY NHẤT CÓ ĐIỀU KIỆN, phải dùng hàm FREQUENCY để giải quyết
Tham khảo bài viết cùng chủ đề:
http://www.giaiphapexcel.com/forum/...rị-ở-1-cột-khác-là-unique&p=353202#post353202

 

[nguyenhavithao]

- Gõ vào E1 số 2009
- Gõ vào E2 số 2010
- Công thức cho F1 là:

=COUNT(1/FREQUENCY(IF($A$1:$A$9=$E1,MATCH($B$1:$B$9,$B$1:$B$9,0)),IF($A$1:$A$9=$E1,MATCH($B$1:$B$9,$B$1:$B$9,0))))

Đây là công thức mảng nên gõ xong phải bấm tổ hợp phím Ctrl + Shift + Enter để kết thúc
- Kéo fill xuống F2
-----------------------
Dạng bài toán này gọi là ĐẾM DUY NHẤT CÓ ĐIỀU KIỆN, phải dùng hàm FREQUENCY để giải quyết
Tham khảo bài viết cùng chủ đề:
http://www.giaiphapexcel.com/forum/...rị-ở-1-cột-khác-là-unique&p=353202#post353202

Em làm theo HD của bác mà KQ không đúng. Bác xem giúp em file đính kèm

 

[ndu96081631]

Em làm theo HD của bác mà KQ không đúng. Bác xem giúp em file đính kèm

Không ra kết quả vì bạn thiếu động tác bấm Ctrl + Shift + Enter (không đọc kỹ hướng dẫn gì cả)
Giờ đặt chuột vào cell F1 ---> Bấm phím F2 rồi bấm tổ hợp Ctrl + Shift + Enter thử xem

 

[viendo]

Đang tìm hiểu về FREQUENCY nên em dùng cách này với dữ liệu cột B liên tục.
- Gõ vào E1 số 2009
- Gõ vào E2 số 2010

F1 = SUMPRODUCT(1/COUNTIF(OFFSET($C$1,MATCH(E1,$B$1:$B$9,0)-1,,COUNTIF($B$1:$B$9,E1),),OFFSET($C$1,MATCH(E1,$B$1:$B$9,0)-1,,COUNTIF($B$1:$B$9,E1),)))

- Kéo công thức xuống F2
Dùng name động cho 2 thằng OFFSET thì công thức nhìn gọn hơn.

 

[ndu96081631]

Đang tìm hiểu về FREQUENCY nên em dùng cách này với dữ liệu cột B liên tục.
- Gõ vào E1 số 2009
- Gõ vào E2 số 2010

F1 = SUMPRODUCT(1/COUNTIF(OFFSET($C$1,MATCH(E1,$B$1:$B$9,0)-1,,COUNTIF($B$1:$B$9,E1),),OFFSET($C$1,MATCH(E1,$B$1:$B$9,0)-1,,COUNTIF($B$1:$B$9,E1),)))

- Kéo công thức xuống F2
Dùng name động cho 2 thằng OFFSET thì công thức nhìn gọn hơn.

Hàm COUNT nó ngon hơn SUMPRODUCT ở chổ: Nó bỏ qua được bất cứ lỗi nào (ví dụ lỗi DIV/0!, N/A!... vân vân...)
Ngoài ra, dùng FREQUENCY còn có thể đếm duy nhất với nhiều điều kiện hơn nữa... Còn nếu như SUMPRODUCT của bạn mà thêm 1 điều kiện nữa chắc.. die luôn

 

[luckily176]

anh ndu có thể giải thích rõ công thức trên của anh dc k, e thấy rất hay mà chưa hiểu lắm

 

[luckily176]

- gõ vào e1 số 2009
- gõ vào e2 số 2010
- công thức cho f1 là:

=count(1/frequency(if($a$1:$a$9=$e1,match($b$1:$b$9,$b$1:$b$9,0)),if($a$1:$a$9=$e1,match($b$1:$b$9,$b$1:$b$9,0))))

đây là công thức mảng nên gõ xong phải bấm tổ hợp phím ctrl + shift + enter để kết thúc
- kéo fill xuống f2
-----------------------
dạng bài toán này gọi là đếm duy nhất có điều kiện, phải dùng hàm frequency để giải quyết
tham khảo bài viết cùng chủ đề:
http://www.giaiphapexcel.com/forum/showthread.php?56332-dùng-hàm-nào-để-đếm-số-giá-trị-ở-1-cột-với-điều-kiện-giá-trị-ở-1-cột-khác-là-unique&p=353202#post353202

nếu dữ liệu có ô rỗng thì công thức này k áp dụng đc rồi anh ah. Trong trường hợp có ô rỗng a có giải pháp nào k ah?

 

[ndu96081631]

nếu dữ liệu có ô rỗng thì công thức này k áp dụng đc rồi anh ah. Trong trường hợp có ô rỗng a có giải pháp nào k ah?

Làm gần hết cả rồi, giờ chỉ còn mỗi cái RỔNG nữa thôi... Tự nghiên cứu đi! (thêm 1 vài cái IF gì gì đó)