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