Các câu đố, bài tập nhằm ôn tập & bổ sung kiến thức căn bản VBA

Liên hệ QC
Hoàng Trọng Nghĩa

Hoàng Trọng Nghĩa

Chuyên gia GPE
Thành viên BQT
Moderator
Tham gia
17/8/08
Bài viết
8,613
Được thích
16,671
Giới tính
Nam
Với tinh thần chơi mà học, học mà chơi, nên tôi đã mở ra topic này, hy vọng các thành viên tham gia, nhất là các thành viên mới biết về VBA.

Sau đây là câu hỏi đầu tiên:

Câu hỏi 1: Bằng phương pháp nào nhanh nhất để tìm ra ô nào trong một cột chứa một điều kiện.

Tôi có 1 file Excel 2007, với cột A, từ A1 đến A1048576 đều có giá trị.

Bằng phương pháp nào nhanh nhất (dùng mảng, dùng For Each v.v...) để tìm ra ô nào trong cột A chứa chữ "Nghia", đồng thời với ô ở cột B tương ứng nhập giá trị "OK" vào đó?

Ví dụ tìm thấy trong ô A2 có giá trị là "Nghia" thì ô B2 nhập vào "OK".

Hiện tại, đáp án nhanh nhất mà tôi có được đã gửi mail riêng (nhằm ghi lại thời gian gửi, để tránh nói ăn gian).

Để tiện việc theo dõi các câu đố, các bài tập tôi đã tạo ra topic này các bạn click vào đây:

Các link của topic "Các câu đố, bài tập nhằm ôn tập & bổ sung kiến thức căn bản VBA"
 

File đính kèm

  • DoVuiCanBan.rar
    1.3 MB · Đọc: 618
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
quocphuoc88 đã viết:
E nãy giờ cũng ngồi ngẫm cách của Thầy Ba Tê, nãy nhìn vào thấy nó ngồ ngộ, giờ mới hiểu ra !
Hay quá !
Nhấp chuột vào đây để mở rộng...

Hay chỗ nào hè? Chỗ bắt uống ly không bia 1 triệu lần á?

Đùa thôi, gán giá trị cho mảng thì gán gì chẳng được, thêm, sửa, xóa, kết quả tính toán, ... thậm chi gán giá trị mảng.
 
Upvote 0
Hoàng Trọng Nghĩa đã viết:
Để mở rộng về mảng, chúng ta trao đổi một số kinh nghiệm một chút hen!

Ngoài việc xử lý mảng trực tiếp như bác Ba Tê làm thì tôi nói thêm về việc cải thiện tốc độ:

Mã: 
Private Sub ForArray()
    Dim i As Long, sArray(), sArr(1 To 1048576, 1 To 1)
[COLOR=#ff0000]    sArray = Sheet1.Range("A:A")[/COLOR]
    For i = 1 To 1048576
        If sArray(i, 1) = "Nghia" Then
            sArr(i, 1) = "OK"
        End If
    Next
    Sheet1.Range("B:B") = sArr
End Sub

Thông thường với dòng màu đỏ ở trên, các bạn có thói quen:

sArray = Sheet1.Range("A:A").Value

Đó không sai, nhưng lại làm chậm tốc độ gán dữ liệu lên mảng. Để tăng tốc, ta loại bỏ .Value thì sẽ nhanh hơn, các bạn thử nghiệm đi, đó là một trong những tiểu xảo tăng tốc đấy.

Dĩ nhiên với trường hợp Array, với bài toán đố này vẫn chưa phải là nhanh, các bạn tiếp tục tìm phương hướng khác xem sao!
Nhấp chuột vào đây để mở rộng...
Theo như a nói còn cách khác nhanh hơn thì e suy nghĩ là lúc tìm được chữ "Nghĩa" mình gán chữ "OK" vào mảng, rồi sau khi xong thì gán kết quả xuống, vậy giờ mình tìm dc rồi gán xuống sheet luôn( đỡ được 1 bước gán vào mảng ). Theo lối suy nghĩ này e sữa câu lệnh trong hàm if là cells(i,2)="OK"
Không biết e suy nghĩ thế được không ? Có gì anh chỉ bảo thêm !
 
Upvote 0
quocphuoc88 đã viết:
Theo như a nói còn cách khác nhanh hơn thì e suy nghĩ là lúc tìm được chữ "Nghĩa" mình gán chữ "OK" vào mảng, rồi sau khi xong thì gán kết quả xuống, vậy giờ mình tìm dc rồi gán xuống sheet luôn( đỡ được 1 bước gán vào mảng ). Theo lối suy nghĩ này e sữa câu lệnh trong hàm if là cells(i,2)="OK"
Không biết e suy nghĩ thế được không ? Có gì anh chỉ bảo thêm !
Nhấp chuột vào đây để mở rộng...
Bạn cứ thử đi, sai chỗ nào ta sửa chỗ đó, đây là chỗ để học, đừng ngần ngại đưa ra ý tưởng của mình! Chẳng hạn với For Each ... Next:

Mã: 
Private Sub ForEachRange()
[COLOR=#ff0000]    Dim t As Double[/COLOR]
[COLOR=#ff0000]    t = Timer[/COLOR]
    Dim Rng As Range
    
    For Each Rng In Sheet1.Range("A1:A1048576")
        If Rng = "Nghia" Then Rng.Offset(, 1) = "OK"
    Next
    
[COLOR=#ff0000]    Debug.Print Timer - t[/COLOR]
End Sub

Với các dòng màu đỏ là thủ tục để đo thời gian. Để biết nhanh chậm thế nào các bạn mở VBA (Alt+F11) và mở cửa sổ Immediate (Ctrl+G), sau khi chạy code các bạn sẽ thấy thời gian được đo tại cửa sổ đó.
 
Upvote 0
Hoàng Trọng Nghĩa đã viết:
Bạn cứ thử đi, sai chỗ nào ta sửa chỗ đó, đây là chỗ để học, đừng ngần ngại đưa ra ý tưởng của mình! Chẳng hạn với For Each ... Next:

Mã: 
Private Sub ForEachRange()
[COLOR=#ff0000]    Dim t As Double[/COLOR]
[COLOR=#ff0000]    t = Timer[/COLOR]
    Dim Rng As Range
    
    For Each Rng In Sheet1.Range("A1:A1048576")
        If Rng = "Nghia" Then Rng.Offset(, 1) = "OK"
    Next
    
[COLOR=#ff0000]    Debug.Print Timer - t[/COLOR]
End Sub

Với các dòng màu đỏ là thủ tục để đo thời gian. Để biết nhanh chậm thế nào các bạn mở VBA (Alt+F11) và mở cửa sổ Immediate (Ctrl+G), sau khi chạy code các bạn sẽ thấy thời gian được đo tại cửa sổ đó.
Nhấp chuột vào đây để mở rộng...
dạ, sau khi e thử chạy cả 3 code thì thời gian là
1. Dùng for each của a như trên là : 2,4765625 giây
2. Dùng mảng lúc đầu là : 1,6640625 giây
3. Dùng mảng theo lối suy nghĩ lúc sau : 0,78125 giây
 
Upvote 0
quocphuoc88 đã viết:
dạ, sau khi e thử chạy cả 3 code thì thời gian là
1. Dùng for each của a như trên là : 2,4765625 giây
2. Dùng mảng lúc đầu là : 1,6640625 giây
3. Dùng mảng theo lối suy nghĩ lúc sau : 0,78125 giây
Nhấp chuột vào đây để mở rộng...
Bạn gửi cái thủ tục ở mục 3 lên xem nào!
 
Upvote 0
Upvote 0
quocphuoc88 đã viết:
Dạ đây a !
Mã: 
Sub gpe2()
Dim i As Long, sarr(), t As Double
t = Timer
sarr = Sheet1.Range("a1:a1048576").Value
For i = 1 To UBound(sarr, 1)
     If sarr(i, 1) = "Nghia" Then
        Cells(i, 2) = "ok"
     End If
Next i
Debug.Print Timer - t
End Sub
Nhấp chuột vào đây để mở rộng...

Đúng thế! Đây là thủ tục nhanh nhất mà tôi biết đến! Vì ta kết hợp với mảng, đồng thời làm trực tiếp lên Cell sẽ cho thời gian nhanh hơn rất nhiều so với các thủ tục khác! Cám ơn bạn!
 
Upvote 0
Private Sub ForEachRange()
Dim t As Double
t = Timer
Dim Arr(), I As Long
Arr = Range("A:A").Value




For I = 1 To UBound(Arr)
If Arr(I, 1) = "Nghia" Then
Cells(I, 2) = "OK"
End If


Next I
Debug.Print Timer - t
End Sub



cái này máy tôi chạy 0.78 s
 
Lần chỉnh sửa cuối:
Upvote 0
Theo tôi thì có thể dùng 2 mảng 1 chiều hữu hiệu hơn 1 mảng 2 chiều.

Lúc cóp từ cột ra mảng thì cóp cột dò ra mảng A, và cột kết quả ra mảng B
Lúc dò thấy đạt thì cứ chiếu theo chỉ số mà ghi mảng B
Xong hết thì chép mảng B trở vào cột.

Nếu bạn nào thực sự muốn thử tốc độ code thì cũng cần biết rằng đây là một nhóm dữ liệu hơn triệu đơn vị. Test chưa hẳn đã đơn giản như thế.
Khi biện minh phép dò và ghi kết quả người ta có công thức riêng. Cách "rừng rú" thì cũng phải đặt ra nhiều trường hợp:
- Chả có ô nào đạt -> 0%
- Số ô đạt khoảng 5%
- Số ô đạt khoảng 10%
...
- Số ô đạt 95%, 100%
Sau đó vẽ biểu đồ thời gian theo số dữ liệu.
Và phải gồm một biện chứng trường hợp điển hình nhất là bao nhiêu phần trăm. Tìm ra điểm tối ưu (sweet spot) của code.

Ghi chú thêm: Nhìn sơ thì ta cũng có thể đoán là cách dùng mảng để đọc và ghi thẳng lên cells sẽ hữu hiệu hơn nếu con số ô cần ghi (số ô đạt) ở tỷ lệ thấp. Khi con số này lên càng cao thì công việc truy cập ô sẽ làm nó chậm đi.
 
Lần chỉnh sửa cuối:
Upvote 0
VetMini đã viết:
Theo tôi thì có thể dùng 2 mảng 1 chiều hữu hiệu hơn 1 mảng 2 chiều.

Lúc cóp từ cột ra mảng thì cóp cột dò ra mảng A, và cột kết quả ra mảng B
Lúc dò thấy đạt thì cứ chiếu theo chỉ số mà ghi mảng B
Xong hết thì chép mảng B trở vào cột.

Nếu bạn nào thực sự muốn thử tốc độ code thì cũng cần biết rằng đây là một nhóm dữ liệu hơn triệu đơn vị. Test chưa hẳn đã đơn giản như thế.
Khi biện minh phép dò và ghi kết quả người ta có công thức riêng. Cách "rừng rú" thì cũng phải đặt ra nhiều trường hợp:
- Chả có ô nào đạt -> 0%
- Số ô đạt khoảng 5%
- Số ô đạt khoảng 10%
...
- Số ô đạt 95%, 100%
Sau đó vẽ biểu đồ thời gian theo số dữ liệu.
Và phải gồm một biện chứng trường hợp điển hình nhất là bao nhiêu phần trăm. Tìm ra điểm tối ưu (sweet spot) của code.
Nhấp chuột vào đây để mở rộng...
Vậy bạn thử làm một thủ tục liên quan đến dòng màu đỏ cho bài toán này xem sao! Tôi rất muốn được học hỏi từ bạn.

Ghi chú thêm: Nhìn sơ thì ta cũng có thể đoán là cách dùng mảng để đọc và ghi thẳng lên cells sẽ hữu hiệu hơn nếu con số ô cần ghi ở tỷ lệ thấp. Khi con số này lên càng cao thì công việc truy cập ô sẽ làm nó chậm đi.
Nhấp chuột vào đây để mở rộng...

Đúng thế, tôi đang băn khoăn về cường độ lặp lại của giá trị đây! Đó là một điều khó khăn cho việc viết code hiệu quả!

Với giá trị 100% thì thủ tục dùng 2 mảng 2 chiều là hiệu quả:

Mã: 
Private Sub ForArray()
    Dim t As Double
    t = Timer
    Dim i As Long, sArray(), sArr(1 To 1048576, 1 To 1)
    sArray = Sheet1.Range("A:A")
    For i = 1 To 1048576
        If sArray(i, 1) = "Nghia" Then
            sArr(i, 1) = "OK"
        End If
    Next
    Sheet1.Range("B:B") = sArr
    Debug.Print Timer - t
End Sub

Nhưng tôi chưa biết phải dùng làm sao cho 2 mảng 1 chiều như bạn nói lại chạy nhanh hơn được!
 
Lần chỉnh sửa cuối:
Upvote 0
Private Sub ForEachRange()
Dim t As Double
t = Timer
Dim Arr(), I As Long
Arr = Range("A:A").Value
Dim Arr1(1048576)
For I = 1 To UBound(Arr)
Arr1(I) = Arr(I, 1)
Next

For I = 1 To UBound(Arr1)
If Arr1(I) = "Nghia" Then
Cells(I, 2) = "OK"
End If
Next
Debug.Print Timer - t
End Sub
tốc độ của đoạn code này cũng nhanh chừng 0.8s mà thôi
 
Upvote 0
Câu hỏi 2: Hai mảng một chiều Arr1 và Arr2 này có giống nhau không?

Mã: 
Public i As Long, Arr1(0 To 4) As Long, Arr2()


Sub CreatArr1()
    For i = 0 To 4
        [COLOR=#ff0000]Arr1[/COLOR](i) = i + 1
    Next
End Sub


Sub CreatArr2()
    [COLOR=#ff0000]Arr2 [/COLOR]= Array(1, 2, 3, 4, 5)
End Sub

Nếu không, tại sao sau khi chạy 2 code trên và kiểm tra tại code dưới đây tất cả các phần tử tương ứng nhau đều là TRUE?

Mã: 
Sub Checked()
    For i = 0 To 4
        MsgBox ([COLOR=#ff0000]Arr1[/COLOR](i) = [COLOR=#ff0000]Arr2[/COLOR](i)) & "/ " & IsNumeric([COLOR=#ff0000]Arr1[/COLOR](i)) & "/ " & IsNumeric([COLOR=#ff0000]Arr2[/COLOR](i))
    Next
End Sub
 
Lần chỉnh sửa cuối:
Upvote 0
phihndhsp đã viết:
Private Sub ForEachRange()
Dim t As Double
t = Timer
Dim Arr(), I As Long
Arr = Range("A:A").Value
Dim Arr1(1048576)
For I = 1 To UBound(Arr)
Arr1(I) = Arr(I, 1)
Next

For I = 1 To UBound(Arr1)
If Arr1(I) = "Nghia" Then
Cells(I, 2) = "OK"
End If
Next
Debug.Print Timer - t
End Sub
tốc độ của đoạn code này cũng nhanh chừng 0.8s mà thôi
Nhấp chuột vào đây để mở rộng...

Bạn vẫn chưa hiểu vấn đề, bạn thử gõ chữ "Nghia" ở ô A1 rồi bạn copy nó, sau đó bạn Paste cho cả cột A, rồi bạn chạy code sẽ cho thời gian là bao nhiêu?
 
Upvote 0
Hoàng Trọng Nghĩa đã viết:
Mã: 
Private i As Long, Arr1(0 To 4) As Long, Arr2()


Sub CreatArr1()
    For i = 0 To 4
        [COLOR=#ff0000]Arr1[/COLOR](i) = i + 1
    Next
End Sub


Sub CreatArr2()
    [COLOR=#ff0000]Arr2 [/COLOR]= Array(1, 2, 3, 4, 5)
End Sub

Nếu không, tại sao sau khi chạy 2 code trên và kiểm tra tại code dưới đây tất cả các phần tử tương ứng nhau đều là TRUE?

Mã: 
Sub Checked()
    For i = 0 To 4
        MsgBox [COLOR=#ff0000]Arr1[/COLOR](i) = [COLOR=#ff0000]Arr2[/COLOR](i)
    Next
End Sub
Nhấp chuột vào đây để mở rộng...

Khi gán xuống Range 2 mảng sẽ có kết quả:
1. Cùng có 5 phần tử: {1,2,3,4,5}
2. Cùng là mảng 1 chiều.

==> Xin trả lời là 2 mảng này giống nhau.

Không biết tôi hiểu vậy có đúng không nữa. Mong mọi người góp ý.

Xin cảm ơn.
 
Upvote 0
Hoàng Trọng Nghĩa đã viết:
Bạn vẫn chưa hiểu vấn đề, bạn thử gõ chữ "Nghia" ở ô A1 rồi bạn copy nó, sau đó bạn Paste cho cả cột A, rồi bạn chạy code sẽ cho thời gian là bao nhiêu?
Nhấp chuột vào đây để mở rộng...

Code này mà chạy với tất cả dòng là "Nghia" thì --> tốn thuốc lá --> treo máy luôn.

Sư phụ NDU nói đúng, xử lý ngay trên mảng và gán xuống Range 1 lần vẫn cho tốc độ nhanh hơn rất nhiều.
 
Upvote 0
mhung12005 đã viết:
Code này mà chạy với tất cả dòng là "Nghia" thì --> tốn thuốc lá --> treo máy luôn.

Sư phụ NDU nói đúng, xử lý ngay trên mảng và gán xuống Range 1 lần vẫn cho tốc độ nhanh hơn rất nhiều.
Nhấp chuột vào đây để mở rộng...


Cuối cùng, bài học rút ra ở đây là:

1) Xử lý mảng rồi gán xuống sheet 1 lần vẫn là tối ưu (cho dữ liệu thỏa điều kiện từ 20% trở lên)

Mã: 
Sub gpe()
    Dim i As Long, Arr(), sArr()
    sArr = Sheet1.Range("a1:a1048576").Value
    ReDim Arr(1 To UBound(sArr, 1), 1 To 1)
    For i = 1 To UBound(sArr, 1)
         If sArr(i, 1) = "Nghia" Then
            Arr(i, 1) = "Ok"
         End If
    Next i
    Sheet1.Range("b1").Resize(UBound(sArr, 1), 1).Value = Arr
End Sub

2) Dữ liệu thỏa điều kiện từ dưới 20% trở xuống ta kết hợp vừa mảng nguồn rồi gán xuống từng cell là thượng sách.

Mã: 
Private Sub gpe2()
    Dim i As Long, sArray(), sArr(1 To 1048576, 1 To 1)
    sArray = Sheet1.Range("A:A")
    For i = 1 To 1048576
        If sArray(i, 1) = "Nghia" Then
            Cells(i, 2) = "OK"
        End If
    Next
End Sub

3) Xử lý trực tiếp trên sheet luôn luôn cho thời gian rất chậm, nhưng nếu số hàng (row) ít thì OK.

Mã: 
Sub dotimten()
    Dim i As Long
    For i = 1 To 1048576
        If Cells(i, 1) = "Nghia" Then
            Cells(i, 2) = "OK"
        End If
    Next
End Sub


hoặc:


Private Sub ForEachRange()
    Dim Rng As Range
    
    For Each Rng In Sheet1.Range("A1:A1048576")
        If Rng = "Nghia" Then Rng.Offset(, 1) = "OK"
    Next
    
End Sub

Các bạn thành viên mới học VBA cần rút kinh nghiệm về các vấn đề này, để sau này định hướng mình sẽ làm gì trên dữ liệu của mình trước khi viết code nhé các bạn.
 
Lần chỉnh sửa cuối:
Upvote 0
Mời các bạn tiếp tục tham gia:

Câu hỏi 2: Hai mảng một chiều Arr1 và Arr2 này có giống nhau không?

mhung12005 đã viết:
Hoàng Trọng Nghĩa đã viết:
Mã: 
Public i As Long, Arr1(0 To 4) As Long, Arr2()


Sub CreatArr1()
    For i = 0 To 4
        [COLOR=#ff0000]Arr1[/COLOR](i) = i + 1
    Next
End Sub


Sub CreatArr2()
    [COLOR=#ff0000]Arr2 [/COLOR]= Array(1, 2, 3, 4, 5)
End Sub

Nếu không, tại sao sau khi chạy 2 code trên và kiểm tra tại code dưới đây tất cả các phần tử tương ứng nhau đều là TRUE?

Mã: 
Sub Checked()
    For i = 0 To 4
        MsgBox ([COLOR=#ff0000]Arr1[/COLOR](i) = [COLOR=#ff0000]Arr2[/COLOR](i)) & "/ " & IsNumeric([COLOR=#ff0000]Arr1[/COLOR](i)) & "/ " & IsNumeric([COLOR=#ff0000]Arr2[/COLOR](i))
    Next
End Sub
Nhấp chuột vào đây để mở rộng...

Khi gán xuống Range 2 mảng sẽ có kết quả:
1. Cùng có 5 phần tử: {1,2,3,4,5}
2. Cùng là mảng 1 chiều.

==> Xin trả lời là 2 mảng này giống nhau.

Không biết tôi hiểu vậy có đúng không nữa. Mong mọi người góp ý.

Xin cảm ơn.
Nhấp chuột vào đây để mở rộng...
 
Lần chỉnh sửa cuối:
Upvote 0
2 mảng 1 chiều:

Lúc đọc cột bình thường thì được mảng 2 chiều. Nhưng nếu lúc đọc cộng thêm hàm Application.Transpose thì có thể ép nó thành mảng 1 chiều.

mngA = Application.Transpose(Range("A1:A1000000").Value)
mngB = Application.Transpose(Range("B1:B1000000").Value)

Đương nhiên làm những cái này rất tốn nhiên liệu nên chỉ hữu dụng khi mảng rất lớn.

Chú thích thêm về chiều của mảng:

Lệnh Arr2chieu(i,j) sử dụng một con toán tính ra vị trí thứ "i*n+j" của mảng, cho nên theo lý thuyết nó sẽ chậm hơn Arr1chieu(i) chỉ sử dụng con toán tính vị trí thứ "i" thôi.
 
Lần chỉnh sửa cuối:
Upvote 0
VetMini đã viết:
2 mảng 1 chiều:

Lúc đọc cột bình thường thì được mảng 2 chiều. Nhưng nếu lúc đọc cộng thêm hàm Application.Transpose thì có thể ép nó thành mảng 1 chiều.

mngA = Application.Transpose(Range("A1:A1000000").Value)
mngB = Application.Transpose(Range("B1:B1000000").Value)

Đương nhiên làm những cái này rất tốn nhiên liệu nên chỉ hữu dụng khi mảng rất lớn.

Chú thích thêm về chiều của mảng:

Lệnh Arr2chieu(i,j) sử dụng một con toán tính ra vị trí thứ "i*n+j" của mảng, cho nên theo lý thuyết nó sẽ chậm hơn Arr1chieu(i) chỉ sử dụng con toán tính vị trí thứ "i" thôi.
Nhấp chuột vào đây để mở rộng...

Cái Application.Transpose này Thầy ndu96081631 đã nói đi nói lại rất rất nhiều lần rồi, nó xử lý rất tệ với dữ liệu lớn, nó còn chậm chạp hơn nhiều so với ta dùng 2 vòng lặp để chuyển chúng thành mảng 1 chiều từ mảng 2 chiều. Vậy sao bạn cứ dường như là "lạc hậu" so với vấn đề này nhỉ?
 
Lần chỉnh sửa cuối:
Upvote 0
Muốn nhanh hơn nữa có lẽ phải dùng Data Object. Cái này chờ cụ Hai Lúa thử xem. Hình như với số lượng này, DAO hữu hiệu hơn ADO một bậc.
 
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

hongquan93vn
Tìm công thức có thể đánh STT của giá trị mỗi lần xuất hiện
2
Trả lời
28
Đọc
499
ptm0412
ptm0412
T
  • Đã khóa
Tìm một bạn có kinh nghiệm về Excel
Trả lời
0
Đọc
1
tinhnhi1202
T
H
  • Question
Code VBA cộng tổng trên bảng CĐKT
Trả lời
4
Đọc
101
vak1590
vak1590
hongphuong1997
Tìm kiếm số âm dương trong chuỗi
Trả lời
3
Đọc
52
hongphuong1997
hongphuong1997
Q
Trả Lời google form bằng excel VBA
Trả lời
0
Đọc
38
quoc1995db
Q
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