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

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

 

[Phan Thế Hiệp]

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)

Mấy nay, đi đâu mà mất tích biền biệt vậy em gái!
Chào mừng trở lại với anh em.

 

[bebanhbeo]

Mấy nay, đi đâu mà mất tích biền biệt vậy em gái!
Chào mừng trở lại với anh em.

Người không ra đi mà như trở lại

 

[Vũ Phạm]

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)

Cảm ơn bạn thuhuonglee, nhưng mình cần tính số ở các ô như: E2, G2, I2, K2.
Số máy ngưng hoạt động theo từng ngày.

 

[Phan Thế Hiệp]

Cảm ơn bạn thuhuonglee, nhưng mình cần tính số ở các ô như: E2, G2, I2, K2.
Số máy ngưng hoạt động theo từng ngày.

Bạn thử thêm cột O chứa kết quả tổng kết "Số máy thiếu vật tư":

O7=SUMPRODUCT(($N7<=SUMIF(OFFSET($F$5,,,,{1,3,5,7}),"SL",OFFSET($F7,,,,{1,3,5,7})))*N(OFFSET($E7,,{0,2,4,6})))

Enter fill xuống.

Muốn theo dõi số lượng máy thiếu theo từng ngày trả kết quả tại các ô: E2, G2, I2, K2, bạn làm như sau:

E2=SUMPRODUCT(INDEX($N$7:$N$13<=SUMIF(OFFSET($F$5,,,,{1,3,5,7}),"SL",OFFSET($F$7,ROW(1:7)-1,,,{1,3,5,7})),,COUNTA($E$4:E$4))*E$7:E$13)

Copy cho các ô còn lại.

Xem file kèm.
Thân.

 

[Vũ Phạm]

Bạn thử thêm cột O chứa kết quả tổng kết "Số máy thiếu vật tư":

O7=SUMPRODUCT(($N7<=SUMIF(OFFSET($F$5,,,,{1,3,5,7}),"SL",OFFSET($F7,,,,{1,3,5,7})))*N(OFFSET($E7,,{0,2,4,6})))

Enter fill xuống.

Muốn theo dõi số lượng máy thiếu theo từng ngày trả kết quả tại các ô: E2, G2, I2, K2, bạn làm như sau:

E2=SUMPRODUCT(INDEX($N$7:$N$13<=SUMIF(OFFSET($F$5,,,,{1,3,5,7}),"SL",OFFSET($F$7,ROW(1:7)-1,,,{1,3,5,7})),,COUNTA($E$4:E$4))*E$7:E$13)

Copy cho các ô còn lại.

Xem file kèm.
Thân.

Thanks Phan Thế Hiệp,
Đúng thứ mình cần rồi bạn, nhưng vẫn không hiểu cấu trúc của hàm OFFSET cho lắm, trước giờ mình chưa áp dụng hàm này.

 

[Phan Thế Hiệp]

Cảm ơn Phan Thế Hiệp,
Đúng thứ mình cần rồi bạn, nhưng vẫn không hiểu cấu trúc của hàm OFFSET cho lắm, trước giờ mình chưa áp dụng hàm này.

Công thức:
SUMIF( OFFSET($F$5,,,,{1,3,5,7}), "SL" , OFFSET($F$7,ROW(1:7)-1,,,{1,3,5,7}) )

giống Sumif( Vùng so, Tiêu chí so, Vùng cộng) bình thường, nhưng nhờ Mảng {1,3,5,7} mà hàm Offset() sẽ nhảy qua cách cột, để làm nhiệm vụ cộng dồn số liệu, ví dụ:

• OFFSET($F$5,,,,{1,3,5,7}): chỉ lấy các gía trị tại các ô: bắt đầu từ F5, nhảy qua 2 cột (3-1=2) lấy đến ô H5, cứ vậy lấy giá trị đến các ô J5 và L5 để so sánh với tiêu chí "SL". Bạn có thể hình dung cách thức nó hoạt động như sau: cột đầu tiên F là Sumif(F5:F5,"SL".....), cột H là Sumif(F5:H5,"SL".....), cột J là Sumif(F5:J5,"SL".....), cột L là Sumif(F5:L5,"SL".....)
• OFFSET($F$7,ROW(1:7)-1,,,{1,3,5,7}): Tương tự với cách thức trên, nó đứng từ cột F7, với giá trị đầu tiên là 1, thì nó lấy giá trị tại chỗ là =12, nhảy qua 2 cột nữa (1+2=3) tức lấy giá trị ô H7 (vì H5="SL") là =4, nó cộng dồn 12+4=16, rồi tiếp tục 5 (1+2+2=5) tức qua cột J7 là =6, nó lại cộng dồn 16+6=22, sau cùng là nhảy đến ô L7 là =8, cộng dồn 22+8=30
• Kế tiếp nó lấy vật tư tồn tại cột N7 = 14 đem so với mảng mà nó vừa cộng dồn là {12,16,22,30}, tức 14<={12,16,22,30} <=> trả về Mảng {False, True, True, True} hay {0,1,1,1} (True=1; False=0)
• Hàm OFFSET($F$7,ROW(1:7)-1,,,{1,3,5,7}) không những lấy cách cột mà nó còn chạy xuống cho hết 7 hàng như dữ liệu của bạn có. Nó sẽ tạo ra "Mảng hai chiều" gồm: hàng x cột: chứa số cộng dồn theo từng hàng dữ liệu và có 4 cột. Gom chung lại cả công thức bạn có thể hình dung nó hoạt động theo hướng sau:
  • Dòng 7: Sumif(F5:F5,"SL",F7:F7), Sumif(F5:H5,"SL",F7:H7), Sumif(F5:J5,"SL",F7:J7), Sumif(F5:L5,"SL",F7:L7)
  • Dòng 8: Sumif(F5:F5,"SL",F8:F8), Sumif(F5:H5,"SL",F8:H8), Sumif(F5:J5,"SL",F8:J8), Sumif(F5:L5,"SL",F8:L8)
  • Dòng 9: Sumif(F5:F5,"SL",F9:F9), Sumif(F5:H5,"SL",F9:H9), Sumif(F5:J5,"SL",F9:J9), Sumif(F5:L5,"SL",F9:L9)
  • ..........Đến dòng 13:....
  • Tức kết quả sẽ ra 1 Mảng như sau:
  • 
• Sau đó nó đem Mảng này ra so với cột Tồn vật tư N để tạo ra Mảng hai chiều chứa giá trị thỏa hay không cột nào bị thiếu vật tư.
  • 
• Hàm Index(Mảng, ,'Thứ tự cột cần lấy') sẽ lấy ra Mảng cột, Vd: hình dạng cột F là {0;0;0;0;1;0;0} tức dòng 5 có giá trị 1 báo dòng đó bị thiếu vật tư. Tùy theo Thứ tự cột cần lấy, Ví dụ đứng tại F là 1, qua cột H là 2....., mà lấy ra Mảng điều kiện phù hợp (xem các cột 0,1 như hình trên).
• Cuối cùng, nó nhân 2 Mảng: 'Mảng điều kiện vừa tìm ra trên' x 'Cột số máy tương ứng', Ví dụ: cột F gồm hai Mảng: {0;0;0;0;1;0;0}*{6;2;5;4;5;2;6} --> kết quả cuối ={0;0;0;0;5;0;0}
• Sumproduct() làm nhiệm vụ cộng lại các số trong Mảng kết quả trên, tức = 5.

Chúc bạn học tập vui với anh em GPE.
Thân

 

[Lasderybi]

Các bạn thân mến giúp mình kéo dữ liệu từ Sheet 2 sang với nhé, cám ơn nhiều lắm. Mình đang mò mẫm mà không ra.

 

[Phan Thế Hiệp]

Các bạn thân mến giúp mình kéo dữ liệu từ Sheet 2 sang với nhé, cám ơn nhiều lắm. Mình đang mò mẫm mà không ra.

Thử:

D4=SUMIFS(Data!$F$2:$F$13,Data!$B$2:$B$13,$B4,Data!$E$2:$E$13,"17Q"&COLUMN(A$1))

Enter, fill qua phải, rồi copy cả dòng xuống.
Nếu muốn linh động về niên độ tính thuế thì cập nhật tại ô nào đó Vd: A1=01/01/2017, và thực hiện công thức trong file kèm.

Thân.
p/s: Bạn chắc đang làm công tác quản lý thuế tại Chi Cục à!?

 

[Lasderybi]

Thử:

D4=SUMIFS(Data!$F$2:$F$13,Data!$B$2:$B$13,$B4,Data!$E$2:$E$13,"17Q"&COLUMN(A$1))

Enter, fill qua phải, rồi copy cả dòng xuống.
Nếu muốn linh động về niên độ tính thuế thì cập nhật tại ô nào đó Vd: A1=01/01/2017, và thực hiện công thức trong file kèm.

Thân.
p/s: Bạn chắc đang làm công tác quản lý thuế tại Chi Cục à!?

Rất hay và cũng rất...khó hehe, nhưng thôi giải quyết được việc của mình là ok rồi, cám ơn anh nhiều nhé, vâng em làm thuế ở Chi cục ạ. Cho em hỏi thêm tẹo, theo anh thì cách nào hay hơn và muốn tìm hiểu thì cách nào dễ hiểu hơn ạ?

 

[Phan Thế Hiệp]

Rất hay và cũng rất...khó hehe, nhưng thôi giải quyết được việc của mình là ok rồi, cám ơn anh nhiều nhé, vâng em làm thuế ở Chi cục ạ. Cho em hỏi thêm tẹo, theo anh thì cách nào hay hơn và muốn tìm hiểu thì cách nào dễ hiểu hơn ạ?

Tùy theo bạn thấy cái nào tiện cho công việc của bạn:

D4=SUMIFS(Data!$F$2:$F$13,Data!$B$2:$B$13,$B4,Data!$E$2:$E$13,"17Q"&COLUMN(A$1))

• Công thức này gọn dễ hiểu hơn, nhưng khi qua năm mới (Vd: 2018), bạn phải sửa lại công thức tại "17Q" thành "18Q". Tương tự cho các niên độ sau nữa.

D4=SUMIFS(Data!$F$2:$F$13,Data!$B$2:$B$13,$B4,Data!$E$2:$E$13,TEXT($A$1,"yy")&"Q"&RIGHT(D$3))

• Công thức này bạn chỉ cần điều chỉnh niên độ mới tại A1 (Vd: gõ 01/01/2018, tương tự cho các niên độ sau) thì không cần chỉnh công thức.
• TEXT($A$1,"yy"): Ra kết quả là 2 số đuôi của năm do định dạng "yy" (Vd: 01/01/2017 ra 17)
• RIGHT(D$3): Lấy bên phải của ô D3 ra 1 ký tự, tức số của các Quý: 1,2,3,4
• TEXT($A$1,"yy")&"Q"&RIGHT(D$3) ghép các kết quả chung với chữ "Q" thành các chuỗi: Vd: "17Q1" hay "17Q2" "17Q3" "17Q4"

Thân.

 

[Arsenelupin]

Chào các bạn,
Mình cần tìm tổng số lượng theo từng size của các đơn hàng. Chi tiết đơn hàng ở Sheet1, giá trị cần tính ở cột Total sheet2.
Hiện tại, mình đang dùng hàm như sau:

=SUMPRODUCT(SWITCH([@Size],"36",PO[36],"38",PO[38],"40",PO[40],"42",PO[42],"44",PO[44],"46",PO[46],"48",PO[48],"XS",PO[XS],"S",PO[S],"M",PO[M],"L",PO[L],"XL",PO[XL],"2XL",PO[2XL],"3XL",PO[3XL])*(PO[MODEL]=[@Model]))

Nhưng hàm này quá dài và chỉ những máy sử dụng Office 365 mới dùng được. Các bạn giúp mình thay hàm trên bằng 1 hàm khác đơn giản hơn.
Xin cảm ơn!

 

[Phan Thế Hiệp]

Chào các bạn,
Mình cần tìm tổng số lượng theo từng size của các đơn hàng. Chi tiết đơn hàng ở Sheet1, giá trị cần tính ở cột Total sheet2.
Hiện tại, mình đang dùng hàm như sau:

=SUMPRODUCT(SWITCH([@Size],"36",PO[36],"38",PO[38],"40",PO[40],"42",PO[42],"44",PO[44],"46",PO[46],"48",PO[48],"XS",PO[XS],"S",PO[S],"M",PO[M],"L",PO[L],"XL",PO[XL],"2XL",PO[2XL],"3XL",PO[3XL])*(PO[MODEL]=[@Model]))

Nhưng hàm này quá dài và chỉ những máy sử dụng Office 365 mới dùng được. Các bạn giúp mình thay hàm trên bằng 1 hàm khác đơn giản hơn.
Xin cảm ơn!

Thử:

C2=SUMIF(Sheet1!$B$1:$B$1000,$A2,OFFSET(Sheet1!$D$1:$D$1000,,MATCH($B2,Sheet1!$E$1:$Z$1,)))

Thân.

 

[hocexcel_1991]

Chào các bạn,
Mình cần tìm tổng số lượng theo từng size của các đơn hàng. Chi tiết đơn hàng ở Sheet1, giá trị cần tính ở cột Total sheet2.
Hiện tại, mình đang dùng hàm như sau:

=SUMPRODUCT(SWITCH([@Size],"36",PO[36],"38",PO[38],"40",PO[40],"42",PO[42],"44",PO[44],"46",PO[46],"48",PO[48],"XS",PO[XS],"S",PO[S],"M",PO[M],"L",PO[L],"XL",PO[XL],"2XL",PO[2XL],"3XL",PO[3XL])*(PO[MODEL]=[@Model]))

Nhưng hàm này quá dài và chỉ những máy sử dụng Office 365 mới dùng được. Các bạn giúp mình thay hàm trên bằng 1 hàm khác đơn giản hơn.
Xin cảm ơn!

Thêm cách nữa

=SUMPRODUCT((Sheet1!$B$2:$B$1000=$A2)*(Sheet1!$E$1:$T$1=$B2),Sheet1!$E$2:$T$1000)

 

[Arsenelupin]

Thử:

C2=SUMIF(Sheet1!$B$1:$B$1000,$A2,OFFSET(Sheet1!$D$1:$D$1000,,MATCH($B2,Sheet1!$E$1:$Z$1,)))

Thân.

Cảm ơn bạn! Mình vẫn chưa hiểu hàm OFFSET trong hàm. Bạn có thể giải thích giúp mình được không?

 

[Phan Thế Hiệp]

Cảm ơn bạn! Mình vẫn chưa hiểu hàm OFFSET trong hàm. Bạn có thể giải thích giúp mình được không?

OFFSET(Sheet1!$D$1:$D$1000,,MATCH($B2,Sheet1!$E$1:$Z$1,))
Sẽ lấy cột size thích hợp với B2 (Vd: là size "XS"), thì Offset() sẽ trả về Vùng dữ liệu với địa chỉ là L1: L1000, để cung cấp Vùng cộng cho SUMIF(), tức có thể hiểu lúc sau cùng công thức có dạng là:
=SUMIF(Sheet1!$B$1:$B$1000,$A2,Sheet1!$L$1:$L$1000)

Vận hành của Offset() như sau: đứng tại mốc Sheet1!$D$1:$D$1000, sử dụng Macth() để tìm size "XS" có cột 'thứ mấy' trong Vùng Sheet1!$E$1:$Z$1, khi tìm được nó trả về stt cột, tức cách D1 bao nhiêu cột nhằm cung cấp thông số 'cột' cho Offset() để nhảy đến lấy cả vùng đó.

Cũng có thể thay thế bằng =OFFSET(Sheet1!$D$1,,MATCH($B2,Sheet1!$E$1:$Z$1,1000)) cũng cùng ý nghĩa.

=SUMPRODUCT((Sheet1!$B$2:$B$1000=$A2)*(Sheet1!$E$1:$T$1=$B2),Sheet1!$E$2:$T$1000)

Dùng hàm này là phù hợp với 'chủ đề' ở đây, tuy nhiên tôi muốn lưu ý với bạn dùng Sumproduct() là tập họp 1 mảng 2 chiều, do đó nó chiếm khá nhiều bộ nhớ. Nếu dữ liệu càng lớn, thì bạn sẽ thấy nó làm trì trệ tốc độ xử lý của máy tính.
Bạn thử hình dung như sau với công thức của bạn có khoảng 50 dòng x 14 cột = 700 ô dữ liệu, nếu dòng dữ liệu khoảng 1000 (như công thức Sumif() tôi đưa Vùng vào) thì nó vào khoảng 14.000 ô dữ liệu chiếm trong bộ nhớ.

Với Sumif() do dữ liệu được ghi nhận bởi Vùng (vùng so khớp, và vùng cộng đã ghi nhận vào ô hiện hữu trên bảng tính, ước lượng cao lắm cũng thao tác trong khoảng 2000 ô mà thôi), sau khi xác định rõ Vùng thì máy tính xử lý theo dòng dữ liệu nên nó ít chiếm bộ nhớ của máy tính, vì vậy tốc độ xử lý sẽ nhanh hơn.

Cũng là một dịp chia sẻ để biết cái lợi và hại của Sumproduct().

Thân.

 

[batman1]

Tôi nghĩ không nên bắt chước ai cả. Nếu không dùng các hàm volatile mà công thức không phức tạp hơn thì không nên dùng các hàm volatile. Chỉ dùng khi hết cách - tức khi cách khác thì phức tạp hơn nhiều.
Dùng volatile thì các công thức luôn được tính lại mặc dù tham chiếu không đổi.

Đây là tôi nói về nguyên tắc, về tập cho mình một thói quen

=SUMIF(Sheet1!$B$1:$B$1000,$A2,INDEX(Sheet1!$E$1:$Z$1000,,MATCH($B2,Sheet1!$E$1:$Z$1,0)))

 

[hocexcel_1991]

OFFSET(Sheet1!$D$1:$D$1000,,MATCH($B2,Sheet1!$E$1:$Z$1,))
Sẽ lấy cột size thích hợp với B2 (Vd: là size "XS"), thì Offset() sẽ trả về Vùng dữ liệu với địa chỉ là L1: L1000, để cung cấp Vùng cộng cho SUMIF(), tức có thể hiểu lúc sau cùng công thức có dạng là:
=SUMIF(Sheet1!$B$1:$B$1000,$A2,Sheet1!$L$1:$L$1000)

Vận hành của Offset() như sau: đứng tại mốc Sheet1!$D$1:$D$1000, sử dụng Macth() để tìm size "XS" có cột 'thứ mấy' trong Vùng Sheet1!$E$1:$Z$1, khi tìm được nó trả về stt cột, tức cách D1 bao nhiêu cột nhằm cung cấp thông số 'cột' cho Offset() để nhảy đến lấy cả vùng đó.

Cũng có thể thay thế bằng =OFFSET(Sheet1!$D$1,,MATCH($B2,Sheet1!$E$1:$Z$1,1000)) cũng cùng ý nghĩa.


Dùng hàm này là phù hợp với 'chủ đề' ở đây, tuy nhiên tôi muốn lưu ý với bạn dùng Sumproduct() là tập họp 1 mảng 2 chiều, do đó nó chiếm khá nhiều bộ nhớ. Nếu dữ liệu càng lớn, thì bạn sẽ thấy nó làm trì trệ tốc độ xử lý của máy tính.
Bạn thử hình dung như sau với công thức của bạn có khoảng 50 dòng x 14 cột = 700 ô dữ liệu, nếu dòng dữ liệu khoảng 1000 (như công thức Sumif() tôi đưa Vùng vào) thì nó vào khoảng 14.000 ô dữ liệu chiếm trong bộ nhớ.

Với Sumif() do dữ liệu được ghi nhận bởi Vùng (vùng so khớp, và vùng cộng đã ghi nhận vào ô hiện hữu trên bảng tính, ước lượng cao lắm cũng thao tác trong khoảng 2000 ô mà thôi), sau khi xác định rõ Vùng thì máy tính xử lý theo dòng dữ liệu nên nó ít chiếm bộ nhớ của máy tính, vì vậy tốc độ xử lý sẽ nhanh hơn.

Cũng là một dịp chia sẻ để biết cái lợi và hại của Sumproduct().

Thân.

Cám ơn anh đã chia sẽ những thông tin hữu ích
Chúc anh vui vẻ

 

[honey_552911]

Mọi người giúp mình file này với ạ.
Mình muốn cộng số lượng, theo điều kiện loại giày (thấp cổ, cao cổ, cổ lỡ, đế cao) và theo kích thước hộp giày, nhóm size.

Chi tiết trong File đính kèm.

Cảm ơn cả nhà

 

[Phan Thế Hiệp]

Mọi người giúp mình file này với ạ.
Mình muốn cộng số lượng, theo điều kiện loại giày (thấp cổ, cao cổ, cổ lỡ, đế cao) và theo kích thước hộp giày, nhóm size.
Chi tiết trong File đính kèm.
Cảm ơn cả nhà

Sheet "THĐH":

• Cột H: không hiểu bạn lấy kết quả từ đâu ra nên không làm.
• Từ Cột O đến AE:

  O4=IF($E4=LOOKUP("zzz",$O$1:O$1),SUMPRODUCT((MATCH(ĐH!$K$2:$AQ$2*1,IFERROR(MID(O$2,{0,1,4},2)+{0,0,1},))=2)*OFFSET(ĐH!$K$2:$AQ$2,LOOKUP(2,1/($F4=ĐH!$D$3:$D$500)/($G4=ĐH!$E$3:$E$500),ROW($1:$500)),)),)
  Hoặc
  O4=IF($E4=LOOKUP("zzz",$O$1:O$1),SUMIFS(OFFSET(ĐH!$K$2:$AQ$2,LOOKUP(2,1/($F4=ĐH!$D$3:$D$500)/($G4=ĐH!$E$3:$E$500),ROW($1:$500)),),ĐH!$K$2:$AQ$2,">="&LEFT(O$2,2),ĐH!$K$2:$AQ$2,"<="&RIGHT(O$2,2)),)

  Enter, fill sang phải đến AE, rồi fill cả hàng xuống.

Thân

 

[yeskukumalu]

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 !

 

[Loc Uyen]

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]

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]

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:

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

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]

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

 

[Phan Thế Hiệp]

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

View attachment 213877

Tôi lại hiểu có xét và không xét Vùng A9:A10 như trong file kèm chứ!

Thân

 

[tieuthubuongbinh]

Tôi lại hiểu có xét và không xét Vùng A9:A10 như trong file kèm chứ!

Thân

CT của bác em hiểu là sum(sumifs của A9,Sumifs của A10)=190, bác bỏ sum ra thì là 100 ấy, và nếu xét thì 100 mới đúng bác ạ

 C9=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))

 

[lathanhtre]

các anh cho em hỏi có các nào cộng tổng với tình huống thế này:

	a​	b​
1​	01/02/2013​	8T3→001
2​	02/02/2013​	8T3→001
3​	03/02/2013​	8T3→001
4​	04/02/2013​	8T3→004
	tổng​

nếu cộng thủ công thì tổng là 7.
Cách của em làm là em phải làm ra thêm 1 cột nữa để lấy số sau đó mới cộng lại. Vậy em muốn hỏi là có công thức nào cộng mà không cần phải thêm cột ko ạ.
thanks

 

[Phan Thế Hiệp]

các anh cho em hỏi có các nào cộng tổng với tình huống thế này:

	a​	b​
1​	01/02/2013​	8T3→001
2​	02/02/2013​	8T3→001
3​	03/02/2013​	8T3→001
4​	04/02/2013​	8T3→004
	tổng​

nếu cộng thủ công thì tổng là 7.


Cách của em làm là em phải làm ra thêm 1 cột nữa để lấy số sau đó mới cộng lại. Vậy em muốn hỏi là có công thức nào cộng mà không cần phải thêm cột ko ạ.
Cảm ơn

Không cần thêm cột gì ráo, chỉ cần:

=SUM(A1:A5) =10, lận! sao bằng 7?

Thân

 

[lathanhtre]

Không cần thêm cột gì ráo, chỉ cần:

=SUM(A1:A5) =10, lận! sao bằng 7?

Thân

ý là muốn cộng số ở sao dấu"→" á. từ ngày 1 tới ngày 04/02/2013 là được 7 phiếu á
thanks

 

[Phan Thế Hiệp]

ý là muốn cộng số ở sao dấu"→" á. từ ngày 1 tới ngày 04/02/2013 là được 7 phiếu á
Cảm ơn

Dùng:
=SUMPRODUCT(--RIGHT(C2:C5,3)) Enter.

Thân

 

[tieuthubuongbinh]

Dùng:
=SUMPRODUCT(--RIGHT(C2:C5,3)) Enter.

Thân

Bác cho em hỏi -- có nghĩa gì ạ? em rất hay thấy mà ko hiểu

 

[Phan Thế Hiệp]

Bác cho em hỏi -- có nghĩa gì ạ? em rất hay thấy mà ko hiểu

Dựa vào toán học:

• -(-a) = --a = a
• Vd:
  • =-TEXT(125,"0")=-125
  • =--TEXT(125,"0")=125

Vận dụng điều này để "ép" a đang là dạng "chuỗi số" (Vd: chuỗi '12345) về thành số tính toán (12,345).
Có nhiều cách đưa "chuỗi số" về dạng số, gồm:

1. VALUE( "chuỗi số" a )
2. "chuỗi số" a nhân hay chia 1 (*1 ; /1), hoặc cộng hay trừ (+0 ; -0)
3. "chuỗi số" a cộng/trừ/nhân/chia/căn/lũy thừa với một "chuỗi số" khác.
   • Vd: =TEXT(10,"0")^TEXT(3,"0")=1000

Thân

 

[lathanhtre]

Dùng:
=SUMPRODUCT(--RIGHT(C2:C5,3)) Enter.

Thân

thanks anh nhiều ạ, mình làm được rồi.

 

[echo07]

Chào các bạn. Tôi cần tính tổng theo nhiều điều kiện như file đính kèm, cũng đã sử dụng công thức mảng nhưng không ra được kết quả như mong muốn. Rất mong anh em giúp đỡ. Xin cảm ơn trước. Nội dung cần trợ giúp đã ghi rõ trong file.

 

[Phan Thế Hiệp]

Chào các bạn. Tôi cần tính tổng theo nhiều điều kiện như file đính kèm, cũng đã sử dụng công thức mảng nhưng không ra được kết quả như mong muốn. Rất mong anh em giúp đỡ. Xin cảm ơn trước. Nội dung cần trợ giúp đã ghi rõ trong file.

Bạn giải thích cách tính toán chi tiết thêm:

1. Ngày 08/04/19 tại sao là 3
2. Ngày 22/04/19 tại sao là 8
3. Ngày 06/05/19 tại sao là 10
4. Ngày 29/07/19 tại sao là 7

Thân

 

[echo07]

Bạn giải thích cách tính toán chi tiết thêm:

1. Ngày 08/04/19 tại sao là 3 -->Em cần 3 người làm trong 47 ngày cho đầu mục công việc số 1.(cột số ngày LV) bắt đầu từ ngày 8/4 đến ngày 31/5. Do đó tất cả các ngày làm việc trong khoảng thời gian này đều bố trí 3 người. Các đầu việc khác không bố trí cùng thời gian này nên kết quả là 3.
2. Ngày 22/04/19 tại sao là 8 -- Em cộng tất cả các đầu việc lại, tương tự cho các ngày khác.
3. Ngày 06/05/19 tại sao là 10
4. Ngày 29/07/19 tại sao là 7

Thân

 

[Phan Thế Hiệp]

Ngày 08/04/19 tại sao là 3 -->Em cần 3 người làm trong 47 ngày (cột số ngày LV) bắt đầu từ ngày 8/4 đến ngày 31/5. Do đó tất cả các ngày làm việc trong khoảng thời gian này đều bố trí 3 người.

1. Ngày 22/04/19 tại sao là 8 -- Em cộng tất cả các đầu việc lại, tương tự cho các ngày khác.
2. Ngày 06/05/19 tại sao là 10
3. Ngày 29/07/19 tại sao là 7

À! tôi hiểu ý bạn rồi, phải xem cả bảng thời gian "bắt đầu - kết thúc" và vì nó không sắp thứ tự theo ngày nên ban đầu tôi nhầm chút.
Thử công thức sau:

H19=SUMPRODUCT((MMULT(COUNTIF(H$18,">="&$E$3:$F$8+{0,1}),{1;1})=1)*INDEX($B$3:$D$8,,MATCH($G19,$B$2:$D$2,)))

Enter, fill qua phải, rồi copy cả dòng xuống

Thân

 

[echo07]

Bạn trả lời chung chung quá đi!
Ví dụ: Ngày 22/04/19: tôi lấy B3=3 (từ ngày 08/04 đến mốc này), cộng B4=3 (từ ngày 20/04 đến mốc này) chỉ bằng 6 thôi mà???? sao bạn lại tính 8. ---> Em tính nháp cho phần Nhân công 1 trong phần bên trên đó ạ. Ngày 22/4 cần 8 người cho các đầu việc sau: Mục 1 =3 (ô i3), Mục 2 = 3 (ô i4) Mục 4 bằng 2 (ô i6) như vậy sum lại ngày đó là cần 8 người.

Giải thích càng chi tiết thì người giải mới biết bạn muốn tính như thế nào là đúng chứ!?

Thân

 

[Phan Thế Hiệp]

Đã giải cho bạn ở trên rồi đó.

Thân

 

[echo07]

Đã giải cho bạn ở trên rồi đó.

Thân

Vâng, cảm ơn bác, quá hay ạ. Em lại phải nghiên cứu thêm cái hàm MMULT này mới được.

 

[Phan Thế Hiệp]

Em lại phải nghiên cứu thêm cái hàm MMULT này mới được.

Đây, cho bạn "ngâm cứu" về "người đẹp" ấy.

Thân

 

[phihndhsp]

Vâng, cảm ơn bác, quá hay ạ. Em lại phải nghiên cứu thêm cái hàm MMULT này mới được.

Nếu bạn học toán tuyến tính thì sẽ hiểu được các quy luật của các phép tính trên ma trận, còn không học thì hơi bị nhứt cái đầu đó

 

[echo07]

Nếu bạn học toán tuyến tính thì sẽ hiểu được các quy luật của các phép tính trên ma trận, còn không học thì hơi bị nhứt cái đầu đó

Quá khó ấy ạ. Tạm thời em copy cái công thức này để giải quyết công việc cái đã.

 

[hue xoan]

Mình dùng hàm này nhưng không thể kết hợp được 2 điều kiện được. Bạn nào giúp mình với nhe
- Tôi muốn có một công thức thế này: nếu A1:A10 = "X" va B1:b10="Y" thì kq sẽ là : tổng từ c1:C10 thoả mãn 2 đk trên

bạn dùng thử: =sumifs(C1:C10,A1:A10,"X",B1:B10,"Y")

 

[tieuthubuongbinh]

Đây, cho bạn "ngâm cứu" về "người đẹp" ấy.

Thân

Em đã hiểu và ứng dụng được rồi, cám ơn anh lắm

 

[hue xoan]

giúp lập công thức

giúp mình lập công thức thống kê tổng số người theo từng loại thâm niên công tác,theo từng bộ phận nữa. Bảng dữ liệu ở sheet1, bảng tổng hợp ở sheet 2 . mình đã ghi chú trong file đính kèm
hepl meeeeeeeeeeeeeee
Xin cảm ơn

Em dùng countifs, anh xem hữu ích và đơn giản hơn ko:

 

[echo07]

Chào các bạn.
Tôi có vấn đề mới cần hỗ trợ chỗ tính thời gian lũy kế cho các hạng mục khác nhau, đã sử dụng công thức mảng nhưng kết quả không như ý muốn, rất mong anh chị em giúp đỡ. Chi tiêt vấn đề được nêu trong file đính kèm. Cảm ơn và chúc cả nhà cuối tuần vui vẻ.

 

[Phan Thế Hiệp]

Chào các bạn.
Tôi có vấn đề mới cần hỗ trợ chỗ tính thời gian lũy kế cho các hạng mục khác nhau, đã sử dụng công thức mảng nhưng kết quả không như ý muốn, rất mong anh chị em giúp đỡ. Chi tiêt vấn đề được nêu trong file đính kèm. Cảm ơn và chúc cả nhà cuối tuần vui vẻ.

Vấn đề quá hay! Truy lục ngày trùng theo từng mảng, nhưng với số giờ khác nhau, rồi tổng cộng cho 6 ngày, hoặc tính cho cả quá trình hoạt động.
Công thức tính số giờ của ngày trong tuần của bạn chưa đúng, nên kết quả sai.

Nhưng sáng nay tôi bận rồi, bạn chịu khó chờ nha, sẽ có nhiều anh em hỗ trợ bạn nhiều cách thú vị.

Chúc bạn ngày vui

 

[echo07]

Vấn đề quá hay! Truy lục ngày trùng theo từng mảng, nhưng với số giờ khác nhau, rồi tổng cộng cho 6 ngày, hoặc tính cho cả quá trình hoạt động.
Công thức tính số giờ của ngày trong tuần của bạn chưa đúng, nên kết quả sai.

Nhưng sáng nay tôi bận rồi, bạn chịu khó chờ nha, sẽ có nhiều anh em hỗ trợ bạn nhiều cách thú vị.

Chúc bạn ngày vui

Vâng, cảm ơn bác. Vấn đề là nó sai ở chỗ nào đó nên kết quả mới không đúng ạ.

 

[leonguyenz]

Vấn đề quá hay! Truy lục ngày trùng theo từng mảng, nhưng với số giờ khác nhau, rồi tổng cộng cho 6 ngày, hoặc tính cho cả quá trình hoạt động.
Công thức tính số giờ của ngày trong tuần của bạn chưa đúng, nên kết quả sai.

Nhưng sáng nay tôi bận rồi, bạn chịu khó chờ nha, sẽ có nhiều anh em hỗ trợ bạn nhiều cách thú vị.

Chúc bạn ngày vui

Gãi đúng chỗ ngứa của thầy rồi
Mà em thấy không ổn ở chỗ thời gian cột B, C so không cố định, tính toán kiểu quy hồi, gần với bài tính thời gian định cư liên tục 5 năm của anh huuthangbd

 

[echo07]

Gãi đúng chỗ ngứa của thầy rồi
Mà em thấy không ổn ở chỗ thời gian cột B, C so không cố định, tính toán kiểu quy hồi, gần với bài tính thời gian định cư liên tục 5 năm của anh huuthangbd

Không cố định có thể hiểu là mình có một hợp đồng gồm nhiều gói, mỗi gói có thời gian thực hiện khác nhau và số giờ công cho phép khác nhau nhưng tổng thời gian thực hiện và tổng số giờ được phép sử dụng thì cố định.

 

[tam888]

Gãi đúng chỗ ngứa của thầy rồi
Mà em thấy không ổn ở chỗ thời gian cột B, C so không cố định, tính toán kiểu quy hồi, gần với bài tính thời gian định cư liên tục 5 năm của anh huuthangbd

Bài này excel công thức thường giải quyết tốt và dễ hiểu, nhưng phải bảng phụ
Khó ở đây là do ta muốn công thức khủng, mà khủng là tự làm khó rồi
Công thức khủng đó chỉ trông ngắn gọn, còn tốc độ tính toán thì chắc sẽ mệt.
Vẫn muốn ngắn gọn thì nên dùng VBA

 

[leonguyenz]

Bài này excel công thức thường giải quyết tốt và dễ hiểu, nhưng phải bảng phụ
Khó ở đây là do ta muốn công thức khủng, mà khủng là tự làm khó rồi

Bài này em nghĩ là dạng khó.
Dữ liệu có trùng ngày, tuần (cột B, C), không biết chọn dòng nào phù hợp (đưa ra 1 tập hợp số, cột E), phân bổ tập hợp số đó thỏa điều kiện thời gian tăng dần (theo tuần) và tổng bằng số cho trước (được tính ra ở F11).

 

[tam888]

Bài này em nghĩ là dạng khó.
Dữ liệu có trùng ngày, tuần (cột B, C), không biết chọn dòng nào phù hợp (đưa ra 1 tập hợp số, cột E), phân bổ tập hợp số đó thỏa điều kiện thời gian tăng dần (theo tuần) và tổng bằng số cho trước (được tính ra ở F11).

Cứ tính từng Hoạt động theo tuần (cộng dồn), thì được bảng các thời gian tuần cho các hoạt động, tổng cột, rồi tổng tất cả là được kết quả mong muốn.

Tính từng hoạt động theo tuần (liệt kê) thì dễ dàng rồi xét khoảng thời gian hiệu theo số ngày trong tuần (min, max ngày) thì bạn tính tốt dễ dàng.

==> cần 1 bảng phụ

 

[echo07]

Cứ tính từng Hoạt động theo tuần (cộng dồn), thì được bảng các thời gian tuần cho các hoạt động, tổng cột, rồi tổng tất cả là được kết quả mong muốn.

Tính từng hoạt động theo tuần (liệt kê) thì dễ dàng rồi xét khoảng thời gian hiệu theo số ngày trong tuần (min, max ngày) thì bạn tính tốt dễ dàng.

==> cần 1 bảng phụ

Em nghĩ cũng không quá khó với các bác đâu ạ. Nếu thử kéo dài thời gian kết thúc ra đến ngày 7/2/20 thì sai số theo em tính chỉ là 0.26%. Đấy là phải tính thêm số giờ theo ngày và theo tuần rồi mới cộng lũy kế lại.
Vấn đề em muốn là chỉ cần dựa vào cột F (Số giờ cho phép) và các cột C và D để rải vào các ngày từ lúc bắt đầu đến lúc kết thúc theo dạng lũy kế như ở dòng số 4 thôi và mục tiêu cuối cùng là đường biểu đồ vừa lên đúng với số giờ cho phép.

 

[tam888]

Em nghĩ cũng không quá khó với các bác đâu ạ. Nếu thử kéo dài thời gian kết thúc ra đến ngày 7/2/20 thì sai số theo em tính chỉ là 0.26%. Đấy là phải tính thêm số giờ theo ngày và theo tuần rồi mới cộng lũy kế lại.
Vấn đề em muốn là chỉ cần dựa vào cột F (Số giờ cho phép) và các cột C và D để rải vào các ngày từ lúc bắt đầu đến lúc kết thúc theo dạng lũy kế như ở dòng số 4 thôi và mục tiêu cuối cùng là đường biểu đồ vừa lên đúng với số giờ cho phép.
View attachment 217951

Xem file kèm
Với File thứ 2 thì bạn tự làm theo, và lưu ý kéo ngày đúng tuần +7 (file 2 phải kéo đến tháng 3)

 

[Phan Thế Hiệp]

Gãi đúng chỗ ngứa của thầy rồi
Mà em thấy không ổn ở chỗ thời gian cột B, C so không cố định, tính toán kiểu quy hồi, gần với bài tính thời gian định cư liên tục 5 năm của anh huuthangbd

Sáng nhìn vội, nên cũng hình dung trong đầu y vậy. Nhưng vừa về xem lại thì có lẽ nó nhẹ nhàng hơn tí!

Vấn đề em muốn là chỉ cần dựa vào cột F (Số giờ cho phép) và các cột C và D để rải vào các ngày từ lúc bắt đầu đến lúc kết thúc theo dạng lũy kế như ở dòng số 4 thôi và mục tiêu cuối cùng là đường biểu đồ vừa lên đúng với số giờ cho phép.

Chắc là công thức này:

I4=SUMPRODUCT((I1+COLUMN($A:$F)-1>=$B$2:$B$10)*(I1+COLUMN($A:$F)-1<=$C$2:$C$10)*$E$2:$E$10)

Chỉ Enter, rồi fill qua phải.

Thân

 

[HieuCD]

Sáng nhìn vội, nên cũng hình dung trong đầu y vậy. Nhưng vừa về xem lại thì có lẽ nó nhẹ nhàng hơn tí!

Chắc là công thức này:

I4=SUMPRODUCT((I1+COLUMN($A:$F)-1>=$B$2:$B$10)*(I1+COLUMN($A:$F)-1<=$C$2:$C$10)*$E$2:$E$10)

Chỉ Enter, rồi fill qua phải.

Thân

Chắc không phải rùi, dòng 4 là số lũy kế cộng dồn các tuần trước
Chúc bạn 1 tối vui

 

[Phan Thế Hiệp]

Chắc không phải rùi, dòng 4 là số lũy kế cộng dồn các tuần trước
Chúc bạn 1 tối vui

Vậy, cộng với ô đằng trước chắc "hợp với dáng em".
Cảm ơn anh @HieuCD.

I4=SUMPRODUCT((I1+COLUMN($A:$F)-1>=$B$2:$B$10)*(I1+COLUMN($A:$F)-1<=$C$2:$C$10)*$E$2:$E$10)+N(H4)
Enter, rồi fill qua phải.

Chúc anh ngày vui

 

[echo07]

Vậy, cộng với ô đằng trước chắc "hợp với dáng em".
Cảm ơn anh @HieuCD.

I4=SUMPRODUCT((I1+COLUMN($A:$F)-1>=$B$2:$B$10)*(I1+COLUMN($A:$F)-1<=$C$2:$C$10)*$E$2:$E$10)+N(H4)
Enter, rồi fill qua phải.

Chúc anh ngày vui

Cảm ơn các bác nhiều. Tối muộn vẫn hỗ trợ anh em. Chúc các bác một tuần mới nhiều niềm vui và hạnh phúc.

 

[echo07]

Vậy, cộng với ô đằng trước chắc "hợp với dáng em".
Cảm ơn anh @HieuCD.

I4=SUMPRODUCT((I1+COLUMN($A:$F)-1>=$B$2:$B$10)*(I1+COLUMN($A:$F)-1<=$C$2:$C$10)*$E$2:$E$10)+N(H4)
Enter, rồi fill qua phải.

Chúc anh ngày vui

Hay quá bác ạ. Vấn đề phát sinh là dữ liệu của em có thể thay đổi nên các cột cũng vì thế thay đổi theo. Với "COLUMN($A:$F)" như này em thêm hoặc bớt cột ở trong khoảng đó đều cho kết quả bị sai. Em đã thừ thay thế bằng
=SUMPRODUCT((J1+{1,2,3,4,5,6}-1>=$A$2:$A$10)*(J1+{1,2,3,4,5,6}-1<=$B$2:$B$10)*$F$2:$F$10)+N(I5) thì có thể thay đổi số cột thoải mái. Vậy mình có thể thay thế dạng COLUMN($A:$F) này bằng dạng khác để không bị ảnh hưởng bởi số cột trong đó không ạ.
Một vấn đề nhỏ nữa là trong ví dụ này thì ngày cuối cùng kết thúc là 19/12 nhưng kết quả tính đến ngày 16/12 đã hết khối lượng rồi do đó 3 ngày sau không được tính ạ.

 

[Phan Thế Hiệp]

Hay quá bác ạ. Vấn đề phát sinh là dữ liệu của em có thể thay đổi nên các cột cũng vì thế thay đổi theo. Với "COLUMN($A:$F)" như này em thêm hoặc bớt cột ở trong khoảng đó đều cho kết quả bị sai. Em đã thừ thay thế bằng
=SUMPRODUCT((J1+{1,2,3,4,5,6}-1>=$A$2:$A$10)*(J1+{1,2,3,4,5,6}-1<=$B$2:$B$10)*$F$2:$F$10)+N(I5) thì có thể thay đổi số cột thoải mái. Vậy mình có thể thay thế dạng COLUMN($A:$F) này bằng dạng khác để không bị ảnh hưởng bởi số cột trong đó không ạ.
Một vấn đề nhỏ nữa là trong ví dụ này thì ngày cuối cùng kết thúc là 19/12 nhưng kết quả tính đến ngày 16/12 đã hết khối lượng rồi do đó 3 ngày sau không được tính ạ.

View attachment 218011

Nếu bạn muốn tùy biến "Ngày bất kỳ", thì tôi đưa ra các trường hợp sau:

1. Phải xử lý "Ngày" bạn ghi nhận vào hàng 1 (I1: U1) là ngày bất kỳ trong tuần. Ví dụ: I1 có thể bắt đầu từ Chủ nhật (29/09/2019). Công thức sẽ loại ra các Ngày Chủ nhật cho bạn, đồng thời chỉ lấy những ngày có trong khoảng thời gian "Ngày bắt đầu - Ngày kết thúc" của bảng C2: D10
2. Công thức sẽ lấy các ngày trong khoảng từ giá trị "Ngày" của cột đang thực hiện cho đến "Nhỏ" hơn "Ngày" của cột liền kế sau. Vd: công thức tại I4 thì nó lấy các ngày "thỏa điều kiện" từ ngày 30/09 đến ngày 05/10 (như dữ liệu trong file kèm thể hiện tại I1 và J1). Nhờ điều này, bạn không cần phải buộc giữa các cột là 6 ngày cố định, mà có thể tăng giảm tùy thích, (do đó không còn ngại Column(A:F))
3. Nếu cột "Ngày" liền kế sau trống rỗng, thì chỉ lấy duy nhất "Ngày" của cột mà công thức đang thực hiện.

Do các yếu tố trên, nên công thức sẽ dài hơn:

I4=SUM((TRANSPOSE(IFERROR(ROW(INDIRECT(I$1&":"&J$1-1)),I$1)*(WEEKDAY(IFERROR(ROW(INDIRECT(I$1&":"&J$1-1)),I$1))>1))>=$B$2:$B$10)*(TRANSPOSE(IFERROR(ROW(INDIRECT(I$1&":"&J$1-1)),I$1)*(WEEKDAY(IFERROR(ROW(INDIRECT(I$1&":"&J$1-1)),I$1))>1))<=$C$2:$C$10)*$E$2:$E$10)+N(H4)

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

Thân

 

[echo07]

Nếu bạn muốn tùy biến "Ngày bất kỳ", thì tôi đưa ra các trường hợp sau:

1. Phải xử lý "Ngày" bạn ghi nhận vào hàng 1 (I1: U1) là ngày bất kỳ trong tuần. Ví dụ: I1 có thể bắt đầu từ Chủ nhật (29/09/2019). Công thức sẽ loại ra các Ngày Chủ nhật cho bạn, đồng thời chỉ lấy những ngày có trong khoảng thời gian "Ngày bắt đầu - Ngày kết thúc" của bảng C2: D10
2. Công thức sẽ lấy các ngày trong khoảng từ giá trị "Ngày" của cột đang thực hiện cho đến "Nhỏ" hơn "Ngày" của cột liền kế sau. Vd: công thức tại I4 thì nó lấy các ngày "thỏa điều kiện" từ ngày 30/09 đến ngày 05/10 (như dữ liệu trong file kèm thể hiện tại I1 và J1). Nhờ điều này, bạn không cần phải buộc giữa các cột là 6 ngày cố định, mà có thể tăng giảm tùy thích, (do đó không còn ngại Column(A:F))
3. Nếu cột "Ngày" liền kế sau trống rỗng, thì chỉ lấy duy nhất "Ngày" của cột mà công thức đang thực hiện.

Do các yếu tố trên, nên công thức sẽ dài hơn:

I4=SUM((TRANSPOSE(IFERROR(ROW(INDIRECT(I$1&":"&J$1-1)),I$1)*(WEEKDAY(IFERROR(ROW(INDIRECT(I$1&":"&J$1-1)),I$1))>1))>=$B$2:$B$10)*(TRANSPOSE(IFERROR(ROW(INDIRECT(I$1&":"&J$1-1)),I$1)*(WEEKDAY(IFERROR(ROW(INDIRECT(I$1&":"&J$1-1)),I$1))>1))<=$C$2:$C$10)*$E$2:$E$10)+N(H4)

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

Thân

Cảm ơn bác rất nhiều. Em sẽ nghiên cứu thêm để áp dụng cho thuận tiện nhất ạ. Chúc bác và gia đình sức khỏe và hạnh phúc.

 

[Phan Thế Hiệp]

Chúc bác và gia đình sức khỏe và hạn phúc.

Vừa bị "Hạn", lại vừa được "Phúc", thì kể như "huề vốn"
A1= -9 + 9 = 0

Vậy, nên ghi "Chúc huề vốn"
Thân