Tạo Pivot với dữ liệu nguồn tới 5 triệu dòng. (1 người xem)

[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

 

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

Đừ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

Theo ý em thì nên dùng Union All thay vì dùng Union Thầy à.

 

[ptm0412]

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ảm ơn

Chạy pivot từ query đó trong access chứ. Làm trong Excel làm sao chứa nổi 5 tr dòng.

Theo ý em thì nên dùng Union All thay vì dùng Union Thầy à.

Hai Lúa giúp hắn giùm anh đi.

 

[VetMini]

Theo ý em thì nên dùng Union All thay vì dùng Union Thầy à.

UNION ALL chỉ việc chập bên này vào bên kia thành một ds chung.
UNION bắt buộc phải xét để lọc ra các dòng dữ liệu trùng nhau, cho nên [theo lý thuyết] sẽ chậm hơn.

 

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

UNION ALL chỉ việc chập bên này vào bên kia thành một ds chung.
UNION bắt buộc phải xét để lọc ra các dòng dữ liệu trùng nhau, cho nên [theo lý thuyết] sẽ chậm hơn.

Ví dụ nếu như có 2 dòng dữ liệu hoàn toàn giống nhau thì sao bạn? Chẳng lẻ chỉ lấy 1?

 

[pqhong]

Chạy pivot từ query đó trong access chứ. Làm trong Excel làm sao chứa nổi 5 tr dòng.



Hai Lúa giúp hắn giùm anh đi.

Vậy anh HaiLua giúp em nhé.

Em cảm ơn.

 

[VetMini]

Ví dụ nếu như có 2 dòng dữ liệu hoàn toàn giống nhau thì sao bạn? Chẳng lẻ chỉ lấy 1?

UNION là phép giao trong đại số tập hợp (ký hiệu là[SUB]

[/SUB] )

trong toán đại số: A union B = A + B - (A intersect B)

Theo luật của SQL, UNION loại tất cả những phần tử trùng, chỉ giữ lại 1.

UNION ALL là phép gộp tập hợp

A union B tương đương với A + B trong toán đại số.

Theo luật của SQL, UNION ALL gộp tất cả các phần tử của hai tập hợp, không cần biết chúng chứa gì, miễn là chúng có cùng đăc tính (trong ngữ cảnh SQL, phần tử là một dòng, đặc tính của phần tử là các trường)

==== Xin đính chánh một vài chỗ viết nhầm ====

UNION là phép HỘI chứ không phải phép GIAO - phép giao là intersection

Câu "A union B tương đương với A + B trong toán đại số"
Xin chỉnh lai là "A union all B tương đương với A + B trong toán đại số"

 

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

UNION là phép giao trong đại số tập hợp (ký hiệu là[SUB]

[/SUB] )

trong toán đại số: A union B = A + B - (A intersect B)

Theo luật của SQL, UNION loại tất cả những phần tử trùng, chỉ giữ lại 1.

UNION ALL là phép gộp tập hợp

A union B tương đương với A + B trong toán đại số.

Theo luật của SQL, UNION ALL gộp tất cả các phần tử của hai tập hợp, không cần biết chúng chứa gì, miễn là chúng có cùng đăc tính (trong ngữ cảnh SQL, phần tử là một dòng, đặc tính của phần tử là các trường)

Vậy ta dùng union all thì đúng rồi, dùng union có thể bỏ sót dữ liệu trong trường hợp này.

 

[ptm0412]

Vậy anh HaiLua giúp em nhé. Em cảm ơn.

View query dưới dạng SQL rồi thay cái này vào:

Select * from 2
union all
select * from 3
union all
select * from 4
UNION ALL select * from 5;

Còn Pivot table bạn phải tự thử nghiệm theo ý muốn xem có vướng mắc gì không chứ? Tôi xem cái ảnh Pivot mẫu chả liên quan gì đến dữ liệu cả nên không test thay bạn được.

 

[pqhong]

View query dưới dạng SQL rồi thay cái này vào:

Select * from 2
union all
select * from 3
union all
select * from 4
UNION ALL select * from 5;

Còn Pivot table bạn phải tự thử nghiệm theo ý muốn xem có vướng mắc gì không chứ? Tôi xem cái ảnh Pivot mẫu chả liên quan gì đến dữ liệu cả nên không test thay bạn được.

Em đã làm được rồi nhưng máy yếu quá nên ko chạy nổi. Có gần 5tr dòng mà cứ chạy 70% là treo máy. Chán quá. hic

Em cảm ơn.

 

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

Có gần 5tr dòng mà cứ chạy 70% là treo máy. Chán quá. hic

Em cảm ơn.

5 triệu dòng là quá lớn rồi đó bạn.

 

[zorroz]

Bạn nghiên cứu thử PowerPivot, free addin của Microsoft luôn, chuyên cho xử lý dữ liệu lớn như trường hợp của bạn

 

[ptm0412]

Bạn nghiên cứu thử PowerPivot, free addin của Microsoft luôn, chuyên cho xử lý dữ liệu lớn như trường hợp của bạn

Power Pivot cũng bị giới hạn bởi số dòng trên bảng tính

Em đã làm được rồi nhưng máy yếu quá nên ko chạy nổi. Có gần 5tr dòng mà cứ chạy 70% là treo máy. Chán quá. hic

Cái file Excel 4 sheet của bạn, mỗi sheet có hơn chục dòng, bạn chép chung vào 1 sheet Union để giả định là query Union. Lấy sheet này bạn tạo những Pivot table khác nhau theo ý muốn của bạn, rồi gởi lên đây. Nếu muốn group theo tháng, bạn nên cho thêm mỗi sheet vài chục dòng dữ liệu với tháng khác nhau.


Tôi sẽ xem có thể tạo những query thứ cấp ít dòng hơn để chạy nhẹ nhàng hơn, hoặc có thể chạy trực tiếp trên Excel.

Nhớ đừng chụp hình Pivot, chụp hình tôi chẳng làm được gì đâu.

 

[pqhong]

Power Pivot cũng bị giới hạn bởi số dòng trên bảng tính Cái file Excel 4 sheet của bạn, mỗi sheet có hơn chục dòng, bạn chép chung vào 1 sheet Union để giả định là query Union. Lấy sheet này bạn tạo những Pivot table khác nhau theo ý muốn của bạn, rồi gởi lên đây. Nếu muốn group theo tháng, bạn nên cho thêm mỗi sheet vài chục dòng dữ liệu với tháng khác nhau. Tôi sẽ xem có thể tạo những query thứ cấp ít dòng hơn để chạy nhẹ nhàng hơn, hoặc có thể chạy trực tiếp trên Excel. Nhớ đừng chụp hình Pivot, chụp hình tôi chẳng làm được gì đâu.

Em ko gửi được file. Mặc dù đã nén lại có 540kb nhưng site vẫn báo vượt. Em có phải xóa mấy file cũ đã up ở bài trước ko? nếu xóa mấy file cũ thì up lần này sẽ đủ Mọi người hướng dẫn giúp em. Em cảm ơn.

 

[ptm0412]

Tại sheet 5, tô chọn từ dòng 70 đến hết, delete row,
Xóa 2 picture Pivot

chỉ còn 25 Kb

 

[pqhong]

Tại sheet 5, tô chọn từ dòng 70 đến hết, delete row,
Xóa 2 picture Pivot

chỉ còn 25 Kb

Cảm ơn anh
Em đã làm được.
Nhưng dung lượng up của em chỉ cho có hơn 300kb còn lại. Em ko biết làm sao được tăng hạn mức lên.

Mọi người chỉ giúp em với.

 

[ptm0412]

Bạn xem file, có 2 query thứ cấp và số lượng dòng đã giảm:

Query1 Dùng tạo Pivot 1 và Pivot2, giảm rất nhiều do không có tham số ngày. Nếu dữ liệu 1 năm và 5 kho, số dòng tối đa 60.
Query2 dùng tạo Pivot 3, có tham số ngày nên giảm ít hơn. Nếu dữ liệu 1 năm và 5 kho, tối đa 1.825 dòng

Khi đang view dạng Pivot table, nhấn nút Export to Excel trong tab Pivot design, sẽ được 1 file excel 2 sheet.

Lưu ý: Tên field không được đặt là 1 từ khóa, thí dụ "Countif"

 

[pqhong]

Bạn xem file, có 2 query thứ cấp và số lượng dòng đã giảm:

Query1 Dùng tạo Pivot 1 và Pivot2, giảm rất nhiều do không có tham số ngày. Nếu dữ liệu 1 năm và 5 kho, số dòng tối đa 60.
Query2 dùng tạo Pivot 3, có tham số ngày nên giảm ít hơn. Nếu dữ liệu 1 năm và 5 kho, tối đa 1.825 dòng

Khi đang view dạng Pivot table, nhấn nút Export to Excel trong tab Pivot design, sẽ được 1 file excel 2 sheet.

Em cảm ơn anh.
Để em thử.

 

[ptm0412]

Nếu test ok, thì có thể bỏ qua trung gian Access, dùng code VBA và ADO tạo 2 query thứ cấp đó trên Excel.
Cũng có thể dùng mảng và Dictionary.
Có lẽ sẽ nhanh hơn.

 

[pqhong]

Bạn xem file, có 2 query thứ cấp và số lượng dòng đã giảm:

Query1 Dùng tạo Pivot 1 và Pivot2, giảm rất nhiều do không có tham số ngày. Nếu dữ liệu 1 năm và 5 kho, số dòng tối đa 60.
Query2 dùng tạo Pivot 3, có tham số ngày nên giảm ít hơn. Nếu dữ liệu 1 năm và 5 kho, tối đa 1.825 dòng

Khi đang view dạng Pivot table, nhấn nút Export to Excel trong tab Pivot design, sẽ được 1 file excel 2 sheet.

Lưu ý: Tên field không được đặt là 1 từ khóa, thí dụ "Countif"

1-cái field countif là em muốn tính tổng các giao dịch > 0. Nhưng khi làm pivot thì ko biết làm sao nên để cột đó là cái nào > 0 thì cho là 1. Để vào pivot chỉ việc sum lại.
2-pivot3 của em có store mà anh. Cái query2 ko có store thì làm sao tạo được pivot3?
3-em test thử cái union trước với gần 2tr dòng đã đứng máy rồi. 2 cái query thứ cấp cũng lấy data từ union. vậy chắc cũng ko nhanh được hơn anh ạ.

 

[ptm0412]

1-cái field countif là em muốn tính tổng các giao dịch > 0. Nhưng khi làm pivot thì ko biết làm sao nên để cột đó là cái nào > 0 thì cho là 1. Để vào pivot chỉ việc sum lại.
2-pivot3 của em có store mà anh. Cái query2 ko có store thì làm sao tạo được pivot3?
3-em test thử cái union trước với gần 2tr dòng đã đứng máy rồi. 2 cái query thứ cấp cũng lấy data từ union. vậy chắc cũng ko nhanh được hơn anh ạ.

1. Đặt tên lại
2. Thêm trường store vào (view design mode và kéo thả).
3. Chạy query1 hoặc query2 khác với chạy Union. Phải nhanh hơn.

Ngoài ra, còn có các cách sau:

1. Trên Excel tạo 1 sheet get external data là query1 và query2, sau đó tạo Pivot dựa vào sheet này

2. Open query1 hoặc query2 dạng pivot, nhưng không thao tác pivot trên access, mà nhấn nút Export to Excel, sau đó thực hiện Pivot trên Excel

 

[leelc1981]

Minh la newbie - minh cung gap van de tuong tu nhu tren khi lam BCTC bang pivot

Ý 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

Mình đã xem qua file acess multidata, tuy nhiên, thật sự mình không biết cách nào trích xuất query union trên access de thanh 1 table nam tren access data do luon (tuc 1 table sẽ nhận dữ liệu trích xuất này). Nhờ các bạn, các anh chị chỉ dẫn giúp. Chân thành cám ơn! Thank you all, dear!