Bài tập VBA: Tách kết quả ra nhiều sheet khi vượt số dòng của Excel

[ptm0412]

Tình huống:
Tôi có 1 bảng dữ liệu kiểm tra kết quả đạt/ không đạt theo 1 tiêu chí X nào đó của một số mặt hàng đang bán tại 1 số cửa hàng:
- Có mặt hàng cần lấy kết quả, có mặt hàng không cần. Số lượng mặt hàng cần lấy chưa biết trước, có 1 cột yes/ No (Y/N) cho biết cần lấy mặt hàng nào.
- Mỗi mặt hàng có cửa hàng đạt (Y), có cửa hàng không đạt (N)
- Cấu trúc dữ liệu là các mặt hàng theo dòng, các cửa hàng theo cột
- Dữ liệu hiện tại là 5000 mặt hàng và 500 cửa hàng, có thể nhiều hơn hoặc ít hơn tùy theo đợt kiểm tra (nghĩa là chưa biết trước)
- Giả định là phải viết code cho nhiều tiêu chí, giao cho nhiều người dùng tự thu thập dữ liệu, chạy và import kết quả.

Yêu cầu:
Chuyển dữ liệu cột thành dòng ra excel để import vào phần mềm khác (theo cấu trúc của sheet Result).

Vấn đề:
- Kết quả sau khi chuyển cột thành dòng sẽ có thể vượt quá số dòng của Excel (1 triệu dòng). Do phải dùng excel để import (giả sử phần mềm đó kỳ cục như vậy) nên không dùng Power query đưa vào data model, hoặc dùng code khác đưa ra csv, hoặc dùng Access/ công cụ khác.
- Chấp nhận tách kết quả ra nhiều sheet để import nhiều lần.
- Trong file đính kèm có code sẵn chuyển cột thành dòng, nhưng mới chạy khoảng 2500 dòng/ 5000 dòng mặt hàng đã bị tràn 1 triệu dòng kết quả

Nội dung bài tập
Câu 1. Nếu kết quả vượt quá 1 triệu dòng thì chia ra nhiều sheet, mỗi sheet 1 triệu dòng, sheet cuối là số thừa còn lại.
Câu 2. Giả sử máy của người dùng có thể yếu, tạo mảng kết quả bị lỗi tràn bộ nhớ, mức độ mạnh yếu cũng khác nhau. Nên thay vì 1 triệu, cần cho người dùng tùy chọn 1 con số nhỏ hơn (500 ngàn, 200 ngàn, thậm chí 50 ngàn dòng). Căn cứ vào con số người dùng chọn sẽ chia kết quả ra nhiều sheets hay ít sheets, mỗi sheet có số dòng là con số người dùng chọn. (nhiều sheet thì import nhiều lần, không sao cả)

Bài này dành cho các bạn đang học, đã vượt qua mức căn bản, đã biết về mảng.

 

[ptm0412]

em tìm ra lỗi rồi ạ. Sửa đoạn này:

Bạn thử bỏ hẳn If k <= limit (và end if của nó) xem.

 

[quanluu]

Bạn thử bỏ hẳn If k <= limit (và end if của nó) xem.

Vâng, Mình bỏ hẳn đi là được ạ, đọc lại em thấy cái này bị thừa, không cần thiết.

 

[ptm0412]

Chắc không còn ai tham gia nữa nên tôi đưa code của tôi lên. Trong đó:
1- Sử dụng thủ thuật như của quanluu (khi biến đếm dòng kết quả tăng lên đến con số chọn thì trả về 0 để tăng lại từ đầu), nhưng gói trong 1 thủ tục chính không có hàm/ sub con
2- Sử dụng sheet Result có sẵn, khi vượt số dòng mong muốn mới tạo thêm sheet, tên sheet là Result + đánh số để phân biệt với các tiêu chí kiểm tra khác (nếu có).
3- Dự phòng trường hợp chạy nhiều lần, nên viết 1 hàm kiểm tra sự tồn tại của sheet trước khi tạo mới.
4- Dự phòng lần chạy sau thay đổi số dòng nên có 1 thủ tục xóa kết quả của lần chạy trước, nhất là các sheet thừa của lần chạy sau nếu có. Nếu là xóa sheet thì không cần hàm (3)
5- Code có kiểm tra nếu cửa hàng không có mặt hàng bán thì bỏ qua (ô trống không Y không N)
6- Nếu kết quả không vượt, hoặc phần thừa ở sheet cuối thì đưa ra khỏi 2 vòng lặp rồi mới xét, không như code của quanluu là để giữa 2 vòng lặp. Nhờ vậy việc xét điều kiện đơn giản hơn là 4 điều kiện And với nhau
7- ReDim mảng kết quả có số dòng = Min(số dòng chọn, số dòng mặt hàng * số cột cửa hàng), vì nếu chỉ kiểm tra 10 mặt hàng thì Redim 5000 dòng chứ redim 1 triệu làm chi.

Function ShExist(ByVal ShName As String) As Boolean
ShExist = False
For Each sh In ThisWorkbook.Sheets
    If sh.Name = ShName Then ShExist = True: Exit For
Next
End Function
''___________________
Sub DelResult()
For Each sh In ThisWorkbook.Sheets
    If IsNumeric(Right(sh.Name, 1)) Then sh.Range("A2:G1000000").Clear
Next
End Sub

Code chính:

Sub RunIt()
Dim LastRw As Long, LastCol As Long
Dim Data1Arr(), StoreArr(), Data2Arr(), RArr()
Dim MaxRw As Long, Page As Long
Application.ScreenUpdating = False
t = Timer
MaxRw = Sheet2.[H1].Value
DelResult
With Sheet1
    LastRw = .Cells(50000, 2).End(xlUp).Row
    LastCol = .Cells(1, 1000).End(xlToLeft).Column
    Data1Arr = .Range("B2:F" & LastRw).Value
    StoreArr = .Range(.Cells(1, "G"), .Cells(1, LastCol)).Value
    Data2Arr = .Range(.Cells(2, "G"), .Cells(LastRw, LastCol)).Value
End With
ReDim RArr(1 To Application.Min(MaxRw, UBound(Data1Arr, 1) * UBound(Data2Arr, 2)), 1 To 7)
For i = 1 To UBound(Data1Arr, 1)
    If Data1Arr(i, 5) = "Y" Then
        For j = 1 To UBound(Data2Arr, 2)
            If Not IsEmpty(Data2Arr(i, j)) Then
                k = k + 1: p = p + 1
                RArr(k, 1) = p
                RArr(k, 2) = Data1Arr(i, 1)
                RArr(k, 3) = Data1Arr(i, 2)
                RArr(k, 4) = Data1Arr(i, 3)
                RArr(k, 5) = Data1Arr(i, 4)
                RArr(k, 6) = StoreArr(1, j)
                RArr(k, 7) = Data2Arr(i, j)
            End If
            If k = MaxRw Then
                Page = Page + 1
                If Page = 1 Then
                    Sheet2.Range("A2:G1000000").ClearContents
                    Sheet2.Cells(2, 1).Resize(k, 7).Value = RArr
                Else
                    If ShExist("Result" & Page) = False Then
                        Sheet2.Copy After:=ThisWorkbook.Sheets(Sheets.Count)
                        ActiveSheet.Name = "Result" & Page
                    End If
                    Sheets("Result" & Page).[A2].Resize(1000000, 7).Clear
                    Sheets("Result" & Page).[A2].Resize(k, 7).Value = RArr
                End If
                k = 0
                ReDim RArr(1 To MaxRw, 1 To 7)
            End If
        Next
    End If
Next
If k > 0 Then
    If Page = 0 Then
        Sheet2.Range("A2:G1000000").Clear
        Sheet2.Cells(2, 1).Resize(k, 7).Value = RArr
    Else
        Page = Page + 1
        If ShExist("Result" & Page) = False Then
            Sheet2.Copy After:=ThisWorkbook.Sheets(Sheets.Count)
            ActiveSheet.Name = "Result" & Page
        End If
        Sheets("Result" & Page).[A2].Resize(1000000, 7).Clear
        Sheets("Result" & Page).[A2].Resize(k, 7).Value = RArr
    End If
End If
Sheet2.Activate
Application.ScreenUpdating = True
MsgBox Timer - t & " seconds" & Chr(13) & _
Format(p, "#,###") & " result rows" & Chr(13) & _
"in " & Page & " Pages", , "ptm0412"
End Sub

 

[SteveNguyen1991]

Chưa mà bác ơi, đợt này công ty mới hoạt động lại bận quá, tối về làm được ít. em cũng muốn đưa lên mà code chưa xong luôn chứ không nói chuyện tối ưu.

 

[ptm0412]

Chưa mà bác ơi, đợt này công ty mới hoạt động lại bận quá, tối về làm được ít. em cũng muốn đưa lên mà code chưa xong luôn chứ không nói chuyện tối ưu.

Bạn cứ từ từ đưa lên, dù hay dù dở cũng đáng quý mà.

 

[SteveNguyen1991]

Bạn cứ từ từ đưa lên, dù hay dù dở cũng đáng quý mà.

Em nhờ bác xem giúp có đúng theo yêu cầu không nhá. Bài này đúng cái em cần, em đọc về mảng cách đây hơn tháng và áp dụng vào Mấy bài thấy tốc độ cải thiện rõ so với khi dùng Range.
Cảm ơn bác và mong bác chia sẻ thêm kiến thức!!

 

[VetMini]

Tính xem thử mà thấy cụm từ "tốc độ cải thiện rõ" đành co vòi lại.

 

[SteveNguyen1991]

Tính xem thử mà thấy cụm từ "tốc độ cải thiện rõ" đành co vòi lại.

Sao vậy bác, em nói thật mà. Trước em cứ dùng Range().value nên cái gì cũng chậm ( có thể em dùng sai cách). Bác có cái gì hay chỉ em với!!

 

[VetMini]

Tôi già rồi, dễ bị xâm. Cứ nghe đến từ "tốc độ" là chóng mặt. Có ngày đứt gân máu.

Nếu tĩnh từ "hay" liên quan đến "tốc độ" thì từ nào giờ tôi viết code dở bẹt. Không có gì hay để chỉ ai đâu.

 

[ptm0412]

Tính xem thử mà thấy cụm từ "tốc độ cải thiện rõ" đành co vòi lại.

Bạn ấy nói dùng mảng tốc độ cải thiện thấy rõ "so với dùng range" mà anh! Dùng Range nghĩa là đọc trên sheet và ghi lại xuống sheet từng ô một), chuyển sang dùng mảng thì nhanh thấy rõ.

Em nhờ bác xem giúp có đúng theo yêu cầu không nhá.

Kết quả của bạn (chọn RowExpect = 500 ngàn) ra 5 sheet đầy = 2.5 triệu dòng. Do If Fm = "Y" bạn để sai chỗ.
Bạn sửa lại đúng chỗ sẽ thấy những cái sai khác nữa.
Thuật toán của bạn cũng khác so với bạn quanluu, làm kỹ lại sẽ đúng thôi.

 

[ptm0412]

Code khác dùng 1 hàm con và 1 sub chính, theo cách b, phương pháp 2.1 gì đó của lão @VetMini

(b) cho code ấy vào một hàm, và gọi hàm để lấy từng phần dữ liệu, bảo đảm không bị tràn.
2.1. thảy code vào một hàm, và dùng tham số hoặc biến toàn cục để liên lạc với code chính.

- Hàm con không cần biết khi nào hết, code chính bảo khi nào chạy thì chạy, khi nào ngưng thì ngưng.
- Số sheet và số dòng cho mỗi lần chạy cho 1 sheet do code chính tính sẵn.
- Code chính truyền tham số dòng bắt đầu và dòng kết thúc của 1 lần chạy cho hàm con, lấy kết quả về tạo sheet và gắn xuống.
- Chỉ với dòng bắt đầu và dòng kết thúc, hàm con tự tìm dữ liệu nguồn, xào nấu ra kết quả trả cho code chính.

Ghi chú:
- Chấp nhận các sheet có số dòng không giống nhau do điều kiện cột F = "Y" không phân bố đều.
- Tuân thủ các điều kiện tạo sheet, xóa dòng, Redim, ... như bài #23

Kết quả các lần test:



Code hàm con:

Dim StoreArr(), StoreCols As Long, p As Long, MaxRw As Long
    ''__________________
Function RunPart(ByVal StartRw As Long, ByVal EndRw As Long) As Variant
Dim PartArr(), Data1Arr(), Data2Arr(), k As Long
With Sheet1
    Data1Arr = .Range(.Cells(StartRw, 2), .Cells(EndRw, 6)).Value
    Data2Arr = .Range(.Cells(StartRw, 7), .Cells(EndRw, StoreCols + 6)).Value
End With
ReDim PartArr(1 To Application.Min(MaxRw, (EndRw - StartRw + 1) * StoreCols), 1 To 7)
For i = 1 To UBound(Data1Arr, 1)
    If Data1Arr(i, 5) = "Y" Then
        For j = 1 To UBound(Data2Arr, 2)
            If Not IsEmpty(Data2Arr(i, j)) Then
                k = k + 1: p = p + 1
                PartArr(k, 1) = p
                PartArr(k, 2) = Data1Arr(i, 1)
                PartArr(k, 3) = Data1Arr(i, 2)
                PartArr(k, 4) = Data1Arr(i, 3)
                PartArr(k, 5) = Data1Arr(i, 4)
                PartArr(k, 6) = StoreArr(1, j)
                PartArr(k, 7) = Data2Arr(i, j)
            End If
        Next
    End If
Next
Debug.Print StartRw, EndRw, k, p
RunPart = PartArr
End Function

Code chính:

Sub Main()
Dim LastRw As Long, LastCol As Long, RArr(), TotalRw As Long
Dim Pages As Long, MaxRes As Long, DataRws As Long
Dim SRw As Long, ERw As Long, PRw As Long
Application.ScreenUpdating = False
t = Timer
MaxRw = Sheet2.[H1].Value
DelResult
With Sheet1
    LastRw = .Cells(50000, 2).End(xlUp).Row
    LastCol = .Cells(1, 1000).End(xlToLeft).Column
    StoreArr = .Range(.Cells(1, "G"), .Cells(1, LastCol)).Value
    DataRws = LastRw - 1
    StoreCols = LastCol - 6
End With
MaxRes = DataRws * StoreCols
Pages = Int(MaxRes / MaxRw) + IIf(MaxRes Mod MaxRw > 0, 1, 0)
PRw = Int(MaxRw / StoreCols)
p = 0
If Pages = 1 Then
    RArr = RunPart(1, DataRws)
    Sheet2.Range("A2:G1000000").Clear
    Sheet2.Cells(1, 2).Resize(UBound(RArr, 1), 7).Value = RArr
Else
    For pg = 1 To Pages
        SRw = (pg - 1) * PRw + 1
        ERw = IIf(pg <> Pages, SRw + PRw - 1, DataRws)
        RArr = RunPart(SRw, ERw)
        If pg = 1 Then
            Sheet2.Range("A2:G1000000").Clear
            Sheet2.Cells(2, 1).Resize(UBound(RArr, 1), 7).Value = RArr
        Else
            If ShExist("Result" & pg) = False Then
                Sheet2.Copy After:=ThisWorkbook.Sheets(Sheets.Count)
                ActiveSheet.Name = "Result" & pg
            End If
            Sheets("Result" & pg).[A2].Resize(1000000, 7).Clear
            Sheets("Result" & pg).[A2].Resize(UBound(RArr, 1), 7).Value = RArr
        End If
    Next
End If
Application.ScreenUpdating = True
MsgBox Timer - t & " seconds" & Chr(13) & _
Format(p, "#,###") & " rows" & " in " & Pages & " pages", , "ptm0412"
End Sub

 

[VetMini]

Từ đầu, tôi có nói là tôi chỉ khai triển thêm. Gãi ngứa những người đã rành code. Và đòng thời cũng nói rõ là chỉ cần lý thuyết/lý luận.

Nếu trong vòng 1 giờ nữa không thấy đọng tĩnh gì thì tôi sẽ tự trả lời ở đây.

Tự trả lời:
Phần 1 tôi hỏi chuyên về quản lý dữ liệu. Nhưng có lẽ do ở đây không có mấy ai thích cái đó. Dân viết code ở đây thường chỉ khoái viết code. Vì vậy tôi cảm thấy không cần trả lời.

Phần 2 :
(a) nhét code ấy vào code chính. Khá mệt
I lied. Không mệt, nhưng tôi biết tác giả rốt cuộc rồi cũng viết phần này. "Reinvent the wheel" là không nên

(b) cho code ấy vào một hàm, và gọi hàm để lấy từng phần dữ liệu, bảo đảm không bị tràn.

Có 3 cách thực hiện:

2.1. thảy code vào một hàm, và dùng tham số hoặc biến toàn cục để liên lạc với code chính.
Ưu: giản dị, dễ làm, và tuân thủ luật "code phân tích dữ liệu (code xào nấu, đầu bếp) riêng biệt với code chép đầu ra (dọn món ăn, nhân viên phục vụ)
Khuyết: gặp dữu liệu truyền nhiều mặt thì quản lý tham số mệt nghỉ. Nếu tách một mớ ra thành biến toàn cục thì đỡ số tham phảin truyền nhưng lại bị vướng vấn đề quản lý số biến này

2.2. biến code thành sub nội, đặt ở cuối code chính, và dùng lệnh GoSub để gọi. Với cách này, sub nội và sub bao nó hoàn toàn chia sẻ mọi biến, không cần phải truyền.
Ưu: tuân thủ luật "công việc nào rõ công việc ấy như trên đến trên 50%. Không phải quan tâm tham số. Bởi biến nội là biến chung cho tầm vực hàm. Sub nội chỉ là một hình thức nhảy dòng, vẫn nằm trong tầm vực hàm chứa nó.
Biết dùng code kiểu này sẽ hiểu rõ hơn về tầm vực của một Sub/Hàm.
Khuyết: gồm tất cả các khuyết điểm của GoSub
Phải nhớ lệnh Exit Sub đặt trước điểm vào (label) của sub nội. Tất cả các lệnh dọn sạch phải đặt trước điểm này.
Phải nhớ ít nhất là có 1 lệnh Return trong sub nội để nó biết đường về. Chứ để chạy đến End Sub thì nát bét
code không hoàn toàn "đặt riêng" cho nên về sau có chỉnh đổi phải xét đủ 2 chỗ.

2.3. đặt code trong một Class Module
Tuy VBA không phải là ngôn ngữ hỗ trợ HĐT, nhưng đi vào một chút để hiểu cách dùng class module tạo tính chất "gói gọn" của code.

Quý vị có thể coi như là tình trạng gọi trước cho nhà hàng làm món ăn. Cứ dùng hết một món là lại gọi họ mang món ăn kế tiếp.
Với Object tạo ra, quý vị có thể đưa chi tiết về nơi cần lấy dữ liệu (1 con chó lỡ bị xe quý vị cán què ở Cầu Chữ Y - đã đền khổ chủ). Nhà hàng quen cho nên biết bạn muốn làm gì (thui chó, cạo lông, xả thịt, rồi làm từng dĩa). Cứ mỗi Text kế đó thì nhà hàng tự động biết mang dĩa kế lên. Nhà hàng không cần biết quý vị tiêu thụ cách nào. Cho đến hết thịt thì nhà hàng báo: hết. Quý vị tự biết chỉ còn rượu, cứ việc uống tiếp hay nghỉ là tuỳ.
Ưu: chuyện con chó chỉ có quý vị và nhà hàng biết với nhau. Chủ chó không cần biết. Hàng xóm không cần biết (nhưng nhớ các cháu nhé, làm như thằng bố trong "Trẻ Con Không Biết Ăn Thịt Chó" của Nam Cao là bậy). Nhà hàng cũng không cần biết quý vị làm cái gì - họ chỉ tuân răm rắp theo chỉ thị có sẵn, chó đấy, các món yêu cầu đã vẽ rồi.
Khuyết: cần khá nhiều kinh nghiệm viết code để chỉ phải thiết kế class 1-2 lượt. Nếu kém kinh nghiệm thì thiết kế nhiều lượt mới xong. Đấy là code nhỏ. Thực tế khác hơn nhiều. Nếu có sẵn code gần giống để chỉ cần phải viết lớp khác bọc ngoài thì khá dễ. Nhưng phải thiết kế từ đầu thì khá chông gai.
Code HĐT gần như luôn luôn kém hiệu quả hơn hướng cấu trúc về tốc độ. Trư phi code được tinh chế nhiều lần. Đối với thói quen code chữa cháy như GPE thì không đủ thuyết phục để thực hiện.

Trong bài code gọi một Object ở trên tôi có gợi ý về cách thiết kế giao diện. Nếu quý vị không hề đọc, hoặc đọc mà không có ý kiến gì thì coi như quý vị không cho là nó quan trọng.

 

[ptm0412]

Phần 1 tôi hỏi chuyên về quản lý dữ liệu. Nhưng có lẽ do ở đây không có mấy ai thích cái đó. Dân viết code ở đây thường chỉ khoái viết code. Vì vậy tôi cảm thấy không cần trả lời. (1)

Trong bài code gọi một Object ở trên tôi có gợi ý về cách thiết kế giao diện. Nếu quý vị không hề đọc, hoặc đọc mà không có ý kiến gì thì coi như quý vị không cho là nó quan trọng. (2)

(1) Tôi cho rằng câu hỏi của anh dành cho người đang muốn làm bài, nên tôi cũng để dành lại không trả lời, hoặc ra bài tập kế tiếp có liên kết bảng khác. Thiết kế cấu trúc bảng data của tôi nhằm mục đích liên kết đến ít nhất 4 bảng khác (có xen 1 chút thực tế cấu trúc dỏm của đa số người dùng hiện tại là có tên không có mã). Vụ có tên không có mã sẽ là 1 bài tập nhỏ gọi là "liệt kê lỗi thiếu/ dư/ sai so với danh mục"
(2) Cũng vẫn là câu hỏi cho người tham gia giải bài tập. Và anh nói đúng là đa phần người viết code không biết đánh giá ưu khuyết của cái mình đang làm, chứ đừng nói là so sánh 3 cái với nhau.

Riêng tôi thì yếu về class, nên thường là xoay sở với code thường. Làm được như 2 code trên đã là cố lắm rồi.

 

[VetMini]

Nhiệm vụ làm việc rắc rối quá thì nó không xứng đáng dùng HĐT với VBA.
Ngôn ngữ LT HĐT là phải hỗ trợ tính thừa kế một cách hết mình, không thể đôi ba chục phần trăm như VBA.
Lý do là để người viết code có thể thiết kế lớp cơ bản thật nhanh, sau đó mới thêm mấy lớp nữa làm những điều kiện phức tạp.

Đó là phần khuyết điểm tôi nêu ra ở bài #32:
Đối với thói quen code chữa cháy như GPE thì không đủ thuyết phục để thực hiện.

 

[ptm0412]

Nhiệm vụ làm việc rắc rối quá thì nó không xứng đáng dùng HĐT với VBA.
Ngôn ngữ LT HĐT là phải hỗ trợ tính thừa kế một cách hết mình, không thể đôi ba chục phần trăm như VBA.
Lý do là để người viết code có thể thiết kế lớp cơ bản thật nhanh, sau đó mới thêm mấy lớp nữa làm những điều kiện phức tạp.

Đó là phần khuyết điểm tôi nêu ra ở bài #32:
Đối với thói quen code chữa cháy như GPE thì không đủ thuyết phục để thực hiện.

Qua 2 bài tham gia thực hiện bài tập ở trên, kết hợp những chủ đề trước đây trên GPE, tôi có nhận xét về người học VBA ở đây:
- Đặt ra thuật toán mà không soát xét yêu cầu của thuật toán (có phù hợp để dùng hay không)
- Không test hoặc không test kỹ kết quả, sai mà không biết.
- Test không có phương pháp nên chữa lỗi này rồi chữa lỗi khác mà vẫn chưa hết.
- Chưa lường trước cho những trường hợp sẽ có thể lỗi khi dữ liệu thay đổi (thay đổi số lượng/ kiểu), chỉ mới đúng cho dữ liệu mẫu của 1 bài toán cụ thể. Dù cho dữ liệu mẫu được cho 1 cách sơ sài hay đã rõ ràng.

 

[Maika8008]

...
Hàng xóm không cần biết (nhưng nhớ các cháu nhé,Ưu: chuyện con chó chỉ có quý vị và nhà hàng biết với nhau. Chủ chó không cần biế làm như thằng bố trong "Trẻ Con Không Biết Ăn Thịt Chó" của Nam Cao là bậy). ...

Chỗ này ít người biết vì phải đúng tuổi, đúng sở thích mới đọc truyện đó của Nam Cao (tựa truyện mà tôi đọc là Trẻ con không được ăn thịt chó). Hồi đó đọc đi đọc lại nhiều lần tôi cứ thắc mắc mãi là mấy khúc mía mà chị vợ đi chợ mua về đâu rồi mà mấy đứa con khóc đói đứt ruột thế. Ít nhất đó cũng là thức ăn lót lòng đỡ đói. Tôi đoán chắc tác giả quên chứ hiện thực phê phán đâu bỏ qua chi tiết hiện thực quan trọng vậy.

 

[VetMini]

Chỗ này ít người biết vì phải đúng tuổi, đúng sở thích mới đọc truyện đó của Nam Cao (tựa truyện mà tôi đọc là Trẻ con không được ăn thịt chó). Hồi đó đọc đi đọc lại nhiều lần tôi cứ thắc mắc mãi là mấy khúc mía mà chị vợ đi chợ mua về đâu rồi mà mấy đứa con khóc đói đứt ruột thế. Ít nhất đó cũng là thức ăn lót lòng đỡ đói. Tôi đoán chắc tác giả quên chứ hiện thực phê phán đâu bỏ qua chi tiết hiện thực quan trọng vậy.

Chúng không thể đói mía được. Ai lại lâu rồi chưa ăn thịt, mâm thịt chó đã thấy qua mà còn lòng ăn thứ "đồ giả". Thà ăn thừa mâm còn hơn gặm mía ngửi mùi từ nhà ngoài bay vào. Cái độc địa (*1) của Nam Cao là chỗ đó. Cái độc nó đi từ lúc chị ấy đi vay gạo, vay rượu (ngầm hiểu chị tin rằng nhiều thế thì thể nào các con chị cũng còn chút thừa) cho đến lúc Cái chị nó bưng mâm trên đầu, không cho Cu em thấy...

(*1) phân biệt với "độc đáo": ngày xưa, cũng có nhiều người nói truyện Nam Cao độc địa quá, nhất là lúc tả Thị Nở, không chừa cho điểm lương tâm nào hết. Ngày xưa, tôi cũng ngỡ Bùi Hiển đã độc hơn Ngô Tất Tố rồi, không ngờ Nam Cao còn hơn một bậc.

 

[ptm0412]

Những bước cần thiết phải làm khi lập trình

Nói trước:
- Chủ đề này chưa đóng và bài này chưa phải là sơ kết hay tổng kết, chỉ là bài gợi ý về cách lập trình và phương pháp test sau khi lập trình. Những gợi ý sau đây áp dụng cho bài tập trong chủ đề này và những bài tương tự (chuyển cột thành dòng và tách kết quả)
- Bài viết này có thể chưa đầy đủ, nhưng là những bước tối thiểu.

Bước 1: Chọn thuật toán​

Một bài toán có nhiều cách giải, tương tự như vậy việc lập trình sẽ có nhiều thuật toán. Vấn đề là tư duy của người lập trình sâu sắc đến đâu, thì thuật toán sẽ phù hợp. trước khi chọn thuật toán phải nắm thật chắc yêu cầu của đề bài, các điều kiện cần tuân thủ, các giới hạn của kết quả.
Ví dụ code bài 17 và 23 dùng 2 vòng lặp cho 2 chiều của dữ liệu và biến đếm kết quả, khi biến đếm đạt mức yêu cầu thì trả về 0 để đếm lại từ đầu.
Code bài 26 lại dùng 1 vòng lặp cho cả 2 chiều dữ liệu và 2 biến đếm cho 2 chiều: Khi mỗi biến đếm đạt tới số cần thiết thì trả về giá trị ban đầu để đếm lại. Vòng lặp thứ hai là vòng lặp tạo sheet và gắn kết quả.
Hai code 17 và 23 chưa biết trước số sheet, vòng lặp chạy đến khi hết dữ liệu, chạy đến đâu tạo sheet đến đó.
Code bài 26 và 31 tính trước số sheet, số dòng dữ liệu cho mỗi sheet, sau đó dùng 1 vòng lặp sheet, mỗi sheet 2 vòng lặp tính kết quả.
Thuật toán có thể đơn giản, có thể phức tạp tùy theo tư duy và cả phong cách của người lập trình. Tuy nhiên hãy thử ít nhất 2 thuật toán.

Bước 2: Xét tính khả thi của thuật toán​

Hãy tập thói quen dùng giấy và bút. Nếu khả năng trung bình thì vẽ sơ đồ khối, con đường đi của dữ liệu từ bảng dữ liệu ban đầu cho đến khi thành kết quả: Lặp, rẽ nhánh, tính toán, quay về, …
Nếu thấy sơ đồ quá phức tạp thì hãy thử bỏ thuật toán tìm thuật toán khác.
- Sơ đồ có thể thực hiện là “có thể vẽ được”, rõ ràng, tường minh, trong tầm kiểm soát
- Sơ đồ không nên thực hiện là “không vẽ được”, quá nhiều nhánh rẽ không kiểm soát được.
Nếu có 2 thuật toán thì so sánh 2 sơ đồ để chọn.
Khi trình độ khá hơn và bài toán tương đối dễ có thể tưởng tượng sơ đồ, nhưng với dự án lớn vẫn phải vẽ ra giấy. Nếu cần thì sẽ vẽ những sơ đồ con. Ai không biết sơ đồ khối thì cần phải tìm tài liệu để đọc.

Bước 3: Chọn phương tiện dùng trong code​

Giữa việc đọc và ghi lên sheet với việc dùng mảng, hãy ưu tiên dùng mảng.
Nếu cần kiểm soát dữ liệu theo danh mục, nên cân nhắc việc có dùng Dictionary hay không. Không phải lúc nào dùng Dict. cũng là tối ưu.

Nếu dùng mảng:​

• Cân nhắc việc chỉ tạo 1 mảng hay nhiều mảng đầu vào (dữ liệu nguồn). Đôi khi dùng nhiều mảng nguồn dễ kiểm soát hơn là 1 mảng: Ví dụ code bài 17, 26 chỉ dùng 1 mảng dữ liệu nguồn, khi chạy vòng lặp sẽ phải lưu ý thứ tự cột đầu, cột cuối của vùng 2, 3 và thứ tự dòng đầu của vùng 1, 3. Trong khi code bài 23 dùng 3 mảng dữ liệu nên vòng lặp chạy từ đầu đến cuối không quan ngại.
• Trường hợp cần lấy dữ liệu trên 5, 7 cột rời rạc thì dùng 5, 7 mảng mỗi cột 1 mảng vẫn tốt hơn là dùng 1 mảng lớn cả chục cột.
• Redim mảng kết quả với kích thước đúng và vừa phải. Hãy ước lượng số dòng tối đa, tối thiểu, cũng như số cột tối đa tối thiểu. Lớn quá thì hao tài nguyên mà nhỏ thì thiếu. Tuân thủ nguyên tắc thà dư còn hơn thiếu nhưng đừng dư quá đáng. Ví dụ đề bài chủ đề này đọc kỹ sẽ thấy câu: “Số lượng mặt hàng cần kiểm tra và số lượng cửa hàng cần kiểm tra chưa biết trước”, thì có thể hiểu dữ liệu có thể chỉ có năm mười dòng và cũng có thể lên hàng chục ngàn dòng (tương tự là cột). Kết quả ước lượng khoảng vài ngàn dòng, nếu số lượng dòng tối đa theo sheet là 500 ngàn, mà Redim theo 500 ngàn thì quá dư. Vậy kết hợp là Redim theo số nhỏ hơn giữa 2 số đó.

Nếu dùng Dict:​

• Cân nhắc việc sử dụng bao nhiêu Dict. Một Dict dùng chung cho 2, 3 nội dung chưa hẳn là tối ưu lại còn khó kiểm soát. Khi cần đếm riêng lại không đếm được.
• Cân nhắc việc sử dụng items của Dict thế nào để tận dụng nó.

Dùng If ElseIf hay dùng Select Case​

Cần nắm vững tính chất và ứng dụng của từng loại để chọn dùng cho phù hợp.

Bước 4: Viết code phần tính toán ra kết quả​

Khai báo biến ​

Khai báo rõ ràng, tên biến gợi lên ý nghĩa của giá trị biến.

Xác định vị trí đặt các câu lệnh phụ và đặt điều kiện đúng:​

• Câu lệnh điều kiện (If): Điều kiện theo đề bài và điều kiện theo thuật toán. Xem lại sơ đồ giấy cho chắc chắn. Code bài 17 mắc lỗi này (thiếu điều kiện bằng) nên mỗi sheet tạo ra (kể từ sheet thứ hai) mất 1 dòng kết quả. Code bài 26 để điều kiện cột F bằng “Y” ở ngoài vòng lặp nên không có tác dụng.
• Câu lệnh tăng biến đếm: Khi nào tăng, khi nào không tăng, tăng trước hay tăng sau câu lệnh chính.
• Câu lệnh trả giá trị biến đếm về giá trị ban đầu: Điều kiện gì và đặt ở đâu.
• Câu lệnh thoát vòng lặp: Thoát 1 vòng hay thoát cả 2 vòng, điều kiện thoát là gì. Vòng lặp Do có điều kiện thoát chưa, điều kiện đó có khi nào thỏa không?
• Nếu mảng dùng lại trong các vòng lặp, phải bảo đảm mảng phải bị xóa giá trị của vòng lặp trước. Code bài 26 bị lỗi này. Cách xóa dễ nhất là Redim lại.
• …

Bước 5: Viết code phần gán kết quả​

Tạo sheet mới hay dùng sheet đang có​

• Tạo sheet mới khi nào, có cần đặt tên lại không, nếu đặt tên lại phải xét tên dự định đạt đã tồn tại chưa
• Dùng sheet đang có thì dùng sheet nào, khi nào tạo sheet mới, tên gì, đã tồn tại chưa.
• Kiểm soát trường hợp đặc biệt: Dữ liệu nguồn ít khiến cho kết quả ít hơn số dòng tối đa mong muốn, đã kiểm soát được chưa. Code bài 26 tính trước số sheet và khi gặp trường hợp này số sheet bằng không, chẳng có kết quả để gán xuống.

Xóa dữ liệu đang có​

Nếu dùng sheet đang có, phải bảo đảm trên sheet đó không có kết quả của lần chạy trước. Nếu có phải xóa cho hết.

Viết hàm con – thủ tục con​

Viết hàm con và/ hoặc thủ tục con giúp cho code gọn gàng hơn khi trong code chính có những đoạn code thực hiện nhiều lần với những trường hợp khác nhau hoặc những thông số khác nhau.

Ví dụ trong bài 31, hàm con nhận 2 tham số dòng đầu & dòng cuối của dữ liệu, tự lấy đoạn dữ liệu giữa 2 dòng tham số và trả về kết quả trong đoạn dữ liệu đó.

Xác định chức năng cho hàm/ thủ tục con​

• Nếu chỉ muốn trả về mảng kết quả thì dùng hàm con
• Nếu muốn mỗi lần ra kết quả là 1 lần tạo sheet luôn thì dùng thủ tục con.

Xác định tham số truyền vào​

• Truyền bao nhiêu tham số
• Truyền những gì

Code bài 31 chọn dùng hàm, và chỉ truyền 2 tham số dòng đầu dòng cuối của dữ liệu. Hai tham số này được code chính tính toán sẵn.
Cũng có thể truyền chỉ 1 tham số là mảng dữ liệu đã tìm sẵn theo dòng đầu & dòng cuối.

Bước 7: Kiểm tra kết quả​

Viết code xong chạy lần đầu mà đúng ngay, hãy tự nhủ là hên, chứ không phải là giỏi. Phải kiểm tra có phương pháp và kiểm tra kỹ.

So sánh với kết quả tính tay​

• Tính nhẩm hoặc dùng công thức (CountIf) ra số lượng dòng kết quả, đối chiếu với kết quả mới chạy ra.
• Tìm kiếm bằng mắt hoặc Ctrl F xem 1 dòng, 1 ô dữ liệu đáng lẽ không tính có bị tính không
• Nếu kết quả là tính toán số liệu, dùng công thức tính tay hoặc bằng hàm Excel để kiểm tra.
• Dùng pivot table để kiểm tra kết quả có bỏ sót trường hợp hoặc dư trường hợp nào. Trong nhiều trường hợp phương pháp này rất hiệu quả ví dụ như Count = Count If không giống Count = Pivot table.
• Để ý cột số thứ tự nếu có. Số thứ tự phải kế thừa từ sheet này qua sheet kia chứ không phải mỗi sheet mỗi đánh lại từ 1.

Chạy thử trường hợp đặc biệt:​

• Cho LastRw là số khá nhỏ (10 – 100 dòng dữ liệu)
• Cho ExpectRow là số khá lớn (hơn 1 triệu)
• Cho ExpectRow là số khá nhỏ (50.000 – 100.000)
• Cho ExpectRow là vài số không tròn ngàn, tròn vạn, đặc biệt là không phải ước số của tổng kết quả.
• Cho ExpectRow là ước số (chia hết) của tổng kết quả

Chạy code nhiều lần​

Chạy code cùng 1 điều kiện ít nhất 2 lần để đối chiếu kết quả. Ví dụ code bài 26 chạy lần 2 thì câu lệnh gán tiêu đề A1:F1 mất tác dụng.
Ngoài ra nếu 1 lần chạy tạo mới 5 sheet, chạy nhiều lần thì số sheet mới bị nhân lên, không còn biết sheet nào của lần chạy nào. Nên có thủ tục xóa sheet hoặc dùng lại sheet

 

[ptm0412]

Code lại bằng thuật toán của @SteveNguyen1991 ở bài 26. Như đã nói ở trên, code bài 26 có 1 số lỗi đáng kể như sau:
- Điều kiện cột F nằm ngoài vòng lặp nên không có tác dụng, kết quả đủ 2.5 triệu dòng (số đúng là 2.080.500)
- Vòng lặp chạy theo dòng của dữ liệu, kết quả gán vào mảng kết quả nhưng theo thứ tự dòng của dữ liệu. Cho nên mỗi sheet kết quả đều có những dòng trống. Lẽ ra phải tạo biến tăng mới cho thứ tự dòng kết quả
- Vòng lặp chạy theo thứ tự dòng dữ liệu =1 to RowExpect, cho tất cả sheet, bằng cách nào đó kết quả tổng 5 sheet lên đến 2.554.000, không biết lòi đâu ra 54 ngàn dòng.
- Tính trước số sheet mà chỉ tính khi tổng số dòng kết quả lớn hơn 1 triệu, kết quả là dưới 1 triệu có số sheet bằng không.
- Và vài chỗ sai không đáng kể khác

Tôi viết lại code đó bằng y thuật toán, chỉ sửa những chỗ gây lỗi như vừa liệt kê. Tôi vẫn khai báo 3 mảng dữ liệu đầu vào để dễ kiểm soát. Code với thuật toán này thậm chí còn nhanh hơn 3 code bên trên.

Sub DelSheet()
For Each sh In ThisWorkbook.Sheets
    If IsNumeric(Right(sh.Name, 1)) Then sh.Delete
Next
End Sub
'___________________________'
Sub try()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim i As Long, j As Long, k As Long, RowExpect As Long, RowNumber As Long
Dim Pages As Long, sh As Long, m As Long
Dim t As Double, p As Long
t = Timer()

Dim Data1Arr(), Data2Arr(), StoreArr(), RArr()
Dim LastRow As Long, LastCol As Long
With Sheet1
    LastRw = .Cells(50000, 2).End(xlUp).Row
    LastCol = .Cells(1, 1000).End(xlToLeft).Column
    Data1Arr = .Range("B2:F" & LastRw).Value
    StoreArr = .Range(.Cells(1, "G"), .Cells(1, LastCol)).Value
    Data2Arr = .Range(.Cells(2, "G"), .Cells(LastRw, LastCol)).Value
    DataRws = LastRw - 1
    StoreCols = LastCol - 6

End With
RowExpect = Sheet2.[H1]
ReDim RArr(1 To RowExpect, 1 To 7)
RowNumber = DataRws * StoreCols
Pages = Int(RowNumber / RowExpect) + IIf(RowNumber Mod RowExpect > 0, 1, 0)
PRw = Int(MaxRw / StoreCols)
DelSheet
i = 1: p = 0: j = 1
For sh = 1 To Pages
    q = 0
    Sheets.Add After:=Sheets(Sheets.Count)
    ActiveSheet.Name = "KQ" & sh
    Sheet2.Range("A1:G1").Copy ActiveSheet.[A1]
   
    For k = 1 To RowExpect
        If i = DataRws + 1 Then GoTo skip
        If Data1Arr(i, 5) = "Y" And Not IsEmpty(Data2Arr(i, j)) Then
            p = p + 1
            q = IIf(q = RowExpect, 1, q + 1)
            RArr(q, 1) = p
            RArr(q, 2) = Data1Arr(i, 1)
            RArr(q, 3) = Data1Arr(i, 2)
            RArr(q, 4) = Data1Arr(i, 3)
            RArr(q, 5) = Data1Arr(i, 4)
            RArr(q, 6) = StoreArr(1, j)
            RArr(q, 7) = Data2Arr(i, j)
        End If
        j = j + 1
        If j = StoreCols + 1 Then
           i = i + 1
           j = 1
        End If
    Next k
    ActiveSheet.Range("A2").Resize(q, 7) = RArr
    Debug.Print ActiveSheet.Name, m, q, p
Next sh
skip:
    ActiveSheet.Range("A2").Resize(q, 7) = RArr
    Debug.Print ActiveSheet.Name, m, q, p
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Sheet2.Activate
[I5].Value = Timer() - t
End Sub

 

[SteveNguyen1991]

Code lại bằng thuật toán của @SteveNguyen1991 ở bài 26. Như đã nói ở trên, code bài 26 có 1 số lỗi đáng kể như sau:
- Điều kiện cột F nằm ngoài vòng lặp nên không có tác dụng, kết quả đủ 2.5 triệu dòng (số đúng là 2.080.500)
- Vòng lặp chạy theo dòng của dữ liệu, kết quả gán vào mảng kết quả nhưng theo thứ tự dòng của dữ liệu. Cho nên mỗi sheet kết quả đều có những dòng trống. Lẽ ra phải tạo biến tăng mới cho thứ tự dòng kết quả
- Vòng lặp chạy theo thứ tự dòng dữ liệu =1 to RowExpect, cho tất cả sheet, bằng cách nào đó kết quả tổng 5 sheet lên đến 2.554.000, không biết lòi đâu ra 54 ngàn dòng.
- Tính trước số sheet mà chỉ tính khi tổng số dòng kết quả lớn hơn 1 triệu, kết quả là dưới 1 triệu có số sheet bằng không.
- Và vài chỗ sai không đáng kể khác

Tôi viết lại code đó bằng y thuật toán, chỉ sửa những chỗ gây lỗi như vừa liệt kê. Tôi vẫn khai báo 3 mảng dữ liệu đầu vào để dễ kiểm soát. Code với thuật toán này thậm chí còn nhanh hơn 3 code bên trên.

Sub DelSheet()
For Each sh In ThisWorkbook.Sheets
    If IsNumeric(Right(sh.Name, 1)) Then sh.Delete
Next
End Sub
'___________________________'
Sub try()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim i As Long, j As Long, k As Long, RowExpect As Long, RowNumber As Long
Dim Pages As Long, sh As Long, m As Long
Dim t As Double, p As Long
t = Timer()

Dim Data1Arr(), Data2Arr(), StoreArr(), RArr()
Dim LastRow As Long, LastCol As Long
With Sheet1
    LastRw = .Cells(50000, 2).End(xlUp).Row
    LastCol = .Cells(1, 1000).End(xlToLeft).Column
    Data1Arr = .Range("B2:F" & LastRw).Value
    StoreArr = .Range(.Cells(1, "G"), .Cells(1, LastCol)).Value
    Data2Arr = .Range(.Cells(2, "G"), .Cells(LastRw, LastCol)).Value
    DataRws = LastRw - 1
    StoreCols = LastCol - 6

End With
RowExpect = Sheet2.[H1]
ReDim RArr(1 To RowExpect, 1 To 7)
RowNumber = DataRws * StoreCols
Pages = Int(RowNumber / RowExpect) + IIf(RowNumber Mod RowExpect > 0, 1, 0)
PRw = Int(MaxRw / StoreCols)
DelSheet
m = 1: p = 0
For sh = 1 To Pages
    i = 1
    j = 1
    q = 0
    Sheets.Add After:=Sheets(Sheets.Count)
    ActiveSheet.Name = "KQ" & sh
    Sheet2.Range("A1:G1").Copy ActiveSheet.[A1]
 
    For k = 1 To RowExpect
        If m = DataRws + 1 Then GoTo skip
        If Data1Arr(m, 5) = "Y" Then
            p = p + 1
            q = IIf(q = RowExpect, 1, q + 1)
            RArr(q, 1) = p
            RArr(q, 2) = Data1Arr(m, 1)
            RArr(q, 3) = Data1Arr(m, 2)
            RArr(q, 4) = Data1Arr(m, 3)
            RArr(q, 5) = Data1Arr(m, 4)
            RArr(q, 6) = StoreArr(1, j)
            RArr(q, 7) = Data2Arr(m, j)
        End If
        i = i + 1
        j = j + 1
        If j = StoreCols + 1 Then
           m = m + 1
           j = 1
        End If
    Next k
    ActiveSheet.Range("A2").Resize(q, 7) = RArr
    Debug.Print ActiveSheet.Name, m, q, p
Next sh
skip:
    ActiveSheet.Range("A2").Resize(q, 7) = RArr
    Debug.Print ActiveSheet.Name, m, q, p
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Sheet2.Activate
[I5].Value = Timer() - t
End Sub

Chào bác,
em cảm ơn bác đã sửa và chỉ ra những lỗi sai của em. Nhưng đến giờ vẫn cố gắng trong thời gian có thể để tự làm được bài này, không phải có ý gì nhưng em thấy phải tự mò code mới hiểu được bản chất của bài toán và tiến bộ được. Chân thành cảm ơn bác!