Trong công việc, nhất là những công việc liên quan đến dữ liệu như ngân hàng, tài chính, kế toán và các ngành nghiên cứu, các bạn đã gặp phải trường hợp cần lấy dữ liệu từ một File khác đang đóng có nhiều trường thông tin nhưng chỉ muốn lấy một số trường nhất định. Trong bài viết này, TIN HỌC SAO VIỆT sẽ hướng dẫn các bạn làm thế nào để có thể lấy dữ liệu từ file excel khác đang đóng
Bài viết này tôi sẽ đưa ra một ví dụ về một chương trình mẫu, lấy dữ liệu từ một file Excel đang đóng.
Bạn đang xem: Cập nhật dữ liệu từ file excel khác
Ta hãy xem xét chương trình dưới đây sẽ thực hiện việc lấy dữ liệu từ một file Excel đang đóng cho vào file excel hiện hành chứa macro.
Các bước thực hiện như sau:
1 Tạo một Module mớivà Copy 2 đoạn Code sau dán vào
Sub Get
Data(Cell
KQ As Range, s
SQL As String) Dim Cnn As New ADODB.Connection Dim Rs As New ADODB.Recordset Dim str
Connect As String, Path As String On Error Resume Next Ch
Drive "E:" Ch
Drive "D:" Ch
Drive "F:" On Error Go
To 0 Path = Application.Get
Open
Filename str
Connect = "Provider=Microsoft.ACE.OLEDB.12.0; " _ & "Data Source = MY_FULL_NAME_FILEDATA; " _ & "Extended Properties='Excel 12.0; HDR=No; IMEX=1'; " str
Connect = Replace(str
Connect, "MY_FULL_NAME_FILEDATA", Path) Cnn.Connection
String = str
Connect Cnn.Open Rs.Open s
SQL, Cnn, 3, 1 Cell
KQ.Copy
From
Recordset Rs Rs.Close Cnn.Close Msg
Box "Cap nhat du lieu hoan tat", vb
Sub Import
File() Application.Calculation = xl
Calculation
Manual Dim s
SQL As String Dim Cell
KQ As Range Set Cell
KQ = Active
Sheet.Range("A8") With Sheets("Bang
Ke
NH") .Auto
Filter
Mode = False .Range("A8").Resize(.Range("A500000").End(xl
Up).Row, 20).Clear
Contents End With s
SQL = "SELECT cdate(f1),f2,f3,f4,f5,f6,f7,f8,f9,val(f10) FROM
Data(Cell
KQ, s
SQL) Application.Calculation = xl
Calculation
Automatic
End Sub
2 Tạo một nút bấm để gọi lệnh tại Sheet muốn ghi dữ liệu vào
Sheet tôi hiện tại có tên Bang
Ke
NH, tại đây tôi chèn một Shapes, đặt tên Import Filesau đó nhấp chuột phải vào Shapes và chọn Assign Macro, tiếp theo chọn Macro có tên Import
File
3 Cách sử dụng
Khi bấm nút này, dữ liệu cũ sẽ được xóa, một cửa sổ yêu cầu chọn File chứa dữ liệu cần lấy, bạn chọn File sau đó chọn Open, ở đây tôi chọn file có tên Bang
Ke
NH, và kết quả như hình dưới đây:
Tham khảo các bài viết VBA trong Excel sau:
1 Lập trình VBA từ cơ bản đến nâng cao2 VBA Căn bản bài 01: Ghi và gắn Macro3 Code VBA tạo phiếu in lương hàng loạt bằng4 Tạo Form VBA quản lý sheets trong VBA Excel5 Code VBA Excel Tự Động Lưu Và Tạo Một Bản Sao Chép Vào Thư Mục Khác6
Vậy, làm sao để cập nhật dữ liệu từ nhiều file excel về một file excel duy nhất một cách tự động. Bài viết này sẽ chia sẻ giúp các bạn một trong những cách làm đơn giản nhất. Thay vì phải viết code VBA phức tạp, thì chỉ với vài thao tác thiết lập ban đầu với Power Query bạn có thể dự động toàn bộ file dữ liệu cho các lần tiếp theo.
Trước tiên, chúng ta sẽ cùng tìm hiểu về cách lấy dữ liệu từ file excel khác thông qua VBA xem như thế nào nhé. Cách này khá phức tạp đối với người mới, cần biết code VBA.
Chương trình YOUNG TALENT đã nhận được đánh giá tích cực từ nhiều học viên. Mọi người đều bất ngờ với lộ trình đào tạo bài bản 20 môn kết hợp tư duy và kỹ năng phục vụ mục đích phân tích. Nội dung chương trình mang tính thực tiễn cao, áp dụng ngay vào công việc, phù hợp với các bạn sinh viên hoặc vừa tốt nghiệp. TRẢI NGHIỆM LUÔN !
Lấy dữ liệu từ file Excel khác thông qua VBA
Thông thường, đối với nhiều người sử dụng Excel lâu năm sẽ nghĩ ngay đến VBA để giải quyết tình huống ghép nhiều hoặc ghép 2 file excel thành 1. VBA là một ngôn ngữ lập trình trên Excel, mục đích của VBA nhằm tự động hóa các thao tác mà người dùng Excel bằng cách viết một đoạn code.
Điểm mạnh của code VBA đó là người dùng chỉ viết 1 lần duy nhất để thiết lập ban đầu, sau đó chỉ cần click một nút được thiết lập để run – chạy đoạn code đã thiết lập sẵn đó.
Tuy nhiên, điểm yếu của VBA đó là người dùng rất khó tiếp cận. Bản chất của VBA là một ngôn ngữ lập trình, yêu cầu sự logic và người học vô cùng cần mẫn, chuyên sâu mới có thể viết được. Thông thường ít nhất mất 3 tháng để các bạn có thể viết được ngôn ngữ này, nhưng chỉ sau 1 tháng không sử dụng bạn có thể quên luôn cách viết.
Bên cạnh đó các file cần tổng hợp cần có cấu trúc giống nhau về tên cột, số lượng cột, dòng và vùng dữ liệu. Nếu file khác số lượng cột thì code VBA sẽ không hiểu và báo lỗi không thực hiện được.
Phía dưới là đoạn code VBA dành cho bạn nào mong muốn được biết:
| Sub merge_all() | |
| Dim cnn As ADODB.Connection | |
| Dim rst As ADODB.Recordset | |
| Dim s As Worksheet | |
| Dim I As Long, d As Long, Count Files As Long, J As Long | |
| Sheet Name = “Sheet1” & “$” — Sheet1 là tên sheet của file bạn cần tổng hợp | |
| Range Address = “A1:U1000” — đây là vùng dữ liệu của sheet mà bạn cần tổng hợp | |
| Dim files As Variant | |
| files = Application.Get Open Filename(, , , , True) | |
| If Var Type(files) = vb Boolean Then Exit Sub | |
| Set s = Sheets(“Master”) – tên sheet này tuy bạn chọn | |
| For d = LBound(files) To UBound(files) | |
| Set cnn = Get Conn XLS(files(d)) | |
| If cnn Is Nothing Then | |
| Msg Box “kiem tra lai du lieu file: ” & files(d) | |
| Exit Sub | |
| End If | |
| Set rst = cnn.Execute(“SELECT *,””” & files(d) & “”” as Name & Range Address & “>”) | |
| Count Files = Count Files + 1 | |
| If Count Files = 1 Then | |
| For J = 0 To rst.Fields.Count – 1 | |
| s.Cells(3, J + 1).Value = rst.Fields(J).Name | |
| Next J | |
| End If | |
| I = I + s.Range(“A” & 4 + I).Copy From Recordset(rst) – A4 hiện tại là ô dán dữ liệu vào, sửa nếu thay đổi | |
| rst.Close | |
| Set rst = Nothing | |
| cnn.Close | |
| Set cnn = Nothing | |
| Next d | |
| Msg Box “hoan thanh” | |
| End Sub |
Không dễ để viết được đoạn code phía trên, ghi nhớ cho lần sau đúng không các bạn! Do vậy, trong bài viết này mình sẽ chia sẻ các bạn thêm cách đơn giản hơn. Bạn sẽ biết cách tổng hợp dữ liệu từ nhiều file Excel bằng Power Query. Bạn có thể ghép 2 file excel thành 1 hoặc nhiều hơn một cách dễ dàng.
Power Query là một công cụ mới được phát triển bởi Microsoft trong khoảng 5 năm gần đây. Tuy nhiên, ở Việt Nam việc được nhắc đến, hay sử dụng còn rất hạn chế. Các tài liệu về Power Query chủ yếu là bằng tiếng anh.
Do vậy, một số lượng lớn người sử dụng chưa tiếp cận được.
Giống với VBA, thì Power Query giúp người dùng có thể tự động hóa dữ liệu thông qua việc thiết lập duy nhất 1 lần ban đầu.
Điểm khác của Power Query đó là người dùng không cần biết viết ngôn ngữ lập trình khó hiểu như VBA, bạn chỉ cần thao tác chọn – thả vô cùng đơn giản, tiết kiệm thời gian, đơn giản dễ học. Đây chính là điểm ưu việt của Power Query so với VBA.
Với cách làm này, bạn có thể xử lý rất nhiều tình huống cụ thể trong công việc mà không cần biết đến coding.
Microsoft Power Query phát triển trong thời gian gần đây, nhằm giúp đại đa số người dùng có thể tăng hiệu quả công việc, thay vì lặp lại các thao các hàng ngày. Thì bạn có thể sử dụng Power Query như một công cụ cứu cánh, tiết kiệm thời gian. Bạn hãy tham khảo cách cách gộp nhiều file excel thành 1 bằng cách ứng dụng power query bên dưới nhé.
Cách tổng hợp dữ liệu từ nhiều file Excel về một sheet Excel duy nhất
Trường hợp đầu tiên mình muốn giới thiệu các bạn cách tổng hợp dữ liệu từ các nguồn khác nhau với template của mỗi file là giống nhau. Mục tiêu của các bạn đó là tất cả các file nhận được sẽ tự động trở thành một sheet tổng hợp cho mục đích cá nhân, ví dụ như dùng để tổng hợp danh sách nhân viên theo từng tháng, hoặc báo cáo doanh số theo từng tháng, năm.
Bước đầu tiên: tổng hợp, download toàn bộ các file mà các bạn nhận được bỏ chung vào 1 folder. Đối với cách này, sẽ không phân biệt tên của file, quan trọng các file có chung template, tức là có tiêu đề giữa các file giống nhau là được.
Bước 2:Mở 1 file excel mới dùng để tổng hợp toàn bộ các file mà các bạn nhận được và thao tác lần lượt như sau:
Data --> Get Data --> From File --> From Folder
Bước 3: Chọn nguồn dữ liệu folder mà các bạn đã bỏ tất cả các file cần cập nhập theo các thao tác
Browse --> Tìm kiếm tên folder bỏ các file đã download hoặc tập hợp (ở đây mình ví dụ là folder Data Source) --> Ok --> Ok
Bước 4: Sẽ xuất hiện 1 bảng thể hiện tất cả các file mà các bạn đã bỏ vào folder --> chọn Transform Data
Sau khi chọn Transform data, thì bạn sẽ vào bên trong môi trường của Power Query.
Tiếp sau đó mình sẽ lấy data của tất cả các file vào cùng 1 sheet bằng cách sau:
Add Column --> Custom Column --> Viết câu lệnh (Excel.Workbook --> Chọn Content bên cột Available Column , true ) --> OK
Bước 5: Sau đó bạn tìm cột có tên là Custom thực hiện các bước như hình vẽ phía dưới để kéo data về.
Bước 6: Bạn tìm cột Custom.Kind --> Chọn Sheet --> OK
Lý do vì sao chọn sheet, vì một số file bạn nhận được trong tương lai của các phòng ban không được định dạng table. Do vậy, tên sheet gần như không thay đổi theo thời gian. Nên đây là cách để tránh trường hợp dữ liệu không hiểu.
Bước 7: Bạn tìm đến cột Costom.Data --> Click chuột phải --> Remove Other Columns thì các cột không cần thiết sẽ được xóa bỏ, chỉ để lại cột cần thiết để lấy dữ liệu.
Bước 8: Sau đó bạn xổ data từ con trỏ ngay tại B1 trong hình vẽ và ấn OK. Mục đích bước này để trích xuất toàn bộ dữ liệu từ tất cả các file theo các cột cần lấy. Bạn có thể bỏ chọn những cột mà bạn nghĩ không cần thiết, hoặc có thể remove column ở bước tiếp theo
Bước 9: Sau đó dữ liệu được lấy ra, bạn cần đưa dòng đầu tiên của dữ liệu thành cột tiêu đề. Bạn thực hiện các bước như trong hình.
Click con trỏ vào biểu tượng table góc trái màn hình --> lựa chọn Use First Row as Headers
Vậy là toàn bộ dữ liệu các bạn cần tổng hợp đã nằm trong 1 sheet. Bây giờ mình cần đưa ra giao diện Excel thuần túy.
Bước 10: Chọn Home --> Close & Load --> Close & Load To --> Table --> New Worksheet --> OK
H11. Chọn như hình
Sau 10 bước trên, bạn chỉ thiết lập 1 lần duy nhất. Nếu bạn có 1 file có template giống vậy các tháng sau, hoặc tuần sau. Bạn chỉ cần bỏ file mới vào folder và ấn Data --> refresh all. Toàn bộ dữ liệu của các bạn sẽ được cập nhập một cách tự động và nhanh chóng.
Cách tổng hợp dữ liệu từ nhiều file Excel thành một file Excel có nhiều sheet
Trường hợp thứ 2: Tổng hợp dữ liệu từ các nguồn khác nhau với template mỗi file là khác nhau. Mục tiêu nhằm thiết lập công thức giữa các file với nhau để ra mục tiêu cụ thể.
Bước 1: Download file mà các bạn nhận được, bỏ chung vào 1 folder nhằm mục đích dễ quản lý. Các file download này phải thống nhất tên file để phần mềm có thể nhận diện cho lần tiếp theo
Bước 2. Mở file mà bạn cần tổng hợp toàn bộ các file mà bạn nhận được thành các sheet khác nhau để thiết lập công thức:
Data --> Get Data --> From File --> From Workbook
Bước 3: Chọn tên file mà bạn muốn cập nhập hàng ngày. Lưu ý, tên file không thay đổi giữa các lần cập nhập. Nếu file mới được bỏ vào folder thì file cũ phải xóa đi. Đồng thời tên của file phải giữa nguyên so với lần đầu tiên thiết lập.
Bước 4. Thực hiện thao tác load file ra sheet. Lưu ý, các file bạn chọn có thể file được định dạng table hoặc không, do đó nên chọn tên sheet để load ra. Như hình phía dưới thì table sẽ có ký hiệu như Table1 và tên sheet sẽ có ký hiệu như DATA.
Dữ liệu các bạn chọn được load ra sheet của file Excel.
H14. Dữ liệu được chọn load ra sheet của file excelLặp lại các thao tác trên tương ứng với số lượng file cần cập nhập vào 1 sheet. Các bước trên chỉ thực hiện 1 lần. Ngày hôm sau, nếu có các file mới. Các bạn chỉ việc download và bỏ vào folder với tên file không đổi so với ngày hôm trước, xóa file cũ đi. Để báo cáo được cập nhập.
Bước 5: Cập nhập các file mới nhận được hàng ngày bằng cách
Data --> Refresh All --> Refresh All
Vậy là toàn bộ các file mà hàng ngày bạn phải copy thủ công vào từng sheet đã được cập nhập tự động. Các thao tác thiết lập ban đầu chỉ làm 1 lần duy nhất. Việc này sẽ tiết kiệm được rất nhiều thời gian cho các bạn
Các cách làm việc trên ứng dụng Power Query trong Excel để tự động hóa dữ liệu. Vậy để học Power Query ở đâu? Tại emcanbaove.edu.vn chúng tôi có các khóa học từ cơ bản đến nâng cao cho các bạn, nhằm tự động hóa dữ liệu. Vui lòng tham khảo khóa học nền tảng của chúng tôi.
Làm sao để học power, học power bi ở đâu hiệu quả
Cách tổng hợp dữ liệu từ nhiều file Excel vào 1 file không cần mở file
Tương tự như cách làm phía trên, bạn cũng có thể làm các thao tác tương tự để có thể . Thay vì hàng ngày phải thực hiện các thao tác thủ công như mở file nhận được --> copy --> paste vào từng sheet của 1 file để chạy công thức. Thì việc thiết lập như các bước phía trên đã giúp các bạn tiết kiệm được rất nhiều thời gian.
Xem thêm: Những Hình Ảnh Tình Yêu Buồn Cô Đơn Khi Chia Tay Đẹp Nhất, 99++ Hình Ảnh Buồn Đăng Facebook Cực Deep
Như vậy là emcanbaove.edu.vn vừa hướng dẫn đến bạn cập nhật dữ liệu từ nhiều file excel về một file excel duy nhất ( tổng hợp dữ liệu từ nhiều sheet trong excel). Chúc bạn thực hiện thành công. Vui lòng liên hệ với chúng tôi, để được tư vấn cụ thể. Giải đáp các thắc mắc và giúp các bạn đến với các khóa học phù hợp với ngành nghề, lĩnh vực và khả năng của các bạn.