Tăng tốc VBA bằng mảng: Use 1 Array, Use 2 Arrays, Use 3 Arrays

Liên hệ QC
Cá ngừ F1

Cá ngừ F1

( ͡° ͜ʖ ͡°)
Thành viên BQT
Moderator
Tham gia
1/1/08
Bài viết
2,579
Được thích
3,715
Donate (Momo)
Donate
Giới tính
Nam
Nghề nghiệp
Quan hệ.. và quan hệ..
Xin chào các anh/chị trên GPE,

- Căn cứ Topic "Hội thảo VBA trình độ 2: cho người đã học cơ bản"
- Theo đoạn Video tại bài #6 của sư phụ PTM

[video=youtube;aQtQiL2zVnE]https://www.youtube.com/watch?v=aQtQiL2zVnE&feature=youtu.be[/video]​

Em thấy có Use 1 Array, Use 2 Arrays, Use 3 Arrays: trong bài toán tính Doanh thu.
Quả thật là em chưa rõ về khái niệm này lắm: Dùng 1 mảng, 2 mảng, 3 mảng.
Em có làm 1 file tương tự, có sử dụng Code:

Mã: 
Sub DoanhThu()


Dim i&, Arr(), KQ(), T As Double


T = Timer


Arr = Range(Sheet1.[B2], Sheet1.[C1000000].End(3))


    ReDim KQ(1 To UBound(Arr), 1 To 1)


    For i = 1 To UBound(Arr)


        KQ(i, 1) = Arr(i, 1) * Arr(i, 2)


    Next i


    Sheet1.[D2].Resize(i - 1, 1) = KQ


    MsgBox "SoDong " & i & " ThoiGian: " & Timer - T




End Sub

Em cũng ko biết code đó: Dùng 1 mảng, 2 mảng, 3 mảng...???

Vậy, mong anh/chị trên GPE chia sẻ những bài toán thực tế có thể áp dụng & kinh nghiệm về Dùng 1 mảng, 2 mảng, 3 mảng... để tăng tốc độ code

Em xin cảm ơn!
 

File đính kèm

  • TangTocBangMang.xlsb
    662 KB · Đọc: 34
Sắp xếp theo thời gian sắp xếp theo bầu chọn
tờ rung đã viết:
Đây là code vận dụng tối đa mảng của thầy,nhưng thầy khuyên là không nên dùng cách này vì không có tính tượng hình để mà sau này lỡ có sữa code thì khó hình dung lắm...bài này chỉ dùng 1 mảng duy nhất là ra kết quả !

For i = 1 To UBound(Arr, 1)
Nhấp chuột vào đây để mở rộng...
Còn chỗ này nữa: Dùng 1 biến ghi nhận giá trị UBound, để khỏi tính UBound 1 tỷ lần
 
Upvote 0
ptm0412 đã viết:
Còn chỗ này nữa: Dùng 1 biến ghi nhận giá trị UBound, để khỏi tính UBound 1 tỷ lần
Nhấp chuột vào đây để mở rộng...
Câu này không đúng. Dòng lệnh đó chỉ thực thi 1 lần.
Chạy thử thủ tục này sẽ thấy:
PHP: 
Sub Test()
Dim Arr(), i&
ReDim Arr(1 To 2)
For i = 1 To UBound(Arr, 1)
    ReDim Arr(1 To 10)
    Debug.Print i
Next
End Sub
 
Upvote 0
huuthang_bd đã viết:
Câu này không đúng. Dòng lệnh đó chỉ thực thi 1 lần.
Nhấp chuột vào đây để mở rộng...
Nguyên lý làm việc của vòng lặp For Next là sau khi thực thi xong các câu lệnh bên trong, biến đếm i sẽ tăng 1 giá trị là step, sau đó so sánh với giá trị <To> để quyết dịnh chạy tiếp hay không. Giá trị <To> không phải là số, mà là UBound(), nên phải tính UBound() rồi mới so sánh được.

Câu lệnh Debug.Print i chỉ cho biết giá trị i trước khi vào next để tăng step, chứ không có ý nghĩa gì trong việc có tính UBound() hay không.
 
Lần chỉnh sửa cuối:
Upvote 0
ptm0412 đã viết:
Nguyên lý làm việc của vòng lặp For Next là sau khi thực thi xong các câu lệnh bên trong, biến đếm i sẽ tăng 1 giá trị là step, sau đó so sánh với giá trị <To> để quyết dịnh chạy tiếp hay không. Giá trị <To> không phải là số, mà là UBound(), nên phải tính UBound() rồi mới so sánh được.

Câu lệnh Debug.Print i chỉ cho biết giá trị i sau khi tăng step, chứ không có ý nghĩa gì trong việc có tính UBound() hay không.
Nhấp chuột vào đây để mở rộng...
Chưa chính xác, sau khi 1 câu lệnh được đặt ra, máy sẽ kiểm tra đầy đủ các điều kiện chưa, cấu trúc đúng chưa sau đó, nó sẽ tính trước, đại loại như thế này:

For i = 1 To UBound(Arr, 1)
....
Next

Giả sử UBound(Arr, 1)=100, Nó sẽ copy (biên dịch lại) vào một bộ nhớ tạm nào đó thành cái này:

For i = 1 To 100
....
Next

Rồi nó mới chạy.

Tôi luôn nghĩ nguyên tắc hoạt động của máy tính là như vậy.
 
Upvote 0
ptm0412 đã viết:
Hãy thử với vòng lặp 10 triệu lần.
Việc này đã được nói đến nhiều lần trước đây rồi.
Nhấp chuột vào đây để mở rộng...
Thử test với cái này:

Mã: 
Sub test()
    Dim i As Long
    Dim Arr(1 To 10000000)
    Dim T As Double
    T = Timer()
    For i = 1 To 10000000
    
    Next
    MsgBox Timer - T
    T = Timer()
    For i = 1 To UBound(Arr)
    
    Next
    MsgBox Timer - T
End Sub

Hình như thời gian như nhau hoặc nếu có chậm cũng chả đáng kể.
 
Upvote 0
Nếu tách riêng 2 thủ tục, tôi thấy Ubound đôi khi còn nhanh hơn cả em kia nữa!

Mã: 
Sub test()
    Dim i As Long
    Dim T As Double
    T = Timer()
    For i = 1 To 10000000
    
    Next
    MsgBox Timer - T
End Sub


Sub test1()
    Dim i As Long
    Dim Arr(1 To 10000000)
    Dim T As Double
    T = Timer()
    For i = 1 To UBound(Arr)
    
    Next
    MsgBox Timer - T
End Sub
 
Upvote 0
Tôi chỉ nói là mất công tính nhiều lần. Rõ ràng là VBA có tính lại vì:

Trong thủ tục của doveandrose:

Mã: 
Public Sub demLan()
Dim r As Integer
For r = 1 To return10(r) Step 1
    
Next
End Sub

Hoặc bất kỳ thí dụ nào mà tham số <to> được tính từ biến đếm đang tăng, nếu không tính lại thì sao biết để so sánh? Nên nói rằng "Nó sẽ copy (biên dịch lại) vào một bộ nhớ tạm nào đó" (trở thành giá trị cố định) là không đúng.

Thử 10 triệu lần mà không làm gì trong mỗi vòng lặp thì cũng chưa biết ra sao, vì UBound() tính nhanh quá. Nhưng thử dùng 1 phép tính khác:
Giả định là vùng dữ liệu 100.000 dòng và 100 cột có số liệu là 0 toàn bộ.

Chạy thử 2 thủ tục sau:

PHP: 
Sub Calc1()
Dim arr(1 To 100000, 1 To 100)
t = Timer
k = UBound(arr, 1): m = UBound(arr, 2)
For i = 1 To k
    For j = 1 To m
        arr(i, j) = 0
    Next
Next
'[A2].Resize(100000, 100) = arr'
[A1] = Timer - t

End Sub

Trước tiên chạy Calc1() 1 lần có dòng xanh xanh [A2].Resize(100000, 100) = arr để có số liệu. Sau đó vô hiệu dòng đó và So sánh với thủ tục sau:

PHP: 
Sub Calc2()
Dim arr(1 To 100000, 1 To 100)
t = Timer
For i = 1 To ([A150000].End(xlUp).Row - 1)
    For j = 1 To [XX2].End(xlToLeft).Column
        arr(i, j) = 0
    Next
Next

[B1] = Timer - t

End Sub
 
Upvote 0
ptm0412 đã viết:
Tôi chỉ nói là mất công tính nhiều lần. Rõ ràng là VBA có tính lại vì:

Trong thủ tục của doveandrose:

Mã: 
Public Sub demLan()
Dim r As Integer
For r = 1 To return10(r) Step 1
    
Next
End Sub

Hoặc bất kỳ thí dụ nào mà tham số <to> được tính từ biến đếm đang tăng, nếu không tính lại thì sao biết để so sánh? Nên nói rằng "Nó sẽ copy (biên dịch lại) vào một bộ nhớ tạm nào đó" (trở thành giá trị cố định) là không đúng.

Thử 10 triệu lần mà không làm gì trong mỗi vòng lặp thì cũng chưa biết ra sao, vì UBound() tính nhanh quá. Nhưng thử dùng 1 phép tính khác:
Giả định là vùng dữ liệu 100.000 dòng và 100 cột có số liệu là 0 toàn bộ.

Chạy thử 2 thủ tục sau:

PHP: 
Sub Calc1()
Dim arr(1 To 100000, 1 To 100)
t = Timer
k = UBound(arr, 1): m = UBound(arr, 2)
For i = 1 To k
    For j = 1 To m
        arr(i, j) = 0
    Next
Next
'[A2].Resize(100000, 100) = arr'
[A1] = Timer - t

End Sub

Trước tiên chạy Calc1() 1 lần có dòng xanh xanh [A2].Resize(100000, 100) = arr để có số liệu. Sau đó vô hiệu dòng đó và So sánh với thủ tục sau:

PHP: 
Sub Calc2()
Dim arr(1 To 100000, 1 To 100)
t = Timer
For i = 1 To ([A150000].End(xlUp).Row - 1)
    For j = 1 To [XX2].End(xlToLeft).Column
        arr(i, j) = 0
    Next
Next

[B1] = Timer - t

End Sub
Nhấp chuột vào đây để mở rộng...

Trời ơi, so thì phải so như thế này chứ?

Mã: 
Sub Calc1()
Dim arr(1 To 100000, 1 To 100)
T = Timer
k = UBound(arr, 1): m = UBound(arr, 2)
For i = 1 To k
    For j = 1 To m
        arr(i, j) = 0
    Next
Next
[A1] = Timer - T
End Sub


Sub Calc2()
Dim arr(1 To 100000, 1 To 100)
T = Timer
For i = 1 To UBound(arr, 1)
    For j = 1 To UBound(arr, 2)
        arr(i, j) = 0
    Next
Next
[A2] = Timer - T
End Sub

Sub Calc3()
Dim arr(1 To 100000, 1 To 100)
T = Timer
For i = 1 To 100000
    For j = 1 To 100
        arr(i, j) = 0
    Next
Next
[A3] = Timer - T
End Sub

Chạy ô tô cũng đến, chạy xe đạp cũng đến, sao lấy ô tô lại so vận tốc của xe đạp?
 
Lần chỉnh sửa cuối:
Upvote 0
Hoàng Trọng Nghĩa đã viết:
Trời ơi, so thì phải so như thế này chứ?


Chạy ô tô cũng đến, chạy xe đạp cũng đến, sao lấy ô tô lại so vận tốc của xe đạp?
Nhấp chuột vào đây để mở rộng...
Hai thủ tục vừa rồi có chứng minh được rằng UBound() chỉ tính 1 lần không tính lại không? Tôi không so tốc độ để hơn thua, tôi chỉ chứng minh rằng nếu <to> phải tính toán, mà không dùng biến ghi nhận giá trị <to>, thì <to> phải tính nhiều lần. Tính nhanh cũng phải tính nhiều lần. Nếu Nghĩa vẫn không thấy vấn đề, không nhớ mình đã nói những gì và phải chứng minh cái gì, thì tôi không viết thêm gì nữa. Sẵn đầu gối trước mặt tôi nói chuyện với nó.
 
Lần chỉnh sửa cuối:
Upvote 0
ptm0412 đã viết:
Hai thủ tục vừa rồi có chứng minh được rằng UBound() chỉ tính 1 lần không tính lại không? Tôi không so tốc độ để hơn thua, tôi chỉ chứng minh rằng nếu <to> phải tính toán, mà không dùng biến ghi nhận giá trị <to>, thì <to> phải tính nhiều lần. Tính nhanh cũng phải tính nhiều lần. Nếu Nghĩa vẫn không thấy vấn đề, không nhớ mình đã nói những gì và phải chứng minh cái gì, thì tôi không viết thêm gì nữa. Sẵn đầu gối trước mặt tôi nói chuyện với nó.
Nhấp chuột vào đây để mở rộng...
Đã từng trải nghiệm khi sử dụng gì đó trên Range đều chậm mà, muốn thử thì thử Range với Range chứ ai nào lại thử Ubound với Range chứ!+-+-+-+
 
Upvote 0
Hai thủ tục của tôi xài trên range thật. Kết quả 1 thủ tục 0.7 giây, 1 thủ tục 9.5 giây. Chả lẽ tính xlUp.Row 1 lần cộng với xlToLeft.Column 1 lần, mất 7.8 giây?

Vậy thì sửa thủ tục Calc1()

Mã: 
[COLOR=#0000cd]k = ([A150000].End(xlUp).Row - 1): m = [XX2].End(xlToLeft).Column[/COLOR]
For i = 1 To k
    For j = 1 To m
Nhớ là sửa sau khi đã có số liệu 10 triệu con số 0
 
Lần chỉnh sửa cuối:
Upvote 0
Nói túm lại là For đầu nó chỉ tính 1 lần, các For trong được tính theo số lần lặp lại. Cho nên làm gì thì làm bên trong For nếu xử lý được dữ liệu càng cố định càng tốt, bớt tính toán trong For là ổn.

Hai thủ tục dưới đây đều ngang thời gian nhau, như vậy vòng lặp đầu, For chỉ tính có 1 lần (không phải tính 1 trăm ngàn lần như Lão Chết Tiệt tưởng):

Mã: 
Sub Calc4()
Dim arr(1 To 100000, 1 To 100)
T = Timer
For i = 1 To ([b150000].End(xlUp).Row - 1)
    For j = 1 To [XX2].End(xlToLeft).Column - 1
        arr(i, j) = 0
    Next
Next
[a1] = Timer - T
End Sub


Sub Calc6()
Dim arr(1 To 100000, 1 To 100)
T = Timer
k = ([b150000].End(xlUp).Row - 1)
For i = 1 To k
    For j = 1 To [XX2].End(xlToLeft).Column - 1
        arr(i, j) = 0
    Next
Next
[a3] = Timer - T
End Sub
 
Upvote 0
Ban đầu thì Nghĩa nói thế này:
Giả sử UBound(Arr, 1)=100, Nó sẽ copy (biên dịch lại) vào một bộ nhớ tạm nào đó thành cái này:

For i = 1 To 100
....
Next

Rồi nó mới chạy.

Tôi luôn nghĩ nguyên tắc hoạt động của máy tính là như vậy.
Nhấp chuột vào đây để mở rộng...

Bây giờ lại có nguyên tắc hoạt động for ngoài copy và for trong không copy. Lát nữa còn nguyên tắc gì nữa không?

Bài 33 tôi có edit về việc sửa Calc1()

Mã: 
[COLOR=#0000cd]k = ([A150000].End(xlUp).Row - 1): m = [XX2].End(xlToLeft).Column[/COLOR]
For i = 1 To k
    For j = 1 To m

Như vậy là đồng đều cả 2 thủ tục đều xài range. Bây giờ thấy lập luận "nhiều nguyên tắc hoạt động" nên tôi thôi. Lần này thôi thật.
 
Upvote 0
Hoàng Trọng Nghĩa đã viết:
Vậy bài này nói gì đây? Chạy 1 tỷ lần hay 1 lần ở vòng lặp đầu? Tưởng hoài!
Nhấp chuột vào đây để mở rộng...

Từ đầu tới giờ tôi chỉ chứng minh chính điều đó. Nếu không dùng biến ghi nhận giá trị <to>, trong khi <to> phải tính toán, thì sẽ phải tính nhiều lần. Tính nhanh, tính chậm, đều phải tính nhiều lần.

Câu nói đó đang nói về 1 thủ tục cụ thể nên tôi nói UBound. Và bây giờ tôi vẫn nói UBound vẫn phải tính nhiều lần. Nhất quán, không có 2, 3, 4 nguyên tắc ba hồi 1 ba hồi nhiều.

Nguyên tắc của tôi và tôi muốn những người nghe tôi hướng dẫn trong lớp VBA làm theo, là phải khai báo biến và dùng biến ghi giá trị <to> nếu như <to> phải tính toán, dù cho tính nhanh hay tính chậm. Nếu không, thì từ 0.7 giây biến thành 9.5 giây cho 10 triệu lần lặp.

Bài này tôi viết cho những người khác đọc, và nên làm theo.
 
Upvote 0
ptm0412 đã viết:
Từ đầu tới giờ tôi chỉ chứng minh chính điều đó. Nếu không dùng biến ghi nhận giá trị <to>, trong khi <to> phải tính toán, thì sẽ phải tính nhiều lần. Tính nhanh, tính chậm, đều phải tính nhiều lần.

Câu nói đó đang nói về 1 thủ tục cụ thể nên tôi nói UBound. Và bây giờ tôi vẫn nói UBound vẫn phải tính nhiều lần. Nhất quán, không có 2, 3, 4 nguyên tắc ba hồi 1 ba hồi nhiều.

Nguyên tắc của tôi và tôi muốn những người nghe tôi hướng dẫn trong lớp VBA làm theo, là phải khai báo biến và dùng biến ghi giá trị <to> nếu như <to> phải tính toán, dù cho tính nhanh hay tính chậm. Nếu không, thì từ 0.7 giây biến thành 9.5 giây cho 10 triệu lần lặp.

Bài này tôi viết cho những người khác đọc, và nên làm theo.
Nhấp chuột vào đây để mở rộng...
Túm lại, nếu vòng lặp For chỉ 1 vòng, có tính 1 tỷ lần không hay tính 1 lần? Chỉ vậy thôi.
 
Upvote 0
Hỏi thừa. Câu trả lời là câu đầu tiên trong bài trích dẫn của câu hỏi.
 
Upvote 0
ptm0412 đã viết:
Hỏi thừa. Câu trả lời là câu đầu tiên trong bài trích dẫn của câu hỏi.
Nhấp chuột vào đây để mở rộng...
bài #34 giải thích quá đúng về lý do 2 sub của thầy PTM có tốc độ khác nhau . Nhược bằng thầy PTM nhất định rằng mỗi vòng lặp nó sẽ tính toán lại biểu thức để so sánh biến lặp thì mời thầy dự đoán vòng lặp này sẽ chạy bao nhiêu lần khi cột A hoàn toàn trống trơn . thầy muốn ví dụ về Range thì em cũng xin dùng Range luôn
Mã: 
Public Sub hello()
Dim r As Integer
With Sheet3
    For r = 1 To .[A1000000].End(xlUp).Row Step 1
        .Range("A" & .[A1000000].End(xlUp).Row + 1).Value = r
    Next
End With
End Sub
 
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

LuuAnh980
Vấn đề viết tắt trong Diễn Đàn.
    • Thích
    • Cười
2
Trả lời
28
Đọc
269
Hoàng Tuấn 868
H
L
Tạo hàm Round cho nhiều dòng
Trả lời
14
Đọc
6K
KenJayce
K
N
Giải pháp nào để gỡ bỏ hàm Round (là hàm ngoài cùng) trong tất cả các ô trong bảng?
Trả lời
8
Đọc
4K
KenJayce
K
T
Tổng hợp từ Sheet1 qua Sheet2
Trả lời
7
Đọc
114
thaiquy7
T
ann266
Kế toán kho F&B
    • Cười
2
Trả lời
22
Đọc
249
le_vis
L
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