Tốc độ xử lý vòng lặp for (1 người xem)

[sonlamhpu]

Chào các anh, em là thành viên mới, mới tiếp xúc với excel được một thời gian ngắn, kiến thức còn khá hạn hẹp, mong các bác chỉ giáo.
Hiện tại em có một chương trình tính bộ lọc filter, nhưng tốc độ xử lý chậm quá, do dùng tới 4 vòng for lồng
tính ra với khoảng 1000 dòng thì chắc phải đợi hơn tiếng đồng hồ mới xong được :|. Các bác bớt chút thời gian xem qua dùm em với nhé, vì em sử dụng record macro, nên code hơi dài em giải thích lại như sau ạ:
1. Vùng dữ liệu: Cột B - > Cột J. Dòng thì tuỳ thuộc vào số lượng dữ liệu mình nhập vào
2. Các bước thực hiện của chương trình:
Bước 1: Đếm số phần tử trong các cột D, E, G, H và đẩy vào các mảng tương ứng
Bước 2: Tìm được các phần tử rồi, tiến hành xử lý dữ liệu:
a. Lọc cột D, lấy 1 phần tử ( A(0) )
b. Các dữ liệu đã lọc được tiếp tục filter cột E để lấy 1 tên hàng ( B(0) )
c. Các dữ liệu đã lọc được tiếp tục filter cột G để lấy 1 đơn giá ( Z(0) )
d. Các dữ liệu đã lọc được tiếp tục filter cột G để lấy 1 xuất xứ ( Y(0) )
Bước 3: Copy dòng đầu tiên của phần filter, paste vào phần dòng kế tiếp của phần có dữ liệu (lastcell + 1)
Bước 4: Dòng kế tiếp của dòng cuối cùng có dữ liệu của cột F (tiếp theo của phần có dữ liệu) tính tổng (Subtotal) của những gì mình đã filter được.
Bước 5:. Copy dòng đó sang sheet 2 để có một dữ liệu của phần ta đã lọc
3. Vòng lặp for sẽ chạy dần để lọc được hết dữ liệu

* Mục đích: Khi làm thủ công, lọc các hàng có cùng một mã, cùng tên hàng, có đơn giá và xuất xứ giống nhau, tính tổng của chúng rồi copy mã hàng, tên hàng, số lượng (tổng) đơn giá, xuất xứ ra một file khác. Để cuối cùng ta có một file tổng hợp (tổng cộng các dữ liệu lại)

Các bác giúp em với nhé, em hí hửng làm, cứ tưởng chạy ngon rồi, ai dè cuối cùng lại làm mất khá nhiều thời gian để xứ lý, tuy là có nhanh hơn với việc làm tay (cái này chưa chắc). Nhưng lại phải đợi lâu nếu dữ liệu nhiều thì lại càng toi :|.
Em cảm ơn các bác nhiều !

 

[HungQuoc49]

Chào các anh, em là thành viên mới, mới tiếp xúc với excel được một thời gian ngắn, kiến thức còn khá hạn hẹp, mong các bác chỉ giáo.
Hiện tại em có một chương trình tính bộ lọc filter, nhưng tốc độ xử lý chậm quá, do dùng tới 4 vòng for lồng
tính ra với khoảng 1000 dòng thì chắc phải đợi hơn tiếng đồng hồ mới xong được :|. Các bác bớt chút thời gian xem qua dùm em với nhé, vì em sử dụng record macro, nên code hơi dài em giải thích lại như sau ạ:
1. Vùng dữ liệu: Cột B - > Cột J. Dòng thì tuỳ thuộc vào số lượng dữ liệu mình nhập vào
2. Các bước thực hiện của chương trình:
Bước 1: Đếm số phần tử trong các cột D, E, G, H và đẩy vào các mảng tương ứng
Bước 2: Tìm được các phần tử rồi, tiến hành xử lý dữ liệu:
a. Lọc cột D, lấy 1 phần tử ( A(0) )
b. Các dữ liệu đã lọc được tiếp tục filter cột E để lấy 1 tên hàng ( B(0) )
c. Các dữ liệu đã lọc được tiếp tục filter cột G để lấy 1 đơn giá ( Z(0) )
d. Các dữ liệu đã lọc được tiếp tục filter cột G để lấy 1 xuất xứ ( Y(0) )
Bước 3: Copy dòng đầu tiên của phần filter, paste vào phần dòng kế tiếp của phần có dữ liệu (lastcell + 1)
Bước 4: Dòng kế tiếp của dòng cuối cùng có dữ liệu của cột F (tiếp theo của phần có dữ liệu) tính tổng (Subtotal) của những gì mình đã filter được.
Bước 5:. Copy dòng đó sang sheet 2 để có một dữ liệu của phần ta đã lọc
3. Vòng lặp for sẽ chạy dần để lọc được hết dữ liệu

* Mục đích: Khi làm thủ công, lọc các hàng có cùng một mã, cùng tên hàng, có đơn giá và xuất xứ giống nhau, tính tổng của chúng rồi copy mã hàng, tên hàng, số lượng (tổng) đơn giá, xuất xứ ra một file khác. Để cuối cùng ta có một file tổng hợp (tổng cộng các dữ liệu lại)

Các bác giúp em với nhé, em hí hửng làm, cứ tưởng chạy ngon rồi, ai dè cuối cùng lại làm mất khá nhiều thời gian để xứ lý, tuy là có nhanh hơn với việc làm tay (cái này chưa chắc). Nhưng lại phải đợi lâu nếu dữ liệu nhiều thì lại càng toi :|.
Em cảm ơn các bác nhiều !

Cái này dùng PivotTable là được. Nếu dùng VBA thì cho hỏi là có phải bạn muống tổng hợp theo mã hàng hóa?

 

[sonlamhpu]

không phải theo mã hàng mà theo 4 cột bao gồm D, E, G, H tức là đầu tiên filter cột D lấy 1 mã hàng, sau đó dữ liệu đã được filter, thì tiến hành filter tiếp trên cột E, lấy một tên hàng, sau đó dữ liệu đã được filter 2 lần, filter tiếp trên cột đơn giá, lấy một đơn giá, sau đó dữ liệu đã được filter 3 lần, tiến hành filter trên cột xuất xứ để ra dữ liệu cuối cùng. Sau đó tính tổng ở cột F ra được số lượng tổng của các dữ liệu đã filter (của các hàng có cùng một mã hàng, cùng một tên hàng, cùng một giá và cùng một xuất xứ) Rồi copy lại cái phần đó, sang một bảng mới, lưu lại, làm tương tự với các mã hàng khác. Do quá trình nhập hàng là nhiều lần (cùng mã hàng có thể có đơn giá khác nhau, tên khác nhau, và xuất xứ khác nhau bác ạ) nên sẽ có nhiều dữ liệu như vậy, yêu cầu là phải tỉnh được tổng số lượng đã nhập về, nên mới phải dùng filter để đưa ra dược kết quả tổng đó ạ

 

[dhn46]

Bạn xem như thế này có được không? Nhưng trước tiên phải chuyển dữ liệu kiểu Text tại cột số lượng và đơn giá sang dạng number.

Sub TongHop()
    Dim Arr, Res
    Dim Tmp As String, i As Long, k As Long
    Arr = Sheet1.Range("D4:J" & Sheet1.Range("D65536").End(3).row)
    ReDim Res(1 To UBound(Arr, 1), 1 To UBound(Arr, 2))
    With CreateObject("Scripting.Dictionary")
        For i = 1 To UBound(Arr, 1)
            Tmp = Arr(i, 1) & "#" & Arr(i, 2) & "#" & Arr(i, 4) & "#" & Arr(i, 5)
            If Not .Exists(Tmp) Then
                k = k + 1
                .Add Tmp, k
                Res(k, 1) = Arr(i, 1)
                Res(k, 2) = Arr(i, 2)
                Res(k, 4) = Arr(i, 4)
                Res(k, 5) = Arr(i, 5)
            End If
        Next
        For i = 1 To UBound(Arr, 1)
            Tmp = Arr(i, 1) & "#" & Arr(i, 2) & "#" & Arr(i, 4) & "#" & Arr(i, 5)
            Res(.Item(Tmp), 3) = Res(.Item(Tmp), 3) + Arr(i, 3)
            Res(.Item(Tmp), 6) = Res(.Item(Tmp), 6) + Arr(i, 6)
            Res(.Item(Tmp), 7) = Res(.Item(Tmp), 7) + Arr(i, 7)
        Next
    End With
    Sheet3.Range("A2").Resize(k, 7) = Res
End Sub

 

[sonlamhpu]

Bạn xem như thế này có được không? Nhưng trước tiên phải chuyển dữ liệu kiểu Text tại cột số lượng và đơn giá sang dạng number.

Sub TongHop()
    Dim Arr, Res
    Dim Tmp As String, i As Long, k As Long
    Arr = Sheet1.Range("D4:J" & Sheet1.Range("D65536").End(3).row)
    ReDim Res(1 To UBound(Arr, 1), 1 To UBound(Arr, 2))
    With CreateObject("Scripting.Dictionary")
        For i = 1 To UBound(Arr, 1)
            Tmp = Arr(i, 1) & "#" & Arr(i, 2) & "#" & Arr(i, 4) & "#" & Arr(i, 5)
            If Not .Exists(Tmp) Then
                k = k + 1
                .Add Tmp, k
                Res(k, 1) = Arr(i, 1)
                Res(k, 2) = Arr(i, 2)
                Res(k, 4) = Arr(i, 4)
                Res(k, 5) = Arr(i, 5)
            End If
        Next
        For i = 1 To UBound(Arr, 1)
            Tmp = Arr(i, 1) & "#" & Arr(i, 2) & "#" & Arr(i, 4) & "#" & Arr(i, 5)
            Res(.Item(Tmp), 3) = Res(.Item(Tmp), 3) + Arr(i, 3)
            Res(.Item(Tmp), 6) = Res(.Item(Tmp), 6) + Arr(i, 6)
            Res(.Item(Tmp), 7) = Res(.Item(Tmp), 7) + Arr(i, 7)
        Next
    End With
    Sheet3.Range("A2").Resize(k, 7) = Res
End Sub

em chưa hiểu rõ lắm, bác giúp em giải thích được ko ạ? em mới vào làng, nên chưa hiểu lắm, thay cái này trong phần nào hả bác ?

 

[sonlamhpu]

Bạn xem như thế này có được không? Nhưng trước tiên phải chuyển dữ liệu kiểu Text tại cột số lượng và đơn giá sang dạng number.

Sub TongHop()
    Dim Arr, Res
    Dim Tmp As String, i As Long, k As Long
    Arr = Sheet1.Range("D4:J" & Sheet1.Range("D65536").End(3).row)
    ReDim Res(1 To UBound(Arr, 1), 1 To UBound(Arr, 2))
    With CreateObject("Scripting.Dictionary")
        For i = 1 To UBound(Arr, 1)
            Tmp = Arr(i, 1) & "#" & Arr(i, 2) & "#" & Arr(i, 4) & "#" & Arr(i, 5)
            If Not .Exists(Tmp) Then
                k = k + 1
                .Add Tmp, k
                Res(k, 1) = Arr(i, 1)
                Res(k, 2) = Arr(i, 2)
                Res(k, 4) = Arr(i, 4)
                Res(k, 5) = Arr(i, 5)
            End If
        Next
        For i = 1 To UBound(Arr, 1)
            Tmp = Arr(i, 1) & "#" & Arr(i, 2) & "#" & Arr(i, 4) & "#" & Arr(i, 5)
            Res(.Item(Tmp), 3) = Res(.Item(Tmp), 3) + Arr(i, 3)
            Res(.Item(Tmp), 6) = Res(.Item(Tmp), 6) + Arr(i, 6)
            Res(.Item(Tmp), 7) = Res(.Item(Tmp), 7) + Arr(i, 7)
        Next
    End With
    Sheet3.Range("A2").Resize(k, 7) = Res
End Sub

cảm ơn bác rất nhiều nhé, cách của bác quá nhanh quá nguy hiểm, xo với thời gian em ngồi đợi dài mồm ra đấy :|. Ra biển lớn mới thấy mình là con tép :d. Em sẽ tìm hiểu lại một vài cấu trúc, bác có thời gian rảnh giải thích dùm em với nhé, một vài chỗ em chưa thấy bao giờ nên chưa hiểu lắm. Cảm ơn bác rất nhiều ạ.

 

[dhn46]

em chưa hiểu rõ lắm, bác giúp em giải thích được ko ạ? em mới vào làng, nên chưa hiểu lắm, thay cái này trong phần nào hả bác ?

Cái này là Dictionary, bạn tìm sẽ thấy.

Thực ra với bài này chẳng cần lập trình cũng có thể làm được mà không phải Auto Filter, tốc độ 1000 dòng nếu máy cấu hình tạm tạm thì cũng không đến nỗi nào. Bạn làm như sau:

Thêm một cột với công thức:

=D4&"#"&E4&"#"&G4&"#"&H4

=>Pvote table để lấy ra số lượng, .... theo cột phụ
=> Dò tìm ngược trở lại theo cột phụ vừa tạo để lấy các thông số còn lại

Chắc cũng không mất quá nhiều thời gian.

 

[sonlamhpu]

Cái này là Dictionary, bạn tìm sẽ thấy.

Thực ra với bài này chẳng cần lập trình cũng có thể làm được mà không phải Auto Filter, tốc độ 1000 dòng nếu máy cấu hình tạm tạm thì cũng không đến nỗi nào. Bạn làm như sau:

Thêm một cột với công thức:


=>Pvote table để lấy ra số lượng, .... theo cột phụ
=> Dò tìm ngược trở lại theo cột phụ vừa tạo để lấy các thông số còn lại

Chắc cũng không mất quá nhiều thời gian.

vâng, em cũng mới tìm hiểu về lập trình và excel nên còn chút bỡ ngỡ, 1 tuần của em bác làm trong vòng 1s. Pivote table em chưa bao giờ sử dụng, có lẽ phải học dài dài, mong các bác giúp đỡ nhiều.

 

[Quang_Hải]

Bạn xem như thế này có được không? Nhưng trước tiên phải chuyển dữ liệu kiểu Text tại cột số lượng và đơn giá sang dạng number.

Sub TongHop()
    Dim Arr, Res
    Dim Tmp As String, i As Long, k As Long
    Arr = Sheet1.Range("D4:J" & Sheet1.Range("D65536").End(3).row)
    ReDim Res(1 To UBound(Arr, 1), 1 To UBound(Arr, 2))
    With CreateObject("Scripting.Dictionary")
        For i = 1 To UBound(Arr, 1)
            Tmp = Arr(i, 1) & "#" & Arr(i, 2) & "#" & Arr(i, 4) & "#" & Arr(i, 5)
            If Not .Exists(Tmp) Then
                k = k + 1
                .Add Tmp, k
                Res(k, 1) = Arr(i, 1)
                Res(k, 2) = Arr(i, 2)
                Res(k, 4) = Arr(i, 4)
                Res(k, 5) = Arr(i, 5)
            End If
        Next
        For i = 1 To UBound(Arr, 1)
            Tmp = Arr(i, 1) & "#" & Arr(i, 2) & "#" & Arr(i, 4) & "#" & Arr(i, 5)
            Res(.Item(Tmp), 3) = Res(.Item(Tmp), 3) + Arr(i, 3)
            Res(.Item(Tmp), 6) = Res(.Item(Tmp), 6) + Arr(i, 6)
            Res(.Item(Tmp), 7) = Res(.Item(Tmp), 7) + Arr(i, 7)
        Next
    End With
    Sheet3.Range("A2").Resize(k, 7) = Res
End Sub

Đang thắc mắc tại sao dhn46 viết bài này lạ thiệt, tự nhiên chạy chi 2 vòng vậy? Có ý gì đặc biệt à?

Sub TongHop()
    Dim Arr, Res
    Dim Tmp As String, i As Long, k As Long, j
    Arr = Sheet1.Range("D4:J" & Sheet1.Range("D65536").End(3).row)
    ReDim Res(1 To UBound(Arr, 1), 1 To UBound(Arr, 2))
    With CreateObject("Scripting.Dictionary")
        For i = 1 To UBound(Arr, 1)
            Tmp = Arr(i, 1) & "#" & Arr(i, 2) & "#" & Arr(i, 4) & "#" & Arr(i, 5)
            If Not .Exists(Tmp) Then
                k = k + 1
                .Add Tmp, k
                For j = 1 To UBound(Arr, 2)
                  Res(k, j) = Arr(i, j)
                Next
            Else
               Res(.Item(Tmp), 3) = Res(.Item(Tmp), 3) + Arr(i, 3)
               Res(.Item(Tmp), 6) = Res(.Item(Tmp), 6) + Arr(i, 6)
               Res(.Item(Tmp), 7) = Res(.Item(Tmp), 7) + Arr(i, 7)
            End If
         Next
    End With
    Sheet3.Range("A2").Resize(k, UBound(Arr, 2)) = Res
End Sub

 

[nmhung49]

Đang thắc mắc tại sao dhn46 viết bài này lạ thiệt, tự nhiên chạy chi 2 vòng vậy? Có ý gì đặc biệt à?

Qua mình test tốc độ thì cách dhn46 tốc độ sẽ nhanh hơn cách Quanghai qua 5 lần test như sau. Kiểm trên Excel 2010, Core I5, Gram 4G, Win7
Tính trên milliseconds(ms)
Quanghai

65.343
67.437
67.619
67.852
67.537

dnh46

65.6249
64.1693
63.1390
63.2764
62.9737



Thật sự với cấu trúc if ...esle có thể chậm hơn dùng thêm vòng lặp nhưng ngược lại nó sẽ gọn hơn.

 

[sonlamhpu]

công nhận với các bác, khi khối lượng dữ liệu lớn, việc tối ưu code thật quan trọng :|. Được các bác giúp mới sáng mắt ra nhiều điều

 

[dhn46]

Đang thắc mắc tại sao dhn46 viết bài này lạ thiệt, tự nhiên chạy chi 2 vòng vậy? Có ý gì đặc biệt à?

Cũng không có gì đặc biệt đâu anh, cũng như bạn nmhung9 đã chỉ ra, "có lẽ" tốc độ If...else ảnh hưởng chút tới tốc độ em cũng đã test vài trường hợp.

 

[Quang_Hải]

Cũng không có gì đặc biệt đâu anh, cũng như bạn nmhung9 đã chỉ ra, "có lẽ" tốc độ If...else ảnh hưởng chút tới tốc độ em cũng đã test vài trường hợp.

Mình cũng thắc mắc nên nhân bản dữ liệu lên 100 000 dòng và kết quả thì ngược lại. Mặc dù chỉ có tí tẹo nhưng vẫn là 1 vòng nhanh hơn.

 

[hungpecc1]

Mình cũng thắc mắc nên nhân bản
ữ liệu lên 100 000 dòng và kết quả thì ngược lại. Mặc dù chỉ có tí tẹo nhưng vẫn là 1 vòng nhanh hơn.

+Cùng phải thực hiện 2 việc :
* Cách của Dn46 : thực hiện 2 viêc độc lập với nhau
* Cách của a Hải : một công đôi việc
ví dụ : phải vác 2 bao xi măng lên tầng 4,
một anh khỏe có thể vác cả 2 bao cùng một lúc, còn anh yếu thì vác từng bao lên 1
Xét về thời gian nhanh chậm thì còn phụ thuộc vào sức khỏe của từng anh.
em test máy tính của em thì code của anh Hải tốc độ vẫn nhanh hơn của Dhn46, kết quả cụ thể như sau :
* Cấu hình má y : Core I5 -337UCPU @1.80GHz , Ram DDR3 -6G
* Công cụ đo:

Declare Function GetTickCount Lib "kernel32" () As Long

sub()
T1 = gettickcount()
.......................
T2  =gettickcout()
Debug.Print T2-T1
End sub

* Tốc độ code của anh Hải(milisecond) :
31
31
31
32
31
31
31
47
31
31
* Tốc độ code của Dhn46 (milisecond) :
47
47
47
31
32
31
47
47
31
47

 

[Quang_Hải]

Thật ra nhanh chậm trong nháy mắt cũng không sao vì anh em mình toàn là dân nghiệp dư ham vui
Nhưng nếu code đơn giản thì mình vẫn khoái hơn dù có chậm tẹo cũng không sao. Bởi vậy học ADO hoài mà chẳng cách nào nhớ được cú pháp.

 

[dhn46]

Thật ra nhanh chậm trong nháy mắt cũng không sao vì anh em mình toàn là dân nghiệp dư ham vui
Nhưng nếu code đơn giản thì mình vẫn khoái hơn dù có chậm tẹo cũng không sao. Bởi vậy học ADO hoài mà chẳng cách nào nhớ được cú pháp.

Để hiểu được chắc mất nhiều thời gian anh Hải nhỉ? Mấy cái tốc độ này test thì biết thế chứ phụ thuộc vào cái gì cũng còn tìm "lâu lâu", nhận xét thì cũng là "có lẽ" thôi. Cũng như duyệt hàng trước hay cột trước sẽ nhanh hơn ^^, các cao thủ không phân tích thì lại "có lẽ". Quan điểm của em là nghiệp dư nên làm sao Copy Code để ứng dụng được là tốt rồi tùy biến cho dữ liệu thực của mình thật hiệu quả là tốt hơn nữa. Cũng như ADO, làm các bài cơ bản copy, paste là giải quyết được đến giờ cái cú pháp kết nối quên mất tiêu ah .

 

[nmhung49]

Kiểm tra với dữ liệu 100.000 dòng cách đo chỉ mở file excel mở Task Manager chờ CPU, Gram chạy ổn định (dự vào code và cách đo của a.Tuân) thì cách của a Quanghai nhanh hơn dnh46, với cách đo này với dữ liệu trước là 800 dòng mình thấy cách dnh46 lại nhanh hơn, không biết trong nội bộ code chạy thế nào vì anh em mình chỉ là dân nghiệp dư. Nhưng có thể chỉ đoạn code của a Quanghai và dnh46 lại như thế nào thì cả dữ liệu ít và nhiều rất điều nhanh

Res(.Item(Tmp), 3) = Res(.Item(Tmp), 3) + Arr(i, 3)
Res(.Item(Tmp), 6) = Res(.Item(Tmp), 6) + Arr(i, 6)
Res(.Item(Tmp), 7) = Res(.Item(Tmp), 7) + Arr(i, 7)

Thành
[GPECODE=vb]
index = .Item(Tmp)
Res(index, 3) = Res(index, 3) + Arr(i, 3)
Res(index, 6) = Res(index, 6) + Arr(i, 6)
Res(index, 7) = Res(index, 7) + Arr(i, 7)
[/GPECODE]

@ Quanghai, dnh46 kiểm tra lại xem phải không?

 

[VetMini]

Trên nguyên tắc, code 2 phải hiệu quả hơn code 1 bởi vì nó chỉ tính câu lệnh
Tmp = Arr(i, 1) & "#" & Arr(i, 2) & "#" & Arr(i, 4) & "#" & Arr(i, 5)
một lượt thôi

Theo thiển ý, nếu code 2 có bị hơi khó khăn với số dòng nhỏ thì là do cái vòng lặp con (để tính chỉ số thứ 2) chứ không phải do IF-ELSE

Tuy nhiên, tôi không học cùng trường, tức là không cùng trường phái, với các bạn cho nên không hiểu lắm các codes trên; và khả năng tôi hiểu sai cũng khá cao. Nhất là cách đo thời gian chạy code.

 

[Quang_Hải]

Kiểm tra với dữ liệu 100.000 dòng cách đo chỉ mở file excel mở Task Manager chờ CPU, Gram chạy ổn định (dự vào code và cách đo của a.Tuân) thì cách của a Quanghai nhanh hơn dnh46, với cách đo này với dữ liệu trước là 800 dòng mình thấy cách dnh46 lại nhanh hơn, không biết trong nội bộ code chạy thế nào vì anh em mình chỉ là dân nghiệp dư. Nhưng có thể chỉ đoạn code của a Quanghai và dnh46 lại như thế nào thì cả dữ liệu ít và nhiều rất điều nhanh

Thành
[GPECODE=vb]
index = .Item(Tmp)
Res(index, 3) = Res(index, 3) + Arr(i, 3)
Res(index, 6) = Res(index, 6) + Arr(i, 6)
Res(index, 7) = Res(index, 7) + Arr(i, 7)
[/GPECODE]

@ Quanghai, dnh46 kiểm tra lại xem phải không?

Đúng là thêm 1 biến để lấy chỉ số thì tốc độ nhanh hơn rõ rệt, và mình thử khai báo 2 biến mảng rõ ràng, không phải biến Variant thì lại nhanh hơn được xíu nữa. Vậy cũng rút ra được thêm kinh nghiệm khi code những bài tương tự.