Hàm Sumproduct

[handung107]

Adam Tran đã giải quyết xong rồi đấy, công thức tính đơn giá theo phương pháp bình quân liên hoàn không dùng Name tốt hơn, ít bị lỗi Circular reference. Nên dùng mảng với địa chỉ cố định tại Cell đầu tiên, em à

 

[thanhnhan53]

Cám ơn tất cả các bạn, nhân đây cho tôi hỏi thêm một tí nếu có tồn đầu kỳ ở một sheet khác thì phải làm sao cho công thức gọn lại

 

[adam_tran]

Cám ơn tất cả các bạn, nhân đây cho tôi hỏi thêm một tí nếu có tồn đầu kỳ ở một sheet khác thì phải làm sao cho công thức gọn lại

Trong 1 file NXT, nếu bạn tổ chức CSDL của 1 năm thì đưa tồn kho của năm trước như 1 nghiệp vụ nhập kho bình thường, lấy ngày cuối năm trước.

 

[thanhnhan53]

Bạn hiểu nhầm ý của tui rồi, theo cách bấy lâu nay tui làm là 1 sheet nhật ký NX và 1 sheet Tổng hợp XNT và dĩ nhiên có Tồn đầu kỳ. Sau khi đọc các bài về sumproduct tui đã thay đổi và áp dụng thấy có hiệu quả rất nhiều. Tính giá bình quân tại thời điểm xuất không thể thiếu yếu tố tồn đầu kỳ của tháng, tui cũng tạm giải quyết được rồi, nhưng lòng vẫn tham là theo ý tưởng cua bạn handung107, tui muốn viết hàm ngắn hơn nữa nhưng chưa được nên mới đặt vấn đề ở đây.Kho vật tư của tui có hơn 400 mặt hàng. Các bạn hiểu cho là mục tiêu của tui là không sử dụng macro, bởi tui đã "say" các hàm trong excel và muốn hiểu nó thật tường tận, và một kinh nghiệm bấy lâu nay là không phải đọc sách là có thể hiểu hết được, chỉ có công việc thực tế mới giúp chúng ta cụ thể những gì đã học, "LAO ĐỘNG LÀ SÁNG TẠO" câu này luôn luôn đúng.

 

[adam_tran]

Tính giá bình quân tại thời điểm xuất không thể thiếu yếu tố tồn đầu kỳ của tháng

Trong công thức ở bài số 20 thì giá xuất được tính không phụ thuộc vào sheet NXT, bản thân trong công thức cũng đã bao gồm: Giá xuất = (Tổng tiền Nhập - Tổng tiền Xuất)/(Tổng SL nhập - Tổng SL xuất).
Ở đây bạn Thanh Nhàn đề cập đến số tồn đầu kỳ. Nếu đưa số dư đầu kỳ vào như một nghiệp vụ nhập thì công thức sẽ đưa luôn số dư đó vào phần tổng nhập. Khi tính số dư đầu kỳ, vì NV chuyển số dư được ghi vào ngày kỳ trước, công thức sẽ tự tính và đưa vào số dư đầu kỳ trong kỳ. Đây là 1 "kinh nghiệm" nhỏ khi xử lý số liệu Excel, chuyển số dư từ file kỳ trước sang file kỳ này vì nhược điểm của Excel là tính toán rất chậm khi dữ liệu quá nhiều, không chỉ cho NXT mà cả số liệu kế toán nói chung.
Thí dụ: Cty bắt đầu hoạt động vào đầu năm 2004. Tổng nhập SP A trong năm 2004 là 1200, tổng xuất là 1000, số dư cuối năm 2004 là 200. Trong file quản lý NXT năm 2005, bạn có thể đưa số tồn = 1 trong 2 cách:
- Nhập kho SPA, ngày 31/12/2004 là 200
- Nhập SPA 1200, xuất SPA 1000 cùng ngày 31/12/2004.

 

[handung107]

Hàm SUMPRODUCT chỉ có thể rút gọn với điều kiện các mảng nằm trên cùng một Sheet với nhau, khi đó, ta có thể gộp chung lại trong cùng một công thức nếu các mảng có cùng điều kiện, nếu khác Sheet, ta không thể rút gọn được
Công thức tính đơn giá bình quân gia quyền liên hoàn của Adam Tran chưa đúng và đủ nhé. Nếu đúng và đủ, không thể gọn hơn.

Giả sử ta có 2 Sheet, một Sheet là DMMH có chứa mã MH và các số dư đầu kỳ. Tại Sheet này ta đặt 3 tên : TonMaMH cho cột chứa Mã MH, TonDauTG cho trị giá tồn đầu kỳ, TonDauSL cho số lượng tồn đầu kỳ

Sheet thứ 2 là Sheet NhapXuatHH, Sheet này dùng để nhập các dữ liệu về Nhập Xuất HH phát sinh trong tháng
Tại Sheet này ta có các cột sau : Cột K là Mã MH, cột M là Số Lượng Nhap, cột N là TGNhap, cột O là SLXuat, cột Q là TGXuat

Tại Cell đầu tiên tính đơn giá vốn, giả sử là Cell K8, ta có công thức sau :
=IF(OR(K8="",SUMIF(TonMaMH,K8,TonDauSL)=0),0,SUMIF(TonMaMH,K8,TonDauTG)/SUMIF(TonMaMH,K8,TonDauSL))

Bắt đầu Cell K9, công thức sẽ trở thành :
=IF(K9="",0,(SUMIF(TonMaMH,K9,TonDauTG)+SUMPRODUCT(($K$8:K8=K9)*($N$8:N8-$Q$8:Q8)))/(SUMIF(TonMaMH,K9,TonDauSL)+SUMPRODUCT(($K$8:K8=K9)*($M$8:M8-$O$8:O8))))

 

[handung107]

Một cách khác để rút gọn công thức là đặt tên cho từng đoạn công thức nhu sau :
Bạn đặt con trỏ ngay tại Cell đầu tiên áp dụng công thức, trong File gửi lên diễn đàn, tôi chọn Cell K8

Đặt tên cho các công thức sau :

SLDuDau = SUMPRODUCT((TonMaMH=NhapXuatHH!$K8)*TonDauSL)
TGDuDau = SUMPRODUCT((TonMaMH=NhapXuatHH!$K8)*TonDauTG)

Công thức trong Cell K8 sẽ trở thành :

=IF(OR(K8="",SLDuDau=0),0,TGDuDau/SLDuDau)

Bây giờ, ta đặt con trỏ tại Cell K9, và tiếp tục đặt tên cho công thức :

SLDuCuoi = SLDuDau+SUMPRODUCT((NhapXuatHH!$K$8:K8=NhapXuatHH!K9 )*(NhapXuatHH!$M$8:M8-NhapXuatHH!$O$8:O8))
TGDuCuoi = TGDuDau+SUMPRODUCT((NhapXuatHH!$K$8:K8=NhapXuatHH!K9 )*(NhapXuatHH!$N$8:N8-NhapXuatHH!$Q$8:Q8))

Công thức tại Cell K9 sẽ được viết thành :

=IF(OR(K9="",SLDuCuoi=0),0,TGDuCuoi/SLDuCuoi)

Bạn kéo rê công thức xuống các dòng tiếp theo. Nếu bạn muốn người khác không thấy tên của các công thức này, bạn hãy xem thử phần này nhé

Có 1 thủ thuật của VBA có thể giúp bạn dấu đi những name trong Name list (nhìn rối mắt) là thuộc tính visible của name.
Sub ToggleNameVisible()
Dim nName As Name
For Each nName In ActiveWorkbook.Names
nName.Visible = Not nName.Visible
Next nName
End Sub
Vận dụng cách đặt tên Name và thuộc tính Visible của Name, bạn có thể che bớt các Name để người dùng đỡ nhìn rối mắt hoặc tránh co người dùng thay đổi các Name này.
Bạn vận dụng thuộc tính này cùng Add-In Name Manager của MaiKa (Forest) chắc chắn sẽ rất hữu ích.

 

[lexthien]

To: Chị Handung107
Mình có ví dụ nhỏ nhờ giúp như sau:
ngày CT số tiền TKno TKco
01/01/05 50 331 111
02/01/05 60 331 156
03/01/05 70 331 112
01/01/05 45 331 111
03/01/05 65 331 111
.............
Như vậy ứng dụng hàm sumproduct vào để tính
01/01/05 chi cho 331 bang 111 bao nhieu
02/01/05 chi cho 331 bang 111 bao nhieu
Minh ứng dụng hàm sumproduct((ngayps=01/01/05)*(TKno=331)*(TKco=111)*sotien) thì kết quả là 0

 

[ForestC]

ngayps=datevalue("01/01/05") thi` moi' dc. anh ah.

 

[handung107]

Với Excel, ngày chỉ là một con số, kiểu dd/mm/yy chẳng qua chỉ là định dạng bên ngoài, và Excel thực hiện các phép tính toán ngày tháng thông qua các số cụ thể (quy định của Excel là ngày 01/01/1900 xem như số 1). Do đó, khi bạn nhập công thức như trên, Excel trả kết quả về 0 vì nó không thấy con số 01/01/05 trong dãy ngayps đâu cả. Con số thực của 01/01/05 mới là số 38353. Bây giờ, bạn thử lại xem
=Sumproduct((ngayps=38353)*(TKno=331)*(TKco=111)*sotien)
sẽ cho kết quả như bạn mong muốn
Tuy vậy, không ai thiết lập công thức như bạn cả. Nếu lập công thức là :
=Sumproduct((ngayps=A3)*(TKno=331)*(TKco=111)*sotien)
Trong đó, cột A là cột ngày tháng, thì ta chẳng cần băn khoăn dạng Format ngày tháng làm gì cả

 

[lexthien]

To All
Hay quá rất cảm ơn sự quan tâm của mọi người
Thân chào

 

[handung107]

To: Chị Handung107
Mình có ví dụ nhỏ nhờ giúp như sau:
ngày CT số tiền TKno TKco
01/01/05 50 331 111
02/01/05 60 331 156
03/01/05 70 331 112
01/01/05 45 331 111
03/01/05 65 331 111
.............
Như vậy ứng dụng hàm sumproduct vào để tính
01/01/05 chi cho 331 bang 111 bao nhieu
02/01/05 chi cho 331 bang 111 bao nhieu
Minh ứng dụng hàm sumproduct((ngayps=01/01/05)*(TKno=331)*(TKco=111)*sotien) thì kết quả là 0

Còn một cách khác ứng dụng cho trường hợp này ngoài hàm DATEVALUE là :
=Sumproduct((ngayps=(--("01/01/05")))*(TKno=331)*(TKco=111)*sotien)

 

[handung107]

Bây giờ, chúng ta tiếp tục sử dụng hàm SUMPRODUCT nữa, các bạn nhé
1/Chúng ta sẽ tính tổng của các mã MH là AA, BB và CC có trong cột Mã MH đặt tại cột A, từ A2:A20, và trị giá các MH này tại cột B, từ B2:B20. Nếu chỉ có 3 mặt hàng, chúng ta có thể sử dụng phép cộng cho hàm SUMPRODUCT như sau :
=SUMPRODUCT(((A2:A20="AA")+(A2:A20="BB")+(A2:A20="CC"))*(B2:B20))
Nhưng nếu chúng ta cần tính tổng của 5,6 mặt hàng hay nhiều hơn, công thức này sẽ dài lắm. Chúng ta sẽ tạo một vùng Criteria là cột D, từ D13 và nhập vào 3 mã MH trên.
-Bây giờ, các bạn hãy sử dụng hàm TRANSPOSE và hàm này phải nhập công thức mảng.
{=SUMPRODUCT((A2:A20=TRANSPOSE(D13))*(B2:B20))}
Dĩ nhiên, cách này cũng không phải là tối ưu vì có thể nếu nhiều record, công thức tính toán sẽ chậm, nhưng với 2 cách như trên, thì cách thứ 2 công thức ngắn hơn, các bạn à. Vả lại, trong bài này, tôi muốn giới thiệu để các bạn có cách nhìn tổng quát hơn về hàm SUMPRODUCT này

 

[Đào Việt Cường]

Dear minh,
----------
Chủ đề đang bàn về sumproduct mà bạn, sao lại đưa "hàm tách tên" của bạn vào đây?
Theo mình để giải quyết được một yêu cầu nào đó trước hết bạn phải hiểu rõ ý nghĩa các hàm trong Excel từ đó lựa chọn cho mình cách giải quyết thích hợp nhất.
Hì, nếu lười đọc hàm và nếu như dữ liệu chưa được imput thì bạn có thể tổ chức bảng dữ liệu là được:
A| B | C
__________________________
1|nguyen hoang |minh
__________________________
2|duong minh |tuan
__________________________
3|tran hoang |ha
__________________________
4|Le thi |Nghieng
__________________________

Còn nếu dữ liệu đã nhập vào rồi, bạn tham khảo hàm Search

 

[khuenguyen.tran]

Để hiểu hàm SUMPRODUCT làm việc như thế nào, chúng ta sẽ xem thí dụ dưới đây :
=SUMPRODUCT((A1:A5="AA")*(B1:B5="N")*(C1:C5))
Tạm hiểu là số lượng nhập của mặt hàng AA, với cột A là mã MH, cột B là cột cho cho biết nhập (N) hay xuất (X), cột C là cột số lượng

A1:A5 = {AA, BB,AA,AA,AA}
B1:B5 = {X,X,N,N,X}
C1:C5 = {3,4,2,1,4}

1 / Phần đẩu tiên của công thức (A1:A5="AA") sẽ kiểm tra mặt hàng nào là AA và cho giá trị là TRUE, còn lại là False. Như vậy công thức này sẽ tạo ra mảng : (A1:A5 = "AA") = {True, False, True, True, True}

2/ Tương tự cho mảng B1:B5 với giá trị là "N" sẽ cho mảng sau :
(B1:B5 = "N") = {False, False, True, True, False}

3/ Và mảng C1:C5 = {3,4,2,1,4}

Bây giờ, chúng ta có 3 mảng trên, hàm SUMPRODUCT làm việc trên các mảng số (number) nhưng ở đây chúng ta có 2 mảng (True/False). Nhưng khi chúng ta thực hiện phép nhân (*), chúng ta sẽ có mảng số. Vì True*True =1 và True*False =0, do đó, khi nhân 2 mảng ((A1:A5 = "AA")*(B1:B5 = "N")) với nhau, chúng ta có mảng sau :
((A1:A5 = "AA")*(B1:B5 = "N")) = {0, 0, 1, 1, 0}
Và nhân 3 mảng :
((A1:A5 = "AA")*(B1:B5 = "N")*(C1:C5)) = {0, 0, 2, 1, 0}

Hàm SUMPRODUCT là hàm tính tổng của phép nhân 3 mảng với nhau, do đó nó sẽ tính tổng của mảng sau :
SUMPRODUCT((A1:A5 = "AA")*(B1:B5 = "N")*(C1:C5)) = SUM{0,0,2,1,0} và cho kết quả là 3

Từ đây, chúng ta có nhận xét như sau :

- Khi chúng ta thực hiện chuyển giá trị True / False thành 1/0, chúng ta cũng có thể thực hiện những phép tính sau :
* Nhân mảng True/False với giá trị 1 :
=SUMPRODUCT((A1:A5 = "AA")*1,(B1:B5 = "N")*1,(C1:C5))
Hay :
= SUMPRODUCT(1*(A1:A5 = "AA"),1*(B1:B5 = "N"),(C1:C5))
Hay :
=SUMPRODUCT((A1:A5 = "AA")^1,(B1:B5 = "N")^1,(C1:C5))
* Cộng thêm số 0 :
= SUMPRODUCT((A1:A5 = "AA")+0,(B1:B5 = "N")+0,(C1:C5))
*Cách hay nhất là chúng ta thực hiện 2 dấu trừ liên tiếp (--) :
= SUMPRODUCT(--(A1:A5 = "AA"),--(B1:B5 = "N"),(C1:C5))

Hiểu như thế, chúng ta thấy rằng việc thực hiện hàm SUMPRODUCT với đối số là một mảng duy nhất có thể thực hiện được
=SUMPRODUCT((Đk1)*(Đk2))
sẽ được hiểu là : Đk 2 = một mảng tương ứng với các giá trị 1
=SUMPRODUCT (1*Đk1) hay SUMPRODUCT (--(ĐK1))
Cụ thể hơn ta có thể thực hiện hàm sau :
=SUMPRODUCT(--(A1:A5="AA")) để đếm các giá trị "AA" có trong mảng A1:A5, giống hàm COUNTIF

 

[handung107]

Cấu trúc của hàm SUMPRODUCT như vậy là đã rõ ràng, nhưng trong 5 cách viết, chúng ta sẽ sử dụng cách nào ? Theo bài trên thì :
1/SUMPRODUCT((A1:A5="AA"),(B1:B5="N"),(C1:C5))
2/SUMPRODUCT((A1:A5="AA")*(B1:B5="N")*(C1:C5))
3/SUMPRODUCT(--(A1:A5="AA"),--(B1:B5="N"),(C1:C5))
4/SUMPRODUCT((A1:A5="AA")*1,(B1:B5="N")*1,(C1:C5))
5/SUMPRODUCT((A1:A5="AA")+0,(B1:B5="N")+0,(C1:C5))
Theo www.decisionmodels.com thì
-Sử dụng -- sẽ nhanh hơn +0 hay *1
-Sử dụng --, nếu trong mảng C1:C5 có lẫn giá trị Text, hàm vẫn bỏ qua giá trị này mà không báo lỗi
-Phép (,) sẽ nhanh hơn dấu (*), và phép (*) sẽ gây ra lỗi nếu trong dãy tổng có lẫn giá trị Text

 

[handung107]

Còn một cách khác ứng dụng cho trường hợp này ngoài hàm DATEVALUE là :
=Sumproduct((ngayps=(--("01/01/05")))*(TKno=331)*(TKco=111)*sotien)

Thêm cách nữa :

=Sumproduct((TEXT(ngayps,"dd/mm/yy")="01/01/05")*(TKno=331)*(TKco=111)*sotien)

 

[vananhkt]

Chào các ban!
Công ty mình sản xuắt bê tông tươi mình có làm bảng tổng hợp để tính khối lượng cho từng lái xe và muốn tính xem cuối tháng mỗi lái xe được chở được bao nhiêu m3. mình được người chỉ là dụng hàm sumproduct cho nhanh nhưng mình làm mãi mà không ra. xin cac bạn giúp đỡ mình với
mình có gửi đính kèm các bạn xem rồi giúp mình nhé.
Dựa vào bảng hoàn chỉnh để tính khối lượng tổng sang sheet 2 nhé!

ui các bạn ơi sao mình không thể upload file đính kèm vậy ta?

 

[huynkel]

Ðề: Hàm Sumproduct

Hàm SUMPRODUCT chỉ có thể rút gọn với điều kiện các mảng nằm trên cùng một Sheet với nhau, khi đó, ta có thể gộp chung lại trong cùng một công thức nếu các mảng có cùng điều kiện, nếu khác Sheet, ta không thể rút gọn được
Công thức tính đơn giá bình quân gia quyền liên hoàn của Adam Tran chưa đúng và đủ nhé. Nếu đúng và đủ, không thể gọn hơn.

Giả sử ta có 2 Sheet, một Sheet là DMMH có chứa mã MH và các số dư đầu kỳ. Tại Sheet này ta đặt 3 tên : TonMaMH cho cột chứa Mã MH, TonDauTG cho trị giá tồn đầu kỳ, TonDauSL cho số lượng tồn đầu kỳ

Sheet thứ 2 là Sheet NhapXuatHH, Sheet này dùng để nhập các dữ liệu về Nhập Xuất HH phát sinh trong tháng
Tại Sheet này ta có các cột sau : Cột K là Mã MH, cột M là Số Lượng Nhap, cột N là TGNhap, cột O là SLXuat, cột Q là TGXuat

Tại Cell đầu tiên tính đơn giá vốn, giả sử là Cell K8, ta có công thức sau :
=IF(OR(K8="",SUMIF(TonMaMH,K8,TonDauSL)=0),0,SUMIF(TonMaMH,K8,TonDauTG)/SUMIF(TonMaMH,K8,TonDauSL))

Bắt đầu Cell K9, công thức sẽ trở thành :
=IF(K9="",0,(SUMIF(TonMaMH,K9,TonDauTG)+SUMPRODUCT(($K$8:K8=K9)*($N$8:N8-$Q$8:Q8)))/(SUMIF(TonMaMH,K9,TonDauSL)+SUMPRODUCT(($K$8:K8=K9)*($M$8:M8-$O$8:O8))))

Em vào đọc trên diễn đàn đã lâu và thật sự rất là biết ơn các a chị đã chia sẻ nhiều kinh nghiệm quý báu cho lớp đàn em như e, chúc chị sức khỏe và đóng góp nhiều hơn nữa cho diễn đàn!!!