Giải pháp tìm duy nhất để thay thế DIC( cái này để học hành và tư duy thôi)
Nếu bạn nào muốn tìm hiểu thuật toán ở dạng đơn sơ nhất không sử dụng hàm trong VBA thì có thể tham khảo bài này tôi không bẫy bất cứ lỗi gì nên code cũng ngắn gọn, tốc độ xem ra cũng được so với DIC (kém chừng 5-6s so với dữ liệu 60000 dòng)
Mã:
Sub DIC_01()
Dim Rng As Range
Dim Arrd()
Dim Arrn()
Dim DongCuoi As Long
Dim i As Long, j As Long
Dim DongHienTai As Long
DongCuoi = Sheet1.Range("A150000").End(xlUp).Row
Arrn = Sheet1.Range("A2:A" & DongCuoi)
ReDim Arrd(1 To DongCuoi, 1 To 1)
Arrd(1, 1) = Arrn(1, 1)
DongHienTai = 1
flag = True
For i = 1 To UBound(Arrn, 1)
For j = 1 To DongHienTai
If (Arrn(i, 1) = Arrd(j, 1)) Then
flag = False
Exit For
End If
Next j
If (flag = True) Then
DongHienTai = DongHienTai + 1
Arrd(DongHienTai, 1) = Arrn(i, 1)
End If
flag = True
Next
Sheet1.Range("J2:Z10000").Clear
Sheet1.Range("J2").Resize(DongHienTai, 1) = Arrd
End Sub
Với file trên, tôi test thấy tốc độ code không dùng dic cho thời gian khoảng 8s
Tuy nhiên Phi test vậy chưa được đâu! Hãy gõ số thứ tự 1, 2, 3... vào toàn bộ dữ liệu (tức không cho cell nào trùng cả)... rồi test lại code xem thời gian bao nhiêu?
Ngoài ra, code dùng dic Phi viết vậy cũng chưa tối ưu đâu (vì vẫn dùng Range chứ chưa hoàn toàn dùng Array)
Bài tập này dành cho thực hành vòng lặp, thời gian như vậy cũng xem như khá khá rồi, trong thực tế thì không có nhiều trường hợp không trùng nhau như vậy đâu, (bài này em chạy 60000 dòng không trùng thì code chạy 2 phút thua xa dùng dic nhiều), nhưng những dữ liệu nhỏ nhỏ có thể áp dụng.
Trước kia chưa biết gì VBA áp dụng công thức, có nhiều khi áp dụng công thức mảng chỉ có 5000 dòng mà mỗi lần chạy là em phát sợ 10 đến 15 phút, trong 1 file mà nhiều công thức lằng nhằng, ngại thêm mấy ông nội sumproduct file chậm cực kỳ, nên em nghĩ dùng VBA như vậy cũng tối ưu rồi đỡ mất khoảng thời gian nhiều lắm như vậy cũng làm toại nguyện nhiều người rồi, nhất là những người chưa biết gì vba. (còn ai đã rành đường lối rồi thì tự nhiên người ta sẽ tìm cách nào đó cao siêu hơn để giải quyết vấn đề lòng tham của con người là vô đáy, tối ưu còn muốn tối ưu hơn).
nhật kí thử nghiệm DIC01_Mang.xlsb trên máy Cỏ i5 Ram 4GB
để công bằng ta chạy Sub này trước
Mã:
Public Sub randArr()
Dim arr(1 To 200000, 1 To 1) As Variant, r As Long, sourceSTR As String, c As Integer, tempStr As String
sourceSTR = "0123456789AaBbCcDdEeFfGgHhIiJjKkLlMmNnOoPpQqRrSsTtUuVvWwXxYyZz"
Randomize
For r = 1 To 200000 Step 1
tempStr = Space(10)
For c = 1 To 10 Step 1
Mid(tempStr, c, 1) = Mid(sourceSTR, Int(62 * Rnd) + 1, 1)
Next
arr(r, 1) = tempStr
Next
Sheet1.Range("A2:A200000").Value = arr
End Sub
đếm thời gian dùng hàm
Mã:
Public Declare Function GetTickCount Lib "kernel32.dll" () As Long
sử dụng Dic kết quả thời gian : 3796 ( 3.8 giây )
sử dụng mảng kết quả : treo máy .....
hạ cột A xuống còn 100 000 dòng
sử dụng Dic thời gian : 1922 ( 2 giây )
sử dụng mảng thời gian : 333 250 ( 6 phút )
ví dụ 1 phần thôi là lấy ra duy nhât, chứ không phải so sánh hết, vì Dic là 1 đối tượng làm sao so sánh được chứ, mình chỉ so sánh 1 phương thức nhỏ của Dic thôi
Liên quan đến lọc duy nhất Bác Bill cho cái Dic, AF và Array ...mà cái Dic là cho tốc độ hay nhất thì cứ lôi ra mà xài vậy cho sướng đi ... hai cái kia đâu bằng dic mà suy nghĩ
Muốn nói về thuật toán thì phải giải thích thuật toán và cho biết tại sao mình nghĩ như thế là tối ưu rồi.
Phải cho người xem cơ hội tìm một thuật toán hay hơn chứ (đương nhiên phải giả sử hệ thống không cung cấp Dictionary)
Tự dưng viết một đống code rồi chạy thử. Ai biết nó làm cái gì, dựa trên lý thuyết nào mà "học hành và tư duy"
Giải pháp tìm duy nhất để thay thế DIC( cái này để học hành và tư duy thôi)
Nếu bạn nào muốn tìm hiểu thuật toán ở dạng đơn sơ nhất không sử dụng hàm trong VBA thì có thể tham khảo bài này tôi không bẫy bất cứ lỗi gì nên code cũng ngắn gọn, tốc độ xem ra cũng được so với DIC (kém chừng 5-6s so với dữ liệu 60000 dòng)
Mã:
Sub DIC_01()
Dim Rng As Range
Dim Arrd()
Dim Arrn()
Dim DongCuoi As Long
Dim i As Long, j As Long
Dim DongHienTai As Long
DongCuoi = Sheet1.Range("A150000").End(xlUp).Row
Arrn = Sheet1.Range("A2:A" & DongCuoi)
ReDim Arrd(1 To DongCuoi, 1 To 1)
Arrd(1, 1) = Arrn(1, 1)
DongHienTai = 1
flag = True
For i = 1 To UBound(Arrn, 1)
For j = 1 To DongHienTai
If (Arrn(i, 1) = Arrd(j, 1)) Then
flag = False
Exit For
End If
Next j
If (flag = True) Then
DongHienTai = DongHienTai + 1
Arrd(DongHienTai, 1) = Arrn(i, 1)
End If
flag = True
Next
Sheet1.Range("J2:Z10000").Clear
Sheet1.Range("J2").Resize(DongHienTai, 1) = Arrd
End Sub
bài này là tôi muốn hướng dẫn học viên thực hành lặp thôi, tôi biết là nó không xử lại ADO, chủ yếu tìm ra thuật toán, và lấy ví dụ điển hình là lấy ra duy nhất
bài này là tôi muốn hướng dẫn học viên thực hành lặp thôi, tôi biết là nó không xử lại ADO, chủ yếu tìm ra thuật toán, và lấy ví dụ điển hình là lấy ra duy nhất
Nếu muốn hướng dẫn cho học viên thì phải từ cái đơn giản rồi mới đến cái phức tạp. Tự nhiên bạn đưa ra 1 đống code vậy làm sao những người mới như mình tiêu hóa cho nổi.
ADO có cái khuyết điểm của nó: sử dụng nhiều lần trên file đang mở sẽ bị treo máy.
Vả lại ADO là loại code lười. Sau khi sưu tập xong mấy cái hàm chuyên kết nối bỏ vào thư viện rồi thì chỉ việc viết một câu lênh SQL và truy cập thôi.
bài này là tôi muốn hướng dẫn học viên thực hành lặp thôi, tôi biết là nó không xử lại ADO, chủ yếu tìm ra thuật toán, và lấy ví dụ điển hình là lấy ra duy nhất
Code vòng lặp trên chưa hoàn thiện. Viết như thế là chưa nắm vững cách hoạt động của vòng lăp FOR trong VBA.
Điểm quan trọng: vòng lặp FOR chấm dứt bất thường khi gặp lệnh Exit For; chấm dứt bình thường khi biến số vòng lặp lớn hơn điểm cuối.
Vì vậy, trong code trên, biến flag không cần thiết, và biến DongHienTai có thể bắt đầu ở 0 (thay vì 1). Muốn biết vòng lặp có tìm được trị hay không thì xét j > DongHienTai
lời thầy quả thú vị . nhưng trong 10 người đọc đoạn code liệu đếm được bao nhiêu người hiểu tại sao xét j > DongHienTai trong khi code mẫu trên mạng về VBA và nhiều ngôn ngữ lập trình khác gặp trường hợp này hầu như ai cũng sử dụng thêm biến flag ?
theo em đó là để tạo sự trong sáng dễ đọc để đủ 10 người code phổ thông có thể đọc hiểu được là vòng lặp trước đã bị break .
lời thầy quả thú vị . nhưng trong 10 người đọc đoạn code liệu đếm được bao nhiêu người hiểu tại sao xét j > DongHienTai trong khi code mẫu trên mạng về VBA và nhiều ngôn ngữ lập trình khác gặp trường hợp này hầu như ai cũng sử dụng thêm biến flag ?
theo em đó là để tạo sự trong sáng dễ đọc để đủ 10 người code phổ thông có thể đọc hiểu được là vòng lặp trước đã bị break .
Tôi nghĩ cách a.Phi không phải là không hay nhưng khi dùng dic ta quên mất nếu không dùng dic thì làm sao lấy duy nhất, có thể dùng công cụ có sẵn của Excel là AF, hoặc Remove dup vì 2 cách đó rất nhanh, ta cũng không nên ném đá quá nhiều với chủ đề này, mình xin góp ý nhỏ để code trên có thể nhanh hơn 1 chút
[GPECODE=vb]
Sub Khong_dung_dic()
Dim arr(), i As Long, Rarr(1 To 100000, 1 To 1)
Dim j As Long, m As Long
Dim t As Double
t = Timer
arr = Range([A2], [A65000].End(xlUp)).Resize(, 3).Value
m = 0
Rarr(1, 1) = arr(1, 1)
For i = 2 To UBound(arr)
For j = 1 To m
If arr(i, 1) = Rarr(j, 1) Then
Exit For
End If
Next j
If j > m Then
m = m + 1
Rarr(m, 1) = arr(i, 1)
End If
Next
[H2:H10000].Clear
[H2].Resize(m, 1) = Rarr
[G1] = Timer - t
End Sub
[/GPECODE]
Theo mình, bạn Phi đã nói rõ mục đích khi viết bài này rồi, không bàn về nhanh chậm nữa, nếu dùng 2 vòng lặp như trong bài thì:
1) Không cần biến flag
Bài của bạn Hùng
1) Có thể không dùng Exit For, dùng cái khác (sẽ bớt công đoạn "If j > m Then" nếu còn thích dùng)
2) Có thể không cần công đoạn "If j > m Then"
Nói .....đại thôi, bi giờ đi ủng hộ U23 Việt Nam cái đã
Việt Nam thắng 4-0
Hihi
- Dùng SELECT DISTINCT bằng ADO thì kết quả trả về có thể bị sort, đây có thể không phải điều tác giả mong muốn.
- Ngày trước học Pascal, các thầy có dạy tránh dùng Goto vì phá vỡ cấu trúc của chương trình, nhưng lại dùng exit thoải mái. Code ở dưới dùng Goto thay Exit for để không cần so sánh If j>m. Tuy vậy tốc độ cải thiện không đáng kể, vẫn là 10s với máy core i5 ram 4G; nguyên nhân tăng tốc ở code này ( còn 2s) do dữ liệu đề bài có thể đã sort nên tìm từ dưới lên sẽ nhanh thoát vòng lặp hơn.
Mã:
Sub FilterUnSortedRange()
Dim ArrN(), ArrD(), i&, j&, DongHienTai&, n&, t
t = Timer
n = Range("A" & Columns(1).Rows.Count).End(xlUp).Row
ArrN = Range("A2:A" & n).Value2
ReDim ArrD(1 To n - 1, 1 To 1)
ArrD(1, 1) = ArrN(1, 1)
DongHienTai = 1
For i = 2 To n - 1
For j = DongHienTai To 1 Step -1
If ArrN(i, 1) = ArrD(j, 1) Then GoTo NextI
Next
DongHienTai = DongHienTai + 1
ArrD(DongHienTai, 1) = ArrN(i, 1)
NextI:
Next
Range("J2:J1000000").Clear
Range("J2:J" & DongHienTai + 1) = ArrD
MsgBox Timer - t
End Sub
- Dùng SELECT DISTINCT bằng ADO thì kết quả trả về có thể bị sort, đây có thể không phải điều tác giả mong muốn.
- Ngày trước học Pascal, các thầy có dạy tránh dùng Goto vì phá vỡ cấu trúc của chương trình, nhưng lại dùng exit thoải mái. Code ở dưới dùng Goto thay Exit for để không cần so sánh If j>m. Tuy vậy tốc độ cải thiện không đáng kể, vẫn là 10s với máy core i5 ram 4G; nguyên nhân tăng tốc ở code này ( còn 2s) do dữ liệu đề bài có thể đã sort nên tìm từ dưới lên sẽ nhanh thoát vòng lặp hơn.
Mình học viết code kiểu "giang hồ" trên GPE nên mình chẳng biết cái thằng Goto nó phá vỡ cấu trúc của chương trình là như thế nào, mình chỉ biết trong trường hợp này dùng Goto sẽ bớt việc cho code rất nhiều ( chắc cũng......đỡ hao xăng tí tẹo) vì không phải hỏi han "If Iếc" gì cả, quan trọng (theo mình) là mình kiểm soát tốt thằng Goto đừng cho nó "chạy bậy" là "con gà đen" thôi, mà trong bài có mỗi một thằng Goto thì ...."cóc" lo
Híc
lời thầy quả thú vị . nhưng trong 10 người đọc đoạn code liệu đếm được bao nhiêu người hiểu tại sao xét j > DongHienTai
trong khi code mẫu trên mạng về VBA và nhiều ngôn ngữ lập trình khác gặp trường hợp này hầu như ai cũng sử dụng thêm biến flag ?
theo em đó là để tạo sự trong sáng dễ đọc để đủ 10 người code phổ thông có thể đọc hiểu được là vòng lặp trước đã bị break .
Chủ đề này có nói “hướng dẫn học viên về vòng lặp”. Nếu không có câu này thì nó chỉ là code viết chơi giải sầu, tôi chả buồn xía vào cho mệt xác.
Tôi chỉ thừa dịp chỉ dẫn cho các bạn còn chưa thấu suốt về cách sử dụng vòng lặp. (nếu bạn cho rằng tôi ngạo mạn cũng không hẳn là sai. Tất cả những thành viên lão thành ở đây dều biết tôi có tánh ngạo mạn)
10 người không có mấy người hiểu là vì đây là trường hợp it gặp. Nhưng gặp mà nhận ra thì mới gọi là nhuyễn tay nghề.
Tôi không thấy tận mắt những cái “code mẫu trên mạng” và “nhiều ngôn ngữ lập trình khác” kia nên không thể xét việc “hầu như ai cũng sử dụng thêm biến flag”. Nếu là học trò tôi thì xét biến duyệt vòng lặp là điều không thể thiếu sót.
Riêng về vấn đề trong sáng: cái từ flag chính nó đâu có trong sáng? Tại bạn quen dùng nó cho nên nghĩ vậy thôi. Đối với tôi, i, j, flag đâu có trong sáng!
Nếu muốn trong sáng thì phải đặt tên các biến duyệt vòng lặp theo đúng nhiệm vụ của chúng. Đáng lẽ phải gọi j là doTim, và flag là timThay chẳng hạn? Tối thiểu cũng nên tránh cặp i, j bởi vì theo thông lệ i và j là cặp dùng để duyệt dòng và cột. (trường hợp duyệt 2 mảng, người có kinh nghiệm sẽ dùng i1 và i2)
Ở đây tôi muốn nhấn mạnh về kỹ thuật duyệt vòng lặp cho nên tôi chỉ nói sơ về sự “trong sáng” thế thôi, không thêm nữa.
...
- Ngày trước học Pascal, các thầy có dạy tránh dùng Goto vì phá vỡ cấu trúc của chương trình, nhưng lại dùng exit thoải mái. Code ở dưới dùng Goto thay Exit for để không cần so sánh If j>m. Tuy vậy tốc độ cải thiện không đáng kể, vẫn là 10s với máy core i5 ram 4G; nguyên nhân tăng tốc ở code này ( còn 2s) do dữ liệu đề bài có thể đã sort nên tìm từ dưới lên sẽ nhanh thoát vòng lặp hơn.
Mã:
Sub FilterUnSortedRange()
Dim ArrN(), ArrD(), i&, j&, DongHienTai&, n&, t
t = Timer
n = Range("A" & Columns(1).Rows.Count).End(xlUp).Row
ArrN = Range("A2:A" & n).Value2
ReDim ArrD(1 To n - 1, 1 To 1)
ArrD(1, 1) = ArrN(1, 1)
DongHienTai = 1
For i = 2 To n - 1
For j = DongHienTai To 1 Step -1
If ArrN(i, 1) = ArrD(j, 1) Then GoTo NextI
Next
DongHienTai = DongHienTai + 1
ArrD(DongHienTai, 1) = ArrN(i, 1)
NextI:
Next
Range("J2:J1000000").Clear
Range("J2:J" & DongHienTai + 1) = ArrD
MsgBox Timer - t
End Sub
Tôi có từng viết bài nói về dùng Goto rồi. Biết cách dùng thì rất hiệu nghiệm mà chả có gì nguy hiểm cả.
Tuy nhiên, Goto chỉ cải thiện được vài phần tỷ giây cho mỗi lần lặp, cho nên bạn không thấy đnags kể là phải rồi.
Điểm này mới quan trọng: Code của bạn có một điểm mà tôi đã từng nhắc nhở chủ thớt “cho biết tại sao chủ thớt nghĩ rằng giải thuật của mình tối ưu”. Tuy nhiên chủ thớt cố tình lờ.
Khi duyệt mảng kết quả từ dưới lên trên, code có thể cải thiện được tốc độ nếu dữ liệu thường hay dính cụm (hay đi liền nhau). Nếu ta có vài mã “ABC” đi liền nhau thì lúc tìm lần thứ 2 sẽ thấy ngay trong mảng kết quả, không cần phải đi sâu.
Trên nguyên tắc, đo hiệu quả tốc độ của một giải thuật phải đi kèm với diều kiện dữ liệu. Giải thuật nào cũng có vùng mượt (sweet spot, vùng mà nó chạy êm nhất) của nó. Giải thuật tối ưu là giải thuật mà vùng mượt của nó bao được hầu hết các tình trạng thường gặp.
Tôi nghĩ cách a.Phi không phải là không hay nhưng khi dùng dic ta quên mất nếu không dùng dic thì làm sao lấy duy nhất, có thể dùng công cụ có sẵn của Excel là AF, hoặc Remove dup vì 2 cách đó rất nhanh, ta cũng không nên ném đá quá nhiều với chủ đề này, ...
Ở đây không phải nói chuyện ném đá. Chủ thớt nói chuyện “hướng dẫn về vòng lặp”, và sẵn dịp đây là trường hợp đăc biệt của vòng lặp cho nên tôi thêm ý kiến.
