Hàm Sumproduct

  • Thread starter gacon0904
  • Ngày gửi
G

gacon0904

Thành viên sơ cấp
9/7/05
81
0
0
37
tphcm
#1
Các anh/chị cho em hỏi công dụng của hàm Sumproduct và cách sử dụng.Cám ơn các anh/chị nhiều.Chúc một tuần mới nhiều niềm vui mới.
 
H

handung107

Thành viên thân thiết
28/8/04
576
13
0
VN
www.giaiphapexcel.com
#3
Hàm SUMPRODUCT không chỉ đơn giản như thí dụ của Nguyên Bình đâu nhé, đây là một hàm cực kỳ hay, nó có thể sử dụng để đếm và tính tổng theo nhiều điều kiện khác nhau trong khi hàm SUMIF, COUNTIF chỉ đếm và tính tổng theo một điều kiện cho trước mà thôi. Nó có thể thay thế cho công thức mảng, nhưng thuận lợi và dễ sử dụng, đỡ tốn bộ nhớ hơn công thức mảng rất nhiều. Có rất nhiều bài đã nói về hàm này trên diễn đàn rất nhiều, bạn chỉ cần vào những chủ đề nào mà các bạn hay thảo luận về tính tổng theo nhiều điều kiện cho trước là sẽ gặp hàm này ngay
Cuối cùng, nhắn với các bạn EFC là hãy nghiên cứu thật nhiều và áp dụng hàm SUMPRODUCT cho các ứng dụng của các bạn, có thể quên bớt công thức mảng đi khi chưa thực sự cần thiết
 
adam_tran

adam_tran

Steel Partner
17/5/05
1,373
32
48
41
Goooogle
#4
Chị handung ơi, em thấy SUMPRODUCT ứng dụng rất hay, nhưng bài viết lại ít được chú ý, có lẽ vì ít người biết. Em xin phép đưa lến số ví dụ của SUMPRODUCT. Từ những ví dụ này các bạn có thể phát triển hay ứng dụng cụ thể hơn.
 

Đính kèm

B

Bình_OverAC

Over Abnormal / Crazy
14/5/04
845
7
18
36
Nha Trang
#5
Vâng ví dụ của anh Adam rất hay nhưng cũng cần phải nói rõ hơn cho mọi người hiểu ở cái chổ điều kiện > * < + =... để mọi người hiểu những điều kiện được lập chứ một cục như thế thì không có nhiều người mới có thể hiểu được công dụng tuyệt với của hàm này.
Gởi những người mới làm quen với hàm Sumproduct:
Trong công thức ứng dụng hàm Sumproduct của anh Adam_tran: Sumproduct dù sử dụng như thế nào cũng mang ý nghĩa là nhân theo kiểu trên chỉ có điều một trong 2 chuổi được sử dụng là chuổi kết quả của điều kiện. Tôi xin trình bày thêm một tí về ý nghĩa ứng dụng của sumproduct từ ví dụ của tôi để mọi người có thể hiểu hàm sumproduct bắt đầu thay thế công thức mãng sum * if như thế nào
 

Đính kèm

W

WhoamI

Thành viên thân thiết
#6
Vâng ví dụ của anh Adam rất hay nhưng cũng cần phải nói rõ hơn cho mọi người hiểu ở cái chổ điều kiện > * < + =... để mọi người hiểu những điều kiện được lập chứ một cục như thế thì không có nhiều người mới có thể hiểu được công dụng tuyệt với của hàm này.
Có thể phân tích hàm Sumproduct theo logic 0(Fasle) - 1(true) thì sẽ dễ hiểu hơn chăng ?
 
H

handung107

Thành viên thân thiết
28/8/04
576
13
0
VN
www.giaiphapexcel.com
#7
Sửa công thức của adam tran chút xíu nhé :

Tại Sheet TongHop, Cell B12, công thức của adam_tran là :

=(SUMPRODUCT((Ngay<C7)*(MaHH=D7)*(Loai="N")*(Tien))-SUMPRODUCT(((Ngay<C7)*(MaHH=D7)*(Loai="X")*(Tien))))/(SUMPRODUCT((Ngay<C7)*(MaHH=D7)*(Loai="N")*(SoLuong))-SUMPRODUCT(((Ngay<C7)*(MaHH=D7)*(Loai="X")*(SoLuong))))

Chị sửa lại cho gọn như sau :

=SUMPRODUCT((Ngay<C7)*(MaHH=D7)*((Loai="N")-(Loai="X"))*(Tien))/SUMPRODUCT((Ngay<C7)*(MaHH=D7)*((Loai="N")-(Loai="X"))*(SoLuong))

So với công thức mảng :

{=SUM(IF(MaHH=D7,IF(Ngay<C7,IF(Loai="N",Tien,-Tien))))/SUM(IF(MaHH=D7,IF(Ngay<C7,IF(Loai="N",SoLuong,-SoLuong))))}

Hàm SUMPRODUCT không dài hơn đâu, ngược lại dễ sử dụng hơn và chạy nhanh hơn nhé
 
Thích: popyaof
H

handung107

Thành viên thân thiết
28/8/04
576
13
0
VN
www.giaiphapexcel.com
#8
HongViet nói:
Nếu mãng của bạn >30 thì hàm này chịu thua, phải không?
Lúc đó sẽ ra răng???
Lúc đó Hàm =DSUM() sẽ chiếm lĩnh trận địa!
(Thử đi; Nói thì nói vậy thôi chứ mình chưa lên đến đó bao giờ!)
Nghĩa là sao ? 30 vòng ngoặc ? Cũng giống hàm IF hay công thức mảng, phải sử dụng nhiều vòng ngoặc, nếu lớn 30 vòng ngoặc thì có thể hàm này chịu thua, và dĩ nhiên, mỗi hàm đều có những ưu và nhược điểm riêng, tuy vậy, hàm DSUM không tiện dụng với người KT hơn hàm SUMPRODUCT đâu.
 
T

Tuanktcdcn

Lão già ham vui
18/6/04
548
51
28
41
Hà Nội
www.bluesofts.net
#9
HongViet nói:
Nếu mãng của bạn >30 thì hàm này chịu thua, phải không?
Lúc đó sẽ ra răng???
Lúc đó Hàm =DSUM() sẽ chiếm lĩnh trận địa!
(Thử đi; Nói thì nói vậy thôi chứ mình chưa lên đến đó bao giờ!)
Hàm DSum chỉ có thể áp dụng khi thống kê cho một hoặc vài chỉ tiêu đơn lẻ không có tính liên tiếp, tập hợp theo dãy. Thực sự nó rất hay nếu chỉ đơn lẻ.Bạn có thể dùng công thức DSum để tính tổng n điều kiện cho 1000 mã không? Chắc không ổn vì lúc đó bạn phải tạo 1000 vùng criteria.

Bất kỳ hàm nào trong EXCEL đều cho phép số đối số<=30, nhưng đối số lại có thể là một hàm, sự lồng nhau đó tạo ra sự hấp dẫn trong EXCEL.
 
H

handung107

Thành viên thân thiết
28/8/04
576
13
0
VN
www.giaiphapexcel.com
#10
handung107 nói:
Sửa công thức của adam tran chút xíu nhé :

Tại Sheet TongHop, Cell B12, công thức của adam_tran là :

=(SUMPRODUCT((Ngay<C7)*(MaHH=D7)*(Loai="N")*(Tien))-SUMPRODUCT(((Ngay<C7)*(MaHH=D7)*(Loai="X")*(Tien))))/(SUMPRODUCT((Ngay<C7)*(MaHH=D7)*(Loai="N")*(SoLuong))-SUMPRODUCT(((Ngay<C7)*(MaHH=D7)*(Loai="X")*(SoLuong))))

Chị sửa lại cho gọn như sau :

=SUMPRODUCT((Ngay<C7)*(MaHH=D7)*((Loai="N")-(Loai="X"))*(Tien))/SUMPRODUCT((Ngay<C7)*(MaHH=D7)*((Loai="N")-(Loai="X"))*(SoLuong))

So với công thức mảng :

{=SUM(IF(MaHH=D7,IF(Ngay<C7,IF(Loai="N",Tien,-Tien))))/SUM(IF(MaHH=D7,IF(Ngay<C7,IF(Loai="N",SoLuong,-SoLuong))))}

Hàm SUMPRODUCT không dài hơn đâu, ngược lại dễ sử dụng hơn và chạy nhanh hơn nhé
Còn có thể gọn hơn nữa, adam_tran này :

=SUMPRODUCT((Ngay<C7)*(MaHH=D7)*((Loai="N")-(Loai="X"))*(Tien/SoLuong))

Cũng vậy, công thức mảng sẽ sửa gọn lại như sau :

{=SUM(IF(MaHH=D7,IF(Ngay<C7,IF(Loai="N",Tien/SoLuong,-Tien/SoLuong))))}

Thật tuyệt vời, phải không em ?
 
adam_tran

adam_tran

Steel Partner
17/5/05
1,373
32
48
41
Goooogle
#11
handung107 nói:
Còn có thể gọn hơn nữa, adam_tran này :

=SUMPRODUCT((Ngay<C7)*(MaHH=D7)*((Loai="N")-(Loai="X"))*(Tien/SoLuong))

Thật tuyệt vời, phải không em ?
Á.... càng học càng thấy mình biết ít!
Không phải vô duyên mà mình cứ nhắc các member mới chú ý đọc mấy bài viết của chị Dung nói chung và hàm SUMPRODUCT nói riêng!

handung107 nói:
=SUMPRODUCT((Ngay<C7)*(MaHH=D7)*((Loai="N")-(Loai="X"))*(Tien/SoLuong))
Cũng vậy, công thức mảng sẽ sửa gọn lại như sau :
{=SUM(IF(MaHH=D7,IF(Ngay<C7,IF(Loai="N",Tien/SoLuong,-Tien/SoLuong))))}
Có lẽ bây giờ nhiều bạn sẽ à một tiếng rằng sao công thức tính giá xuất bình quân gia quyền liên hoàn lại đơn giản và dễ thế!
Chú ý: Nếu các bạn đánh giá lại hàng tồn kho, điều chỉnh giá trị, bổ sung chi phí vận chuyển... bằng nghiệp vụ nhập với số lượng = 0 thì công thức sẽ báo lỗi #DIV/0. Thí dụ, sau khi nhập 1 ngày bạn nhận được hóa đơn vận chuyển SP A với chi phí thực tế cao hơn giá trị đã phân bổ, nếu SPA chưa xuất thì bạn có thể sửa lại NV nhập hôm trước, nếu đã xuất rồi, bạn thường bổ sung = NV nhập số lượng =0, giá trị = Chi phí VC.
 
Sửa lần cuối:
adam_tran

adam_tran

Steel Partner
17/5/05
1,373
32
48
41
Goooogle
#12
Điều kiện OR cho hàm SUMPRODUCT

Mình đã quá chủ quan khi nói rằng SUMPRODUCT không tổng hợp được điều kiện OR. Hôm qua sau khi tìm hiểu công thức của chị handung, mình thấy điều này hoàn toàn được, và cách làm cũng rất đơn giản.
Để các bạn có thể hiểu kỹ hơn về SUMPRODUCT, mình để các bạn tự tìm hiểu thử, xem như là một bài tập vậy. Từ file ví dụ của mình, bài tập như sau (dùng SUMPRODUCT, chỉ dùng 1 hàm SUMPRODUCT và hàm RIGHT):
1. Tổng hợp doanh số bán của tất cả các mặt hàng có mã 01, 03 và 05, trước ngày 10/11 và sau ngày 20/11.

Chú ý: Bài tập này không dành cho Đại sư tỷ handung107 và các Mod.
 
W

WhoamI

Thành viên thân thiết
#14
Chú ý: Bài tập này không dành cho Đại sư tỷ handung107 và các Mod.
hehe thế là mình không cần đổi nick cũng có thể nằm trong diện được quyền làm bài tập này rồi. Mà anh Adam trần vẫn chưa gọi chị Dung là "Đại sư mẫu" ah!
Ah, cho em hỏi một câu nữa ạ:

Trích:
Nguyên văn bởi handung107
=SUMPRODUCT((Ngay<C7)*(MaHH=D7)*((Loai="N")-(Loai="X"))*(Tien/SoLuong))
Cũng vậy, công thức mảng sẽ sửa gọn lại như sau :
{=SUM(IF(MaHH=D7,IF(Ngay<C7,IF(Loai="N",Tien/SoLuong,-Tien/SoLuong))))}
Có lẽ bây giờ nhiều bạn sẽ à một tiếng rằng sao công thức tính giá xuất bình quân gia quyền liên hoàn lại đơn giản và dễ thế!
Em chưa thử nhưng nếu công thức tính giá xuất bình quân gia quyền liên hoàn hoạt động được thì anh có thể giải thích giúp em xem tại sao sử dụng Sumproduct lại không tạo ra tham chiếu vòng ạ???
 
adam_tran

adam_tran

Steel Partner
17/5/05
1,373
32
48
41
Goooogle
#15
WhoamI nói:
tại sao sử dụng Sumproduct lại không tạo ra tham chiếu vòng ạ???
Không hiểu "tham chiếu vòng"? là 2 cái ngoặc 2 đầu?
Mà anh Adam trần vẫn chưa gọi chị Dung là "Đại sư mẫu" ah!
Gọi bác Duyệt béo là sư phụ rồi, gọi chị handung là sư mẫu thì... :biggrin: lộn xộn.
 
L

Longlv

Thành viên sơ cấp
21/10/05
44
1
8
41
Hanoi
#17
adam_tran nói:
Mình đã quá chủ quan khi nói rằng SUMPRODUCT không tổng hợp được điều kiện OR. Hôm qua sau khi tìm hiểu công thức của chị handung, mình thấy điều này hoàn toàn được, và cách làm cũng rất đơn giản.
Để các bạn có thể hiểu kỹ hơn về SUMPRODUCT, mình để các bạn tự tìm hiểu thử, xem như là một bài tập vậy. Từ file ví dụ của mình, bài tập như sau (dùng SUMPRODUCT, chỉ dùng 1 hàm SUMPRODUCT và hàm RIGHT):
1. Tổng hợp doanh số bán của tất cả các mặt hàng có mã 01, 03 và 05, trước ngày 10/11 và sau ngày 20/11.

Chú ý: Bài tập này không dành cho Đại sư tỷ handung107 và các Mod.
Tôi xin đưa ra công thức, có gì sai mọi người sửa giúp nhé :

=SUMPRODUCT(((RIGHT(MaHH,2)="01")+(RIGHT(MaHH,2)="02")+(RIGHT(MaHH,2)="03"))*((Ngay<DATEVALUE("10/10/05"))+(Ngay>DATEVALUE("20/10/05"))))
 
W

WhoamI

Thành viên thân thiết
#18
Longlv nói:
Tôi xin đưa ra công thức, có gì sai mọi người sửa giúp nhé :

=SUMPRODUCT(((RIGHT(MaHH,2)="01")+(RIGHT(MaHH,2)="02")+(RIGHT(MaHH,2)="03"))*((Ngay<DATEVALUE("10/10/05"))+(Ngay>DATEVALUE("20/10/05"))))
Còn thiếu "tien" bác ạh! Công thức này mới chỉ cộng các điều kiện true (1) thôi.
Ah, nhưng anh Adam phải sửa lại Điều kiện về ngày thành <10/10 và >20/10 nữa vì trong bài chưa đến ngày 20/11 .
 
Sửa lần cuối:
adam_tran

adam_tran

Steel Partner
17/5/05
1,373
32
48
41
Goooogle
#19
NguyênBình nói:
Treo giải đi anh Adam ơi! Để em lấy nick khác nhận giải cái :biggrin:
Ai dám treo giải đâu, cái này đâu có khó, treo giải rồi quà đâu mà phát :biggrin: Longlv giải xong rồi đấy.

Là cái CircularReference ấy. Có nghĩa là kết qủa của cái này là dữ liệu của cái kia ...thành ra 1 vòng luẩn quẩn...sẽ tạo ra lỗi CircularReference.
Hiểu ý WhoAmI rồi. Tất nhiên khi lập công thức tính giá bình quân thì không dùng Name, mà dùng địa chỉ thực trong đó, cố định 1 ô đầu.
Thí dụ tại ô H3, nhập vào công thức =SUMPRODUCT(($A$2:A2=A3)*(($D$2:D2="N")-($D$2:D2="X"))*($G$2:G2))/SUMPRODUCT(($A$2:A2=A3)*(($D$2:D2="N")-($D$2:D2="X"))*($E$2:E2)) rồi copy xuống. Như vậy sẽ không bị lỗi Circular... tất nhiên để tạo 1 file NXT hoàn chỉnh thì phải thiết kế CSDL, thêm mắm muối cho nó "ngon lành" mới được.
 
Sửa lần cuối:
W

WhoamI

Thành viên thân thiết
#20
Ho, nhưng mà sao em cứ dùng name vẩn được nhỉ?? em còn đang định lên đây cảm ơn anh và chị Dung nữa cơ mà. hix, vì công thức mãng báo lỗi CircularReference nên em toàn phải dùng phương pháp tính bình quân cuối tháng thôi àh.
 

BQT trực tuyến

  • Rua Diu Dang
    Rua Diu Dang
    Điều hành cao cấp

Thành viên trực tuyến

  • MINA
  • thanhdn78
  • tridung290798
  • thuymjlo
  • daongocnam0603
  • Rua Diu Dang
  • vananh181888
  • huong238
  • NgocChildonyou
  • hienhtt88
  • huygia88
  • Kubinlun
  • tuanxitin
  • cuvanba18
  • Phương Hạ 111
  • wanbixla
  • Tú Vưu
  • anhtu2504
  • reddevil1989
  • MAITHU122

Xem nhiều