用EXCEL做质量分析柱状图模板.docx

用EXCEL做质量分析柱状图模板
关键词：EXCEL分析柱状图

EXCEL具有强大的数据处理功能，但是，在进行分析时，面对大量枯燥的数据，往往很难以让人提起兴趣。
学校教务管理中，常常要进行质量分析。一个规模较大的学校的一个年级的质量分析，往往含有上千个数据，分析时大家会听得或看得索然寡味，这时EXCEL图表就能发挥作用。EXCEL图表能给人视觉冲击，让数据易于理解和记住，让分析者与被分析者之间易于交流。
一、原始数据
图一是我校的七年级质量分析总分分析表。
图二是七年级质量分析语文分析表。
图一、图二各占17行，七个单科（语文、数学、英语、生物、思品、历史、地理）的分析表与图一、图二内容相似。
图一、图二及七个单科分析表在同一工作表。
要分析的数据为均分、合格率（及格率）、优质率、后20名均分。

图一

图二
二、数据整理
1、插入工作表，名称为分析图表（图三）。

图三
2、做一个控制按钮
调出窗体工具箱（在菜单栏右边空白处，右键，勾选窗体）。
单击窗体工具箱的微调项按钮——在工作表中画出微调项按钮——放置在B2单元格——右键单击微调项按钮——单击设置控件格式——单击控制，然后设置控件格式（图四），最小值为0，步长为17，最大值可根据科目多少设置，单元格链接为B3（后面的公式将与B3单元格联系），单击确定。

图四
操作控制按钮（微调项按钮），B3单元格的值会以在0——119的范围内以17的倍数变化。
2、转移数据
现在，我们用OFFSET函数将质量分析表的数据转移到分析图表工作表。
OFFSET是一个引用函数，是根据参照区域，通过上下左右偏移得到新的区域的引用。返回的引用可以是一个单元格也可以是一个区域。
这里以返回单元格为例。
在某单元格输入：=OFFSET(A1,2,3)，返回D3单元格的值（以A1单元格作为参照，向下移动2行，向右移动3列）。
①在图三的A4单元格输入：=OFFSET(数据!A4,B3,8)
B4单元格输入：=$A$4&OFFSET(数据!$A$4,1+$B$3,2)
C4单元格输入：=$A$4&OFFSET(数据!$A$4,1+$B$3,6)
D4单元格输入：=$A$4&OFFSET(数据!$A$4,1+$B$3,10)
E4单元格输入：=$A$4&OFFSET(数据!$A$4,1+$B$3,13)
如果B3=0，得到图五的结果。

图五
如果B3=17，得到图六的结果。从图五、图六可以看出，B3发生变化，分析的科目发生变化。

图六
②在B5单元格输入：=OFFSET(数据!$A5,1+$B$3,2)，公式向下复制。
C5单元格输入：=OFFSET(数据!$A5,1+$B$3,6，公式向下复制。
D5单元格输入：=OFFSET(数据!$A5,1+$B$3,10)，公式向下复制。
E5单元格输入：=OFFSET(数据!$A5,1+$B$3,13)，公式向下复制。
如果B3=0，得到图七的结果。

图七
3、添加辅助列
均分、合格率（及格率）、优质率、后20名均分都有平均值，每一个数据都与其平均值进行比较，图表才会更直观。
在合格率（及格率）、优质率、后20名均分所在列的前面分别插入行（图八）。

图八
在图八的C4单元格输入：=B4&"平均值"，复制C4单元格粘帖到E4、G4和I4单元格（图九）。

图九
在C5单元格输入：=OFFSET(数据!$A$19,1+$B$3,2)，公式向下复制。
在E5单元格输入：=OFFSET(数据!$A$19,1+$B$3,6)，公式向下复制。
在G5单元格输入：=OFFSET(数据!$A$19,1+$B$3,10)，公式向下复制。
在I5单元格输入：=OFFSET(数据!$A$19,1+$B$3,13)，公式向下复制（图十）。

图十
三、制作EXCEL图表
1、选中B4：C18，单击工具栏图表向导（或菜单插入——图表）（图十一）。

图十一
单击标准类型，选择柱形图，单击完成，出现图十二的图表。图十二的柱状图有二组图：总分均分和总分均分平均值。

图十二
2、把图例作为标题
右键单击图十二右边的图例，单击图例格式（图十三）。

图十三
单击字体，可以调整字体、字形、字号等，字号调整为16号。
单击位置，选择靠上，单击确定（图十四）。

图十四
3、改平均值柱状图为折线图
右键单击平均值柱状图，单击图表类型（图十五）。

图十五
单击标准类型，选择折线图，单击完成（图十六）。

图十六

4、调整柱状宽度及其它设置
右键单击图十六的柱状，单击数据序列格式（图十七）。

图十七
单击选项，重叠比例和分类间距均设为0。
单击数据标志，勾选“值”（图十八）。

图十八
右键单击柱状上面的数值，单击数据标志格式（图十九）

图十九
单击对齐，在标签位置后选择数据标记