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

Liên hệ QC
Hàm Sumproduct

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

File đính kèm

  • Book22222.xls
    34.5 KB · Đọc: 199
Chào bạn ndu96081631

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

File đính kèm

  • AnalysisWithPivot.rar
    19.2 KB · Đọc: 368
Lần chỉnh sửa cuối:
Chào bạn ca_dafi;77094

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
 
Sử dụng hàm SUMPRODUCT.

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!
 
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-/.
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 "*".
 
Cách sử dụng hàm Sumproduct

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!
 

File đính kèm

  • Sumproduct2.JPG
    Sumproduct2.JPG
    133.2 KB · Đọc: 555
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)
 
Lần chỉnh sửa cuối:
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
 
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é
 

File đính kèm

  • TON DAU,TON CUOI.rar
    37.8 KB · Đọc: 291
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
 
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
 
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
 
Web KT
Back
Top Bottom