thay sumproduct bằng VBA

[tongquangtuyen]

Em có bảng theo dõi công nợ (theo file đính kèm) có sử dụng hàm sumproduct (từ cột AV-> BI),thì khi nhập dữ liệu vào bảng tính đợi một lúc lâu mới nhập được file chạy rất châm.
Nhờ các Anh / Chị trên GPEX giúp thay đổi hàm S um pro duct bằng VbA để file chạy nhanh hơn.
Xin chân thành cảm ơn!

 

[anhphuong]

Tắt chức năng Automatic đi là được.
Vào Tools/Oftions trong trang Calculation tích vào dấu Manual, bỏ Automatic. Nhập liệu thoải mái, khi cần tính toán, nhấn F9, Ex sẽ refesh lại bảng tính

 

[xichdragon]

File của bạn có nhiều công thức tính như vậy thì không nên để chức năg tự động cập nhật bảng tính.
Vào Option/Calculation/Manual để khi nào nhập hết dữ liệu thì bấm F9 rồi đi uống trà đợi nó tính thôi
Regards

 

[SA_DQ]

Nếu bạn vẫn muốn chuyển sang tính toán bằng macro, thì giải thích công thức rõ hơn

Em có bảng theo dõi công nợ Nhờ các Anh / Chị trên GPEX giúp thay đổi hàm S um pro duct bằng VbA để file chạy nhanh hơn.
Xin chân thành cảm ơn!

Ví dụ hãy giải thích công thức sau:

=SUMPRODUCT(--(LEFT($E$9:$E$2500,2)="11"),--($C$9:$C$2500=AO9),--($B$9:$B$2500<=$AV$8),--($H$9:$H$2500))​

 

[tongquangtuyen]

Công thức trên là để tính tổng số tiền khách hàng đã thanh toán ở tuần đầu tiền củ tháng

cột B:B -> là vùng ghi nhận ngày tháng
Cột C:C -> là vùng ghi nhận khách hàng
cột E:E -> là vùng ghi định khoản tài khoản 111;1121,1122
Cột H:H -> là vùng ghi nhận số tiền
AO9 -> ghi nhận khách hàng A cần tính
AV8 -> là ngày chủ nhật đầu tiên của tháng

 

[SA_DQ]

Macro ch câu cột "AV" đây, xin mời thử & cho biết kết quả

Option Explicit
Sub SumProduct()
 Dim eRw As Long, Jj As Long:                   Dim MyAdd As String
 Dim Rng As Range, sRng As Range
  
 eRw = [AO65500].End(xlUp).Row:                 Application.ScreenUpdating = False
 Set Rng = Range([c8], [c65500].End(xlUp))
 For Jj = 9 To eRw
   With Cells(Jj, "Ao")
      Set sRng = Rng.Find(.Value, , xlFormulas, xlWhole)
      If Not sRng Is Nothing Then
         MyAdd = sRng.Address
         Do
9           If Left(sRng.Offset(, 2), 2) = "11" And sRng.Offset(, -1) <= [AV8].Value Then
               If sRng.Offset(, 5) > 0 Then _
                  Cells(Jj, "AV").Value = sRng.Offset(, 5) + Cells(Jj, "AV").Value
            End If
            Set sRng = Rng.FindNext(sRng)
         Loop While Not sRng Is Nothing And sRng.Address <> MyAdd
      End If
   End With
 Next Jj
End Sub

Hướng dẫn cách dùng:

(*) Chép macro này vô cửa sổ VBE

(*) Chọn cột 'AV' & vô menu Insert để thêm cột; Tại dòng 8 của cột mới thêm ta nhập trị ngày của 'AW8' ( 7-Dec)

(*) Chạy macro sau đó đối chiếu với cột số liệu cũ.

Để phát triển sang các cột khác, ta chú í dòng lệnh có đánh số trong macro

Chúc thành công!

 

[tongquangtuyen]

Cám ơn bạn nhiều nha !
Mình kiểm tra rồi thấy đúng lắm .Để nghien cứu cho các cột sau ?
À mà cho mình hỏi là các cột sau cũng sẽ lọc kết quả theo tuần hả bạn?
Mình đã kiểm tra các cột tiếp theo thì thấy số tiền có ở cột trước vẫn xuất hiên trong cọt kế tiếp .đúng lý ra nó phải không chạy ra chứ .vì điều kiện bây giờ là > av8(7 -Dec) và nhỏ hơn hoặc bằng aw8(14 - Dec)

 

[SA_DQ]

Macro cho cột 'AW' đây, xin mời!

Option Explicit
Sub SumProductAW()
 Dim eRw As Long, Jj As Long:                   Dim MyAdd As String
 Dim Rng As Range, sRng As Range, tRng As Range
 
 eRw = [AN65500].End(xlUp).Row:                 Application.ScreenUpdating = False
 Set Rng = Range([c8], [c65500].End(xlUp))
 For Jj = 9 To eRw
   With Cells(Jj, "Ao")
      Set sRng = Rng.Find(.Value, , xlFormulas, xlWhole)
      If Not sRng Is Nothing Then
         MyAdd = sRng.Address
         Do
9           If Left(sRng.Offset(, 2), 2) = "11" And _
               (sRng.Offset(, -1) > [AV8].Value And sRng.Offset(, -1) <= [AW8].Value) Then
               If sRng.Offset(, 5) > 0 Then _
                  Cells(Jj, "AW").Value = sRng.Offset(, 5) + Cells(Jj, "AW").Value  '<=|'
            End If
            Set sRng = Rng.FindNext(sRng)
         Loop While Not sRng Is Nothing And sRng.Address <> MyAdd
      End If
   End With
 Next Jj
End Sub

Chú ý:

(*) Các dòng lệnh 9 & 10 đã đổi so với macro dành cho cột 'AV'

(*) Dòng lệnh xác định trị cho biến eRw đã đổi xíu cho chính xác hơn!

 

[tongquangtuyen]

Mình thử chạy code trên thấy ok rồi.nhưng nó lại lấy số tiền từ cột Av luôn làm cho kết quả bị trùng
đáng lý ra nếu số tiền đã có ở cột Av thì sẽ không có ở cột Aw.mình lấy ví dụ cho bạn dễ hinh dung
Khách hàng A trả ở tuần thứ 1 là 100tr và tuần thứ 2 trả 50tr thì sẽ được tổng kết như sau:

AV(7 -dec ) AW(14-dec)
A 100tr 50tr

 

[SA_DQ]

Mình thử chạy code trên thấy ok rồi.nhưng nó lại lấy số tiền từ cột Av luôn làm cho kết quả bị trùng
đáng lý ra nếu số tiền đã có ở cột Av thì sẽ không có ở cột Aw.

Đúng là mình chưa nói rõ cách làm;

(Sau khi thực hiện việc thêm cột 'AV' & chạy macro đầu để nó cập nhật cột mới này), ta xóa dữ liệu (không phải xóa cột à nha) trong cột ngay bên phải liền kề, bắt đầu từ dòng 9 . Còn ô tại dòng 8 ta cũng phải sửa lại cho đúng í đồ của bạn ( đó là tại [AV8] là 7-Dec & [Aw8] là 14-Dec)
Chỉ sau đó mới chạy macro thứ hai;
Hãy ngâm cứu kĩ hơn về 2 dòng lệnh có số ở 2 macro nha!

Chúc thành công.

 

[tongquangtuyen]

Mình kiểm tra lại lại thấy hoàn toàn chính xác! Cám ơn bạn rất nhiều
Cho mình hỏi thêm nha nếu trong vùng nhập liệu(A : K) mình insert thêm cột thì code trên còn đúng nữa hay không ? Vì mình đang có ý thêm một cột mã sản phẩm để tổng hợp số tiền khách hàng trả cho từng loại sản phẩm họ mua

 

[sealand]

Mình thấy nếu dùng VBA sao vẫn dùng link để kết nối nhiều thế. Những cái link này đáng sợ lắm đấy. Thực tế, mình không đợi nổi mở được File của bạn.
Trên sh này nên có 1 check box nếu đánh dấu thì cho cập nhật. Nếu bỏ thì khóa vùng nhập và chạy code tổng hợp dữ liệu.

 

[tongquangtuyen]

vì sh chạy chậm nên mình mới gửi lên để mong mọi người viết code nhằm thay thế hàm sumproduct mình đang sử dụng trong sh,dùng nhiều hàm sumproduct máy chạy quá chậm luôn

 

[SA_DQ]

Mình kiểm tra lại lại thấy hoàn toàn chính xác! Cám ơn bạn rất nhiều

Cho mình hỏi thêm nha nếu trong vùng nhập liệu(A : K) mình insert thêm cột thì code trên còn đúng nữa hay không ? Vì mình đang có ý thêm một cột mã sản phẩm để tổng hợp số tiền khách hàng trả cho từng loại sản phẩm họ mua

2 Macro trên đều lấy cột 'Ao' làm chuẩn để duyệt danh sách khách hàng,;
Nếu cần thêm số liệu sau cột 'H' & trước cột này thì vẫn không sao cả. Nhưng mình nhấn mạnh là thêm số liệu, chư không thêm cột nha;
(Bạn còn 1 số cột chưa có dữ liệu cơ mà; Hãy dời qua lại giữa chúng cho thích hợp!)

Còn nếu muốn thêm cột thì phải có kiến thức về VBA để tự sửa lại macro

Chúc thành công.

 

[uocmoketoantruong]

tạo VBA thay cho hàm sumproduct

Em cũng có sử dụng hàm sumproduct trong bảng tính rất nhiều. Vì vậy file của em chạy rất chậm và khi nhập liệu thì cứ một lần ấn Enter thì máy nó cứ chạy rất là mệt. Các anh chị có thể hướng dẫn cách lập một macro để thay thế cho hàm này được không? Vì file của em là file dữ liệu và rất lớn lại có liên tới nhiều sheet nên không thể up lên diên đàn được. Mong các anh chị giúp đỡ.