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

[HitmanC47]

Thực sự là dùng công thức mảng rất là chậm đối với bảng dữ liệu cơ sở >1000 record. Nhưng mà vì đặc thù của việc lập báo cáo quản trị đòi hỏi yếu tố "nhanh" và "kịp thời" cho nên mình chả còn cách nào khác để đáp ứng được yêu cầu thông tin từ cấp trên nên đành phải dùng tới công thức mảng. Bây giờ thì thấm thía quá rồi, file báo cáo theo tháng chạy ì ạch vào thời điểm cuối tháng, đến là khổ.
Bạn nào biết có giải pháp nào thay thế cho công thức mảng mà tăng được tốc độ xử lý lên không? Tư vấn mình với...

 

[ndu96081631]

Thực sự là dùng công thức mảng rất là chậm đối với bảng dữ liệu cơ sở >1000 record. Nhưng mà vì đặc thù của việc lập báo cáo quản trị đòi hỏi yếu tố "nhanh" và "kịp thời" cho nên mình chả còn cách nào khác để đáp ứng được yêu cầu thông tin từ cấp trên nên đành phải dùng tới công thức mảng. Bây giờ thì thấm thía quá rồi, file báo cáo theo tháng chạy ì ạch vào thời điểm cuối tháng, đến là khổ.
Bạn nào biết có giải pháp nào thay thế cho công thức mảng mà tăng được tốc độ xử lý lên không? Tư vấn mình với...

Giải pháp là:
- Nhập liệu thật đúng chuẩn CSDL
- Dữ liệu luôn được sort trước (theo tiêu chí nào đó mà ta đang quan tâm)
- Đặt name phù hợp để giới hạn vùng tính toán ---> Mục đích cuối cùng để thay SUMPRODUCT nhiều điều kiện thành SUMIF 1 điều kiện
--------------------------------
Nguyên tắc chung là vậy nhưng còn tùy vào dữ liệu cụ thể mới quyết định được

 

[dvu58]

Hỏi về cách tìm Min theo một số điều kiện

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

Cảm ơn bạn kietbui đã hướng dẫn 1 số hàm mảng sử dụng có hiệu quả.
Tuy nhiên, mình muốn hỏi thêm về cách tim Min theo một số điều kiện.

VD:
Giả sử Có bảng điểm chung các lớp 1A, 1B, 2A, 2B, ....
Tôi lập 1 danh sách tổng hợp tình hình điểm cao nhất và điểm thấp nhất của từng lớp -
theo cột mục:

STT : Lớp : Điểm thấp nhất : Điểm cao nhất
1 : 1A : ? : Max (mảng)
2 : 1B : ? : .......
............


+ Điểm cao nhất của từng lớp dùng hàm Max mảng như trên là ok rồi.
+ Điểm thấp nhất của từng lớp thì tìm như thế nào?

(Do mình không còn quota nên không gửi file ví dụ lên được. Mong các bạn hướng dẫn chung)

Cảm ơn các bạn,

 

[ndu96081631]

+ Điểm cao nhất của từng lớp dùng hàm Max mảng như trên là ok rồi.
+ Điểm thấp nhất của từng lớp thì tìm như thế nào?

(Do mình không còn quota nên không gửi file ví dụ lên được. Mong các bạn hướng dẫn chung)

Cảm ơn các bạn,

Với hàm MIN có điều kiện thì dùng thế này

MIN(IF(Vùng điều kiện = Điều kiện, Vùng kết quả, ""))

Ngày cả hàm MAX cũng thế, anh "tưởng" công thức ở trên dùng được nhưng thật ra vẫn có vấn đề... anh thử với số âm sẽ thấy
Vậy, cả MIN và MAX thì vẫn nên dùng công thức tổng quát như em viết ở trên, chắc ăn không sai

 

[thanhhuyennt86]

Nhờ giúp đỡ về hàm sumproduct

Các anh, chị giúp em bảng tính này với ạ. Em muốn tạo một bảng tổng hợp xuất, tồn trên PM FCC và xuất tồn thực tế theo mã hàng và phòng GD, em đã sử dụng hàm sumproduct, nhưng với cột số lượng thì được nhưng cột thành tiền thì nó báo Value. Có ai giúp e vấn đề này với ạ. Em cảm ơn nhìu nhìu

 

[phihndhsp]

Trong sheet1 tại Cột P
P8=IF(K8<>0,N8*O8,0) tự động hàm sumproduct của bạn được thôi
nếu muốn đẹp thì định dạng cho mất số 0

 

[thanhhuyennt86]

Trong sheet1 tại Cột P
P8=IF(K8<>0,N8*O8,0) tự động hàm sumproduct của bạn được thôi
nếu muốn đẹp thì định dạng cho mất số 0

Em đã sửa lại công thức như anh chỉ nhưng nó vẫn ra lỗi Value. Anh xem lại giúp em lần nữa được không ạ?

 

[huuthang_bd]

Em đã sửa lại công thức như anh chỉ nhưng nó vẫn ra lỗi Value. Anh xem lại giúp em lần nữa được không ạ?

Sau khi sửa công thức bạn phải copy cho cả cột P thì mới được.

 

[candy_man253]

Hàm sumproduct bị lỗi

Mình làm công thức Sumproduct bên bảng tổng hợp với mã " TC,AP,CK" nó bị lỗi mình chỉnh không được nhờ mọi người giúp dùm...Thanks

http://www.mediafire.com/?7d3mhv84d4hv6lp

 

[nghiaphuc]

Mình làm công thức Sumproduct bên bảng tổng hợp với mã " TC,AP,CK" nó bị lỗi mình chỉnh không được nhờ mọi người giúp dùm...Thanks

http://www.mediafire.com/?7d3mhv84d4hv6lp

Nguyên nhân do các mảng trong Sumproduct không cùng kích thước.
Bạn sửa lại như vầy là được (chú ý chỗ màu đỏ):

F9=SUMPRODUCT(('NHAP LIEU'!$B$3:$B$[COLOR=#ff0000]72[/COLOR]=$B9)*('NHAP LIEU'!$M$3:$M$[COLOR=#ff0000]72[/COLOR]=F$8)*('NHAP LIEU'!$O$3:$O$[COLOR=#ff0000]72[/COLOR]))

 

[candy_man253]

Thanks! bạn nha...Mình sửa lại 72 là được hết rồi, củng bên bảng đó mình làm công thức này 0 được bạn xem giúp.

http://www.mediafire.com/?s1154ahwq41wa2n

 

[nghiaphuc]

Thanks! bạn nha...Mình sửa lại 72 là được hết rồi, củng bên bảng đó mình làm công thức này 0 được bạn xem giúp.
http://www.mediafire.com/?s1154ahwq41wa2n

Nguyên nhân là từ công thức tại cột I trên sheet NHAP LIEU: Công thức =IF(H3="","",G3*H3) sẽ làm phát sinh trong vùng I3:I72 những chuỗi "", do đó không thể đem nhân vào 2 mảng trước được.
Bạn sửa lại công thức cho cột I như vầy là OK: =IF(H3="",0,G3*H3)

 

[tpt909]

Tính tổng giá bằng hàm SUMPRODUCT

Tình hình là em đang làm cái bang tính tog giá tiền cho từng loại hàng bán ra trong từng tháng. Em dung hàm SUMPRODUCT có thể tính được ngay tong số lượng từng mục bán ra. Nhưng vấn đề là khi nhân với cái giá của từng loại thì em không biết cách nào lồng vào hàm Sumproduct luôn, mà phải them nhiều cột phụ rối bang quá. Bác nào biết cách chỉ giùm em với, em cám ơn các bác nhiều.

 

[tvxdgt]

Tình hình là em đang làm cái bang tính tog giá tiền cho từng loại hàng bán ra trong từng tháng. Em dung hàm SUMPRODUCT có thể tính được ngay tong số lượng từng mục bán ra. Nhưng vấn đề là khi nhân với cái giá của từng loại thì em không biết cách nào lồng vào hàm Sumproduct luôn, mà phải them nhiều cột phụ rối bang quá. Bác nào biết cách chỉ giùm em với, em cám ơn các bác nhiều.

Bạn sửa lại công thức như thế này xem ở ô B20:

=SUMPRODUCT(--($A$2:$A$17=A20),B$2:B$17*VLOOKUP(A20,Price!$A$2:$B$5,2,0))

 

[phanan24]

Xin góp ý về hàm Sumproduct

Tôi có một bảng tính sử dụng Hàm Sumproduct nhưng gặp một chuyện khó hiểu, không biết giải thích thế nào. Nhờ Giải pháp Excel gỡ rối dùm. Xem file đính kèm. trong file đã ghi chú thích.

 

[Ba Tê]

Tôi có một bảng tính sử dụng Hàm Sumproduct nhưng gặp một chuyện khó hiểu, không biết giải thích thế nào. Nhờ Giải pháp Excel gỡ rối dùm. Xem file đính kèm. trong file đã ghi chú thích.

Sửa công thức ô L58 như vầy thử xem:

=SUMPRODUCT(($C$4:$C$53="X")*($R$4:$R$53>=8)*($R$4:$R$53<>""))

Thêm điều kiện <>""

 

[phanan24]

Cảm ơn bạn. Vậy việc cùng công thức, cùng điều kiện nhưng sao chỗ thì cộng chỗ thì không cộng. thậm chí khi không đủ điều kiện nõ vẫn cộng là sao. hay tôi lập công thức sai ở chỗ nào đó. mong "Ba te" và GPE xem kĩ lại dùm.

 

[Ba Tê]

Cảm ơn bạn. Vậy việc cùng công thức, cùng điều kiện nhưng sao chỗ thì cộng chỗ thì không cộng. thậm chí khi không đủ điều kiện nõ vẫn cộng là sao. hay tôi lập công thức sai ở chỗ nào đó. mong "Ba te" và GPE xem kĩ lại dùm.

Bạn đưa một bảng dữ liệu trông rỗng, làm sao có số liệu kiểm tra công thức?
Chả lẽ lại nhập liệu giùm bạn luôn sao?

 

[phanan24]

Mình gởi bản trống là vì khi để dữ liệu trống ở cột điểm thi thì một số ô không cộng(màu xanh), số ô màu vàng vẫn cộng (phần nữ, nữ dân tộc).
Nếu nhập đầy đủ dữ liệu thì nó cộng bình thường. Vậy mới khó hiểu. Với bảng tính đó chỉ cần nhập mấy điểm (1, 4, 6, 8 gì đó) vào cột điểm thi là được thôi mà. Xin cảm ơn.

 

[Ba Tê]

Mình gởi bản trống là vì khi để dữ liệu trống ở cột điểm thi thì một số ô không cộng(màu xanh), số ô màu vàng vẫn cộng (phần nữ, nữ dân tộc).
Nếu nhập đầy đủ dữ liệu thì nó cộng bình thường. Vậy mới khó hiểu. Với bảng tính đó chỉ cần nhập mấy điểm (1, 4, 6, 8 gì đó) vào cột điểm thi là được thôi mà. Xin cảm ơn.

Không có gì khó hiểu, dữ liệu trong cột R của bạn thấy trống trơn chứ thật sự nó rỗng do công thức tạo ra.
Phải thêm điều kiện khác rỗng (<>"").

G64=SUMPRODUCT(($C$4:$C$53="X")*($Q$4:$Q$53<2))

Các ô trống trong cột Q vẫn được hiểu là <2.
Tùy bạn sửa lại theo sự hiểu biết của mình.