4.1 多工作表的數據匯總與分析

|第4章|

巧用輔助列

製作靈活多變的餅圖

在日常工作中，我們建議大家儘可能地將數據源匯總在同一張工作表中，進行原始數據記錄的管理。然而在實際工作中，因為個人製表習慣的不同，有時會將相同結構的數據分散到多個月份中進行記錄。比如：將各部門、各月度的費用支出情況分散在多張以月份命名的工作表中。那麼在製作年度費用的支出與預算對比情況時，這就比較麻煩了。首先要對數據源的多張表格進行匯總，然後進一步地查找各項目的預算情況，再做對比並製作商務圖表。不僅如此，在實際業務中可能還要統籌地去展示整個公司的預算使用率等。在這樣的商務圖表應用環境中，如果單純地基於原始數據表格，是無法有效完成數據的統計工作的。因此在圖表的製作過程中，我們會根據圖表呈現的需求，製作輔助數據源。尤其是餅圖、圓環圖的製作，會使用大範圍的輔助數據作為製圖數據源，以實現圖表高質量呈現的目標。

4.1

多工作表的數據匯總與分析

在數據源（見圖4-1）中，分4張工作表列示了各部門、各類費用的支出情況。對於只具備一行標題行、最左側一列標題列的數據源匯總，可以採用Excel合併計算的功能，快速實現多工作表的匯總與計算。而超出該要求的複雜表格，在進行合併計算和數據分析時，還將用到SQL、VBA等高階工具。

圖4-1

下面我們來看看合併計算在製作多工作表的數據匯總與分析方面的具體使用方法。

①打開示例文件，在【數據】選項卡的【數據工具】功能組中單擊【合併計算】按鈕，在彈出的【合併計算】對話框中，在【函數】中選擇【求和】，在【引用位置】處選中1～4表中的數據源區域，並依次添加。可在【所有引用位置】處瀏覽所添加的數據源匯總情況，勾選【標籤位置】中的【首行】和【最左列】（見圖4-2）。

圖4-2

②雙擊【開始】選項卡中的【格式刷】按鈕，啟用重複使用格式刷的功能。將表1～4中的格式樣式「刷」到匯總合併計算的工作表中（見圖4-3）。

③在F1單元格中輸入「小計」，在A27單元格中輸入「總計」，然後選中A1：F27單元格區域（所有數據源區域），按Alt+=組合鍵進行快速求和（見圖4-4）。

④查找各部門的預算值。在G1單元格中輸入「預算值」，在G2單元格中輸入公式=VLOOKUP（A2，部門預算！$A$2：$B$26，2，0），並雙擊單元格右下角的十字句柄，使其快速填充至整列，即可將部門預算表中該部門對應的預算值查找后的結果填寫到目標位置中，如G列（見圖4-5）。

⑤在H列新建一列預算判斷的列，其公式=G列的預算值-F列的費用發生小計值。設置完成後，對該列的單元格顯示格式進行進一步設置：選中H2：H26單元格區域，單擊滑鼠右鍵，選中【設置單元格格式】，在彈出的【設置單元格格式】對話框的【數字】選項卡中選擇【自定義】，在【類型】中輸入「＂預算內＂；[紅色]＂超預算＂；＂＂；」即可（見圖4-6）。

圖4-3

圖4-4

圖4-5

圖4-6

⑥為數據源設置條件格式。繼續選中H2：H26單元格區域，單擊【開始】選項卡，選擇【條件格式】→【圖標集】→【標記】中相應的圖標集（見圖4-7）。

圖4-7

⑦默認生成的圖標集規則並不匹配數據源中數值顯示的要求，需要進一步設置。此時繼續選中H2：H26單元格區域，單擊【開始】選項卡中的【條件格式】，之後單擊【管理規則】，在彈出的【條件格式規則管理器】對話框中選中第一個圖標集規則后，單擊【編輯規則】按鈕（見圖4-8）。

圖4-8

⑧修改圖標集的規則如下（見圖4-9）。

圖4-9

圖標：黃色感嘆號，當值是【＞0】時，類型為數字。

圖標：無單元格圖標，當值是【＜1】且【＞=0】時，類型為數字。

圖標：紅色×號，當值是【＜0】時，類型為數字。

⑨在H27單元格中輸入公式=SUM（H2：H26），並將顯示樣式修改為【常規】（效果參見圖4-10）。

圖4-10

至此，已經完成在表格內匯總並比較預算的執行情況一覽表。下面根據實際費用與預算的情況來製作預算執行率統計圖，效果如圖4-11所示。在圖4-11中可見，實際發生值99%用藍色圓環圖呈現，未完成的差額部分1%用橘黃色的細圓環圖標識在內圈，呈現出一種「劍」與「劍鞘」套疊在一起的效果。製作方法如下。

圖4-11

①按Ctrl鍵，依次選中F27和H27單元格，單擊【插入】選項卡中的【餅圖】進行設置（見圖4-12）。

②選中圖表區域后，單擊【設計】選項卡【數據】功能組中的【選擇數據】，在彈出的【選擇數據源】對話框中單擊【添加】按鈕，在彈出的【編輯數據系列】對話框的【系列值】中添加F27和H27兩個單元格（見圖4-13）。

圖4-12

圖4-13

③依次重複上一步驟，即在數據源中添加了4個一模一樣的數據源后，單擊【確定】按鈕（見圖4-14），在圖表區域中便出現了4層圓環圖。

圖4-14

④在【格式】選項卡下利用【形狀填充】和【形狀輪廓】進行圓環圖各元素的顏色設置，將圖表設置為如圖4-15所示的配色方案，即可將圖表設置為具有劍鞘樣式的圖表類型。注意，差額1%的部分，實際上把最外層和最內層的圓環設置為無填充顏色、無邊框填充的效果了。

圖4-15

⑤為圖表添加數據標籤。選中圖表區域中的圓環圖以後，單擊滑鼠右鍵，選擇【添加數據標籤】→【添加數據標籤】，並在右側的【設置數據標籤格式】窗格中將【標籤選項】修改為【百分比】。設置完成後，調整標籤的位置、字體大小等，即可完成圖表設計（見圖4-16）。

圖4-16