5.7 【生产】动态图表：车间产能看板

5.7

【生产】动态图表：车间产能看板

5.7.1产能数据的特点

在制造业，尤其是离散型制造业中，实时了解产能的情况是科学排产、发挥产能最大化的重要信息手段之一。因此，有效地将产能情况进行可视化的呈现也是产能类的商务图表的制作目标所在。

在本节中，我们将模拟某生产车间中各班组、各产品完工情况，以梳理其实际产能与目标完成率情况；并通过控件工具制作人机交互式的动态图表——根据操作者选择的内容，呈现出与之对应的图表效果。此外，还将剖析仪表盘类商务图表的具体制作方法。

5.7.2动态图表的应用介绍

在企业中，各个管理系统平台、数据库之间存在三大应用困难：

·业务系统数据过于分散，而通过软件公司二次开发的方式构建统一报表平台的成本较高，且客户化开发的软件功能固化，需求扩展性差。

·市场环境和业务需求多变，无法一次性穷举出所有的图表信息展示需求，无法快速地满足企业不断变化的报表需求。

·使用专业的编程软件，对操作者个人的应用水平要求较高。而且这类人员大多为IT业出身，在满足业务需求实践时需要付出大量的沟通成本。

而使用Excel来制作动态图表具备了简捷、专业、灵活3个重要特性，使其被越来越多的企业广泛应用，作为企业数据管理可视化呈现的有效手段之一。

·简捷：通过Excel商务图表制作的动态图表是完全基于Excel环境制作的，日常办公人员非常熟悉，没有二次学习成本。而且，能够通过控件、数据有效性等与函数的结合，实现“所见即所得”的人机交互式报表的呈现。

·专业：各类型的Excel商务图表，都可以基于Excel的环境进行设计和实践，能够制作出各类中国式的复杂报表，并且能够实现与数据源的填写、录入实时联动。其数据准确、图表高效。

·灵活：数据源的更新与商务图表的生成，支持根据管理者的不同维度进行关联和联动。可以随着管理动作的渐进明细，即随着各工序的推进、细化、计划调整与完工情况，逐步添加更多的商务图表。这样一来，就避免了固化管理系统扩容难的情况出现。

5.7.3产能看板动态图表的制作

1.用控件制作连续的5组计划产能与实际产能对比柱形图

具体步骤如下。

①调用【开发工具】选项卡。在默认情况下，Excel顶部的工具栏只有文件、开始、插入、页面布局、公式、数据、审阅、视图这8个选项卡，而不包含【开发工具】选项卡（见图5-66）。因此，需要将其调取出来，方便后面插入控件等交互式报表用到的工具。

图5-66

单击【文件】选项卡中的【选项】，在弹出的【Excel选项】对话框中选择【自定义功能区】，勾选【开发工具】（见图5-67）。

图5-67

单击【确定】按钮后，可见工具栏中已经出现了【开发工具】选项卡（见图5-68）。

②插入控件。单击【开发工具】选项卡，之后单击【插入】→【数值调节按钮】，按鼠标左键绘制控件。

图5-68

在【控件】中的相关按钮均可以用插入的方式，像绘制图形一样在Excel单元格区域内直接绘制（见图5-69）。

图5-69

③修改控件格式。选中已经插入的【数值调节按钮】控件后，单击鼠标右键，选择【设置控件格式】，在弹出的【设置对象格式】对话框中单击【控制】选项卡，进一步设置控件的属性：选择【单元格链接】框后，单击Excel中的K1单元格，之后单击【确定】按钮。这样实现了在调整控件上下小箭头时，其控制的值与单元格的值绑定（链接）的效果（见图5-70）。

④构建绘图数据源。在工作表顶部第1～3行的位置，我们通过函数的方法构建制作商务图表的数据源，使其实现在单击选择控件上下小箭头时，绘图数据源能够随着选择内容的变化，而关联到数据源第4～24行中对应内容的效果。

图5-70

这里构建的绘图数据源要实现以当前控件控制的【班组】为中心（见图5-71），并包含前后两个班组的【计划产能】、【加工总量】一共5个班组的数据情况。

此时，以D1单元格为中心，使其值=K1单元格的值，即绑定了【数值调节按钮】控件控制的单元格的值。

然后对其左右两侧单元格分别设置如下。

在B1：F1单元格区域（不包含D1）中分别输入（D1-2，D1-1，D1+1，D1+2）。

之后采用VLOOKUP函数，查找各组别对应的产值情况：

在B2单元格中输入复合函数（=IFERROR（（VLOOKUP（B1，$A$4：$C$24，2，0），””）），并将其应用于B2：F2单元格区域。

注意，如果被查找的【班组】超出数据源范围已经给定的1～20组之间时，则使用VLOOKUP函数查找出来的结果会出现错误；如根据班组序号21，使用VLOOKUP函数查找相应的结果，则会显示＃N/A的错误。此时只需在VLOOKUP函数外部嵌套一个IFERROR函数即可。即，当VLOOKUP函数查询的结果为错误值时，单元格返回的计算结果为＂＂（空）。当然，如果VLOOKUP函数运行的结果不是错误值，就返回其查找的对应结果（见图5-71）。

图5-71

⑤创建相邻组别产能情况柱形图。选中A1：F3单元格区域，单击【插入】选项卡，之后单击【图表】→【堆积柱形图】进行具体设置（见图5-72）。

图5-72

选中图表区域后，单击【设计】选项卡，之后选择【数据】功能组中的【选择数据】，选择对数据源的引用，以进行进一步的设置。

在弹出的【选择数据源】对话框中，选中【班组】系列后，单击【删除】按钮。然后选中【水平（分类）轴标签】下的【编辑】按钮，设置其【轴标签区域】为B1：F1单元格区域。设置完毕后，单击【确定】按钮（见图5-73）。

图5-73

⑥修改图表的类型。选中图表区域后，单击【设计】选项卡中的【更改图表类型】，在弹出的【更改图表类型】对话框中找到【组合】选项。将【计划产能】、【加工总量】数据系列的图表类型均修改为【簇状柱形图】，单击【确定】按钮。

回到图表操作区域后，选中任意一个数据系列（即柱形图），然后单击鼠标右键，选择【设置数据系列格式】，在右侧的【设置数据系列格式】窗格中，将柱形图的【系列重叠】修改为100%。

⑦柱形图的美化。选中图表区域中的【图表标题】，对其标题进行修改，例如命名为【班组计划与实际完成情况对比】。

固定坐标轴范围：因为各组的产能大小情况不尽相同，为了让各个生产班组的图表在同一标准之下进行呈现，我们需要将该图表的坐标轴范围进行固定。选中坐标轴以后，在右侧的【设置坐标轴格式】窗格中，将【坐标轴】范围设置为0～280。然后清洁图表版面：删除坐标轴以及网格线，之后调整柱形图的填充颜色（见图5-74）。

⑧添加数据标签。选中柱形图中需要添加数据标签的数据系列后，单击鼠标右键，选择【添加数据标签】→【添加数据标签】，在此添加数据标签。选择已添加的数据标签，在右侧的【设置数据标签格式】窗格的【标签选项】中，将【标签位置】设置为【数据标签内】。

图5-74

制作完毕后，可以单击【数值调节按钮】，查看到连续5组计划产能与实际产能对比的动态柱形图表。

2.制作相邻两组产品明细情况对比动态图表

具体步骤如下。

①制作制图数据源。在L2单元格中输入函数=VLOOKUP（$K$1，$A：$I，COLUMN（A4），0），将其向右拖曳至T2单元格。

说明：根据K1单元格的值，即由【数值调节按钮】控制的组数，在数据源列表区域中查找对应的班组、计划产能、加工总量以及A/B/C/D/E/F各产品的产值数据。其中，VLOOKUP函数的第三参数返回的列数，使用COLUMN函数嵌套得出（见图5-75）。

图5-75

COLUMN函数会返回引用单元格的列号，如A1单元格的列号=1。如果参数中不填写内容，则返回当前单元格的列号=COLUMN（）。

②计算上一组产能情况。在L3单元格中输入函数公式=IF（L2-1=0，1，L2-1），确保第3行数据为上一生产班组的实际情况；并使用VLOOKUP函数，将上一组的产值数据查找出来。M3单元格的函数公式=VLOOKUP（$L$3，$A：$I，COLUMN（B5），0）。

③插入柱形图。选择O1：S3单元格区域，单击【插入】选项卡，之后单击【图表】→【柱形图】。插入柱形图后，选中图表区域，单击【设计】选项卡，之后选择【数据】功能组中的【选择数据】（见图5-76）。

图5-76

在弹出的【选择数据源】对话框中，将数据系列的名称分别修改成单元格L2、L3的值，单击【确定】按钮（见图5-77）。

④固定坐标轴范围。修改坐标轴范围为0～30，用ColorPix取色器工具，将填充颜色修改为黄、蓝色系，并删除坐标轴、网格线，修改图表背景和边框为“无色”，即完成本图的制作。当单击【数值调节按钮】控件时，图表发生联动效果（见图5-78）。

图5-77

图5-78

3.制作仪表盘类图表

具体步骤如下。

①建立仪表盘标识。在K2单元格中计算该组别的完成率，即输入函数（N2/M2）。然后将该完成率绑定到文本框中予以显示。

单击【插入】选项卡中的【文本框】，在Excel空白区域绘制一个文本框。绘制完毕后选中文本框，并在编辑栏中输入：K1&＂组计划完成率：＂&ROUND（K2*100，0）&＂%＂（说明：K2单元格计算的是一个小数。将其乘以100即放大100倍以后，通过ROUND函数四舍五入保留到整数位，然后再通过拼接字符串的方法关联链接一个“%”，是将小数在公式显示结果中以百分比形式进行呈现的有效手段）。

插入完毕后，文本框的填充颜色、边框均设置为无色，字体、字号更改为微软雅黑10号。

②剪切仪表盘。在工具表中，将制作好的仪表盘模板剪切并粘贴到动态图表的相应位置，修改图表的相关配色方案，即可完成仪表盘与文本框的组合。

说明：因为仪表盘的绘制过程较为复杂，所以按照下文的方式在已经制作完仪表盘工具后，在工作中遇到相应的场景时，只需重复将其剪切到你需要布局的图表位置即可。无须从零开始一步步进行设置，这样可提高商务图表的制作效率（见图5-79）。

图5-79

【仪表盘的绘制】（见图5-80）

具体步骤如下。

①插入圆环图。按照A1：C24单元格区域输入对应的数值，然后选中A1：C24单元格区域，单击【插入】选项卡，之后在【图表】中单击【饼图】→【圆环图】，Excel默认插入一个3层的圆环图（见图5-81）。

图5-80

图5-81

②修改圆环设置。选择内部圆环，单击鼠标右键，之后选择【设置数据系列格式】，在右侧【设置数据系列格式】窗格的【系列选项】中，将【第一扇区起始角度】修改为“225°”，将【圆环图内径大小】修改为50%（见图5-82）。

③简化圆环图。删除图表标题和图例，并将最外层和最内层圆环的填充颜色和边框设置成无色。

④添加百分比刻度。选中最内层圆环，单击鼠标右键，选择【添加数据标签】→【添加数据标签】，在右侧的窗格中设置数据标签的格式为【无引导线】，并通过拖曳的方式将数据标签移至相适宜的位置（见图5-83）。

图5-82

图5-83

⑤圆环图的配色美化。将第二层即中间的那层圆环图，按照从0%到100%、由浅入深的配色方案，进行颜色填充。注意，将多余的非仪表盘的部分设置为无色填充（见图5-84）。

⑥指针的绘制。制作指针数据源如下。

图5-84

E1：指针；E2：=E6*200；E3：2（指针的宽度）；E4：=270-SUM（E2：E3）。这里，在E6单元格中会显示仪表盘需要显示的比率值。而E7单元格中为将其转化为整数的计算结果，E7的函数公式=ROUND（E6*100，0）。

选中E1：E4单元格区域，单击【插入】选项卡，之后单击【图表】→【饼图】进行设置（见图5-85）。

图5-85

选择已经插入的饼图后，单击内部圆环，之后单击鼠标右键，选择【设置数据系列格式】，在右侧的【设置数据系列格式】窗格中单击【系列选项】，修改【第一扇区起始角度】为225°，即与仪表盘的底部圆环图的起始角度一致（见图5-86）。

图5-86

⑦修改指针的颜色。将除指针之外的扇形区域的填充颜色和边框修改为无色，将指针修改为你喜欢的颜色，比如蓝色。

设置指针饼图的背景填充颜色与边框颜色均为无色，然后依次选中【仪表盘】与【指针】两张图表，单击【格式】选项卡，之后单击【对齐】→【水平对齐】→【垂直居中】，使这两张图表对齐。初步调整后，可进一步拖曳指针图表的大小，使其居中放置在仪表盘的中心区域。

⑧制作标签。单击【插入】选项卡，依次绘制3个【文本框】，之后分别在编辑栏中输入“您的业绩完成率为：”、“=E7”和“%”。调整文本框的字体、大小和位置，使其放置在仪表盘的合适位置，并作为标签显示当前仪表盘的百分比情况（见图5-87）。

图5-87

⑨插入控件，控制完成率数值。单击【开发工具】选项卡，之后单击【插入】→【数值调节按钮】。按住鼠标左键拖曳，于合适区域绘制数值调节按钮。绘制完毕后，选中【数值调节按钮】控件并单击鼠标右键，选择【设置控件格式】，在打开的【设置对象格式】对话框的【控制】选项卡中，设置【单元格链接】到E7单元格，【最小值】设置为0，【最大值】设置为100。设置完毕后，单击【确定】按钮（见图5-88）。

图5-88

设置完毕后，你可以单击【数值调节按钮】，看到仪表盘的指针随着数值的大小变化而变化了。