如何用excel制作图表(4)

第三十一节：利用误差线标识折线图的平均线

如下图所示的图表，是利用误差线为折线图标识的平均线。

单击A2:B13数据源，插入折线图。
单击数据系列→“布局”选项卡→“分析”组→“误差线”→“其他误差线选项”→单击“误差量”选项组中的“标准偏差”单选选项→设置标准偏差为0→单击“线条颜色”选项→“实线”→设置线条颜色为红色→单击“线型”选项→设置线型宽度为3磅→关闭“设置误差线格式”对话框。
最后格式化图表。

第三十二节：直方图和正态分布图制作

第一部分：直方图和正态分布图解释
　　什么是Excel直方图？所谓Excel直方图，就是将某期间所收集的计量值数据经分组整理成次数统计表，并使用柱形予以图形化，以掌握这些数据的分布情况。
　　直方图的运用：比如考试成绩的分布，收入支出的分布，加工尺寸的分布等等。
　　直方图是反映分组数据频数的柱形图。正态分布图是在直方图的基础上绘制的，是一条单峰、对称呈钟形的曲线。
　　直方图表现的是频数，正态分布图表现的是频率。

第二部分：直方图和正态分布图制作相关的函数
　　作图之前，需要先统计数据的最大、小值，区间，平均值，标准偏差，直方图柱数，直方图组距。
　　设计到的函数有：STDEV、NORMDIST、FREQUENCY等，使用很简单，详见附件的实例。
　　正态分布函数的语法是NORMDIST(x，mean，standard_dev，cumulative)
　　cumulative为一逻辑值，如果为0则是密度函数，如果为1则是累积分布函数。如果画正态分布图，则为0。
　　STDEV：估算样本的标准偏差。标准偏差反映相对于平均值 (mean) 的离散程度。
　　语法：STDEV(number1,number2,...)
　　Number1,number2,... 为对应于总体样本的 1 到 30 个参数。也可以不使用这种用逗号分隔参数的形式，而用单个数组或对数组的引用。

第三部分：绘制直方图和正态分布图

　　选择F9：F15，插入——图表——柱形图。
　　将水平坐标轴标签改为：分组数据（E10：E15）。
　　选择G9：G15，复制，单击图表，粘贴，插入系列。将此系列改为折线图，单击数据系列，设置数据系列格式，系列选项——次坐标轴。
　　剩下就是格式化图表，常规的操作方法，结合以前的知识点完成。

第三十三节：利用XY散点图绘制圆形

第一部分：构造数据
　　在A1输入X，B1输入Y，然后在A2输入公式：=COS(PI()*(ROW(1:1)-1)/12)，下拉至A26单元格。在B2输入公式：=SIN(PI()*(ROW(1:1)-1)/12)，下拉至B26单元格。选择A2：B26，设置单元格数据保留两位小数。

第二部分：绘制散点图

　　选择A2：B26数据，插入——图表——散点图——带平滑线的散点图。
　　删除图例、网格线、坐标轴，设置图表区为9厘米乘以9厘米的正方形、边框为无线条，设置绘图区和图表区为无填充，完成圆的绘制。

第三十四节：利用散点图绘制三角形

第一部分：构造数据
在B5单元格输入公式=B2，在C5单元格输入公式=C2，作为绘制三角形的辅助数据。

第二部分：利用散点图绘制三角形

选择B2:C5单元格区域→单击“插入”选项卡→“图表”组→“散点图”→“带直线的散点图”。
删除图例、网格线，将绘图区、图表区填充、线型均为无。将数据系列颜色设置为绿色。

 

第三十五节：柱形图两根柱子重叠效果制作

柱形图两跟柱子重叠效果最主要用到主次坐标轴和添加辅助空白行来绘制。如下图所示，１月和２月销量两根柱子重叠在一起，年度预算单独一根柱子。

单击B1：D3单元格区域，插入——图表——二维柱形图
单击B4：D5单元格区域，复制，单击图表，粘贴系列。
单击系列1，设置数据系列格式，分类间距：150%，系列绘制在“次坐标轴”
单击系列2，设置数据系列格式，分类间距：150%，系列绘制在“次坐标轴”。
删除次坐标轴，更改图例名称：方法是单击图表，右键，选择数据，找到相应的系列，单击“编辑”，比如单击系列1，编辑，弹出的编辑数据系列，在系列名称输入=Sheet1!$A$2或选取A2单元格，就将A2单元格的内容变为系列1图例的内容。
最后格式化图表


第三十六节：如何构造制作专业的图表

　　通过前面35个小节的图表学习，一个图表无非就是数据系列类型、图例、标题、绘图区、坐标轴、网格线、数据标签等等这些元素构成。

　　叨叨自己做图表的一点感受，做一个图表，无非就是利用工具将这些元素拼凑到一起，但怎么拼凑，美观、简洁，让人一看就懂，就是咱们作图之前应该思考的，比如综合利用图表+单元格、矩形框、文本框、线条、箭头、辅助序列等非图表元素。做法并不唯一，视各人习惯，方便顺手为原则。

　　专业商务图表的特点：1、专业的外观；2、简洁的类型；3、明确的观点；4、完美的细节。

　　制作一个专业的图表，可以从颜色、布局、字体三方面来突破Excel的默认设置，让自己做出图表更加新颖独特，专业，简洁大方。

第一：突破EXCEL的默认颜色
　　页面布局——主题选项卡——颜色、主题、字体、效果，可以根据需要新建主题颜色。

附：ColorPix取色器
第二：突破EXCEL的默认布局

第三：突破EXCEL的默认字体
　　字体可以分为有衬线和无衬线两种。
　　有衬线字体：宋体为代表，笔划起始处带有装饰。
　　无衬线字体：黑体为代表，笔划起始处直来直去，无装饰。
　　专业商业报表一般都选择无衬线字体。

第三十七节：柱形图的图例颜色方块变宽

　　如下面所示，通过两图对比，可以发现右边的图例颜色方块变宽。

　　思路：通过添加一白色线条，数值为0的折线图实现。
　　操作步骤如下：
　　绘制完基础图表之后，单击图表——右键——选择数据，插入一个空白系列，如下图所示：

　　然后将系列改为折线图，将系列颜色填充为白色，即可以实现右边的效果。


第三十八节：隔行填色的网格线背景制作

　　隔行填色的网格线背景，原理：隔行填色的效果来源于0和1辅助数据制作的条形图。如下图所示：

操作步骤如下：
　　1、单击D2：D4数据源，插入条形图。
　　2、设置横坐标轴，刻度最小0、最大1。
　　3、X、Y轴格式设置中均勾选交叉于最大值，此时坐标轴均切换到次要位置。
　　技巧：目的是为了将常规坐标轴位置给真正的数据序列。
　　4、选择数据系列，设置数据系列格式，分类间距为0。
　　5、格式化图表：隔行填色样式：图表区、绘图区使用一种填充色，条形图使用另一种填充色。
　　6、单击图表，右击，选择数据，添加新的数据系列，将B1：C4数据添加进来。
　　注：因坐标刻度原因，暂时不可见。设置序列图表类型为柱形图或折线图。
　　7、柱形图出现，以常规位置为坐标轴。然后完成整个图表辅助数据的制作。


第三十九节：使用offset函数和列表框控件制作动态图
 

第一，构造辅助数据：
　　在B9单元格输入：1
　　A12单元格输入公式：=OFFSET(A1,$B$9,)，公式的意思就是以A1为基准，向下偏移B9单元格数字的行数。
　　右拉，复制公式，直到M12。
第二：制作图表
　　选择A12：M12单元格，插入——图表——面积图。然后再次选择A12：M12，添加一个数据系列，系列图表类型更改为折线图。
　　格式化图表
第三：制作列表框控件
　　单击“开发工具”——插入——表单控件——列表框（窗体控件），然后右键单击插入的控件，设置控件格式，做如下图所示的设置。

这样就将数据源，图表，控件结合起来，从而制作出动态图。



第四十节：不等分格子背景的折线图

　　在38节制作了一个隔行填色的网格线背景，这个小节再变通一下，将网格线背景，不等分填充。比如：2,6,2的格子。
　　目的：这样做背景格子的好处是可以通过修改辅助数据方便的增加分段范围查看数据趋势变化，颜色等设置。

操作步骤：
　　单击A2：E9区域，插入——图表——柱形图——二维堆积柱形图。
　　然后将销售量系列图表类型更改为带数据标记的折线图。
　　接下来，将辅助数据系列，右击“数据序列格式”，在”选项”标签中将”分类间距”设为0。
　　右击坐标轴，在“坐标轴格式”中”刻度”标签内的最大值设为“100”，然后删除图例。