TÁCH DỮ LIỆU TỪ 1 SHEET FILE EXCEL TỔNG THÀNH FILE EXCEL NHỎ

[Raito yagami]

Chào các anh chị, em có 1 số file excel khoangr 500 nghìn dòng giờ em muốn tách file đấy thành các file exel nhỏ hơn tối đa 9000 dòng từ trên xuống dưới, file excel gốc không có công thức chỉ có dữ liệu. Anh chị giúp em cách tách với

 

[Ngày mai trời lại sáng]

dùng phương pháp copy và paste

Phương pháp này giống suy nghĩ của em nè... mỗi một lần copy thì tạo file mới rồi pase vào cho gọn, còn lẹ hay không thì em chưa làm nên chưa biết.

 

[ptm0412]

Paste ở đây là paste vào các Sheet cùng workbook với sheet gốc.

Copy paste thành sheet mới thì nhanh hơn copy paste file mới, lưu (tên file mới) và đóng file mới đó.
Còn copy paste file mới so sánh với gán giá trị vào file mới (là code 20s của tôi) thì tôi chưa thử. Tuy vậy theo suy luận thì copy paste chậm hơn hoặc bằng chứ không nhanh hơn, ngoài ra sẽ còn vấn đề về clipboard (bộ nhớ tạm khi copy). Càng nhiều dòng, copy càng nhiều lần, càng hao tốn.
Cuối cùng thì: Nếu như nhanh chậm hơn một vài giây (19, 20 với 22, 23) thì tôi vẫn chọn gán giá trị, không chọn copy paste.

 

[HUONGHCKT]

Copy paste thành sheet mới thì nhanh hơn copy paste file mới, lưu (tên file mới) và đóng file mới đó.
Còn copy paste file mới so sánh với gán giá trị vào file mới (là code 20s của tôi) thì tôi chưa thử. Tuy vậy theo suy luận thì copy paste chậm hơn hoặc bằng chứ không nhanh hơn, ngoài ra sẽ còn vấn đề về clipboard (bộ nhớ tạm khi copy). Càng nhiều dòng, copy càng nhiều lần, càng hao tốn.
Cuối cùng thì: Nếu như nhanh chậm hơn một vài giây (19, 20 với 22, 23) thì tôi vẫn chọn gán giá trị, không chọn copy paste.

Cảm ơn anh đã khai sáng.
Tôi thử: tạo các Sheet trên cùng Workbook

......
sh.Range("A3").Resize(SoDong, 15).Value = Ws.Cells(i, 1).Resize(SoDong, 15).Value
......

Chạy hết 73,515625s . Cũng có thể do máy tôi cấu hình thấp

 

[VetMini]

Tôi thử 500800 dòng 15 cột dùng phương pháp copy và paste hết 10,3s

...
Ws.Range......Copy Sh.range....
...

và nghĩ rằng số cột nhiều, ít không ảnh hưởng đến tốc độ code. Không biết suy nghĩ thế có đúng không. Anh PTM 0412 hoặc ai biết xin giải thích giùm, tôi xin được khai sáng.
Trân trọng

Copy nhiều đương nhiên có ảnh hưởng. Số cột nhiều đòi hỏi RAM phải nhiều. Chép lại là có khả năng vào Hard Disk. Mà vào HD thì còn tùy thuộc vào tốc độ của chúng.
Túm lại, cỡ chục giây không xác định được gì cả.

Chép vào nhiều files thì HD là chỗ quyết định. Code có thể chả làm gì được.

Chú thích:
Copy Range to Range là VBA dùng map bảng tính. Đương nhiên là nói sẽ nhanh hơn bất cứ gì khác.
Copy Range-Array-Range thì phải qua map range to array, rồi ngược lại. Hai lần map thì phải lâu hơn.
ADO phải qua data stream. Cung 2 lần, 1 lần stream vào recordset và một lần vào bảng tính. Tuy sau khi nó map được rồi thì cứ đổ stream nhưng cái phí tổn RAM của record set không phải là nhỏ.
Theo tôi thì cứ đưa cái range cho sub con nó ghi là dễ nhất. Nó ghi bằng cách nào thì chỉnh sửa ở đó, ghi thẳng, ghi gián tiếp qua array đều dễ chỉnh sửa. Sub mẹ chỉ việc phân range và chỉ tay thôi.
(ADO thì khong nên dùng sub con. Chuyển nhiều tham quá đâm lộn xộn)

 

[befaint]

Bài này tách thành file thì copy sheet thành new book rồi xóa dòng là nhanh nhất. Không động chạm gì tới định dạng của bảng tính gốc cả.

 

[VetMini]

Bài này tách thành file thì copy sheet thành new book rồi xóa dòng là nhanh nhất. Không động chạm gì tới định dạng của bảng tính gốc cả.

Đã thấy sự tiện lợi của sub con chưa?
Sub con có thể quyết định chuyện này.

Tuy nhiên, nếu phải làm rất nhiều files (như thớt nói) thì đáng lẽ PowerShell là giải pháp tốt nhất.

 

[ptm0412]

Tôi thử 500800 dòng 15 cột dùng phương pháp copy và paste hết 10,3s

Paste ở đây là paste vào các Sheet cùng workbook với sheet gốc.

Tôi đã thử code lại: (500 ngàn dòng x 15 cột)
- Copy paste sheet mới cùng file: Range1.Copy NewSh.[A1] : 4.8s
- Gán giá trị sheet mới cùng file: Range2.Value = Range1.Value 6.8s
- Copy paste file mới, lưu file tên mới và đóng: Range1.Copy NewWb.ActiveSheet.[A1]: 19s
- Gán giá trị file mới, lưu file tên mới và đóng: Range2.Value = Range1.Value: 20s

Tôi thử: tạo các Sheet trên cùng Workbook

Chạy hết 73,515625s

Tôi hơi ngạc nhiên. Tạo các sheet trên cùng workbook (giữa copy paste và gán giá trị) sao lại chênh lệch thời gian gấp 7 lần như vậy? Nếu máy yếu thì theo 1 tỷ lệ tương đối sẽ khoảng 13 - 14s thôi chứ?
Có khi nào vòng lặp gán giá trị hoặc câu lệnh không khéo chăng?

 

[HUONGHCKT]

Tôi đã thử code lại: (500 ngàn dòng x 15 cột)
- Copy paste sheet mới cùng file: Range1.Copy NewSh.[A1] : 4.8s
- Gán giá trị sheet mới cùng file: Range2.Value = Range1.Value 6.8s
- Copy paste file mới, lưu file tên mới và đóng: Range1.Copy NewWb.ActiveSheet.[A1]: 19s
- Gán giá trị file mới, lưu file tên mới và đóng: Range2.Value = Range1.Value: 20s

Tôi hơi ngạc nhiên. Tạo các sheet trên cùng workbook (giữa copy paste và gán giá trị) sao lại chênh lệch thời gian gấp 7 lần như vậy? Nếu máy yếu thì theo 1 tỷ lệ tương đối sẽ khoảng 13 - 14s thôi chứ?
Có khi nào vòng lặp gán giá trị hoặc câu lệnh không khéo chăng?

Tôi đã thử lại cả 3 cách : Chỉ dùng 1 vòng for để lấy dòng cuối
for i=2 to Lastrow step 9000 nhưng không hiểu sao mà vẫn cho kết quả đúng nhưng thời gian chạy code lại khác nhau nhiều như vậy
1/ Arr=Ws.Cells(i, 1).Resize(SoDong, 15).Value
Sh.range("A3").resize(sodong,15)=Arr ====> chạy hết 79,533203125s
2/ Ws.Cells(i, 1).Resize(SoDong, 15).Copy sh.Range("A3") ' chạy hêt 10,6171875s
3/ sh.Range("A3").Resize(SoDong, 15).Value = Ws.Cells(i, 1).Resize(SoDong, 15).Value ' chạy hêt 69,126953125 s

Sub Tach_1_SheetThanhNhieuSheet()

    Dim sh As Worksheet, Ws As Worksheet   
    Dim SoSheet As Integer, i As Long, Lr As Long
    Dim tTime As Double
 '   Dim Rng As Range
 '   Dim Arr()

    Const SoDong = 9000
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    tTime = Timer
    Set Ws = Sheets("Sheet1")
    Lr = Ws.Cells(Rows.Count, 1).End(xlUp).Row
    For i = 2 To Lr Step SoDong
'      Arr = Ws.Cells(i, 1).Resize(SoDong, 15).Value
            SoSheet = SoSheet + 1
            Set sh = Worksheets.Add
            sh.Name = "Sheet_" & SoSheet
            sh.Cells(1, 1) = sh.Name
            Ws.Range("A1:O1").Copy sh.Range("A2")
'          sh.Range("A3").Resize(SoDong, 15).Value = Arr      ' chay hêt 79,533203125s
'           Ws.Cells(i, 1).Resize(SoDong, 15).Copy sh.Range("A3")   ' chay hêt 10,6171875s
            sh.Range("A3").Resize(SoDong, 15).Value = Ws.Cells(i, 1).Resize(SoDong, 15).Value  ' chay hêt 69,126953125 s
    Next i
    Debug.Print Timer - tTime
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True 
    MsgBox "Done"
    End Sub

Nhân đây cũng nhờ anh @ptm0412 xem lại code của tôi xem có vấn đề gì chưa hợp lý và giải thích khai sáng giúp tôi và có thể có các bạn khác có quan tâm

 

[ptm0412]

Tôi đã thử lại cả 3 cách : Chỉ dùng 1 vòng for để lấy dòng cuối
for i=2 to Lastrow step 9000 nhưng không hiểu sao mà vẫn cho kết quả đúng nhưng thời gian chạy code lại khác nhau nhiều như vậy
1/ Arr=Ws.Cells(i, 1).Resize(SoDong, 15).Value
Sh.range("A3").resize(sodong,15)=Arr ====> chạy hết 79,533203125s
2/ Ws.Cells(i, 1).Resize(SoDong, 15).Copy sh.Range("A3") ' chạy hêt 10,6171875s
3/ sh.Range("A3").Resize(SoDong, 15).Value = Ws.Cells(i, 1).Resize(SoDong, 15).Value ' chạy hêt 69,126953125 s

Tôi chạy code của bạn:
Dùng mảng: 7.3671875
Copy paste: 3.47265625
Gán value: 5.65625

Cũng không nhanh chậm gấp nhiều lần.
Ghi chú:
Tôi chạy 1 code 3 lần lấy trung bình.
Trước khi chạy lần sau, xóa sheet đã tạo của lần trước.
Nếu kỹ hơn thì trước khi đổi code, đóng hẳn excel mở lên lại, hoặc thậm chí restart máy, trường hợp này chưa đến nỗi phải kỹ đến thế.

 

[HUONGHCKT]

Tôi chạy code của bạn:
Dùng mảng: 7.3671875
Copy paste: 3.47265625
Gán value: 5.65625

Cũng không nhanh chậm gấp nhiều lần.
Ghi chú:
Tôi chạy 1 code 3 lần lấy trung bình.
Trước khi chạy lần sau, xóa sheet đã tạo của lần trước.
Nếu kỹ hơn thì trước khi đổi code, đóng hẳn excel mở lên lại, hoặc thậm chí restart máy, trường hợp này chưa đến nỗi phải kỹ đến thế.

Cảm ơn anh đã phản hồi. Có lẽ do máy của tôi ì ạch, hoặc do dữ liệu của tôi giả lập không thật chuẩn nên thòi gian chạy code hết nhiều như vậy.

 

[ptm0412]

Tôi có sẵn 1 file 1 triệu dòng, dữ liệu chuẩn và gồm các kiểu đa dạng: Ngày, số, chuỗi, ... Chỉ có 11 cột, 4 cột phía sau thêm vô để so sánh thôi.

 

[HUONGHCKT]

Tôi có sẵn 1 file 1 triệu dòng, dữ liệu chuẩn và gồm các kiểu đa dạng: Ngày, số, chuỗi, ... Chỉ có 11 cột, 4 cột phía sau thêm vô để so sánh thôi.

View attachment 280604

Dữ liệu của tôi cũng là dữ liệu của ngân hàng, lúc đầu nó chỉ có 20000 dòng sau đó tôi copy thêm để thành hơn 508000 dòng, nó cũng bao gồm số , text,..

 

[ptm0412]

Dữ liệu của tôi cũng là dữ liệu của ngân hàng, lúc đầu nó chỉ có 20000 dòng sau đó tôi copy thêm để thành hơn 508000 dòng, nó cũng bao gồm số , text,..

Vậy thì tôi chịu thua.
Định nói do Office không có bản quyền, nhưng không chắc nên thôi.

 

[HUONGHCKT]

Vậy thì tôi chịu thua.
Định nói do Office không có bản quyền, nhưng không chắc nên thôi.

Điều anh định nói có lẽ là nguyên nhân chính, bởi vì tôi dùng Offce 365 không bản quyền, và cấu hình máy thì ram chỉ có 4 Gb thôi. Cảm ơn anh đã chia sẻ những kinh nghiệm quý cho 1 người a ma tơ- không có căn bản và cũng chả có tý kinh nghiệm nào như tôi.