excel图表学习

目录

7.1

创建与修改图表 ........................................................................................................... 2 7.1.1认识Excel图表 ........................................................................................................... 2 7.1.2图表类型 ...................................................................................................................... 3 7.1.3建立图表 .................................................................................................................... 13 7.1.4建立复杂图表 ............................................................................................................ 16 7.1.5修改图表 .................................................................................................................... 17 7.2

迷你图 ......................................................................................................................... 19

7.2.1认识迷你图 ................................................................................................................ 19 7.2.2迷你图的分类 ............................................................................................................ 20 7.2.3创建迷你图 ................................................................................................................ 20 7.2.4自定义迷你图 ............................................................................................................ 21 7.3

动态图的创建 ............................................................................................................. 22

7.3.1自动筛选法 ................................................................................................................ 22 7.3.2添加辅助区域法 ........................................................................................................ 23 7.3.3定义名称法 ................................................................................................................ 24 7.3.4数据透视图 ................................................................................................................ 27 7.3.5使用VBA编程法 ....................................................................................................... 28 7.4

实用技巧 ..................................................................................................................... 30

7.4.1移动图表 .................................................................................................................... 30 7.4.2设置图表的大小 ........................................................................................................ 31 7.4.3向图表中快速添加数据系列 .................................................................................... 32 7.4.4制作静态图表 ............................................................................................................ 32 7.4.5多工作表作图 ............................................................................................................ 33 7.4.6折线图中空白单元格的处理 .................................................................................... 34 7.4.7数字条件控制图表 .................................................................................................... 35 7.4.8将坐标轴标题链接到单元格 .................................................................................... 36 7.4.9制作三维立体图表 .................................................................................................... 37 7.4.10在柱形图中使用图片 .............................................................................................. 39 7.4.11制作计划进度表 ...................................................................................................... 40 7.4.12绘制等高线图 .......................................................................................................... 41

1

7.5 实战练习 ..................................................................................................................... 43

7.5.1股票交易的动态研究 ................................................................................................ 43 7.5.2艾宾浩斯记忆曲线研究 ............................................................................................ 45 7.5.3本量利分析模型的创建 ............................................................................................ 47 小结 ......................................................................................................................................... 51

第七章图表展示

图表是Excel中一种强大的数据分析工具，使用图表可以直观地表现数据之间的各种关系，能够更为清晰地反映数据的差异、发展趋势以及预测走向。Excel提供了多种标准图形类型，包括柱形图、条形图、折线图、饼图、和XY散点图等，每种图表类型有包括若干小类。根据数据的不同和使用要求的不同，用户可以创建不同类型的图表以满足需求。本章类容将详细地讲解不同类型图表的建立和修改、迷你图的使用、动态图的建立等相关使用技巧。

7.1 创建与修改图表

7.1.1认识Excel图表 7.1.1.1图表的组成 首先，我们以“折线图”（详解见本章节后面内容）为例，了解图表的组成部分：以下是2012年某电脑城联想、戴尔电脑的销售情况（见下图7.1.1-1折线图）

图7.1.1-1折线图

7.1.1.2图表术语

– 数据点:一个数据点就是一个单元格的数值的图形表示；

2

– – – – – – –

数据系列:一组相关数据点就是一个数据系列； 网格线:有助于查看数据的可添加至图表的线条；

轴:图表中进行度量作为图表区一侧边界的直线(分类轴、数值轴等) ； 刻度线与刻度线标志:轴上与轴交叉起度量作用的短线；

误差线:与图的数据系列中每个数据标记都相关的潜在错误的图形线； 图例:标识图表中为数据系列或分类所指定的图案或颜色； 图表中的标题:表明图表或分类的内容。

7.1.2图表类型

7.1.2.1“嵌入式图表”和“图表工作表”的概念区别：

式图表：是把图表直接插入到数据所在的工作表中，主要用于说明数据与工作表的关系，用图表来说明和解释工作表中的数据. 如图7.1.1-1折线图。

图表工作表：图表与源数据表分开存放，图表放在一个独立的工作表中，图表中的数据存在于另一个独立的工作表中：

以7.1.1-1为例，Sheet1工作表存放数据，Chart1存放图表

图7.1.1-2

7.1.2.2图表类型

从大类上讲，分为标准类型和自定义类型。

标准类型：提供包括柱形图、折线图、饼图、条形图、面积图、散点图、组合图等图形种类，大约14种类型。

自定义类型：有对数图、折线图、饼图、蜡笔图等，不少于20种类型。 提示：柱形图、折线图、饼图、条形图、面积图又可分为二维和三维。他们区别在于二维是平面的，三维给人一种立体感。本章将以平面二维图表为主进行讲解。 下面将详细介绍各种图形的用法 柱形图

柱形图用于排列工作表中行或列中的数据，可以显示一段时间内的数据变化或说明各项之间的比较情况。柱形图又可分为簇状柱形图、堆积柱形图、百分比堆积柱形图等类型。

3

? 簇状柱形图：簇状柱形图使用二维垂直矩形显示工作表中的数据，可以比较多个类别的

数值。三维簇状柱形图是在二维的基础上采用三维透视效果显示表中数据。 ? 堆积柱形图：堆积柱形图使用二维垂直堆积矩形显示表中的数据，用于显示单个项目于

中体的关系。三维堆积柱形图同样是在二维的基础上采用三维透视效果显示表中数据。 ? 百分比堆积柱形图：百分比堆积柱形图使用二维垂直百分比堆积矩形显示工作表中的数

据，并且跨类别比较每个值占总体的百分比。三维百分比堆积柱形图同样是在二维的基础上采用三维透视效果显示表中数据。

图7.1.2-1所示为二维簇状柱形图，用于比较A产品和B产品的销售收入。

图7.1.2-1 簇状柱形图（二维）

图7.1.2-2簇状柱形图（三维）

图7.1.2-3 所示的图表为堆积柱形图，用于比较数值与整体的变化。

4

图7.1.2-3堆积柱形图

图7.1.2-4 所示的图表为百分比堆积柱形图，用于比较数值占整体的比例。

图7.1.2-4百分比堆积柱形图

折线图

折线图适合于显示相等时间间隔下数据的趋势，可以显示随时间变化的连续数据。在折线图中，类别数据沿水平轴均匀分布，所有的值数据眼垂直轴均匀分布。折线图分为折线图、带数据折线图、三维折线图3种类型。其中每类二维折线图又可分为折线图、堆积折线图、百分比堆积折线图。

? 折线图和带数据的折线图：可以带有数据标记显示图表中的单个数值，适用于显示随时

间或排序类别变化的趋势。 ? 堆积折线图和带数据的折线图：堆积折线图用于显示每个值所占百分比随时间或排序列

表变化的趋势，显示时既可以带有数据，也可以不带数据。带有数据标记的堆积折现图可以显示每个值所占比例大小随时间或排序类别变化的趋势。 ? 百分比堆积折线图和带数据标记的百分比堆积折线图：显示每个数值所占百分比随时间

或排序类别变化的趋势，显示时可以带有数据，也可以不带数据标记。

图7.1.2-5 所示的图表为折线图，用于显示数据变化的趋势，左图为不带数据图表，右图为带数据图表。

5

图7.1.2-5 折线图

图7.1.2-6 所示为堆积折线图，用于比较数值与整体的变化。

图7.1.2-6堆积折线图

图7.1.2-7所示为百分比堆积折线图，用于比较数值与整体的比例。

图7.1.2-7百分比堆积折线图

6

饼图

饼图用于排列工作表一行或一列中的数据，显示一个数据系列中各项的大小与各项总和的比例。饼图分为二维饼图、三维饼图和圆环图。二维饼图又可以分为饼图、复合饼图、复合条饼图。

图7.1.2-8 所示为饼图，用于显示表中A产品的一年销售变化。

图7.1.2-8饼图

图7.1.2-9 所示为复合饼图。

图7.1.2-9复合饼图

图7.1.2-10 所示为复合条饼图。

7

图7.1.2-10复合条饼图

条形图

条形图用于排列工作表中行或列中的数据，可以显示各项之间的比较情况。条形图同样分为二维和三维。每类条形图又可分为簇状条形图、堆积条形图和百分比堆积条形图。基于前面对“堆积”和“百分比堆积”的介绍，此处直接向大家展示三种图形。 图7.1.2-11 所示为簇状条形图，用于显示数值的变化。

图7.1.2-11 簇状条形图

图7.1.2-12所示为堆积条形图，用于显示数值与整体的变化。

8

图7.1.2-12堆积条形图

图7.1.2-13 所示为百分比堆积条形图，用于显示数值与整体数值的比例。

图7.1.2-13百分比堆积条形图

面积图

面积图用于排列工作表的行或列中的数据，可以突出显示数量随时间变化的程度。面积图分为二维和三维。每类又可分为面积图、堆积面积图、百分比堆积面积图。 图7.1.2-14 所示为面积图，用于显示数值的变化趋势。

9

图7.1.2-14面积图

图7.1.2-15 所示为堆积面积图，用于显示单个数值的变化趋势。

图7.1.2-15堆积面积图

图7.1.2-16 所示为百分比堆积面积图，用于显示单个数值所占百分比的变化。

图7.1.2-16百分比堆积面积图

10

XY散点图

XY散点图用于显示多个数据系列中各值之间的关系，沿横坐标（Ｘ轴）方向显示医嘱数值数据，沿纵轴（Ｙ轴）方向显示另一组数据。XY散点图分为散点图、带平滑线和数据标记的散点图、带平滑线的散点图、带直线和数据标记的散点图、带直线的散点图。 图7.1.2-17 所示为带直线和数据标记的散点图，用于比较成对的数值。

图7.1.2-17带直线和数据标记的散点图

气泡图

气泡图用于排列工作表中的数据，第一列为X值，在相邻列中显示相应的Y值和气泡大小的值。气泡图可分为二维气泡图和三维气泡图两种类型。 图7.1.2-18所示为三维气泡图，用于数值之间的变化趋势。

图7.1.2-18三维气泡图

股价图

股价图通常用来显示估计的波动，也可以用于科学数据。 图7.1.2-19所示为股价图，用于计算成交量。

11

图7.1.2-19股价图

曲面图

曲面图用于排列工作表的列或行中的数据，可以查找两组数据之间的最佳组合。曲面图分为三维曲面图、三维曲面图（框架图）、曲面图、曲面图（俯视框架图）。 图7.1.2-20所示为三维曲面图，用于显示数值的变化范围。

图7.1.2-20三维曲面图

雷达图

雷达图用于排列工作表中的行或列中的数据，可以显示多个数据系列的总值。雷达图分为雷达图、带数据标记的雷达图、填充雷达图。

图7.1.2-21所示为带数据标记的雷达图，用于比较各数值之间的差别。

12

图7.1.2-21带数据标记的雷达图

提示：本节还有一种组合图，即为前面所介绍的图形的两种或两种以上的组合，本节只介绍以上单独的基础图表，组合图将在本章“7.1.4节建立复杂图表”中讲解。

7.1.3建立图表

新建图表有多种方式，现以两种常用的方式介绍。

方法一：以某商场的销售数据表为例，数据如图7.1.3-1所示。

图7.1.3-1销售数据表

步骤一:1.单击工具栏中的“插入”命令，在菜单栏中找到“图表”区，区域如图7.1.3-2所示。

图7.1.3-2图表选项卡

步骤二:2.选择你要创建的图表类型（以簇状柱形图为例），单击图例可以看到中作表中多了一个空白“图表区”，如图7.1.3-4。

标志，如图7.1.3-3。

13

图7.1.3-3柱形图选项卡图7.1.3-4空白图表区

步骤三:3.在图表区右键单击，在关联菜单中选择“选择数据”，如图7.1.3-5。

图7.1.3-5图表关联菜单 步骤四:4.弹出对话框“选择数据源”，在图表数据区域中选择表中数据，如图7.1.3-6。

图7.1.3-6选择数据源 步骤五:5.单击“确定”按钮，即可得到图表，如图7.1.3-7。

图7.1.3-7销售柱形图

方法二：以上例数据为例。

步骤一:1.选中数据表区域，如图7.1.3-8。

14

图7.1.3-8销售数据表

步骤二:2.选择工具栏中的“插入”，在“图表”区选择你要插入的图表类型，即可直接得到图表，如图7.1.3-9。

图7.1.3-9销售柱形图

当然，我们也可以在方法二的基础上，找到另一种实用的方法。

在方法二中，步骤一的后面，我们可以发现，当我们选中了我们要使用的数据表区域的时候，右下角出现一个图标

，如图7.1.3-10。

图7.1.3-10销售数据表

单击图标，选择“图表”，可以看到一些推荐的图表，如图7.1.3-11。

图7.1.3-11推荐图表类型

单击“更多图表”，弹出“插入图表”对话框，可以看到推荐图表的预览图，也可以在“所有图表”选项卡中看到所有的图表类型预览图，你可以直观的选择你想要的图表类型，如图7.1.3-12。

15

图7.1.3-12插入图表

单击“确定”就可以得到同样的效果，如图7.1.3-13。

图7.1.3-13销售柱形图 提示：由以上两种方法可以看出，“方法二”更为简单快捷，我们常用“方法二”来创建图表。

7.1.4建立复杂图表

复杂图表即为复合图，复合图表是指在一个图表中表示两个或两个以上的数据系列，不同的数据系列用不同的图表类型表示。常用的有“簇柱形图-折线图”、“簇状柱形图-次坐标轴上的折线图”、“堆积面积图-簇状柱形图”。

打开菜单栏中的“组合图”图标即可看到以上三种常用的组合图，如图7.1.4-1。

图7.1.4-1组合图选项卡

? 簇状柱形图-折线图：主要用于突出显示不同类型的信息，通常在具有混

合类型数据的时候使用。

? 簇状柱形图-次坐标轴上的折线图：主要用于突出显示不同类型的信息，

通常在具有混合类型数据且表中的数据变化很大的时候使用。 ? 堆积面积图-簇状柱形图：主要用于突出显示不同类型的信息，通常在具

有混合类型数据的时候使用。

16

在这里，我们来以实例图区分“簇柱形图-折线图”（如图7.1.4-2）和“簇状柱形图-次坐标轴上的折线图”（如图7.1.4-3）的使用区别。

图7.1.4-2簇柱形图-折线图

图7.1.4-3簇状柱形图-次坐标轴上的折线图

可见，为了便于对比和分析数据，需要在同一图表中表达几种具有相关性的数据。但由于数据的衡量单位不同，所以“簇柱形图-折线图”就很难直观清晰地表达图表的意图，“簇状柱形图-次坐标轴上的折线图”能够解决这一矛盾。

7.1.5修改图表

图表的修改包括三个部分：图表类型修改、图表数据源修改、图表选项修改 图表类型修改

对于一个已建立的图表（包括嵌入式图表和图表工作表），可以对图表的类型进行修改。如对7.1.1-1进行图表类型修改为柱形图。

步骤一：选中绘图区右键单击，在弹出的关联菜单中选择“更改数据类型”，如图7.1.5-1。

17

图7.1.5-1图表关联菜单

步骤二：在弹出的对话框中选择你需要的图表类型“簇状柱形图”，单击“确定”，如图7.1.5-2所示。

图7.1.5-2更改图表类型

图表数据源修改

对于数据源中的数据发生了改变（包括删除、添加和更新），此时需要更改数据源，使得图表显示的信息与表格中的数据相一致。

图表数据源的修改与“建立图表”的“方法一”类似，直接右击“绘图区”，选择“数据源”，再单击“确定”即可。 修改图表选项

图表选项的修改是最常用操作，包括图表元素、图表样式、图表筛选器。 如图7.1.5-3所示，单击图表区的任何位置，可以看到右上方的出现三个标记。

图7.1.5-3图表标记

图表元素中又包含：坐标轴、坐标轴标题。图表标题、数据标签、数据表、误差线等等

18

选项，如图7.1.5-4。

图7.1.5-4图表元素

图表样式包含：样式和颜色，可以根据你的喜好对图表进行修改，如图7.1.5-5。

图7.1.5-5图表样式

图表筛选器包含：数值和名称，其作用有点不同于以上两种修改，它的作用仅仅是当鼠标移动到你要查看的数据选项上时，对你要查看的数据高亮显示，而其他数据则灰暗显示。

7.2 迷你图

7.2.1认识迷你图

迷你图是自从Microsoft Excel 2010时就新增的一个功能，它是工作表单元格中的一个微型图表，可提供数据的直观表示。使用迷你图可以显示一系列数值的趋势（例如，季节性增加或减少、经济周期），或者可以突出显示最大值和最小值。

虽然行或列中呈现的数据很有用，但很难一眼看出数据的分布形态。通过在数据旁边插入迷你图可为这些数字提供上下文。迷你图可以通过清晰简明的图形表示方法显示相邻数据的趋势，而且迷你图只需占用少量空间。尽管并不要求将迷你图单元格紧邻其基本数据，但这是一个好的习惯。

在使用迷你图时，你可以快速查看迷你图与其基本数据之间的关系，而且当数据发生更改时，可以立即在迷你图中看到相应的变化。除了为一行或一列数据创建一个迷你图之外，还可以通过选择与基本数据相对应的多个单元格来同时创建若干个迷你图，也可以通过在包含迷你图的相邻单元格上使用填充柄，为以后添加的数据行创建迷你图。

19

7.2.2迷你图的分类

迷你图分为“折线图”、“柱形图”和“盈亏”，如图7.2.2-1所示。 折线图：折线迷你图适合于显示相等时间间隔下数据的变化，可以显示数据随时间变化的变化趋势情况。

柱形图：柱形迷你图用于排列工作表中行或列中的数据，可以显示一段时间内的数据变化或说明各项之间的比较情况。

盈亏：盈亏迷你图用于突出显示一列或一行数据的大小、正负情况。

图7.2.2-1迷你图

7.2.3创建迷你图

步骤一：选择要在其中插入一个或多个迷你图中的一个空白单元格或一组空白单元格。 步骤二：在“插入”选项卡上的“迷你图”组中，单击要创建的迷你图的类型：“折线图”、“柱形图”或“盈亏图”，如图7.2.3-1。

图7.2.3-1迷你图菜单栏 步骤三：在“数据区域”框中，键入包含迷你图所基于的数据的单元格区域，再单击“确定”如图7.2.3-2。

图7.2.3-2创建迷你图

当在工作表上选择一个或多个迷你图时，将会出现“迷你图工具”，并显示“设计”选项卡，如图7.2.3-3所示。在“设计”选项卡上，可以从下面的组中选择几个命令中的一个或多个：“迷你图”、“类型”、“显示”、“样式”和“分组”。使用这些命令可以创建新的迷你图、更改其类型、设置其格式、显示或隐藏折线迷你图上的数据点，或者设置迷你图组中的垂直轴的格式。

图7.2.3-3迷你图工具栏

20

如果数据区域包含日期，则可以从“坐标轴”选项（“迷你图工具”--“设计”选项卡--“组”组--“坐标轴”按钮）中选择“日期坐标轴类型”，如图7.2.3-4。将迷你图上的各个数据点进行排列以反映任何不规则的时间段。比如，如果前三个数据点正好每个相隔一周，而第四个数据点相隔一个月，则第三个和第四个数据点之间的间距将按比例增加以反映更长的时间段。

也可以使用坐标轴选项为迷你图或迷你图组的垂直轴设置最小值和最大值。明确地设置这些值可帮助您控制比例，以便以一种更有意义的方式显示这些值之间的关系。

还可以使用“从右到左的绘图数据”选项来更改在迷你图或迷你图组中绘制数据的方向

图7.2.3-4坐标轴选项

7.2.4自定义迷你图

创建迷你图之后，可以控制显示的值点（例如，高值、低值、第一个值、最后一个值或任何负值），更改迷你图的类型（折线、柱形或盈亏），从一个库中应用样式或设置各个格式选项，设置垂直轴上的选项，以及控制如何在迷你图中显示空值或零值。 控制显示的值点

通过使一些或所有标记可见来突出显示折线迷你图中的各个数据标记（值），如图7.2.4-1。其步骤为：

1：选择要设置格式的一幅或多幅迷你图。

2：在“迷你图工具”中，单击“设计”选项卡。

3：步骤三在“显示”组中，选中“标记”复选框以显示所有数据标记。 4：在“显示”组中，选中“负点”复选框以显示负值。

5：在“显示”组中，选中“高点”或“低点”复选框以显示最高值或最低值。

6：在“显示”组中，选中“首点”或“尾点”复选框以显示第一个值或最后一个值。

图7.2.4-1迷你图显示选项卡

更改迷你图的样式或格式

使用“设计”选项卡上的样式库（当您选择包含迷你图的单元格时，“设计”选项卡将变为可用）。选择一个迷你图或一个迷你图组。若要应用预定义的样式，可以在“设计”选项卡上的“样式”组中，单击某个样式，或单击该框右下角的“其他”按钮以查看其他更多样式，如图7.2.4-2。若要更改迷你图或其标记的颜色，请单击“迷你图颜色”或“标记颜色”，然后在相应的下拉选项中选择迷你图或各显示点的颜色。

21

图7.2.4-2迷你图样式选项卡

显示或隐藏数据标记

在使用折线样式的迷你图上，可以显示数据标记以便突出显示各个值。

在工作表上，选择一个迷你图，在“设计”选项卡上的“显示”组中，选中任一复选框以显示各个标记（例如，高值、低值、负值、第一个值或最后一个值），或者选中“标记”复选框以显示所有标记，或者清除复选框将隐藏指定的一个或多个标记。 处理空单元格或零值

可以使用“隐藏和清空单元格设置”对话框（“迷你图工具”--“设计”--“迷你图(编辑数据)”--“隐藏和清空单元格”按钮，如图7.2.4-3）来控制迷你图如何处理区域中的空单元格（从而控制如何显示迷你图），如图7.2.4-4。

7.2.4-3迷你图编辑数据栏 7.2.4-4隐藏和空单元格设置

7.3 动态图的创建

动态图又称为交互式图表，用户通过对其源数据进行筛选或者对控件的操作，可以实现图表的动态更新，动态图表一般借助筛选、有效性、视图、窗体控件、函数公式、名称或数据透视图等功能来辅助实现。 提示：使用时应将Excel重新计算模式设置为“自动重算”，默认的情况为“自动重算”，对于一些高级会计师来说，一般会设置成“手动重算”，因为在大量的数据处理情况下，“自动重算”会使Excel反应迟钝，影响工作效率，对于普通用户来说，我们无需担心。 7.3.1自动筛选法 设置自动筛选法是实现动态图表最简单的方法。以如图7.3.1-1为例制作柱形图（部分数据有隐藏）。

图7.3.1-1信息统计表

22

步骤一：选取工作表中数据区域的任意一个单元格，单击菜单栏上的“数据””，再单击“筛选”图标

，得到如图7.3.1-2信息统计筛选表（部分数据隐藏）。

图7.3.1-2信息统计筛选表

步骤二：单击B2单元格的按钮，筛选出5月数据，数据区域只显示每年5月的行，然后再选中如表，创建柱形图，如图7.3.1-3，以此方法创建的图表具有自动更新图表的功能，当我们改变筛选的月份时，图表也跟着改变。

图7.3.1-3信息统计柱形图

7.3.2添加辅助区域法 在辅助单元格区域中，使用数据有效性下拉列表和查找引用、逻辑判断等函数，动态地对原始数据进行选择或处理，并在图表源数据中引用该辅助区域，可以达到交互式动态地更新图表的目的。 以职工离职推移表为例，通过添加辅助单元格区域的方法来制作动态图表，如图7.3.2-1所示。

图7.3.2-1职工离职推移表

步骤一：选取A11单元格，单击“数据”，在点击数据工具栏中的“数据验证”的图标

，

弹出“数据验证”对话框，在“验证”选项卡中，选取“允许”下拉单中的“序列”，在“来源”文本框中输入“=$A$4:$A$8”，点击“确定”即可，如图7.3.2-2所示。

23

图7.3.2-2数据验证栏

步骤二：选取A11单元格，单击右侧的按钮，在下拉列表中选择“生产部”。选取B11单元格，输入函数“=VLOOKUP($A$11,$A$4:$F$9,COLUMN(),0) ”。再将B11单元格的公式填充到C11:F11单元格，如图7.3.2-3所示。

图7.3.2-3函数参数栏

步骤三：以辅助单元格区域A11:F11为数据系列，A3:F3为分类轴（X轴）制作折线图，如图7.3.2-4。在A11单元格的下拉列表选择不同的部门，即可动态获得相应部门的离职推移图。

图7.3.2-4职工离职动态表

7.3.3定义名称法

在定义名称中使用OFFSET等函数与窗体控件（组合框、选项按钮、滚动条、微调项等）建立联系，从而实现由窗体控件控制的动态图表。

仍以离职推移表为例，通过定义名称法实现的动态图表的制作方法如下：

步骤一：打开“开发工具”工具栏。由于默认情况下，工具栏中没有“开发工具”栏，需要我们手动打开。点击“文件”——“选项”，如图7.3.3-1。单击“自定义功能区”，勾选主选项卡中的“开发工具”，单击“确定”。如图7.3.3-2。

24

图7.3.3-1 Excel首页

图7.3.3-2 Excel选项栏

步骤二：添加窗体控件选项按钮。单击“开发工具”——“插入”——“选项按钮（窗体控件）”，如图7.3.3-3。在表格区域画上一个选项按钮，并将文本内容改为“生产部”，如图7.3.3-4。

图7.3.3-3开发工具栏图7.3.3-4绘制控件

步骤三：设置窗体控件选项按钮。右键单击选项按钮，在弹出的快捷菜单中单击“设置控件格式”命令，打开“设置控件格式”对话框，切换到“控件”选项卡，选择“单元格链接”为A11单元格，如图7.3.3-5。单击“确定”按钮，就建立了选项按钮与A11单元格的联系。在复制出四个选项按钮，依次修改文字内容为技术部、质量部、业务部、管理部，在通过“格式”——“对齐”的方式使五个选项按钮水平等距摆布，如图7.3.3-6。

25

图7.3.3-5设置控件格式

图7.3.3-6控件命名设置

步骤四：定义名称。单击“公式”——“定义名称”，打开“新建名称”对话框，在“名称”中输入“定义名称法!x”其中“!”前面为工作表名，在“范围”中选择“定义名称法”，在“引用位置”中输入“=OFFSET(定义名称法!$A$3,定义名称法!$A$11,1,1,5)”，单击“确定”，如图7.3.3-7。

图7.3.3-7新建名称

步骤五：选取B3:F4单元格区域，利用新建图表的方式，插入“折线图”。右键单击折线图区域，单击“选择数据源”，在“选择数据源对话框”中，单击“切换行/列”，再单击“系列”中的“编辑”，如图7.3.3-8。在“编辑数据系列”对话框的“系列值”中输入“=定义名称法!x”，单击“确定”，如图7.3.3-9。选择不同的选项按钮，可以得到不同的动态图表，效果如图7.3.3-10。

图7.3.3-8选择数据源图7.3.3-9编辑数据系列

26

图7.3.3-10员工离职对图表

7.3.4数据透视图 数据透视图可以通过改变报表的布局或选择不同的字段来实现动态图表。在创建数据透视图的时候，也要同时创建数据透视表，以便为创建的图表提供源数据。 以公司员工年销售额记录表为例，做动态数据透视图。数据如图7.3.4-1。

图7.3.4-1员工年销售额记录表

步骤一：在工具栏中选取“插入”，在菜单栏中选择“数据透视图”下拉列表中的“数据透视图和数据透视表”，在弹出的“创建数据透视表”的对话框中，表区域输入“A3:D16”，单击“确定”，如图7.3.4-2。

图7.3.4-2创建数据透视表 步骤二：在“数据透视表工具”中选取“分析”，单击“选项”，弹出“数据透视表选项”，在“显示”中勾选“经典数据透视表布局”，单击“确定”，如图7.3.4-3。

27

图7.3.4-3数据透视表选项

步骤三：根据手动拖动自动选项到相应的位置，即可得到如图7.3.4-4所示的动态透视图表，选择透视表或者透视图中不同的字段，即可得到相关联的动态数据图表。

图7.3.4-4动态数据透视图

提示：步骤二中的设置是为了能够根据自己的意愿，设计出更好的透视图表，这里的设置只对当前的透视图表有用，当再次新建透视表的时候，需要再次的设置才能实现手动布局。

7.3.5使用VBA编程法 使用VBA编程法，可以制作更为灵活的动态图表，只要懂得编程，就可以制作出任何理想中的动态图。然而这也是没有接触过VBA编程学习者的难点。 本例我们将介绍如何通过VBA，将选中单元格所在的行或列区域作为图表系列的源数据，进而实现图表的动态更新。

步骤一：选中图表中的数据区域，插入一个柱形图，并添加纵坐标轴标题，如图7.3.5-1。

28

图7.3.5-1销售柱形图

步骤二：右击工作表名称，在关联菜单中点击“查看代码”，打开VBA代码编辑器，在代码窗口输入如下代码：

Private Sub Worksheet_SelectionChange(ByVal Target As Range) '如果在工作表中选择单元格，运行本程序 Dim i, t '定义两个变量 If ActiveCell.Row = 2 And ActiveCell.Column>= 2 And ActiveCell.Column<= 4 Then '如果选中的单元格在第1二行，且在2到4列之间 i = ActiveCell.Column '设所选单元格的列数为i t = ActiveCell.Text '设所选单元格的文字为t ActiveSheet.ChartObjects(1).Select '选中图表 ActiveChart.SeriesCollection(1).XValues = \图表!R3C1:R14C1\ '设置图表的分类（X）轴引用到A3:A14单元格区域 ActiveChart.SeriesCollection(1).Values = \图表!R3C\ '设置图表的数据系列1引用第i列的3:14行区域 ActiveChart.ChartTitle.Characters.Text = t '设置图表的标题引用活动单元格文字 End If '结束if语句 If ActiveCell.Column = 1 And ActiveCell.Row>= 3 And ActiveCell.Row<= 15 Then '如果选中的单元格在第1列，且在3到15行之间 i = ActiveCell.Row '设所选单元格的行数为i t = ActiveCell.Text '设所选单元格的文字为t 29

ActiveSheet.ChartObjects(1).Select '选中图表 ActiveChart.SeriesCollection(1).XValues = \图表!R2C2:R2C4\ ''设置图表的分类（X）轴引用到B2:D2单元格区域 ActiveChart.SeriesCollection(1).Values = \图表!R\ '设置图表的数据系列1引用第i列的2:4列区域 ActiveChart.ChartTitle.Characters.Text = t '设置图表的标题引用活动单元格文字 End If '结束if语句 End Sub '结束程序 步骤三：按住Shift+F7组合键返回工作表，选择D2单元格，图表中的数据系列自动变为第4列的“2013年”数据，选取A10单元格，图表中的数据系列自动变为第10行的“八月”数据，如图7.3.5-2所示。

图7.3.5-2动态销售图

7.4 实用技巧

7.4.1移动图表 在创建图表后，可能会需要移动图表的位置。在Excel2013中分为在工作表内移动图表和在工作表间移动图表两种情况，下面对两种情况进行介绍。 （1）工作表内移动图表

选取要移动的图表区域，将鼠标指针放到图表边框上，当鼠标指针变为“*”时，拖动图表即可在工作表内移动图表的位置，如图7.4.1-1。 （2）工作表间移动图表 选择整个图表右键单击，在关联菜单中选择“移动图表”命令，打开“移动图表”对话框，在对话框中选择“对象位于”单选按钮，在其后的下拉列表中选择目标工作表，然后“确定”，即可移动到指定的工作表中，如图7.4.1-2。

30

图7.4.1-1移动数据表图7.4.1-2移动图表

7.4.2设置图表的大小 在工作表中插入体表之后，往往需要对图表的大小进行调整。在Excel2013中，调整图表的大小主要有三种，下面分别进行介绍。 （1）选择图表，将鼠标指针放到图表边框的尺寸控制柄上，拖动控制柄即可调整图表到理想的大小，如图7.4.2-1。

图7.4.2-1手动设置图表大小

（2）选择图表，在“图表工具”的“格式”选项卡的“大小”组中的“形状高度“和“形状宽度”微调框中输入可以精确设置图表的大小，如图7.4.2-2。

图7.4.2-2精确设置图表大小

（3）在图表区域中鼠标双击，打开“设置图表区格式”窗口，单击“大小属性”按钮，此时在“高度”和“宽度”微调框中输入数值，可以精确设置图表的大小，在缩放高度和缩放宽度微调框中输入百分比值，可以对图表进行缩放操作，如图7.4.2-3。

31

图7.4.2-3设置图表格式

7.4.3向图表中快速添加数据系列 按照之前的讲解，如果要在原有的图表中添加新的数据系列，则需要多次打开“编辑数据系列”对话框，逐个进行添加。实际上，Excel2013中有更为便捷的方法，下面简单的介绍一下该方法的步骤。 步骤一：工作表中，选择要添加的数据区域，按住“Ctrl+C”组合键复制选择区域的数据，如图7.4.3-1。 步骤二：单击图表区，按住“Ctrl+V”组合键粘贴复制的数据，此时图表中自动添加新的数据系列，如图7.4.3-2。

图7.4.3-1复制数据区图7.4.3-2粘贴图表区

提示：在图表中单击某个数据系列，按住Delete键可以将其从图表中删除。在工作表中选择某个数据系列所在的单元格区域，按住Delete键则可以在删除数据的同时将数据系列从图表中删除。 7.4.4制作静态图表 Excel创建的图表将会随着数据源数据的变化而发生改变，如果需要图表为不可变得静态图表，除了可以将创建好的图表复制为图表外，还可以使用以下的简便方法进行设置。 步骤一：在图表中选择某个数据系列，可以看到编辑栏中的公式“=SERIES(……)”，该公式指明了数据系列与工作表中相关联的数据区域，工作表中对应的数据也被框出来，如图7.4.4-1所示。

32

图7.4.4-1图表编辑栏

步骤二：将插入点光标定位到编辑栏中，按F9键将公式转换为数组，按Enter键完成设置，此时数据源中对数据的框选也消失，如图7.4.4-2所示。此时，修改表中的数据，则发现图表不会发生任何的改变。这样就获得了静态图表。

图7.4.4-2静态图表

7.4.5多工作表作图 为了增加图表制作的灵活性，有些时候，我们需要制作的图表，所用到的数据并不能完全来源于一个工作表，也可能来自不同的工作簿，往往同一个类型的数据资料存放在一个工作表中，下面我们就来看看在不同的工作表中创建图表。

步骤一：选择表中A2:G3数据区域，插入三维柱形图，如图7.4.5-1。

图7.4.5-1销售柱形图

步骤二：右击图表，在关联菜单中选择“选择数据”，打开“选择数据源”对话框，在系列中点击“添加”在打开的编辑数据系列中，“系列名称”输入“=Sheet2!$A$3”，“系列值”输入“=Sheet2!$B$3:$G$3”，单击“确定”，再确定“选择数据源”对话框中的“确定”，如图7.4.5-2。

33

图7.4.5-2编辑数据系列

步骤三：打开“图表元素”系列框，勾选“图例”，完成图表制作，如图

图7.4.5-3多表柱形图

提示：在不同的工作簿中创建图表的方法也是一样的操作步骤。

7.4.6折线图中空白单元格的处理 如果数据源中某些单元格数据缺失，也就存在着空白单元格，在创建图表是，Excel默认会跳过这些空白单元格。实际上，对于这种情况，Excel提供了其他的处理方式，下面以折线图为例。

步骤一：以存在空白单元格的数据表创建折线图，图表中存在“断点”的折线图，如图

图7.4.6-1断点折线图

步骤二：打开“选择数据源”对话框，在对话框中单击“隐藏的单元格和空单元格”按钮，如图7.4.6-2，在弹出的“隐藏和空单元格设置”中，选择“用直线连接数据点”单选按钮，如图7.4.6-3，此时Excel将会以直线连接缺失点前后的两个数据点形成完整的折线图（如果不存在前点或者后点，则此点处数据仍然为空），如图7.4.6-4。

34

图7.4.6-2选择数据源图7.4.6-3隐藏和空单元格设置

图7.4.6-4空距折线图

步骤三：如果在“隐藏和空单元格设置”中选择“零值”，此时Excel会把空单元格的值当做零值来处理，如图7.4.6-5。

图7.4.6-5断点折线图

7.4.7数字条件控制图表

通过在自定义数字格式中设置条件格式的方法，可以使图表按给定的条件将坐标轴刻度标签中的数字设置不同的格式。

步骤一：选择数据区域，插入折线图，如图7.4.7-1。

35

图7.4.7-1销售折线图

步骤二：选择“设置坐标轴格式”，在“坐标轴选项”中选择“数字”，选择“自定义类型”，在“格式代码”中输入如下代码“[蓝色][>10000]#;[红色]#”，如图7.4.7-2。即可得到对坐标轴的刻度设置不同的颜色，如图7.4.7-3。

图7.4.7-2设置坐标轴格式

图7.4.7-3销售折线图

7.4.8将坐标轴标题链接到单元格 在图表中，坐标轴标题可以标示出坐标轴的意义，但是在默认情况下，Excel图表是不显示坐标轴标题的，此时用户可以通过设置使其显示。对于显示的坐标轴标题，用户可以通过设置使其是某个单元格的内容，当单元格内容发生更改时，坐标轴标题也会跟着改变。此方法同样可以用来设置图表的标题。

步骤一：打开图表的“图表元素”关联列表，勾选“坐标轴标题”下的“主要横坐标轴”，如图7.4.8-1。

36

图7.4.8-1添加坐标轴标题

步骤二：选择坐标轴标题标签，在编辑栏里输入公式“=坐标轴标题连接单元格!$A$1”（！前的内容为工作表的名称），然后按Enter键确认公式，如图此时选择的坐标轴标题显示为指定单元格的内容。

图7.4.8-2坐标轴标题链接单元格

7.4.9制作三维立体图表 图表中的三维图表具有比二维图表更加美观，反映数据间关系更加形象的特点。在三维图表中，图表区域数据系列后的区域为背景墙，图表数据系列底部区域为基底。对三维图表的背景墙和基底进行合理的设置能够使图表更加美观，获得更好的立体感。下面以三维柱形图进行三维设置为例来具体介绍。

步骤一：选定数据区域创建三维柱形图。鼠标右击图表背景墙，选择关联菜单中的“设置背景墙格式”，打开“设置背景墙格式”窗格。在窗格中鼠标单击“填充线条”按钮，打开“填充”栏对背景墙应用渐变填充效果，如图7.4.9-1。

37

图7.4.9-1设置背景墙格式

步骤二：在图表中选择基底，在设置基底窗格中对基底应用预设渐变填充，如图7.4.9-2。

图7.4.9-2设置基底格式

步骤三：选择图表的绘图区，在“设置绘图区格式”窗格中鼠标单击“效果”按钮，打开“三维旋转”栏，首先取消对“直角坐标轴”复选框的勾选，然后设置绘图区绕X轴和Y轴的旋转角度以及透视角度，如图7.4.9-3。

图7.4.9-3设置绘图区格式

步骤四：在图表中依次选择数据系列，在“设置数据系列格式”窗格中鼠标单击“效果”按钮，打开“阴影”栏，对数据系列应用预设阴影效果（向下偏移），这样可以看到柱形图在灯光投射下的阴影效果，如图7.4.9-4。

38

图7.4.9-4设置投影图

7.4.10在柱形图中使用图片 柱形图是一种常用的图表类型，在默认情况下，柱形图中的数据系列都是用颜色进行修饰的。实际上，Excel允许对数据系列使用图片填充获得个性化的效果。下面将以实例来介绍其操作方法。

步骤一：选定数据区域，创建“柱形图”，如图7.4.10-1。

图7.4.10-1销售柱形图 步骤二：在图表中鼠标右击数据系列，在打开的关联菜单中选择“设置数据系列格式”命令，在打开的“设置数据系列格式”窗格中选择“填充”选项，选择“图片或纹理填充”单选按钮，单击文件按钮插入图片对话框选择需要使用的图片，如图7.4.10.-2。在单击插入按钮关闭插入图片对话框，图片将替换柱形图中的柱形图形，如图7.4.10-3。

图7.4.10-2插入图片

39

图7.4.10-3图片柱形图

步骤三：在“设置数据系列格式”窗格中选择“层叠”单选按钮，图片将以原始大小层叠显示，如图7.4.10-4。

图7.4.10-4设置数据系列格式

步骤四：在“设置数据系列格式”窗格中鼠标单击系列选项按钮，鼠标拖动分类间距滑块调整数据分类的间距，对间距的调整将影响到填充图形的显示效果，如图7.4.10-5。

图7.4.10-5图片柱形图间距设置

7.4.11制作计划进度表 在Excel中，使用条形图能够方便地制作计划进度表，直观地展现计划的持续天数和每天需要完成的计划任务。下面将以实例介绍具体的操作方法。

步骤一：在工作表中选择A2:B8单元格区域，在“插入”选项中的“图表”组中单击“条形

40

图”按钮，在打开的列表中选择“二维条形图”栏中的“堆积条形图”选项，如图7.4.11-1。

图7.4.11-1堆积条形图

步骤二：在工作表中选择“持续时间”数据单元格区域，按住“Ctrl+C”键复制该区域数据，在图表中选择“绘图区”后按住“Ctrl+V”键向图表中添加一个新的数据系列，如图7.4.11-2。

图7.4.11-2添加数据系列

步骤三：在图表中鼠标选中“开始时间”数据系列打开“设置数据系列格式”窗格，在窗格中将条形图的填充方式设置为“无填充”，此时该数据系列在图表中不可见，如图7.4.11-3，完成计划进度表。

图7.4.11-3计划进度图表

7.4.12绘制等高线图 等高线图可以用来制作地理、温度等表示高低变量和位置关系的图表。等高线可以用平面图表的不同色彩来表示，也可以用立体图来表示。下面以实例介绍具体制作方法。

41

步骤一：选择数据区域A2:14，插入“三维曲面图”，如图7.4.12-1。

图7.4.12-1三维曲面图

步骤二：选择“图表工具”的“设计”工具栏，点击“图表布局”组下“快速布局”里的“布局4”，使得刻度尺寸单位为“0.05”，如图7.4.12-2。

图7.4.12-2快速布局

步骤三：分别选择图表的“基底”和“背景墙”用适度的颜色色填充，即可得到直观的等高线图，如图7.4.12-3。

图7.4.12-3等高线图

42

7.5 实战练习

7.5.1股票交易的动态研究 股票交易公司的股票交易记录随着时间的推移，数据越来越多，对于一些股票的研究者来说，在一张股票图中，往往需要某些特点时间段的数据来做研究预测。下面，我们介绍利用滚动条和定义名称的方法实现动态的股票图，这样对于股票的研究者来说，就可以查看股票图中的任意区间的交易数据。 步骤一：单击“开发工具”下的“插入选项”框，选择“滚动条（窗体控件）”，如图7.5.1-1。在空白处画一个横向滚动条，右键单击滚动条，在弹出的关联菜单中选择“设置控件格式”命令，打开“设置控件格式”对话框，输入设置最小值为1，最大值为2000，步长为1，单元格链接为“$I$3”,如图7.5.1-2。在复制一个滚动条控件，并在“设置控件格式”对话框中设置单元格链接为”$I$”,单击“确定”，完成添加滚动条控件

图7.5.1-1插入控件

图7.5.1-2设置空距格式 步骤二：选取A1:F5单元格区域，单击“公式”菜单栏中定义名称组中的根据所选内容创建，弹出“以选定区域创建名称”，勾选“首行”，如图7.5.1-4，单击“确定”。

43

图7.5.1-3创建名称设置

步骤三：打开步骤二下的“名称管理器”，会发现已经自动指定了6个名称，如图7.5.1-4，逐个修改指定的名称如下：

? “时间”的引用位置为“=OFFSET(股票研究图!$A$1,股票研究图!$I$3,,股

票研究图!$I$5,1)”；

? “成交量”的引用位置为“=OFFSET(时间,,1)”； ? “开盘”的引用位置为“=OFFSET(时间,,2)”； ? “最高”的引用位置为“=OFFSET(时间,,3)”； ? “最低”的引用位置为“=OFFSET(时间,,4)”； ? “收盘”的引用位置为“=OFFSET(时间,,5)”。

图7.5.1-4名称管理器

步骤四：选取A1:F5单元格区域，插入股票图，子图表类型选择“成交量—开盘—盘高—盘底—收益图”。右键单击图表，在关联菜单中点击“选择数据”，打开“选择数据”对话框，在系列中分别设置5相应的系列值为刚才定义的名称，例如设置“最高”系列的值为“=股票研究图!最高”，以此类推，如图7.5.1-5。在“分类”中设置为“=股票研究图!时间”，如图7.5.1-6，单击“确定”。

图7.5.1-5编辑数据系列图7.5.1-6轴标签设置 步骤五：在图表的“图表元素”中勾选“坐标轴标题”，在横轴上输入“时间”，“主纵坐标轴”输入“成交量”，“次纵坐标轴”输入“指数”，单击滚动条，即可查看任何区间的股票交易情况趋势，如图7.5.1-7。

44

图7.5.1-7股票交易趋势图

7.5.2艾宾浩斯记忆曲线研究 德国著名的心理学家艾宾浩斯，在1885年发表了著名的遗忘规律研究报告，被称为艾宾浩斯记忆曲线。艾宾浩斯记忆曲线提示，在记忆的最初始阶段遗忘的速度很快，后来就逐渐减慢，到了相当长的时间后，几乎就不在遗忘。 下面以艾宾浩斯记忆曲线为例，做XY散点图，计算指定点的坐标值。

步骤一：选定A3:I4区域，插入XY散点图，子图表类型选择“带平滑线和数据标记的散点图”，添加坐标轴标题，水平表示“时间（天数）”，纵轴表示“记忆量”，如图7.5.2-1。

图7.5.2-1 XY散点图

步骤二：单击“开发工具”下的“插入选项”框，选择“数值调节钮（窗体控件）”，如图7.5.2-2，在C7单元格附近画一个微调按钮，右键单击微调项按钮，在弹出的关联菜单中选择“设置控件格式”命令，打开“设置控件格式”对话框，输入设置最小值为22，最大值为100，步长为1，单元格链接为“$C$7”,如图7.5.2-3，单击“确定”按钮，完成微调控件的添加。

图7.5.2-2添加表单控件

45

图7.5.2-3设置控件格式

步骤三：在B6:B13 单元格区域输入内插值的计算公式，公式如下，计算结果如图7.5.2-4。

?

? ? ? ? ? ? B7单元格公式：“=C7/100”；

B8单元格公式：“=MATCH(B7,B4:I4,-1)+1”； B10单元格公式：“=INDEX($A$3:$I$3,,$B$8)”； B11单元格公式：“=INDEX($A$3:$I$3,,$B$8+1)”； B12单元格公式：“=INDEX($A$4:$I$4,,$B$8)”； B13单元格公式：“=INDEX($A$4:$I$4,,$B$8+1)”； B6单元格公式：“=TREND(B10:B11,B12:B13,B7)”。

图7.5.2-4计算结果图

步骤四：选择图表右键单击，在关联菜单中点击“选择数据”，打开“选择数据源”对话框，在系列中单击“添加”按钮，打开“编辑数据系列”对话框，设置X值为“=艾宾浩斯记忆曲线!$B$6”，Y值为“=艾宾浩斯记忆曲线!$B$7”，如图7.5.2-5，单击“确定“按钮，在图表中添加了一个数据点，如图7.5.2-6。

图7.5.2-5编辑数据系列

46

图7.5.2-6曲线标记点

步骤五：选择刚刚自动添加的点，右键单击，在关联菜单中点击“添加数据标签”下的“添加数据标注”，如图7.5.2-7所示。完成曲线坐标值的动态显示，单击微调按钮，点沿着艾宾浩斯记忆曲线移动，并动态显示点的坐标值，如图7.5.2-8所示。

图7.5.2-7添加数据标注

图7.5.2-8艾宾浩斯记忆曲线

7.5.3本量利分析模型的创建

本量利分析是管理会计的主要内容，是企业在预测、决策、规划和控制工作中常用的也是最有用和最有效的方法之一。本节着重讨论基于Excel工作表的动态图表本量利分析模型的创建与应用。即应用Excel工作表的图表功能将本量利之间的数量关系以动态图表的形式展现出来，并在动态图表本量利分析模型中进行因素变动的假设分析，使各因素变动对结果（利润）的影响程度直观地、动态地再现出来。

例：假定江海电器有限公司生产一种新产品A，产品单位售价为98元，单位变动成本为60元，全年固定成本为80000元，企业正常的产品销售量为2000件。

如果上述案例中的A产品的销售数量可能在1000件到2600件之间变动（变化率为100件），那么在不同销售量水平下，该企业的利润总额各是多少？或者说如果A产品的销售单价可能

47

在95元到110元之间变动（变化率为1元），那么在不同单位售价条件下，其利润总额各是多少？

根据以上资料首先在Excel工作表中创建本量利分析模型。

步骤一：将案例中的相关资料输入Excel工作表，在Excel工作表中输入创建本量利分析基本模型的相关资料，如图7.5.3-1所示。

图7.5.3-1 基本数据模型

步骤二：计算销售总额、成本总额、利润总额和保本点。

用鼠标单击要输入公式的单元格C8然后输入公式“=C4*C7”，然后回车确定，在C8单元格就可以得到算的结果，本例计算出来的销售总额为196000件\。同理，我们可以通过在C9单元格输入公式“=C5+C4*C6”计算出成本总额，在C10单元格输入公式“=C8-C9”计算出利润总额，在C12单元格输

入公式“=C5/(C7-C6)”计算出保本点等指标，如图7.5.3-2。

图7.5.3-2保本点计算

步骤三：在B14单元格输入IF语句“=IF(C10>0,\利润总额\亏损额\”，在C14单元格输入“=IF(C10>0,ROUND(C10,0),-ROUND(C10,0))”，如图7.5.3-3。

图7.5.3-3盈亏额计算

其次根据以上数据模型来创建直观、形象便于操作和理解的动态本量利分析模型图表。 步骤一：在F4、G4和H14单元格中，分别引用C8、C9和C10单元格中的销售总额、成本总额、利润总额，然后在E5单元格输入0，在E6单元格输入1000，在E7单元格输入1200，然后选中两个单元格，将鼠标指向选定E6:E7两个单元格的右下角，待鼠标指针变成黑色“+”指针时，按下鼠标左键向下拖动至E14单元格，如图7.5.3-4。

48

图7.5.3-4 销售量设计

步骤二：利用Excel工作表中的模拟运算表的数据处理功能，一次计算出在不同销售量情况下的销售总额、成本总额、利润总额三项指标。其方法是选中E4:H14单元格，点击菜单栏的“数据”，选择“数据工具”中“模拟分析”下的“模拟运算表”，弹出“模拟运算表”对话框，点击“输入引用列的单元格”，然后点击C4单元格，如图7.5.3-5。按下“模拟运算表”对话框上的“确定”按钮，不同销售量情况下的销售总额、成本总额、利润总额就一次全部计算出来了，如图7.5.3-6。

图7.5.3-5模拟运算表图7.5.3-6模拟运算结果

步骤三：为了使动态图表本量利分析模型出效果，还需要在动态图表本量利分析模型做出保本点指示线和利润指示线两条辅助线。

在B16单元格引用C12单元格的保本点的数据，按下F4键将“C12”转换为“$C$12”,将鼠标指向B16单元格的右下角,待鼠标指针变成黑色“+”指针时!按下鼠标左键向下拖动至B18单元格。然后选中C17单元格，输入公式“=C7*C17”，计算出保本点的销售额。然后在C16单元格输入指示线的下标0，在C18单元格输入指示线的上标260000，这样绘制保本点指示线的数据资料就已经准备好了。

同理，在E16单元格引用C4单元格的销售量的数据，按下“F4”键将“C4”转换为“$C$4”，将鼠标指向E16单元格的右下角待鼠标指针变成黑色“+”指针时,按下鼠标左键向下拖动至E19单元格，然后选中F16单元格引用C16单元格地址，选中F17单元格引用C8单元格地址，选中F18单元格引用C9单元格地址，选中F19单元格引用C18单元格地址，这样绘制利润指示线的数据资料就已经准备好了，如图7.5.3-7。

图7.5.3-7辅助线设计

49

步骤四：绘制动态图表本量利分析图表。

在工作表中插入“带平滑线和数据标记的散点图”，右击图表区，在关联菜单中“选择选择数据”，打开“选择选择数据源”对话框，在“图表数据区域”文本框中选择区域E5:H14，这样在系列中就有三个系列值，分别为“系列1”、“系列2”、“系列2”，编辑更改相对应的名称为“成本总额”、“销售总额”、“利润中总额”。 点击系列中的“添加”，弹出的“编辑数据”对话框，在“系列名称”中输入“保本点指示线”，在“X轴系列值”中选择区域“B16:B18”，在“Y轴系列值”中选择区域“C16:C18”，单击“确定”，同理添加“利润指示线系列”，在“X轴系列值”中选择区域“E16:E19”，在“Y轴系列值”中选择区域“F16:F19”，单击“确定”，如图7.5.3-8。再单击“选择选择数据源”对话框中的“确定”。回到图表，添加图表元素中的“坐标轴标题”和“图例”，如图7.5.3-9。

图7.5.3-8添加系列值

图7.5.3-9本量利分析散点图 最后添加窗体控件完善动态图表本量利分析模型。 步骤一：在J24:J26单元格中分别输入“销售额”、“利润额”、“单价”，将K24单元格链接到“=$C$4”，K25单元格链接到“=$C$10”，K26单元格链接到“=$C$7”。

步骤二：单击“开发工具”下的“插入选项”框，选择“数值调节钮（窗体控件）”，在L24单元格附近画一个微调按钮，右键单击微调项按钮，在弹出的关联菜单中选择“设置控件格式”命令，打开“设置控件格式”对话框，输入设置最小值为1000，最大值为2600，步长为100，单元格链接为“$C$4”，单击“确定”按钮，完成微调控件的添加。

同理在L26单元格附近再绘制一个微调按钮，设置最小值为90，最大值为110，步长为1，单元格链接为“$C$7”，单击“确定”按钮，完成微调控件的添加，如图7.5.3-10。

50

本文来源：https://www.bwwdw.com/article/w9no.html