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

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

 

[vodoi2x]

Bạn muốn thế thì xem các công thức này đi.

Làm tip cho bate
và xem thêm một bảng công thức sau

1:
2:
....
n:

Đó toàn công thức hay, nhớ xem thêm công thức của bate trên nhé

 

[Ba Tê]

Làm tip cho bate
và xem thêm một bảng công thức sau

1:
2:
....
n:

Đó toàn công thức hay, nhớ xem thêm công thức của bate trên nhé

Định gởi 1 Table toàn công thức mà bị lỗi, đã sửa lại ở bài trên rồi "bồ" ơi!

 

[vnwo2]

Dùng hàm sumproduct() nếu điều kiện là ngày hoặc tháng thì làm thế nào cho khớp
vd: cột ngày bán có giá trị:15/5/07, 17/6/07, 20/7/07,...
yêu cầu xuất ra tổng tiền các ngày 15 và 20 mỗi tháng. Bạn giu2p mình với

 

[nghiaphuc]

Dùng hàm sumproduct() nếu điều kiện là ngày hoặc tháng thì làm thế nào cho khớp
vd: cột ngày bán có giá trị:15/5/07, 17/6/07, 20/7/07,...
yêu cầu xuất ra tổng tiền các ngày 15 và 20 mỗi tháng. Bạn giu2p mình với

Công thức có dạng:

=SUMPRODUCT(((DAY(Vùng_Ngày)=15)+(DAY(Vùng_Ngày)=20))*Vùng_Tiền)

Trong đó, Vùng_Ngày và Vùng_Tiền thay bởi các địa chỉ cho phù hợp với thực tế.

 

[tieuthu0602]

Nhờ các anh chị giúp sum dữ liệu với 2 điều kiện

xin vui lòng giúp làm công thuc o o mau vàng ạ

 

[nghiaphuc]

xin vui lòng giúp làm công thuc o o mau vàng ạ

Công thức cho ô T49 là như vầy:

=SUMPRODUCT(($P$4:$P$43=P49)*($Q$4:$Q$43=Q49)*$T$4:$T$43)

Tuy nhiên dữ liệu của bạn lại không nhất quán (chỗ thì Number, chỗ thì Text) nên kết quả có thể sai.
Cách xử lý: Hoặc là bạn phải chuẩn hóa dữ liệu (có thể là toàn bộ cột dạng Text hoặc toàn bộ cột dạng Number) và sử dụng công thức trên, hoặc là bạn sử dụng công thức sau thay cho công thức trên:

=SUMPRODUCT((TEXT($P$4:$P$42,"@")=TEXT(P49,"@"))*(TEXT($Q$4:$Q$42,"@")=TEXT(Q49,"@"))*$T$4:$T$42)

 

[vanthuydo]

Dùng hàm Sumifs là OK bạn nhé:
T49=+SUMIFS($T$4:$T$42,$P$4:$P$42,P49,$Q$4:Q$42,Q49)
Các ô còn lại kéo xuống là OK

 

[yeuthamhangxom]

xin vui lòng giúp làm công thuc o o mau vàng ạ

Tại T49 bạn gõ thử công thức =SUM(IF($P$4:$P$43=P49,IF($Q$4:$Q$43=Q49,T4:T43))) kết thúc ấn Ctrl+Shift+Enter
Xem file đính kèm.

 

[bigpiger]

Nhờ anh xem dùm file e trong công việc em giải quyết khó quá

 

[mhung12005]

Nhờ anh xem dùm file e trong công việc em giải quyết khó quá

Bạn dùng công thức này xem sao:

=COUNTIF($E$3:$E$151,">50000000")
=COUNTIF($E$3:$E$151,"<=50000000")

 

[bigpiger]

Do mình chưa diễn đạt đúng yêu cầu, nên diễn đàn xem dùm mình lại, mình cụ thể hóa lại rồi.

 

[hophoang]

Do mình chưa diễn đạt đúng yêu cầu, nên diễn đàn xem dùm mình lại, mình cụ thể hóa lại rồi.

Có phải đếm số lần xuất hiện của A nhưng Tiền lớn hơn 1000 phải không
Bạn dùng cái này xem đúng không nhé!
=SUMPRODUCT(($A$2:$A$7="A")*($B$2:$B$7<>111)*($B$2:$B$7<>555)*($C$2:$C$7>1000))

 

[bigpiger]

tks bác nhiều, vậy có cách nào làm hàng loạt, mà không phải làm từng HOTEN vay không bác!!!

 

[haohao0489]

Giúp em về hàm sumproduct

Em mún tính tổng số lượng có 2 điều kiện, ví dụ mã tỉnh HNOI thì có bao nhiêu BO (Mã HC). em gửi kèm file đính kèm. Các cao thủ giúp e với

 

[mhung12005]

Em mún tính tổng số lượng có 2 điều kiện, ví dụ mã tỉnh HNOI thì có bao nhiêu BO (Mã HC). em gửi kèm file đính kèm. Các cao thủ giúp e với

Bạn dùng công thức này cho ô B3:

=SUMPRODUCT(('THEO DOI PHIEU DAT'!$Q$7:$Q$24='TONG HOP'!$A3)*('THEO DOI PHIEU DAT'!$P$7:$P$24='TONG HOP'!B$2))

Sorry bạn vì đã không đọc kỹ đầu bài.

 

[thanhsangnguyen1982]

Bạn dùng công thức này cho ô B3:

Công thức của you thiếu nhé, xem lại đi: Công thức đúng là
B3=SUMPRODUCT(('THEO DOI PHIEU DAT'!$Q$7:$Q$24='TONG HOP'!$A3)*('THEO DOI PHIEU DAT'!$P$7:$P$24='TONG HOP'!B$2)*('THEO DOI PHIEU DAT'!$I$7:$I$24))

 

[dinhphuong9327]

Sumproduct help!!!!

Nhân tiện chủ đề về hàm sumproduct. Mình xin gởi 1 file dữ liệu rất mong được sự giúp đỡ của mọi người. thông thường thì mình dùm hàm này để tính toán thì ra ngay nhưng sao trong file này mình làm mãi mà không ra, không biết sai ở chỗ nào nũa. Mong mọi người giúp đỡ.

 

[mhung12005]

Nhân tiện chủ đề về hàm sumproduct. Mình xin gởi 1 file dữ liệu rất mong được sự giúp đỡ của mọi người. thông thường thì mình dùm hàm này để tính toán thì ra ngay nhưng sao trong file này mình làm mãi mà không ra, không biết sai ở chỗ nào nũa. Mong mọi người giúp đỡ.

Dữ liệu trong cột D và E có ký tự CHAR(32) ==> Sumproduct báo lối #Value. Replace nó đi là xong. Bạn xem file.

 

[toanthanhcong]

Các bác cho em hỏi, cũng cùng 1 công thức Sumproduct tham chiếu ở cũng 1 địa chỉ, nhưng khi 2 điều kiện trong thức có sử dụng List có sự thay đổi thì công thức Sumproduct lai sai. Em có up file lên, nhờ các anh chị xem và sửa giúp hộ em với.
Công thức ở Sheet TONGHOP cột AE,AF,AG.

 

[tanokrsti]

Cách đặt và giải quyết vấn đề của bạn rất hay, xin cảm ơn bạn rất nhiều.
Bạn có thể giúp mình cách đếm Tổng các số có trong một cột (bằng công thức) được không? Mình đã gởi bài lên diễn đàn rồi
Chúc bạn vui, khỏe
Thân
tanokrsti@yahoo.com

 

[vuthithanhbinh]

=sum(if(A1:A10 = "X",if(B1:b10="Y",c1:C10,0)))và nhấn tổ hợp phím CTRL SHIFT ENTER

 

[ptm0412]

Các bác cho em hỏi, cũng cùng 1 công thức Sumproduct tham chiếu ở cũng 1 địa chỉ, nhưng khi 2 điều kiện trong thức có sử dụng List có sự thay đổi thì công thức Sumproduct lai sai. Em có up file lên, nhờ các anh chị xem và sửa giúp hộ em với.
Công thức ở Sheet TONGHOP cột AE,AF,AG.

Công thức J8 sheet Cap sửa lại:


=IF($M8="",0,INDEX(OFFSET(Data,,-7),$M8))

 

[vietvitcon]

Cái ứng dụng mảng này khá hay.

 

[trungtran.tphcm]

Bạn dùng thử
=Sumproduct((A1:A10="X")*(B1:b10="Y")*(C1:C10)
cái này kết hợp 2 điều kiện đó.

 

[won-won]

Oh, sumproduct ứng dụng nhiều thật!

 

[won-won]

Nhân nội dung đếm dữ liệu không trùng trong bài. Em có một vấn đề cần giải quyết gấp. Mấy anh chị thương tình giúp em.
Em đang xử lý dữ liệu sau (là các mã hợp đồng):
129159/AB-XK-2.1
129159/AB-XK-2.2
129160/IF-XK-3.1
129160/IF-XK-3.2
129160/IF-XK-3.3
129161/NM-XK-2.1
129161/NM-XK-2.2
Trong đó 1 hợp đồng có thể chia thành nhiều đợt nên có thứ tự là 1.1, 1.2...Nhiệm vụ của em là đếm xem có tất cả bao nhiêu hợp đồng. (trường hợp mà 1 hợp đồng chia làm nhiều kì thì tính 1).
Em phải làm sao đây?(xem như các dữ liệu trên nằm từ ô A1:A7). Hix, giúp em với!!!

 

[mhung12005]

Nhân nội dung đếm dữ liệu không trùng trong bài. Em có một vấn đề cần giải quyết gấp. Mấy anh chị thương tình giúp em.
Em đang xử lý dữ liệu sau (là các mã hợp đồng):
129159/AB-XK-2.1
129159/AB-XK-2.2
129160/IF-XK-3.1
129160/IF-XK-3.2
129160/IF-XK-3.3
129161/NM-XK-2.1
129161/NM-XK-2.2
Trong đó 1 hợp đồng có thể chia thành nhiều đợt nên có thứ tự là 1.1, 1.2...Nhiệm vụ của em là đếm xem có tất cả bao nhiêu hợp đồng. (trường hợp mà 1 hợp đồng chia làm nhiều kì thì tính 1).
Em phải làm sao đây?(xem như các dữ liệu trên nằm từ ô A1:A7). Hix, giúp em với!!!

Mình chưa thật sự hiểu bạn ý muốn nữa. hình như là thế này. Bạn xem file đính kèm nhé.

 

[won-won]

Cảm ơn bạn đã giúp mình. Mình đã hiểu công thức bạn viết, nó thật sự là điều mình cần.

 

[lebiinlove]

cho em hỏi nếu một cột điều kiện của sumproduct là ngày tháng thì làm ntn?

 

[Hoàng Trọng Nghĩa]

cho em hỏi nếu một cột điều kiện của sumproduct là ngày tháng thì làm ntn?

Thưởng theo tháng:

=SUMPRODUCT(($D$3:$D$12=$A24)*(MONTH($B$3:$B$12)=8)*$G$3:$G$12)

=SUMPRODUCT(($D$3:$D$12=$A24)*(MONTH($B$3:$B$12)=9)*$G$3:$G$12)

Loại hàng theo tháng:

=SUMPRODUCT(($E$3:$E$12=$E24)*(MONTH($B$3:$B$12)=8)*$F$3:$F$12)

=SUMPRODUCT(($E$3:$E$12=$E24)*(MONTH($B$3:$B$12)=9)*$F$3:$F$12)

Với công thức ở Bảng 1 về tiền thưởng, thay vì:

=IF(F3>=300,F3*5%,IF(AND(200<=F3,F3<300),F3*2%,IF(AND(100<=F3,F3<200),F3*1%,0)))

Thì bạn nên rút gọn lại tí:

=IF(F3>=300,F3*5%,IF(F3>=200,F3*2%,IF(F3>=100,F3*1%,0)))

Hoặc:

=IF(F3>=300,5%,IF(F3>=200,2%,IF(F3>=100,1%,0)))*F3

 

[smartgirl189]

Trợ Giúp hàm Sumproduct

Cả nhà giúp e xem sai chố nào với, e làm mãi mà không thấy ra!!! thanks cả nhà nhiều



==================
HLMT: Lần sau viết bài post cho đúng box nha bạn

 

[dongducnhiem]

Cả nhà giúp e xem sai chố nào với, e làm mãi mà không thấy ra!!! thanks cả nhà nhiều



==================
HLMT: Lần sau viết bài post cho đúng box nha bạn

Cell D9 kg biết bạn tham chiếu cái gì?
Lỗi: (Sheet1!$B$1:$B$8=DM!#REF!)
ở đây thiếu dấu ()
=SUMPRODUCT((Sheet1!$A$2:$A$8=DM!$A10)*(Sheet1!$B$2:$B$8=$D$8)*(Sheet1!$E$2:$E$8))

 

[smartgirl189]

Mình làm theo công thức của bạn thì có ra kết quả nhưng tuy nhiên áp dụng vào bảng thực tế lại vẫn báo lỗi. Bạn giúp mình với nhé. Mình úp file lên
Thanks bạn

 

[dongducnhiem]

Mình làm theo công thức của bạn thì có ra kết quả nhưng tuy nhiên áp dụng vào bảng thực tế lại vẫn báo lỗi. Bạn giúp mình với nhé. Mình úp file lên
Thanks bạn

file của bạn tôi kg mở được

 

[Ba Tê]

Mình làm theo công thức của bạn thì có ra kết quả nhưng tuy nhiên áp dụng vào bảng thực tế lại vẫn báo lỗi. Bạn giúp mình với nhé. Mình úp file lên
Thanks bạn

Sheet Data, cột E lúc là số lúc là chữ, Sumproduct sẽ bị lỗi.
Với lại dữ liệu hơn 1500 dòng, dùng VBA là nhanh nhất.
Enable Macros rồi bấm nút 1 phát thử xem.

 

[smartgirl189]

Sheet Data, cột E lúc là số lúc là chữ, Sumproduct sẽ bị lỗi.
Với lại dữ liệu hơn 1500 dòng, dùng VBA là nhanh nhất.
Enable Macros rồi bấm nút 1 phát thử xem.

Bạn có thể hướng dẫn cho mình cách dùng VBA không. mình chưa dùng lần nào cả. Thanks

 

[Ba Tê]

Bạn có thể hướng dẫn cho mình cách dùng VBA không. mình chưa dùng lần nào cả. Thanks

Tìm trên GPE cách Enable Macros, ví dụ như ở đây:
http://www.giaiphapexcel.com/forum/...ting-trong-2007-nằm-ở-đâu&p=204215#post204215
Muốn xem code thì ấn All+F11 sẽ gặp cái này:

Private Sub CommandButton1_Click()
Dim Rng(), Rng2(), Arr(), Cot As Long, I As Long, J As Long, K As Long, Dic As Object, Tem As String
Set Dic = CreateObject("Scripting.Dictionary")
With Sheets("Data")
    Rng = .Range(.[A6], .[A65000].End(xlUp)).Resize(, 5).Value
End With
    For I = 1 To UBound(Rng, 1)
        Tem = Rng(I, 1) & "#" & Rng(I, 2)
        If Not Dic.Exists(Tem) Then
            Dic.Add Tem, Rng(I, 5)
        End If
    Next I
With Sheets("GPE")
    Rng2 = .Range(.[C8], [IV8].End(xlToLeft)).Value
    Rng = .Range(.[A12], .[A65000].End(xlUp)).Value
    ReDim Arr(1 To UBound(Rng, 1), 1 To UBound(Rng2, 2))
    For I = 1 To UBound(Rng, 1)
        If Rng(I, 1) <> "" Then
            For J = 1 To UBound(Rng2, 2)
                Tem = Rng(I, 1) & "#" & Rng2(1, J)
                If Dic.Exists(Tem) Then
                    Arr(I, J) = Dic.Item(Tem)
                End If
            Next J
        End If
    Next I
        .[C12].Resize(I - 1, UBound(Rng2, 2)).Value = Arr
End With
Set Dic = Nothing
End Sub

 

[linh_trk]

Thống kê theo ngày. tháng...

Mình mới là thành viên, nên còn bỡ ngỡ, mong mọi người thông cảm!
Mình gửi đề tài mà mình đang làm, mọi người giúp mình với.
Mình có ghi trực tiếp câu hoi ở sheet "báo cáo tuần" bên cạnh ô cần thể hiện công thức luôn đó.
Ở sheet "Nhật ký" vì tính chất công việc nên mình phải khoá lại. Những ai cùng lĩnh vực công tác cần trao đổi thì mail cho mình: linh_trk@yahoo.com.vn hoặc Trần Khánh Linh - Thanh tra quận Phú Nhuận - 72 Trần Huy Liệu, phường 12, quận PN, TP.HCM.

 

[nlanh]

Mình có sử dụng hàm sumproduct để lấy dữ liệu từ sheet DA sang sheet OD theo 2 điều kiện cụ thể ( các bạn xem file sẽ rõ ) , nhưng ko hiểu sao trong cùng 1 sheet lúc thì sử dụng được lúc lại ko ra kết quả . Mình rất băn khoăn ko hiểu sai ở đâu ? Mong các bạn chỉ giùm . Rất cám ơn.

 

[Hong.Van]

Mình có sử dụng hàm sumproduct để lấy dữ liệu từ sheet DA sang sheet OD theo 2 điều kiện cụ thể ( các bạn xem file sẽ rõ ) , nhưng ko hiểu sao trong cùng 1 sheet lúc thì sử dụng được lúc lại ko ra kết quả . Mình rất băn khoăn ko hiểu sai ở đâu ? Mong các bạn chỉ giùm . Rất cám ơn.

Sheet DA bỏ #N/A ở cột I, K!

 

[namlongholidays]

em xin lỗi vì em không rành về kế toán nên nhìn các công thức của mọi người thấy xa lạ, cho em hỏi là em muốn học thì học trường nào ah

 

[Hoàng Trọng Nghĩa]

sao mình dùng chính xác rùi mà cứ báo sai hoài ta hhuhu

Bạn sai ở chỗ nào? gửi file lên xem thử, chứ than khóc gì ở đây?

 

[blq.trung]

Điều kiện trong hàm SUMPRODUCT

Xin chào các anh/chị, có anh/chị nào có thể giải thích và cho ví dụ về cách đặt điều kiện trong hàm SUMPRODUCT cho mình hiểu được không, và cách dấu nhân (*) giữa các điều kiện có ý nghĩa như thế nào. Xin cảm ơn nhiều lắm.

 

[vanle33]

Xin chào các anh/chị, có anh/chị nào có thể giải thích và cho ví dụ về cách đặt điều kiện trong hàm SUMPRODUCT cho mình hiểu được không, và cách dấu nhân (*) giữa các điều kiện có ý nghĩa như thế nào. Xin cảm ơn nhiều lắm.

http://www.giaiphapexcel.com/forum/showthread.php?7174-SumProduct-v%C3%A0-c%C3%B4ng-th%E1%BB%A9c-m%E1%BA%A3ng-Ph%C3%A9p-t%C3%ADnh-c%C3%B3-nhi%E1%BB%81u-%C4%91i%E1%BB%81u-ki%E1%BB%87n
Bạn tham khảo

 

[heartking]

Xin chào các anh/chị, có anh/chị nào có thể giải thích và cho ví dụ về cách đặt điều kiện trong hàm SUMPRODUCT cho mình hiểu được không, và cách dấu nhân (*) giữa các điều kiện có ý nghĩa như thế nào. Xin cảm ơn nhiều lắm.

Trang đầu tiên hướng dẫn rất chi tiết bạn a.

 

[kid_kiss_kick]

Các bác giải thích hộ mình công thức dưới đây với:

"=SUMPRODUCT(('1.HDV_tien'!H8:S8) * (COLUMN('1.HDV_tien'!H8:S8)=7+'TONG HOP'!$C$2))"

Mình không hiểu chỗ (COLUMN('1.HDV_tien'!H8:S8)=7+'TONG HOP'!$C$2)) là có ý nghĩa gì?

 

[Hong.Van]

Các bác giải thích hộ mình công thức dưới đây với:"=SUMPRODUCT(('1.HDV_tien'!H8:S8) * (COLUMN('1.HDV_tien'!H8:S8)=7+'TONG HOP'!$C$2))"Mình không hiểu chỗ (COLUMN('1.HDV_tien'!H8:S8)=7+'TONG HOP'!$C$2)) là có ý nghĩa gì?

Muốn hiểu hàm COLUMN() là gì? thì vào đây

 

[Hoàng Trọng Nghĩa]

Các bác giải thích hộ mình công thức dưới đây với:

"=SUMPRODUCT(('1.HDV_tien'!H8:S8) * (COLUMN('1.HDV_tien'!H8:S8)=7+'TONG HOP'!$C$2))"

Mình không hiểu chỗ (COLUMN('1.HDV_tien'!H8:S8)=7+'TONG HOP'!$C$2)) là có ý nghĩa gì?

Bạn có thể gửi cái file có chứa công thức này lên được không? Kết quả thực của nó như thế nào?

(COLUMN('1.HDV_tien'!H8:S8)=7+'TONG HOP'!$C$2))

Phải hiểu như ở trên mới đúng, tức là COLUMN('1.HDV_tien'!H8:S8) bản thân nó bằng 8 rồi và nếu ô C2 có giá trị 0 thì kết quả là 0 và C2 = 1 thì kết quả sẽ lấy giá trị ở ô H8, cứ thế bạn lần lượt thay thế cho nó từ 1 đến 12 sẽ thấy nó sẽ lấy giá trị tương ứng từ H8 đến S8 (12 cột).

 

[nguyentunhitung]

Hàm tự tạo trong excel.

Giúp mình cái hàm tự tạo này với!!!
loay hoay mãi mà không có làm được

 

[ndu96081631]

Giúp mình cái hàm tự tạo này với!!!
loay hoay mãi mà không có làm được

- Thứ nhất: Bạn post bài lung tung với cùng 1 chủ đề
- Thứ hai: Tiêu đề không rõ ràng (hàm tự tạo là cái quái gì?)
- Thứ ba: Post bài sai box, chen ngang chủ đề của người khác (không liên quan với chủ đề cần hỏi)
Với 3 cái "thứ" trên, đủ để bài viết của bạn bị xóa vĩnh viễn rồi
Vậy bạn vào box này:
http://www.giaiphapexcel.com/forum/forumdisplay.php?80-Các-hàm-tự-tạo-cho-worksheet
Mở 1 chủ đề mới, đặt tiêu đề cho rõ ràng rồi mọi người sẽ giúp bạn

 

[phamthimin]

Dùng hàm yearfrac(start-date,end-date) đơn giản hơn. Áp dụng theo file đính kèm yearfrac(O4,$M$2). Bạn thử xem sao nhé

 

[Backy1990]

Cám ơn bạn rát nhiều, mình đang loay hoay với bài tập này mà chưa được.
Bạn giúp mình nha.
Tính tổng Thu, tổng Chi và Tồn quỹ theo tháng (file gửi kèm)
Cảm ơn thật nhiều !!!

 

[vnproc]

Xin giúp đỡ hàm SumProduct

Mình dùng hàm Sumproduct để tính với các điều kiện trên 1 cột như A1:A10 thì được nhưng khi tính A1:B10 thì không được mà bị lổi Value. Mong các bạn giúp. Xin cảm ơn.

 

[ndu96081631]

Mình dùng hàm Sumproduct để tính với các điều kiện trên 1 cột như A1:A10 thì được nhưng khi tính A1:B10 thì không được mà bị lổi Value. Mong các bạn giúp. Xin cảm ơn.

Hỏi cái gì tào lao thế không biết? Bạn tự nói một mình chắc?

 

[vnproc]

Sorry lúc này mình quên up file. Để mình up file lên các các bạn xem mình với.

 

[nhapmon]

sumproduct là công thức mảng, kích thướt của các mảng phải bằng nhau.
bạn ko thể làm bằng cách này đâu

 

[vnproc]

sumproduct là công thức mảng, kích thướt của các mảng phải bằng nhau.
bạn ko thể làm bằng cách này đâu

Vậy có cách nào xử lý cái này không ? Giúp mình với, cảm ơn

 

[ndu96081631]

sumproduct là công thức mảng, kích thướt của các mảng phải bằng nhau.
bạn ko thể làm bằng cách này đâu

Không hẳn vậy đâu
SUMPRODUCT cho phép hoạt động khi kích thước mảng không bằng nhau nhưng với điều kiện phải viết theo cú pháp:

=SUMPRPDUCT((Vùng điều 1)*(Vùng điều 2)*(Vùng điều 3)*(......)*(Vùng kết quả))

Vậy công thức của tác giả phải sửa thành:

=SUMPRODUCT(($C$9:$N$9>=$D$4)*($C$10:$N$10<=$D$5)*($A$11:$A$20=$D$6)*($C$11:$N$20))

Xong!

 

[nhapmon]

bó tay,
từ ngày 01/04 đếng ngày30/04.
nếu làm bằng tay thì bạn làm sao?. các bạn để ngày như vậy ko hiểu được thí dụ C9=01/01/2013
C10=31/01/2013
hiểu như thế nào?

 

[ndu96081631]

bó tay,
từ ngày 01/04 đếng ngày30/04.
nếu làm bằng tay thì bạn làm sao?. các bạn để ngày như vậy ko hiểu được thí dụ C9=01/01/2013
C10=31/01/2013
hiểu như thế nào?

Lý ra chỉ nên có 1 dòng ngày (dòng 9) là đủ
Dòng 10 là ngày cuối tháng, thêm vào chỉ thêm rối

 

[vnproc]

Giờ mình muốn thay đổi ngày 01/04/2013 và 30/04/2013 sang ngày khác 04/04/2013 và 27/04/2013 nhưng vẫn tính được giá trị của tháng 04/2013 (01/04/2013 và 30/04/2013) Mình có làm thử nhưng không được.

 

[vodoi2x]

Giờ mình muốn thay đổi ngày 01/04/2013 và 30/04/2013 sang ngày khác 04/04/2013 và 27/04/2013 nhưng vẫn tính được giá trị của tháng 04/2013 (01/04/2013 và 30/04/2013) Mình có làm thử nhưng không được.

2 dòng tiêu đề ngày đó bạn chuyển thành 1 dòng và ghi theo tháng: 1 2 3 4 .... 12
thì mới chuẩn

 

[vnproc]

mình dùng Validation để chọn ngày nhưng khi dùng công thức trên với 01/04/2013 & 30/04/2013 thì tính tổng được còn 02/04/2013 & 15/04/2013 thì nó không tính được tổng trong tháng.

 

[ducthanh338]

bạn dùng công thức này xem mình thấy đáp ứng được nhu cầucủa bạn đấy!
code: Sumproduct((A1:A10=X)*(B1:B10=Y)*(C1:C10))
chú thích : Nếu bạn có một dãy dự liệu nhập vào cột A (từ A1 đên A10) ứng với mỗi điều kiện ở cột A thì có ở cột B ( A1 = B1) thì cho ra kết quả ở cột C . xem file mẫu đính kèm sẽ rõ hơn

 

[tuyenbw]

Công thức SUM có điều kiện

Xin chào cả nhà

Em có một công thức yêu cầu tính tổng số tiền theo từng nhan viên theo từng ngày nhưng em thử dùng SUMIF nhưng không ra kết quả. Nếu dùng Pivot thì rất nhanh nhưng Sếp lại không thích dùng Pivot nên em nhờ sự trợ giúp của cả nhà.

Em gởi file đính kèm

 

[nhapmon]

bạn xài excel 2007 trở lên thì có thể sử dụng sumifs
Ì2=SUMIFS($C$1:$C$13;$A$1:$A$13;$H2;$B$1:$B$13;I$1)

 

[tuyenbw]

Mình làm theo công thức sumproduct của bạn ĐứcThanh338 và đã có kết qủa. Xin cảm ơn cả nhà

 

[tuyenbw]

Dùng cách của bạn cũng rất ok. Cảm ơn bạn nhiều

 

[nhapmon]

Mình làm theo công thức sumproduct của bạn ĐứcThanh338 và đã có kết qủa. Xin cảm ơn cả nhà

ok, rất tốt, hàm sumproduct rất đa năng, tuy nhiên nó là công thức mảng nên khi data lớn thì nó trở nên ì ạch, trước đây excel 2003 ko có sumifs không có nên xài đỡ sumproduct..........tuy trường hợp mà sử dụng bạn ạ

 

[luxubuxu]

Bạn ơi giúp mình với nhé. Mình có đang cần tính số quán có sản lượng là không trong từng khu vực với ký hiệu khác nhau thì mình dùng công thức gì bây giờ? Mình thử dùng CT trên như không được.
Cột A: Khu vực Cột B: Sản lượng Cột C: Ký hiệu
A1= West B1= 2 C1= Cate1-Cate4
A2= East B2= 0 C2= Cate5

 

[linhvn_vpc]

Nhờ các anh em trong diễn đàn giải giúp mình bài toán tính số huyện của một tỉnh được kê như trong file đính kèm. Mình nghĩ mãi không ra mà cũng không biết gửi vào đâu nên mình up lên chủ đề này, nếu sai mong admin thông cảm!

 

[vodoi2x]

Nhờ các anh em trong diễn đàn giải giúp mình bài toán tính số huyện của một tỉnh được kê như trong file đính kèm. Mình nghĩ mãi không ra mà cũng không biết gửi vào đâu nên mình up lên chủ đề này, nếu sai mong admin thông cảm!

=SUMIF($A$2:$A$10,F2,$B$2:$B$10)

Cho cell G2, copy cho các cells còn lại

 

[linhvn_vpc]

=SUMIF($A$2:$A$10,F2,$B$2:$B$10)

Cho cell G2, copy cho các cells còn lại

Bạn không hiểu ý mình rồi! Nếu đơn giản vậy thì có gì mà phải nghĩ! Ở đây Huyện 1, 2,3 chỉ là tên huyện thôi không phải là số để sum. Mình có bảng như vậy và muốn hỏi cách đếm duy nhất (huyện 1 của tỉnh A có thể xuất hiện nhiều lần nhưng chỉ coi là 1 huyện). Mình thay bằng file sau để anh em khỏi hiểu nhầm và giúp đỡ!

 

[leonguyenz]

Bạn không hiểu ý mình rồi! Nếu đơn giản vậy thì có gì mà phải nghĩ! Ở đây Huyện 1, 2,3 chỉ là tên huyện thôi không phải là số để sum. Mình có bảng như vậy và muốn hỏi cách đếm duy nhất (huyện 1 của tỉnh A có thể xuất hiện nhiều lần nhưng chỉ coi là 1 huyện). Mình thay bằng file sau để anh em khỏi hiểu nhầm và giúp đỡ!

Công thức mảng:

=COUNT(1/FREQUENCY(IF($A$2:$A$10=F2,MATCH($B$2:$B$10,$B$2:$B$10,0)),ROW($A$2:$A$10)))

 

[linhvn_vpc]

Cảm ơn bạn! Cách làm của bạn rất đúng, song sử dụng frequency mình chưa biết. Có cách nào khác đơn giản hơn không?

 

[thanhsangnguyen1982]

Dùng công thức này đi bạn=SUMPRODUCT(($A$2:$A$10=F2)*1/(COUNTIF($B$2:$B$10,$B$2:$B$10)))

 

[halun762]

Tính tổng có điều kiện.

mình có một file này giờ dùng hàm gì để tính tổng đây. mong các bạn giúp đỡ. thanks nhiều

 

[leonguyenz]

mình có một file này giờ dùng hàm gì để tính tổng đây. mong các bạn giúp đỡ. thanks nhiều

Thử áp dụng công thức này xem:

=IF(A5="","",SUM(E5:E$41)-SUM(C6:C$42))

 

[nhapmon]

mình có một file này giờ dùng hàm gì để tính tổng đây. mong các bạn giúp đỡ. thanks nhiều

thử vậy xem
C5=IF(B5="","",SUM(E5:$E$41)-SUM(C6:$C$41))
kéo xuống tới C41, ko kéo hết ko đúng nha...............

 

[halun762]

thanks Leonguyen và nhapmon nhiều. 2 Anh đều làm đúng với số dòng ít, còn nếu nhiều thì ra là value. thật khó hiểu.