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

uthoiemve · 29/4/08

Hàm Sumproduct

buioanhvn đã viết:
Em chào anh chị,chúc anh chị 1 ngày tốt lành.
Em đang vướng ở 1 bài toán là tính tổng có điều kiện ràng buộc là thỏa mãn 1 khoảng thời gian nhất định.
Ví dụ thế này,
Sheet 1 có cột A là các ngày trong tháng 3/08, Cột B là doanh thu bán được theo ngày
Sheet 2 là pivot sheet 1 theo 2 chỉ tiêu ngày tháng và doanh thu tương ứng cột a và b.
Sheet 3 là chi tiết sheet 1 theo doanh thu của từng loại sản phẩm theo ngày.
Bây giờ, em muốn tính tổng số doanh thu từng loại sản phẩm ở sheet 3 bị ràng buộc theo ngày được chọn trong sheet 2
Em đã nghĩ đến công thức mảng và hàm sumproduct nhưng chưa biết xử lý việc để hàm logic <, > của ngày tháng như thế nào, đồng thời vì ngày được chọn trong sheet2 có thể thay đổi, muốn mặc nhiên công thức tự link vào mà không fải sửa đổi điều kiện nữa.
Từ hôm qua đến giờ em cứ quay đi quẩn lại với bài toán này mà vẫn chưa ra, anh chị giúp em với nhé
Thankyou & best regards.
Oanh

Đưa ví dụ (file excel) nên cho mọi người biết ý của bạn./.

buttri · 9/6/08

Các hướng dẫn giúp về SUMPRODUCT

Tôi muốn lập công thức SUMPRODUCT sẳn để khi cập nhật data.DDH mới vào nó sẽ tự động ra kết quả.có kèm file
CÁM ƠN NHIỀU NHIỀU

ndu96081631 · 9/6/08

buttri đã viết:
Tôi muốn lập công thức SUMPRODUCT sẳn để khi cập nhật data.DDH mới vào nó sẽ tự động ra kết quả.có kèm file
CÁM ƠN NHIỀU NHIỀU

SUM theo cái gì?
Theo Slnhan ?
Theo Tngay ?
Theo Slcon ?
Theo Gia ?
Hay ???

buttri · 11/6/08

Chào bạn ndu96081631

ndu96081631 đã viết:
SUM theo cái gì?
Theo Slnhan ?
Theo Tngay ?
Theo Slcon ?
Theo Gia ?
Hay ???

Cụ thể như vầy lấy cột "Slcon" nhân cột "Gia" theo diều kiện
thứ 1 là tính theo tháng
thứ 2 là tính theo LINE
thứ 3 là trên cột ORDER không tính những số oder có kí tự đầu là KD,D
Bạn xem bên sheet "tong tôi có ghi rõ yêu cầu
Mong bạn giúp đỡ.cám ơn nhiều

cadafi · 11/6/08

buttri đã viết:
Cụ thể như vầy lấy cột "Slcon" nhân cột "Gia" theo diều kiện
thứ 1 là tính theo tháng
thứ 2 là tính theo LINE
thứ 3 là trên cột ORDER không tính những số oder có kí tự đầu là KD,D
Bạn xem bên sheet "tong tôi có ghi rõ yêu cầu

Tôi có hai ý kiến muốn chia sẻ với bạn như sau:
1. Sử dụng Sumproduct trong trường hợp của bạn là một bất lợi:
a. Sheet báo cáo, bạn phải lọc ra dang sách GR và Line bằng tay (advance filter).
b. Với dữ liệu nhiều (theo như bạn mô tả trong sheet data.DDH), nếu dùng sumproduct nhiều điều kiện, máy sẽ chạy rất chậm (nếu không muốn nói là rất rất chậm).

2. Tôi sẽ cho bạn thấy công cụ Pivotalble mạnh mẽ như thế nào trong trường hợp xử lý báo cáo phân tích của bạn:
a. Tôi thiết kế thêm ba cột nữa cho sheet data.DDH của bạn, đó là [Thanhtien], [Reference] và [Period]

Mã:
[Thanhtien]: đơn giản là lấy [Slcon] nhân với [Gia]
[Reference]: là cột tham chiếu điều kiện đơn hàng,

Mã:

[Reference]=IF(OR(LEFT([ORDER],2)="KD",LEFT([ORDER],1)="D"),"KD-D","Others")

[Period] là kỳ báo cáo,

Mã:

[Period]=CONCATENATE(RIGHT("00"&MONTH([Ngayxuat]),2),"/",RIGHT("0000"&YEAR([Ngayxuat]),4))

b. Đặt Name vùng dữ liệu Range("A:O")=Data
c. Dùng Pivotable. bạn xem file đính kèm.
d. Để hiểu vể Pivotable, bạn xem theo đường link này.
http://www.giaiphapexcel.com/forum/showthread.php?t=6216
Khi cập nhật dữ liệu vào data.DDH xong, chỉ cần qua sheet Pivotable click chuột phải vào vùng chứa Pivot và chọn Refresh Data là xong!

e. Với cơ sở dữ liệu thiết kế lại như tôi, bạn hoàn toàn có thể sử dụng sumproduct, sumif hoặc thậm chí SUM để giải quyết bài toán này! Tuy nhiên với thiển ý của tôi, dùng Pivotable trong trường hợp này là tối ưu nhất.

buttri · 11/6/08

Chào bạn ca_dafi;77094

ca_dafi đã viết:
Tôi có hai ý kiến muốn chia sẻ với bạn như sau:
1. Sử dụng Sumproduct trong trường hợp của bạn là một bất lợi:
a. Sheet báo cáo, bạn phải lọc ra dang sách GR và Line bằng tay (advance filter).
b. Với dữ liệu nhiều (theo như bạn mô tả trong sheet data.DDH), nếu dùng sumproduct nhiều điều kiện, máy sẽ chạy rất chậm (nếu không muốn nói là rất rất chậm).

2. Tôi sẽ cho bạn thấy công cụ Pivotalble mạnh mẽ như thế nào trong trường hợp xử lý báo cáo phân tích của bạn:
a. Tôi thiết kế thêm ba cột nữa cho sheet data.DDH của bạn, đó là [Thanhtien], [Reference] và [Period]

b. Đặt Name vùng dữ liệu Range("A:O")=Data
c. Dùng Pivotable. bạn xem file đính kèm.
d. Để hiểu vể Pivotable, bạn xem theo đường link này.
http://www.giaiphapexcel.com/forum/showthread.php?t=6216
Khi cập nhật dữ liệu vào data.DDH xong, chỉ cần qua sheet Pivotable click chuột phải vào vùng chứa Pivot và chọn Refresh Data là xong!

e. Với cơ sở dữ liệu thiết kế lại như tôi, bạn hoàn toàn có thể sử dụng sumproduct, sumif hoặc thậm chí SUM để giải quyết bài toán này! Tuy nhiên với thiển ý của tôi, dùng Pivotable trong trường hợp này là tối ưu nhất.

Rất cám ơn đã hướng dẫn tôi sẽ tìm hiểu về Pivotable coi sao
Chúc bạn sức khỏe và thành công

micarazavuska · 15/6/08

Sử dụng hàm SUMPRODUCT.

TuanVNUNI đã viết:
Mình xin phân tích cách dùng SumProduct và Công thức mảng.

Hàm SumProduct:
Cấu trúc SUMPRODUCT(array1,array2,array3, ...)
Array - Mảng dữ liệu là một tập hợp dãy giá trị liên tiếp trong một khảng nào đó. VD A1:C1 hoặ A1:A10,...

Phép tính này cho phép chúng ta tính tổng của tích array1*array2*array3* ...array30.
VD: A: Số lượng; B: Đơn giá
A1 =2 B1=20 C1="Cam" D1="Giống lai"
A2 =3 B2=10 C2="Bưởi" D2="Không"
A3 =4 B3=25 C3="Cam" D3="Không"

Bây giờ cần tính doanh thu của các loại hoa quả
array1=A1:A3
array2=B1:B3
Công thức =SumProduct(A1:A3, B1:B3) = 170
Bản chất công thức làm việc như thế này =A1*B1+A2*B2+A3*B3 kết quả là 170
Nhắc lại về phép tính logic:
Giá trị kiểu logic chỉ cho ra 1 trong 2 giá trị là TRUE/1, FALSE/0
Phép toán logic:<, >, <>, =, >=, <=, Not()
VD:
2>3=False
3>1=True
4>3=True
*) Logic và - AND
=(2>3)*(3>1)*(4>3)=False*True*True=0*1*1=False/0 tương đương với hàm AND(2>3,3>1,4>3). Ít nhất một logic=False thì kết quả sẽ là False hay 0.
* Logic hoặc - OR
=(2>3)+(3>1)+(4>3)=False+True+True=0+1=True/1 tương đương với hàm OR(2>3,3>1,4>3). Ít nhất một logic=True thì kết quả sẽ là True hay 1.
Lưu ý tổng của các giá trị là True=True=1).

*) Tính tổng có nhiều điều kiện:
Cách 1: dùng SUMPRODUCT
Tính tổng doanh thu của loại là "Cam"
=SUMPRODUCT(A1:A3,B1:B3*(C1:C3="Cam")) hoặc =SUMPRODUCT(A1:A3*B1:B3*(C1:C3="Cam")) = 140
Công thức tính như sau:
=A1*B1*(C1="Cam")+A2*B2*(C2="Cam")+A3*B3*(C3="Cam")
=2*20*True+3*10*False+4*25*True
=2*20*1+3*10*0+4*25*1= 140
Cách 2: dung Công thức mảng - "Formula Array"
=Sum(IF(C1:C3="Cam",A1:A3*B1:B3,0))
Kết thức nhẫn tổ hợp CTRL+SHIFT+ENTER. Với cách làm này EXCEL sẽ phân tích như sau:
Xét trên từng dòng trong mảng (array)
dòng1: (c1="Cam")=true nên lấy A1*B1=2*20
dòng2: (c2="Cam")=false nên lấy 0 (theo cách của lấy của hàm IF)
dòng3: (c3="Cam")=true nên lấy A3*B3=4*25
Sau khi chạy hết các dòng, EXCEL sẽ dùng hàm SUM để tính tổng kết quả tính được ở từng dòng=2*20+0+2*25=140. Nếu trong công thức là hàm khác hàm SUM thì cách tính sẽ theo hàm đó.

Như vậy có 2 cách tính:

=SUMPRODUCT(A1:A3*B1:B3*(C1:C3="Cam")) và
=Sum(IF(C1:C3="Cam",A1:A3*B1:B3,0))

*) Vậy tại sao không dùng là =SUM(A1:A3*B1:B3*(C1:C3="Cam"))
mà phải dùng hàm =SUMPRODUCT(A1:A3*B1:B3*(C1:C3="Cam")) ?

Các bạn nhớ lại cấu trúc của SUM là
SUM(number1,number2, ...)
Còn SUMPRODUCT là
SUMPRODUCT(array1,array2,array3, ...)
number <> array

Nếu SUMPRODUCT(A1:A3*B1:B3*(C1:C3="Cam")) rồi ENTER là đúng vì đối số của nó phải là mảng - Array.

Nếu công thức =SUM(A1:A3*B1:B3*(C1:C3="Cam")) rồi ENTER kết quả là #VALUE! -lỗi vì A1:A3 là một array chứ không phải là một number.

Nếu nhấn tổ hợp CTRL+SHIFT+ENTER. Với cách làm này EXCEL sẽ phân tích như sau:
Xét trên từng dòng trong mảng (array)
dòng1: A1*B1*(c1="Cam")=2*20*True=2*20*1
dòng2: A2*B2*(c2="Cam")=3*10*False=3*10*0
dòng3: A3*B3*(c3="Cam")=2*25*True=4*25*1

Sau khi chạy hết các dòng, EXCEL sẽ dùng hàm SUM để tính tổng kết quả tính được ở từng dòng=2*20*1+3*10*0
+4*25*1=140.

Vậy vẫn dùng được =SUM(A1:A3*B1:B3*(C1:C3="Cam")) với điều kiện nhấn tổ hợp phím CTRL+SHIFT+ENTER

Như vậy đến đây chúng ta có có 3 cách tính:

=SUMPRODUCT(A1:A3*B1:B3*(C1:C3="Cam")) nhấn phím ENTER
=SUM(IF(C1:C3="Cam",A1:A3*B1:B3,0)) nhấn phím CTRL+SHIFT+ENTER
=SUM(A1:A3*B1:B3*(C1:C3="Cam")) nhấn phím CTRL+SHIFT+ENTER

Chúng có thể kết hợp rất nhiều điều kiện vào trong hàm thông qua phép toán logic nhân-và- And, cộng - hoặc - Or.

*) Dùng hàm SUMPRODUCT hay dùng SUM kết hợp CTRL+SHIFT+ENTER đều cho ra được kết quả như nhau chính là do phép toán logic của bạn.
*) Hàm SUMPRODUCT chỉ có thể tính tổng theo nhiều điều kiện
*) Công thức mảng - Formula Array ngoài việc tính tổng có nhiều điều kiện còn làm rất nhiều phép tính khác do cách sử dụng hàm mà thôi.

Thân chào!

Chào anh TuanVNUNI! Em cũng đang học Ex, còn rất ngố.Em vừa đọc bài của anh viết nhưng chưa hiểu nhiều lắm. Khi em làm ví dụ anh đưa ra, sử dụng hàm SUMPRODUCT:
1.Em sử dụng bằng tay, tức là tự đánh lệnh hàm SUM, sau đó Ex đưa ra một danh sách các hàm bắt đầu bằng lệnh SUM, trong đó có SUMPRODUCT.Em chọn và thực hiện đúng như công thức của anh đưa ra SUMPRODUCT(A1:A3,B1:B3*(C1:C3="Cam"))
Thì kết quả là =140
2.Em ấn Shift+F3 để dò tìm hàm SUMPRODUCT thi hiện ra bảng có array 1,2,3 .Em kéo tại array 1=A1:A3 , array2=B1:B3, Còn array3 =C1:C3=C1 nhưng kết quả lại cho =0
Vậy, tại array3 phải kéo cells như thế nào cho đúng ạ? -0-/.

Thanks!

cadafi · 15/6/08

micarazavuska đã viết:
1.Em sử dụng bằng tay, tức là tự đánh lệnh hàm SUM, sau đó Ex đưa ra một danh sách các hàm bắt đầu bằng lệnh SUM, trong đó có SUMPRODUCT.Em chọn và thực hiện đúng như công thức của anh đưa ra SUMPRODUCT(A1:A3,B1:B3*(C1:C3="Cam"))
Thì kết quả là =140
2.Em ấn Shift+F3 để dò tìm hàm SUMPRODUCT thi hiện ra bảng có array 1,2,3 .Em kéo tại array 1=A1:A3 , array2=B1:B3, Còn array3 =C1:C3=C1 nhưng kết quả lại cho =0
Vậy, tại array3 phải kéo cells như thế nào cho đúng ạ?

Như vậy công thức của bạn là: =SUMPRODUCT(A1:A3,B1:B3,C1:C3=C1)
Khi đã ra được công thức như trên, không phải là do bạn kéo cells gì cả, mà bạn phải sửa lại công thức một chút:

Mã:

=SUMPRODUCT(A1:A3,B1:B3[B]*[/B](C1:C3=C1))

Thay dấu "," bằng dấu "*".

micarazavuska · 16/6/08

Cách sử dụng hàm Sumproduct

ca_dafi đã viết:
Như vậy công thức của bạn là: =SUMPRODUCT(A1:A3,B1:B3,C1:C3=C1)
Khi đã ra được công thức như trên, không phải là do bạn kéo cells gì cả, mà bạn phải sửa lại công thức một chút:

Mã:

=SUMPRODUCT(A1:A3,B1:B3[B]*[/B](C1:C3=C1))

Thay dấu "," bằng dấu "*".

Cho iem hỏi lại.
Anh coi image em up nhé.
Thanks!

cadafi · 16/6/08

micarazavuska đã viết:
Cho iem hỏi lại.
Anh coi image em up nhé.
Thanks!

Không cần Array3 bạn à, tại ô Array2 bạn gõ vào:

Mã:

B1:B3*(C1:C3=C1)

Kéo chuột từ B1 xuống B3, xong gõ dấu "*" rồi "(", rồi kéo chuột từ C1 đến C3, rồi gõ "=", chỉ vào ô C1, rồi gõ ")"

[COLOR="Gray"]Còn không thì gõ hết vào cho nhanh.[/COLOR]

Còn nếu vẫn muốn tách bạch điều kiện vào array3 thì bạn gõ vào:

Mã:

Array1= A1:A3
Array2= B1:B3
Array3= --(C1:C3=C1)

micarazavuska · 17/6/08

ca_dafi đã viết:
Không cần Array3 bạn à, tại ô Array2 bạn gõ vào:

Mã:

B1:B3*(C1:C3=C1) Kéo chuột từ B1 xuống B3, xong gõ dấu "*" rồi "(", rồi kéo chuột từ C1 đến C3, rồi gõ "=", chỉ vào ô C1, rồi gõ ")" [COLOR=Gray]Còn không thì gõ hết vào cho nhanh.[/COLOR]

Còn nếu vẫn muốn tách bạch điều kiện vào array3 thì bạn gõ vào:

Mã:

Array1= A1:A3 Array2= B1:B3 Array3= --(C1:C3=C1)

Thanks anh nhé ! Em khoái code thứ 2 hơn! &&&%$R

minhquan299 · 19/6/08

View attachment bai17.rar

xin mọi người hứong dẫn mình làm câu số 9, cám ơn mọi người rất nhiều

cadafi · 19/6/08

minhquan299 đã viết:
View attachment 11789

xin mọi người hứong dẫn mình làm câu số 9, cám ơn mọi người rất nhiều

Bạn xem file đính kèm nhé! Dùng SUMPRODUCT.

fun.tiger · 18/7/08

file này có cả hàm sumproduct luôn

các pác chỉ giúp mình thực hiện hàm Vlook up để làm tồn đầu cuối nhé,mình cần gấp lắm,cảm ơn các pác trước nhé

ThuNghi · 18/7/08

fun.tiger đã viết:
các pác chỉ giúp mình thực hiện hàm Vlook up để làm tồn đầu cuối nhé,mình cần gấp lắm,cảm ơn các pác trước nhé

Lấy dữ liệu đâu mà biết tồn đầu kỳ.

Nancy Nguyen · 18/7/08

Xin các đại ca chỉ cho em tại sao trong công thức tính hàm SUMPỎDUCT lại có dấu (--..) đằng trước là gì vậy

Cám un các đại ca

cadafi · 18/7/08

Nancy Nguyen đã viết:
Xin các đại ca chỉ cho em tại sao trong công thức tính hàm SUMPỎDUCT lại có dấu (--..) đằng trước là gì vậy

Cám un các đại ca

Bạn xem bài giải thích tại đây, bài số #7
http://www.giaiphapexcel.com/forum/showthread.php?t=11165

fun.tiger · 18/7/08

@ThuNghi

ThuNghi đã viết:
Lấy dữ liệu đâu mà biết tồn đầu kỳ.

đầu kỳ tháng này =cuối kỳ của tháng trước,giúp mình copy thêm một sheet xem như là báo cáo tháng trước rồi đặt công thức mình xem là minh hiểu hà,cảm ơn nhiều

buivankiet · 23/7/08

Theo tôi nghĩ dấu -- trong hàm sumproduct là để chuyển giá trị logic true, false sang giá trị 1,0 để thực hiện các tích vô hướng

hongnhung4206 · 23/7/08

Help me! Tính tổng với 2 điều kiện.

Chào cả nhà! Xin các anh chị chỉ giúp em cách tính tổng với 2 điều kiện. Hiện em đang xài Office 2003 nên ko thể xài hàm SUMIFS đề tính tổng có 2 đk, các anh chị biết hàm nào tương tự hàm SUMIFS trong Office 2003 chi em với. Em ko biết xài VBA

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

Thành viên mới

Thành viên mới

File đính kèm

Huyền thoại GPE

Thành viên mới

Hành động từ trái tim

File đính kèm

Thành viên mới

Thành viên mới

Hành động từ trái tim

Thành viên mới

File đính kèm

Hành động từ trái tim

Thành viên mới

Thành viên mới

Hành động từ trái tim

File đính kèm

Thành viên mới

File đính kèm

Hãy cho rồi sẽ nhận!

Thành viên mới

Hành động từ trái tim

Thành viên mới

Thành viên mới

Thành viên mới

Đếm ngược thời gian