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

Liên hệ QC
bebo021999 đã viết:
Lỗi là chắc rồi với dữ liệu không bất nhất. Trong ví dụ chỉ là 1ký số+1 ký tự, (1a,2b) bây giờ lại ngược lại (a1,b2). Bạn đưa hết các trường hợp vào và post lại xem sao
Nhấp chuột vào đây để mở rộng...
Giá trị cột F là biến số có thể thay đổi bất kỳ và có thể trùng nhau chứ không phải là giá trị xác định như trên bài ạ.
 
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
 
phamnamha93 đã viết:
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
Nhấp chuột vào đây để mở rộng...
=-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

  • loi value.xlsm
    12.7 KB · Đọc: 10
queluatb đã viết:
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
Nhấp chuột vào đây để mở rộng...
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)
:)
 
kietbui đã viết:
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
Nhấp chuột vào đây để mở rộng...
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

  • sumifs.xlsx
    11.1 KB · Đọc: 16
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

  • hoi-dap.xlsx
    175 KB · Đọc: 7
noidoi đã viết:
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 ạ
Nhấp chuột vào đây để mở rộng...
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

  • hoi-dap.xlsx
    16.3 KB · Đọc: 13
Mộng Thường 2001 đã viết:
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^
Nhấp chuột vào đây để mở rộng...
Sao vẫn không được vậy ạ. Em toàn thấy VALUE thôi
 
Bạn phải đăng nhập hoặc đăng ký để trả lời bài viết tại đây.
Web KT

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

B
  • Question
Chuyển đổi dữ liệu sạng dạng mảng
Trả lời
5
Đọc
80
ongke0711
ongke0711
LuuAnh980
  • Question
Code lấy dữ liệu từ 3 file con.
Trả lời
11
Đọc
158
ho van luong
H
S
Nhờ giúp đỡ code in toàn bộ danh sách có điều kiện
Trả lời
6
Đọc
137
Hoàng Tuấn 868
H
S
  • Question
Không lấy đủ dữ liệu bằng kết nối ADO
Trả lời
0
Đọc
59
shnhatha221108
S
T
Không hiện dữ liệu ở ô công thức
Trả lời
3
Đọc
72
thien-16
T
Xem thêm…

Facebook

Giaiphapexcel.com

Group

DIỄN ĐÀN GIẢI PHÁP EXCEL Group 1
DIỄN ĐÀN GIẢI PHÁP EXCEL Group 2

Thành viên có số lượng bài viết cao nhất tháng

Thành viên có điểm tương tác cao nhất tháng

Back
Top Bottom