pivot table cố định vùng lấy dữ liệu

[uyvu]

Dear diễn đàn
Xin nhờ các bạn giúp đỡ trường hợp này.
Tôi có tạo pivot table trong excel, những khổ nỗi mỗi lần copy file excel sang đường dẫn khác, là nó lại bắt phải chọn lại vùng lấy dữ liệu, mặc dù vùng lấy dữ liệu của pivot không thay đổi (file excel có 2 sheet, 1 sheet dữ liệu của pivot, 1 sheet là pivot table)
File excel này tui đưa rất nhiều người xài, nên có rất nhiều người không biết làm.

Mong diễn đàn hỗ trợ

 

[be_09]

Dear diễn đàn
Xin nhờ các bạn giúp đỡ trường hợp này.
Tôi có tạo pivot table trong excel, những khổ nỗi mỗi lần copy file excel sang đường dẫn khác, là nó lại bắt phải chọn lại vùng lấy dữ liệu, mặc dù vùng lấy dữ liệu của pivot không thay đổi (file excel có 2 sheet, 1 sheet dữ liệu của pivot, 1 sheet là pivot table)
File excel này tui đưa rất nhiều người xài, nên có rất nhiều người không biết làm.

Mong diễn đàn hỗ trợ

Góp ý cho bạn:
1/ Muốn như thế nào thì cần đính kèm File.
2/ Khi đính kèm File thì nên có 1 sheet chứa dữ liệu và 1 sheet là kết quả mong muốn thì may ra có thành viên hiểu mới giúp được.

 

[ptm0412]

Góp ý cho bạn:
1/ Muốn như thế nào thì cần đính kèm File.
2/ Khi đính kèm File thì nên có 1 sheet chứa dữ liệu và 1 sheet là kết quả mong muốn thì may ra có thành viên hiểu mới giúp được.

1. Tình huống là chép file sang thư mục khác thì datasource của Pivot bị mất. Vậy thì lấy file làm gì.
2. Rõ ràng là 1 sheet dữ liệu, 1 sheet là Pivot table của dữ liệu đó và là kết quả (đã có, và không mong muốn kết quả gì khác).

 

[uyvu]

Trước hết cảm ơn anh em diễn đàn rất nhiều
Tình huống tôi bị là như vầy:
Tôi đã làm sẵn file excel (Trong file có 2 sheet: 1 sheet chứa data, 1 sheet chứa pivot) sau đó đưa cho các chi nhánh khác sử dụng
Các chi nhánh khác khi lấy file về chi nhánh mình, sau khi nhập liệu xong, nhấn nút refresh thì nó bắt cập nhật lại data source, mặc dù data source của tôi là nằm trên chính file excel này ở sheet "data"

Kính nhờ diễn dàn giúp đỡ tôi 2 việc:
1. Cố định vùng lấy data source ở sheet "data" (Data!$A$1:$R$1000), cho dù file này có chép đi chỗ nào cũng không bắt phải chọn lại vùng data source nữa
2. Khi nhập liệu ở sheet "data" thì sheet "Analyze" tự refresh số liệu, không cần phải nhấn nút refresh
Note:
Tôi đã gửi kèm file excel


Một lần nữa chân thành cảm ơn diễn đàn

 

[ptm0412]

Chép về máy của tôi thì không bị mất source, có lẽ do khác phiên bản. Tôi đoán là Office 2007 hoặc 2010 bị tình trạng này (máy tôi 2016)
Tuy nhiên bạn tải file dưới đây, có 2 cách khác để lấy nguồn xem có bị lệ thuộc vào địa chỉ thư mục nữa không.

 

[uyvu]

Chép về máy của tôi thì không bị mất source, có lẽ do khác phiên bản. Tôi đoán là Office 2007 hoặc 2010 bị tình trạng này (máy tôi 2016)
Tuy nhiên bạn tải file dưới đây, có 2 cách khác để lấy nguồn xem có bị lệ thuộc vào địa chỉ thư mục nữa không.

Dear anh
Anh nói có 2 cách, mà không nói rõ, tôi coi file anh rồi không hiểu anh làm cách nào, anh diễn đạt rõ hơn giúp tôi
Trong file tôi chỉ thấy, data source thay vì lấy địa chỉ, anh đã đặt tên vùng "data1", rồi gắn vào data source bằng tên vùng này

Mong anh giúp thì giúp cho chót, tôi có nhờ 2 việc, cái thứ 2 này mong anh hỗ trợ luôn
2. Khi nhập liệu ở sheet "data" thì sheet "Analyze" tự refresh số liệu, không cần phải nhấn nút refresh --> Anh có thể viết code VBA

Note:
Trong file gốc tôi gửi có yêu cầu gắn vùng data source là Data!$A$1:$R$1000. Từ dòng 1 đến 1000.
Từ dòng 832 đến dòng 999 là tên của các nhân viên của sheet "Staff List", mỗi nhân viên vậy tôi cho đủ 12 tháng--> mục đích việc làm này là khi pivot các tháng nhân viên đó chưa có số liệu, thì tên nhân viên đó vẫn có trong pivot (vì đã có 1 dòng ảo nhập sẵn bên dưới)

 

[ptm0412]

Data1 là table, Data2 là name động.
Dữ liệu của bạn thấy trống trơn nguyên 1 khúc sau từ 532 trở đi (về mặt cấu trúc dữ liệu theo tôi là không cần làm vậy), mà định dạng thì cho cả 1 sheet 1 triệu dòng, tôi thì ghét kiểu đó nên xóa béng đi. Với cấu trúc table thì nhập liệu lúc nào chẳng được, công thức còn không cần copy xuống nữa.

Tự động refresh thì có đầy, thí dụ code này bỏ vào sheet Analize:

Private Sub Worksheet_Activate()
Sheet2.PivotTables(1).SourceData = "Data1"
Sheet2.PivotTables(1).PivotCache.Refresh
End Sub

 

[uyvu]

Data1 là table, Data2 là name động.
Dữ liệu của bạn thấy trống trơn nguyên 1 khúc sau từ 532 trở đi (về mặt cấu trúc dữ liệu theo tôi là không cần làm vậy), mà định dạng thì cho cả 1 sheet 1 triệu dòng, tôi thì ghét kiểu đó nên xóa béng đi. Với cấu trúc table thì nhập liệu lúc nào chẳng được, công thức còn không cần copy xuống nữa.

Tự động refresh thì có đầy, thí dụ code này bỏ vào sheet Analize:

Private Sub Worksheet_Activate()
Sheet2.PivotTables(1).SourceData = "Data1"
Sheet2.PivotTables(1).PivotCache.Refresh
End Sub

Rất chân thành cảm ơn bác ptm0412 rất nhiều, 2 vấn đề trên xem như đã xử lý ok

Qua làm thực tế, có phát sinh vấn đề này, mong bác hỗ trợ thêm
Khi nhập liệu đến cột R, nhấn tab sẽ trở về cột A để nhập liệu dòng mới, nhưng người dùng thường quen nhấn enter
Mong bác hỗ trợ 2 việc sau:
- Bác có thể hỗ trợ, nhập liệu tới dòng R, nhấn enter hoặc tab đều về cột A
- Về cột A tự gắn số thứ tự rồi, tự nhảy sang cột B

Note:
Mình có tham khảo code này, nhưng không biết cách gắn sự kiện gì, vì trong clip là macro, phải nhấn nút run macro

Excel VBA tip: Tự động đánh số thứ tự - unitrain.edu.vn

Khi làm việc với Excel, chúng ta thường nhập cột số thứ tự vào bảng tính. Ví dụ sẽ hướng dẫn bạn cách ứng dụng VBA để lập Macro để tự động đánh số thứ tự.

unitrain.edu.vn

 

[ptm0412]

Code trong link nhảm quá
FormularR1C1 mà không xài tham chiếu RC, lại dùng Offset
Dữ liệu mẫu tiếng Anh mà tiêu đề cột A lại là STT
Quay lại dữ liệu đã định dạng table:
Tìm 1 công thức đánh số thứ tự (max + 1 hoặc cái gì khác). Table tự fill công thức (kể cả các cột khác)
Còn tab quay về cột đầu thì bắt buộc phải tập lại thói quen, tôi không thích chiều ý người dùng