Hàm đếm dữ liệu trùng nhau và có điều kiện, (1 người xem)

[majoson]

Chào anh, chị,

Tôi có một vấn đề là: Tìm lượng khách hàng theo tháng và không trùng nhau. Xin giúp đỡ công thức tính.

Xin cám ơn,

 

[viendo]

Tạm sử dụng công thức sau tại F4 và fill xuống nhé

F4 = SUMPRODUCT(1/COUNTIF(OFFSET($B$3,MATCH(E4,$B$4:$B$100,0),1,COUNTIF($B$3:$B$100,E4),),OFFSET($B$3,MATCH(E4,$B$4:$B$100,0),1,COUNTIF($B$3:$B$100,E4),)))

 

[majoson]

Tạm sử dụng công thức sau tại F4 và fill xuống nhé

Cám ơn bác Viendo nhiều nha. Bài toán ban đầu mình đưa ra một điều kiện tháng mà quên mất nó còn 02 cột điều kiện năm và quý nữa. Bác help thêm chút nữa nhé.

 

[majoson]

Bác viendo ơi, bác loại trừ giúp trường hợp trong cột customer có dòng trống nữa nhé bác. Nếu có dòng trống bác loại trừ lỗi #DIV/0 dùm tôi luôn nhé.

 

[viendo]

Có lẽ bạn phải đưa dữ liệu thật lên để xem cách bố trí dữ liệu và báo cáo thế nào thì mọi người dễ giúp bạn

 

[majoson]

Cám ơn bác viendo. Bác thật tốt ah. Bác thêm dùm tôi cái điều kiện để loại trừ lỗi chia cho 0 nữa với nhé. Thỉnh thoảng trong cột khách hàng là dòng trống thì nó sẽ báo lỗi #DIV/0!. Cám ơn bác nhé.

 

[ndu96081631]

Có lẽ bạn phải đưa dữ liệu thật lên để xem cách bố trí dữ liệu và báo cáo thế nào thì mọi người dễ giúp bạn

Bài toán đếm duy nhất có điều kiện đã nói nhiều rồi mà! ---> Dùng FREQUENCY nha
1> Công thức tại H4

=COUNT(1/FREQUENCY(IF(($B$4:$B$100=$G$2)*($D$4:$D$100=$G4),IF($E$4:$E$100<>"",MATCH($E$4:$E$100,$E$4:$E$100,0))),IF(($B$4:$B$100=$G$2)*($D$4:$D$100=$G4),IF($E$4:$E$100<>"",MATCH($E$4:$E$100,$E$4:$E$100,0)))))

Kéo fill xuống H7
2> Công thức tại H8:

=COUNT(1/FREQUENCY(IF(($B$4:$B$100=$G$2)*($C$4:$C$100=$G8),IF($E$4:$E$100<>"",MATCH($E$4:$E$100,$E$4:$E$100,0))),IF(($B$4:$B$100=$G$2)*($C$4:$C$100=$G8),IF($E$4:$E$100<>"",MATCH($E$4:$E$100,$E$4:$E$100,0)))))

Ưu điểm:
- Công thức duy nhất, không cần bất cứ name nào và cũng không cần dữ liệu phải sort trước
- Bao nhiêu điều kiện cũng chơi được hết
- Chấp luôn dữ liệu có cell rổng

 

[bemyself]

Cám ơn bác viendo. Bác thật tốt ah. Bác thêm dùm tôi cái điều kiện để loại trừ lỗi chia cho 0 nữa với nhé. Thỉnh thoảng trong cột khách hàng là dòng trống thì nó sẽ báo lỗi #DIV/0!. Cám ơn bác nhé.

Dùng ISNA hoặc ISERROR để loại bỏ lỗi là xong mà. gọi cái công thức của bác VIENDO là A thì khi thêm ISERROR se là =if(iserror(A),"",A)

 

[majoson]

Hay quá thầy Dũng ơi. Em cám ơn thầy nhiều nha. Em làm được như mong muốn rồi. Cái này trước đây vì nghĩ không ra công thức nên em phải chạy cái Pivot rồi mỗi lần cập nhật là refresh cực dễ sợ luôn. Em cám ơn thầy nha.

 

[majoson]

Àh, nếu mình sài cái hàm này thì nó sẽ cho giá trị rỗng luôn. To @bemyself

 

[ndu96081631]

Hay quá thầy Dũng ơi.

Hổng biết thầy DŨNG là cha nào vậy ta?
Spam... đầu tuần
Ẹc... Ẹc...

 

[phihndhsp]

Hay quá thầy Dũng ơi. Em cám ơn thầy nhiều nha. Em làm được như mong muốn rồi. Cái này trước đây vì nghĩ không ra công thức nên em phải chạy cái Pivot rồi mỗi lần cập nhật là refresh cực dễ sợ luôn. Em cám ơn thầy nha.

Tôi nghĩ cái này dùng Pivot thì hay đó, dữ liệu nhiều chạy cũng nhanh nữa và cách làm cũng rất đơn giản . Nếu sợ mỗi lần cập nhật thì refesh, thì bạn nên viết 1 macro tự động refresh. Công thức của anh ndu rất hay nhưng sợ dùng công thức mảng nếu dữ liệu nhiều sẽ chậm. Chú ý bạn rõ 1 tí nha, người viết ra công thức này là Anh ndu chứ không phải anh dũng đâu.

 

[ndu96081631]

Tôi nghĩ cái này dùng Pivot thì hay đó, dữ liệu nhiều chạy cũng nhanh nữa và cách làm cũng rất đơn giản . .

Tôi cũng từng nghĩ sẽ dùng PivotTable nhưng chẳng biết bố trí cách nào cho giống với form của tác giả
Hay bạn làm thử 1 cái gửi lên đây cho tôi học hỏi với

 

[phihndhsp]

Tôi cũng từng nghĩ sẽ dùng PivotTable nhưng chẳng biết bố trí cách nào cho giống với form của tác giả
Hay bạn làm thử 1 cái gửi lên đây cho tôi học hỏi với

Dạ đúng rồi Pivot không có cách bố trí nào giống như của tác giả. Ý em là làm sao cho nó nhanh và dễ làm thôi.

 

[majoson]

Bài toán đếm duy nhất có điều kiện đã nói nhiều rồi mà! ---> Dùng FREQUENCY nha
1> Công thức tại H4

=COUNT(1/FREQUENCY(IF(($B$4:$B$100=$G$2)*($D$4:$D$100=$G4),IF($E$4:$E$100<>"",MATCH($E$4:$E$100,$E$4:$E$100,0))),IF(($B$4:$B$100=$G$2)*($D$4:$D$100=$G4),IF($E$4:$E$100<>"",MATCH($E$4:$E$100,$E$4:$E$100,0)))))

Kéo fill xuống H7
2> Công thức tại H8:

=COUNT(1/FREQUENCY(IF(($B$4:$B$100=$G$2)*($C$4:$C$100=$G8),IF($E$4:$E$100<>"",MATCH($E$4:$E$100,$E$4:$E$100,0))),IF(($B$4:$B$100=$G$2)*($C$4:$C$100=$G8),IF($E$4:$E$100<>"",MATCH($E$4:$E$100,$E$4:$E$100,0)))))

Ưu điểm:
- Công thức duy nhất, không cần bất cứ name nào và cũng không cần dữ liệu phải sort trước
- Bao nhiêu điều kiện cũng chơi được hết
- Chấp luôn dữ liệu có cell rổng

Hiz, thầy ơi. Em làm cái công thức mảng của thầy mà máy nó chạy như rùa ah. Vì dữ liệu của em hơn 30,000 dòng nên nó chậm quá. Thầy có cách nào khác để nó tính nhanh hơn không thầy! Em sài laptop Core i3.

 

[ditimdl]

Theo ý mình bạn nên filter advanced để loại những khách hàng trùng nhau trong tháng/quý, trích dữ liệu ra một sheet khác rồi mới đếm chắc sẽ nhẹ nhàng hơn rất nhiều.
Với excel >20 000 dòng đã là chậm rồi chứ nói gì 30 000 dòng.

 

[ndu96081631]

Hiz, thầy ơi. Em làm cái công thức mảng của thầy mà máy nó chạy như rùa ah. Vì dữ liệu của em hơn 30,000 dòng nên nó chậm quá. Thầy có cách nào khác để nó tính nhanh hơn không thầy! Em sài laptop Core i3.

Thì đương nhiên rồi.. Công thức mảng nó phải vậy thôi
Dữ liệu nhiều, bạn nên chọn phương án khác: PivotTable hoặc lập trình VBA

 

[majoson]

à, cái này không phải là em muốn lấy kết quả đó ra. Nếu muốn lấy ra thì như anh nói hoặc Pivot sau đó dùng hàm là ra liền. Cái chính là nó nằm trong data và cái kết quả của nó bị chia bởi một công thức khác nữa. Nói chung là, em chỉ có thể tìm công thức nào đó để ra được thôi. Cám ơn anh ditimdl nhé.

 

[nguyenmanhnam]

Em tập làm Pivot table nhưng vẫn phải dùng hàm Count sau khi tổng hợp, không biết có cách nào làm nhanh hơn không ah

 

[nguyenmanhnam]

Thì đương nhiên rồi.. Công thức mảng nó phải vậy thôi
lập trình VBA

Phiền thày chỉ giúp em phương pháp này (VBA), em cũng đang cần bài toán này.

 

[mynguyen1207]

Dạ em có một ví dụ nhờ các bác hổ trợ nhé .
Đếm số đơn hàng ( loại bỏ trùng nhau) nhưng với điều kiện là mã khách hàng rỗng và theo ngày

 

[quocgiacan]

Dạ em có một ví dụ nhờ các bác hổ trợ nhé .
Đếm số đơn hàng ( loại bỏ trùng nhau) nhưng với điều kiện là mã khách hàng rỗng và theo ngày

Chào mynguyen1207,

Bạn có thể xem tại đây có hướng dẫn và làm theo nha.
http://www.giaiphapexcel.com/forum/...gày-có-loại-bỏ-sự-lặp-lại&p=733205#post733205

Chúc bạn ngày vui

 

[khuongvietphong]

Dạ em có một ví dụ nhờ các bác hổ trợ nhé .
Đếm số đơn hàng ( loại bỏ trùng nhau) nhưng với điều kiện là mã khách hàng rỗng và theo ngày

Nếu chưa làm được nữa thì bạn có thể dùng CT này :

=SUMPRODUCT((D2:D11="")/COUNTIFS(B2:B11,B2:B11,A2:A11,A2:A11))

 

[mynguyen1207]

Dạ cảm ơn mọi người nhé!

 

[mynguyen1207]

Dear all!
em cũng xem những hướng dẫn nhưng làm không được, không biết em thao tác đúng khô g nữa. Nhờ mọi người hướng dẫn em thêm nhé

 

[Ba Tê]

Dear all!
em cũng xem những hướng dẫn nhưng làm không được, không biết em thao tác đúng khô g nữa. Nhờ mọi người hướng dẫn em thêm nhé

Có lẽ bạn xài Excel 2003 nên không thể dùng CountIfs().
Nếu dữ liệu của bạn chắc chắn 1 mã đơn hàng không thể có 2 ngày khác nhau, và 1 mã đơn hàng không thể vừa có Mã KH vừa có trống thì xem file này