利用EXCEL进行数据统计若干技巧

利用Excel 进行数据统计的若干技巧一、认识常用的统计函数：1、SUM ：计算单元格区域内所有数据的和。

表达式为：=SUM （D2：2、A VERAGE：计算单元格区域内所有数据的算术平均数。

表达式为：=A VERAGE（D2：F50）3、COUNT 表达式为：=COUNT （D2：F50）4、COUNTIF 表达式为：=COUNTIF （D2：F50，5、MAX ：返回一组数值中的最大值。

表达式为：=MAX （D2：F50）6、MIN ：返回一组数值中的最小值。

表达式为：=MIN （D2：7、MIDB8、LARGE ：返回数据中第K 个最大值。

表达式为：=LARGE(F2:F34,9、SMAL ：返回数据中第K 个最小值。

表达式为10、RANK 表达式为：利用好统计公式中的定位符$ 例：RANK(I2,$I$2:$I$180)11、EXACT ：比较两个字符串是否完全相同（区分大小写），如果完全相同，返回TREU ，否则返回FALSE 。

表达式为：=EXACT(D2,J2)12、V ALUE：将代表数值的文本字符串转换成数值。

表达式为：=V ALUE(F2)13注意：表达式中输入的数据、符号必须在英文状态下输入。

二、简单数据的简便统计：（一）数据整理。

1、按单位整体集中数据（班为单位），统一编序号，删除与统计无关项目。

把学校、班级列调整到靠近数据区。

（操作方法：剪切要移动列→选中要移入的目标列→点击“【插入已剪切的单元格】”）2、按升序或降序方式逐项清理各科数据，处理不合理数据（文本数据，超满分数据）。

注意：利用升降序工具进行操作时，数据必须至少有一方与数据区域相连。

而按菜单【数据】→【排序】操作则可避免此问题。

3、划分分数线。

按升序或降序方式：例：按A段人数（3500*40%=1400），选中语文科第1400行数据，将语文科降序排列，选中单元格即语文科A段分数线；其它学科照此类推。

（二）利用分类汇总工具进行数据统计。

1、平均分：【数据】→【分类汇总】→【分类字段】：班级→【汇总方式】：平均值→【选定汇总项】：各科均选中→【确定】→【分级显示】：选中2级。

抄录有关数据到统计总表。

2、合格段人数：【移动或复制工作表】（选中【建立副本】）→语文科成绩降序排列→删除分数线下所有单元格数据。

→以班为单位排序→【分类汇总】→【分类字段】：班级→【汇总方式】：计数→【选定汇总项】：语文科→【确定】→【分层显示】：选中2层。

抄录有关数据到统计总表。

其它各科数据照此类推。

3、数据高级筛选：（适用于均合格；有效上线人数）（1）数据前插入条件区域，建立条件项：（在相应栏目上方，与数据区域有隔断行）语文数学总分>85 >78 >125（2）筛选数据：【数据】→【筛选】→【高级筛选】→点中【“筛选结果复制到基它区域”】→选定数据区域→选定条件区域→选定筛选结果存放区域→【确定】（3）对筛选出来区域的数据进行统计。

（三）利用数据透视表进行数据统计。

（1）【数据】→【数据透视表和数据透视图】→根据“数据透视表和数据透视图导向”进行操作：（2）【键入数据源区域】→【下一步】→【现有工作表】，指定建立统计区域位置→【布局】：“学校”拖入“行”，“班级”拖入“列”，各科成绩拖入“数据”（双击科目可变更统计项，也可将各科成绩多次拖入，变更成不同的统计项）→【确定】→【完成】（3）数据透视表中数据的显示：点击学校项下拉箭头，可显示指定学校数据。

点击班级项下拉箭头，可显示指定班级数据。

（四）建立班级统计模板1、在原始成绩表输入学生原始成绩。

（学生顺序不能变，缺考空位）（1）锁定单元格：【工具】→【保护】→【允许用户编辑区域】→【新建】→【引用单元格】：选择可编辑区（各科成绩、总分、位次列）→【工作表保护】→【确定】（2）计算总分：选中总分下第一格→输入=SUM(C2:F2)→【确定】→填充计算位次：选中总分下第一格→输入=RANK(G2,G:G) →确定→填充2、建班级档案工作表（1）在班级档案工作表中选中单元格A1，输入=原始成绩!A1→确定→拖动拖动填充柄至B56，复制学生信息。

（2）在班级档案工作表中选中单元格C1，输入=原始成绩!G1→确定→拖动拖动填充柄至D56，复制第一次总分、位次信息。

（3）在班级档案工作表中选中C、D两列，【复制】→选中E、F两列，【选择性粘贴】→【粘贴数值】→重命名列标题，固定第一次总分、位次信息。

以后每次获得的信息均以此方式固定，即可形成学生成绩档案。

3、建成绩分析表（1）选中“成绩分析”工作表，建立统计项：标题、科目、考试人数、各分数段、最高分、最低分、平均分（2）分别在各统计项后单元格内输入统计公式：考试人数：=COUNT(原始成绩!C:C)→确定→拖动填充柄至各科。

90-100分人数：=COUNTIF(原始成绩!C:C,">=90")→确定→拖动填充柄至各科。

80-89分人数：=COUNTIF(原始成绩!C:C,">=80")-B4→确定→拖动填充柄至各科。

70-79分人数：=COUNTIF(原始成绩!C:C,">=70")-B4-B5→确定→拖动填充柄至各科。

60-69分人数：=COUNTIF(原始成绩!C:C,">=60")-B4-B5-B6→确定→拖动填充柄至各科。

60分以下人数：=COUNTIF(原始成绩!C:C,"<60")→确定→拖动填充柄至各科。

最高分：=MAX(原始成绩!C:C)→确定→拖动填充柄至各科最低分：=MIN(原始成绩!C:C)→确定→拖动填充柄至各科平均分：=A VERAGE(原始成绩!C:C)→确定→拖动填充柄至各科（3）使用图表分析数据：【插入】→【图表】→【图表向导】→【图表类型】：饼型→【下一步】→【数据区域】→单击【折叠对话框】：选择数据区域（只选择与本学科有关的数据，不连续区域的选择办法是：选了第一区域后，按住CTEL控制键，选第二区域；依次类推）→【下一步】→【下一步】→【作为其中对象插入】→【确定】三、大面积数据的综合统计（一）整理数据：1、列的排列依次为：考号、学校、班级、各科成绩及总分2、学校、班数据集中。

3、利用函数LARGE（返回数据中第K个最大值。

）（1）确定分数线。

复制各科标题为分数线标题排在数据区域之右侧，选中分数线存放位置；,（2）确定第一科分数线后，向右拖动填充柄，即可获取各科分数线；在下行复复制公式，更改分数段人数，即可获取另一层次分数线。

（3）将分数线数据复制到新建空白统计表中：更改表名（选中表标签，点右键，重命名：分数线）；复制数据表中分数线→在分数线表中选中左上角单元格→点鼠标右键→【选择性粘贴】→【粘贴：数值】。

（二）分类汇总1、点击数据区域。

【数据】→【分类汇总】→【分类字段】：学校→【汇总方式】：计数→【选定汇总项】：第一科→【确定】。

2、再次以班为字段分类汇总。

【数据】→【分类汇总】→【分类字段】：班级→【汇总方式】：计数→【选定汇总项】：第一科→【确定】。

把【“替换当前分类汇总”】选项取消。

3、筛选：（1）选中表中任意单元格。

【数据】→【筛选】→【自动筛选】→点击学校列【下拉箭头】→【自定义】→【包含】→内容内输入“计数”（2）将筛选出的“学校计数项”复制到班级列。

（选中复制区域，拖动填充柄到班级列释放即可）（3）点击学校列下拉箭头→【显示全部数据】。

（4）在“班级”列重复第（1）步操作：点击班级列下拉箭头→【自定义】→【包含】→内容内输入“计数”（三）建立统计区1、在数据区域正面（隔两行）建立统计项目：（空）学校班级参统人数语文数学……平均分A段数B段数平均分A段数B段数2、替换引用区：选中第一科（即D列）所有筛选出的项目，执行替换操作：【编辑】→【替换】→【查找内容】：D→【替换为】$D$→【全部替换】3、替换公式：选中第一科（即D列）所有筛选出的项目，执行替换操作：【编辑】→【替换】→【查找内容】：subtotal(3,→【替换为】counta(→【全部替换】4、将替换后的内容整体复制到统计区域：选中第一科（即D列）所有筛选出的项目，复制选中统计区“学校”项下单元格，点击鼠标右键→【选择性粘贴】→【粘贴公式】。

5、删除原始数据区内含公式的行：选中原始数据区内所有筛选出的行，全部删除6、去除行标题中“计数”字符：选中统计区内所有包含“计数”字符的区域，执行替换操作：【编辑】→【替换】→【查找内容】：计数→【替换为】（保持空格）→【全部替换】（四）分科统计（统计第一科数据）1、复制参考人数栏内数据到“语文”科下各统计项下”平均分”、“A 段数”列下：选中参考人数栏内数据，拖动填充柄到目标列下释放。

2、选中“平均分”列所有数据，执行替换操作：【编辑】→【替换】→【查找内容】：counta→【替换为】:average→【全部替换】3、选中“A段数”列所有数据，执行替换操作：【编辑】→【替换】→【查找内容】：）→【替换为】：,”>=85”→【全部替换】(即：把后括号替为A段分数线)4、选中“A段数”列所有数据，再次执行替换操作：【编辑】→【替换】→【查找内容】：counta→【替换为】：countif→【全部替换】5、复制“A段数”列下数据到“B段数”列下：(拖动填充柄到目标列下释放)6、选中“B段数”列所有数据，执行替换操作：【编辑】→【替换】→【查找内容】：”>=85”→【替换为】：”>=70”→【全部替换】(即：把A段分数线替为B段分数线)(五) 分科统计（统计其它各科数据）1、将第一科统计结果复制到其它各科统计栏目下。

2、分科替换引用区：选中第二科下所有数据，执行替换操作：【编辑】→【替换】→【查找内容】：$D$→【替换为】：$E$→【全部替换】。

(即：把引用的D列（语文科）的原始数据替为E列（数学科）的原始数据。

3、分段替换分数线：（1）把语文A段分数线替为数学A段分数线：选中数学A段下所有数据，执行替换操作：【编辑】→【替换】→【查找内容】：”>=85”→【替换为】：”>=72”→【全部替换】（2）把语文B段分数线替为数学B段分数线：【编辑】→【替换】→【查找内容】：”>=72”→【替换为】：”>=69”→【全部替换】4、照此类推，替换其它所有学科下的数据。

（六）建立统计表1、插入新的空白工作表，明确统计方案：一、分数线：“合格”按参考人数的98%划线，“优秀”按参考人数的50%划线；“优生”人数中包括“合格”人数。

二、按考四科和三科两条线统计到学科、班级、学校。