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

Loc Uyen · 20/10/18

yeskukumalu đã viết:
Mình dùng hàm sumproduct, sau đó dùng hàm large(cột kết quả của hàm sumproduct, 1), rồi large(cột kết quả của hàm sumproduct, 3) thì kết quả của hàm large ra như nhau, cho dù thay đổi tham số thứ hai của hàm large thì kết quả vẫn như khi large(cột kết quả của hàm sumproduct, 1). Mình không hiểu tại sao, mong nhận được ý kiến của mọi người ạ. Mình cảm ơn !

Em nghĩ anh/chị nên đưa file lên ạ.
Với lại giá trị của sumproduct thì chỉ ra 1 số nên large(...,1) hay large(...,3) gì thì vẫn là nó thôi mà nhỉ?

trum_so · 23/10/18

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".

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

Thanks all.

Phan Thế Hiệp · 23/10/18

trum_so đã viết:
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 "Hàng trọng điểm" được phân loại là 1 và 2 (nói chung nhỏ hơn 10), thử:

1/ Theo dõi số khách hàng:

Mã:

B2=COUNT(1/(MATCH(Input!$B$2:$B$100,IF(Input!$A$2:$A$100=$A2,Input!$B$2:$B$100),)=ROW($1:$100)))

Kết thúc bằng Ctrl+Shift+Enter, rồi fill xuống.

2/ Theo dõi "Hàng trọng điểm":

Mã:

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,)))

Kết thúc bằng Ctrl+Shift+Enter, copy qua phải 1 cột, rồi fill xuống.
Xem file kèm.

Thân

batman1 · 23/10/18

trum_so đã viết:
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 ý và không nhầm lẫn

thì ...
1. Tôi đổi tên sheet thành csdl và nguon.
2. Công thức cho B2

Mã:

=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 thúc bằng Ctrl+Shift+Enter, và copy xuống dưới.

Công thức cho C2

Mã:

=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 thúc bằng Ctrl+Shift+Enter, và copy sang phải tới cột D rồi xuống dưới.

trum_so · 26/10/18

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:

Mã:

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 · 26/10/18

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 · 26/10/18

trum_so đã viết:
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 chỉ KH mua >= 10 mới được đeo huy hiệu?

1. Hoặc con bò mộng cho B2

Mã:

=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 thúc bằng Ctrl+Shift+Enter

2. Hoặc cột phụ.
Công thức cho nguon!F2

Mã:

=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 và copy xuống đến F200

Công thức cho B2

Mã:

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

Kết thúc bằng Ctrl+Shift+Enter

Tôi chỉ nhìn lướt qua kết quả cho dữ liệu hiện có trong tập tin của bạn. Tôi không test nhiều trường hợp vì vấn đề là của bạn.

Phan Thế Hiệp · 26/10/18

trum_so đã viết:
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ự.

trum_so đã viế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 · 13/12/18

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 · 13/12/18

newstar611 đã viết:
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ử:

Mã:

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

Enter.

Thân

newstar611 · 13/12/18

Phan Thế Hiệp đã viết:
Thử:

Mã:

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 · 13/12/18

newstar611 đã viết:
Ý 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 · 13/12/18

Phan Thế Hiệp đã viết:
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 · 13/12/18

newstar611 đã viết:
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 · 13/12/18

Phan Thế Hiệp đã viết:
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 · 30/12/18

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 · 2/3/19

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 · 19/3/19

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 · 22/3/19

tieuthubuongbinh đã viết:
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:

Mã:

=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 · 22/3/19

tieuthubuongbinh đã viết:
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:

Mã:

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

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

Thành viên thường trực

Thành viên mới

File đính kèm

-⍟-

File đính kèm

Thành viên gạo cội

Thành viên mới

Thành viên mới

Thành viên gạo cội

-⍟-

File đính kèm

Thành viên chính thức

File đính kèm

-⍟-

File đính kèm

Thành viên chính thức

-⍟-

Thành viên chính thức

-⍟-

Thành viên chính thức

Thành viên mới

Thành viên thường trực

Học hoài vẫn dốt

File đính kèm

Thành viên gạo cội

-⍟-

Facebook

Group