Lọc và lấy data từ nhiều file , đưa về file tổng hợp

[LuuGiaPhúc]

Nhờ các anh chị giúp mình viết đoạn code để lấy data từ nhiều file báo cáo (mỗi file báo cáo có thể có 1 sheet hoặc nhiều sheet tùy file), đưa về sheet TrnClss của file tổng hợp
Em muốn khi mình nhấn nút import , nó sẽ hiện ra bảng cho mình chọn file nào cần lấy, chọn xong nó sẽ lọc theo điều kiện bên dưới rồi đưa kết quả về sheet TrnClss của file tổng hợp
Điều kiện lọc :
Trong các file báo cáo, nhìn cột A, nếu thấy 3 chữ đầu là các chữ : ATD , BAN , PD0 , ZPC thì bỏ, không lấy cả hàng đó
Cột B : nếu có chữ Cancelled hoặc Preparation thì bỏ nguyên hàng, không lấy
cột D : chỉ lấy những ô nào có chữ AR Class và UL Class, tất cả các lớp còn lại đều bỏ, không lấy.
Tóm lại, mình chỉ muốn lấy những hàng nào có Class Type là AR Class hoặc UL Class và Status là Open hoặc Close và Class ID không phải là ATD , BAN , PD0 , ZPC
theo hình ví dụ bên dưới thì hàng thứ 45, cột B có chữ Close (thỏa điều kiện), cột D có chữ AR CLass (thỏa điều kiện) nhưng cột A lại có chữ ATD (không thỏa điều kiện) : nên loại , không lấy


Dưới đây là kết quả muốn có :


Cảm ơn các anh chị

 

[BuiQuangThuan]

Bài này nhìn quen quen. Bữa nay là biến thể khác ạ

 

[LuuGiaPhúc]

Bài này nhìn quen quen. Bữa nay là biến thể khác ạ

Dạ anh, yêu cầu mới . Hiện nay em đang làm bằng tay, import hết vào, rồi filter và xóa tay theo các điều kiện trên.
Nên em nghĩ nếu mình có thể lọc luôn ngay từ bước import multi file về file tổng hợp thì tiện hơn

 

[snow25]

Dạ anh, yêu cầu mới . Hiện nay em đang làm bằng tay, import hết vào, rồi filter và xóa tay theo các điều kiện trên.
Nên em nghĩ nếu mình có thể lọc luôn ngay từ bước import multi file về file tổng hợp thì tiện hơn

File cố định hay phải chọn file.

 

[LuuGiaPhúc]

File cố định hay phải chọn file.

Dạ, phải chọn từng file nhé anh
Em muốn nhờ các anh viết đoạn code, khi nhấn nút import, thì nó sẽ hiện ra bảng cho mình chọn từng file mà mình muốn import, chọn xong, nó sẽ lọc data theo điều kiện trên và đưa kết quả về file tổng hợp

 

[snow25]

Dạ, phải chọn từng file nhé anh
Em muốn nhờ các anh viết đoạn code, khi nhấn nút import, thì nó sẽ hiện ra bảng cho mình chọn từng file mà mình muốn import, chọn xong, nó sẽ lọc data theo điều kiện trên và đưa kết quả về file tổng hợp

Bài này muốn nhanh chắc phải nhờ ai đó viết code ADO và câu lênh SQL thì mới nhanh được.Nếu không ai code mai mình code nhưng không nhanh.

 

[ptm0412]

Dùng power query theo chủ đề Lấy dữ liệu từ nhiều file trong 1 folder + file nhiều sheet có bổ sung:
- Liệt kê file cần lấy, sheet cần lấy thành 1 bảng. query lọc theo bảng này
- trong vòng lặp của query, thêm bước lọc theo điều kiện (3 điều kiện And với nhau)

 

[LuuGiaPhúc]

Bài này muốn nhanh chắc phải nhờ ai đó viết code ADO và câu lênh SQL thì mới nhanh được.Nếu không ai code mai mình code nhưng không nhanh.

Dạ, cảm ơn anh, nếu được anh giúp thì tốt quá. Hiện tại mình vẫn đang làm bằng tay nên cũng không gấp lắm, vì mình nghĩ đã có thể có đoạn code để import data từ nhiều file vào rồi nên nếu có thể thêm luôn bước lọc bỏ trước khi import thì sẽ hay hơn thôi nên mình muốn cải tiến công việc, bớt thao tác thủ công thôi. Khi nào rảnh, nhờ anh xem giúp nhé. Cảm ơn anh rất nhiều.

Dưới đây là đoạn code dùng để import data từ nhiều file mà mình đang dùng (chỉ là nó không có bước lọc bỏ theo điều kiện như đã nói ở trên

Sub Import_Trainner_Class()
Dim openfile
Dim mn(), md
Dim chonfile As Variant
Dim sh As Worksheet, sn As Worksheet
Dim i As Long, j As Long, a As Long, lrn As Long, lr As Long
Application.DisplayAlerts = False
Application.ScreenUpdating = False
On Error Resume Next
ActiveSheet.ShowAllData
lr = Cells(Rows.Count, "A").End(xlDown).Row
Range("A2:S" & lr).ClearContents 'Xóa dòng này neu muon copy tiep theo (giu lai du lieu dang có)

Set sh = ThisWorkbook.ActiveSheet
chonfile = Application.GetOpenFilename(Title:="Chon file...", filefilter:="exel file(*.xls*),*.xls*", MultiSelect:=True)
For i = 1 To UBound(chonfile)
Set openfile = Workbooks.Open(chonfile(i))
For j = 1 To openfile.Sheets.Count
Set sn = openfile.Sheets(j)
openfile.Activate
sn.Activate
ActiveSheet.ShowAllData
lr = sh.Cells(Rows.Count, 2).End(xlUp).Row
a = 6 ' noi bat dau copy
lrn = sn.Cells(a, 1).End(xlDown).Row
sn.Range(sn.Cells(a, 1), sn.Cells(lrn, 19)).Copy 'so 1 là cot A , 19 là cot cuôi cùng can copy
ThisWorkbook.Activate
sh.Activate
sh.Range("A" & lr + 1).PasteSpecial xlPasteValues ''Cot A la cot bat dau paste vao
lr = sh.Cells(Rows.Count, 2).End(xlUp).Row
Next j
openfile.Close
Next
On Error GoTo 0
End Sub

 

[ptm0412]

đã có thể có đoạn code để import data từ nhiều file vào rồi nên nếu có thể thêm luôn bước lọc bỏ

Record macro filter delete, hoặc filter copy qua sheet kết quả cuối (sheet tổng hợp hiện tại coi như sheet kết quả tạm)

 

[huevantran]

Dùng power query theo chủ đề Lấy dữ liệu từ nhiều file trong 1 folder + file nhiều sheet có bổ sung:
- Liệt kê file cần lấy, sheet cần lấy thành 1 bảng. query lọc theo bảng này
- trong vòng lặp của query, thêm bước lọc theo điều kiện (3 điều kiện And với nhau)

Hôm nay em vừa đọc lại và thẩm thấu hơn tài liệu hướng dẫn Power Query và M-code của thầy, những hướng dẫn rất tuyệt vời. Cảm ơn Thầy rất nhiều và chúc Thầy nhiều sức khỏe.

 

[LuuGiaPhúc]

Dùng power query theo chủ đề Lấy dữ liệu từ nhiều file trong 1 folder + file nhiều sheet có bổ sung:
- Liệt kê file cần lấy, sheet cần lấy thành 1 bảng. query lọc theo bảng này
- trong vòng lặp của query, thêm bước lọc theo điều kiện (3 điều kiện And với nhau)

cảm ơn Thầy, để em thử tìm hiểu thêm về Power Query xem làm được không, trước giờ chưa từng làm Power Query
với sẵn tiện Thầy cho em hỏi thêm, em thường hay làm việc với lượng data rất lớn, đôi khi lên đến >700.000 dòng (import mỗi lần >12 file, mỗi file 2 ~ 3 sheet) nên file tổng hợp rất nặng, import xong dùng pivot lọc lại là nó đơ máy liên tục, nhiều khi import xong, nhấp refresh cái nó thoát ngang luôn.
Vậy có thể thay thế việc import lượng data lớn như thế rồi dùng pivot lọc và xuất kết quả bằng power query hay không ?

 

[ptm0412]

Vậy có thể thay thế việc import lượng data lớn như thế rồi dùng pivot xuất kết quả và lọc bằng power query hay không ?

Với dữ liệu lớn (trăm ngàn dòng - triệu dòng, thậm chí hơn), chỉ power query mới làm được nhờ việc không lưu xuống sheet mà lưu trong data model. File sẽ nhẹ.
Nếu dùng Query lọc sẵn, thì có thể rút gọn xuống ít dòng hơn, nhưng refresh vẫn chậm hơn code VBA. Dù vậy query linh hoạt hơn so với việc sửa code. Nếu trên 500 ngàn dòng thì refresh mất khoảng 3-5 phút (máy mạnh).
Nếu dữ liệu ổn định thì ít khi cần refresh lắm.