Excel在统计方面的应用技巧

唐义勇 永州市统计局

在实际的数据处理中，往往涉及到数据的统计，数据统计时常常对应线性回归与相关性分析，当前人们也更倾向于使用相应的工具软件或编程系统进行数据的大规模高效率统计，既追求数据统计的速度，也追求统计的质量。但无论是使用工具软件还是编程进行数据统计，不仅操作繁琐，且对操作人员技能要求较高，应用效果也不甚理想。而Excel作为一款优秀的数据分析软件，支持数据的统计分析与处理，因此，总结其在数据统计分析方面的应用技巧非常必要。

一、Excel的常用统计函数介绍

AVERAGE工作表函数是返回参数的算术平均值，对应的语法格式为AV-ERAGE(n1，n2，…，n30)；CORREL工作表函数，其对应的是返回两个数值单元格区域之间的相关关系，对应的语法格式为CORREL(array1，array2)，其中Array1和Array2参数分别表示第一和第二单元格区域；COUNTIF工作表函数，对应的是给定区域内符合条件的单元格的数目，对应的语法格式为COUNTIF(range，criteria)，Range参数代表单元格区域[1]，而Criteria是给定条件，工作表函数主要是使用最小二乘法进行最佳直线拟合，返回直线数组的描述，也有一定的语法格式为LINEST(y's，x's，const，stats)，对应的参数是y's和x's。MAX、MIN工作表函数，分别对应数据集中的最大值、最小值，对应的语法格式为MAX(n1，n2，…，n30)和MIN(n1，n2，…，n30)，RANK工作表函数反映了数值在一组数值中的排名，对应的语法格式为RANK(number，ref，order)。STDEV、STDEVP工作表函数，前者对应的是样本的标准偏差，而后者对应的是样本总体标准偏差，对应的语法格式为STDEV(n1，n2，…)和STDEVP(n1，n2，…)，Number1，Number2，…为1到30个样本值[2]。Excel作为友好的统计软件，操作界面友好，操作简单，比较容易上手，其数据处理功能十分强大，即使面对复杂的数据多重检索标准，也能进行数据的引用应用，并进行数据的高效高质处理。

二、Excel进行统计分析的技巧说明

(一)用Excel整理统计数据

使用Excel统计分组，一种对应是FREQUENCY函数，另一种是利用数据分析中的“直方图”工具，用函数编制频数表，先明确每组的上限值，这是编制频数表的关键一步，明确了上限值就相当于明确了组的组距和组线[3]，如果将上限值对应输入为900/1000/1100/1300/1500，如果将上线值输入到A7：A11，选取结果存放的单元格区域B7：B11，于编辑栏输入公式=frequency(A1:J5，A7:A11)就可获得各组相应的频数。采用数据分析工具进行频数分布表的制作，需要于工具菜单中选择数据分析选项，找到对话框中的直方图工具，打开对话框输入，在接收区域输入$A$1:$J$5，接收区域是分组标志所在的区域。选择输入项，可以自主选择输出区域，可以是新工作表或工作簿或者图表输出，从而得到直方图。选择累计百分率，则直方图上伴有累计频率折线，若选择“柏拉图”，可以获得按降序排列的直方图，按确定按钮能得到“草图”，我们可以在“草图”上作进一步的修改[4]，如用鼠标单击任意直线，右键单击在弹出的快捷菜单中选数据格式，根据对话框的内容选择相对应的标签，进行直方图颜色的修改、标题的清除。

(二)用Excel计算描述统计量

众数、中位数、算术平均数、调和平均数、标准差等都属于描述统计量，使用Excel进行这些统计量的统计时分组的资料用函数计算，未分组的材料用公式计算。以某城市居民家庭收入表为例，见表1，求众数[5]。先创建Excel文件，在B11中输入数据，输入公式=20000+((260-240)/((260-240)+(260-140)))×5000计算即可得出结果。在计算中位数时则利用上限公式，于B11单元格中输入公式=20000-(500-488)/240×5000即可得出结论。用数据分析工具描述统计量，先要把数据输入到单元格中，于工具菜单栏中选择数据分析这一选项，于对话框中选择描述统计，打开统计对话框于需要输入的区域中输入，选择汇总统计就可获得统计量。

表1 某城市居民家庭收入表

(三)用Excel进行区间估计

使用Excel进行区间估计，主要是根据区间估计的要求逐步进行，先计算样本均值，也就是点估计值，接着计算样本标差，结合样本量构造抽样误差，再结合置信度构造极限误差，样本均值加上减去极限误差能得到区间估计上下限。

(四)用Excel进行指数分析

首先是计算总指数，这是研究经济现象数量变动情况的常用统计分析方法。指数分析有综合指数分析和平均指数分析的区别，以某企业三种产品的生产情况为例，见表2，以基期价格p0作为同度量因素来进行生产指数的计算。先进行p0q0的计算，具体是在G2区域中输入并复制公式到区域G3：G4计算，然后计算各个p0q1，在H2中输入：“=E2*D2”，并用鼠标拖拽将公式复制到H3：H4区域。接下来计算∑p0q0 和∑p0q1，选定G2：G4区域，单击工具栏上的“∑”按钮，在H5出现该列的求和值。最后计算生产量综合指数Iq=p0q1/p0q0，在C6中输入“=H5/G5”便可得到生产量综合指数。

表2 某企业三种产品的生产情况表

三、以学生成绩为例进行Excel使用技巧的说明

(一)VLOOKUP函数查找对应班级成绩

在教学中，教师需要统计自己所教班级学生的成绩，并将成绩与全院所有班级做对比，明确自身与其他院系教师、学生的差异。例如，在某场模拟考试成绩之后，某教师收到了该年级1到8班全体学生模考成绩总表，数据表结构和记录如下页表3所示。学生模考成绩表可以显示学生学号、对应分值、总成绩，但没有班级，无法直观看出学生的成绩变化，可以将四个工作表的模考数据整合在一个汇总表中，于汇总表中，得出教师所在的班级1班所有学生每次考试对应的成绩。具体操作是找到工作表共同的字段“学号”，使用Excel表中的VLOOKUP函数在E3单元格中输入公式：=VLOOKUP(A3，’模考1’!$B$2：$P$277，15，FALSE)，计算出第一位学生模考一的成绩，逐渐下拉填充，计算出其他所有学生模考一的成绩。同样完成学生其余三次模考的成绩，使用平均函数进行1班和所有班级学院学生四次模考的平均成绩的计算，进行数据的对比，从而探明教师的教学水平。

表3 年级1到8班全体学生模考成绩总表截取

(二)直方图显示学生成绩

表4 年级1班全体学生模考四成绩直方图

通过直方图可以准确了解一组数据的分布特征，了解数据是否存在对称分布的属性，查明数据异常，从而评估学生整体学习情况。例如，基于汇总表可以了解1班所有学生模考四的成绩，教师创建直方图，进行直方图分析，明确该班此次考试成绩整体情况。具体操作是选取模考四成绩列区域，绘制图表类型中的直方图，并设置好图表标题、坐标轴标题、图例、箱宽度等，将箱宽度设置为5，由此看到1班模考四的成绩符合正态分布，且主要集中在34到44分之间，而及格率按45分及以上来推算的话，仅有7人合格，因此教师接下来的教学重点是重点关注34到44这部分分数段的学生。

(三)预测成绩趋势变化

使用Excel进行数据的统计分析，也可以用于预测学生的成绩变化趋势，可以根据学生历史考试成绩创建预测，去推断学生下几次考试的通过情况、波动幅度，以指导教师做好教学调整，具体来说，可以选取“日期”和“通过率”两列数据来创建预测工作表，生成预测数据和图表，其不仅有历史数据，且有预测数据，还伴有预测图。教师可以一目了然地了解到学生接下来考试的通过率走势，并有上限和下限范围的标注。一般来说，历史数据越多，预测趋势结果就越准确。

四、结语

本文基于实际案例，具体介绍了Excel在社会学统计分析、学生成绩分析等方面的应用优势，也论述了其具体的应用思路和应用技巧，对于利用Excel工具进行数据统计分析与应用具有指导意义。Excel版本较多，Excel在实际的应用中应结合具体情况进行数据的统计与处理，使其强大的统计功能的发挥，以带来数据统计的高效与高质。■