5.4 【財務】比率圖：賬款回收率
5.4
【財務】比率圖：賬款回收率
5.4.1製作應收賬款分類堆積柱形圖
在財務的應收賬款類別當中，根據應收賬款即客戶欠款總額，結合當期回款明細，如銀行承兌、往來沖抵、商業承兌、現金等方式，分析該客戶的回款率情況。其中，各類回款明細累加值為當期回款總額，在圖表展示中，即以堆積柱形圖的形式構建圖表。然而在數據源表中，如何有效利用Excel圖表工具製作出一部分數據系列為柱形圖（如欠款總額）的效果，而其他的數據系列為堆積柱形圖（如各類回款明細構成了回款總額）的效果？在本節中將通過針對某一客戶欠款/應收賬款總額及其回款情況的對比分析，呈現該客戶的回款情況。利用柱形圖與堆積柱形圖的組合，製作出類似於溫度計的統計圖表效果。
操作步驟如下。
①打開示例文件，選中A1：B5數據源區域，單擊【插入】選項卡，在【圖表】中選擇【堆積柱形圖】。選中圖表區域后，單擊【圖表】→【更改數據源】，即可在打開的【選擇數據源】對話框中看到如圖5-29所示的效果，單擊【確定】按鈕。
圖5-29
②選中區域后，單擊【設計】選項卡，選擇【類型】→【更改圖表類型】，之後在彈出的【更改圖表類型】對話框中選擇【組合】，將【A客戶欠款總額】的坐標軸類別勾選為【次坐標軸】，單擊【確定】按鈕（見圖5-30）。
圖5-30
③統一主、次坐標軸的刻度值。選中主、次坐標軸，單擊鼠標右鍵，選擇【設置坐標軸格式】，在右側的窗格中，將最小值修改為0，最大值修改為1000。
④圖表的美化。將圖表區域中單元格的底紋顏色設置為淡黃色。選中圖表后，單擊【圖表工具】→【格式】選項卡，將【形狀填充】和【邊框填充】均改為無填充顏色。
⑤刪除圖表區域中的坐標軸、圖表標題、網格線。
⑥設置欠款總額柱形圖。選中圖表中的【A客戶欠款總額】數據系列，對其填充方式進行修改：填充顏色和填充邊框均設置為淡粉色。然後單擊【設計】選項卡，之後單擊【形狀樣式】→【填充顏色】→【其他顏色填充】，在彈出的對話框中，將【自定義】下的【透明度】修改為78%。
此時，可見頂層【A客戶欠款總額】的色塊，因為透明度的變化，使其下方“已收”款項的各堆積柱形圖顯現出來了（見圖5-31）。
圖5-31
⑦隱藏【A客戶欠款總額】，完善其他堆積柱形圖的顏色設置。選中圖表區域后，單擊【設計】選項卡，之後選擇【數據】功能組中的【選擇數據】，取消勾選【A客戶欠款總額】。通過是否勾選各數據系列的方式，可以在圖表區域中實現顯示或隱藏某數據系列的效果（見圖5-32）。
圖5-32
⑧美化堆積柱形圖。將【已收-銀行承兌】、【已收-往來沖抵】、【已收-商業承兌】、【已收-現金】設置為不同填充顏色和邊框。
⑨調整分類間距。選中圖表區域后，單擊鼠標右鍵，選擇【設置數據系列格式】，在右側的【設置數據系列格式】窗格中選擇【系列選項】，將分類間距調整至最大，即500%（見圖5-33），使類似於“溫度計”的堆積柱形圖呈現細長的狀態。
圖5-33
⑩顯示並美化【A客戶欠款總額】。單擊【設計】選項卡，之後選擇【數據】功能組中的【選擇數據】，在彈出的對話框中勾選【A客戶欠款總額】（見圖5-34），單擊【確定】按鈕。在此可以看見圖表區域中再次顯示半透明的柱形圖形狀了。
選中【A客戶欠款總額】柱形圖后，單擊鼠標右鍵，選擇【設置數據系列格式】，在右側的窗格中將邊框的寬度調整至2.5磅，並修改分類間距為450%（見圖5-35）。
圖5-34
圖5-35
設置完成以後，圖表區域中【A客戶欠款總額】的柱形圖像一個外框一樣括在內部各回款情況的堆積柱形圖之外。
添加溫度計底部。在溫度計底部位置，單擊【插入】選項卡，之後單擊【形狀】→【橢圓】形狀，按住Shift鍵並拖曳鼠標，繪製完成一個正圓形。調整正圓形的配色方案和位置，並更改其顯示圖層為【置於底層】，讓它恰好放置於柱形圖下方（見圖5-36）。
用文本框的形式添加欠款總額。插入文本框，在【編輯欄】中輸入公式“=B2”。此時該文本框中顯示的值，就能夠與數據源中的B2單元格實現同步聯動。然後進一步設置文本框的顏色、大小和字體樣式，使其呈現在溫度計底部圓形圖上方位置。
添加回款情況的數據標籤。選擇堆積柱形圖區域，單擊鼠標右鍵，選擇【添加數據標籤】→【添加數據標籤】。選中數據標籤后，單擊鼠標右鍵，之後單擊【設置數據標籤格式】，在右側【設置數據標籤格式】窗格的【系列選項】中勾選【系列名稱】、【值】、【顯示引導線】（見圖5-37）。
圖5-36
圖5-37
調整標籤位置。數據標籤設置完畢后，用拖曳的方式將其放置於圖表中合適的位置。繪製完畢后，選擇任意一個圖表元素，按Ctrl+A組合鍵全選，然後單擊鼠標右鍵，選擇【組合】，即可將所有的繪圖區域組合為同一整體，完成本例的圖表繪製（見圖5-38）。
5.4.2製作波浪式完成率餅圖
比率類的圖表通常應用在回款率、指標完成率等業務場景，其主要呈現的是一個具體的比率數值。除本書前面講解的使用圓環圖、餅圖來製作以外，在綜合智能看板的圖表呈現中還會使用更高級的圖表美化技能（如：水平線在下，圓環框在上，通過圓環框下部水平線的高低表示完成率的大小），從而讓你的商務圖表更精美（見圖5-39、圖5-40）。
操作步驟如下。
圖5-38
①模擬完成率數據源。在A1單元格中輸入“比率”，在B1單元格中輸入隨機函數：=ROUND（）【說明：隨機生成0～1之間的任意小數】。設置單元格格式為百分比。選中A1：B1單元格區域作為數據源，單擊【插入】選項卡，之後單擊【圖表】→【二維柱形圖】。
圖5-39
圖5-40
②添加100%完成率的數據源。選中圖表區域后，單擊【設計】選項卡，之後選擇【數據】功能組中的【選擇數據】，在彈出的【選擇數據源】對話框（見圖5-41）中單擊【添加】按鈕，然後在【編輯數據系列】中添加數據源信息：在【系列名稱】中輸入“1”，【系列值】默認為【={1}】，單擊【確定】按鈕。說明：系列值中的【={1}】表示該數據系列的值等於1，也就是100%。
設置完成後，圖表區域將出現兩個柱形圖：一個柱形圖表示實際完成率，另一個柱形圖表示目標值100%。
圖5-41
③繪製圓環元素。單擊【插入】選項卡中的【橢圓】，並按住Shift鍵拖曳鼠標左鍵，繪製如圖5-42所示的兩個正圓、圓環圖。左側圓環圖：插入的圓環，內部填充顏色為無色，邊框為加粗的棕色邊框。右側圓形圖：插入的圓形，內部填充顏色為藍色，邊框為加粗的棕色邊框。
④將步驟③中已經繪製好的兩個圖形元素複製並粘貼到柱形圖中（見圖5-43）：左側圓環圖複製到圖表區域中完成率為100%的柱形圖上。右側圓形圖複製到圖表區域中左側實際完成率的柱形圖上。
圖5-42
圖5-43
⑤更改圖片的填充方式。選中左側藍色的圓形圖，單擊鼠標右鍵，選擇【設置數據系列格式】，在右側窗格的【填充與線條】選項卡中，將圖片的填充方式設置為【層疊】格式（見圖5-44），並將【系列選項】→【系列重疊】修改為100%。
設置完成後，原本分離的兩個柱形圖就合併、重疊到一起了。
⑥簡化圖表。選中圖表區域中的【坐標軸】，此時右側窗格變為【設置坐標軸格式】。修改坐標軸的默認範圍為固定值【0-1.0】（說明：經過手動調整的坐標軸範圍，右側的【自動】標識會變成【重置】的字樣）。
進一步刪除坐標軸、網格線（注意：當網格線不容易選中時，可單擊【設計】選項卡，之後單擊【添加圖表元素】→【網格線】→【主軸主要水平網格線】）。
選中整個圖表區域后，設置圖表的填充顏色和邊框填充均為無色。設置完成後，將圖表區域調整成如圖5-45所示的狀態，即可完成【樣式1】圓環在上、水平線在下的比率圖的製作。
圖5-44
圖5-45
⑦快速製作【樣式2】水平線填充在圓環中的比率圖。
將步驟⑥生成的圓環圖複製一份，選中新複製的比率圖后，單擊【設計】選項卡中的【選擇數據】，取消勾選【系列1】，即將其隱藏不顯示，而非直接刪除。
將【樣式2】所需的圖表元素複製並粘貼到新圖表中實際完成率的數據系列上（見圖5-46）。
圖5-46
⑧調整填充順序。選擇【樣式2】的比率圖，單擊【圖表工具】→【設計】選項卡，選擇【選擇數據】，在打開的【選擇數據源】對話框的【圖例項（系列）】區域中，調整系列【1】和系列【比率】的順序，確保將藍色的圓環圖部分顯示在圖表區域的最上方（見圖5-47）。
圖5-47
⑨製作【樣式3】水波紋形式的比率圖。
圖表結構解析：
·上層圓環圖，實際是內部填充顏色為無色的鏤空圓環；圓環區域外部的正方形區域，填充顏色為背景色，即淡粉色。
·下層水波紋為波浪形圖形素材。當波浪元素的柱身不夠高時，可通過繪製與其等寬的長方形並填充相同顏色後進行組合，即成為“水波紋”柱形素材（見圖5-48、圖5-49）。
圖5-48
圖5-49
⑩柱形圖的填充與美化。重複步驟③的操作技巧，然後複製到圖表區域中的實際完成率數據系列上，並修改其圖片填充方式為【伸展】（見圖5-50、圖5-51）。
圖5-50
圖5-51
填充完畢后，注意在圖表數據來源中調整【實際完成率】與【目標完成率100%】的先後順序，從而實現波浪在下、圓環在上的視覺效果。
插入完成率數顯框。單擊【插入】選項卡中的【文本框】，拖曳繪製一個空白文本框，選中該文本框后，在編輯欄中輸入函數（=$B$1），即可實現將單元格中的數值綁定到文本框中進行顯示的效果。這樣按快捷鍵F9（刷新公式運算）后，水波紋圖便可隨着B1單元格中函數（=ROUND（））值的變化而變化，也便實現了圖表與數據源實時聯動的效果。
設置完成後，修改文本框的填充顏色、邊框為無色，並設置字體大小等，即可完成最終效果圖（見圖5-52）。
小結：在實際工作中，各項工作任務、業績指標、人員離職率、答題正確率等都可以通過函數計算或數據透視表的方式計算出來，這樣輸出的就是一個具體的完成率數值。在商務圖表的可視化呈現中，這是衡量某項工作完成情況的最直觀的KPI之一。因而在設計和製作圖表時，可以在這個點上多花一些心思、運用一些可視化的技巧，讓圖表“靈動”起來。在數據呈現的同時，在受眾看圖的第一眼，就能緊緊抓人眼球，並讓他們認可你的工作付出和職業化的工作態度，為自己後面的工作彙報、方案確認奠定一個良好的形象基礎。
圖5-52