第14章 費用使用情況分析

1、ExcelExcel數(shù)據(jù)透視表數(shù)據(jù)透視表入門與實戰(zhàn)入門與實戰(zhàn)清華大學出版社清華大學出版社第十四章第十四章費用使用情況分析本章要點定義單元格名稱創(chuàng)建動態(tài)的數(shù)據(jù)透視表添加計算字段更改標簽字段名稱更改報表樣式和布局將沒有超支數(shù)據(jù)底紋取消使用VBA禁止字段操作保護數(shù)據(jù)源結(jié)構(gòu)不被更改共享數(shù)據(jù)透視表上傳OneDrive備份共享學習目標學習目標在本章中，我們將通過使用名稱、設(shè)置數(shù)據(jù)格式、添加字段和套用數(shù)據(jù)透視表樣式、條件規(guī)則和VBA代碼等知識來制作一個費用使用情況分析的數(shù)據(jù)透視表，幫助用戶更好的、更靈活的使用基礎(chǔ)知識來制作出完整報表。同時，達到舉一反三的目的。14.1 案例制作效果和思路在分析金額的使用情況

2、時，主要是查看超支或沒有用的金額數(shù)據(jù)，從而分析出金額的配置合理性，如圖14-1所示是制作的費用明細分析報表的部分效果。如圖14-2所示是制作該案例的大體操作思路。14.1 案例制作效果和思路14.1 案例制作效果和思路如圖14-2所示是制作該案例的大體操作思路。14.2 創(chuàng)建動態(tài)的數(shù)據(jù)透視表我們要對費用使用情況進行分析，首先需要創(chuàng)建一個數(shù)據(jù)透視表，而且這個數(shù)據(jù)透視表需要是動態(tài)的，然后對其進行結(jié)構(gòu)布局和格式設(shè)置，所以，其中要使用到定義名稱、套用表格樣式和字段添加等相關(guān)知識。14.2.1 定義單元格名稱要實時動態(tài)分析費用使用情況，就需使用到名稱來獲取已有和新添加的數(shù)據(jù)，其具體操作如下。14.2.1

3、 定義單元格名稱14.2.2 創(chuàng)建動態(tài)的數(shù)據(jù)透視表單元格名稱定義后，我們就可以根據(jù)名稱來創(chuàng)建動態(tài)數(shù)據(jù)透視表，其具體操作如下。14.2.2 創(chuàng)建動態(tài)的數(shù)據(jù)透視表14.2.2 創(chuàng)建動態(tài)的數(shù)據(jù)透視表14.2.3 添加計算字段在實際中費用的使用情況，一定程度上可以從預算和實際開支之間的差額體現(xiàn)出來，所以，我們需要手動添加“超/省金額”字段，具體操作如下。14.2.3 添加計算字段14.2.4 更改標簽字段名稱我們的費用字段都只有一列，所以沒有多列進行求和的操作，同時，添加字段的計算方式是減法，所以，在數(shù)據(jù)透視表的標題行中包含“求和項:”字樣不太符合實際，可將其取消掉，具體操作如下。14.3 設(shè)置數(shù)據(jù)透

4、視表顯示樣式創(chuàng)建的數(shù)據(jù)透視表，雖然整體結(jié)構(gòu)已顯示完成，但看起來還是不夠美觀和專業(yè)，這時，還需要對其進行整體樣式、布局結(jié)構(gòu)以及數(shù)據(jù)字體字號的依次設(shè)置。14.3.1 更改報表樣式和布局要對數(shù)據(jù)透視表進行美化和結(jié)構(gòu)設(shè)置，這里主要從3方面入手，一是套用表格樣式；二是取消數(shù)據(jù)透視表中分類匯總行；三是對數(shù)據(jù)透視表字體字號以及數(shù)字顯示方式進行設(shè)置，其具體操作如下。14.3.1 更改報表樣式和布局14.3.1 更改報表樣式和布局14.3.1 更改報表樣式和布局14.3.2 使用顏色來展示超值程度由于每個部門在實際的費用中會因為項目的不同而出現(xiàn)多出或少用的情況，這時為了更好的顯示超預算情況，我們可以使用顏色來進

5、行區(qū)分，從而幫助我們在下次預算時能估算充分，具體操作如下。14.3.2 使用顏色來展示超值程度14.3.2 使用顏色來展示超值程度14.3.3 將沒有超支數(shù)據(jù)底紋取消在三色刻度顏色換分數(shù)據(jù)范圍時，明顯將一些沒有超支或低于預算的數(shù)據(jù)單元格劃分為與預算持平的顏色，為了更好的分析數(shù)據(jù)，我們將其底紋取消，具體操作如下。14.3.3 將沒有超支數(shù)據(jù)底紋取消14.4 保護數(shù)據(jù)透視表由于我們的數(shù)據(jù)透視表要進行共享，讓其他相關(guān)人員也能查看到相應(yīng)數(shù)據(jù)和分析結(jié)果，同時，要保證數(shù)據(jù)透視表的結(jié)構(gòu)完整和數(shù)據(jù)安全，這時，可以對其進行相應(yīng)的保護。14.4.1 使用VBA禁止字段操作數(shù)據(jù)結(jié)構(gòu)確定后，為防止其他人員對字段進行添

6、加或減少，所以要對字段設(shè)置保護，禁止他人添加或刪除字段操作，具體操作如下。14.4.1 使用VBA禁止字段操作14.4.1 使用VBA禁止字段操作14.4.1 使用VBA禁止字段操作14.4.1 使用VBA禁止字段操作14.4.1 使用VBA禁止字段操作14.4.2 保護數(shù)據(jù)源結(jié)構(gòu)不被更改數(shù)據(jù)透視表的結(jié)構(gòu)和分析結(jié)果很大程度上取決于數(shù)據(jù)源，因此，要對數(shù)據(jù)透視表進行保護，還需從數(shù)據(jù)源入手，但本例中數(shù)據(jù)源允許用戶輸入費用最新數(shù)據(jù)，這時，我們就可以通過設(shè)置可編輯區(qū)來解決，具體操作如下。14.4.2 保護數(shù)據(jù)源結(jié)構(gòu)不被更改14.4.2 保護數(shù)據(jù)源結(jié)構(gòu)不被更改14.4.2 保護數(shù)據(jù)源結(jié)構(gòu)不被更改14.4.

7、2 保護數(shù)據(jù)源結(jié)構(gòu)不被更改14.4.2 保護數(shù)據(jù)源結(jié)構(gòu)不被更改14.5 共享和備份數(shù)據(jù)透視表我們要讓局域網(wǎng)中的用戶都能查看和添加數(shù)據(jù)，就需要將其共享。為了保證數(shù)據(jù)最初的樣式和安全，我們可以在OneDrive上進行備份，同時讓遠方的人員能對數(shù)據(jù)透視表的數(shù)據(jù)進行查看和操作。14.5.1 共享數(shù)據(jù)透視表要讓數(shù)據(jù)透視表共享到局域網(wǎng)中，首先需要對工作簿本身進行共享，然后才能在局域網(wǎng)中進行共享，下面我們就將工作簿進行共享，具體操作如下。14.5.1 共享數(shù)據(jù)透視表14.5.2 上傳OneDrive備份共享為了防止工作簿的意外丟失，我們可以將其上傳到OneDrive中，同時，也方便遠方的人員查看和添加數(shù)據(jù)等，具體操作如下。14.5.2 上傳OneDrive備份共享14.5.2 上傳OneDrive備份共享14.6 案例制作總結(jié)和答疑在本章中，我們制作的費用使用情況數(shù)據(jù)