Tổng hợp dữ liệu từ nhiều sheet

[maixuanvuong276]

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. Chân thành cảmơnạ!

 

[Maika8008]

khó sử dụng lắm + chạy chậm
ADODB viết cho nó 1 hàm chọn File bất kỳ là lấy hết thôi
chọn file bất kỳ ko biết trước tên Sheet tổng hợp lại là xong

Đâu phải công cụ mạnh là chạy nhanh đâu bạn nhỉ!

 

[ngocphuong0612]

Nhờ các cao nhân giúp mình gộp dữ liệu từ File 1 – Sheet “File tổng” sang File 2 – Sheet “BC chi tiết” – “CN VBTT”:

• Dữ liệu ở file 2 – sheet “BC chi tiết”: Luôn cập nhật dữ liệu mới nhất từ File 1
• Dữ liệu ở File 2 – Sheet “ CN VBTT”: Lấy toàn bộ dữ liệu của sheet “BC chi tiết” nếu có phát sinh số lần CN

Em làm công thức mảng nhưng mỗi lần lưu thì mất rất nhiều thời gian ạ

 

[Kiều Mạnh]

Đâu phải công cụ mạnh là chạy nhanh đâu bạn nhỉ!

rảnh đang hỏi sơ bộ vài thứ xem nó siêu việt cỡ nào đó mà

Ít hôm nữa rảnh xem tình hình sao viết thêm 1 Hàm phụ là lấy 3 CSDL : Access + Excel + SQLite thôi ... còn Ms Server + xx cũng thế có điều tách riêng nó ra vì liên quan tới Ip + Port còn lại nó giống như nhau cả vì làm biếng thêm Optional cho dài dòng + rối ra đó mà

cơ bản hàm đã có sẳn trong Cái API mới úp GPE đó ... chỉ viết thêm Hàm Phụ duyệt Sheetname tới đâu gán nó vào tới đó là Xong

 

[Cá ngừ F1]

đang xem ... hình như nó hơi khó sử dụng + chạy chậm thì phải

View attachment 265787

Ư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

Bài đã được tự động gộp: 10/9/21

Làm sao để lấy được đường dẫn Full File Name của Workbook đang chạy code M vậy bạn? Tôi tìm hoài không thấy lệnh.

Mục đích: Gửi File này cho bất cứ ai cũng chạy được. Không phải thay lại Source (Source đang lấy là Full File Name của File excel đang chạy PQ)

Cảm ơn!

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.

 

[Kiều Mạnh]

Không hiểu lắm trong List có vài Files mà trên Sheet nó lặp lại nhiều thế
Mở lên thôi nó laod cũng chậm ... nó load cả .Net nữa hèn chi chậm

 

[Cá ngừ F1]

Cụ thể tôi cần thay cụm này:

Source = Excel.Workbook(File.Contents("D:\Excel\Query\File Test PowerQuery.xlsx"), null, true),

Cụm: "D:\Excel\Query\File Test PowerQuery.xlsx" này chính là Full Name của File Excel đang chạy PQ

Nếu đặt name trong File thì có thể xài công thức để lấy ra cụm trên: nhưng không biết kết hợp thay thế vào như nào

Name là: TenFile =SUBSTITUTE(LEFT(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1),1)-1),"[","")

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.

Bài đã được tự động gộp: 10/9/21

Không hiểu lắm trong List có vài Files mà trên Sheet nó lặp lại nhiều thế
Mở lên thôi nó laod cũng chậm ... nó load cả .Net nữa hèn chi chậm

View attachment 265806

thêm trường đó để biết dữ liệu lấy từ File nào đó anh.

 

[ptm0412]

Không hiểu lắm trong List có vài Files mà trên Sheet nó lặp lại nhiều thế
Mở lên thôi nó laod cũng chậm ... nó load cả .Net nữa hèn chi chậm

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:


Step FileNameList cũng chỉ thấy 2:



Đặ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.

 

[ptm0412]

Nói thêm: Trong file ở chủ đề này: https://www.giaiphapexcel.com/diendan/threads/tổng-hợp-nhiều-file-thành-1-file-bằng-power-query.156768/post-1040037
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.



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.

 

[tranhungdao12a3]

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 đó.

 

[Kiều Mạnh]

Nói thêm: Trong file ở chủ đề này: https://www.giaiphapexcel.com/diendan/threads/tổng-hợp-nhiều-file-thành-1-file-bằng-power-query.156768/post-1040037
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.

View attachment 265827

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.

có lẻ sức mạnh của nó là nạp dữ liệu vào Data model và khi cần thì phân tích nó thôi ... còn lấy dữ liệu thông thường VD như tổng hợp Sheet + Files mà ko biết tên Sheet thì ADOB làm ok hết + tốc độ sẻ nhanh hơn đấy

VD: tạo 1 Button nhấn nút chọn 1 File bất kỳ ... thì code đơn gian lắm nó lấy lên hết + rất nhanh
Còn Power Query sẻ mất công chỉnh sửa + vvv xong mới thực hiện được

 

[ptm0412]

có lẻ sức mạnh của nó là nạp dữ liệu vào Data model và khi cần thì phân tích nó thôi ... còn lấy dữ liệu thông thường VD như tổng hợp Sheet + Files mà ko biết tên Sheet thì ADOB làm ok hết + tốc độ sẻ nhanh hơ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.

 

[Kiều Mạnh]

Đồ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.

đồng ý với anh cái khúc Em tô đậm thôi
còn chỉnh sửa các hàm M em thấy chưa phổ biến lắm ít nhất là thời điểm hiện tại trên GPE này
và người dùng phổ thông rất khó khi vận dụng nó

Còn ADODB câu SQL nó vô cùng lắm ... trên GPE này e rằng chưa có ai dám khẳng định là mình làm được tất cả thuần SQL cho ADODB

 

[Cá ngừ F1]

đồng ý với anh cái khúc Em tô đậm thôi
còn chỉnh sửa các hàm M em thấy chưa phổ biến lắm ít nhất là thời điểm hiện tại trên GPE này
và người dùng phổ thông rất khó khi vận dụng nó

Còn ADODB câu SQL nó vô cùng lắm ... trên GPE này e rằng chưa có ai dám khẳng định là mình làm được tất cả thuần SQL cho ADODB

Em không rành về ADO, chỉ áp dụng mấy hàm mà các tiền bối đã xây dựng. Nên thấy cấu trúc của ADO hơi khó hiểu, làm trên Power Query thấy đơn giản hơn.

 

[tranhungdao12a3]

đồng ý với anh cái khúc Em tô đậm thôi
còn chỉnh sửa các hàm M em thấy chưa phổ biến lắm ít nhất là thời điểm hiện tại trên GPE này
và người dùng phổ thông rất khó khi vận dụng nó

Còn ADODB câu SQL nó vô cùng lắm ... trên GPE này e rằng chưa có ai dám khẳng định là mình làm được tất cả thuần SQL cho ADODB

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.

 

[Kiều Mạnh]

Em không rành về ADO, chỉ áp dụng mấy hàm mà các tiền bối đã xây dựng. Nên thấy cấu trúc của ADO hơi khó hiểu, làm trên Power Query thấy đơn giản hơn.

ADODB chỉ khó nhất cái câu lệnh SQL thôi nó vô cùng lắm
còn lại không khó lắm chỉ có cái Conn và Rs thôi ... cơ bản mọi cái trên GPE này có hết rồi
quan trọng là có biết vận dụng nó hay không thôi

Rảnh mạnh sẻ viết thêm 1 hàm API chọn 1 Files bất kỳ là nó tổng hợp hết dữ liệu có trong File đó gán nối xuống trên 1 Sheet ... mọi cái có hết rồi chỉ là thêm vào chút thôi
Vì xét thấy nhu cầu thực tế trên GPE này lâu lâu có người hỏi bài kiểu đó

Sẻ áp dụng cho 3 CSDL là Access + Excel + SQLite chung vào 1 hàm duy nhất

 

[Maika8008]

ADODB chỉ khó nhất cái câu lệnh SQL thôi nó vô cùng lắm
còn lại không khó lắm chỉ có cái Conn và Rs thôi ... cơ bản mọi cái trên GPE này có hết rồi
quan trọng là có biết vận dụng nó hay không thôi

Câu SLQ 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.

 

[Kiều Mạnh]

@Cá ngừ F1 cái bộ hàm đó rất đơn giản và tiện dụng đấy
Áp dụng cho 3 trong 1 Hàm duy nhất truy xuất 3 CSDL khác nhau
trên GPE này tới thời điểm hiện tại đó là Hàm đầu tiên xuất hiện đấy ... mong là sau này có đầy ra

Chia sẻ thư viện API truy xuất CSDL SQLite - Access - Excel và Ms Server

1/ Rảnh tôi đang thử nghiệm tự tay viết lấy thư viện API cho mình sử dụng khi cần thiết ... Truy xuất CSDL như tiêu đề nêu trên 2/ Úp lên đây cho bạn nào cần thì sử dụng... ko cá nhân hóa hay bất cứ bản quyền gì ... ai muốn sử dụng nó vào mục đích thương mai hay gì đó Mình ko quan tâm Cho đi là...

www.giaiphapexcel.com

 

[ptm0412]

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.

 

[ongke0711]

Theo tôi thì không so sánh ADO và Power Query (PQ) được vì cách thức hoạt động, chức năng nó khác nhau mặc dù có công đoạn nào đó giống nhau như: kết nối CSDL.
- PQ thì lấy dữ liệu và tích hợp sẳn công cụ phân tích xử lý rất mạnh. Nói chung là làm trọn gói. Tôi nghĩ có thể nói PQ, PBI nó giống như một cái ứng dụng (như Ms Access chẳng hạn) hơn là một thư viện ADODB.
- Còn đối với ADO (như cách tôi thường làm) là một lớp trung gian để kết nối với các loại CSDL, thực thi các câu lệnh SQL. Vấn đề là ở việc xử lý các câu lệnh SQL: nếu thực thi hoàn toàn các câu lệnh SQL từ ADO thì tôi chắc chắn không đủ để tổng hợp, truy vấn, phân tích dữ liệu đáp ứng yêu cầu được. Đối với CSDL Access, tôi phải tận dụng sức mạnh Query của nó để tổng hợp ra dữ liệu mình cần, đối với CSDL SQL Server thì viết các store proc, hàm trực tiếp trên SQL Server (dùng ngôn ngữ của nó) và ADO chỉ làm cầu nối, gửi các tham số, yêu cầu SQL Server thực thi rồi lấy kết quả về thôi.
Nói tóm lại để kết nối dữ liệu, thực hiện các truy vấn cơ bản thì ADO làm được nhưng chuyên sâu xử lý thì không. Ngược lại thì PQ nó làm được điều này. Nói thực là tôi chỉ biết PQ ở mức cơ bản nhưng chỉ cần đọc tài liệu là có thể hiểu được khả năng của nó.

 

[excel_lv1.5]

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.

2 môi trường khác nhau, nếu viết full câu lệnh SQL (giả sử request tới SQL server) thì nó xử lý ở trên SQL server, còn đẩy raw về rồi ETL ở power query thì nó là môi trường của power query kết quả trả về là kết quả cuối cùng mặc dù gọi nhiều bảng nhưng chỉ là query chứ không lưu, còn việc xử lý thì chắc chắn môi trường ở sql server sẽ nhanh hơn power query rồi, còn so với môi trường xử lý khác như Access, ODBC tới database của Foxpro thì không chắc Power query thua đâu khi cần clean data ở dạng nhiều,với SQL server thì connect Power query full SLQ sẽ ngon hơn mà thực tế là vậy người ta sẽ viết thẳng cái code rồi gửi request connect luôn, PQ cho phép điều gửi code lập trình SQL thẳng để request, không biết ADO có làm được điều này không tôi đã thử sử dụng tạo bảng tạm và tính toán trên bảng tạm đó để lấy dữ liệu mong muốn bằng ADO nhưng không connect được chưa kể đến loop, ví dụ với câu đơn giản như vầy ADO sẽ báo lỗi, nếu mà không request được dạng này mà chỉ là mấy câu select bình thường ADO thì không thể so ETL được với power query