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

[Mộng Thường 2001]

Sao vẫn không được vậy ạ. Em toàn thấy VALUE thôi

Máy vi tính của bạn đang xài excel phiên bản mấy vậy!?

 

[HieuCD]

Máy vi tính của bạn đang xài excel phiên bản mấy vậy!?

Phiên bản Excel có liên quan gì đến kết quả Value vậy ta

 

[batman1]

Sao vẫn không được vậy ạ. Em toàn thấy VALUE thôi

Với code như thế thì bạn phải có thiết lập trong CP giống như người viết code cho bạn. Tức code phụ thuộc vào thiết lập trong CP. Nếu thiết lập của bạn khác đi thì sẽ có VALUE
Bạn hãy nhìn kỹ công thức.

A4=IF(COUNTA(B4:G4),IF(B4<>"",INT(IFERROR(--A3,0))+1,INT(--A3)&"."&TEXT(IFERROR(LOOKUP(4,{2,3}/(SEARCH({"Nhân","Máy"},C4)>0)),1),"00")),)

Tức trong cột A giá trị dòng dưới sẽ được tính theo dòng trên. Chắc chắn giá trị trong cột A có nhiều số "lẻ", vd. A5. Và nó có dạng TEXT với quyết định cứng nhắc là có dấu chấm. Vd. A5 = "1.01"

Trên máy người ta thì dấu chấm là dấu thập phân nên với

A6 =IF(COUNTA(B6:G6),IF(B6<>"",INT(IFERROR(--A5,0))+1,INT(--A5)&"."&TEXT(IFERROR(LOOKUP(4,{2\3}/(SEARCH({"Nhân"\"Máy"},C6)>0)),1),"00")),)

thì không có lỗi vì INT(--A5) không gây ra lỗi.

Bây giờ bạn mở tập tin nhưng bạn có dấu phẩy là dấu thập phân nên INT(--A5) = INT(--"1.01") sẽ gây lỗi. Nỗ lực convert chuỗi có dấu chấm thành số trên máy có thiết lập dấu phẩy là dấu thập phân chắc chắn không thành công, chắc chắn gây ra lỗi.

A6 lỗi thì A7 cũng lỗi, mà A7 lỗi thì A8 cũng lỗi, vân vân và phân vân.

Cột A có lỗi thì nhiều giá trị trong cột H cũng sẽ lỗi.
----------------------
Bạn rút kinh nghiệm lần sau. Phải mô tả dữ liệu để biết có cấui trúc thế nào. Không phải ai cũng thích đoán mò.

Tôi hiểu như sau, nếu hiểu không đúng thì hãy đừng đọc tiếp. Vì mọi code tôi viết đều chỉ đúng với giả thiết dữ liệu cụ thể.

Dòng "Vật liệu khác" và "Máy khác" không nhất thiết phải có.

Nếu có "Vật liệu khác" thì ở cột H là tổng các giá trị ở cột H tính từ dòng dưới dòng có số thứ tự đến dòng trên dòng có "Vật liệu khác", được nhân với cột E

Nếu có dòng "Máy khác" thì dòng "Nhân công ..." sẽ có "công" ở cột D, và lúc đó thì ở cột H là tổng các giá trị ở cột H tính từ dòng dưới dòng "công" đến dòng trên dòng có "Máy khác", được nhân với cột E và G

Nếu đúng như trên tôi hiểu thì công thức cho H4

=IF(B4<>"",SUM(H5:INDEX(H$1:H$1000,IFERROR(MATCH("*",B5:B$1000,0)+ROW(B4)-1,LOOKUP("zzz",C5:C$1000,ROW(C5:C$1000))))),IF(C4="Vật liệu khác",E4*SUM(INDEX(H$1:H4,LOOKUP("zzz",B$1:B3,ROW(B$1:B3))+1):H3)/100,IF(C4="Máy khác",E4*G4*SUM(INDEX(H$1:H4,LOOKUP(2,1/(D$1:D4="công"),ROW(D$1:D4))+1):H3)/100,E4*F4*G4)))

copy, kéo công thức xuống dưới.

Tôi giả thiết là có không quá 1000 dòng dữ liệu. Nếu không bao giờ quá vd. 100 thì sửa 1000 trong công thức thành 100.

 

[Mộng Thường 2001]

Phiên bản Excel có liên quan gì đến kết quả Value vậy ta

Trong công thức có dùng Iferror() nếu xài phiên bản trước 2010 thì sửa cùng lúc luôn.

Sao vẫn không được vậy ạ. Em toàn thấy VALUE thôi

Nếu như lỗi #Value như bài #603 đề cập do định dạng trong Control Panel về dấu phân cách hàng đơn vị và hàng ngàn..., thì bạn có thể thay đổi dấu "." trong công thức A4 thành dấu "," như sau:

A4=IF(COUNTA(B4:G4);IF(B4<>"";INT(IFERROR(--A3;0))+1;INT(--A3)&","&TEXT(IFERROR(LOOKUP(4;{2;3}/(SEARCH({"Nhân";"Máy"};C4)>0));1);"00"));)

Fill xuống là xong.

Tôi có gửi thêm cách điền ký tự "_" thay cho dấu "." hay "," thì không ngại bạn định dạng ".," trong Control Panel nữa, và công thức tổng ngắn gọn hơn để bạn tham khảo:

A4=IF(COUNTA(B4:G4),IF(B4<>"",MAX($A$3:A3)+1,MAX($A$3:A3)&"_"&TEXT(IFERROR(LOOKUP(4,{2,3}/(SEARCH({"Nhân","Máy"},C4)>0)),1),"00")),)

Fill xuống

H4=IF(A4<>"",ROUND(IF(B4<>"",SUMIF(A5:$A$1000,A4&"_??",H5:$H$1000),PRODUCT(E4,F4,G4)*IF(OR(C4={"Vật liệu khác","Máy khác"}),SUMIF($A$3:A3,A4,$H$3:H3)%,1)),),"")

Fill xuống. Bạn cũng có thể thay "_??" trong SUMIF(A5:$A$1000,A4&"_??",H5:$H$1000) thành "_*", nhưng tôi thì thích "_??" hơn.
Xem file đính kèm nhen! hihi ^o^

 

[noidoi]

Trong công thức có dùng Iferror() nếu xài phiên bản trước 2010 thì sửa cùng lúc luôn.

Nếu như lỗi #Value như bài #603 đề cập do định dạng trong Control Panel về dấu phân cách hàng đơn vị và hàng ngàn..., thì bạn có thể thay đổi dấu "." trong công thức A4 thành dấu "," như sau:

A4=IF(COUNTA(B4:G4);IF(B4<>"";INT(IFERROR(--A3;0))+1;INT(--A3)&","&TEXT(IFERROR(LOOKUP(4;{2;3}/(SEARCH({"Nhân";"Máy"};C4)>0));1);"00"));)

Fill xuống là xong.

Tôi có gửi thêm cách điền ký tự "_" thay cho dấu "." hay "," thì không ngại bạn định dạng ".," trong Control Panel nữa, và công thức tổng ngắn gọn hơn để bạn tham khảo:

A4=IF(COUNTA(B4:G4),IF(B4<>"",MAX($A$3:A3)+1,MAX($A$3:A3)&"_"&TEXT(IFERROR(LOOKUP(4,{2,3}/(SEARCH({"Nhân","Máy"},C4)>0)),1),"00")),)

Fill xuống

H4=IF(A4<>"",ROUND(IF(B4<>"",SUMIF(A5:$A$1000,A4&"_??",H5:$H$1000),PRODUCT(E4,F4,G4)*IF(OR(C4={"Vật liệu khác","Máy khác"}),SUMIF($A$3:A3,A4,$H$3:H3)%,1)),),"")

Fill xuống. Bạn cũng có thể thay "_??" trong SUMIF(A5:$A$1000,A4&"_??",H5:$H$1000) thành "_*", nhưng tôi thì thích "_??" hơn.
Xem file đính kèm nhen! hihi ^o^

Đại ca ơi, em hỏi ngu tí?! Em thêm 4 cột giữa cột G và cột H, khi đó cột "thành tiền" là cột L và em áp dụng công thức của Đại ca thì kết quả cho = 0. Lý do tại sao vậy ạ?

 

[Mộng Thường 2001]

Đại ca ơi, em hỏi ngu tí?! Em thêm 4 cột giữa cột G và cột H, khi đó cột "thành tiền" là cột L và em áp dụng công thức của Đại ca thì kết quả cho = 0. Lý do tại sao vậy ạ?

Bạn nên download file tại bài #604, sau đó muốn chèn thêm 4 cột trắng nữa vào giữa G và H, thì nên chọn nguyên cột H rồi nhấn Ctrl+"+" (dấu "+" của bàn phím số bên tay phải) nhấn thêm 3 lần như vậy nữa thì công thức không bị biến đổi.

Lúc đó công thức tại cột L sau khi đã chèn 4 cột thành như sau:
H4=IF(A4<>"",ROUND(IF(B4<>"",SUMIF(A5:$A$1000,A4&"_??",L5:$L$1000),PRODUCT(E4,F4,G4)*IF(OR(C4={"Vật liệu khác","Máy khác"}),SUMIF($A$3:A3,A4,$L$3:L3)%,1)),),"")

hihi ^o^

 

[noidoi]

Bạn nên download file tại bài #604, sau đó muốn chèn thêm 4 cột trắng nữa vào giữa G và H, thì nên chọn nguyên cột H rồi nhấn Ctrl+"+" (dấu "+" của bàn phím số bên tay phải) nhấn thêm 3 lần như vậy nữa thì công thức không bị biến đổi.

Lúc đó công thức tại cột L sau khi đã chèn 4 cột thành như sau:
H4=IF(A4<>"",ROUND(IF(B4<>"",SUMIF(A5:$A$1000,A4&"_??",L5:$L$1000),PRODUCT(E4,F4,G4)*IF(OR(C4={"Vật liệu khác","Máy khác"}),SUMIF($A$3:A3,A4,$L$3:L3)%,1)),),"")

hihi ^o^

Vì file cũ của em có thêm 4 cột giữa cột H và cột G nên áp dụng công thức của đại ca không được

 

[Mộng Thường 2001]

Vì file cũ của em có thêm 4 cột giữa cột H và cột G nên áp dụng công thức của đại ca không được

1. Hoặc bạn đưa file thật của bạn lên
2. Hoặc chép công thức ở trên bài #606 về

hihi ^o^

 

[noidoi]

1. Hoặc bạn đưa file thật của bạn lên
2. Hoặc chép công thức ở trên bài #606 về

hihi ^o^

file của em đây thưa Đại ca

 

[Mộng Thường 2001]

file của em đây thưa Đại ca

Tôi làm cho bạn hai cách, tùy ý bạn chọn:

1. Cách có bộ đếm STT ở cột A (Sheet "Chiết tính"): mục đích để phân Vùng cho sheet này, nhưng cũng dành cho về sau bạn có báo cáo nào khác cần tổng hợp trên các mục tổng chi tiết, Ví dụ: Mã; Nội dung; Khối lượng; Chi phí Vật tư; Chi phí Nhân công; Chi phí Máy móc; Tổng Chi phí. Giống dạng báo cáo theo đường link: http://www.giaiphapexcel.com/dienda...nh-toán-trị-giá-theo-dòng.133787/#post-847886
2. Cách không cần bộ đếm STT ở cột A (Sheet "Chiết tính (2)"): chỉ dành Công Phân Vùng hoặc tính dòng chi tiết cho Sheet "Chiết tính (2)" mà thôi, không phục vụ cho các báo cáo khác nữa.

   L4=IF(B4<>"",SUM(L5:OFFSET(L4,IFERROR(MATCH("*",B5:B$1000,)-1,COUNTA(C5:C$1000)),)),PRODUCT(E4,F4,G4)*IF(OR(C4={"Vật liệu khác"
   ,"Máy khác"}),SUM(OFFSET(L$3,LOOKUP(2,1/IF(LEFT(C4)="M",INDEX(D$4:D4="công",),INDEX(B$4:B4<>"",)),ROW($1:1))+1,):L3)%,1))

   Chỉ Enter, Fill xuống.

Xem file kèm. hihi ^o^

 

[cdtbk]

Có thể dùng hàm sumproduct mà trong đó có công thức có điều kiện không bạn, mình muốn đếm bao nhiêu complaint PF10 ở cột G và complaint đó nằm trong tháng 5 thì dùng sumproduct sao được nhỉ?

 

[huonglien1901]

Có thể dùng hàm sumproduct mà trong đó có công thức có điều kiện không bạn, mình muốn đếm bao nhiêu complaint PF10 ở cột G và complaint đó nằm trong tháng 5 thì dùng sumproduct sao được nhỉ?

=SUMPRODUCT((MONTH($C$2:$C$720)=5)*($G$2:$G$720="PF10"))

 

[lightknight90]

sao ảnh chủ thớt post lên mà ko xem được hả mọi người?

 

[ntrang612]

tính tổng có điều kiện và ngày tháng.
mình muốn tính tổng tiền của từng khách hàng đến hạn phải trả theo tháng như đính kèm.
nhờ các bạn xem giúp nhé.
cám ơn các bạn nhiều,

 

[huonglien1901]

tính tổng có điều kiện và ngày tháng.
mình muốn tính tổng tiền của từng khách hàng đến hạn phải trả theo tháng như đính kèm.
nhờ các bạn xem giúp nhé.
cám ơn các bạn nhiều,

=SUMPRODUCT(($E$4:$E$134="VD")*MONTH($T$4:$T$134=8),$AA$4:$AA$134)

 

[ntrang612]

=SUMPRODUCT(($E$4:$E$134="VD")*MONTH($T$4:$T$134=8),$AA$4:$AA$134)

cám ơn bạn Huonglien1901.
nhưng mình làm y mọi cách mà kết quả ko đúng bạn ạ, bạn xem dùm mình file thử xem công thức mình còn sót chỗ nào ko nhé.

 

[Ba Tê]

cám ơn bạn Huonglien1901.
nhưng mình làm y mọi cách mà kết quả ko đúng bạn ạ, bạn xem dùm mình file thử xem công thức mình còn sót chỗ nào ko nhé.

Công thức như trên thì tính cả tháng 8/2017 và tháng 8/2018 (nếu có).
Có bao nhiêu điều kiện thì bạn vẫn dùng SumIfs() được mà.

 

[ntrang612]

Công thức như trên thì tính cả tháng 8/2017 và tháng 8/2018 (nếu có).
Có bao nhiêu điều kiện thì bạn vẫn dùng SumIfs() được mà.

cám ơn bạn Ba Tê rất nhiều nhé.
hướng dẫn kĩ và attached nữa

 

[Vũ Phạm]

Chào các bác,
Hiện mình đang gặp vấn đề về cách tính tổng có điều kiện theo lũy kế.
Mỗi ngày mình có 30 máy, sản xuất 7 sản phẩm. Mình cần tính tổng số máy sẽ bị thiếu vật tư theo từng ngày. Các bac nhìn file đính kèm dễ hiểu hơn mình trình bày suông trên đây cũng hơi khó hiểu.
Mong các bác trợ giúp cho em.
Thanks.

 

[thuhuonglee]

Chào các bác,
Hiện mình đang gặp vấn đề về cách tính tổng có điều kiện theo lũy kế.
Mỗi ngày mình có 30 máy, sản xuất 7 sản phẩm. Mình cần tính tổng số máy sẽ bị thiếu vật tư theo từng ngày. Các bac nhìn file đính kèm dễ hiểu hơn mình trình bày suông trên đây cũng hơi khó hiểu.
Mong các bác trợ giúp cho em.
Cảm ơn.

Hổng biết có đúng theo ý bạn chưa ?
Số máy ngưng hoạt động
O7=ROUNDUP((M7-N7)/B7,0)