Tạo Pivot với dữ liệu nguồn tới 5 triệu dòng.

[pqhong]

Chào các anh/chị,

Xin chỉ giúp mình vấn đề tạo pivot với dữ liệu tới 5 triệu dòng.
Hiện nay do dữ liệu quá lớn nên mình chia nhỏ làm rất nhiều sheet.
Mình đã biết tạo pivot với nhiều sheet nguồn nhưng excel rất hạn chế với kiểu pivot từ nhiều sheet.
Có nhiều người khuyên nên chuyển vào access. Nhưng 1 vấn đề là dữ liệu nguồn trên excel có công thức và mình cần giữ nguyên để sau chỉ refresh cái pivot là có báo cáo mới. Do đó khi chuyển vào access thì chỉ mình tạo linked table chứ ko import vì access sẽ ko update.
Tóm lại mình hỏi 2 cái:
1- Làm sao tạo pivot với data hơn 5tr dòng với y/c giữ nguyên công thức trong data nguồn.
2- Làm sao import từ nhiều sheet excel vào 1 new table trong access.

Xin cảm ơn các anh/chị

 

[ndu96081631]

1- Làm sao tạo pivot với data hơn 5tr dòng với y/c giữ nguyên công thức trong data nguồn.

Thế bạn kiểm tra lại xem Excel có thể chứa tối đa là bao nhiêu dòng mà đòi 5 triệu vậy?

 

[Hoàng Trọng Nghĩa]

Thế bạn kiểm tra lại xem Excel có thể chứa tối đa là bao nhiêu dòng mà đòi 5 triệu vậy?

Có thể bạn đó nói rằng, bạn ấy có file Excel 2007 trở về sau, chỉ cần 5 sheet là đã chứa đủ dữ liệu của hơn 5 triệu dòng rồi. Nhưng dùng Pivot cho nhiều sheet như thế thì khủng quá, nên bạn ấy muốn dùng Access để giải quyết đó mà.

 

[ptm0412]

Chào các anh/chị,

Do đó khi chuyển vào access thì chỉ mình tạo linked table chứ ko import vì access sẽ ko update.

Bạn hãy link vào Access thành 5 table, sau đó tạo 1 query Union, và thao tác Pivot table trên query này.

 

[pqhong]

Đúng rồi.
Em có 5 sheet nên đủ chứa 5tr dòng.
Vấn đề là làm pivot từ 5 sheet thì được nhưng ko cho tùy chỉnh dạng báo cáo (layout) nhiều như làm pivot với dữ liệu trên 1 sheet. Em ko biết là do excel hạn chế với trường hợp nhiều sheet hay do em chưa biết sử dụng như thế nào.
Em có đọc nhiều bài cùng chủ đề này thì thấy khuyên là nên đưa vào access. Nhưng khi đưa vào access rồi thì gặp vấn đề là em ko biết tạo 1 table bao gồm dữ liệu của cả 5 sheet đó và làm sao cho dữ liêu giữ nguyên công thức như trong excel hoặc tự động update nếu excel thay đổi.

Bạn hãy link vào Access thành 5 table, sau đó tạo 1 query Union, và thao tác Pivot table trên query này.

Em đang tự học access nên chưa biết tạo 1 query union. Giả sử em có link thành 5 table với tên lấn lượt từ 1->5 thì tiêp theo em phải làm gì?
Một vấn đề nữa là khi tạo linked table thì phải mở file excel lên. Khi đóng file excel nguồn thì access báo là data nguồn bị mất. Có thể em ko biết tạo link table. Anh chỉ giúp em với.

Em cảm ơn nhiều.

 

[Hai Lúa Miền Tây]

Em đang tự học access nên chưa biết tạo 1 query union. Giả sử em có link thành 5 table với tên lấn lượt từ 1->5 thì tiêp theo em phải làm gì?
Một vấn đề nữa là khi tạo linked table thì phải mở file excel lên. Khi đóng file excel nguồn thì access báo là data nguồn bị mất. Có thể em ko biết tạo link table. Anh chỉ giúp em với.

Em cảm ơn nhiều.

Bạn đưa 1 ít dữ liệu của cái file có chứa 5 sheets đó và cái form báo cáo lên xem thử nhé.

 

[pqhong]

Bạn đưa 1 ít dữ liệu của cái file có chứa 5 sheets đó và cái form báo cáo lên xem thử nhé.

Đây là 1 ít data a dang báo cáo.
Mọi người giúp em nhé.
Ai biết tạo query union thì giúp em với

Em cảm ơn.

 

[ptm0412]

Bạn xem file và thử thao tác Pivot table trên access

 

[Hai Lúa Miền Tây]

Đây là 1 ít data a dang báo cáo.
Mọi người giúp em nhé.
Ai biết tạo query union thì giúp em với

Em cảm ơn.

Cho mình hỏi thêm là trong 4 sheet đó sau khi bạn tổng hợp lại dữ liệu có tổng cộng bao nhiêu dòng nếu nhỏ hơn 1 triệu dòng thì làm thẳng trên Excel luôn.

 

[ndu96081631]

Cho mình hỏi thêm là trong 4 sheet đó sau khi bạn tổng hợp lại dữ liệu có tổng cộng bao nhiêu dòng nếu nhỏ hơn 1 triệu dòng thì làm thẳng trên Excel luôn.

Ý của tác giả là vầy:

Vấn đề là làm pivot từ 5 sheet thì được nhưng ko cho tùy chỉnh dạng báo cáo (layout) nhiều như làm pivot với dữ liệu trên 1 sheet.

Tức là sau khi ra được cái PivotTable (5 sheet), tác giả không có nhiều lựa chọn thay đổi cách bố trí như khi dữ liệu nằm trong 1 sheet

 

[Hai Lúa Miền Tây]

Ý của tác giả là vầy:

Tức là sau khi ra được cái PivotTable (5 sheet), tác giả không có nhiều lựa chọn thay đổi cách bố trí như khi dữ liệu nằm trong 1 sheet

Ý em là nếu dữ liệu tổng hợp lại (Group) thì nếu số lượng dòng < 1triệu thì có thể dùng ado trên đó luôn.

 

[ndu96081631]

Ý em là nếu dữ liệu tổng hợp lại (Group) thì nếu số lượng dòng < 1triệu thì có thể dùng ado trên đó luôn.

Thì chắc chắn là vậy rồi (nếu không thì làm sao mà PivotTable)
Mình vẫn e rằng ADO không đáp ứng được nhu cầu linh hoạt như PivotTable (khi người ta thay đổi layout báo cáo thì ADO sẽ làm sao?)

 

[Hai Lúa Miền Tây]

Thì chắc chắn là vậy rồi (nếu không thì làm sao mà PivotTable)

Vậy ta có thể dùng ado để nối 5 sheet đó lại, xong group dữ liệu của 5 sheet rồi sau cùng là ghi xuống sheet.

 

[ndu96081631]

Vậy ta có thể dùng ado để nối 5 sheet đó lại, xong group dữ liệu của 5 sheet rồi sau cùng là ghi xuống sheet.

Vấn đề là biết "Group" thế nào?
Cái vụ "group" ấy chỉ có được sau khi người ta quyết định thay đổi layout báo cáo mà (khi người ta dùng chuột kéo thả các Field thì layout đã thay đổi)
Nếu PivotTable có thể lấy dữ liệu từ mảng thì.. xong chuyện rồi

 

[Hai Lúa Miền Tây]

Vấn đề là biết "Group" thế nào?
Cái vụ "group" ấy chỉ có được sau khi người ta quyết định thay đổi layout báo cáo mà (khi người ta dùng chuột kéo thả các Field thì layout đã thay đổi)
Nếu PivotTable có thể lấy dữ liệu từ mảng thì.. xong chuyện rồi

Ý em là group trong ado ấy chứ không phải pivot, là vầy: Nối 5 sheet đó vào thành 1, dùng tiếp 1 cái câu lệnh để group cột tháng, store và tổng 2 cột giá trị và số kg. Sau cùng là ghi dữ liệu xuống sheet ấy.

 

[pqhong]

Ý của tác giả là vầy:

Tức là sau khi ra được cái PivotTable (5 sheet), tác giả không có nhiều lựa chọn thay đổi cách bố trí như khi dữ liệu nằm trong 1 sheet

Đúng vậy ạ.
Có data mà ko cho tùy chỉnh layout thì còn gì sức mạnh của pivot nữa.
Em đang xem file của ptm0412 nhưng cái union query ko chạy.
pmt0412 xem lại giúp em nhé. Em down về rồi extract ra 2 file và mở database11. Lỗi báo như sau:
Em cảm ơn

 

[Hai Lúa Miền Tây]

Đúng vậy ạ.
Có data mà ko cho tùy chỉnh layout thì còn gì sức mạnh của pivot nữa.
Em đang xem file của ptm0412 nhưng cái union query ko chạy.
pmt0412 xem lại giúp em nhé. Em down về rồi extract ra 2 file và mở database11. Lỗi báo như sau:View attachment 102782
Em cảm ơn

Bạn phải link lại mới được vì đường dẫn khác nhau nên không mở được.

 

[pqhong]

Em cảm ơn.

Để em thử lại. Tại em mới làm quen access nên mong mọi người thông cảm.

 

[ptm0412]

Đừng link lại thông thường, hãy vào tab External data, nhấn nút link table manager và sửa trong đó.
Nếu làm lại từ đầu thì không khéo cái Union cũng không chạy vì table nguồn bị thay đổi

 

[pqhong]

Đừng link lại thông thường, hãy vào tab External data, nhấn nút link table manager và sửa trong đó.
Nếu làm lại từ đầu thì không khéo cái Union cũng không chạy vì table nguồn bị thay đổi

Em cảm ơn ptm0412

Em đã làm được rồi.
Giờ vào excel chạy thử pivot từ query xem sao.
Em có 1 câu hỏi nữa là: cái pivot tạo ra chạy như rùa (vì link tới query, query lại link tới data nguồn, data nguồn thì có công thức) thậm chí treo máy. vậy mọi người giúp em tạo 1 macro như sau:
1-sẽ chép công thức vào nhiều cell chỉ định (trong các file excel chưa mở mà chỉ định trong 1 folder).
2-Rồi copy down xuống hết dòng. Sau đó paste value vào chính vị trí đã copy.
Mục đích là chuyển tất cả về dạng value để sau đó cái pivot chạy nhẹ hơn.

Em cảm ơn