Khai thác khả năng của Data pivot table

  • Thread starter WhoamI
  • Ngày gửi
W

WhoamI

Cao cấp
Các bác cao thủ Excel share cho em ít kinh nghiệm ứng dụng Pivot table với, em thấy trong A-excel của bác Tuân có mấy cái báo cáo "động" (ấn refresh? sẽ update số liệu sau mỗi lần cập nhật số liệu) được lập bằng Pivot table hấp dẫn quá, em mới chỉ biết ứng dụng để lập mỗi cái Bảng cân đối phát sinh kiểu bàn cờ thôi. Có bác nào cần cái này thì em ti toe luôn!
 
Khóa học Quản trị dòng tiền
H

handung107

Cao cấp
28/8/04
576
15
0
VN
www.giaiphapexcel.com
Hỏi thật nhé, đôi khi cứ nghe nói upload, mình chẳng biết upload bằng cái ngả nào, bạn chỉ giúp với nhé
 
T

Tuanktcdcn

Lão già ham vui
18/6/04
549
52
28
47
Hà Nội
www.bluesofts.net
handung107 nói:
Hỏi thật nhé, đôi khi cứ nghe nói upload, mình chẳng biết upload bằng cái ngả nào, bạn chỉ giúp với nhé
Khi bạn chọn "Đổi sang khung lớn" để ghi bài, dưới của sổ "Chức năng" có nút "Tải file từ máy" bạn chọn nó... là upload.
Trong khung "Quyền hạn của bạn" bạn có 4 quyền, xem có được gửi kèm file không? phải có thì bạn mới upload được.
 
W

WhoamI

Cao cấp
Tuanktcdcn nói:
Em upload lên đi, nếu có hướng dẫn cụ thể thì càng tốt, có gì chúng ta trao đổi.
Em có thể chuyển vấn đề này sang Excel Fans?
Em thử upload cái ví dụ này để mở đầu cho topic vậy.
Mong được T quan tâm, chỉ bảo!?
 

Đính kèm

  • CDPS=pivot taple.zip
    56.4 KB · Lượt xem: 1,114
levanduyet

levanduyet

Welcome
16/10/04
537
19
18
HCM
my.opera.com
Chao cac ban,
Cac ban co the doc bai viet cua minh ve Pivot table de su ly tuy theo nhu cau cua cac ban. (Minh da upload len)
DÙNG VBA TRONG EXCEL ĐỂ TẠO VÀ SỬA CHỮA PIVOT TABLE
Chức năng pivot table là chức năng mạnh của Excel. Chức năng này đầu tiên xuất hiện trong Excel 5. Nó giúp cho bạn tổng kết số liệu nhanh một cách kinh ngạc.

Trong bài viết này tôi giả sử rằng các bạn đã làm quen với việc tạo vào sửa chữa pivot table một cách thủ công và bài viết này viết cho Excel 2000. Tôi sẽ hướng dẫn cho các bạn dùng VBA để tạo và sửa chữa pivot table một cách linh động.

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 pivot table 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.





Sau đó tôi vào cửa sổ màn hình VBE bằng cách nhấn tổ hợp phím Alt + F11. Tôi vào Module1, tôi sẽ thấy được đoạn mã như sau:

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


Với chú ý:

Region là trường page trong pivot table.
SalesRep là trường row trong pivot table.
Month là trường column trong pivot table.
Sales là trường data trong pivot table 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
CreatePivotTable một phương thức của đối tượng PivotCache để tạo một pivot table sử dụng dữ liệu trong một pivot cache

Ta có thể viết lại thủ tục trên như thủ tục CreatePivotTable (chú ý bạn nhập thủ tục này vào trong 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 sau:



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
Khi bạn chạy xong thủ tục trên thì bạn cũng sẽ được một pivot table như ở sheet2, nhưng trong trường hợp này thì là sheet có tên PivotSheet.(Hình5)

Bạn 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 pivot cache 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 rằng 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 của tôi, và trong pivot table 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 sau:



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. Vậy tôi phải sửa lại đoạn mã của mình như sau:



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
' 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
.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, và đế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 của bạn sẽ trở nên đơn giản hơn chỉ cần vài dòng chỉnh sửa mã. Ngoài ra ta cũng có thể tạo một pivot table từ nguồn dữ liệu bên ngoài như Access chẳng hạn. Để cho việc lập trình về pivot table được tốt, tôi đề nghị 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 mà tôi đã đề 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.

Mọi góp ý, xin các bạn gởi về levanduyet@yahoo.com
Lê Văn Duyệt.
 
W

WhoamI

Cao cấp
levanduyet nói:
Chao cac ban,
Cac ban co the doc bai viet cua minh ve Pivot table de su ly tuy theo nhu cau cua cac ban. (Minh da upload len)
DÙNG VBA TRONG EXCEL ĐỂ TẠO VÀ SỬA CHỮA PIVOT TABLE
Chức năng pivot table là chức năng mạnh của Excel. Chức năng này đầu tiên xuất hiện trong Excel 5. Nó giúp cho bạn tổng kết số liệu nhanh một cách kinh ngạc.

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.

Mọi góp ý, xin các bạn gởi về levanduyet@yahoo.com
Lê Văn Duyệt.
Thanhks anh Lê Văn Duyệt rất nhiều về những bài viết VB for Excel. Nhưng thú thật là W chưa thể hiểu được nên chỉ dám save as về nhà để nghiền ngẫm sau. Nếu có thể các bác có thể giúp em tiếp cận với Pivot table từ các bước cơ bản và sẵn có trên Excel không ạ?:
VD: ứng dụng để lập báo cáo công nợ, vật tư theo tháng ..hay theo một số yêu cầu khác...
 
T

Tuanktcdcn

Lão già ham vui
18/6/04
549
52
28
47
Hà Nội
www.bluesofts.net
Cái em đã làm, trong kế toán thế cũng là sáng tạo rồi, cứ theo đà học hỏi này em sẽ làm được nhiều nhiều điều trong kế toán.
Ở Hà Nội, hôm nào mời I cafe em sẽ có những điều em muốn ở trên.
 
Sửa lần cuối:
levanduyet

levanduyet

Welcome
16/10/04
537
19
18
HCM
my.opera.com
Bay gio minh dang rat ban tui bui. Khong the dien giai cu the duoc mong thong cam. Neu duoc cho nao Upload len bai viet va ca hinh anh thi de hieu hon. Co ban nao co the giup minh cho upload cac bai viet khong vay? Minh co cho nhung la do free nen khong an toan va mot so ban o ngoai Bac khong truy cap duoc.
www.levanduyetexcel.netfirms.com
Dang tranh thu, chac co le sau tet minh se co upload bai nhieu hon.
Le Van Duyet
 
H

Hidebody

Guest
21/5/08
11
0
0
43
Hà Nội
Re: PivotTable

expression.PivotTableWizard(SourceType, SourceData, TableDestination, TableName, RowGrand, ColumnGrand, SaveData, HasAutoFormat, AutoPage, Reserved, BackgroundQuery, OptimizeCache, PageFieldOrder, PageFieldWrapCount, ReadData, Connection)

Mình có dùng PivotTableWizard để lấy dữ liệu trong oracle ra:
Dùng phiên bản Microsoft ODBC for Oracle để kết nối:
ConnStr = "driver={Microsoft ODBC for Oracle}; server=ALT; uid=alt_read; pwd=alt_read"
Set conn = CreateObject("ADODB.Connection")
conn.ConnectionString = ConnStr
conn.Open
Dữ liệu lấy từ 2 bảng, được các fields: Hoten, So CMND, ngaysinh
Mình tạo 2 textbox để nhập lọc từ ngày đến ngày cho field: ngaysinh
Cmd_loc của mình phải làm thế nào để biến đổi kết quả được nhỉ?
Tóm lại bằng hữu nào biết cho mình code kết nối với Oracle theo phiên bản trên không ạh?
Thanks
 
H

Hidebody

Guest
21/5/08
11
0
0
43
Hà Nội
Xin cho mình hỏi về vấn đề nếu dùng PivotTableWizard trên VB để kết nối với oracle thì SourceType, SourceData, Connection có cấu trúc như thế nào? Mình dùng "Microsoft ODBC for Oracle". Ai làm rồi chỉ mình với! mình search hoài trên MSDN mà không thấy cái example nào cả! Lý Bí quá!
 
adam_tran

adam_tran

Guitar inspiration
17/5/05
1,374
36
48
46
Goooogle
Bạn thử qua forum www.giaiphapexcel.com xem, bên đó nhiều cao thủ hơn. ADO for specification thì thuộc dạng kiến thức cao cấp của Excel rồi
 

Xem nhiều

Webketoan Zalo OA