EXCEL函数公式1

1、1、平均分公式：=AVERAGE(C3:C45)2、给成绩打出等级区分优秀/合格/不合格的公式：=IF(C3=90,优秀,IF(C3=60,及格,不及格)3、优秀人数的公式：=COUNTIF(C3:C45,=90)4、优秀率的公式：=COUNTIF(C3:C45,=90)/COUNTA(A3:A45)5、合格人数的公式：=COUNTIF(C3:C45,=60)6、合格率的公式：=COUNTIF(C3:C45,=60)/COUNTA(A3:A45)7、不合格人数的公式：=COUNTIF(C3:C45,60)8、不合格率的公式：=COUNTIF(C3:C45,60)/COUNTA(A3:A45)9

2、、最高分的公式：=MAX(C3:C45)10、最低分的公式：=MIN（C3：C45）11、试卷难度公式：=AVERAGE(C3:C45)/10012、参考人数计算公式：=COUNTA(A3:A45)13、变异系数的公式：标准差STDEV/平均值AVERAGE=STDEV(G3:G32)/AVERAGE(G3:G32)14、试卷难度值公式：全卷实际难度值=全体学生的平均总分/试卷总分=AVERAGE(C3:C45)/10015、把一张表中不同列的数据合并到一张新的表格中的同一列（数据合并）公式：=CONCATENATE(统计数据!C3 )一个班 解释：先复制一个班，然后用同样的公式继续复制第二个

3、班16、统计各分数段人数，并填到新工作表中的公式：=COUNTIF(统计数据!C3:C32,=60)-COUNTIF(统计数据!G3:G32,=70)学校全面的试卷分析主要包括下列内容： 介绍考试类型说明本次考试是什么类型、什么范围的考试，考试的目的是什么，试题由什么人命题的。 介绍试卷的特点1内容结构分析：试卷分析要介绍试卷考查的范围、知识点及分值、试卷结构（题型比例、分值）、命题特点等。2目标水平结构分析：按照考试要求所列水平（通常分为知道、理解、应用、综合运用等）种类，分析它们的组成比例并且作内容水平双向交叉分析。3题型结构分析：各类题型的比例分析。4分数结构分析：内容分数和水平分数的双

4、向交叉分析。5难度和时限结构分析：对难度测验作难度分布描述，对速度测验作时限分布描述。6试卷特点及横向、纵向比较：就试卷是否符合课程标准的规定、是否反映学科特点和内在联系、符合学生实际水平、合理性、适宜性以及其他特点作出描述和判断。横向比较可以在班级班级或学校学校间进行，纵向比较可以在不同年级间进行。 统计数据试卷分析要统计有关数据，数据来源：全体考生(普查)、也可随机抽取样本，样本容量尽可能大一些。有关数据很多，如：难度、区分度、信度、效度、方差、标准差、变差数等，这里主要介绍难度和区分度：1.各题统计数据（难度、区分度）；（1）难度（一般用P表示）：难度是指测验试题的难易程度。客观题的难度

5、一般用正确回答试题的人数与参加测验的总人数的比值来表示；主观题的难度一般用参加测试考生在这题的平均得分与该题的分值的比值。适用于主、客观试题的计算公式：P=（PH+PL）/2（PH、PL分别为试题针对高分组和低分组考生的难度值）具体步骤为:将考生的总分由高至低排列；从最高分开始向下取全部试卷的27%作为高分组；从最低分开始向上取全部试卷的27%作为低分组；按上面的公式计算。例1：一次数学测试中，在100名学生中，高低分组各有27人，其中高分组答对第一题有20人，低分组答对第一题的有5人，这道题的难度为：PH=20/27=0.74 PL=5/27=0.19 P=(0.74+0.19)/2=0.4

6、7整个试卷的难度等于所有试题难度之平均值（包括主、客观试题）。由此可知，难度是试题对学生知识和能力水平的适合程度的指标，是一个相对概念，难度的高低与被试人员的水平直接相关，难度的取值范围为01之间，当P=0时，试题的实际困难程度最大，当P=1时，试题的实际难度最小。在一般的教学质量检测中，一份试卷的平均难度应在0.40.6之间，只有适中的难度，才能使试题产生区分不同程度考生的最大效果。 （2）区分度：区分度是指测验对考生水平的区分程度或鉴赏能力。它是题目质量和测验质量的一个重要指标。具有良好区分度的测验，实际水平高的该得高分，实际水平低的应该得低分，它是评价试题质量，筛选试题的主要指标和依据。

7、区分度的计算方法为：客观性试题区分度D的计算公式D=PH-PL（PH、PL分别为试题高分组和低分组考生的难度值）PH、PL的计算方法同上。例2：一次数学测试中，在100名学生中，高低分组各有27人，其中高分组答对第一题有20人，低分组答对第一题的有5人，这道题的区分度为：D=PH-PL=0.74-0.19=0.55主观试题（非选择题）区分度D的计算公式D=（XH-XL）/N（H-L）（XH表示接受测验的高分段学生的总得分数，XL表示接受测验的低分段学生的总得分数，N表示接受测验的学生总数，H表示该题的最高得分，L表示该题的最低得分。）整个试卷的区分度，是所有试题区分度的平均值。一般说来，当D0

8、.20时，题目的区分度太低，必须淘汰或者加以修改；当D0.40时，题目的区分度非常好；通常题目的区分度指数D在0.20.4之间。题目难度跟题目区分度之间有着一定的联系。难度太大或者太小，都可能使区分度变小；只有难度适中时，才可能有较高的区分度。2.试卷统计数据（平均分、标准差、难度比例、区分度比例）；平均分、最高分、最低分、分数段、优秀率、及格率标准差、难度、区分度3.相关图表分数段分布图、各题难度分布图、各题区分度分布图、难度比例统计图、区分度比例统计图，可利用excel软件统计画图，并作必要的文字说明。 学生答题情况统计各题的好的解答、错误的解答及原因分析。 教学分析和教学建议从得分、失分

9、情况以及某些部分之间的比较、分析，发现教师、学生以及命题等方面的成功与不足之处，并针对存在问题提出改进意见。为了使试卷分析全面、可靠和有效地发挥应有作用，试卷分析工作应实行教师分析和学生自我分析结合、定性分析和定量分析结合，在认真分析的基础上，简明、扼要、有重点地写出试卷分析报告。任课教师试卷分析教师撰写的一份完整的试卷分析报告应包括命题评价、成绩统计、存在问题、改进措施等（简称一查、二统、三找、四改）。一查即查每次考试的目的是什么，考试的知识范围有哪些，试卷中有些什么题型，哪类题型学生已见过，哪类题型学生初次见面，哪些题是考查基本知识和基本技能的，哪些题是能力题，题目难易分布情况如何及所占比

10、例的大小等等。（1）命题覆盖范围：是否覆盖了所有的章节，与课程标准和考核说明的要求是否一致，有无超标现象，能力要求是否进行恰当考查。（2）题量大小、难易程度：试题本身的难度、深度是否与教学大纲（课程标准）、教学要求相符，有无偏题、怪题等。可以列出双向细目表进行统计，例如：题号考查知识点能力要求过程方法情感态度价值观要求分值估计难度统计难度1速度公式（刘翔跨栏）精确度联系社会，爱国情感10.850.78注：表中“能力要求及过程、方法、情感、态度、价值观要求”不一定每个题目都有体现，有的话注明即可。估计难度一般为教师根据经验得到的估计值，统计难度为根据抽样试卷统计出的结果。2、成绩统计即数据统计分

11、析，任课教师通常关注的是优秀率、及格率、得分率（即难度值）及平均分。例：某次中考物理考试的数据统计参考人数15098优秀80分人数2537及格60分人数5997最高分100最高分人数3平均分69.14标准差15.73分差异系数54.84%难 度0.486做试卷分析需要进行数据统计，下面就有关的概念和统计方法介绍如下：（1）抽样统计：进行质量分析时，平均分、标准差、全卷难度等用全部考生的分数进行统计，但涉及每个题的情况就需要抽取部分试卷进行统计，这就需要抽取样本试卷。样卷尽可能考虑各个层次学生都有，这样才能更有代表性。按照统计学要求，样本量一般不小于30，所以对一个总人数较多的级段可以取学生总数

12、的5%10%，但对一个班级抽样时可以取2030人。样本抽取好后要做序号标记，以免重复统计。（2）优秀率、及格率、得分率：优秀率计算一般有两种统计方法，一种是分数达到某个值为优秀，多用于水平性测试，比如小学高段和初中的期中、期末考试，要求达到80分以上，这时优秀人数即为80分以上人数；一种是达到前多少名为优秀，多用于选拔性考试，比如2005年某地中考计划录取人数2400人，则前2400名为优秀人数，考试成绩第2400名的分数就是优秀线。优秀率=（优秀人数/总人数）100%难度值和得分率：全卷实际难度值=全体学生的平均总分/试卷总分全卷抽样难度值=抽样学生的平均总分/试卷总分比较上面两个难度值可以

13、看出抽样的代表性。如抽样难度值实际难度值，说明抽样学生的程度偏好。另外从上式看出，难度值越大题越容易。某题难度值=抽样中某题平均得分/该题满分难度在命题评价上叫难度系数（介于0和1之间），在答题方面实际也等于得分率（习惯上把小数转换成百分数）。小学高段试题的难度系数一般设定在0.8左右，初中试题一般设定在0.7左右，高中试题一般设定在0.6左右，高考试题的理想难度是0.55。（3）标准差和差异系数：标准差实际上反映了考生的两极分化程度，因此标准差应越小越好。在统计学上标准差有计算公式，现在用计算机统计常用Excel帮助统计，有现成的公式。使用方法如下：在Excel中已经输好的分数下面的空格中，

14、点菜单栏的“插入”函数统计STDEVP，看数据区域正确后确定即可。其它函数如平均分（AVERAGE）、求和（SUM）、条件统计（COUNT）等都可以用这样的方法得到。因为标准差随文、理学科情况有很大变化，也随学科总分值和试题难度系数而变化（难度较大的理科和总分较高的学科标准差会较大），所以为了使各学科之间能够比较、各次考试能够比较，人们又定义了差异系数差异系数=（标准差/平均分）100%中学理科的差异系数一般在45%左右，中学文科一般在30%左右。3、存在问题（1）通过学生考试的分数，可以发现教师教学中存在的一些问题。从教学方法、对待教学工作的责任感以及驾驭教材的能力等方面去思考。是教师钻研教材不深，驾驭教材能力不强，还是教学方法不当或者是工作态度不好，责任感欠强等等。（2）从卷面情况看，可以看出学生答题的具体问题。是学生智力因素引起的还是非智力因素引起的，是基础知识掌握不牢还是学习方法不当，发现学生出现的知识错误及引起错误的原因。试卷上普遍存在的问题是什么，个别问题又出现在哪些个别学生身上，是概念法则不清，还是计算能力薄弱，是单项知识没掌握，还是综合运用所学知识解决实际问题的能力不强。（3）