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

1、利用Excel进行数据统计的若干技巧认识常用的统计函数:131、SUM :计算单元格区域内所有数据的和。表达式为：=SUM (D2:2、AVERAGE :计算单元格区域内所有数据的算术平均数。表达式为：=AVERAGE (D2: F50)3、COUNT :计算表中的数字参数和包含数字的单元格的个数。表达式为：=COUNT (D2: F50)4、COUNTIF :计算单元格区域内满足给定条件单元格的个数。5、表达式为：=COUNTIF (D2: F50,MAX :返回一组数值中的最大值。表达式为：=MAX (D2: F50)表达式为=SMALL(F2:F28,5)10、RANK :返回指定数字在

2、列中的排位。表达式为：=RANK(I2,I:I指定数列例：RANK(I2,$I$2:$I$180)利用好统计公式中的定位符 $11、EXACT :比较两个字符串是否完全相同(区分大小写) 否则返回FALSE。,如果完全相同，返回 TREU,表达式为：=EXACT(D2,J2)满足条件返回顾1，不 满足条件返回0条件12、VALUE :将代表数值的文本字符串转换成数值。表达式为：=VALUE（F2）13、IF:判定一个条件是否满足，如果满足则返回一个值，如果不满足则返回另一个值。表达式为:=IF（F2>=80,"合格"，"不合格"）同时满足多重条件的

3、表达式：=IF(C2>=90,IF(D2>=84,IF(E2>=38,1,0),0),0)注意：表达式中输入的数据、符号必须在英文状态下输入。二、简单数据的简便统计：（一）数据整理。1、按单位整体集中数据（班为单位），统一编序号，删除与统计 无关项目。把学校、班级列调整到靠近数据区。 （操作方法：剪切要 移动列-选中要移入的目标列-点击“【插入已剪切的单元格】”）2、按升序或降序方式逐项清理各科数据，处理不合理数据（文 本数据，超满分数据）。注意：利用升降序工具进行操作时，数据必须至少有一方与数据区 域相连。而按菜单【数据】-【排序】操作则可避免此问题。3、划分分数线。按升序

4、或降序方式：例：按 A段人数（3500*40%=1400）,选中语 文科第1400行数据，将语文科降序排列，选中单元格即语文科 A段 分数线；其它学科照此类推。（二）利用分类汇总工具进行数据统计。1、 平均分：【数据】7【分类汇总】T【分类字段】：班级7【汇总 方式】：平均值7【选定汇总项】：各科均选中7【确定】T【分级显 示】：选中2级。抄录有关数据到统计总表。2、合格段人数：【移动或复制工作表】(选中【建立副本】)r语文科 成绩降序排列r删除分数线下所有单元格数据。 r以班为单位排序r【分类汇总】r【分类字段】：班级r【汇总方式】：计数r【选定汇总项】：语文科r【确定】t【分层显示】：选中

5、2层。抄录有关数据 到统计总表。其它各科数据照此类推。3、数据高级筛选：(适用于均合格；有效上线人数)(1) 数据前插入条件区域，建立条件项：(在相应栏目上方，与数据 区域有隔断行)语文数学总分>85>78>125(2)筛选数据:【数据】-»【筛选】T【高级筛选】7点中【“筛选结果复制到基它区域”】7选定数据区域7选定条件区域7选定筛选结果存放区域7【确定】(3)对筛选出来区域的数据进行统计。(三) 利用数据透视表进行数据统计。(1) 【数据】T【数据透视表和数据透视图】7根据“数据透视表和数据透视图导向”进行操作：(2) 【键入数据源区域】7【下一步】T【现有工作

6、表】，指定建立 统计区域位置7【布局】："学校”拖入“行”，“班级”拖入“列”， 各科成绩拖入“数据”(双击科目可变更统计项，也可将各科成绩多 次拖入，变更成不同的统计项)T【确定】T【完成】(3) 数据透视表中数据的显示：点击学校项下拉箭头，可显示指定学校数据。点击班级项下拉箭头，可显示指定班级数据。(四) 建立班级统计模板1、 在原始成绩表输入学生原始成绩。(学生顺序不能变，缺考空位)(1) 锁定单元格：【工具】-【保护】-【允许用户编辑区域】T【新 建】7【引用单元格】：选择可编辑区(各科成绩、总分、位次列)T【工作表保护】T【确定】(2) 计算总分：选中总分下第一格r输入 =

7、SUM(C2:F2) r【确定】7填充计算位次：选中总分下第一格r输入=RANK(G2,G:G) 确定填充2、建班级档案工作表(1) 在班级档案工作表中选中单元格 A1,输入=原始成绩!A1 7确 定r拖动拖动填充柄至B56,复制学生信息。(2) 在班级档案工作表中选中单元格 C1,输入=原始成绩！G1 r确 定r拖动拖动填充柄至 D56,复制第一次总分、位次信息。(3) 在班级档案工作表中选中 C、D两列，【复制】r选中E、F两 列，【选择性粘贴】T【粘贴数值】7重命名列标题，固定第一次总分、位次信息。以后每次获得的信息均以此方式固定，即可形成学生成绩档案。3、建成绩分析表（1）选中“成绩分

8、析”工作表，建立统计项：标题、科目、考试人数、各分数段、最高分、最低分、平均分（2）分别在各统计项后单元格内输入统计公式：考试人数：=COUNT（原始成绩!C:C） r确定r拖动填充柄至各科。90-100分人数：=COUNTIF（原始成绩！C:C,">=90"） r确定拖动填充 柄至各科。80-89分人数：=COUNTIF（原始成绩!C:C,">=80"）-B4 确定拖动填 充柄至各科。70-79分人数：=COUNTIF（原始成绩！C:C,">=70”）-B4-B5 确定拖 动填充柄至各科。60-69分人数：=COUNTIF（

9、原始成绩！C:C,">=60”）-B4-B5-B6 确定 拖动填充柄至各科。60分以下人数：=COUNTIF（原始成绩！C:C,"<60"）r确定拖动填充 柄至各科。最高分：=MAX（原始成绩！C:C） r确定r拖动填充柄至各科最低分：=MIN（原始成绩！C:C） r确定r拖动填充柄至各科平均分：=AVERAGE（原始成绩!C:C） 确定拖动填充柄至各科（3）使用图表分析数据：【插入】r【图表】r【图表向导】t【图表类型】：饼型r【下一 步】r【数据区域】r单击【折叠对话框】：选择数据区域（只选择 与本学科有关的数据，不连续区域的选择办法是：选了第一区

10、域后，按住CTE母空制键，选第二区域；依次类推），【下一步】，【下一步】【作为其中对象插入】【确定】三、大面积数据的综合统计（一）整理数据：1、列的排列依次为：考号、学校、班级、各科成绩及总分2、学校、班数据集中。3、利用函数LARGE （返回数据中第K个最大值。）（1）确定分数线。复制各科标题为分数线标题排在数据区域之右侧，选中分数线存放位置；在公式编辑栏中输入分式：例：=LARGE（F2:F34,10）（2）确定第一科分数线后，向右拖动填充柄， 即可获取各科分数线； 在下行复复制公式，更改分数段人数，即可获取另一层次分数线。（3）将分数线数据复制到新建空白统计表中：更改表名（选中表标签，点

11、右键，重命名：分数线）；复制数据表中分数线-在分数线表中选中左上角单兀格点鼠标右键 【选择性粘贴】【粘贴：数值】。（二）分类汇总1、 点击数据区域。【数据】-【分类汇总】-【分类字段】：学校- 【汇总方式】：计数【选定汇总项】：第一科【确定】。2、再次以班为字段分类汇总。【数据】-【分类汇总】-【分类字段】: 班级7【汇总方式】：计数7【选定汇总项】：第一科7【确定】。把【"替换当前分类汇总”】选项取消。3、筛选：（1） 选中表中任意单元格。【数据】T【筛选】T【白动筛选】7点击学校列【下拉箭头】T【白定义】T【包含】7内容内输入“计数”（2）将筛选出的“学校计数项”复制到班级列。（

12、选中复制区域，拖动填充柄到班级列释放即可）（3）点击学校列下拉箭头7【显示全部数据】。（4）在“班级”列重复第（1）步操作：点击班级列下拉箭头r【白定义】T【包含】7内容内输入“计数”（三）建立统计区1、在数据区域正面（隔两行）建立统计项目：（空） 学校 班级 参统人数 语文数学 ，平均分 A段数 B段数 平均分 A段数 B段数2、替换引用区：选中第一科（即 D列）所有筛选出的项目，执行替 换操作：【编辑】7【替换】T【查找内容】：Dr【替换为】$D$r【全部替换】3、替换公式：选中第一科（即 D列）所有筛选出的项目，执行替换 操作：【编辑】T【替换】T【查找内容】：subtotal（3,-【

13、替换为】countap【全部替换】4、将替换后的内容整体复制到统计区域：选中第一科（即D列）所有筛选出的项目，复制选中统计区“学校”项下单元格，点击鼠标右键7【选择性粘贴】7【粘贴公式】。5、删除原始数据区内含公式的行：选中原始数据区内所有筛选出的行，全部删除6、去除行标题中“计数”字符：选中统计区内所有包含“计数”字符的区域，执行替换操作：【编辑】7【替换】T【查找内容】：计数7【替换为】（保持空格）7【全部替换】（四）分科统计（统计第一科数据）1、 复制参考人数栏内数据到“语文”科下各统计项下”平均分”、A 段数”列下：选中参考人数栏内数据，拖动填充柄到目标列下释放。2、选中“平均分”列所

14、有数据，执行替换操作：【编辑】7【替换】t【查找内容】：counted【替换为】：average【全部替换】3、选中“A段数”列所有数据，执行替换操作：【编辑】r【替换】t【查找内容】：）r【替换为】：，”>=85”r【全部替换】（即：把后括号替为A段分数线）4、选中“A段数”列所有数据，再次执行替换操作：【编辑】7【替换】t【查找内容】：counter【替换为】：countifr【全部替换】5、复制A段数”列下数据到 B段数”列下：（拖动填充柄到目标列下 释放）6、选中“ B段数”列所有数据，执行替换操作：【编辑】r【替换】t【查找内容】：”>=85 J 【替换为】：”>=

15、70”T【全部替换】（即：把A段分数线替为B段分数线）（五）分科统计（统计其它各科数据）1、将第一科统计结果复制到其它各科统计栏目下。2、分科替换引用区：选中第二科下所有数据，执行替换操作：【编辑】r【替换】t【查找内容】：$D$r【替换为】：$EA【全部替换】。（即：把引用的D列（语文科）的原始数据替为 E列（数 学科）的原始数据。3、分段替换分数线：（1）把语文A段分数线替为数学A段分数线：选中数学A段下所有数据，执行替换操作：【编辑】r【替换】t【查找内容】：”>=85 J 【替换为】：”>=72”T【全部替换】（2）把语文B段分数线替为数学B段分数线：【编辑】r【替换】t【

16、查找内容】：”>=72 J 【替换为】：”>=69”T【全部替换】4、照此类推，替换其它所有学科下的数据。（六）建立统计表1、插入新的空白工作表，明确统计方案：一、 分数线：“合格”按参考人数的98喊。线，“优秀”按参考人 数的50喊。线；“优生”人数中包括“合格”人数。二、按考四科和三科两条线统计到学科、 班级、学校。学校按层次 进行分析。三、学科统计“平均分”、“合格率”、“优秀率”三项；学校统 计“总分平均分”、“总分合格率”、“均合格率”、“均优率”四项。“均合格（优）率”按语 数 外 综四科统计。四、相关公式：学科平均分比率=平均分/该科全县最高平均分*100学科合格率=

17、合格人数/参考人数*100优秀率=优秀人数/参考人数*100总分平均分比率=总分平均/全县学校最高平均分*100总分合格率=总分合格人数/参考人数*100均合格（优）率=四科均合格（优）人数/参考人数*100学科积分=平均分比率心格率+优生率学校积分=总分平均分比率*25%+总分合格率*25%+匀合格率*25%+均优秀率*25%2、复制数据到空白统计表中选中统计区内的所有数据：复制选中空白统计表左上角：【选择性粘贴】7【粘贴数字】3、在各段人数后分别插入空白列。4、在“语文A段”列后空白列白定义统计公式：选中该列数据区第一行单元格。在公式编辑栏输入公式：=d3/$c3*100,【确定】双击填充柄复制公式到该列所有单元格。5、将“语文A段”的统计公式复制到其它各科各段人数列后：选中“语文A段”列的统计公式，复制。分别粘贴到其它空白列：【选择性粘贴】r【粘贴公式】6、计算各科积分：分别在各科后插入空白列学科积分=平均分比率+合格率砒生率在公式编辑栏输入公式：=d4/MAX（d:d） *100+f4+h4,【