Nhờ sửa lại code tính tổng bằng sumproduct

Liên hệ QC
ST-Lu!

ST-Lu!

Love Wingchun
Tham gia
19/8/08
Bài viết
730
Được thích
546
Nghề nghiệp
Xích lô một thời
Em đang loay hoay tính tổng bằng sumproduct nhưng chưa được, các anh giúp em nhé
Yêu cầu: em ghi rõ trong sheet PIC file đính kèm

Cám ơn Các Anh
 
Lần chỉnh sửa cuối:
Sắp xếp theo thời gian sắp xếp theo bầu chọn
Định dạng cột ngày cho thống nhất trong tất cả các sheet
 
Upvote 0
Trước mình không biết hàm date nên mình dùng now() nên cột ngày sẽ có thời gian đằng sau,
Bạn có thể xóa nó đi mà
Vấn đề là gợi ý cho mình cách viết sumproduct
 
Upvote 0
Cái này hình như đâu phải Sumproduct? Nó là 2 SumIf cộng lại mà.
 
Upvote 0
Thay vì
Sumproduct(Cột ARMS Amount * (ngày = ...) * payment medhod) em
viết code tạm cho sheet Im Ex 2010 nhưng không chạy được

Nó báo lỗi dòng sau
Sửa giúp em đoạn code này với
PHP: 
Cells(i + 7, "Q") = Application.WorksheetFunction.SumProduct(Sheet5.Range("G2:G" & Sheet5.[A65000].End(xlUp)) * (Sheet5.Range("M2:M" & Sheet5.[A65000].End(xlUp)) = Cells(i + 5, i + 16)) * (Sheet5.Range("N2:N" & Sheet5.[A65000].End(xlUp)) = Cells(i + 7, i + 15)))
 
Lần chỉnh sửa cuối:
Upvote 0
PHP: 
Cells(i + 7, "q") = WorksheetFunction.SumProduct(Sheet5.Range("G2:G" & Sheet5.[A65000].End(xlUp)) * (Sheet5.Range("N2:N" & Sheet5.[A65000].End(xlUp)) = Cells(i + 7, "p")))

Em sửa code lại như trên không thấy sai mà vẫn không được nhỉ?
HIc hic
 
Upvote 0
ST-Lu! đã viết:
PHP: 
Cells(i + 7, "q") = WorksheetFunction.SumProduct(Sheet5.Range("G2:G" & Sheet5.[A65000].End(xlUp)) * (Sheet5.Range("N2:N" & Sheet5.[A65000].End(xlUp)) = Cells(i + 7, "p")))

Em sửa code lại như trên không thấy sai mà vẫn không được nhỉ?
HIc hic
Nhấp chuột vào đây để mở rộng...
Sai cú pháp thôi ---> End(xlUp).Row chứ
Ngoài ra, dùng SUMPRODUCT trong WorksheetFunction không dễ ăn thế đâu ---> Với SUMPRODUCT có điều kiện thì dùng thế nó cóc ra ---> Phải Evaluate(..._) mới xong!
Vậy:
- Thay vì dùng SUMPRODUCT sao bạn không viết 1 hàm riêng để tính toán ---> Nói không chừng tốc độ còn nhanh hơn
 
Upvote 0
ndu96081631 đã viết:
Sai cú pháp thôi ---> End(xlUp).Row chứ
Ngoài ra, dùng SUMPRODUCT trong WorksheetFunction không dễ ăn thế đâu ---> Với SUMPRODUCT có điều kiện thì dùng thế nó cóc ra ---> Phải Evaluate(..._) mới xong!
Vậy:
- Thay vì dùng SUMPRODUCT sao bạn không viết 1 hàm riêng để tính toán ---> Nói không chừng tốc độ còn nhanh hơn
Nhấp chuột vào đây để mở rộng...

Thầy ơi, tại vì em dùng immediate test code có thêm .address thấy nó trả về vùng dữ liệu nên nghĩ là ok
Để em thử laị Thầy ạh

Cám ơn Thầy
 
Upvote 0
ndu96081631 đã viết:
Sai cú pháp thôi ---> End(xlUp).Row chứ
Ngoài ra, dùng SUMPRODUCT trong WorksheetFunction không dễ ăn thế đâu ---> Với SUMPRODUCT có điều kiện thì dùng thế nó cóc ra ---> Phải Evaluate(..._) mới xong!
Vậy:
- Thay vì dùng SUMPRODUCT sao bạn không viết 1 hàm riêng để tính toán ---> Nói không chừng tốc độ còn nhanh hơn
Nhấp chuột vào đây để mở rộng...
Thầy ơi
Em đang nghĩ viết một hàm kiểu như sau
PHP: 
Function Sum_ST(Ngay As Date, method As String)
End Function
Mà phương pháp mình làm như sau
Giả sử mình muốn xem tiền mặt TM thu được tại ngày 01/06/2010 là bao nhiều ta có
TM = TM sheet Im 2010 (Tm1) + TM sheet Ex 2010 (TM2)
tính TM1:
- Dùng Autofilter:
PHP: 
with sheet5.[A1].currentregion ' --------------sheet Im 2010 (hang nhap)
.AutoFilter Field:=13, Criteria1:="TM"  '----------> ta loc ra phương thức trả ví dụ: TM
.AutoFilter Field:=14,Operator:= _xlFilterValues, Criteria2:=Array(2, "06/01/2010")  
End with
Sau khi ta filter như trên thì vùng tiền cột G sẽ chính là TM1

Tuy nhiên điều em chưa hiểu là ghi record marco thì ngày 01/06/2010 (d/m/y) lại chuyển thành 06/01/2010 (m/d/y)
và trong điều kiện filter lần 2 ngày lại nằm trong array( 2,"06/01/2010")>?????????????
Đây mới chỉ là ý nghĩ của em
Thầy gợi ý tiếp cho em với
 
Upvote 0
PHP: 
Function Sum_ST(Ngay As Date, method As String)

With Sheet5.[A1].CurrentRegion
.AutoFilter Field:=13, Criteria1:="method"
.AutoFilter Field:=14, Criteria2:="ngay"
End With
Amt1 = WorksheetFunction.Sum(Sheet5.Range("G2:G" & Sheet5.[A65000].End(xlUp).Row).SpecialCells(12))
Sum_ST = Amt1

End Function
SUm_ST = Amt1 + AMt2 (AMt2 tương ứng với sheet Ex 2010)
Nhưng tạm thời em làm Amt1 (cho sheet Im 2010) nhưng kết quả bị sai Thầy NDU ơi
Giúp em với
 
Lần chỉnh sửa cuối:
Upvote 0
Chết em rồi, vừa gửi bài xong thì đọc được bài specialcell không làm việc với UDF
Hu hu
 
Upvote 0
Nhờ viết code thay cho Sumproduct

Em loay hoay mãi không biết kết hợp Evaluate & Sumproduct như nào
nên đã đặt name và làm bài theo công thức sumproduct
Kết Quả:
Mỗi lần thực hiện chạy rất chậm nhưng tạm thời cải tiến bằng một cách rất củ chuôi
1. Công thức tính
=SUMPRODUCT(ImEU*(ImMethod=S$6)*(ImDate=$P19))+SUMPRODUCT(ExEU*(ExMethod=S$6)*(ExDate=$P19))
Nhấp chuột vào đây để mở rộng...

Ai giúp em chuyển nó về code với, vì để công thức nó chạy chậm lắm
 
Lần chỉnh sửa cuối:
Upvote 0
PHP: 
Private Sub CommandButton1_Click()

Application.Calculation = xlCalculationManual
Range("Q9:U38").ClearContents
Me.Calendar1.Visible = False
With Sheet8
If [Q3] > [Q4] Then: MsgBox "Re-check date From...to..": Exit Sub
[P8:P40].ClearContents
[P8] = [Q3]
For i = 1 To [Q4] - [Q3]
Cells(i + 8, "P") = Cells(i + 7, "P") + 1
Next i
End With
Range("Q8:U38").FillDown
Application.Calculation = xlCalculationAutomatic
End Sub



Private Sub Worksheet_Deactivate()
Range("Q9:U38").ClearContents
End Sub

Em vừa cải tiến bằng một cách củ chuối là
Khi deactive sheet thì xóa hết các công thức sumproduct từ lại dòng đầu
Khi nhấn button Do-This thì lại fill down xuống

Dù sao cũng cảm thấy nó nhanh hơn nhiều nhưng giá làm được bằng code thì học hỏi thêm nhiều thứ
He he
 
Upvote 0
ST-Lu! đã viết:
PHP: 
Function Sum_ST(Ngay As Date, method As String)

With Sheet5.[A1].CurrentRegion
.AutoFilter Field:=13, Criteria1:="method"
.AutoFilter Field:=14, Criteria2:="ngay"
End With
Amt1 = WorksheetFunction.Sum(Sheet5.Range("G2:G" & Sheet5.[A65000].End(xlUp).Row).SpecialCells(12))
Sum_ST = Amt1

End Function
SUm_ST = Amt1 + AMt2 (AMt2 tương ứng với sheet Ex 2010)
Nhưng tạm thời em làm Amt1 (cho sheet Im 2010) nhưng kết quả bị sai Thầy NDU ơi
Giúp em với
Nhấp chuột vào đây để mở rộng...
Hàm tự tạo làm gì có cái vụ AutoFilter trong này hả bạn? Phải dùng For... Next thôi
Theo tôi, hàm đó phải vầy:
PHP: 
Function Sum_ST(Src1, Cond1, Src2, Cond2, SrcTotal) As Double
.....
End Function
Trong đó:
Src1 là vùng dữ liệu 1
Cond1 là điều kiện lọc 1
Src2 là vùng dữ liệu 2
Cond2 là điều kiện lọc 2
SrcTotal là vùng tính tổng
Tiếp theo
PHP: 
For i = 1 to UBound(Src1)
  If Src(i,1) = Cond1 And Src2(i,1) = Cond2 then
   Sum_ST = Sum_ST + SrcTotal(i,1)
  End Ì
Next i
Đại khái thế
 
Upvote 0
Không paste special sheet8.range("Q8:U38") được

Thưa thấy NDU
chắc trình của em còn phải học dài mới viết được hàm UDF. Cái này em sẽ cố gắng sau
Tạm thời em giải quyết như sau
1. Đặt name khi sheet đó Active và xóa name khi deactive: không biết mấy cái DIm dưới có bị thừa khi đặt name không?
Vậy vẫn đảm bảo File tính toán nhanh hơn nhưng cách làm thì quá lòng vòng. Nhưng hiện tại em đang bí paste special
PHP: 
Private Sub Worksheet_Activate()
Dim ImBill As Range, ImEU As Range, ImDate As Range, Immethod As Range
Dim ExBill As Range, ExEU As Range, ExDate As Range, Exmethod As Range
With Sheet5.Range("A2:A" & Sheet5.[A65000].End(xlUp).Row)
.Name = "ImBill"
.Offset(, 7).Name = "ImEU"
.Offset(, 12).Name = "ImMethod"
.Offset(, 13).Name = "ImDate"
End With
With Sheet6.Range("A3:A" & Sheet6.[A65000].End(xlUp).Row)
.Name = "ExBill"
.Offset(, 12).Name = "ExEU"
.Offset(, 13).Name = "exMethod"
.Offset(, 14).Name = "ExDate"
End With
End Sub

2. Tại command button em có code:
Em bê nguyên cái sumproduct đặt vào [Q8]
PHP: 
Private Sub CommandButton1_Click()
Dim i As Long
Application.Calculation = xlCalculationManual
Range("P8:U38").ClearContents
Me.Calendar1.Visible = False
With Sheet8
If Range("Q3") > Range("Q4") Then: MsgBox "Re-check date From...to..": Exit Sub
Range("P8:P40").ClearContents
Range("P8") = Range("Q3")
For i = 1 To WorksheetFunction.Min(Range("Q4") - Range("Q3"))
Cells(i + 8, "P") = Cells(i + 7, "P") + 1
Next i
End With
Range("Q8") = "=SUMPRODUCT(ImEU*(ImMethod=Q$6)*(ImDate=$P8))+SUMPRODUCT(ExEU*(exMethod=Q$6)*(ExDate=$P8))"
Range("Q8").AutoFill Destination:=Range("Q8:U8")
Range("Q8:U38").FillDown
Application.Calculation = xlCalculationAutomatic
End Sub

Vấn đề em đã đặt xong nhưng không thể paste special vùng Q8:U38 được (thực hiện bằng tay ok) nhưng bằng code không được.
mặc dù em làm bằng tay, record code nhưng khi lồng vào code chạy thì không được
 

File đính kèm

  • test.zip
    499.3 KB · Đọc: 12
Lần chỉnh sửa cuối:
Upvote 0
Em muốn dùng hàm sumproduct để tính tổng của các loại hàng hóa sau với nhiều điều kiện trong cũng một mảng, nhưng mà em làm hoài mà nó ko ra kq được, mọi người xem rồi giúp giùm em với nha
View attachment 49380
 
Upvote 0
Bạn phải đăng nhập hoặc đăng ký để trả lời bài viết tại đây.
Web KT

Bài viết mới nhất

Thịnh Nguyễn 2
Giúp e câu 3 và 4 ạ
Trả lời
1
Đọc
18
Hoàng Tuấn 868
H
hongphuong1997
Đếm ô có dữ liệu.
    • Chấp nhận
    • Thích
Trả lời
14
Đọc
274
Văn Toàn 1996
V
anhkta
  • Question
Xuất dữ liệu bằng VBA
Trả lời
9
Đọc
167
tle2003
T
hancungne
Get dữ liệu từ máy khác thông qua ip
Trả lời
0
Đọc
11
hancungne
hancungne
K
In PDF ở chế độ "Fit all column in one page" ở office 2019 bị giảm số lượng trang so vs màn hình làm việc
Trả lời
0
Đọc
11
KenJayce
K
Xem thêm…

Facebook

Giaiphapexcel.com

Group

DIỄN ĐÀN GIẢI PHÁP EXCEL Group 1
DIỄN ĐÀN GIẢI PHÁP EXCEL Group 2

Thành viên có số lượng bài viết cao nhất tháng

Thành viên có điểm tương tác cao nhất tháng

Back
Top Bottom