- Tham gia
- 4/11/07
- Bài viết
- 14,381
- Được thích
- 37,027
- Donate (Momo)
- Giới tính
- Nam
- Nghề nghiệp
- Consultant
Áp dụng VBA vào Pivot table để lập báo cáo theo mẫu quy định
Như đã nói trong sách Pivot Table 2003, Pivot Table là 1 công cụ rất mạnh để phân tích, nhưng để nguyên Pivot table thì không in ra như 1 báo cáo đúng chuẩn được.
Hôm nay tôi viết bài này, giới thiệu 1 số thủ thuật trong đó có dùng VBA để định dạng Pivot table lại thành 1 báo cáo hoàn chỉnh có thể in ra được.
Có thể dùng VBA cho đối tượng Pivot Table trong các việc:
- Refresh
- Filter trường Page
- Filter trường Row
- Filter trường Column
- Hoán vị trường trong dòng, cột
- Thêm bớt trường vào dòng, cột
- Dời trường từ dòng sang cột, hoặc Page
- Thay đổi dữ liệu cần tổng hợp, phân tích.
- Thay đổi field setting
- Group trường
- ...
Đối với Excel 2007 - 2010, có thể Filter các field Page với multiple selection (2003 chỉ filter multiselect với row field và column field)
Ngoài ra, kết hợp công thức với thù thuật dấu dòng, thêm cột, dùng VBA đánh số thứ tự, kẻ khung, ... ta sẽ có báo cáo hoàn chỉnh.
Việc dùng VBA để filter, còn để tiết kiệm. Thí dụ với báo cáo công nợ chi tiết 1 khách hàng từ ngày A đến ngày B, dùng 1 combobox chọn tên khách hàng và filter trường KH theo combobox này, ta sẽ dùng 1 sheet, 1 pivot table duy nhất để in báo cáo cho cả trăm khách hàng lần lượt từng KH một.
Đối với báo cáo tổng hợp công nợ tất cả KH, ta có thể chọn in chỉ các khách hàng dư nợ, hoặc các khách hàng dư có (trả tiền trước), in các khách hàng hết nợ, và dĩ nhiên, cả in hết.
Khi chọn 1 option button khác, ta sẽ có 1 báo cáo khác.
Code chính của việc tác động vào Pivot table như sau:
Trong đó, "TinhtrangNo" là 1 field bổ sung thêm vào dữ liệu nguồn.
Ngoài ra các code khác để đánh số thứ tự, kẻ khung, ...
Có sự khác biệt của vài dòng lệnh giữa 2003 và 2007:
Trong 2007 - 2010, nếu Pivot table đã định dạng theo 1 style, thì VBA không can thiệp được vào định dạng kẻ khung, đổi chữ đậm, nghiêng, ... cho các thành phần của Pivot table.
Trong file kèm theo tôi có ghi chú.
__________________________
Tiếp theo (bài sau) sẽ là ứng dụng combobox chọn 1 khách hàng trong danh sách để in chi tiết khách hàng đó.
Như đã nói trong sách Pivot Table 2003, Pivot Table là 1 công cụ rất mạnh để phân tích, nhưng để nguyên Pivot table thì không in ra như 1 báo cáo đúng chuẩn được.
Hôm nay tôi viết bài này, giới thiệu 1 số thủ thuật trong đó có dùng VBA để định dạng Pivot table lại thành 1 báo cáo hoàn chỉnh có thể in ra được.
Có thể dùng VBA cho đối tượng Pivot Table trong các việc:
- Refresh
- Filter trường Page
- Filter trường Row
- Filter trường Column
- Hoán vị trường trong dòng, cột
- Thêm bớt trường vào dòng, cột
- Dời trường từ dòng sang cột, hoặc Page
- Thay đổi dữ liệu cần tổng hợp, phân tích.
- Thay đổi field setting
- Group trường
- ...
Đối với Excel 2007 - 2010, có thể Filter các field Page với multiple selection (2003 chỉ filter multiselect với row field và column field)
Ngoài ra, kết hợp công thức với thù thuật dấu dòng, thêm cột, dùng VBA đánh số thứ tự, kẻ khung, ... ta sẽ có báo cáo hoàn chỉnh.
Việc dùng VBA để filter, còn để tiết kiệm. Thí dụ với báo cáo công nợ chi tiết 1 khách hàng từ ngày A đến ngày B, dùng 1 combobox chọn tên khách hàng và filter trường KH theo combobox này, ta sẽ dùng 1 sheet, 1 pivot table duy nhất để in báo cáo cho cả trăm khách hàng lần lượt từng KH một.
Đối với báo cáo tổng hợp công nợ tất cả KH, ta có thể chọn in chỉ các khách hàng dư nợ, hoặc các khách hàng dư có (trả tiền trước), in các khách hàng hết nợ, và dĩ nhiên, cả in hết.
Khi chọn 1 option button khác, ta sẽ có 1 báo cáo khác.
Code chính của việc tác động vào Pivot table như sau:
PHP:
Sub Pivot01()
Dim Status, EndR As Long, BeginR As Long
Select Case ActiveSheet.[I1]
Case 1
Status = "(All)"
Case 2
Status = "1"
Case 3
Status = "2"
Case 4
Status = "0"
End Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("TinhtrangNo")
.CurrentPage = Status
End With
End SubNgoài ra các code khác để đánh số thứ tự, kẻ khung, ...
Có sự khác biệt của vài dòng lệnh giữa 2003 và 2007:
Trong 2007 - 2010, nếu Pivot table đã định dạng theo 1 style, thì VBA không can thiệp được vào định dạng kẻ khung, đổi chữ đậm, nghiêng, ... cho các thành phần của Pivot table.
Trong file kèm theo tôi có ghi chú.
__________________________
Tiếp theo (bài sau) sẽ là ứng dụng combobox chọn 1 khách hàng trong danh sách để in chi tiết khách hàng đó.
