-
09-13-2006, 10:20 PM #1Junior Member
- Ngày tham gia
- Aug 2015
- Bài viết
- 0
Dùng VBA trong Excel để tạo và sửa chữa PivotTable
Chức năng PivotTable là chức năng mạnh của Excel, nó giúp bạn tổng kết số liệu nhanh một cách kinh ngạc. Chức năng này đầu tiên xuất hiện trong Excel 5.
Tôi cho rằng các bạn đã làm quen với việc tạo và sửa chữa PivotTable bằng cách thủ công và bài viết này sẽ hướng dẫn dùng VBA để tạo và sửa chữa PivotTable một cách linh động. Bài viết sử dụng cho Excel 2000.
Giả sử ở sheet1, tôi có khối dữ liệu cần phân tích như hình1. Khối dữ liệu này gồm các trường: SalesRep (đại diện bán hàng), Region (Vùng), Month (Tháng), Sales (doanh số bán).
Trước khi tạo bảng PivotTable như hình 2, tôi đã chọn Record New Macro... như hình 3, để xem đoạn mã được ghi lại như thế nào.
Hình 3
Sau đó tôi vào màn hình VBE bằng cách nhấn tổ hợp phím Alt + F11. Tôi vào Module1, thấy được đoạn mã như sau:
Mã:Sub Macro1() Macro1 Macro Macro recorded 17/03/2003 by Duyet Range("A1:D13").Select ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _ "Sheet1!R1C1:R13C4").CreatePivotTable TableDestination:=Range("A1"), _ TableName:="PivotTable1" ActiveSheet.PivotTables("PivotTable1").SmallGrid = False ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="SalesRep", _ ColumnFields:="Month", PageFields:="Region" ActiveSheet.PivotTables("PivotTable1").PivotFields("Sales").Orientation = _ xlDataField End Sub
Region :Là trường page trong PivotTable.
SalesRep: Là trường row trong PivotTable.
Month: Là trường column trong PivotTable.
Sales: Là trường data trong PivotTable sử dụng hàm Sum
Khảo sát đoạn mã đã được ghi:
Để khảo sát đoạn mã trên bạn cần phải biết một số đối tượng liên quan. Tất cả các đối tượng này đều được giải thích trên online help.
PivotCaches:là tập hợp các đối tượng PivotCache trong đối tượng Workbook
PivotTables: là tập hợp các đối tượng PivotTable trong đối tượng Workbook
PivotTableFields: là tập hợp các trường trong đối tượng PivotTable
Create PivotTable: một phương thức của đối tượng PivotCache để tạo một PivotTable sử dụng dữ liệu trong một PivotCache
Ta có thể viết lại thủ tục trên bằng thủ tục CreatePivotTable (chú ý bạn nhập thủ tục này vào module1) sau đây, có thể nó hơi dài nhưng sẽ dễ hiểu hơn, và bạn có thể chạy chương trình bất cứ đâu bằng cách nhấn tổ hợp phím Alt + F8, sau đó chọn thủ tục CreatePivotTable và chọn Run như hình 4.View more random threads:
- Excel - Diễn Giải
- đặt name range bằng code
- Code lọc dữ liệu trong excel
- VBA Copy file và đổi tên file từ thư mục này qua thư mục khác
- Chạy tự động macro khi mở file excel & Sử dụng Visual Basic tô màu ô tương ứng khi double click cell
- tác giả phần mềm kế toán
- Xử lý giúp Lỗi 40040 trong Excel 2003
- Đọc số thành chữ tự động nhận dạng bảng mã
- Hợp nhất nhiều File vào 1 file
- Dùng hàm cơ sở dữ liệu để thống kê nhân sự theo độ tuổi, thâm niên & học vấn (hỗ trợ bỡi VBA)
-
09-13-2006, 10:26 PM #2Junior Member
- Ngày tham gia
- Nov 2015
- Bài viết
- 0
Mã:Sub CreatePivotTable() Dim PTCache As PivotCache Dim PT As PivotTable Application.ScreenUpdating = False Xoa PivotSheet neu no ton tai On Error Resume Next Application.DisplayAlerts = False Sheets("PivotSheet").Delete On Error GoTo 0 Tao Pivot Cache Set PTCache = ActiveWorkbook.PivotCaches.Add _ (SourceType:=xlDatabase, _ SourceData:=Sheets("Sheet1").Range("A1").CurrentRegion.Address) Tao worksheet moi va dat ten Worksheets.Add ActiveSheet.Name = "PivotSheet" Tao Pivot Table tu Cache Set PT = PTCache.CreatePivotTable _ (TableDestination:=Sheets("PivotSheet").Range("A1"), _ TableName:="PivotTable1") With PT Them cac truong .PivotFields("Region").Orientation = xlPageField .PivotFields("Month").Orientation = xlColumnField .PivotFields("SalesRep").Orientation = xlRowField .PivotFields("Sales").Orientation = xlRowField Application.ScreenUpdating = True End With End Sub
Nếu chú ý, bạn sẽ thấy sự khác biệt của 2 đoạn mã trên. Trong Macro1 khi sử dụng phương thức Add để tạo PivotCache thì SourceData là "Sheet1!R1C1:R13C4" còn trong đoạn mã tôi viết là Sheets("Sheet1").Range("A1").CurrentRegion.Address . Ở đây tôi dùng thuộc tính Current Region, có nghĩa là dữ liệu chúng ta sử dụng dựa trên vùng hiện tại xung quanh ô A1. Điều này để chắc chắn thủ tục CreatePivotTable vẫn tiếp tục làm việc tốt khi chúng ta thêm vào dữ liệu.
Bây giờ giả sử tôi có thêm trường Target (chỉ tiêu) trong khối dữ liệu, và trong PivotTable tôi sẽ đưa thêm trường target vào đồng thời cũng thêm trường tính toán Variance. Trường này (Variance) sẽ bằng Sales - Target. Khối dữ liệu mới của tôi như hình 6.
Đoạn mã trong thủ tục CreatePivotTable trên sẽ được thêm như sau (tôi chỉ thêm trong đoạn With PT ....End With):
With PT
Them cac truong
.PivotFields("Region").Orientation = xlPageField
.PivotFields("Month").Orientation = xlColumnField
.PivotFields("SalesRep").Orientation = xlRowField
.PivotFields("Sales").Orientation = xlDataField
.PivotFields("Target").Orientation = xlDataField
Them truong tinh toan
.CalculatedFields.Add "Variance", "=Sales - Target"
.PivotFields("Variance").Orientation = xlDataField
Thay doi caption
.PivotFields("Sum of Sales").Caption = "Sales ($) "
.PivotFields("Sum of Target").Caption = "Target ($) "
.PivotFields("Sum of Variance").Caption = "Variance ($) "
End With
Sau khi chạy lại thủ tục trên tôi sẽ được như hình 7.
Giả sử bây giờ dữ liệu của tôi gồm 6 tháng (hình 8-), tôi muốn đưa thêm cột tổng theo từng 3 tháng. Tôi phải sửa lại đoạn mã của mình như sau:
With PT
Them cac truong
Mã:.PivotFields("Region").Orientation = xlPageField .PivotFields("Month").Orientation = xlColumnField .PivotFields("SalesRep").Orientation = xlRowField .PivotFields("Sales").Orientation = xlDataField .PivotFields("Target").Orientation = xlDataField
Mã:.CalculatedFields.Add "Variance", "=Sales - Target" .PivotFields("Variance").Orientation = xlDataField Them muc tinh toan .PivotFields("Month").CalculatedItems.Add "Q1", _ "= thang 1 + thang 2 + thang 3" .PivotFields("Month").CalculatedItems.Add "Q2", _ "= thang 4 + thang 5 + thang 6"
Di chuyen cac muc tinh toan
Mã:.PivotFields("Month").PivotItems("Q1").Position = 4 .PivotFields("Month").PivotItems("Q2").Position = 8 Thay doi caption .PivotFields("Sum of Sales").Caption = "Sales ($) " .PivotFields("Sum of Target").Caption = "Target ($) " .PivotFields("Sum of Variance").Caption = "Variance ($) " End With
Sau khi chạy lại thủ tục CreatePivotTable tôi sẽ được kết quả như hình 9.
Vâng, đến đây các bạn thấy đó, nếu chúng ta biết sử dụng VBA thì công việc phân tích dữ liệu sẽ trở nên đơn giản hơn. Ngoài ra, ta cũng có thể tạo một PivotTable từ nguồn dữ liệu bên ngoài như Access chẳng hạn. Để lập trình PivotTable được tốt, các bạn nên đọc phần online help của Excel về các đối tượng, phương thức, thuộc tính đã đề cập ở trên.
Hy vọng rằng bài viết trên sẽ giúp các bạn một phần nào trong công việc.
Lê Văn Duyệt
(PC World)
-
01-26-2013, 04:40 AM #3Junior Member
- Ngày tham gia
- Nov 2015
- Bài viết
- 0
Ðề: Dùng VBA trong Excel để tạo và sửa chữa PivotTable
Tạo PivotTable áp dụng cho tất cả các phiên bản Office
[CODE]
Sub THGiaymoi()
Dim PTCache As PivotCache
Dim PT As PivotTable
Application.ScreenUpdating = False
'Xoa Sheet "TONGHOPGiaymoi" có Pivot néu nó ton tai
On Error Resume Next
Application.DisplayAlerts = False
Sheets("TONGHOPGiaymoi").Delete
On Error GoTo 0
'Láy Source de tao Pivot Cache
Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:=Sheets("Giaymoi").Range("A5").CurrentR egion.Address)
'Tao worksheet moi va dat ten
Worksheets.Add
ActiveSheet.Name = "TONGHOPGiaymoi"
'Tao Pivot Table tu Source
Set PT = PTCache.CreatePivotTable(TableDestination:=Sheets( "TONGHOPGiaymoi").Range("B3"), TableName:="PivotTable1")
With PT
'Them cac truong
.PivotFields("TT").Orientation = xlRowField
.PivotFields("GHI CHU").Orientation = xlColumnField
Application.ScreenUpdating = True
End With
End Sub
Mã:Giải thích: - THGiaymoi: là tên Macro - TONGHOPGiaymoi: là tên sheet được tạo mới khi chạy Macro, các bạn muốn tên gì khác thì thay chỗ này. - Sheets("Giaymoi"): Giaymoi là tên sheet được lấy dữ liệu, các bạn muốn lấy dữ liệu từ sheet nào thì thay chỗ này, khi chạy Macro nó tự động lấy dữ liệu vào PiVotTable. - Range("A5"): là dòng chứa tiêu đề của sheet cần lấy dữ liệu. - Range("B3"): là địa chỉ bắt đầu tạo PiVotTable từ sheet được tạo mới khi chạy Macro. Lưu ý: - Khi chạy Macro có tên THGiaymoi thì sheet mới được tạo ra là TONGHOPGiaymoi có chứa PiVotTable. - Tại B4: chứa dòng TT. - Tại C3: chứa cột GHI CHU. - Dòng tiêu đề và vùng chứa dữ liệu tất cả các Cell không được nhập (Merge and center), nếu bất kỳ một chỗ nào bị nhập thì không thể tạo được PivotTable, thứ hai, các dòng tổng cộng phải xóa bỏ nếu không số liệu sẽ tổng hợp 2 lần. Click vào vùng chứa PiVotTable và bằng cách rê thả từ PiVotTable Field List thì bạn sẽ được kết quả tổng hợp, nếu các bạn nào chưa biết cách sử dụng PiVotTable thì tìm đọc bài tại địa chỉ sau: http://www.**************/forum/ui-ng...ao.164623.html
-
01-26-2013, 07:13 AM #4Junior Member
- Ngày tham gia
- Aug 2015
- Bài viết
- 0
Ðề: Dùng VBA trong Excel để tạo và sửa chữa PivotTable
Pivot em thấy ai dùng đc hiệu quá thì quá tuyệt vời.
ps: Nhìn avatar Bác be09 bên GPE sao già giặn thế ạ? :lasao:
-
01-26-2013, 06:26 PM #5Junior Member
- Ngày tham gia
- Aug 2015
- Bài viết
- 0
Ðề: Dùng VBA trong Excel để tạo và sửa chữa PivotTable
Gửi bởi newbee
-
03-29-2013, 05:51 PM #6Junior Member
- Ngày tham gia
- Aug 2015
- Bài viết
- 0
Ðề: Dùng VBA trong Excel để tạo và sửa chữa PivotTable
Bạn nào yêu thích PivotTable thì tải File về tham khảo, ở sheet THVanBanDi tại A2 tôi dùng vaildation để chạy 4 Macro để tổng hợp cho 4 mục đích khác nhau, ở sheet THVanBanDen có 4 nút để chạy Macro để tổng hợp cho 4 mục đích khác nhau. tải File theo Link:
https://www.box.com/s/een0krqnhnx4o1ykkyqf
Hiện nay, trên mạng cũng có rất nhiều diễn đàn mà cánh nam giới chia sẻ với nhau về nghệ thuật bế tinh và coi đó là bí kíp để gìn giữ sức khỏe và tư thế đàn ông. Tuy nhiên, theo giáo sư Trần Quán...
Tư thế yêu mà không xuất của quý ông. Tác hại như thế nào?