《利用EXCEL进行数据统计若干技巧》由会员分享，可在线阅读，更多相关《利用EXCEL进行数据统计若干技巧（12页珍藏版）》请在金锄头文库上搜索。
1、1利用 Excel 进行数据统计的若干技巧一、认识常用的统计函数：1、SUM ：计算单元格区域内所有数据的和。表达式为：=SUM （D2：F50）2、AVERAGE：计算单元格区域内所有数据的算术平均数。表达式为：=AVERAGE（D2：F50）3、COUNT：计算表中的数字参数和包含数字的单元格的个数。表达式为：=COUNT（D2：F50）4、COUNTIF：计算单元格区域内满足给定条件单元格的个数。表达式为：=COUNTIF（D2：F50 ， =90）5、MAX：返回一组数值中的最大值。表达式为：=MAX （D2 ：F50 ）6、MIN：返回一组数值中的最小值。表达式为：=MIN（D2：F
2、50）7、MIDB：自文字起始位置开始提取指定长度字符串。表达式为：=MIDB(B2,8,6)8、LARGE：返回数据中第 K 个最大值。表达式为：=LARGE(F2:F34,11)9、SMAL：返回数据中第 K 个最小值。表达式为=SMALL(F2:F28,5)10、RANK：返回指定数字在列中的排位。表达式为：=RANK(I2,I:I )利用好统计公式中的定位符$ 例：RANK(I2,$I$2:$I$180)11、EXACT：比较两个字符串是否完全相同（区分大小写） ，如果完全相同，返回TREU，否则返回 FALSE。表达式为：=EXACT(D2,J2)统计区域给定条件指定字符起始位置提取
3、字符串长度指定第 K 个数指定数字指定数列212、VALUE：将代表数值的文本字符串转换成数值。表达式为：=VALUE(F2)13、IF：判定一个条件是否满足，如果满足则返回一个值，如果不满足则返回另一个值。表达式为：=IF(F2=80, 合格,不合格)同时满足多重条件的表达式：=IF(C2=90,IF(D2=84,IF(E2=38 ,1,0),0),0)注意：表达式中输入的数据、符号必须在英文状态下输入。二、简单数据的简便统计：（一）数据整理。1、按单位整体集中数据（班为单位） ，统一编序号，删除与统计无关项目。把学校、班级列调整到靠近数据区。 （操作方法：剪切要移动列选中要移入的目标列点击
4、“【插入已剪切的单元格】 ”）2、按升序或降序方式逐项清理各科数据，处理不合理数据（文本数据，超满分数据） 。注意：利用升降序工具进行操作时，数据必须至少有一方与数据区域相连。而按菜单【数据】【排序】操作则可避免此问题。3、划分分数线。按升序或降序方式：例：按 A 段人数（3500*40%=1400） ，选中语文科第 1400 行数据，将语文科降序排列，选中单元格即语文科A 段分数线；其它学科照此类推。指定条件 不满足条件的结果第一条件 第三条件满足条件返回顾 1，不满足条件返回 03（二）利用分类汇总工具进行数据统计。1、平均分：【数据】【分类汇总】【分类字段】：班级【汇总方式】：平均值【选
5、定汇总项】：各科均选中【确定】【分级显示】：选中 2 级。抄录有关数据到统计总表。2、合格段人数：【移动或复制工作表】 （选中【建立副本】 ）语文科成绩降序排列删除分数线下所有单元格数据。以班为单位排序【分类汇总】【分类字段】：班级【汇总方式】：计数【选定汇总项】：语文科【确定】【分层显示】：选中 2 层。抄录有关数据到统计总表。其它各科数据照此类推。3、数据高级筛选：（适用于均合格；有效上线人数）（1）数据前插入条件区域，建立条件项：（在相应栏目上方，与数据区域有隔断行）语文 数学 总分85 78 125（2）筛选数据：【数据】【筛选】【高级筛选】点中【 “筛选结果复制到基它区域” 】 选定
6、数据区域选定条件区域选定筛选结果存放区域【确定】（3）对筛选出来区域的数据进行统计。（三）利用数据透视表进行数据统计。（1） 【数据】【数据透视表和数据透视图】根据“数据透视表和数据透视图导向”进行操作：4（2） 【键入数据源区域】【下一步】【现有工作表】 ，指定建立统计区域位置【布局】：“学校”拖入“行” ， “班级”拖入“列” ，各科成绩拖入“数据” （双击科目可变更统计项，也可将各科成绩多次拖入，变更成不同的统计项）【确定】【完成】（3）数据透视表中数据的显示：点击学校项下拉箭头，可显示指定学校数据。点击班级项下拉箭头，可显示指定班级数据。（四）建立班级统计模板1、在原始成绩表输入学生原
7、始成绩。 （学生顺序不能变，缺考空位）（1）锁定单元格：【工具】【保护】【允许用户编辑区域】【新建】【引用单元格】：选择可编辑区（各科成绩、总分、位次列）【工作表保护】【确定】（2）计算总分：选中总分下第一格输入=SUM(C2:F2) 【确定】填充 计算位次：选中总分下第一格输入=RANK(G2,G:G) 确定填充2、建班级档案工作表（1）在班级档案工作表中选中单元格 A1，输入= 原始成绩!A1 确定拖动拖动填充柄至 B56，复制学生信息。（2）在班级档案工作表中选中单元格 C1，输入=原始成绩!G1 确定拖动拖动填充柄至 D56，复制第一次总分、位次信息。（3）在班级档案工作表中选中 C、
8、D 两列， 【复制】选中 E、F 两列， 【选择性粘贴】【粘贴数值】重命名列标题，固定第一次总5分、位次信息。以后每次获得的信息均以此方式固定，即可形成学生成绩档案。3、建成绩分析表（1）选中“成绩分析”工作表，建立统计项：标题、科目、考试人数、各分数段、最高分、最低分、平均分（2）分别在各统计项后单元格内输入统计公式：考试人数：=COUNT(原始成绩!C:C) 确定拖动填充柄至各科。90-100 分人数：=COUNTIF(原始成绩!C:C,=90) 确定拖动填充柄至各科。80-89 分人数：=COUNTIF(原始成绩!C:C,=80)-B4 确定拖动填充柄至各科。70-79 分人数：=COU
9、NTIF(原始成绩!C:C,=70)-B4-B5 确定拖动填充柄至各科。60-69 分人数：=COUNTIF(原始成绩!C:C,=60)-B4-B5-B6 确定拖动填充柄至各科。60 分以下人数：=COUNTIF( 原始成绩!C:C,=85” 【全部替换 】 (即：把后括号替为 A 段分数线)4、选中“A 段数”列所有数据，再次执行替换操作：【编辑】【替换】【查找内容】：counta 【替换为】：countif【全部替换】 5、复制“A 段数” 列下数据到 “B 段数”列下：(拖动填充柄到目标列下释放)6、选中“B 段数”列所有数据，执行替换操作：【编辑】【替换】【查找内容】：”=85” 【替
10、换为】：”=70” 【全部替换】 (即：把 A 段分数线替为 B 段分数线) (五) 分科统计（统计其它各科数据）1、将第一科统计结果复制到其它各科统计栏目下。2、分科替换引用区：选中第二科下所有数据，执行替换操作：【编辑】【替换】【查找内容】：$D$【替换为】：$E$【 全部替换】 。(即：把引用的 D 列（语文科）的原始数据替为 E 列（数学科）的原始数据。3、分段替换分数线：（1）把语文 A 段分数线替为数学 A 段分数线：选中数学 A 段下所有数据，执行替换操作：【编辑】【替换】【查找内容】：”=85” 【替换为】：”=72” 【全部替换】 10（2）把语文 B 段分数线替为数学 B
11、段分数线：【编辑】【替换】【查找内容】：”=72” 【替换为】：”=69” 【全部替换 】4、照此类推，替换其它所有学科下的数据。（六）建立统计表1、插入新的空白工作表，明确统计方案：一、分数线：“合格”按参考人数的 98%划线，“优秀”按参考人数的50%划 线；“优生” 人数中包括 “合格”人数。二、按考四科和三科两条线统计到学科、班 级、学校。学校按层次进行分析。三、学科统计“平均分”、 “合格率”、“优秀率”三项；学校统计“总分平均分”、 “总 分合格率” 、“均合格率” 、“均优率” 四项。“ 均合格（优）率”按语数 外综四科统计。四、相关公式：学科平均分比率=平均分/该科全县最高平均
12、分*100学科合格率=合格人数/参考人数 *100优秀率=优秀人数 /参考人数 *100总分平均分比率=总分平均/全县学校最高平均分*100总分合格率=总分合格人数/参考人数*100均合格（优）率=四科均合格（优）人数/参考人数*100学科积分=平均分比率 +合格率 +优生率学校积分=总 分平均分比率*25%+ 总分合格率*25%+均合格率*25%+均优秀率*25%112、复制数据到空白统计表中选中统计区内的所有数据：复制选中空白统计表左上角：【选择性粘贴】【粘贴数字】3、在各段人数后分别插入空白列。4、在“语文 A 段”列后空白列自定义统计公式：选中该列数据区第一行单元格。在公式编辑栏输入公
13、式：=d3/$c3*100， 【确定】双击填充柄复制公式到该列所有单元格。5、将“语文 A 段”的统计公式复制到其它各科各段人数列后：选中“语文 A 段”列的统计公式，复制。分别粘贴到其它空白列：【选择性粘贴】【粘贴公式】6、计算各科积分：分别在各科后插入空白列。学科积分=平均分比率+ 合格率+优生率在公式编辑栏输入公式： =d4/MAX(d:d)*100+f4+h4， 【确定】7、排位在积分后插入空白列。在公式编辑栏输入公式：=RANK(I2,I$4:I$29)(七) 关于各科均优、有效上线的统计：1、在数据区后建立标志，分别用“1” 、 “0”代表上线和未上线。建立列标志，均 A，均 B（或 A 线有效，B 线有效）选中均 A 列下第一格，在公式编辑栏输入公式： 12=IF($D2=89,IF($E2=82,IF($F2=70,IF($G2=84,1,0),0),0),0) 确定。2、其它各项照此类推。3、照第（二） （三） （四）分科统计（统计第一科数据）的操作步骤进行统计注意：（1）数据引用项替换为“标志”栏的列号；（2）将分数线全部替换为“=1”第一条件 第二条件 第三条件 满足条件不满足条件
