1、=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)12、VALUE:将代表数值的文本字符串转换成数值。=VALUE(F2)13、IF:判定一个条件是否满足,如果满足则返回一个值,如果不满足则返回另一
2、个值。=IF(F2=80,合格,不合格同时满足多重条件的表达式:=IF(C2=90,IF(D2=84,IF(E2=38,1,0),0),0)注意:表达式中输入的数据、符号必须在英文状态下输入。二、简单数据的简便统计:(一)数据整理。 1、按单位整体集中数据(班为单位),统一编序号,删除与统计无关项目。把学校、班级列调整到靠近数据区。(操作方法:剪切要移动列选中要移入的目标列点击“【插入已剪切的单元格】”) 2、按升序或降序方式逐项清理各科数据,处理不合理数据(文本数据,超满分数据)。利用升降序工具进行操作时,数据必须至少有一方与数据区域相连。而按菜单【数据】【排序】操作则可避免此问题。 3、划
3、分分数线。按升序或降序方式:例:按A段人数(3500*40%=1400),选中语文科第1400行数据,将语文科降序排列,选中单元格即语文科A段分数线;其它学科照此类推。(二)利用分类汇总工具进行数据统计。1、平均分:【数据】【分类汇总】【分类字段】:班级【汇总方式】:平均值【选定汇总项】:各科均选中【确定】【分级显示】:选中2级。抄录有关数据到统计总表。2、合格段人数:【移动或复制工作表】(选中【建立副本】)语文科成绩降序排列删除分数线下所有单元格数据。以班为单位排序【分类汇总】【分类字段】:计数【选定汇总项】:语文科【确定】【分层显示】:选中2层。其它各科数据照此类推。3、数据高级筛选:(适
4、用于均合格;有效上线人数)(1)数据前插入条件区域,建立条件项:(在相应栏目上方,与数据区域有隔断行)语文 数学 总分85 78 125(2)筛选数据:【数据】【筛选】【高级筛选】点中【 “筛选结果复制到基它区域” 】 选定数据区域选定条件区域选定筛选结果存放区域【确定】(3)对筛选出来区域的数据进行统计。(三)利用数据透视表进行数据统计。(1)【数据】【数据透视表和数据透视图】根据“数据透视表和数据透视图导向”进行操作:(2)【键入数据源区域】【下一步】【现有工作表】,指定建立统计区域位置【布局】:“学校”拖入“行”,“班级”拖入“列”,各科成绩拖入“数据”(双击科目可变更统计项,也可将各科
5、成绩多次拖入,变更成不同的统计项)【确定】【完成】(3)数据透视表中数据的显示:点击学校项下拉箭头,可显示指定学校数据。点击班级项下拉箭头,可显示指定班级数据。(四)建立班级统计模板1、在原始成绩表输入学生原始成绩。(学生顺序不能变,缺考空位)(1)锁定单元格:【工具】【保护】【允许用户编辑区域】【新建】【引用单元格】:选择可编辑区(各科成绩、总分、位次列)【工作表保护】【确定】(2)计算总分:选中总分下第一格输入=SUM(C2:F2) 【确定】填充 计算位次:选中总分下第一格输入=RANK(G2,G:G) 确定填充2、建班级档案工作表(1)在班级档案工作表中选中单元格A1,输入=原始成绩!A
6、1 确定拖动拖动填充柄至B56,复制学生信息。(2)在班级档案工作表中选中单元格C1,输入=原始成绩!G1 确定拖动拖动填充柄至D56,复制第一次总分、位次信息。(3)在班级档案工作表中选中C、D两列,【复制】选中E、F两列,【选择性粘贴】【粘贴数值】重命名列标题,固定第一次总分、位次信息。以后每次获得的信息均以此方式固定,即可形成学生成绩档案。3、建成绩分析表(1)选中“成绩分析”工作表,建立统计项:标题、科目、考试人数、各分数段、最高分、最低分、平均分(2)分别在各统计项后单元格内输入统计公式:考试人数:=COUNT(原始成绩!C:C) 确定拖动填充柄至各科。90-100分人数:=COUN
7、TIF(原始成绩!C,)确定拖动填充柄至各科。80-89分人数:=80)-B4确定拖动填充柄至各科。70-79分人数:=70)-B4-B5确定拖动填充柄至各科。60-69分人数:=60)-B4-B5-B6确定拖动填充柄至各科。60分以下人数:=85” 【全部替换 】 (即:把后括号替为A段分数线)4、选中“A段数”列所有数据,再次执行替换操作:counta【替换为】:countif【全部替换】 5、复制“A段数”列下数据到 “B段数”列下:(拖动填充柄到目标列下释放)6、选中“B段数”列所有数据,执行替换操作:”=85” 【替换为】:=70” 【全部替换】(即:把A段分数线替为B段分数线) (
8、五) 分科统计(统计其它各科数据)1、将第一科统计结果复制到其它各科统计栏目下。2、分科替换引用区:选中第二科下所有数据,执行替换操作:$D$【替换为】:$E$【全部替换】。把引用的D列(语文科)的原始数据替为E列(数学科)的原始数据。3、分段替换分数线:(1)把语文A段分数线替为数学A段分数线:选中数学A段下所有数据,执行替换操作:=72” 【全部替换】 (2)把语文B段分数线替为数学B段分数线:=72” 【替换为】:=69” 【全部替换 】4、照此类推,替换其它所有学科下的数据。(六)建立统计表1、插入新的空白工作表,明确统计方案:一、分数线:“合格”按参考人数的98%划线,“优秀”按参考
9、人数的50%划线;“优生”人数中包括“合格”人数。二、按考四科和三科两条线统计到学科、班级、学校。学校按层次进行分析。三、学科统计“平均分”、“合格率”、“优秀率”三项;学校统计“总分平均分”、“总分合格率”、“均合格率”、“均优率”四项。“均合格(优)率”按语数外综四科统计。四、相关公式:学科平均分比率=平均分/该科全县最高平均分*100学科合格率=合格人数/参考人数*100优秀率=优秀人数/参考人数*100总分平均分比率=总分平均/全县学校最高平均分*100总分合格率=总分合格人数/参考人数*100均合格(优)率=四科均合格(优)人数/参考人数*100学科积分=平均分比率+合格率+优生率学
10、校积分=总分平均分比率*25%+总分合格率*25%+均合格率*25%+均优秀率*25%2、复制数据到空白统计表中选中统计区内的所有数据:复制选中空白统计表左上角:【选择性粘贴】【粘贴数字】3、在各段人数后分别插入空白列。4、在“语文A段”列后空白列自定义统计公式:选中该列数据区第一行单元格。在公式编辑栏输入公式:=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列下第一格,在公式编辑栏输入公式:=IF($D2=89,IF($E2=82,IF($F2=70,IF($G2=84,1,0),0),0),0) 确定。2、其它各项照此类推。3、照第(二)(三)(四)分科统计(统计第一科数据)的操作步骤进行统计(1)数据引用项替换为“标志”栏的列号;(2)将分数线全部替换为“=1”
copyright@ 2008-2022 冰豆网网站版权所有
经营许可证编号:鄂ICP备2022015515号-1