數(shù)據(jù)透視表的格式設(shè)置

1、數(shù)據(jù)透視表的格式設(shè)置數(shù)據(jù)透視表最大的功能在丁分析。但我們?cè)谟玫臅r(shí)候往往不是分析給我們自己看的，而是分析給我們的 同事、老板、客戶供應(yīng)商或給一些相關(guān)的人員看的，這時(shí)候就要注意一下透視表的儀表形象了?！皭勖?之心，人皆有之”，如何美化數(shù)據(jù)透視表，讓透視表成為內(nèi)在美與外在美兼?zhèn)涞摹懊廊恕?，這也就是本 節(jié)課的學(xué)習(xí)內(nèi)容一一打造精美的數(shù)據(jù)透視表?？焖贋閿?shù)據(jù)透視表添加應(yīng)用格式(套用格式有兩種方法)：方法一：數(shù)據(jù)透視表工具欄，第二個(gè)按鈕“設(shè)置報(bào)告格式”，選擇想要套用的格式，確定。方法二：菜單欄格式，自動(dòng)套用格式，確定四類自動(dòng)格式的區(qū)別與特點(diǎn)第一類：報(bào)表 共十種格式。它的顯著特征是一維表格，相當(dāng)于一個(gè)列表,而且

2、不同優(yōu)先級(jí)的字段 呈縮進(jìn)形態(tài)。通過演示文件可以看到，套用報(bào)表格式后，列字段就插入到了行字段的最前面，也就是說 列字段優(yōu)先丁行字段。一一建議大家謹(jǐn)慎使用，因?yàn)樗鼤?huì)把你的布局都給改了。選擇自動(dòng)格式時(shí)，雙擊報(bào)表格式就可以，不用選中后再確定。第二類：表共十種格式。特征是二維表格，有行字段、列字段和數(shù)據(jù)區(qū)。數(shù)據(jù)區(qū)域不帶縮進(jìn)。格式套用后仍保留透視表的布局。第三類：傳統(tǒng)數(shù)據(jù)透視表只有一種格式。這是數(shù)據(jù)透視表的默認(rèn)格式，使用這種格式可以把套有格式的透視表“打回原形”。第四類：無(wú)只有一種格式?？梢园褦?shù)據(jù)透視表變得不像透視表。演示文件中發(fā)現(xiàn),它能去掉透視表數(shù)據(jù)區(qū)域上方的“數(shù)據(jù)”，將數(shù)據(jù)透視表偽裝成普通表格，這就是

3、無(wú)格式的用處?？梢钥吹剑凇鞍讋?dòng)套用格式”對(duì)話框中，有很多的選擇，從預(yù)覽圖中大概的可以看出，具體的為“報(bào)表110”、“表110”、“傳統(tǒng)數(shù)據(jù)透視表”、“無(wú)”，即在“白動(dòng)套用格式”對(duì)話框中提供了 22件“衣服”。2、四類白動(dòng)格式的區(qū)別與特點(diǎn)(1)報(bào)表在英文版的Excel里，“報(bào)表”顯示為“ Report”，從技術(shù)的角度講，其顯著特征體 現(xiàn)為一維的列表，不同級(jí)別的字段呈現(xiàn)縮進(jìn)形態(tài)，如圖可以看出，白動(dòng)套用“報(bào)表”格式之后，列字段插入到行字段的最前面，即列字段優(yōu) 先于行字段，由此可得，“報(bào)表”格式會(huì)改變數(shù)據(jù)透視表的布局，而且原有列字段優(yōu)先于 行字段。但也因?yàn)槿绱?，在一般情況下不推薦使用。(2) 表在英

4、文版的Excel里，“表”顯示為“Table”，所呈現(xiàn)的就是一個(gè)二維形態(tài)的表格， 與生成的數(shù)據(jù)透視表很相像，有行字段、列字段、數(shù)據(jù)區(qū)域，發(fā)現(xiàn)數(shù)據(jù)透視表的布局與最開始生成的一樣， 有行字段、列字段，同時(shí)給整個(gè)數(shù)據(jù)透 視表應(yīng)用了一些格式，包括不同顏色和底紋的標(biāo)題區(qū)、 匯總行等，即不會(huì)讓數(shù)據(jù)透視表看 起來像一張白紙?？梢钥闯觯氨怼辈粫?huì)改變?cè)袛?shù)據(jù)透視表的布局，只是把相應(yīng)單元格 的字體樣式、單元格背景、邊框等做了一些設(shè)置。(3) 傳統(tǒng)數(shù)據(jù)透視表如果應(yīng)用了 “報(bào)表”、“表”等格式后，不想使用了，想回到最初生成的數(shù)據(jù)透視表， 不需要?jiǎng)h了重新生成或手動(dòng)調(diào)整，只需應(yīng)用此項(xiàng)，即可恢復(fù)到最初的狀態(tài)。(4) 無(wú)傳

5、統(tǒng)數(shù)據(jù)透視表已經(jīng)非常樸素，但應(yīng)用“無(wú)”的格式，會(huì)去掉所有的邊框。這種格式 并不是在所有的情況使用都有效果，如圖如果想讓數(shù)據(jù)透視表呈現(xiàn)為普通的表格，可使用“無(wú)”的格式。如圖如果繼續(xù)對(duì)上表做一些修飾，就可以將數(shù)據(jù)透視表變得不像。自動(dòng)格式的優(yōu)點(diǎn)與缺點(diǎn)優(yōu)點(diǎn)：1、快：點(diǎn)開菜單，選擇喜歡的報(bào)表形式。2、自動(dòng)保鮮：當(dāng)對(duì)透視表套用了一個(gè)自動(dòng)格式以后，這個(gè)格式其實(shí)已經(jīng)不是衣服這么簡(jiǎn)單了，它更像 透視表的皮膚。無(wú)論你的透視表發(fā)生什么樣的布局改變，刷新、篩選或內(nèi)容增減，都不影響套用的格式 的顯示。如果不是自動(dòng)套用格式，彳艮難達(dá)到與透視表這種無(wú)縫隙結(jié)合的這個(gè)特點(diǎn)。缺點(diǎn)：1、樣式太少了：才22個(gè)，容易引起審美疲勞且缺少

6、個(gè)性。2、不支持自定義：只有應(yīng)用、確定的按鈕，沒有自定義。個(gè)性鮮明DIY除了使用自動(dòng)套用格式以外，接著學(xué)怎樣對(duì)透視表進(jìn)行個(gè)性化的格式 DIY。摩登套裝雖然好但數(shù)量有限, 如果滿足不了你的需求，那么可以自己動(dòng)手做個(gè)性化的套裝，打上自己的性格烙印，自己的品牌烙印。顯示或隱藏透視表的行、列總計(jì)還有一種方法就是直接在“表格選項(xiàng)”里修改隱藏方法一：把鼠標(biāo)移到行/列總計(jì)欄上，鼠標(biāo)變成向下或向右的實(shí)心小黑箭頭，單擊鼠標(biāo)右鍵，選擇 隱藏。隱藏方法二：?jiǎn)螕魯?shù)據(jù)透視表任意單元格，右鍵，選擇“表格選項(xiàng)”，取消勾選“列總計(jì)”和“行總計(jì)”。顯示方法：?jiǎn)螕魯?shù)據(jù)透視表任意單元格，右鍵，在“表格選項(xiàng)”下，勾選“列總計(jì)”或“行

7、總計(jì)”。啟用選定內(nèi)容特性，可批量選擇。注意：鼠標(biāo)應(yīng)停在單兀格的字段上，不能停在行號(hào)或歹0號(hào)上，這在透視表中是不允許的。鼠標(biāo)形狀相 同，位置不同，則意義不同。顯示或隱藏透視表的分類匯總項(xiàng)當(dāng)行字段和列字段有多個(gè)時(shí)，會(huì)默認(rèn)出現(xiàn)分類匯總的項(xiàng)目，相當(dāng)于分類匯總小計(jì)。隱藏方法一：把光標(biāo)停在其中一個(gè)分類匯總項(xiàng)上，當(dāng)光標(biāo)變成向右的實(shí)心小黑箭頭時(shí)，單擊鼠標(biāo)左鍵， 所有的分類匯總項(xiàng)就都會(huì)被選中（啟用選定內(nèi)容特性），單擊鼠標(biāo)右鍵，彈出的菜單中選擇“隱藏”。隱藏方法二：雙擊行字段名稱，彈出數(shù)據(jù)透視表字段對(duì)話框，分類匯總選項(xiàng)下選擇“無(wú)”。顯示方法：隱藏了哪一列的分類匯總，就雙擊哪一列的行字段名稱，在彈出數(shù)據(jù)透視表字段對(duì)

8、話框，分 類匯總選項(xiàng)下選擇“自動(dòng)”或“自定義”，確定。（自動(dòng)就是表示有，默認(rèn)是求和；自定義可以改變當(dāng) 前的匯總方式，如計(jì)數(shù)等）。顯示或隱藏透視表的明細(xì)項(xiàng)透視表里面不允許隱藏單個(gè)的單元格，因?yàn)橥敢暠硎且粋€(gè)整體區(qū)域，不能去刪除、插入，而此時(shí)乂不希 望出現(xiàn)某一些明細(xì)項(xiàng)，這就需要按照自己的希望顯示或隱藏。隱藏或顯示明細(xì)數(shù)據(jù)之一：右鍵，“組及顯示明細(xì)數(shù)據(jù)”下“隱藏明細(xì)數(shù)據(jù)”。隱藏或顯示明細(xì)數(shù)據(jù)之二： 雙擊某一個(gè)數(shù)據(jù)項(xiàng)即可顯示或隱藏，比較簡(jiǎn)便。合并居中顯示標(biāo)題內(nèi)容在透視表任意單元格，單擊鼠標(biāo)右鍵，“表格選項(xiàng)”，勾選“合并標(biāo)志”。數(shù)據(jù)透視表的居中是對(duì)整個(gè)表生效的，它跟菜單的合并居中是不一樣的。建議：合并居中

9、能不用就不用。每項(xiàng)后面插入空行 透視表中是不允許插入空行的，那樣會(huì)影響數(shù)據(jù)透視表的完整性。方法：雙擊字段名稱，打開“數(shù)據(jù)透視表字段”對(duì)話框，選擇“布局”，勾選“每項(xiàng)后面插入空行”， 確定。設(shè)置錯(cuò)誤值的顯示方式（讓錯(cuò)誤值顯示的更友好或不顯示）在透視表任意單元格，單擊鼠標(biāo)右鍵，表格選項(xiàng)，“格式選項(xiàng)”下勾選“對(duì)于錯(cuò)誤值，顯示（V）：確定。內(nèi)可以為空、“錯(cuò)誤”、“X”等，可以根據(jù)你自己的需求來決定。錄入特殊字符的快捷方式：“X”快捷鍵：alt + 41409 （小鍵盤）W 快捷鍵：alt + 41420 （小鍵盤）處理“空白”對(duì)丁數(shù)據(jù)項(xiàng)有“空白” 單元格：在透視表任一單元格，單擊鼠標(biāo)右鍵，表格選項(xiàng)，格

10、式選項(xiàng)，勾選“對(duì) 于空白選項(xiàng)，可以在空框處輸入你想顯示的內(nèi)容，如 wrong等。對(duì)丁行字段有“空白”字符：選中任何一個(gè)包含空白的內(nèi)容，復(fù)制，查找替換，查找內(nèi)容粘貼剛才復(fù)制 的內(nèi)容，替換為（空格）。分類匯總c自動(dòng)如廣自定義也）,無(wú)值）求計(jì)平量最乘和數(shù)均大小積值值值款靠透裸表字底布扃顯示選項(xiàng)岸:以報(bào)表形式顯親頂目世L以天嗣形我顯示項(xiàng)目Q）r在組的頂部顯示的類匯總cd） r每項(xiàng)后面插入空行魚）打印選項(xiàng)廠每項(xiàng)后面插入為頁(yè)符（D確定 | 取消*11 7-1II-V 15 11X設(shè)置單元格格式行標(biāo)題：選中，可以換一個(gè)背景，進(jìn)行填充、加粗。匯總項(xiàng)：?jiǎn)⒂眠x定內(nèi)容，進(jìn)行填充背景，字體加粗，變換字體顏色。數(shù)據(jù)區(qū)域

11、：設(shè)置數(shù)據(jù)格式區(qū)域的時(shí)候，除了手動(dòng)設(shè)置的方式，最好還是進(jìn)入數(shù)據(jù)字段的字段設(shè)置里面去 做設(shè)置。如：做求和項(xiàng)的金額，雙擊字段，進(jìn)入“數(shù)據(jù)透視表字段”，數(shù)字下進(jìn)行相應(yīng)的設(shè)置。這樣設(shè) 置的格式，不會(huì)因刷新、篩選等發(fā)生格式的丟失，它的格式是持久保鮮的。智能外觀通過自定義數(shù)字格式、條件格式，讓你的數(shù)據(jù)透視表有一個(gè)智能的外觀，在DIY的基礎(chǔ)上更近一步，可以像變色龍一樣，隨著環(huán)境的變化、需求的變化、數(shù)據(jù)源的更新而去做一個(gè)變動(dòng)。稱之為智能外觀。改變數(shù)值的顯示方式（自定義數(shù)字格式）1、將單元格中不太人性化的字符顯示為 W方法一：全部選中，設(shè)置單元格格式。此方法簡(jiǎn)單，不能永久保存。方法二：雙擊計(jì)數(shù)項(xiàng)，數(shù)據(jù)透視表字段

12、，數(shù)字，自定義，類型中按ALT鍵輸入“41420”顯示 如”，確定。如果想讓/變成紅色，則在自定義類型直接輸入 紅色“/”即可。注意：必須在英文輸入狀態(tài)下。2、如班級(jí)下面是純數(shù)字想在數(shù)字后面加一個(gè)“班”字雙擊班級(jí)，數(shù)字，自定義類型，輸入“ G/通用格”班” ”，確定。0.00”元”自定義數(shù)字格式什么時(shí)候能用？只能對(duì)有數(shù)字的字段去設(shè)置。因?yàn)槲谋咀侄尾藛沃袥]有此項(xiàng)顯示。隱藏零值方法一：工具，選項(xiàng)，視圖，勾選掉“零值”。此公共選項(xiàng)適合整個(gè)工作表里只有一個(gè)透視表。方法二：如果工作表中還有其他數(shù)據(jù)，只希望零值不在透視表中顯示，就只能用自定義數(shù)字格式去做。標(biāo)注負(fù)數(shù)：雙擊字段名，數(shù)據(jù)透視表字段選擇數(shù)字，數(shù)值

13、，選擇負(fù)數(shù)用紅字顯示格式；隱藏零值：雙擊字段名，數(shù)據(jù)透視表字段選擇數(shù)字，數(shù)值，點(diǎn)擊自定義，你會(huì)發(fā)現(xiàn)一個(gè)自定義代碼，無(wú) 論在哪個(gè)代碼的后面輸入；確定。這樣就同時(shí)達(dá)到了不顯示零值及負(fù)數(shù)為零的效果。代碼分成四段：正數(shù)；負(fù)數(shù)；零；文本。突出顯示符合條件的數(shù)值1、按數(shù)值大小（500以上用藍(lán)色表示，500以下用紅色表示）方法一：自定義設(shè)置格式：雙擊字段名，數(shù)字，自定義類型下面輸入“紅色>500G/通用格式；藍(lán)色<500G/通用格式”確定。方法二：選中數(shù)據(jù)區(qū)域，格式，條件格式，單元格數(shù)值，大于， 500,格式，顏色，藍(lán)顏色，確定。在添 加條件下，可同樣的設(shè)置小于 500的格式。2、按目標(biāo)值差距數(shù)

14、據(jù)透視表中的某一列數(shù)值與某一個(gè)目標(biāo)值比對(duì)的結(jié)果，根據(jù)這個(gè)結(jié)果去做相應(yīng)的突出顯示。方法一：選中合同結(jié)束列，格式，條件格式，公式， =E5<$H$1格式，圖案綠色，確定方法二：選中合同結(jié)束列，格式，條件格式，公式，=E5- “2007-9- 30” <0,格式，圖案綠色，確定。這兩個(gè)公式條件方法是等效的，那么根據(jù)這樣的條件格式，就可以得到數(shù)據(jù)透視表中的某一個(gè)數(shù)值或某 一列數(shù)值與某個(gè)目標(biāo)值去比對(duì)的結(jié)果，根據(jù)這個(gè)結(jié)果去做相應(yīng)的突出顯示。永恒的邊框傳統(tǒng)透視表格式的風(fēng)格里，只有標(biāo)題行、標(biāo)題列、總計(jì)稍微有一些邊框線，通過邊框添加表格線，刷新 后就沒有了。首先，尋找每行都有數(shù)據(jù)的列與每列都有數(shù)據(jù)的

15、行，判斷當(dāng)前這個(gè)單元格是不是應(yīng)該有邊框線。然后， 設(shè)置條件格式：定位到最右下角的這個(gè)單元格，按住shift鍵，再去選左上角的這個(gè)單元格。條件格式為公式=AND（$A5<> ”，A$5<>” ”），設(shè)置邊框。視頻之四格式保鮮如果你的透視表格式設(shè)置好以后，當(dāng)你的某些情況、外在環(huán)境或者是你的布局發(fā)生了某些變化的時(shí)候， 格式可能會(huì)丟失。那么針對(duì)不同的透視表的變化情況，格式會(huì)產(chǎn)生不同的影響，這時(shí)候我們可以選擇 Excel 一些相應(yīng)的設(shè)置選項(xiàng)或者一些討巧技術(shù)去幫助大家能夠以不變適應(yīng)萬(wàn)變，不會(huì)受到數(shù)據(jù)透視表的 影響，也就是保鮮。注意透視表美化后的格式丟失問題方法一：盡量使用字段屆性方

16、法二：借助條件格式，重點(diǎn)在于變化中邊框的設(shè)置。解決列寬問題方法一:設(shè)置表格選項(xiàng)創(chuàng)建一個(gè)透視表，它會(huì)自動(dòng)套用格式（傳統(tǒng)數(shù)據(jù)透視表格式），設(shè)置列寬后，在表格選項(xiàng)下，取消勾選自動(dòng)套用格式，確定。再刷新的時(shí)候，列寬就不會(huì)變化了。優(yōu)點(diǎn)：最簡(jiǎn)便的一種方式。缺點(diǎn)：從此以后不能再使用透視表的自動(dòng)格式了。方法二：利用空格這是一種討巧的辦法，在列字段的標(biāo)題兩側(cè)加空格來達(dá)到你想要的列寬，加空格后刷新，就可以看到列 的變化。方法三：借助宏（終極武器）錄制一段宏（重新設(shè)置歹0寬的宏）,在菜單欄上右擊，選擇 Visual Basic,調(diào)出Visual Basic對(duì)話框, 選擇第二個(gè)按鈕，錄制宏，錄制新宏，直接點(diǎn)確定，就O

17、K了。下面你做的每一步都會(huì)被 Excel記錄成宏代碼，這個(gè)宏代碼會(huì)在將來進(jìn)一步設(shè)定之后，可以在每次透視 表刷新后進(jìn)行運(yùn)行?，F(xiàn)在需要做的是重新設(shè)置列寬，選中要調(diào)整列寬的所有列，拖動(dòng)列寬至滿意的寬度，調(diào)整完了，這個(gè)操 作就行了。下面點(diǎn)擊 Visual Basic 對(duì)話框第2個(gè)按鈕，停止錄制，這樣代碼就出來了。查看運(yùn)用代碼：在所錄制宏的工作表sheet3標(biāo)簽上按鼠標(biāo)右鍵，選擇查看代碼，會(huì)出現(xiàn)一個(gè)界面，在工 程窗口的下方有一個(gè)模塊，雙擊模塊，在右邊會(huì)出現(xiàn)一段代碼：”columns （ “C:HT ）。select se lection.columnwidth=10.5”復(fù)制代碼，然后在工程窗口雙擊次工

18、作簿中sheet3的名稱，上面通用處點(diǎn)擊 Worksheet,右邊選擇PivotTableUpdate，把代碼粘貼到end sub之前，然后將end sub 之后的刪除?；氐酵敢暠砗?，刷新，會(huì)發(fā)現(xiàn)宏代碼已經(jīng)生效。VBA的大名早已如雷貫耳，但從沒接觸過，總覺得那是非常復(fù)雜、非常深?yuàn)W的DD對(duì)它敬而遠(yuǎn)之。在看過周老師的操作后，覺得用 VBA更簡(jiǎn)單、更方便，今后有時(shí)間得學(xué)學(xué)關(guān)于 VBA的知識(shí)了。 解除數(shù)據(jù)透視表的列數(shù)限制?在一個(gè)表格創(chuàng)建多個(gè)字段的時(shí)候，如果行字段達(dá)到 6個(gè)或6個(gè)以上，就只能加入一個(gè)數(shù)據(jù)字段，加第 個(gè)會(huì)報(bào)錯(cuò)。正確的操作方法是先將字段拖到數(shù)據(jù)區(qū)域， 再把數(shù)據(jù)拖到列字段上面（愛拖多少拖多少）讓它并排顯示, 再拖行字段區(qū)域即可。視頻之五美化實(shí)例把前面的功能申起來，來介紹從頭到尾介紹如何美化數(shù)據(jù)透視表，綜合的將技巧融會(huì)貫通一下。貞字段的美化：表格選項(xiàng)，頁(yè)面布局。自動(dòng)套用格式：選擇自動(dòng)套用格式中的“無(wú)”，表格選項(xiàng)，勾選 合并標(biāo)志。設(shè)置自動(dòng)篩選：選中歹0標(biāo)題最后一個(gè)字段旁邊的空白單元格，數(shù)據(jù)