《Excel在财务中的应用》-第4章

问题情景描述【问题情景描述】

王主管是一间涂料生产厂的财务主管，他每月都要分析生产厂的生产成本数据。表4-1是该公司某年1~8月的产量与部分相关生产成本数据，请根据该数据，运用Excel的图表绘制该厂的成本产量线，并找出相应的固定成本与单位变动成本。【问题分析】

本例要运用Excel的散点图绘制成本产量线，并通过添加趋势线来找到该厂的二元一次函数线，从而找到单位变动成本和固定成本。在解决该问题之前我们首先要学习一些Excel的图表处理和图片处理功能。下一页返回问题情景描述【学习目标】理解Excel图表的基本概念;掌握Excel图表中的饼图、柱形图、折线图、散点图在财务中的运用;掌握Excel的插入剪贴画、图片、艺术字等功能。返回上一页第一节认识Excel的图表4.1.1任务目的及要求通过本次任务让读者能够认识Excel的图表及其分类，会根据图表数据绘制Excel的柱形图、折线图、饼图、组合图并对图形进行修改。由3~6人组成一个小组，共同学习Excel的图表及其分类，共同完成任务1所示的4项活动。下一页返回第一节认识Excel的图表4.1.2背景知识

1.Excel图表概述

Excel的图表具有很好的视觉效果，创建图表后，我们能够更清晰地看到数据间的差异。应用Excel的图表不仅可以清晰地显示数据间的差异，而且可以找到数据内的逻辑关系，找出数据的变化趋势，据以做出合理的预测。设计完美的图表与枯燥的数据清单相比，更能迅速有力的传递数据。

Excel一共提供了14种图表类型，每种图表类型还包含几种不同的子类型，子类型是在图表类型基础上变化而来的。每一种类型的图表都有各自不同的特点，读者可以根据需要选择使用哪一种类型。表4-2列出了不同类型的图表及用途。上一页下一页返回第一节认识Excel的图表2.柱形图

[活动4-1]：科1顺公司2009-2010年四个季度的净利润数据如表4-3所示，请据此表绘制柱形图。[操作步骤]:(1)将表4-3的数据录人Excel电子表格，如图4-1所示:(2)选取数据范围。利用鼠标连续选中工作表中该数据所在的区域，如果希望所使用的数据的行列标志也显示在图表中，则选定区域还包括含有行列标志的单元格，例如:图4-1表格中数据区域上方B2:F2区域及左方的表格B2:B4区域内容。

(3)选择图表类型。执行菜单栏命令[插入]\[图表]，打开如图4-2所示的[图表向导-4步骤之1-图表类型]对话框，该对话框左侧可以看出，图上一页下一页返回第一节认识Excel的图表表类型分为标准类型和自定义类型。在标准类型的[图表类型]中选择[柱形图]后，在对话框右侧显示柱形图的七个子图表类型，选择第一个[簇状柱形图]，点击[下一步]。

(4)选择数据区域。这时打开如图4-3所示的[图表向导-4步骤之2-图表源数据]对话框，切换到[数据区域]选项卡时将会出现预览的图表。预览图表下方的[数据区域]文本框中填列用于绘制图形的数据区域地址，图4-3中已有默认的数据区域，如果想修改数据区域可以点击文本框右侧的[折叠对话框]按钮，用鼠标重新选取数据区域，最后单击[折叠对话框]按钮返回对话框。下面的[系列产生在]选项组用于确定数据系列是横向还是竖向排列，这里选中[行]单选按钮。将图4-3的图表向导窗口切换到[系列]选项卡，在[系列]列表框中列出了用户所选择的所有数据系列名称，上一页下一页返回第一节认识Excel的图表可以通过位于列表框下面的按钮添加和删除数据系列。另外，可以在右侧的[名称]文本框中更改数据系列的名称，在[值]文本框中变更序列的数值，在[分类(X)轴标志]文本框中指定新的类别标志。这里使用默认设置，单击[下一步]按钮打开下一个对话框。

(5)设置图表属性。弹出如图4-4[图表向导一4步骤之3一图表选项]对话框。其中各个选项卡的功能如下:①[标题]选项卡中，可以输入图表标题，分类(X)轴名称、分类(y)轴名称。在本例图表标题中输入“科顺公司净利润图”。②[坐标轴]选项卡，在这里用户可以选择或者隐藏坐标轴。③[网格线]选项卡用于确定是否在图表中显示(主)次网格线。上一页下一页返回第一节认识Excel的图表④[图例]选项卡用于添加图例以及确定图例在图表中的位置。本例中，将图例放在图表的底部。⑤[数据标志]选项卡用于在图表中为数据点添加或删除数据标志。⑥[数据表]选项卡用于确定是否在图表下方的网格中显示数据系列的值。除了特别注明，本例均使用默认值，单击[下一步]按钮，打开下一个对话框。

(6)插入图表。弹出对话框如图4-5所示。该对话框用于确定图表存放的位置。该对话框中图表存放的位置有两个地址，第一是作为新工作表插入，如果选择此项，那么新建的图表会作为一个单独的工作表插入同一工作簿中，数据区域和由数据区域形成的图表不在一张工作表上。第二上一页下一页返回第一节认识Excel的图表是作为其中的对象插入，如果选择此项，同时在右侧选择需要插入的工作表名称，再点击[完成]，那么形成的图表将嵌人到指定的工作表中，同时会在该图表的边框处显示8个黑色控制点，可以用鼠标拖动图表到合适的位置，另外还可以拖动控制点调整图表的大小，新建的图表如图4-6所示。下面以科顺公司净利润图为例，分别介绍各个图表项的名称，如图4-7所示:①标题:图表的标题，一般位于图表的正上方。②图例:图例是对图表中数据系列的说明。③坐标轴:图表坐标轴的名称，分为分类轴和数值轴。④网格线:每种坐标轴都有主次网格线两种。⑤图表区域:图表区域是整个图表所有的图表项所在的背景区。上一页下一页返回第一节认识Excel的图表3.折线图

Excel电子表格可以对图表中的图表项进行修改，例如调整各图表项字体的大小，背景色;调整数值轴、分类轴主次网格线的显示;调整数值轴的间距;用户还可以根据需要调整数据系列的图表选项，将图表中某一系列的数据由柱形图改为折线图等。

[活动4-2]:将图4-6中所有图表项的字体显示为宋体，字号为9号;将标题改为“科顺公司2009-2010年净利润图”;将数值轴主网格线的间距由5,000调整为4,000;将两组数据系列的图表类型由柱形图改为折线图。调整后的图表样式如图4-8所示。

[操作步骤]:(1)光标点击图4-6所示的图表区域的空白位置，当该图表四周出现8个上一页下一页返回第一节认识Excel的图表黑色小方块时，表示该图表已被选中。点击鼠标右键，弹出菜单，选中[图表区格式]命令，弹出对话框如图4-9所示。

(2)在[图表区格式]对话框中，用光标选择[字体]选项卡，在该选项卡中在[字体]文本框中选择[宋体]，在[字形]文本框中选择[常规]，在[字号]文本框中选择[9]号字号，点击[确定]，则图表区域中所有的汉字及数字都调整到规定的大小。

(3)将光标移至图表项[标题]处，左键双击该标题，标题处于选中状态，此时可以直接修改标题的名称，将原标题修改为“科顺公司2009-2010年净利润图”。将光标移至标题处，点击鼠标右键，弹出菜单栏，选择[图表标题格式]后，还可以对图表标题的图案、字体等格式进行设置。上一页下一页返回第一节认识Excel的图表(4)光标点击图4-6中系列“2009年净利润(万元)”的柱形图，当该系列的所有柱形上都出现暗色的小方块时，表示该系列的图形被选中。点击鼠标右键，弹出图4-10所示的菜单，选择[图表类型]命令。弹出如图4-11所示的对话框，在图表类型中选择[折线图]，在子图表类型中选择[数据点折线图]，点击[确定]，形成新的图表如图4-12所示。

(5)同理可以将系列“2010年净利润(万元)”的柱形图调整为折线图。当选中一个系列点击鼠标右键，弹出如图4-10所示的菜单栏时，我们可以通过选择[数据系列格式]命令来调整图形的颜色、坐标轴、系列次序等内容。调整后的图表如图4-13所示。

(6)光标选择图4-13的数值轴，当数值轴两端出现黑色的小方块时，说明数值轴已被选中。点击鼠标右键，弹出菜单栏，选择[坐标轴格式]上一页下一页返回第一节认识Excel的图表命令，弹出如图4-14所示的坐标轴格式对话框，切换到坐标轴选项卡，在主要刻度单位文本框中，将数字“5,000”改为“4,000”即可。我们也可以通过图4-14的对话框中的[图案]选项卡调整坐标轴的图案，[字体]选项卡调整坐标轴的字体等。

4.饼图

[活动4-3]湘滨食品厂的股本构成情况如下:中国大洋食品开发集团占总股本49%，长岛食品研究所占总股本21%，企业高管层人员赵正华占总股本15%，钱小红占总股本10%，孙伟占总股本5%,请绘制图表，描述该厂的股本结构。

[操作步骤]:(1)在一张新工作表中输入湘滨食品厂的股本结构表如图4-15所示。上一页下一页返回第一节认识Excel的图表(2)连续选中图4-15中B2至G3单元格的区域，执行菜单栏[插入]\[图表]命令，弹出[图表向导一4步骤之1一图表类型]对话框，在标准类型中选择[饼图]，在右侧的子图表类型中选择[分离型三维饼图]，点击[下一步]。

(3)弹出对话框[图表向导一4步骤之2一图表源数据]，保持各文本框默认内容不变，点击[下一步]。

(4)弹出[图表向导一4步骤之3一图表选项]命令，选择[标题]选项卡，在[图表标题]文本框中输入[湘滨食品厂股本构成]。再选择[图例]选项卡，将图例置于图表的底部。再选择[数据标志]选项卡，在[数据标签包括]选择栏下，选择[类别名称]、[百分比]，这样图中将显示持股机构和人员名称及各自持股的比例。点击[下一步]。上一页下一页返回第一节认识Excel的图表(5)弹出[图表向导一4步骤之4一图表位置]命令，选中[作为其中的对象插入]，其他文本框默认，点击[完成]。

(6)根据数据区域生成的饼图的大体结构就形成了，但是还要做一些细微的修改，如果将字体再调整一下将使图表更好看。点击图表区域的空白处，当图表四周出现8个黑色的小方块时，说明图表区域已被选中，此时点击鼠标右键，弹出菜单栏，选择[图表区格式]命令，弹出[图表区格式]对话框，切换到[字体]选项卡，在该选项卡中，在[字形]文本框中选择[加粗]，在[字号]文本框中选择字号为[8]。其他文本框为默认值不变，点击[确定]。

(7)用光标选中标题，点击鼠标右键，弹出菜单栏，选择[图表标题格式]命令，弹出[图表标题格式]对话框，切换到[字体]选项卡，将字号调整为[10]号，点击[确定]，原图形调整为如图4-1所示的形式。上一页下一页返回第一节认识Excel的图表5.组台图以上实例都是在同一张图表中显示一个数值轴的情形，下面介绍在同一图表中运用两个数值轴的实例。

[活动4-4]万科公司2009-2010年每股收益及资产负债率情况如下表4-4所示，请在同一张图表中绘制2009-2010年每股收益以及资产负债率图形。[问题分析]:活动4-4中有两组数据，第一组数据是2009年全年以及2010年前3季度的每股收益，以元为计价单位，第二组数据是各时间段的平均资产负债率。两组数据的计价单位不统一，如果在同一图表中反映，不能使用同一数值轴，此时要用两个数值轴。

[操作步骤]:(1)在新工作表中输入万科公司的财务指标数据如图4-17所示，作为要建立图表的数据区域。上一页下一页返回第一节认识Excel的图表(2)选中图4-18中的B2至F4单元格间的区域，执行菜单栏命令[插入]\[图表]命令，弹出[图表向导一4步骤之1一图表类型]对话框，切换到[自定义类型]选项卡，选中[两轴线—柱图]，样图在该对话框的右边列示出来，如图4-18所示，点击[下一步]。

(3)弹出[图表向导一4步骤之2一图表源数据]对话框，保持所有文本框的默认值不变，点击[下一步]。

(4)弹出[图表向导一4步骤之3一图表选项]对话框，切换到[标题]选项卡，在[图表标题]文本框中输入“万科公司财务指标图”，再切换至[图例]选项卡，将图例的放置位置选为图形的底部，点击[下一步]。

(5)弹出[图表向导一4步骤之4一图表位置]对话框，选中[作为其中的对象插入]，其他文本框默认值不变，点击[完成]按钮。形成的财务指标图如图4-19所示。上一页下一页返回第一节认识Excel的图表4.1.3操作步骤下面运用Excel的散点图来解决本项目最初设置的情景问题。该情景问题涉及的财务知识即为成本的属性。在实际生产过程中，生产成本随产量变化而表现不同，有的成本不随产量的变动而变动，这种成本为固定成本;有的成本随产量的变化而成正比例变化，这种成本为完全变动成本;有的成本随产量的变化而变化，但不是正比例变化，这种成本为混合成本。在实际工作中，我们拟合生产成本随产量变动的图形，大约为一个二元一次函数线的形式，即y=ax+b，其中a表示单位变动成本，b表示固定成本，这条线可以在Excel图表中，通过绘制散点图并添加趋势线找到。操作步骤如下:(1)在新建的工作表中输入该涂料厂20xx年1-8月产量与相应的生产成本数据，如图4-20所示:上一页下一页返回第一节认识Excel的图表(2)用鼠标选中B3至J4单元格的单元格区域(注意这里一定不要选中B2至J4单元格间的区域)，执行菜单栏[插入]\[图表]命令，弹出[图表向导一4步骤之1一图表类型]对话框，在图表类型中选择[XY散点图]，在[子图表类型]中选择第一个图形[散点图]，比较成对的数值，如图4-21所示。点击[下一步]。

(3)弹出[图表向导一4步骤之2—图表源数据]对话框，保持所有的文本框默认值不变，点击[下一步]。

(4)弹出[图表向导一4步骤之3一图表选项]对话框，切换到[标题]选项卡，在[图表标题]文本框中输入“涂料厂20xx年1-8月成本产量线”，在[数值(X)轴]文本框中输入“产量(吨)”，在[数值(y)轴]文本框中输入“成本(万元)”。切换至[图例〕选项卡，将图例的位置调整为图表的底部，点击[下一步]按钮。上一页下一页返回第一节认识Excel的图表(5)弹出[图表向导一4步骤之4一图表位置]对话框，选中[作为其中的对象插入]，其他文本框保持默认值不变，点击[完成]命令。

(6)形成新的图表，通过调整该图表的字体大小，使图表变得更加美观，图表的样式如图4-22所示。

(7)图4-22中的八个点，代表该涂料厂20xx年1-8月各个月份的成本产量情况，这里X轴为产量，Y轴为成本。通过绘制该厂的成本产量散点图，我们发现该厂成本和产量存在近似的二元一次函数线关系，我们可以通过为散点图添加趋势线的形式来找到这个二元一次函数。

(8)光标点击18个散点中的任何一个，那么所有的散点均处于选中状态，点击鼠标右键，弹出菜单栏，如图4-23所示，选中[添加趋势线]命令，弹出如图4-24所示的[添加趋势线]对活框，在[类型]选项卡中选择上一页下一页返回第一节认识Excel的图表第一幅图[线性]，切换至[选项]选项卡，在该选项卡中，选中[显示公式]，如图4-25所示，点击[确定]按钮。

(9)形成如下的图表，如图4-26示，在该图表中，我们可以看到有一条直线，图中的8个点均匀分布在这条直线的两侧或者就在该直线上，该直线的二元一次方程式显示在该直线的左上方，即y=0.0075x+39.858。其中39.858万元代表该厂的固定成本，即在不生产的情况下，该厂也存在的生产成本，比如生产厂中机器设备的折旧费、收固定的生产工人的工资费用等，数据0.0075代表该厂的单位变动成本，即每多生产一吨的涂料，该厂要增加75元的生产成本。上一页返回第二节认识Excel的图片管理功能4.2.1任务目的及要求通过本次任务让读者学会插入图片、剪贴画、艺术字操作，学会使用Excel的绘图工具来绘制各种图形。由3~6人组成一个学习小组，以“我对会计专业或职业的认识”为题，在Excel工作表中完成一幅作品，阐释你对该题目的认识。可以通过绘制图表、插入图片、剪贴画、艺术字的形式，也可以通过Excel的绘图工具绘制图形、流程图的形式展示，时间30分钟，之后每个小组展示自己的组的作品并解释。下一页返回第二节认识Excel的图片管理功能4.2.2背景知识

在Excel电子表格中除了可以插入图表外，还可以插入纯粹的图形元素。这里所说的图表与图形的不同在于图表是建立在一个数据清单基础上的，由数据清单生成的图，而图形没有建立在数据清单基础之上，只是一个纯粹的图形。Excel电子表格可以通过插入图片、剪贴画、艺术字以及使用Excel绘图工具的方法将一些精美的图片、剪贴画插入电子表格，为表格填色，从而使制作出来的表格更加赏心悦目。

1.插入图片如果要在Excel电子表格中插入图片，首先选中图片插入位置所在的单元格，然后执行菜单栏命令[插入]\[图片]\[来自文件]命令，如图4-27上一页下一页返回第二节认识Excel的图片管理功能所示:

从图4-27可以看出图片的来源有几种，可以是剪贴画、可以来自扫描仪或照相机还可以来自自选图形，本案例图片来源!来自文件〕，选中后输入插入图片所在的位置，选中要插入的图片即可，如图4-28所示:以此种方式插入的图片是嵌人式的。双击嵌人的图片可以在Excel中进行编辑。如果需要调整图片的大小，可以单击该图片，此时该图片周围显示八个控制点，用鼠标指向其中的一个控制点，当鼠标变成双向箭头时，按住鼠标左键拖动图片使之适当的放大或者缩小。如果要编辑图片，可以点击该图片，点击鼠标右键，弹出菜单栏，如图4-29所示，选择:显示[图片]工具栏命令，弹出[图片]工具栏，如图4-30所示:上一页下一页返回第二节认识Excel的图片管理功能2.插入剪贴画

Office2003包含了一个剪辑库，收集了比以前版本更多的剪贴画。Office2003的各应用程序可以共享该剪辑库。用户可以将剪贴画加人自己的工作簿，还可以进行编辑。要插入剪贴画，首先选择要插入的位置，然后执行菜单栏命令[插入]\[图片]\[剪贴画]命令，打开如图4-31所示的剪贴画任务窗格，此时[剪贴画]显示框中尚无剪贴画显示，此时点击[搜索范围]文本框右侧的下拉按钮，选择剪贴画搜索的范围，再点击[剪贴画]任务窗格上的[搜索]按钮，此时多张剪贴画即被搜索并显示出来，如图4-32所示:3.绘图工具栏的使用使用Excel的绘图工具可以绘制直线、曲线等各种线条;正方形、上一页下一页返回第二节认识Excel的图片管理功能椭圆形等各种图形;各种箭头以及各种文本框等。通过灵活运用Excel的绘图工具栏，可以绘制我们想要的图形，例如流程图等。

(1)绘图工具栏。执行菜单栏命令[视图]\[工具栏]\[绘图]命令，弹出绘图工具栏，如图4-33所示:

其中，各主要命令按钮的功能分别如下:①[绘图]按钮:位于工具栏的最左端，单击此按钮将弹出一个下拉式菜单。可以利用其中的命令对图形进行编辑和修改，用于组合图形、改变叠放次序、对齐或分布、旋转或翻转图形等。②[选择对象]按钮:可以选择活动文档中的单个或多个图形对象。③[自选图形]菜单:单击该按钮，弹出一个下拉列表，从中选择需要插入的自选图形。上一页下一页返回第二节认识Excel的图片管理功能④[直线][箭头][矩形][椭圆]按钮:用于绘制直线、箭头、矩形和椭圆。⑤[文本框]按钮:绘制文本框，用于向图表和图片中添加文字说明。如果要将文字竖排，可以选择[竖排文本框]。⑥[插入艺术字]选项:在工作表中插入艺术字，Excel为用户提供了30种不同的艺术字样式。⑦[插入组织结构图]选项:可以插入组织结构图、循环示意图等简单图示。⑧[插入剪贴画]按钮:从Office2003自带的剪贴画管理器中选择图片插入到工作表中。⑨[填充颜色][线条颜色][字体颜色]按钮:可以为绘制的图形、线条或填充的文字设置颜色。上一页下一页返回第二节认识Excel的图片管理功能10.[线型]和[虚线线型]按钮:用于选择不同的线型。

11.[箭头样式]按钮:用于选择合适的箭头样式。

12[阴影]和[三维效果]按钮:绘制完图形后，可以选择[阴影]和[三维效果]按钮来为图形添加特殊效果。

(2)绘制线条。绘制直线的方法比较简单，首先单击[直线]按钮，这时候鼠标会变成细十字线型，在工作表中拖动鼠标即可画出直线，如图4-34所示。如果要绘制一条曲线，操作步骤如下:首先单击[绘图]工具栏中的[自选图形]按钮，打开图4-35所示的下拉列表，选择[线条]\[曲线]选项，然后，在工作表中单击确定曲线的起点，然后拖动鼠标确定第二个点的位置，与此类似，绘制第三个点的位置、第四个点的位置，直到双击结束绘制上一页下一页返回第二节认识Excel的图片管理功能操作为止。绘制箭头的方法与绘制直线的方法比较类似，单击[箭头]按钮，在工作表中拖动鼠标即可绘制带箭头的线条。

(3)绘制矩形、椭圆和多边形。在工具栏中单击[矩形]按钮，鼠标变成细十字形，然后在工作表中的目标位置单击确定矩形框的一个角点，接着拖动鼠标会出现一个矩形的虚框，当矩形的大小合乎要求时，单击结束绘制。如果在绘制的过程中同时按Shift键，可以绘制出正方形;如果在绘制的过程中按Alt键时，则会将矩形限制在网格线内。绘制椭圆的方法类似，按Shift键时，可以绘制出圆，按Alt键时，可以绘制出限制于网格线的椭圆。如图4-36所示是使用Shift键和Alt键绘图的例子。上一页下一页返回第二节认识Excel的图片管理功能

如图4-36所示的六个图形中，最左列的两个图形是单击了绘图工具栏的[矩形]和[椭圆形]绘制的图形，中间列的两个图形是单击了绘图工具栏的[矩形][椭圆形]后再按住Shift键绘制的正方形以及圆形;右边列的两个图形是单击了绘图工具栏的两个图形后再按住Alt键绘制的在边框线内的矩形、椭圆形。

(4)设置颜色和填充方式。在Exce12003中可以利用[绘图]工具栏中的[填充颜色]工具为指定的对象进行着色、增加图案与纹理等操作。如果要为选定图形添加颜色，首先选中需要设置填充颜色的图形对象，然后单击[绘图]工具栏中的[填充颜色]按钮，从下拉列表中选择一种填充颜色，如图4-37所示。如果要清除填充色，可以单击[无填充颜色]按钮即可。上一页下一页返回第二节认识Excel的图片管理功能

单击[绘图]工具栏的[填充颜色]按钮，从下拉列表中选择[填充效果]按钮，打开如图4-38所示的[填充效果]对话框。除了使用某种颜色填充外，Excel2003还提供了[图案][纹理]和[图片]填充方式，可以从中选择任何一种填充效果。

(5)设置艺术字。艺术字的运用可以起到美化文案的作用。一篇好的报告、文案都离不开各方面的修饰，其中艺术字的运用是其中重要的一个方面。使用[绘图]工具栏中的[插入艺术字]按钮可以添加或编辑特殊的文字效果，可以创建带阴影的、变形的、旋转地和扭曲的文字。要添加特殊文字效果，可以单击[绘图]工具栏中的[插入艺术字]按钮，打开图4-39所示的[艺术字库]对话框。上一页下一页返回第二节认识Excel的图片管理功能

在图4-39所示的艺术字库对话框中选择所需的艺术字形式，单击!确定〕按钮，打开图4-40所示的[编辑艺术字文字]对话框。在其中输入要生成特殊效果的文字，并选择所需字体和字形，单击[确定]按钮，Excel2003就将生成的艺术字插入到工作簿中，如图4-41所示。如果觉得图4-41所示的艺术字效果并不满意，可以点击[艺术字]工具栏的相应按钮对艺术字进行相应的修改。上一页