Excel數(shù)據(jù)透視表的打印功能怎么用
Excel數(shù)據(jù)透視表的打印功能怎么用
在文檔的使用過(guò)程中，打印功能對(duì)于我們來(lái)說(shuō)并不陌生，在Excel數(shù)據(jù)透視表中也有打印功能。以下是學(xué)習(xí)啦小編為您帶來(lái)的關(guān)于Excel數(shù)據(jù)透視表打印，希望對(duì)您有所幫助。
Excel數(shù)據(jù)透視表打印
打印 數(shù)據(jù)透視表頁(yè)字段中的每個(gè)數(shù)據(jù)項(xiàng)
下面的代碼將能夠?qū)崿F(xiàn)打印頁(yè)字段中的每個(gè)數(shù)據(jù)項(xiàng)的功能(假定為一個(gè)頁(yè)字段).請(qǐng)使用打印預(yù)覽測(cè)試. 準(zhǔn)備打印時(shí), 請(qǐng)去掉 ActiveSheet.PrintOut代碼前的單引號(hào), 并在代碼ActiveSheet.PrintPreview前添加一個(gè)單引號(hào).
Sub PrintPivotPages()
'打印數(shù)據(jù)透視表一個(gè)頁(yè)字段下的每個(gè)數(shù)據(jù)項(xiàng)
'假設(shè)只有一個(gè)頁(yè)字段存在
On Error Resume Next
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Set pt = ActiveSheet.PivotTables.Item(1)
For Each pf In pt.PageFields
For Each pi In pf.PivotItems
pt.PivotFields(pf.Name).CurrentPage = pi.Name
' ActiveSheet.PrintOut '使用這個(gè)代碼打印
ActiveSheet.PrintPreview '使用這個(gè)代碼預(yù)覽
Next
Next pf
End Sub
打印數(shù)據(jù)透視表頁(yè)字段下每個(gè)數(shù)據(jù)項(xiàng)的透視圖
下面的代碼將能夠?qū)崿F(xiàn)打印頁(yè)字段中的每個(gè)數(shù)據(jù)項(xiàng)的透視圖功能(假定為一個(gè)頁(yè)字段).請(qǐng)使用打印預(yù)覽測(cè)試. 準(zhǔn)備打印時(shí), 請(qǐng)去掉 ActiveSheet.PrintOut代碼前的單引號(hào), 并在代碼ActiveSheet.PrintPreview前添加一個(gè)單引號(hào).
Sub PrintPivotCharts()
'prints a chart for each item in the page field
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Set pt = ActiveChart.PivotLayout.PivotTable
For Each pf In pt.PageFields
For Each pi In pf.PivotItems
pt.PivotFields(pf.Name).CurrentPage = pi.Name
' ActiveSheet.PrintOut
ActiveSheet.PrintPreview '打印預(yù)覽測(cè)試
Next
Next pf
End Sub
打印數(shù)據(jù)透視表的每個(gè)頁(yè)數(shù)據(jù)項(xiàng) – 多個(gè)頁(yè)字段
下面代碼將完成打印數(shù)據(jù)透視表頁(yè)數(shù)據(jù)項(xiàng)的每個(gè)組合. 如果 PrintFlag 不設(shè)置為 true, 描述信息將被寫(xiě)入PageItemList工作表.
Option Compare Text
Public mrow As Integer
Public PrintFlag As Boolean
'====================================================================
Sub PrintAllPages()
'from code posted by Tom Ogilvy
'September 5 2004
Dim holdSettings
Dim ws As Worksheet
Dim wsPT As Worksheet
Set ws = Worksheets("PageItemList") 'sheet for page items
Set wsPT = Worksheets("Pivot") 'sheet with PivotTable
mrow = 0
If MsgBox("Print?", vbYesNo, "Print?") = vbYes Then
PrintFlag = True
Else
PrintFlag = False
MsgBox "Page field items will be listed on sheet " & ws.Name
End If
If Not PrintFlag Then
ws.Cells(1, 1).CurrentRegion.Clear
End If
Set PvtTbl = wsPT.PivotTables(1)
wsPT.Activate
If PvtTbl.PageFields.Count = 0 Then
MsgBox "The PivotTable has no Pages"
Exit Sub
End If
With PvtTbl
ReDim holdSettings(1 To .PageFields.Count)
I = 1
For Each PgeField In .PageFields
holdSettings(I) = PgeField.CurrentPage.Name
I = I + 1
PgeField.CurrentPage = PgeField.PivotItems(1).Name
Next PgeField
End With
PvtPage = 1
PvtItem = 1
DrillPvt oTable:=PvtTbl, Ipage:=PvtPage, wksht:=ws
I = 1
For Each PgeField In PvtTbl.PageFields
PgeField.CurrentPage = holdSettings(I)
I = I + 1
Next PgeField
End Sub
'====================================================================
Sub DrillPvt(oTable, Ipage, wksht)
'Debug.Print "in DrillPvt, page:=" & Ipage & " Page Item: " & _
' oTable.PageFields(Ipage).CurrentPage & " " & mrow
If Ipage = oTable.PageFields.Count Then
With oTable
For I = 1 To .PageFields(Ipage).PivotItems.Count
.PageFields(Ipage).CurrentPage = _
.PageFields(Ipage).PivotItems(I).Name
mrow = mrow + 1
slist = ""
For j = 1 To .PageFields.Count
slist = slist & .PageFields(j).CurrentPage & " "
Next j
' Debug.Print slist
If PrintFlag Then
'' ActiveSheet.PrintOut 'print the sheet
ActiveSheet.PrintPreview 'preview -- for testing
Else
For j = 1 To .PageFields.Count
wksht.Cells(mrow, j).Value = _
.PageFields(j).CurrentPage.Name
Next j
End If
Next I
End With
For I = oTable.PageFields.Count - 1 To 1 Step -1
For j = 1 To oTable.PageFields(I).PivotItems.Count
If oTable.PageFields(I).CurrentPage = _
oTable.PageFields(I).PivotItems(j).Name Then
CurrItem = j
Exit For
End If
Next j
If CurrItem <> oTable.PageFields(I).PivotItems.Count Then
oTable.PageFields(I).CurrentPage = _
oTable.PageFields(I).PivotItems(CurrItem + 1).Name
Ipage = I + 1
DrillPvt oTable, Ipage, wksht
Else
If I <> 1 Then
oTable.PageFields(I).CurrentPage = _
oTable.PageFields(I).PivotItems(1).Name
Else
Exit Sub
End If
End If
Next I
Else
DrillPvt oTable, Ipage + 1, wksht
End If
End Sub
猜你喜歡：
1.Excel數(shù)據(jù)透視表動(dòng)態(tài)數(shù)據(jù)怎么制作
2.怎樣在Excel2013數(shù)據(jù)透視表中統(tǒng)計(jì)不重復(fù)值數(shù)量