EXCEL函数公式1.docx

EXCEL函数公式1.docx

《EXCEL函数公式1.docx》由会员分享，可在线阅读，更多相关《EXCEL函数公式1.docx（9页珍藏版）》请在冰豆网上搜索。

EXCEL函数公式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、最高分的公式：

=MAX（C3:

C45）

10、最低分的公式：

=MIN（C3：

C45）

11、试卷难度公式：

=AVERAGE（C3:

C45）/100

12、参考人数计算公式：

=COUNTA（A3:

A45）

13、变异系数的公式：

[标准差STDEV/平均值AVERAGE]

=STDEV（G3:

G32）/AVERAGE（G3:

G32）

14、试卷难度值公式：

全卷实际难度值=全体学生的平均总分/试卷总分

=AVERAGE（C3:

C45）/100

15、把一表中不同列的数据合并到一新的表格中的同一列（数据合并）公式：

=CONCATENATE（统计数据!

C3）[一个班]

解释：

先复制一个班，然后用同样的公式继续复制第二个班……

16、统计各分数段人数，并填到新工作表中的公式：

=COUNTIF（统计数据!

C3:

C32,"<60"）

=COUNTIF（统计数据!

C3:

C32,">=60"）-COUNTIF（统计数据!

G3:

G32,">=70"）

学校全面的试卷分析主要包括下列容：

㈠介绍考试类型

说明本次考试是什么类型、什么围的考试，考试的目的是什么，试题由什么人命题的。

㈡介绍试卷的特点

1．容结构分析：

试卷分析要介绍试卷考查的围、知识点及分值、试卷结构（题型比例、分值）、命题特点等。

2．目标水平结构分析：

按照考试要求所列水平（通常分为知道、理解、应用、综合运用等）种类，分析它们的组成比例并且作容——水平双向交叉分析。

3．题型结构分析：

各类题型的比例分析。

4．分数结构分析：

容——分数和水平——分数的双向交叉分析。

5．难度和时限结构分析：

对难度测验作难度分布描述，对速度测验作时限分布描述。

6．试卷特点及横向、纵向比较：

就试卷是否符合课程标准的规定、是否反映学科特点和在联系、符合学生实际水平、合理性、适宜性以及其他特点作出描述和判断。

横向比较可以在班级—班级或学校—学校间进行，纵向比较可以在不同年级间进行。

㈢统计数据

　　试卷分析要统计有关数据，数据来源：

全体考生（普查）、也可随机抽取样本，样本容量尽可能大一些。

有关数据很多，如：

难度、区分度、信度、效度、方差、标准差、变差数等，这里主要介绍难度和区分度：

1.各题统计数据（难度、区分度）；

（1）难度（一般用P表示）：

难度是指测验试题的难易程度。

客观题的难度一般用正确回答试题的人数与参加测验的总人数的比值来表示；

主观题的难度一般用参加测试考生在这题的平均得分与该题的分值的比值。

适用于主、客观试题的计算公式：

P=（PH+PL）/2

（PH、PL分别为试题针对高分组和低分组考生的难度值）

具体步骤为:

①将考生的总分由高至低排列；

②从最高分开始向下取全部试卷的27%作为高分组；

③从最低分开始向上取全部试卷的27%作为低分组；

④按上面的公式计算。

例1：

一次数学测试中，在100名学生中，高低分组各有27人，其中高分组答对第一题有20人，低分组答对第一题的有5人，这道题的难度为：

PH=20/27=0.74PL=5/27=0.19P=（0.74+0.19）/2=0.47

整个试卷的难度等于所有试题难度之平均值（包括主、客观试题）。

　　由此可知，难度是试题对学生知识和能力水平的适合程度的指标，是一个相对概念，难度的高低与被试人员的水平直接相关，难度的取值围为0～1之间，当P=0时，试题的实际困难程度最大，当P=1时，试题的实际难度最小。

在一般的教学质量检测中，一份试卷的平均难度应在0.4～0.6之间，只有适中的难度，才能使试题产生区分不同程度考生的最大效果。

（2）区分度：

区分度是指测验对考生水平的区分程度或鉴赏能力。

它是题目质量和测验质量的一个重要指标。

具有良好区分度的测验，实际水平高的该得高分，实际水平低的应该得低分，它是评价试题质量，筛选试题的主要指标和依据。

区分度的计算方法为：

①客观性试题区分度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表示该题的最低得分。

）

整个试卷的区分度，是所有试题区分度的平均值。

　　一般说来，当D＜0.20时，题目的区分度太低，必须淘汰或者加以修改；当D≥0.40时，题目的区分度非常好；通常题目的区分度指数D在0.2～0.4之间。

题目难度跟题目区分度之间有着一定的联系。

难度太大或者太小，都可能使区分度变小；只有难度适中时，才可能有较高的区分度。

2.试卷统计数据（平均分、标准差、难度比例、区分度比例）；

①平均分、最高分、最低分、分数段、优秀率、及格率

②标准差、难度、区分度

3.相关图表

　　分数段分布图、各题难度分布图、各题区分度分布图、难度比例统计图、区分度比例统计图，可利用excel软件统计画图，并作必要的文字说明。

㈣学生答题情况统计各题的好的解答、错误的解答及原因分析。

㈤教学分析和教学建议

从得分、失分情况以及某些部分之间的比较、分析，发现教师、学生以及命题等方面的成功与不足之处，并针对存在问题提出改进意见。

为了使试卷分析全面、可靠和有效地发挥应有作用，试卷分析工作应实行教师分析和学生自我分析结合、定性分析和定量分析结合，在认真分析的基础上，简明、扼要、有重点地写出试卷分析报告。

任课教师试卷分析

　　教师撰写的一份完整的试卷分析报告应包括命题评价、成绩统计、存在问题、改进措施等（简称一查、二统、三找、四改）。

一查即查每次考试的目的是什么，考试的知识围有哪些，试卷中有些什么题型，哪类题型学生已见过，哪类题型学生初次见面，哪些题是考查基本知识和基本技能的，哪些题是能力题，题目难易分布情况如何及所占比例的大小等等。

（1）命题覆盖围：

是否覆盖了所有的章节，与课程标准和考核说明的要否一致，有无超标现象，能力要否进行恰当考查。

（2）题量大小、难易程度：

试题本身的难度、深度是否与教学大纲（课程标准）、教学要求相符，有无偏题、怪题等。

可以列出双向细目表进行统计，例如：

题号

考查知识点

能力要求

过程方法情感态度价值观要求

分值

估计难度

统计难度

1

速度公式（翔跨栏）

精确度

联系社会，爱国情感

1

0.85

0.78

……

注：

表中“能力要求及过程、方法、情感、态度、价值观要求”不一定每个题目都有体现，有的话注明即可。

估计难度一般为教师根据经验得到的估计值，统计难度为根据抽样试卷统计出的结果。

2、成绩统计即数据统计分析，任课教师通常关注的是优秀率、及格率、得分率（即难度值）及平均分。

例：

某次中考物理考试的数据统计

参考人数

15098

优秀[80分]人数

2537

及格[60分]人数

5997

最高分

100

最高分人数

3

平均分

69.14

标准差

15.73分

差异系数

54.84%

难度

0.486

做试卷分析需要进行数据统计，下面就有关的概念和统计方法介绍如下：

（1）抽样统计：

　　进行质量分析时，平均分、标准差、全卷难度等用全部考生的分数进行统计，但涉及每个题的情况就需要抽取部分试卷进行统计，这就需要抽取样本试卷。

样卷尽可能考虑各个层次学生都有，这样才能更有代表性。

按照统计学要求，样本量一般不小于30，所以对一个总人数较多的级段可以取学生总数的5%～10%，但对一个班级抽样时可以取20～30人。

样本抽取好后要做序号标记，以免重复统计。

（2）优秀率、及格率、得分率：

　　优秀率计算一般有两种统计方法，一种是分数达到某个值为优秀，多用于水平性测试，比如小学高段和初中的期中、期末考试，要求达到80分以上，这时优秀人数即为80分以上人数；一种是达到前多少名为优秀，多用于选拔性考试，比如2005年某地中考计划录取人数2400人，则前2400名为优秀人数，考试成绩第2400名的分数就是优秀线。

优秀率=（优秀人数/总人数）×100%

难度值和得分率：

全卷实际难度值=全体学生的平均总分/试卷总分

全卷抽样难度值=抽样学生的平均总分/试卷总分

　　比较上面两个难度值可以看出抽样的代表性。

如抽样难度值>实际难度值，说明抽样学生的程度偏好。

另外从上式看出，难度值越大题越容易。

某题难度值=抽样中某题平均得分/该题满分

　　难度在命题评价上叫难度系数（介于0和1之间），在答题方面实际也等于得分率（习惯上把小数转换成百分数）。

小学高段试题的难度系数一般设定在0.8左右，初中试题一般设定在0.7左右，高中试题一般设定在0.6左右，高考试题的理想难度是0.55。

（3）标准差和差异系数：

　　标准差实际上反映了考生的两极分化程度，因此标准差应越小越好。

在统计学上标准差有计算公式，现在用计算机统计常用Excel帮助统计，有现成的公式。

使用方法如下：

　　在Excel中已经输好的分数下面的空格中，点菜单栏的“插入”——函数——统计——STDEVP，看数据区域正确后确定即可。

其它函数如平均分（AVERAGE）、求和（SUM）、条件统计（COUNT）等都可以用这样的方法得到。

　　因为标准差随文、理学科情况有很大变化，也随学科总分值和试题难度系数而变化（难度较大的理科和总分较高的学科标准差会较大），所以为了使各学科之间能够比较、各次考试能够比较，人们又定义了差异系数

差异系数=（标准差/平均分）×100%

中学理科的差异系数一般在45%左右，中学文科一般在30%左右。

3、存在问题

（1）通过学生考试的分数，可以发现教师教学中存在的一些问题。

从教学方法、对待教学工作的责任感以及驾驭教材的能力等方面去思考。

是教师钻研教材不深，驾驭教材能力不强，还是教学方法不当或者是工作态度不好，责任感欠强等等。

（2）从卷面情况看，可以看出学生答题的具体问题。

是学生智力因素引起的还是非智力因素引起的，是基础知识掌握不牢还是学习方法不当，发现学生出现的知识错误及引起错误的原因。

试卷上普遍存在的问题是什么，个别问题又出现在哪些个别学生身上，是概念法则不清，还是计算能力薄弱，是单项知识没掌握，还是综合运用所学知识解决实际问题的能力不强。

（3）教师做问题分析不能局限于一次考试中发现的问题，也不能仅根据卷面情况就做出判断，应根据教学经验，联系学生近期学习、生活表现情况，充分挖掘分析，才能比较准确地找到学生学习和教师教学中存在的问题。

4、教学改进计划

　　要针对试卷中发现的问题，提出改进措施。

改进措施要细化、具体。

根据找出的错误及引起错误的原因提出改进的措施。

教师心中要有数。

是思想方面的问题就订出思想教育方面的改进措施，是学习习惯方面的问题就订出培养良好学习习惯的措施，若后进生多就提出如何转化后进生的措施。