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

Liên hệ QC
em cần tính số tiền phải nộp BHXH tự nguyện cho nhiều năm về sau, tối đa là 5 năm với công thức theo quy định là
aDMReEr.png

vậy có cách nào không cần lập biểu tính từng tháng một rồi tính tổng không ạ? e xin cám ơn
 
em cần tính số tiền phải nộp BHXH tự nguyện cho nhiều năm về sau, tối đa là 5 năm với công thức theo quy định là
aDMReEr.png

vậy có cách nào không cần lập biểu tính từng tháng một rồi tính tổng không ạ? e xin cám ơn
=-PV(0.00628,2*12,3000000*0.22,,1)
 
e tính tổng các cột theo điều kiện bao gồm cả dòng trống dùng hàm sumproduct, bị lỗi, các anh, chị có cách nào hay hơn chỉ giúp e với ạ
 

File đính kèm

cám ơn bạn đã hỗ trợ, mình thấy trong cell 90, 91 của mình k có dữ liệu gì mà, bạn giải thích giúp mình với
2 cell đó không biết bạn thao tác copy dán từ đâu: nó đang là kiểu dữ liệu text chứ không phải Number: nên Sum không được.
( bạn thử dùng Istext() là thấy ak)
:-)
 
Trong các ứng dụng về công thức mãng mình được biết thì dạng công thức mãng dạng sum nhiều điều kiện là mình tâm đắc nhất vì cú pháp đơn giản, khi kết hợp với những hàm khác thì nó rất linh hoạt , giải quyết rất nhiều tình huống trước đây mình xử lý bằng hàm thông thường rất vất vã, hình như nó tính hơi lâu nhưng kết quả rất ưng ý
Ví dụ
1/ sum nhiều if - thay thế một macro rút trích rồi tính tổng
vd:tính tiền thu được do bán mặt hàng là "sơn" thời gian từ 01/06/07 ( tungay) đến 30/06/07( denngay)
{sum(if(ngay=>tungay,1,0)*if(ngay<=denngay,1,0)*if(mat_hang="son",1,0)*tien)}
2/ Sumproduct nhiều if
vd:tính tổng diện tích các thửa ruộng ấp A, xã B, Huyện C
{sum(if(ap="a",1,0)*if(xa="b",1,0)*if(huyen="c",1,0)*dai*rong)}
3/ count nhiều if
vd: đếm số sv điểm trên trung bình là nam,lớp B, khoa C, Trường D
{sum(if(diem>=5,1,0)*if(gioitinh="nam",1,0)*if(lop="b",1,0)*if(khoa="c",1,0)*if(truong="d",1,0)}
4/max nhiều if
vd:tự động nhảy số hóa đơn thứ mấy xuất cho đơn vị A, chứ không phải là STT hóa đơn, stt hóa đơn =max(stt_hd)
{max(if(dv="a",1,0)*shd_dv)}
5/ Vlookup_if
vd: tìm trong vùng dữ liệu cột 1 mã máy bằng AAA, cột ngày di chuyển=gần nhất, xem nơi đến là nơi nào ( tìm giá trị cột noi_den )
- kết hợp hàm findtwoconditions trên giaiphapexcel và hàm max_if trên
Bạn ơi, có thể cho mình xin file exel ví dụ minh họa cho cá công thức trên được ko.
mình làm thử rồi mà ko ra kết quả.
Thanks bạn ^^
 

File đính kèm

em muốn tính tổng có điều kiện cho khoảng 500 đầu mục công việc mà không biết dùng hàm nào cho nhanh. Các anh, chị có cách nào giúp em với ạ
 

File đính kèm

em muốn tính tổng có điều kiện cho khoảng 500 đầu mục công việc mà không biết dùng hàm nào cho nhanh. Các anh, chị có cách nào giúp em với ạ
Dữ liệu của bạn gửi tuy tương đồng kết cấu, nhưng phần chi tiết yêu cầu "cộng phân khoản" không phức tạp như file theo đường link mà tôi đề cập trên.
Do vậy, tôi thực hiện cho bạn cùng giải pháp nhưng đơn giản hơn chút, với một vài lưu ý như sau:
  1. Về chi tiết cộng: bạn chỉ muốn phân nhỏ theo 3 mục (!?): Vật liệu, Nhân công, và Máy nhằm cộng Vùng theo từng tiêu chí đó để ra số tính toán cho "Vật liệu khác" và "Máy khác". Nếu đúng như vậy, thì bạn nên lưu ý chỗ các dòng bạn muốn cộng cho "Máy khác" phải đảm bảo có chữ "Máy" nằm ở cột Nội dung công việc, Vd: ô C25: Cần cẩu xích 10T, bạn phải thêm chữ "(Máy)" để báo cho công thức biết mà phân biệt. Tóm lại, nôm na hễ có "Nhân" và "Máy" thì hiểu là "Nhân công" và "Máy móc", các cái khác còn lại là "Vật liệu". Nếu bạn muốn chính xác hơn nữa thì bạn phải tạo thêm cột phân loại theo ba tiêu chí đó, nhưng hơi rườm rà tí, còn nếu như cách nêu trên là tạm ổn thì cứ việc áp dụng công thức dưới đây.
  2. Cột A số thứ tự (STT) tuy nhìn nó rất đơn giản và bình thường, nhưng chính nhờ nó mà bạn phân Vùng được chính xác. Nên nó là cốt lõi cho công thức cộng tại cột H phía sau. Do bạn chỉ phân biệt 3 chỉ tiêu như trên, nên công thức chỉ đơn giản có "Mẹ" và "Con" thôi không cần "Cháu" :)
  3. Công thức tại cột H tôi dùng SUM() và phân từng đoạn Vùng nhỏ và phù hợp với yêu cầu để không chiếm nhiều bộ nhớ máy. Nên nếu bạn có hơn "500 đầu mục" thì không lo nặng máy. Tôi cũng tính áp dụng Sumif(...,".??",....) như cách bên file hướng dẫn theo link kia, nhưng thấy nó kéo Vùng vào bộ nhớ hơi nhiều (với hơn 500 đầu mục), nên đã chọn giải pháp SUM(OFFSET()).
Công thức áp dụng:
Tính số thứ tự:
Mã:
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.
Tính Cộng Phân Vùng hoặc Tính Giá trị dòng Chi tiết:
Mã:
H4=IF(A4<>"",ROUND(IF(B4<>"",SUM(H5:OFFSET(H4,MATCH(1,INDEX(N(A4<>INT(--A4:A1000)),),)-2,)),PRODUCT(E4,F4,G4)*IF(OR(C4={"Vật liệu khác","Máy khác"}),SUM(OFFSET($H$3,MATCH(1,INDEX(N(A4=$A$3:A3),),)-1,):H3)%,1)),),"")
Fill xuống.
Xem file đính kèm.

Chúc bạn học tập vui với GPE.
hihi ^o^
 

File đính kèm

Dữ liệu của bạn gửi tuy tương đồng kết cấu, nhưng phần chi tiết yêu cầu "cộng phân khoản" không phức tạp như file theo đường link mà tôi đề cập trên.
Do vậy, tôi thực hiện cho bạn cùng giải pháp nhưng đơn giản hơn chút, với một vài lưu ý như sau:
  1. Về chi tiết cộng: bạn chỉ muốn phân nhỏ theo 3 mục (!?): Vật liệu, Nhân công, và Máy nhằm cộng Vùng theo từng tiêu chí đó để ra số tính toán cho "Vật liệu khác" và "Máy khác". Nếu đúng như vậy, thì bạn nên lưu ý chỗ các dòng bạn muốn cộng cho "Máy khác" phải đảm bảo có chữ "Máy" nằm ở cột Nội dung công việc, Vd: ô C25: Cần cẩu xích 10T, bạn phải thêm chữ "(Máy)" để báo cho công thức biết mà phân biệt. Tóm lại, nôm na hễ có "Nhân" và "Máy" thì hiểu là "Nhân công" và "Máy móc", các cái khác còn lại là "Vật liệu". Nếu bạn muốn chính xác hơn nữa thì bạn phải tạo thêm cột phân loại theo ba tiêu chí đó, nhưng hơi rườm rà tí, còn nếu như cách nêu trên là tạm ổn thì cứ việc áp dụng công thức dưới đây.
  2. Cột A số thứ tự (STT) tuy nhìn nó rất đơn giản và bình thường, nhưng chính nhờ nó mà bạn phân Vùng được chính xác. Nên nó là cốt lõi cho công thức cộng tại cột H phía sau. Do bạn chỉ phân biệt 3 chỉ tiêu như trên, nên công thức chỉ đơn giản có "Mẹ" và "Con" thôi không cần "Cháu" :)
  3. Công thức tại cột H tôi dùng SUM() và phân từng đoạn Vùng nhỏ và phù hợp với yêu cầu để không chiếm nhiều bộ nhớ máy. Nên nếu bạn có hơn "500 đầu mục" thì không lo nặng máy. Tôi cũng tính áp dụng Sumif(...,".??",....) như cách bên file hướng dẫn theo link kia, nhưng thấy nó kéo Vùng vào bộ nhớ hơi nhiều (với hơn 500 đầu mục), nên đã chọn giải pháp SUM(OFFSET()).
Công thức áp dụng:
Tính số thứ tự:
Mã:
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.
Tính Cộng Phân Vùng hoặc Tính Giá trị dòng Chi tiết:
Mã:
H4=IF(A4<>"",ROUND(IF(B4<>"",SUM(H5:OFFSET(H4,MATCH(1,INDEX(N(A4<>INT(--A4:A1000)),),)-2,)),PRODUCT(E4,F4,G4)*IF(OR(C4={"Vật liệu khác","Máy khác"}),SUM(OFFSET($H$3,MATCH(1,INDEX(N(A4=$A$3:A3),),)-1,):H3)%,1)),),"")
Fill xuống.
Xem file đính kèm.

Chúc bạn học tập vui với GPE.
hihi ^o^
Sao vẫn không được vậy ạ. Em toàn thấy VALUE thôi
 
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 :D
Bạn hãy nhìn kỹ công thức.
Mã:
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
Mã:
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
Mã:
=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.
 

File đính kèm

Web KT

Bài viết mới nhất

Back
Top Bottom