Thay sumifs bằng VBA

[Thien]

Chào cả nhà.

Hiện tại đang dùng sumifs để lấy dữ liệu lên báo cáo nhưng file có nhiều sheet và nhiều dữ liệu nên chạy rất chậm.

Nay nhờ các tiền bối trong VBA viết hộ code thay hàm sumifs để cải thiện tốc độ file, do file quá nặng nên đã xóa bớt sheet và dữ liệu.

Rất mong sự hồi đáp từ các bạn

Thân chào

 

[VetMini]

Dữ liệu nhiều thì đổi dùng Excel 2013/16 và học cách dùng PowerPivot

 

[Thien]

Chào cả nhà,

Giúp mình với, đang cần làm báo cáo gấp mà file chạy quá chậm.

Thân chào

 

[Hai Lúa Miền Tây]

Chào cả nhà,

Giúp mình với, đang cần làm báo cáo gấp mà file chạy quá chậm.

Thân chào

Chưa chắc hàm tự tạo mà tối ưu bằng hàm và công cụ sẵn có.

 

[Thien]

Chưa chắc hàm tự tạo mà tối ưu bằng hàm và công cụ sẵn có.

Nhưng file chứa nhiều công thức quá nên rất chậm. 12 chi nhánh 12 sheet, mỗi sheet 12 tháng, sheet tổng 12 chi nhánh của 12 tháng.
Không biết Anh có cách lấy số liệu nào bằng VBA tối ưu hơn sumifs đang sử dụng.

Thân chào.

 

[Thien]

Thân chào cả nhà,

Có ai hỗ trợ giúp với, đang rất cần.
Chân thành cảm ơn sự lưu tâm.


Thân chào.

 

[Hoang2013]

Khiêp cáo công thức SUMIFs() của bạn & mình cũng không đủ kiên nhẫn để dò hết nó!

Bạn có thể diễn dịch nó thành tiếng Việt được không?

 

[quocgiacan]

Thân chào cả nhà,
Có ai hỗ trợ giúp với, đang rất cần.
Chân thành cảm ơn sự lưu tâm.
Thân chào.

Góp ý:
1. Bạn nên tránh sử dụng vùng trong công thức theo kiểu A:A, B:B.... chính nó góp phần gây ra làm chậm tốc độ xử lý. Nên giới hạn vùng, ví dụ: A1:A100, B5:B2500...
2. Chưa xem hết các công thức của bạn nhưng điều chỉnh 1 công thức của bạn như sau: sheet DP1

D48=SUM(SUMIFS(DuLieu!$E1:$E100,DuLieu!$A1:$A100,D1,DuLieu!B1:B100,$C$1,DuLieu!$C1:$C100,{"CP1_VLQLC";"CP1_CCQL";"CP1_DV_CCQL";"CP1_LVS_CCQL";"CP1_DV_TB";"CP1_LVS_TB";"CP1_DV_VPP";"CP1_LVS_VPP";"CP1_VPP";"CP1_NNV";"CP1_INAN";"CP1_DAOTAO";"CP1_LVS_CCDC"}))

Enter.

Tương tự cách làm như vậy, bạn tự chỉnh các công thức khác có kiểu nhiều SUMIFS().

Chúc bạn ngày vui.

 

[Thien]

Góp ý:
1. Bạn nên tránh sử dụng vùng trong công thức theo kiểu A:A, B:B.... chính nó góp phần gây ra làm chậm tốc độ xử lý. Nên giới hạn vùng, ví dụ: A1:A100, B5:B2500...
2. Chưa xem hết các công thức của bạn nhưng điều chỉnh 1 công thức của bạn như sau: sheet DP1

D48=SUM(SUMIFS(DuLieu!$E1:$E100,DuLieu!$A1:$A100,D1,DuLieu!B1:B100,$C$1,DuLieu!$C1:$C100,{"CP1_VLQLC";"CP1_CCQL";"CP1_DV_CCQL";"CP1_LVS_CCQL";"CP1_DV_TB";"CP1_LVS_TB";"CP1_DV_VPP";"CP1_LVS_VPP";"CP1_VPP";"CP1_NNV";"CP1_INAN";"CP1_DAOTAO";"CP1_LVS_CCDC"}))

Enter.
Tương tự cách làm như vậy, bạn tự chỉnh các công thức khác có kiểu nhiều SUMIFS().
Chúc bạn ngày vui.

Hay quá, cám ơn bạn rất nhiều.

Hiện tại mình có đoạn code này nhưng dùng trong office 2010 khi lưu lại và mở ra thì code không chạy:
Option Explicit
Function SumIfMSh(VungDK As Range, DK As Variant, VungKQ As Range) As Double
Dim Sh As Worksheet, aRng As Range
'Application.Volatile
Set aRng = Application.Caller
For Each Sh In ThisWorkbook.Worksheets
If Sh.Name <> aRng.Parent.Name Then
SumIfMSh = SumIfMSh + WorksheetFunction.SumIf(Sh.Range(VungDK.Address), DK, Sh.Range(VungKQ.Address))
End If
Next Sh
End Function

Các bạn xem hộ.

Thân chào

 

[quocgiacan]

Nhưng file chứa nhiều công thức quá nên rất chậm. 12 chi nhánh 12 sheet, mỗi sheet 12 tháng, sheet tổng 12 chi nhánh của 12 tháng.
Không biết Anh có cách lấy số liệu nào bằng VBA tối ưu hơn sumifs đang sử dụng.

Thân chào.

Do cách đưa dữ liệu các tiểu mục chi phí của bạn tự làm rối chính bạn, nên bạn thấy SUMIFS() "nặng nề" nên mong muốn có VBA thay thế.
Theo tôi bạn nên thực hiện như sau:
1. Tạo 1 sheet theo dõi Danh mục Chi phí theo tiểu mục, theo cột dọc. Nếu có thêm bớt gì cũng dễ quản lý và luôn được cập nhật cho công thức SUMIFS().
2. Thực hiện SUMIFS() dựa vào dòng diễn giải (cũng là tiêu đề mục chi phí) của báo cáo tổng hợp DP1 mà truy xuất.

D7=SUMPRODUCT(SUMIFS(DuLieu!$E$1:$E$100,DuLieu!$A$1:$A$100,$D$1,DuLieu!$B$1:$B$100,$C$1,DuLieu!$C$1:$C$100,OFFSET(DMucCPhi!$A$2:$A$50,,MATCH($B7,DMucCPhi!$A$1:$AW$1,0)-1)))

Fill xuống cho các dòng chi phí tương ứng.
3. Còn có thể điều chỉnh vào công thức theo dõi từng tháng nếu bạn muốn thêm nữa....

Bạn tham khảo file kèm, tôi đã làm sơ khởi cho bạn và cũng điều chỉnh các công thức SUMIFS(), chỉ cần thực hiện 1 ô đầu rồi kéo xuống copy cho các dòng chi phí phía dưới.

Chúc bạn ngày vui.

 

[PhanTuHuong]

Về lý thuyết thì Hàm VBA sẽ chậm hơn hàm Excel nhiều, bạn nên tối ưu hàm thì tốt hơn.

 

[Thien]

Do cách đưa dữ liệu các tiểu mục chi phí của bạn tự làm rối chính bạn, nên bạn thấy SUMIFS() "nặng nề" nên mong muốn có VBA thay thế.
Theo tôi bạn nên thực hiện như sau:
1. Tạo 1 sheet theo dõi Danh mục Chi phí theo tiểu mục, theo cột dọc. Nếu có thêm bớt gì cũng dễ quản lý và luôn được cập nhật cho công thức SUMIFS().
2. Thực hiện SUMIFS() dựa vào dòng diễn giải (cũng là tiêu đề mục chi phí) của báo cáo tổng hợp DP1 mà truy xuất.

D7=SUMPRODUCT(SUMIFS(DuLieu!$E$1:$E$100,DuLieu!$A$1:$A$100,$D$1,DuLieu!$B$1:$B$100,$C$1,DuLieu!$C$1:$C$100,OFFSET(DMucCPhi!$A$2:$A$50,,MATCH($B7,DMucCPhi!$A$1:$AW$1,0)-1)))

Fill xuống cho các dòng chi phí tương ứng.
3. Còn có thể điều chỉnh vào công thức theo dõi từng tháng nếu bạn muốn thêm nữa....
Bạn tham khảo file kèm, tôi đã làm sơ khởi cho bạn và cũng điều chỉnh các công thức SUMIFS(), chỉ cần thực hiện 1 ô đầu rồi kéo xuống copy cho các dòng chi phí phía dưới.
Chúc bạn ngày vui.

Chân thành cảm ơn đã quan tâm giúp đỡ.

Hay quá, mình sẽ làm lại cho toàn bộ xem cải thiện được tốt hơn đến mức nào.

Cảm ơn rất nhiều.

Thân chào.

 

[Thien]

Chào cả nhà,

Mình muốn sử dụng code sau dùng trong office 2010, hiện tại chỉ sử dụng trong office cũ:

Sub SaveThanhFileKhacBoCongThuc()
Dim wPath As String, wName As String
wPath = ThisWorkbook.Path
wName = ThisWorkbook.Name
For i = 1 To Sheets.Count
Sheets(i).Activate
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Next
ActiveWorkbook.SaveAs Filename:=wPath & "\SA_" & wName
End Sub

Thân chào.