l使用技巧.doc

Execl使用技巧（三）如果要創(chuàng)建一個數據透視表，可以使用“數據透視表和數據透視圖向導”來查找和指定要進行分析的源數據并創(chuàng)建透視報表框架。然后就可以通過“數據透視表”工具欄在該報表框架中任意排列數據?！皵祿敢暠砗蛿祿敢晥D向導”使得數據透視表的創(chuàng)建既簡單又快捷。 下面我們以一個實例來說明創(chuàng)建數據透視表的步驟。假設我們已經做好了一個有關銷售業(yè)績的數據清單，如圖3-5-1所示?，F在就可以按照下面的操作步驟一步步進行：1）單擊數據清單中的任一單元格。2）單擊“數據”菜單選項中的“數據透視和圖表報告”命令，在彈出的“數據透視表和數據透視圖向導3步驟之1”對話框中，選擇“Microsoft Excel數據清單或數據庫”及“數據透視表”這兩個單選項，見圖3-5-2所示。上述兩個選項表明，我們創(chuàng)建的將是基于Excel數據清單的數據透視表。當然，也可以進行其他項的選擇，圖3-5-2中各選項按鈕的說明如下：“Microsoft Excel數據清單或數據庫”：以工作表中按列組織的數據創(chuàng)建數據透視表。此選項按鈕為缺省設定?！巴獠繑祿础保阂源鎯υ谕獠繑祿熘谢蛭募械臄祿?chuàng)建數據透視表。“多重合并計算數據區(qū)域”：以Excel工作表的多個區(qū)域中的數據創(chuàng)建數據透視表。“另一數據透視表或數據透視圖”：在相同工作簿中，以另一個數據透視表中的數據創(chuàng)建數據透視表。3）單擊“下一步”按鈕，在彈出的“數據透視表和數據透視圖向導3步驟之2”對話框中，選中數據清單區(qū)域（一般情況下是自動選中，也可以在對話框中單擊“瀏覽”按鈕，從其他Excel工作簿中選擇數據清單區(qū)域）。該數據清單區(qū)域就作為所要創(chuàng)建的數據透視表的數據源。4）單擊“下一步”按鈕，彈出“數據透視表和數據透視圖向導3步驟之3”對話框。完成上述步驟后，Excel在源數據工作表的前面添加了一個新工作表“Sheet5”，并在此工作表中按照我們的選擇放置數據透視表，新工作表的屏幕顯示。同時屏幕上還顯示出“數據透視表”工具欄，在工具欄中列出了選定數據區(qū)域中的所有字段。此時用戶可以按照屏幕上的提示將工具欄上的各字段拖到數據透視表上相應的分頁字段、行字段、列字段和數據項的位置上，操作完成后便創(chuàng)建成功了一個數據透視表。例如，我們可以創(chuàng)建這樣一個數據透視表：1）在“數據透視表”工具欄中，點擊“時間”按鈕，并將其拖至工作表最上方的“頁字段”處；2）在“數據透視表”工具欄中，點擊“產品名稱”按鈕，并將其拖至工作表的“列字段”處；3）用同樣的方法，將“分支機構”、“銷售人員”按鈕依次拖至工作表左側的“行字段”處；將“銷售額”拖至中央的“數據項”處。得到的結果如圖3-5-6所示?，F在我們已經創(chuàng)建完成了一個數據透視表。在這個數據透視表中，可以看到各種匯總結果。例如，北京分公司銷售人員劉東銷售組合沙發(fā)的總銷售額為132350元；深圳分公司銷售所有產品的總金額為464750元，等等。我們仔細觀察一下這個數據透視表，還可以發(fā)現，在頁字段“時間”的右面單元格（B1）的右側有一個下拉箭頭按鈕，點擊這個按鈕，可以查看不同時間段中的各種匯總數據。例如，單擊“二季度”選項，然后單擊“確定”按鈕，數據透視表中顯示的是二季度的匯總數據。用同樣的方法可以查看各個季度的統計數據。當然，要想查看全部數據的統計結果，選擇下拉列表中的“（全部）”選項即可。 在“數據透視表和數據透視圖向導3步驟之3”對話框中有一個“版式”按鈕，單擊這個銨鈕，屏幕上顯示出“數據透視表和數據透視圖向導版式”的對話框。在這個對話框中，各個字段名均排列在右邊，我們可以直接用鼠標將各字段名分別拖至頁字段區(qū)、行字段區(qū)、列字段區(qū)和數據字段區(qū)，從而完成數據透視表的版式設置。這種操作方法與Excel 97版本的操作非常類似。3.5.2 編輯數據透視表創(chuàng)建完成數據透視表后，我們還可以調整它的版式，可以根據需要重新選擇各行字段和各列字段，也可以像在普通數據表中那樣進行格式排版、增加或者刪除記錄等各項操作。1修改數據透視表版式修改數據透視表版式的操作非常簡單，只需用鼠標拖動數據透視表或“數據透視表”工具欄中的字段按鈕和字段標題即可。也可以在“數據透視表”工具欄中單擊“數據透視表向導”的按鈕，在彈出的“數據透視表和數據透視圖向導3步驟之3”對話框中單擊“版式”按鈕，然后在彈出的“數據透視表和數據透視圖向導版式”對話框中進行調整。如果數據透視表包括源數據中的不同字段，就可以添加或刪除字段。如果數據透視表有大的頁字段分組，可以按照行或列排列它們。無論怎樣更改數據透視表版式，都不會影響到源數據。（1）拖動字段或數據單元格更改版式在進行更改前，先單擊數據透視表報表以顯示出每種字段類型區(qū)域的藍色邊框。如果在屏幕上看不見藍色邊框，可以在“數據透視表”工具欄中單擊靠右邊的“顯示字段”按鈕，以確認該按鈕處于按下的狀態(tài)。當我們想要顯示或隱藏“數據透視表”工具欄中的字段按鈕時，也可以點擊這個按鈕。1 ）作移動字段的更改如果要移動某字段及其相關的所有數據項時，可以將該字段按鈕拖動到新的位置。例如，在銷售統計表的數據透視表中，要將“分支機構”字段作為行字段，可以將原來在數據透視表中處于列字段位置的“分支機構”按鈕，或“數據透視表”工具欄中的“分支機構”按鈕拖曳到行字段區(qū)域中去，見圖3-5-10所示。將“分支機構”字段從行字段的位置移到列字段的位置后的數據透視表，如圖3-5-11所示。2）作刪除頁、行或列字段的更改如果要刪除頁字段、行字段或列字段，只需將該字段按鈕拖動到數據透視表報表區(qū)域之外，當鼠標箭頭旁出現一個紅叉圖案時，松開鼠標按鍵就可以了。除此之外，也可以在要刪除的字段上單擊右鍵，然后在彈出的快捷菜單中單擊“隱藏”命令。一旦刪除了某個字段，與之相關聯的數據也就隨之從數據透視表中被刪除了，不過源數據工作表并不會發(fā)生變化。另外，盡管該字段被刪除了，但該字段按鈕仍然會顯示在“數據透視表”工具欄中，因此還可以繼續(xù)使用這個按鈕。例如，在銷售統計表的數據透視表中，如果想要刪除行字段“分支機構”，只需將數據透視表中的“分支機構”按鈕拖到數據透視表區(qū)域之外的任何地方就可以了。刪除“分支機構”字段之后的數據透視表。3）創(chuàng)建新的頁字段如果想要創(chuàng)建頁字段，可以將字段按鈕拖動到數據透視表報表左上角單元格之上的頁字段區(qū)域中去。如果由于報表中的行字段或列字段從第1行/列開始因而沒有頁字段區(qū)域，則可以在“數據透視表”工具欄中單擊“數據透視表向導”按鈕，然后在彈出的“數據透視表和數據透視圖向導3步驟之3”對話框中單擊“版式”按鈕，再在彈出的“數據透視表和數據透視圖向導版式”對話框中創(chuàng)建頁字段。4）改變字段項的排列次序如果要移動某字段項以使字段項的排列次序發(fā)生變化，可以單擊該項的標志，然后將鼠標指向該單元格底部的邊框，當指針的形狀變成箭頭時，就可以拖動該單元格了。此時可將單元格拖到想要放置的新的位置處，從而改變各字段項的排列次序。例如，要將銷售統計表的數據透視表中有關“組合沙發(fā)”的銷售數據移動到“真皮靠背椅”之前，首先單擊“組合沙發(fā)”標題，然后用鼠標拖動該單元格底部的邊框。隨著鼠標的拖動，會出現一個灰色的“I”字形粗線，用以表明該字段要移動到的位置。當“I”字形粗線出現在“真皮靠背椅”之前時，松開鼠標即可，如圖3-5-13所示。 5）更改數據字段的顯示順序如果在數據透視表中有多個數據字段（如本例中的“銷售額”字段），那么數據字段的顯示順序也是可以更改的，這時只需用鼠標右鍵單擊數據字段，并指向快捷菜單上的“順序”子菜單，然后使用“順序”子菜單上的命令將字段移到所需的位置上去。需要注意一點的是：如果用戶更改了與某個數據透視圖報表相關聯的數據透視表報表的布局，這將會影響到數據透視圖報表的布局。例如，如果將數據透視表報表中的行字段和列字段進行了交換，那么數據透視圖報表中的分類和系列也將隨之進行交換。另外，如果更改了數據透視表報表，則某些數據透視圖報表的格式有可能會丟失。 （2）在行或列中安置數據透視表頁字段如果在數據透視表中設有多個頁字段，則可以通過拖動頁字段按鈕的方式來改變其排列順序。如果要設置將頁字段依次放在一行或一列中，可以按以下的操作步驟進行：1）在數據透視表中任選一單元格。2）單擊“數據透視表”工具欄中“數據透視表”菜單中的“表選項”命令。3）在“頁面布局”下拉框中，選擇頁字段排列的順序，如圖3.5.14所示。4）在開始另一行或另一列之前，在“每行字段數”或“每列字段數”輸入框中，輸入當前行或當前列所要包含的頁字段數目。2添加和刪除記錄在數據透視表中，用戶也可以只查看數據清單中的部分數據的匯總結果。例如在數據透視表中刪除“會議桌”的有關數據，或者只在數據透視表中查看“三季度”的數據。然而，無論是在數據透視表中添加記錄還是刪除記錄，都不會影響到源數據。（1）顯示或隱藏數據透視表報表中行和列字段中的項我們用一個實際的例子來說明操作過程。假如要刪除“產品名稱”字段中的“會議桌”項，具體的操作步驟如下：1）在數據透視表中，單擊要隱藏或顯示其中某項的字段右側的下拉箭頭（本例中，單擊“產品名稱”字段右側的下拉箭頭），如圖3-5-15所示。2）凡是要顯示的項，則選中該項所對應的復選框；凡是要隱藏的項，則清除該項所對應的復選框（本例中，需清除“會議桌”項對應的復選框）。如果要顯示或隱藏沒有任何數據的項，可以雙擊該字段按鈕，然后在彈出的“數據透視表字段”對話框中，選中或清除“顯示空數據項”復選框。例如，如果一季度沒有銷售量，則只有當選中了“顯示空數據項”復選框時，才能顯示一季度的項，否則就不顯示。注意：如果顯示或隱藏了與某個數據透視圖報表相關聯的數據透視表報表中的項，則某些數據透視圖報表的格式有可能會丟失。（2）顯示數據透視表字段的前幾項或后幾項在應用數據透視表時，不一定非要顯示字段中的全部項。用戶可以根據具體的需要，選擇顯示字段按某種指標排列的前幾項或后幾項，如顯示銷售量最大的前10項產品或費用支出最低的3個分公司等。例如，在銷售統計表的數據透視表中，可以利用這種方法找到季度銷售額最高的產品是哪一種產品，并且在數據透視表中僅顯示與該產品相關的數據。具體操作可按如下步驟進行：1）在數據透視表中，雙擊“產品名稱”字段。2）在彈出的“數據透視表字段”對話框中， 單擊“高級”按鈕。3）在“數據透視表字段高級選項”對話框中，單擊“自動顯示選項”下的“自動”選項按鈕。4）單擊“顯示”下拉框中的“最大”選項，并在其右側框內，輸入數字“1”，如圖3-5-16所示。5）單擊“使用字段”框中的某一字段（該字段包含用來決定前幾項或后幾項的數據）。本例中，接受默認的字段“銷售額”，然后單擊“確定”按鈕。完成上述步驟后，得到如圖3-5-17所示的數據透視表。從透視表中可以看到，銷售統計表中，季度銷售額最高的產品是“會議桌”。在數據透視表中顯示的是有關“會議桌”的所有數據，而其他的數據項則被隱藏起來了。如果要取消對數據項的隱藏，在“數據透視表字段高級選項”對話框中的“自動顯示選項”標題下單擊“人工”選項按鈕，然后單擊“確定”按鈕就可以了。注意：如果在與某個數據透視圖報表相關聯的數據透視表報表中顯示前幾項或后幾項，則某些數據透視圖報表的格式有可能會丟失。（3）顯示或隱藏數據透視表頁字段中的項如果在銷售統計表的數據透視表中，想要隱藏頁字段“時間”中的一項“二季度”，具體的操作可按如下步驟進行：1）在數據透視表中，單擊頁字段“時間”按鈕，然后單擊“數據透視表”工具欄上的“字段設置”按鈕；或者直接雙擊“時間”字段按鈕。屏幕上將彈出“數據透視表字段”對話框，如圖3-5-18所示。2）在“數據透視表字段”對話框中，在“隱藏數據項”下，單擊選擇要隱藏的每一項（再次單擊可以取消對該項的隱藏）。一次可以選擇對多個數據項的隱藏，但至少應有一項是在顯示狀態(tài)下。本例中，單擊選擇“二季度”選項，使其成為被隱藏的數據項。3）如果要顯示或隱藏沒有數據的項，可以選中或清除“顯示空數據項”復選框的復選標記，最后單擊“確定”按鈕。隱藏了“二季度”數據項后，在數據透視表中單擊“時間”字段按鈕右側的下拉箭頭，在下拉列表中將不再有“二季度”選項出現，如圖3-5-19所示。現在已不能在數據透視表中顯示二季度的數據了。3設置數據透視表選項在“數據透視表和數據透視圖向導3步驟之3”對話框中單擊“選項”按鈕可以對數據透視表的一些選項進行設置，比如設置透視表的名稱等。數據透視表選項也可以在完成數據表創(chuàng)建后進行設置。對數據透視表報表的選項設置與對數據透視圖報表的選項設置相類似。對已經建立好的數據透視表設置選項的方法是：在“數據透視表”工具欄中單擊“數據透視表”菜單中的“表選項”命令，彈出“數據透視表選項”對話框，如圖3-5-20所示?！皵祿敢暠磉x項”對話框中各選項的意義如下：“名稱”：鍵入一個新名稱可以更改報表的名稱。在數據透視圖報表中，此框中包含的是相關聯數據透視表報表的名稱，更改名稱時會重命名數據透視表報表，而不是數據透視圖報表?！傲锌傆嫛保猴@示數據透視表報表的列合計。在數據透視圖報表中，合計顯示在相關聯的數據透視表報表內?！靶锌傆嫛保猴@示數據透視表報表的行合計。在數據透視圖報表中，合計顯示在相關聯的數據透視表報表內。“自動套用格式”：一般情況下，對數據透視表報表應用默認的Excel自動套用格式。使用“格式”菜單中的“自動套用格式”命令，可以對數據透視表報表應用其他自動套用格式。但此項對數據透視圖報表無效?！半[藏的子匯總項”：在數據透視表報表分類匯總中包含隱藏的頁字段項。在數據透視圖報表中，分類匯總受相關聯的數據透視表報表影響。 “合并標志”：默認情況下，外部行字段和列字段中的項標志會出現在項組頂部的左邊。若選中此對話框，項標志在合并單元格中就可以水平居中和垂直居中。此選項對數據透視圖報表無效?！氨Ａ舾袷健保簾o論任何時候刷新報表或更改其布局，都保留應用于數據透視表報表數據的格式設置。但此項對數據透視圖報表無效?！懊恳淮蛴№撋现貜晚椖俊保哼x擇在每個打印頁的頂端打印外部行字段的各項標志。對于有分頁符分隔其項組的字段，項標志會為該字段左邊的所有行字段進行重復打印。清除此復選框則只會在出現項標志的第一個打印頁上打印項標志。“用星號（*）標注匯總”：選中此復選框，將在基于OLAP源數據的數據透視表報表中的每個分類匯總和總計之后顯示星號（*），用以表明這些值中既包括顯示的項也包括任何隱藏的項。如果不希望看到星號（*），則應清除此復選框?！绊撁娌季帧保哼x擇數據透視表頁字段的顯示順序。默認的頁字段布局是“垂直并排”選項。此選項對數據透視圖報表無效?！懊苛凶侄螖怠保涸跀祿敢暠碇校_始另一行或另一列的頁字段之前，選擇每行或每列中所包含的頁字段數。此選項對數據透視圖報表無效。“顯示出錯值”：在數據透視表中，如果要顯示特定值以代表單元格錯誤，可以選中此復選項，然后在其右側鍵入要顯示的數值。此選項對數據透視圖報表無效。“顯示空白單元格”：在數據透視表中，如果要顯示特定值以代表空單元格，可以選中此復選項，然后在其右側鍵入要顯示的數值。此選項對數據透視圖報表無效?！霸O置打印標題”：選擇將數據透視表報表中字段和項的標志用作行和列的打印標題。如果更改了報表布局從而使得字段和項的標志位于不同的行和列中，則新的標志行和列將自動用作打印標題。在選中此復選框之前，單擊“文件”菜單上的“頁面設置”，再單擊“工作表”選項卡，然后清除“頂端標題行”和“左端標題列”復選框，并確保當前報表是打印區(qū)域中的唯一報表。 “用表形式保存數據”：保存工作簿文件中報表的內部數據副本，從而使得用戶在打開工作簿文件來使用報表之前不必刷新報表。如果沒有選定此選項，則工作簿文件將較小。但是需要選中“打開時刷新”復選框或在打開工作簿來使用報表之前，單擊“數據透視表”工具欄上的“更新數據”。如果沒有保存此數據，則工作簿中基于此數據透視表報表或基于與當前數據透視圖報表相關聯的數據透視表報表的其他報表也將丟棄此數據。報表模板文件中會忽略此選項?！按蜷_時刷新”：無論何時打開工作簿，都會根據源數據更新數據透視表報表和數據透視圖報表中的數據。“刷新頻率”：選擇自動刷新來自外部源數據的數據透視表或數據透視圖報表，然后在“分鐘”框內輸入刷新之間的等待時間（單位：分鐘）?！氨４婷艽a”：如果使用需要密碼的外部數據庫作為數據透視表或數據透視圖報表的數據源，則選中“保存密碼”復選框可以將密碼保存為報表的一部分。此后更新報表時，就不需要重新輸入密碼了。“顯示明細數據”：如果清除此復選框，則在數據透視表報表的數據區(qū)中雙擊某單元格時，可避免顯示明細數據。在數據透視圖報表中，此選項只會影響相關聯的數據透視表報表。“后臺查詢”：在運行查詢以檢索報表的外部數據時，清除此復選框會等待檢索數據。若選中此復選框，Excel將在后臺運行查詢?！皟?yōu)化內存”：選中“優(yōu)化內存”復選框，可以在通過外部數據源刷新報表時節(jié)省內存。4格式化數據透視表為了達到美觀的效果，對數據透視表與對一般的工作表一樣可以進行各種形式的格式設置，當然，也可以應用自動套用格式。（1）保留格式在默認情況下，如果更改了數據透視表報表的格式，則當刷新報表或更改其版式時，將保留大部分格式設置。如果要保留格式，則必須選中“數據透視表選項”對話框中的“保留格式”復選框（在“數據透視表”工具欄上，單擊“數據透視表”菜單中的“表選項”命令，屏幕上就會彈出“數據透視表選項”對話框）。無論是使用數據透視表中的選定區(qū)域或是使用常規(guī)的選定區(qū)域，所設置的格式都將保留。（2）更改字符和單元格格式用戶可以根據自己的喜好或要達到的美觀效果來更改數據透視表報表中單元格的格式，例如重新設置字體字形、背景色、邊框線、圖案和對齊方式等。其操作方法與在普通工作表中設置格式時完全相同。數據透視表報表的數據區(qū)域中的數值格式有時會與其源數據中的數值格式并不一致。用戶可以根據實際需要更改數字格式，更改后的格式可以是任意的Excel格式。如果要更改數值格式，除了常用的方法以外，還可以在“數據透視表字段”對話框中進行。例如，在銷售統計表的數據透視表中，要將所有的“銷售額”的數值改為保留兩位小數、使用千位分隔符的格式，具體操作可按如下步驟進行：1）在數據透視表中雙擊“求和項：銷售額”按鈕，在屏幕上彈出“數據透視表字段”對話框。2）在“數據透視表字段”對話框中，單擊“數字”按鈕，彈出“單元格格式”對話框，如圖3-5-21所示。3）在“數字”選項卡中的“分類”列表框中單擊“數值”選項，然后在“小數位數”輸入框中輸入數字“2”，選中“使用千位分隔符”復選項；最后單擊“確定”按鈕。4）在“數據透視表字段”對話框中單擊“確定”按鈕。經過以上格式設置后的數據透視表如圖3-5-22所示。 （3）對數據透視表報表應用自動套用格式在對數據透視表報表應用自動套用格式時，既可以選擇以縮進方式也可以選擇以非縮進方式來顯示數據透視表報表。在默認情況下，新建的數據透視表報表將按非縮進格式顯示。如果要用與傳統的數據庫報表相似的格式顯示數據透視表報表，則可以將非縮進格式改為縮進格式。當其改為縮進格式后，可能還會影響到數據透視表的版式，這樣字段就可由列的方向移動到行的方向上。對數據透視表報表應用自動套用格式，具體操作可按如下步驟進行：1）單擊數據透視表報表。2）單擊“數據透視表”工具欄上的“設置報表格式”按鈕，或者單擊“格式”菜單中的“自動套用格式”命令，屏幕上彈出“自動套用格式”對話框。在“自動套用格式”對話框中，從“報表1”到“報表10”為縮進格式，如圖3-5-23所示。從“表1”到“表10”為非縮進格式，如圖3-5-24所示。另外，還可以選擇“傳統數據透視表”格式作為數據透視表應用格式，選擇“無”只以非縮進布局來顯示數據透視表而不使用其他格式設置。3）單擊選擇其中一種格式（在本例中單擊選擇“報表1”的格式），然后，單擊“確定”按鈕。以報表1格式提供的縮進方式顯示的數據透視表如圖3-5-25所示。5數據透視表的刪除如果將數據透視表報表刪除，并不會對源數據有任何影響。刪除數據透視表的操作可按如下步驟進行：1）單擊數據透視表報表。2）將鼠標指向“數據透視表”工具欄上“數據透視表”菜單中的“選定”子菜單，然后單擊“整張表格”命令。3）將鼠標指向“編輯”菜單中的“清除”子菜單，然后單擊“全部”命令。注意：如果刪除了與某個數據透視圖報表相關聯的數據透視表報表，將不能再更改數據透視圖報表所顯示的數據或使用其中的任何數據透視圖功能。3.5.3 數據透視表的操作 1刷新數據透視表 在源數據發(fā)生任何變化后，可以利用刷新數據透視表功能，從而看到源數據都有了哪些變動。當刷新基于Excel源數據的數據透視表報表或數據透視圖報表時，Excel將檢索源數據區(qū)域或命名數據區(qū)域（該命名數據區(qū)域是用戶在創(chuàng)建數據透視表報表或數據透視圖報表時命名的）中的任意新字段。當刷新基于外部源數據的數據透視表報表或數據透視圖報表時，Excel將檢索外部文件或數據庫中滿足指定查詢條件的新數據行或記錄。注意：當刷新與某個數據透視圖相關聯的數據透視圖報表或數據透視表報表時，某些數據透視圖的格式有可能會丟失。刷新數據透視表或數據透視圖報表中的數據的操作步驟如下：1）單擊數據透視表或數據透視圖報表。2）單擊“數據透視表”工具欄中的“更新數據”按鈕。除了使用“更新數據”按鈕外，也可以讓Excel在每次打開工作簿時，或在一定的時間間隔內定時刷新數據透視表報表或數據透視圖報表。如果想要在每次打開工作簿時進行數據刷新，可以單擊“數據透視表”工具欄上的“數據透視表”菜單中的“表選項”按鈕，然后選中“數據選項”區(qū)域中的“打開時更新”復選框按鈕，這樣工作簿就會在每次打開時先進行數據透視表報表或數據透視圖報表的刷新操作了。如果想要在一定的時間間隔內定期進行數據刷新，可以單擊“數據透視表”工具欄上的“數據透視表”菜單中的“表選項”按鈕，然后選中“數據選項”區(qū)域中的“刷新頻率”復選框按鈕，再在“分鐘”輸入框內鍵入想要進行刷新的時間周期。需要注意一點的是：定時刷新功能只適用于來自外部源數據的數據透視表，對基于Excel源數據的數據透視表則不能使用定時刷新功能。如果數據透視表報表或數據透視圖報表是基于Excel源數據，當往源數據區(qū)域中添加了新字段后，這些新添加的字段會在刷新數據透視表報表或數據透視圖報表后出現在“數據透視表”工具欄的字段列表中。這時，就可以向數據透視表報表或數據透視圖報表中添加新的字段了。 2對數據透視表排序通常情況下，在數據透視表報表或數據透視圖報表中，Excel會自動根據字段項標志按升序次序排列字段項。但是，如果在刷新報表時又檢索到了新添加的項，則這些新添加的項將放到字段的行末或列尾。（1）按標志對數據透視表中的數據項進行排序例如，在銷售統計表的數據透視表中，要對“產品名稱”字段按照筆畫順序進行排序，那么具體的操作可按如下步驟進行：1）在數據透視表中單擊“產品名稱”字段按鈕。在數據透視表報表中，如果選中了列字段，則將對該數據項從左至右進行排序；如果選中了行字段，則將對該數據項從上至下進行排序。 2）在“數據”菜單中單擊“排序”命令，這時屏幕上彈出“排序”對話框，如圖3-5-26所示。在“排序”對話框中，使用“排序依據”框可以重新選擇要排序的字段。使用“遞增” 選項按鈕可以選擇進行升序排序；反之，使用“遞減”選項按鈕可以選擇進行降序排序。另外，在對話框中單擊“選項”按鈕可以進行自定義排序，而不必使用Excel 默認的排序規(guī)則（數據透視表中的排序規(guī)則及自定義排序的操作方法與在Excel工作表中的規(guī)則及方法是完全一致的，這里不再贅述）。3）在“排序”對話框中單擊“選項”按鈕，這時屏幕上彈出“排序選項”對話框，如圖3-5-27所示。在對話框中的“自定義排序次序”下拉列表框中，可以選擇使用Excel內置的自定義排序次序（如第一季、第二季、第三季）或用戶自己創(chuàng)建的自定義排序次序。 4）在對話框中的“方法”選項區(qū)內單擊選中“筆畫排序”選項按鈕，然后單擊“確定”按鈕。5）在“排序”對話框中單擊“確定”按鈕。經過上述排序操作后的數據透視表如圖3-5-28所示。如果希望在每次刷新或更改數據透視表報表的布局后能自動按標志排序，可以先雙擊要排序的字段，在彈出的“數據透視表字段”對話框中單擊“高級”按鈕，然后在“自動排序選項”標題下單擊“遞增”按鈕或“遞減”按鈕，在“使用字段”下拉框中，單擊要排序的相同字段，最后再單擊“確定”按鈕。在刷新數據透視表后，自定義排序次序并不保留。要想恢復自定義排序次序，可以再次對字段進行排序。（2）按數據區(qū)中的數值對數據透視表中的數據項進行排序仍然以銷售統計表的數據透視表為例，如果要按照“銷售額”的大小來對“產品名稱”字段進行排序，具體的操作可按如下步驟進行：1）在數據透視表中，雙擊“產品名稱”字段按鈕。與前述相同，在數據透視表報表中，如果選中了列字段，則將對該數據項從左至右進行排序；如果選中了行字段，則將對該數據項從上至下進行排序。2）在彈出的“數據透視表字段”對話框中單擊“高級”按鈕，這時屏幕上又彈出“數據透視表字段高級選項”對話框，如圖3-5-29所示。3）在“自動排序選項”標題下，單擊“遞增”選項按鈕。4）在“使用字段”下拉框中，單擊含有用于排序數據項數值的數據字段。本例中，選擇“求和項：銷售額”選項，然后單擊“確定”按鈕。5）在“數據透視表字段”對話框中單擊“確定”按鈕。經過上述排序操作后的數據透視表如圖3-5-30所示。由圖中可見，各項產品按總銷售額由低到高的排列依次為：真皮靠背椅、組合沙發(fā)、會議桌。注意：如果在刷新或更改數據透視表報表的版式時，并不想保留新的排序次序，則可以選擇人工排序數據項來將排序次序恢復為默認排序次序。在“數據透視表字段高級選項”對話框中單擊“自動排序選項”標題下的“人工”按鈕，然后兩次單擊“確定”按鈕。單擊選擇要排序的字段后，再單擊“數據”菜單中的“排序”命令。在“排序依據”框中，輸入用于排序依據的數值所在單元格的引用，最后單擊“確定”按鈕。3改變數據透視表的匯總方式在創(chuàng)建數據透視表時，默認的匯總方式是對數據字段進行求和。實際上，用戶也可以根據不同的需要，很方便地改變數據的匯總方式。也就是說，并不一定所有用戶都是用求和來作為數據的匯總方式。比如，用戶還可以通過求數據的平均值、最大值、最小值、乘積值等，對數據透視表的匯總方式進行重新設置。例如，在前面的銷售統計表的數據透視表中，需要顯示各銷售人員3個季度來的季度平均銷售額，則具體操作可按如下步驟進行：1）在數據透視表的數據區(qū)域單擊任意單元格。2）單擊“數據透視表”工具欄上的“數據透視表”按鈕，然后在下拉菜單中單擊“字段設置”命令；或單擊“數據透視表”工具欄上的“字段設置”按鈕，屏幕上彈出“數據透視表字段”對話框，如圖3-5-31所示。3）在“匯總方式”標題下的列表框中，單擊“平均值”選項。4）單擊“確定”按鈕。更改匯總方式后的數據透視表如圖3-5-32所示。 如果想要同時查看數據透視表報表中數據字段的不同匯總方式的結果，可以按以下步驟進行：1）在“數據透視表”工具欄的字段列表中，單擊數據透視表報表的數據區(qū)域中已匯總的字段，然后再次將該字段拖動到數據透視表中。2）在數據透視表報表的數據區(qū)域內，單擊與新字段相對應的任意數據單元格。3）單擊“數據透視表”工具欄上的“數據透視表”按鈕，然后在下拉菜單中單擊“字段設置”命令；或單擊“數據透視表”工具欄上的“字段設置”按鈕。4）在彈出的“數據透視表字段”對話框的“匯總方式”列表框中，為該字段選擇另一個匯總函數。4顯示/隱藏明細數據在數據透視表中，如果要顯示比較詳細的數據構成，可以設置多個行字段或列字段。如果只是對其中的某個或某幾個字段顯示其詳細的數據來源，則可以使用在數據透視表顯示或隱藏明細數據的方法。例如，在前面的銷售統計表的數據透視表中，如果想要了解銷售人員張青的銷售情況的詳細數據，則具體的操作可按如下步驟進行：1）在數據透視表中，單擊選擇“張青”字段項標題。2）單擊“數據透視表”工具欄中的“顯示明細數據”按鈕，屏幕上彈出“顯示明細數據”對話框，如圖3-5-33所示。該對話框用于選擇要顯示其明細數據的字段。實質上就是在數據透視表中，對選定字段項的數據，按選定的字段進行分類。 3）單擊選擇“產品名稱”字段，然后單擊“確定”按鈕。經過上述操作后的數據透視表。由此可見，在數據透視表中，對銷售人員“張青”的銷售數據，按照不同類別的產品進行了分類。顯示在數據透視表中的數據仍然是匯總后的數據，只是在原有的基礎上對選定字段進行了細分。在上面顯示了明細數據的基礎上，還可以進一步顯示更明細的數據。在數據透視表中雙擊“產品名稱”字段中的“會議桌”項，屏幕上彈出“顯示明細數據”對話框，在對話框中單擊“時間”選項，然后再單擊“確定”按鈕。得到的數據透視表如圖3-5-35所示。如果想要隱藏“會議桌”字段項的明細數據，只需單擊選中“會議桌”，然后單擊“數據透視表”工具欄上的“隱藏明細數據”按鈕，或者直接雙擊“會議桌”字段標題即可。隱藏明細數據后，由于顯示明細數據而在數據透視表中造成的版式更改并不會恢復回來，所以如果再次雙擊“會議桌”項以顯示明細數據時，屏幕上將直接顯示“時間”的明細數據，而并不出現“顯示明細數據”對話框。如果要恢復原先的版式設置，可在數據透視表中用鼠標拖曳字段按鈕。除了顯示明細的匯總數據外，在數據透視表中還可以顯示數據單元格的基本源數據的明細數據。雙擊要顯示明細數據的單元格，Excel會將匯總的明細數據放到工作簿中新工作表上的單元格中。 例如，在圖3-5-32中，雙擊行“總計”和列“總計”的交叉單元格F11，則Excel將添加一個新工作表“Sheet6”,這是一個顯示源數據的明細數據的工作表，如圖3-5-36所示。如果要避免通過雙擊數據透視表報表數據區(qū)中的某一單元格，顯示該單元格所匯總的源數據列表，可以單擊“數據透視表”工具欄上的“數據透視表”菜單中的“表選項”命令，然后在“數據選項”標題下，清除“顯示明細數據框”復選框。3.5.4 用數據透視表進行匯總計算在創(chuàng)建數據透視表時，Excel會自動在數據透視表中顯示行總計和列總計，這已經是我們很熟悉的事情了。除此之外，用戶還可以通過相應的設置來顯示或隱藏數據透視表中的各項總計，也可以通過在數據透視表中添加分類匯總來對數據透視表進行合并計算。1顯示或隱藏數據透視表中的各項總計顯示或隱藏數據透視表中的各項總計，其具體的操作可按如下步驟進行：1）單擊數據透視表中的任意單元格。2）單擊“數據透視表”工具欄上的“數據透視表”菜單中的“表選項”按鈕。3）如果要顯示總計，在“格式選項”下選中“行總計”復選框和“列總計”復選框；如果要隱藏總計，在“格式選項”下清除“行總計”復選框或“列總計”復選框。2在數據透視表報表中添加或刪除分類匯總在數據透視表報表中添加或刪除分類匯總，其具體的操作可按如下步驟進行：1）雙擊要添加或刪除分類匯總的字段按鈕。2）如果要顯示外部行字段或列字段的分類匯總，單擊“分類匯總”下的“自動”選項；如果要顯示內部行字段或列字段的分類匯總，單擊“分類匯總”下的“自定義”選項，然后單擊右面框中的某個匯總函數。如果要刪除分類匯總，可以單擊“分類匯總”下的“無”選項。如果要使用其他匯總函數，或是要顯示多種類型的分類匯總，可以單擊“自定義”右面框中的一個或多個匯總函數。例如，在前面的銷售統計表的數據透視表中，對字段“銷售人員”進行“求和”和“計數”分類匯總后的數據透視表如圖3-5-37所示。3在分類匯總數據組的頂部或底部顯示數據透視表分類匯總 如果要更改分類匯總的顯示位置，則字段或數據透視表必須以縮進格式顯示，而且必須顯示字段的分類匯總。具體的操作步驟如下：1）雙擊要更改其分類匯總顯示方式的字段。注意：只能更改外部行字段的分類匯總顯示方式，而不能更改其最內部（最右邊）的行字段。2）在“數據透視表字段”對話框中，單擊“版式”按鈕，屏幕上彈出“數據透視表字段版式”對話框，如圖3-5-38所示。3）在“數據透視表字段版式”對話框中，單擊選中“顯示選項”標題下的“以大綱形式顯示項目”選項框，然后選擇或清除“在組的頂部顯示分類匯總”復選框，再單擊“確定”按鈕。4）在“數據透視表字段”對話框中，單擊“確定”按鈕。3.5.5 創(chuàng)建數據透視圖如果需要快速更改圖表的視圖以便按不同的方式查看比較結果和預測趨勢，則可以使用數據透視圖報表。數據透視圖報表既具有數據透視表報表數據的交互式匯總特點，又具有圖表的可視優(yōu)越性。在創(chuàng)建數據透視圖報表時，Excel會自動創(chuàng)建相關聯的數據透視表報表。如果要基于外部源數據創(chuàng)建數據透視圖報表，那么在創(chuàng)建報表前可能需要檢索外部數據。創(chuàng)建數據透視圖的具體操作步驟如下：1）打開要在其上創(chuàng)建數據透視圖報表的工作簿。2）如果要基于Excel數據清單或數據庫創(chuàng)建報表，單擊數據清單中的單元格或數據庫。3）單擊“數據”菜單上的“數據透視表和圖表報告”命令。4）在“數據透視表和數據透視圖向導3步驟之1”的對話框中，指定待分析數據的數據類型，并單擊“所需創(chuàng)建的報表類型”標題下的“數據透視圖（及數據透視表）”選項框，然后單擊“下一步”按鈕。5）在“數據透視表和數據透視圖向導3步驟之2”的對話框中，選定要建立數據透視表的數據源區(qū)域，然后單擊“下一步”按鈕。6）如果以前曾經建立過具有相同數據源區(qū)域的數據透視表，Excel會提示“是否讓新報表建立在已現有報表相同數據上”？單擊“是”按鈕，可以節(jié)省內存空間并使工作表較?。粏螕簟胺瘛卑粹o，則新建報表與原有報表相互獨立。7）在“數據透視表和數據透視圖向導3步驟之3”的對話框中，指定要放置相關聯數據透視表報表的位置（新建一個工作表或嵌在現有工作表中）。而數據透視圖報表會自動在新的圖表工作表上創(chuàng)建生成，然后確定是否需要單擊“版式”按鈕。如果在向導的步驟3中單擊了“版式”按鈕，那么在向導中設置數據透視圖報表的布局之后，單擊“版式”對話框中的“確定”按鈕，然后單擊“完成”以創(chuàng)建數據透視圖報表。如果沒有在向導的步驟3中單擊“版式”按鈕，那么可直接單擊“完成”按鈕，然后在圖表工作表上設置數據透視圖報表的布局。注意：如果希望相關聯的數據透視表報表是不可見的，則可以在數據透視表報表所在位置上隱藏該工作表。如果已有了數據透視表報表，則可以使用該數據透視表報表創(chuàng)建數據透視圖報表，它將反映出數據透視表報表的視圖。下面使用銷售統計表的數據透視表創(chuàng)建數據透視圖報表，操作步驟如下：1）將鼠標指向“數據透視表”工具欄上的“數據透視表”菜單中的“選定”命令，然后在彈出的子菜單上單擊“啟用選定內容”，以確保該按鈕處于非按下狀態(tài)。2）從數據透視表中刪除所有的分類匯總。3）單擊“數據透視表”工具欄上的“圖表向導”按鈕，或者單擊“常用”工具欄上的“圖表向導”按鈕經過上述步驟后，Excel自動生成數據透視圖，如圖3-5-39所示。對數據透視表的許多操作也同樣適用于數據透視圖。例如，要隱藏分支機構“北京分公司”的數據，則單擊“分支機構”右側的下拉箭頭，在顯現的列表框中清除“北京分公司”復選框，然后單擊“確定”按鈕。這時，數據透視表圖表將自動更新，與“北京分公司”相關的數據不再出現在數據透視圖中，如圖3-5-40所示。 同樣，當隱藏數據項、顯示明細數據或在源數據透視表中重排其他字段時，從數據透視表創(chuàng)建的圖表會隨之發(fā)生變化。如果數據透視表有頁字段，那么當顯示不同頁時，圖表也會發(fā)生變化。當在頁字段的數據列表中顯示每一項時，Excel要更新圖表以顯示當前數據。與數據透視表一樣，即使刪除了數據透視圖，源數據也并不受影響。3.6 使用模擬運算表分析數據模擬運算表是工作表中的一個單元格區(qū)域，利用它可以顯示公式中某些值的變化對計算結果的影響。模擬運算表通過一步操作可以求出多種情況下的計算結果，因此它為同時求解某一運算中所有可能的變化值組合提供了方便。而且，它還可以將所有不同的計算結果同時顯示在工作表中，以便用戶進行查看和比較。模擬運算表分為單變量模擬運算表和雙變量模擬運算表兩種，下面分別給予介紹。知識點：3.6.1 單變量模擬運算表3.6.2 雙變量模擬運算表3.6.3 修改模擬運算表3.6.1 單變量模擬運算表單變量模擬運算表的結構特點是，所有的輸入數值被排列在一列中（稱為“列引用”）或一行中（稱為“行引用”）。雖然輸入單元格不必是模擬運算表的一部分，但模擬運算表中用到的公式必須引用輸入單元格。工作表中的任何單元格都可以用作輸入單元格，存放在輸入單元格中的輸入數據清單將會被替換。創(chuàng)建模擬運算表的操作步驟如下：1）在某一列或某一行中。鍵入將要替換工作表上的輸入單元格的數值序列。2）如果輸入數值被排成一列，則需在第一個數值的上邊一行且處于該數值列右側的單元格中，鍵入所需的公式。然后在同一行中，第一個公式的右邊，依次鍵入其它公式。3）如果輸入數值被排成一行，則需在第一個數值的左邊一列且處于該數值行下方的單元格內，鍵入所需的公式。然后在同一列中，第一個公式的下方，依次鍵入其它公式。4）選定包含所有公式及需要被替換的數值的單元格區(qū)域。5）在“數據”菜單中，單擊“模擬運算表”命令，屏幕上彈出“模擬運算表”對話框。 6）如果模擬運算表是按列排列的，則在“輸入引用列的單元格”編輯框中，為輸入單元格鍵入列引用。如果模擬運算表是按行排列的，則在“輸入引用行的單元格”編輯框中，為輸入單元格鍵入行引用。然后單擊“確定”按鈕。 下面舉一個簡單的財務方面的例子。利用模擬運算表及PMT財務函數計算年利率在7.0%12.0%之間變化時，各種年利率下，5年期（60個月）貨款500000元，每月等額的還款金額。我們先舉另一例回顧一下PMT函數的用法：PMT函數可以根據固定利率及等額分期付款方式，計算出貸款的每期付款額。例如：假設貸款的年利率為8%，貸款金額（本金）為10000元，如果需要10個月付清全部貸款，則每個月需支付的金額（包括本金和利息）根據PMT函數可以求出：PMT(8%/12,10,10000) = -1037.03即每個月連本帶利需支付1037.03元，才能付清全部貸款。 以下是本例的具體操作步驟：1）按照模擬運算表的輸入要求，在工作表中輸入以下內容：貸款總額（500000）、固定年利率（7%）、貸款期限（60）、每月還貸款金額公式、年利率變化序列（7.5%-12.0%），輸入單元格式排列如圖3-6-1所示（注意：期限以月為單位計算）。2）在圖3.6.1中，單元格B4中的公式為“= PMT(B2/12,B3,B1)”；單元格E2中的公式為“= PMT(B2/12,B3,B1)”。3）選定單元格區(qū)域D2：E12，然后單擊“數據”菜單中的“模擬運算表”命令，彈出“模擬運算表”對話框，在“輸入引用列的單元格”中選擇或輸入“$B$2”。最后單擊“確定”按鈕。3.6.2 雙變量模擬運算表雙變量模擬運算表比單變量模擬運算表要略復雜一些，它與單變量模擬運算表的主要區(qū)別在于雙變量模擬運算表使用兩個可變單元格（即輸入單元格）。雙變量模擬運算表中的兩組輸入數值使用的是同一個公式，這個公式必須引用兩個不同的輸入單元格。創(chuàng)建雙變量模擬運算表后，兩組輸入數值分別代替兩個輸入單元格。創(chuàng)建雙變量模擬運算表的一般過程如下：1）在工作表的某個單元格內，輸入所需引用的兩個輸入單元格的公式。2）在公式下面同一列中鍵入一組輸入數值，在公式右邊同一行中鍵入第二組輸入數值。3）選定包含公式以及數值行和列的單元格區(qū)域。4）單擊“數據” 菜單中的“模擬運算表”命令，彈出“模擬運算表”對話框。5）在“輸入引用行的單元格”編輯框中，輸入要由行數值替換的輸入單元格的引用。6）在“輸入引用列的單元格”編輯框中，輸入要由列數值替換的輸入單元格的引用。我們把在前面的例子中規(guī)定的還款期限由固定的5年期改變?yōu)樵?-5年之間變化，即現在對計算的要求變成為：利用雙變量模擬運算表及PMT財務函數計算貨款500000元，年利率在7.0%12.0%之間變化時，各種年利率下，當還款期限在2-5年之間變化時，每月等額的還款金額。根據題目的要求，具體的操作可按如下步驟進行：1）按照雙變量模擬運算表的輸入要求，在工作表中輸入以下內容：貸款總額（500000）、固定年利率（7%）、固定還貸期限（60）、每月還貸款金額公式、年利率變化序列（7.5%-12.0%），還貸期限變化序列（24，36，48，60），輸入單元格式排列如圖3-6-3所示（注意：期限以月為單位計算）。單元格B4中的公式仍然為“= PMT(B2/12,B3,B1)”；單元格區(qū)域B7：B17為要作為替代輸入單元格的“年利率”序列；單元格區(qū)域C6：F6為要作為替代另一個輸入單元格的“還貸期限”序列。2）在單元格B6內輸入公式為“= PMT(B2/12,B3,B1)”，公式中的單元格B2（代表年利率）、B3（代表期限）將作為輸入單元格。3）選定單元格區(qū)域B6：F17。4）單擊“數據”菜單中的“模擬運算表”命令，彈出“模擬運算表”對話框。5）在“輸入引用行的單元格”框中，選擇或輸入要用行數值序列（即“還貸期限”序列C6：F6）替換的輸入單元格“$B$3”。6）在“輸入引用列的單元格”框中，選擇或輸入要用列數值序列（即“年利率”序列B7：B17）替換的輸入單元格“$B$2”。7）單擊“確定”按鈕。經過上述操作過程后，得到雙變量模擬運算表的計算結果如圖3-6-4所示。3.6.3 修改模擬運算表當創(chuàng)建了單變量或雙變量模擬運算表后，可以根據需要作各種修改。1）修改模擬運算表的計算公式。當計算公式發(fā)生變化時，模擬運算表將重新計算，并在相應單元格中顯示出新的計算結果。2）修改用于替換輸入單元格的數值序列。當這些數值序列的內容被修改后，模擬運算表將重新計算，并在相應單元格中顯示出新的計算結果。3）修改輸入單元格。選定整個模擬運算表（其中包括計算公式、數值序列及運算結果區(qū)域），然