SUMPRODUCT và Công thức mảng - Phép tính có nhiều điều kiện

[batman1]

Hi các bác. Mình đang có vấn đề cần giải quyết.

Mình cần thống kê ở sheet "Theo dõi".
- Ở cell B2 mình cần biết NV bán được cho bao nhiêu khách hàng, nhưng công thức đang chạy sai. Ngoài ra nếu ở ô này mình cần biết số KH nv này bán được (Tổng doanh số KH >= 10 đồng) thì làm như thế nào?
- Ở ô C2 mình cần theo dõi số tiền mặt hàng trọng điểm 1 bán được (Mặt hàng trọng điểm 1 gồm có: Mặt hàng trọng điểm 1A và Mặt hàng trọng điểm 1B trong sheet "Dữ liệu nền".

View attachment 206204

Ai biết chỉ dùm mình hoặc cho mình ví dụ giống giống để mình xem

Cảm ơn all.

Nếu tôi hiểu ý và không nhầm lẫn thì ...
1. Tôi đổi tên sheet thành csdl và nguon.
2. Công thức cho B2

=SUM(--(FREQUENCY(IF(nguon!$A$2:$A$200=A2;MATCH(nguon!$B$2:$B$200;nguon!$B$2:$B$200;0));ROW($1:$200))>0))

Kết thúc bằng Ctrl+Shift+Enter, và copy xuống dưới.

Công thức cho C2

=SUM((nguon!$A$2:$A$31=$A2)*COUNTIFS(csdl!$A$2:$A$11;nguon!$C$2:$C$31;csdl!$D$2:$D$11;C$1&"?")*nguon!$E$2:$E$31)

Kết thúc bằng Ctrl+Shift+Enter, và copy sang phải tới cột D rồi xuống dưới.

 

[trum_so]

Thanks bác Phạm Thế Hiệp.

1. Đếm số KH mình đã làm được, nhưng nếu thòng điều kiện (Ví dụ như KH phải có Doanh số lớn hơn 10 mới được tính là 1 KH phát sinh doanh số) thì mình chưa làm được.

2. Theo dõi "Hàng trọng điểm" theo công thức bạn:

C2=COUNT(SEARCH(C$1,VLOOKUP(T(IF((Input!$A$2:$A$31=$A2)*(Input!$D$2:$D$31="Bán"),Input!$C$2:$C$31)),Database!$A$2:$D$11,4,)))

thì nó sẽ sai khi Mã SP là 1 số, theo mình hiểu là trong dòng lệnh T(....) bên trên.
Cho mình hỏi dòng T bên trên nó có cần thiết không?

 

[trum_so]

Thanks bác batman1.

Cái mục "2." mình làm theo bác thì được.

Còn mục "1." mình vẫn vướng theo điều kiện doanh số nhập của KH

 

[batman1]

Cảm ơn bác batman1.

Cái mục "2." mình làm theo bác thì được.

Còn mục "1." mình vẫn vướng theo điều kiện doanh số nhập của KH

Tức chỉ KH mua >= 10 mới được đeo huy hiệu?

1. Hoặc con bò mộng cho B2

=SUM(--(FREQUENCY(IF(nguon!$A$2:$A$200=A2,IF(SUMIFS(nguon!$E$2:$E$200,nguon!$A$2:$A$200,nguon!$A$2:$A$200,nguon!$B$2:$B$200,nguon!$B$2:$B$200)>=10,MATCH(nguon!$B$2:$B$200,nguon!$B$2:$B$200,0))),ROW($1:$200))>0))

Kết thúc bằng Ctrl+Shift+Enter

2. Hoặc cột phụ.
Công thức cho nguon!F2

=IF(SUMIFS($E$2:$E$200,$A$2:$A$200,A2,$B$2:$B$200,B2)>=10,MATCH(nguon!$B$2:$B$200,nguon!$B$2:$B$200,0))

Enter và copy xuống đến F200

Công thức cho B2

=SUM(--(FREQUENCY(IF(nguon!$A$2:$A$200=A2,nguon!$F$2:$F$200),ROW($1:$200))>0))

Kết thúc bằng Ctrl+Shift+Enter

Tôi chỉ nhìn lướt qua kết quả cho dữ liệu hiện có trong tập tin của bạn. Tôi không test nhiều trường hợp vì vấn đề là của bạn.

 

[Phan Thế Hiệp]

1. Đếm số KH mình đã làm được, nhưng nếu thòng điều kiện (Ví dụ như KH phải có Doanh số lớn hơn 10 mới được tính là 1 KH phát sinh doanh số) thì mình chưa làm được.

1/ Doanh số lớn hơn 10: thì thêm 1 chút công thức vào phía sau công thức cũ, như dười đây:

=COUNT(1/(MATCH(Input!$B$2:$B$100,IF(Input!$A$2:$A$100=$A3,Input!$B$2:$B$100),)=ROW($1:$100))/(SUMIFS(Input!$E$2:$E$31,Input!$A$2:$A$31,Input!$A$2:$A$31,Input!$B$2:$B$31,Input!$B$2:$B$31)>10))​

Sau này, nếu có thêm điều kiện gì khác thì cứ nối vào tương tự.

2. Theo dõi "Hàng trọng điểm" theo công thức bạn:
Thì nó sẽ sai khi Mã SP là 1 số, theo mình hiểu là trong dòng lệnh T(....) bên trên.
Cho mình hỏi dòng T bên trên nó có cần thiết không?

Hàm T() dành để hiện những giá trị chuỗi của vùng trong đối số của nó. Thường thì các mã SP không dùng để cộng trừ nhân chia, nên bạn nên định dạng là chuỗi (text), ngay cả nếu nó mang dạng số, Vd như số CMND chẳng hạn, bạn cũng nên định dạng cột đó về 'text'.

Riêng trong bài này, nếu Mã SP của bạn là dạng "số" thì bạn thay thế hàm T() bằng hàm N().

Xem file kèm.

Thân

 

[newstar611]

Em có bài toán Sumproduct, em đã tính được nhưng công thức hơi dài, các anh xem có thể thu gọn hoặc có cách tính nào đơn giản không. Xin cám ơn.

 

[Phan Thế Hiệp]

Em có bài toán Sumproduct, em đã tính được nhưng công thức hơi dài, các anh xem có thể thu gọn hoặc có cách tính nào đơn giản không. Xin cám ơn.

Thử:

C13=SUM(SUMIF($B$9:$B$12,"*"&{1;2;3},$C$9:$C$12))

Enter.

Thân

 

[newstar611]

Thử:

C13=SUM(SUMIF($B$9:$B$12,"*"&{1;2;3},$C$9:$C$12))

Enter.

Thân

Ý của em là dùng dữ liệu thôi, không phải dùng kết quả của những nhóm xe

 

[Phan Thế Hiệp]

Ý của em là dùng dữ liệu thôi, không phải dùng kết quả của những nhóm xe

Vậy, dùng thử:

• Nhóm 1,2,3 =SUMPRODUCT($C$5:$J$5,$C$6:$J$6*ISNUMBER(MATCH(--LEFT($C$4:$J$4),{1,2,3},)))
• Nhóm 2 và 4=SUMPRODUCT($C$5:$J$5,$C$6:$J$6*ISNUMBER(MATCH(--LEFT($C$4:$J$4),{2,4},)))
• Nhóm 1,3,5=SUMPRODUCT($C$5:$J$5,$C$6:$J$6*ISNUMBER(MATCH(--LEFT($C$4:$J$4),{1,3,5},)))

Thân

 

[newstar611]

Vậy, dùng thử:

• Nhóm 1,2,3 =SUMPRODUCT($C$5:$J$5,$C$6:$J$6*ISNUMBER(MATCH(--LEFT($C$4:$J$4),{1,2,3},)))
• Nhóm 2 và 4=SUMPRODUCT($C$5:$J$5,$C$6:$J$6*ISNUMBER(MATCH(--LEFT($C$4:$J$4),{2,4},)))
• Nhóm 1,3,5=SUMPRODUCT($C$5:$J$5,$C$6:$J$6*ISNUMBER(MATCH(--LEFT($C$4:$J$4),{1,3,5},)))

Thân

Cám ơn anh rất nhiều, nếu thay (1,2,3) thành ký tự thì mình thêm dấu "" đúng không anh

 

[Phan Thế Hiệp]

Cám ơn anh rất nhiều, nếu thay (1,2,3) thành ký tự thì mình thêm dấu "" đúng không anh

Nếu lấy ký tự chuỗi thì:

Thay vì = MATCH(--LEFT($C$4:$J$4),{1,2,3},)​

Điều chỉnh thành = MATCH( LEFT($C$4:$J$4) , {"1","2","3"}, )​

​

Thân

 

[newstar611]

Nếu lấy ký tự chuỗi thì:

Thay vì = MATCH(--LEFT($C$4:$J$4),{1,2,3},)​

Điều chỉnh thành = MATCH( LEFT($C$4:$J$4) , {"1","2","3"}, )​

​

Thân

Cám ơn anh đã giúp đỡ.

 

[Loan Doan]

Cả nhà giúp mình với. Mình muốn theo dõi công nợ phải thu bán bds. Mỗi căn hộ theo 1 chính sách bh khác nhau và mỗi csbh có tiến độ thu tiền khác nhau. Mình cần đặt công thức tính số tiền phải thu theo tung đợt của từng csbh của từng căn. Rất mong mn giup. Trân trọng cảm ơn

 

[kh0jy3n]

Như em thấy nếu dùng dc hàm Sumifs thì nên dùng Sumifs còn SUMPRODUCT chạy như rùa @@..

 

[tieuthubuongbinh]

Chào ACE,

Mình có CT như file đính kèm, nhờ các cao thủ chỉ giáo làm sao để ra kết quả ở D10. Vì CT ở C10 ra không đúng ý mình.

 

[leonguyenz]

Chào ACE,

Mình có CT như file đính kèm, nhờ các cao thủ chỉ giáo làm sao để ra kết quả ở D10. Vì CT ở C10 ra không đúng ý mình.

Thử sửa công thức tại C9:

=SUMPRODUCT(SUMIFS($C$2:$C$7,$B$2:$B$7,TRANSPOSE(OFFSET($G$1,MATCH($B9,$F$2:$F$4,0),,COUNTIF($F$2:$F$4,B9))),$A$2:$A$7,$A$9:$A$10))

Ctrl+Shift+Enter, kéo xuống C10.

 

[Phan Thế Hiệp]

Chào ACE,

Mình có CT như file đính kèm, nhờ các cao thủ chỉ giáo làm sao để ra kết quả ở D10. Vì CT ở C10 ra không đúng ý mình.

Thêm cách nữa:

C9=SUM(SUMIFS($C$2:$C$7,$A$2:$A$7,TRANSPOSE($A$9:$A$10),$B$2:$B$7,IF(B9=$F$2:$F$4,$G$2:$G$4)))

Kết thúc bằng Ctrl+Shift+Enter, fill xuống 1 dòng.

Thân

 

[vanthinh3101]

Chào ACE,

Mình có CT như file đính kèm, nhờ các cao thủ chỉ giáo làm sao để ra kết quả ở D10. Vì CT ở C10 ra không đúng ý mình.

Nếu theo công thức bạn viết ở ô D10 thì tôi hiểu rằng bạn không cần điều kiện ở ô A9, A10.
Như vậy thì có thể không xét điều kiện tại A9, A10 và dùng Sumif

C9=SUM(SUMIF($B$2:$B$7,IF($F$2:$F$4=B9,$G$2:$G$4),$C$2:$C$7))

Ctrl + Shift + Enter, Fill xuống

 

[tieuthubuongbinh]

Thử sửa công thức tại C9:

=SUMPRODUCT(SUMIFS($C$2:$C$7,$B$2:$B$7,TRANSPOSE(OFFSET($G$1,MATCH($B9,$F$2:$F$4,0),,COUNTIF($F$2:$F$4,B9))),$A$2:$A$7,$A$9:$A$10))

Ctrl+Shift+Enter, kéo xuống C10.

Thêm cách nữa:

C9=SUM(SUMIFS($C$2:$C$7,$A$2:$A$7,TRANSPOSE($A$9:$A$10),$B$2:$B$7,IF(B9=$F$2:$F$4,$G$2:$G$4)))

Kết thúc bằng Ctrl+Shift+Enter, fill xuống 1 dòng.

Thân

Em được bạn bày thêm cách này:

C9=SUMPRODUCT(--(LOOKUP($B$2:$B$7,$G$2:$G$4,$F$2:$F$4)=$B9),$C$2:$C$7)

 

[tieuthubuongbinh]

Nếu theo công thức bạn viết ở ô D10 thì tôi hiểu rằng bạn không cần điều kiện ở ô A9, A10.
Như vậy thì có thể không xét điều kiện tại A9, A10 và dùng Sumif

C9=SUM(SUMIF($B$2:$B$7,IF($F$2:$F$4=B9,$G$2:$G$4),$C$2:$C$7))

Ctrl + Shift + Enter, Fill xuống

Do em sơ xuất đấy bác, thực ra e muốn có xét cột A nhưng lúc đưa file lên em lại quên tính đến cột A. Nhưng vô tình em lại học được cách sumifs kết hợp transpose. Cách của bác @Phan Thế Hiệp giúp em tận dụng được cả 2 phương án.

Khi cần xét thêm cột A thì em thay hàm transpose là xong

Em rất cám ơn các bác ạ.