Tổng hợp dữ liệu từ nhiều sheet
Nhờ các cao nhân giúp mình gộp dữ liệu từ sheet 1 sang sheet 2, các SP trùng nhau thì cộng dồn lại, kq mong muốn như sheet2 trong file.
2254Chân thành cảmơnạ!
thử cái code này.
Sub gop()
Dim i As Long, lr As Long, dic As Object, a As Long, arr, kq(1 To 1000, 1 To 6), b As Long, dk As String, j As Long
Set dic = CreateObject("scripting.dictionary")
With Sheets("sheet1")
lr = .Range("C" & Rows.Count).End(xlUp).Row
If lr > 2 Then
arr = .Range("B3:G" & lr).Value
For i = 1 To UBound(arr)
dk = arr(i, 1)
If Not dic.exists(dk) Then
a = a + 1
dic.Add dk, a
For j = 1 To 6
kq(a, j) = arr(i, j)
Next j
Else
b = dic.Item(dk)
kq(b, 4) = kq(b, 4) + arr(i, 4)
kq(b, 6) = kq(b, 6) + arr(i, 6)
End If
Next i
End If
End With
With Sheets("sheet2")
lr = .Range("C" & Rows.Count).End(xlUp).Row
If lr > 2 Then
arr = .Range("B3:G" & lr).Value
For i = 1 To UBound(arr)
dk = arr(i, 1)
If Not dic.exists(dk) Then
a = a + 1
dic.Add dk, a
For j = 1 To 6
kq(a, j) = arr(i, j)
Next j
Else
b = dic.Item(dk)
kq(b, 4) = kq(b, 4) + arr(i, 4)
kq(b, 6) = kq(b, 6) + arr(i, 6)
End If
Next i
End If
lr = .Range("j" & Rows.Count).End(xlUp).Row
If lr > 2 Then .Range("J3:O" & lr).ClearContents
If a Then .Range("j3:o3").Resize(a).Value = kq
End With
Set dic = Nothing
End SubTks bạn nhiều, bạn ơi còn Thiếu hàng tính tổng cộng, bạn giúp mình luôn đc hk! cảm ơn bạn trc ạ!
Cái đấy viết công thức vào là được mà.
www.giaiphapexcel.com/diendan/threads/t%E1%BB%95ng-h%E1%BB%A3p-d%E1%BB%AF-li%E1%BB%87u-t%E1%BB%AB-nhi%E1%BB%81u-sheet.157499/
Khóa học SprinGO phù hợp
Khóa học Power PI – Ứng dung trong Nhân sự
TỔNG QUAN KHÓA HỌC: POWER BI CHO NGÀNH NHÂN SỰ Khóa học Power BI cho Nhân sự được thiết kế dành riêng cho các...
Xem khóa học
Thử dùng Power Query nhé.
Kết quả ở sheet PowerQuery.
Click phải chuột vào dùng dữ liệu chọn refresh.
Sư phụ @ptm0412 có riêng một Topic về vấn đề này rồi anh ạ, quan trọng nhất là cấu trúc các sheet phải giống nhau. Hoặc giả sử dữ liệu là các Table nữa thì nối chúng lại với nhau trong PQ cũng sẽ dễ dàng hơn.
http://www.giaiphapexcel.com/diendan/threads/t%E1%BB%95ng-h%E1%BB%A3p-g%E1%BB%99p-nhi%E1%BB%81u-sheet-c%E1%BB%A7a-1-file-excel-nhi%E1%BB%81u-file-trong-folder-b%E1%BA%B1ng-power-query-n%C3%A2ng-cao.156830/
Anh tải mấy file demo đó về rồi đổi đường dẫn, đổi tên tùy thích là được a. Sau đó chọn Data-> Refresh all là được a.
Sao bạn không làm một vòng lặp duyệt qua các Sh để lấy dữ liệu vào mảng.Xin phép bạn tôi sửa lại code như sau:
Đâu phải công cụ mạnh là chạy nhanh đâu bạn nhỉ!
Ưu nhược điểm thì cũng phân tích nhiều rồi anh. Với dữ liệu lớn thì dùng ADO còn theo mệt, chưa kể còn tính toán, tổng hợp, sắp sếp, thiết lập mối quan hệ giữa các bảng
Có thể đặt name đường dẫn file
Bạn đọc topic có trong phần chữ ký của tôi (M function). Bài #8 thì có tham chiếu bằng name.
Không hiểu có phải bạn hỏi ý này.
Tôi gửi bạn 1 File và 1 Folder Source, bạn giải nén ra 1 folder.
Mở File Data, phần Sheet Setting đã có đường dẫn, bạn có thể đổi tên File để thay nguồn. Nguồn tôi đã đặt bằng 1 name. Sang phần Sheet1 rồi refresh. Nó sẽ lấy dữ liệu theo đúng Source.
thêm trường đó để biết dữ liệu lấy từ File nào đó anh.
Trong hình của Kiều Mạnh có 6 files, trong đó 2 file gốc và 4 file copy.
Folder gốc có 2 file:
2259
Step FileNameList cũng chỉ thấy 2:
2260
Đặc điểm là nếu thêm file cùng cấu trúc như hình của Mạnh, khi refresh cũng lấy hết.
Hình thứ 2 kết quả nhiều dòng Data4Sheet.xls và DT là vì file Data4Sheet.xlsx, sheet DT có nhiều dòng dữ liệu. 2 cột này thêm vào là để phân biệt dữ liệu của file nào, sheet nào. Nếu không cần thì trong step DataF, bỏ 2 dòng AddColumn đi.
2261
Nói thêm: Trong file ở chủ đề này:
http://www.giaiphapexcel.com/diendan/threads/tổng-hợp-nhiều-file-thành-1-file-bằng-power-query.156768
Tên file là dữ liệu của tháng nào đó, và của cửa hàng/ chi nhánh nào đó. Tên sheet là dữ liệu của nhóm Sale nào đó, thì cần có 2 cột tên file và tên sheet để lập báo cáo phân tích các kiểu. Chứ dữ liệu tổng hợp lại để đó thì vô nghĩa.
2262
Ngoài ra, sử dụng Power query còn có cái lợi là khi dữ liệu tổng hợp nhiều file nhiều sheet lại mà lớn hơn 1 triệu dòng (thậm chí vài triệu, vài chục triệu) thì có thể không gán xuống sheet mà gán vào data model. VBA, ADO các kiểu không làm được chuyện này.
Đồng ý về điểm tốc độ. Lần đầu tiên mở file sẽ phải load .Net, các lần sau refresh sẽ nhanh hơn 1 chút, nhưng có lẽ vẫn thua ADO.
Power query còn thêm 1 đặc điểm nữa là sau khi load dữ liệu tương đương với ADO xong, các hàm M của PQ cũng còn có thể làm thêm 1 số việc nữa ví dụ như unpivot, thêm cột conditional column, cột luỹ kế, cột đếm có điều kiện, đếm luỹ kế riêng rẽ từng nhóm, … Nếu ADO phải rất rành rẽ câu lệnh SQL mới làm được, hoặc không làm được luôn.
Nếu bạn chập dữ liệu xong, nếu dữ liệu hàng triệu dòng trở lên, bạn dùng Power Query chập dữ liệu rồi tạo connection sau đó nạp nó vào Data Model. Khi này sử dụng Power Pivot để phân tích thì bạn sẽ thấy giá trị phân tích của nó, sẽ hiểu được vì sao microsoft họ đẻ ra cái công cụ này. Chứ mất công chập Power query mà không làm việc gì đến phân tích thì cần gì Power Query, các Tool hay code VBA tổng hợp các anh viết đầy trên diễn đàn đó.
Nhiều người dùng phân tích trong đó có tôi thì cũng không thạo nhiều về M mà chỉ biết các tính năng cơ bản có trên các tab của Power Query. Chủ yếu dùng chập dữ liệu từ các file hoặc folder hoặc get data từ các nguồn khác, chứ việc phân tích thì phân tích trên Power Pivot hoặc Power Bi(+R+Python). Cho nên tính năng của Power Query phần lớn người sử dụng nó để chập dữ liệu, chuẩn hoá dữ liệu thôi. Diễn đàn chủ yếu toàn lên hỏi ăn xổi luôn thì toàn hỏi VBA hoặc công thức Excel chứ mấy người hỏi những thứ khác đâu. Cho nên mấy mục này bắt đầu ở diễn đàn thì mới là sơ khai giai đoạn đầu thôi.
Câu SQL khó mà cứ chịu khó mày mò tí rồi cũng viết được. Mà đã viết được 1 lần thì lần sau thấy dễ hơn. Cái khó và dễ sai nhất của nó là lồng biến vào câu lệnh phức tạp, thêm bớt các dấu nháy đơn, nháy kép muốn phát khùng mà vẫn bị báo lỗi.
Hơi lạc ra khỏi chủ đề này (tổng hợp dữ liệu từ nhiều sheet) 1 chút:
– Power query nếu muốn tạo quan hệ 2 bảng thì phải query 2 bảng đơn trước, rồi mới dùng câu lệnh Merge. Nhiều bảng quan hệ với nhau cũng phải lấy hết xuống rồi merge từng cặp. Còn ADO dùng câu lệnh SQL với cú pháp join … on (left, right hay join tuỳ hoàn cảnh). Một câu lệnh SQL có thể join hết từng ấy bảng, và kết quả chỉ là 1 bảng kết quả cuối cùng.
– Power query dùng Table.Combine đơn thuần cũng phải lấy hết các bảng xuống rồi mới combine, SQL chỉ cần lệnh Union và chỉ kết quả cuối mới đưa xuống. (Hàm tôi viết tổng quát ở những ảnh chụp và trích dẫn bên trên thì không dùng combine, lại phải ứng dụng hàm M theo cách khác.
– SQL nói thật là "vô cùng", thậm chí có thể tạo các bảng ảo và Select trên đó và nhiều thứ khác mà tôi không biết.
Nói chung là người dùng ở 1 trình độ nào đó sẽ thích cái này hơn cái kia, ở trình độ khác thì ngược lại. Thích cái gì cũng nên biết trước ưu nhược điểm của cái đó mà áp dụng cho phù hợp vấn đề đang làm.
Như trên em có nói là em không rành về ADO. Nên em hỏi anh chút. Giả sử dữ liệu 15 ngày đã > 1.2 tr record. Thì ADO có thể collect và group by lại theo các tiêu chí được không anh?
Theo như hình của bạn thì thấy PQ nó dùng luôn cú pháp của SQL Server để viết và gửi lệnh luôn, vậy thì tiện nhỉ.
Đối với ADO, muốn thực hiện câu lệnh trên (câu lệnh SQL cơ bản) thì dùng ADO Command và cung cấp parameter @date, @Year thì cũng lấy dữ liệu về được nhé.
Name đặt ở đâu cũng được chứ anh. Bản chất như nhau. Kể cả name động đi chăng nữa
Như trên em có nói là em không rành về ADO. Nên em hỏi anh chút. Giả sử dữ liệu 15 ngày đã > 1.2 tr record. Thì ADO có thể collect và group by lại theo các tiêu chí được không anh?
Tôi ít sài Name để gọi giá trị ngoài worksheet, thông thường tôi sẽ convert nó thành table rồi đẩy thẳng vào power query, đỡ bước tạo Name. Còn lỗi theo tôi nghĩ là do nó không nhận là value như string hay number mà bắt buộc phải là dạng table, record (range, cell)
Tôi hiểu điều này. PQ (Power query) làm đơn thuần bằng các step lệnh trên giao diện thì phải lấy về đủ các bảng sau đó thiết lập quan hệ và merge, hoặc combine. Và tôi cũng nói thêm rằng nếu không dùng combine thì dùng hàm M khác để thực hiện. Lúc này phải tự viết trong query advanced editor.
Về câu lệnh SQL lấy trực tiếp trên server, thì do tôi không có môi trường SQL server, mà chỉ có môi trường PostGreSQL nên thực hành trên đó để viết tài liệu căn bản. Trong đó tôi chỉ gõ câu lệnh SQL chuẩn bao gồm select lồng, join, groupby, thêm cột đơn giản. Thế là lấy được dữ liệu. Câu SQL đó là câu mà tôi thực hiện trên cửa sổ PostGreSQL thành công, chỉ là cần lấy về Excel để phân tích.
Ảnh sau là trang 132 của tài liệu, câu SQL không phức tạp và không nhiều bảng.
2266
Anh convert thành table thì bản chất nó sinh ra 1 name "Table1", có tiêu đề. Còn đặt name như @Cá ngừ F1 (là làm theo tôi), không có tiêu đề. Chính vì vậy truy xuất sẽ khác nhau: Name không tiêu đề phải truy xuất "Column1" thay vì tiêu đề.
Name định nghĩa cứng trong Name manager, name động bằng hàm offset (hoặc hàm khác) sẽ không được PQ nhận dạng.
– Group by là do cách viết câu lệnh SQL thôi bạn. Tính toán Sum, Count, Average, Min, Max là nhưng cái mặc định trong Group by (Total Query).
– Tổng hợp dữ liệu 1,2 triệu dòng thì ADO làm được nhưng nhanh chậm thì lại đi vào việc so sánh tốc độ lấy dữ liệu. Vừa rồi có bài đề cập rồi đó: ADODB, PQ, Python…
Theo cách tôi làm, không bao giờ dính chết vào một loại công cụ nào đó, qui mô dữ liệu tới đâu mình lựa chọn công cụ phù hợp với nó, nếu ADO chạy ì ạch quá thì tìm cái khác xem có tối ưu hơn không và phù hợp với hệ sinh thái thiết kế của bạn. Vd: Excel giới hạn dòng thì dùng Access làm database; Khi Ms Access database hết 2G dữ liệu thì mình dùng SQL Server làm Back end, dùng Access để thiết kế giao diện Font end thôi. Và đối với tôi ADO chỉ làm cầu nối thôi chứ không dùng thuần ADO để truy vấn dữ liệu (chỉ dùng cho các câu lệnh SQL đơn giản). Dùng tài nguyên máy chủ SQL Server để tổng hợp dữ liệu, dùng tài nguyên máy khách để thực hiện kết nối ADO, tải dữ liệu về.
Các ngôn ngữ xuất hiện để đáp ứng một nhu cầu nào đó và tất nhiên nó sẽ có điểm mạnh ở một phân khúc nào đó, nếu mình có khả năng và cũng đang lập trình ở phân khúc đó thì nên học hỏi thêm để cải tiến. Như tôi hiện giờ, thì khả năng học thêm nữa không cao nên chỉ làm những cái ứng dụng phù hợp với ngôn ngữ mà mình biết mà nó vẫn còn chạy tốt, chứ còn nói tới Big Data, Data Science mà cứ VBA là chết toi. 😀
ADO thì không tự tạo bảng tạm #tempTable trên SQLServer rồi bạn. Như tôi đã nói ADO chạy câu lệnh SQL cơ bản, việc tạo bảng tạm thì ADO chỉ có thể gọi thực thi hàm, thủ tục nội tại trên SQL Server thực hiện thôi.
Khi Name là 1 giá trị đơn ở 1 ô trên sheet, tôi biết rằng đó là table, tôi chỉ nói rằng nó là table không tiêu đề, và bị gán tiêu đề là Column1. Khi truy xuất nó là cú pháp truy xuất table, tên field là Column1, record 0
Và tôi mới test, name động trả về Range vẫn được nhận dạng như bạn bảo. Chắc tôi nhớ nhầm trong 1 trường hợp đạc biệt nào đó.
2268
Giả sử tên file tạo thành 1 table 1 dòng 1 cột ở I1:I2, tiêu đề là "tên file", thì cũng truy xuất record 0, field là [Tên file]
2269
Nếu bảng 2 cột, 3 cột thì truy xuất theo tên field
Nếu bảng 2 dòng, 3 dòng, thì truy xuất theo record 1, record 2 (do bắt đầu từ 0)
Rất xin lỗi thành viên @maixuanvuong276 vì có những trao đổi hơi nhiều về Topic này, thực tế cũng muốn có một giải pháp tốt hơn về việc tổng hợp này của bạn.
Tôi thử làm lại bằng Power Query.
Bản giải nén File đính kèm về máy (để File TongHop và folder Source tại cùng 1 thư mục).
Giả sử các dữ liệu bạn cần phải tổng hợp ở trong Source (tôi có tạo 4 file Data như cấu trúc bạn gửi ở bài #1), tôi nghĩ rằng các file dữ liệu này cần tách hẳn ra thành File riêng, còn lúc tổng hợp sẽ làm ở 1 File khác.
1. Bạn mở File TongHop
2. Ở sheet Setting, ô bôi đỏ là để chọn tương ứng các File bạn cần tổng hợp.
3. Sau khi chọn File nguồn, sang sheet TongHop, click phải chuột chọn Refresh.