Bài viết: Chiêu thứ 54: Sử dụng PivotTable với dữ liệu từ một workbook khác

[kyo]

Chiêu thứ 54: Sử dụng PivotTable với dữ liệu từ một workbook khác


Khi bạn tạo PivotTable trong Excel, bạn có rất nhiều lựa chọn khi chọn nguồn dữ liệu cho nó. Dễ dàng nhất chính là việc bạn sử dụng dữ liệu có sẵn trên workbook. Tuy nhiên, đời không như là mơ và nhiều khi bạn vẫn rơi vào trường hợp là dữ liệu của bạn nằm ở trên một workbook khác.

Sử dụng tên dãy động là một cách rất tốt để giảm thiểu số lần làm mới (refresh) cần thiết cho PivotTable của bạn cập nhật dữ liệu. Bởi vì khi bạn không thể tham chiếu đến tên dãy động từ một workbook khác, điều đó cũng có nghĩa là bạn đã ngăn chặn nguy cơ PivotTable tham chiếu đến hàng ngàn ô rỗng khác cũng như dung lượng file của bạn có thể sẽ tăng lên. Với cách làm này, bạn có thể lấy dữ liệu từ workbook khác và sử dụng nó làm nền tảng dữ liệu cho PivotTable của bạn trên chính workbook đó mà không cần phải tham chiếu từ bên ngoài. Chúng ta cùng tham khảo cách thực hiện nhé. Giả sử dữ liệu của bạn cần như hình dưới đây:

1. Tại workbook sẽ chứa PivotTable, bạn hãy tạo một sheet mang tên Data.
2. Mở workbook chứa dữ liệu mà bạn muốn, và bạn phải bảo đảm rằng sheet chứa dữ liệu của bạn đang được kích hoạt (tức là bạn đang mở sheet đó). Tại một ô trống nào đó trên sheet, bạn hãy gõ công thức =IF(A1="","",A1) với A1 là tiêu đề đầu tiên của dữ liệu bảng.
   
   
   
3. Sau đó hãy cut ô bạn vừa đánh công thức (Ctrl + X) và paste nó (Ctrl + V) qua ô A1 của sheet Data ở workbook sẽ chứa PivotTable của bạn. Với việc này, bạn đã có một liên kết với một workbook khác.
   
   
   
4. Chọn thẻ Formulas -> tại Defined Names, chọn Define Name (với Excel 2003, chọn Insert -> Name -> Define).
5. Tạo một cái tên tại sheet Data với tên là PivotData (gõ tại dòng "Names:” với công thức là =OFFSET($A$1,0,0,COUNTA($A:$A),COUNTA($1:$1)) (gõ tại dòng "Refers To:”) rồi OK.
   
   
   
6. Sau đó, bạn hãy cho đoạn code VBA vào bằng cách nhấn Alt + F11 -> chọn This Workbook.

Private Sub Workbook_Open()
    With Worksheets("Data")
        .Range("2:100").Clear
        .Range("1:1").AutoFill .Range("1:100")
        .Range("2:100") = .Range("2:100").Value
    End With
End Sub

​

7. Sau đó, hãy lưu lại file Excel của bạn.
​

Ở đoạn code trên, bạn có thể thấy con số 100. Đó chính là con số biểu thị dữ liệu tồn tại đến hàng thứ 100. Nếu dữ liệu của bạn ít hơn với con số này, hãy thay đổi cho phù hợp.

Một điều lưu ý rằng, vì bạn đã sử dụng đến macro, hãy bảo đảm rằng bạn đã bật chức năng chạy macro trong Excel, nếu không thì đoạn code của bạn sẽ không thể nào chạy được. Đoạn code bạn vừa thêm vào sẽ tự động chạy mỗi khi bạn mở file. Và nếu như bạn muốn, bạn có thể giấu sheet chứa dữ liệu bằng cách nhấn chuột phải vào sheet đó và chọn Hide, hoặc bạn có thể làm theo chiêu thứ 5: "Ẩn sheet sao cho người dùng không thể dùng lệnh unhide để hiện ra" đã được giới thiệu ở trước đó, bạn có thể tham khảo tại đây.

Bây giờ, bạn hãy chọn bất kỳ một ô nào đó mà bạn sẽ đặt PivotTable, chọn thẻ Insert -> chọn PivotTable -> PivotTable -> tại dòng "Select a Table or Range” gõ =PivotData. Với Excel 2003, chọn Data -> PivotTable and PivotChart Report… -> xuất hiện hộp thoại Wizard, chọn Microsoft Excel List or Database tại dòng "Where is the data that you want to analyze?” và nhấn Next -> gõ =PivotData rồi nhấn Finish.

​

MỤC LỤC Những tuyệt chiêu trong Excel
​

Một số bài viết có liên quan:
1/ Chiêu thứ 53: Di chuyển dòng Grand Total của PivotTable
2/ Chiêu thứ 52: Tự động tạo PivotTable
3/ Chiêu thứ 51: Chia sẻ PivotTable nhưng không chia sẻ cấu trúc dữ liệu của nó
4/ Chiêu thứ 49: Nhận biết tên của dãy trong worksheet
5/ Chiêu thứ 48: Sử dụng dãy động cách linh hoạt nhất
6/ Chiêu thứ 47: Tạo dãy có thể mở rộng và thu hẹp
7/ Chiêu thứ 46: Sử dụng tên để tạo hàm tự tạo
8/ Chiêu thứ 45: Sử dụng cùng một cái tên cho dãy ở những worksheet khác nhau
9/ Chiêu thứ 44: Thay địa chỉ dữ liệu bằng tên
10/ Chiêu 43: Thêm các danh sách có sẵn và cả danh sách tự tạo vào menu chuột phải
http://www.giaiphapexcel.com/vbb/content.php?428