第6讲Excel成绩统计分析表

用excel设计成绩分析表下面就以语文科分析表设计为例进行描述，其他科的分析相似操作。

1.设计分析表如图2所示.将A1：R1 合并并居中，输入“** 中学2007～2008 学年度第一学期高一月考成绩分析表”。

用同样的方法将A2：I2 合并并居中，输入“科目：语文”其他文字按表格内容输入即可。

然后从A5至A14依次输入1,2,3……10班级名.2.求语文科各班参考人数我们在这里可采用COUNTIF函数，COUNTIF 函数用于计算给定区域内满足特定条件的单元格的数目，所以在B5单元格中输入“=COUNTIF(成绩表!$C$3: 成绩表!$C$676,A5)”，即可求出1班的参考人数。

然后选中B5 单元格，拖动B5 单元格右下角的小“+”字图标至B14 单元格(复制公式)，这样，就能自动得到其他各班的参考人数。

最后在B15 单元格中输入“=SUM(B5:B14)”，用于分析全年级参考总人数。

3.求各班及格人数我们在这里可采用数组公式进行多重标准单元计算。

首先在D5 单元格中输入“=SUM(if((成绩表!$C$3: 成绩表!$C$676=A5)*( 成绩!$F$3: 成绩表!$F$676,>=90),1,0))”，按下SHIFT＋CTRL键敲回车，Excel 会自动在公式两侧加上大括号,生成数组公式。

然后对D5至D14单元格进行向下自动填充操作。

最后在D15 中输入“=SUM(D5:D14)”，求出全级及格人数。

公式中的1表示当条件为真时返回结果计为1个,即及格1人,否则为0;4.求各班及格率我们可在E5 单元格中输入“=IF(B5=0,0,D5/B54)*100”，为避免分母为0的错误，用了IF 函数。

公式的作用为：若1班参考人数不为0，则及格率为及格人数除以参考人数后乘以100。

然后对E5 至E14 单元格进行向下自动填充操作。

然后在E15 中输入Average(E5:E14),算出全年级的平均合格率。

Excel countif函数统计分析学生成绩表今天我们学习如何用countif函数来统计学生成绩情况。

下面我们先建立好一张学生成绩表格，并且要统计出学生成绩及格的人数，高于平均分的人数，90分以上人数，80-90分之间的人数。

打开百度APP看高清图片学生成绩统计表countif函数一共有两个参数，第一个表示的就是从哪里找，第二个参数就是找什么，也就是条件。

所以求及格人数也就是成绩大于60分的人，所以整个函数公式为“COUNTIF(B2:B12,">=60")”，注意的就是这里的符号都必须是英文状态下的符号。

countif函数求及格人数接着我们先来计算大于90分的人数，这就比较容易了，和及格人数一样，只需要把条件换算成大于90即可，整个函数公式为“=COUNTIF(B2:B12,">90")”。

如果我们要计算大于80分的人数呢？聪明的你一定也知道公式为“=COUNTIF(B2:B12,">80")”了吧！只需要简单的更换下条件即可。

下面我们来看高于平均分的人数，这里我们先来求出平均分，求平均分我们需要用到average这个函数，具体公式为”=AVERAGE(B2:B12)“，具体的使用方法，后续再讲，这里只需要知道这个简单的公式就算出了平均分。

AVERAGE计算出学生平均分通过公式得出学生的平均成绩是78.7分。

AVERAGE计算出学生平均分然后我们把这两个函数组合起来，组合的公式为”=COUNTIF(B2:B12,">"&AVERAGE(B2:B12))“，注意中间用&符号连接了条件。

countif函数统计高于平均分人数按下回车后得出了高于平均分的人数，如果这里要计算得大于并等于平均分人数，公式应该如何修改呢？大家自己想想！最后我们来统计80-90分区间的人数，这里需要用sum和countif两个函数组合，公式为”=SUM(COUNTIF(B2:B12,">="&{80,90})*{1,-1})“，这里不做详细的解释，说多了，也记不住。

利用Excel进行学生成绩统计分析案例背景期末考试后，除了要统计各个分数段人数分布情况外可能还要统计年级和各班的及格人数与及格率，优秀人数与优秀率，以及各班里前、后若干名（如年级前20名和后20名）的人数和比例等。

本案例以某中学出一年级4个教学班100名学生的期末考试的3科成绩和总分为样本，统计全年级4个班级学生的期末考试及格人数与及格率，优秀人数与优秀率。

1创建原始数据表Step 1创建工作簿、重名工作表创建工作簿“综合统计表.xls”，然后将工作表重命名为“统计表”，删除多余工作表。

Step2输入原始成绩①在单元格A49输入表格标题：“高一年级期末考试成绩统计表”，选择你好单元格区域A49:G49，单击“合并及居中”按钮，字体、字型、字号设置为黑体加粗16号。

②在单元格区域A50：G50输入字段名：“班级”，“学号”，“姓名”，“语文”，“数学“，“英语”和“总分“。

③在单元格区域A51：F150输入全年级4个班100名学生的“语文”，“数学“，“英语”成绩④在单元格G51输入公式：“=SUM(D51:F51) ”，然后按键确认，计算出第一个学生的“总分”。

⑤选中单元格G51，双击单元格G51右下角的填充柄即可完成其余学生“总分”的统计工作。

⑥选中单元格区域A50：G150，按图2样式设置边框，添加底纹，文本居中显示。

2统计及格人数与及格率Step 1创建统计表框架在单元格区域A1:J9按图3样式输入“高一年级各班及格人数与及格率统计表”框架信息。

Step2输入统计分数线①选中单元格区域B3:D3，输入“60”，按组合键确认。

②在单元格E3中输入“180”。

Step3统计及格人数①在单元格B4输入如下公式，然后按组合键确认。

“=SUM(ISNUMBER(D$51:D$150)*(D$51:D$150>=B3))”②在单元格B5输入如下公式，按组合键确认。

“=SUM((($A$51:$A$150=1))*(D$51:D$150>=B$3))”③在单元格区域B6：B8分别输入如下公式，按组合键确认。

原文地址：成效图如下：表一：文科成绩表二：理科成绩表三：文科成绩统计分析表四：理科成绩统计分析同一个文件中有四个工作表：“文科”“理科”“文科统计”“理科统计”其中成绩表中包括有：总分，年级名次（相同的分数相同的名次），班级名次（在不用分班的情形下统计班级名次），单科年级名次，与上一次考试的对照（是进不了仍是退步了）统计表中包括有：每一个班各个分数段的人数，年级各个分数段的人数，年级累计人数，每一个班年级前多少名的人数关于咱们教师来讲，如此的一张表足能够知足咱们分析成绩的需要，若是每次考试都统计一次，工作量就会增加很多，而且是无效的重复劳动，为此在下用了四个小时的时刻制作了如此的一张模板表，每次咱们只需要把单科的成绩复制进来，就会自动求出总分，排出单科的名次，年级名次，班级名次和与上一次的对照情形，还有各个分数段的人数，能够为咱们省去很多没必要要的麻烦，先将制作方式说明如下，有爱好的朋友咱们一路探讨，设计出更好的模板。

1、求总分那个大伙儿都会，就只是量说明了，直接用SUM函数就好了2、如何排出年级名次，要求同一个分数有相同的名次，如成效图方式：在G2单元格中输入=RANK(F2,F:F)就可以够够了，RANK是排序函数，参数的意义是依照F列的数据排序3、如安在不分班的情形下，在不改变年级名次的情形下，排出班级名次？如上图方式：在H2单元格个输入=SUMPRODUCT(($E$2:$E$1000=E2)*(F$2:$F$1000>F2))+14、如何统计各个班在每一个分数段的人数？成效如表三、表四方式：在B2中输入=SUMPRODUCT((文科!E$2:文科!E$1000=A2)*(文科!F$2:文科!F$1000>=600))在B3中输入=SUMPRODUCT((文科!E$2:文科!E$1000=A2)*(文科!F$2:文科!F$1000<<fontcolor="#FF0000">600)*(文科!F$2:文科!F$1000>=590))直接往后面复制，改变红色数字就可统计出各个分数段的人数理科统计表不用再逐个输入了，直接复制文科统计表，然后“编辑——替换”就好了5、如安在统计表中显现文科（理科）成绩表中的最高分，及对应的名字和班级？统计文科(理科)成绩表中的最高分A2=MAX(INDIRECT("文科!"&CHAR(64+ROW(C5))&2):INDIRECT("文科!"&CHAR(64+ROW(C5))&1000))CHAR（）返回单元格中的字符ROW( ) 返回单元格所在的行“64+row（）”的含义是，65对应A，66对应B，……比如上面表一中总分在D列，因此确实是64+ROW(C5)调出姓名=LOOKUP(2,1/((文科!D$2:D$1000=MAX((文科!D$2:D$1000=A2)*文科!D$2:D$1000))),文科!B$2:B$1000)A2是刚返回的最高分D列是分数B列是姓名调出班级=LOOKUP(2,1/((文科!D$2:D$1000=MAX((文科!D$2:D$1000=A2)*文科!D$2:D$1000))),文科!C$2:C$1000) C列是班级6、如何统计各个班在年级前多少名的人数？方式：在B19中输入=SUMPRODUCT((文科!E$2:文科!E$1000=A19)*(文科!G$2:文科!G$1000<=50)) 在C19中输入=SUMPRODUCT((文科!E$2:文科!E$1000=A19)*(文科!G$2:文科!G$1000<=100))后面的就不用说了吧7、单元格里面都好多零好难看又阻碍看数据，如何隐藏这些0？方式：“工具——选项——视图——零值前面的钩去掉就好了”成效如下：好了，一张适用的成绩分析表就做好了，以后拿了直接用就OK了，凌晨一点了，睡觉去……。

用Excel表作班级成绩分析(每次考试，我们都要对考试情况作分析，所分析的项目包括有参考班级，参考人数、平均分，合格人数、合格率、优秀人数、优秀率，最高分、最低分以及各分数段所占的人数：100分人数、80～99 分人数、60～79 人数、30～59 人数、30 分以下人数。

这个统计比较复杂，且工作量大，如果用人工计算，不但效率低，且出错机率大，而用EXCEL函数可以很好解决这个问题。

我们可采用以下方法：新建空白E xcel 工作簿，将自动建立的Sheet1、Sheet2工作表重命名为成绩分析表、各科考试成绩。

其中各科考试成绩工作表用于输入各科成绩。

(一)设计各科成绩工作表在各科考试成绩工作表第一行中输入分析名称，如“** 中学2004～2005 学年上期各科成绩分析表”，在第二行从A1单元格开始顺序输入记录字段名：班级、学号、姓名、政治、计算机、数学、英语、操作基础、电子技术。

假设全级学生有695 人，所以从第3行到第698 行用来输入所有学生各科的成绩。

(二)设计成绩分析工作表下面就以计算机科分析表设计为例进行描述，其他科的分析相似操作。

1.设计分析表将A1：I1合并并居中，输入“** 中学2004～2005 学年第一学期成绩分析表”。

用同样的方法将A2：I2 合并并居中，输入“科目：计算机”其他文字按表格内容输入即可。

2.求科各班参考人数我们在这里可采用COUNTIF函数，COUNTIF 函数用于计算给定区域内满足特定条件的单元格的数目，所以在C4单元格中输入“=COUNTIF(各科考试成绩!$A$3: 各科考试成绩!$A$698,"=A4”)”，即可求出107班的参考人数。

然后选中C4单元格，拖动C4 单元格右下角的小“十”字图标至C7 单元格(复制公式)，这样，就能自动得到C4至C7 单元格的公式。

最后在C8 单元格中输入“=SUM(C4:C7)”，用于分析全年级参考总人数。

3.求科各班总分我们在这里可采用SUMIF 函数，SUMIF 函数功能是根据指定条件对若干单元格求和，在D 4 单元格中输入“=SUMIF(各科考试成绩!$A$3: 各科考试成绩!$A$698,"= A4", 各科考试成绩!$H$3:各科考试成绩!$H$698) ”，该单元格公式作用为：计算107 班计算机科总分。

如何用Excel快速统计成绩和分析成绩分布情况本文中我们介绍如何用Excel快速统计成绩和分析成绩分布情况的方法和步骤。

准备数据输入必要的数据，工作表命名为“总表”，以D列的政治分数(满分100)为例，在单元格E1～I1分别输入60以下(差)、60分及以上(及格)、60-79(中等)、80-89(良好)、90分以上(优秀)。

然后在单元格E2～I2分别输入下面的公式：E2：=IF(D2<60，1，0)、F2：=IF(D2>=60，1，0)、G2：=IF(AND(D2>=60，D2<=79)，1，0)、H2：=IF(AND(D2>=80，D2<=89)，1，0)、I2：=IF(D2>=90，1，0)，最后选择单元格区域E2～I2，将其向下填充到区域E264～I264。

计算基本数据所谓基本数据，就是实考人数、最高分、最低分和平均分(以政治课为例)。

单击数据区域的单元格，选择菜单“数据”→“数据透视表和数据透视图”，弹出“数据透视表和数据透视图向导-3步骤之1”对话框，一路点击“下一步”即可创建一张空白的数据透视表，将工作表命名为“基础分析”。

单击空表区域内的任意位置，显示“数据透视表字段列表”对话框，将“班级”字段拖入“将行字段拖到此处”中，将“政治”字段拖入“请将数据字段拖到此处”中(图1)。

在B4单元格“求和项：政治”处单击鼠标右键，在弹出的菜单中选择“字段设置”，在弹出的“数据透视表字段”对话框中，选择汇总方式为“计数”。

再把“求和项:政治2”的汇总方式选择为“最大值”，“求和项:政治3”的汇总方式选择为“最小值”，“求和项:政治4”的汇总方式选择为“平均值”。

最后，把“数据”字段拖到“汇总”字段处即可(图2)。

统计成绩分布情况从30分～100分，每隔5分统计各班各分数段人数(以政治课为例)。

再创建一张空白数据透视表，命名为“分数段“，将“班级”字段拖入“将列字段拖到此处”中，“政治”字段拖入“将行字段拖到此处”中，“政治”字段拖入“请将数据字段拖到此处”中。

excel 动态成绩统计表的制作方法如下：1.打开Excel并创建工作表:1.打开Microsoft Excel应用程序。

2.创建一个新的工作簿或打开现有的工作簿。

2.设置表格框架:1.在第一行中，输入表的标题，如“学生成绩统计”。

2.在第二行，列出您的列标题，如“学生姓名”、“课程名称”、“成绩”等。

3.输入数据:1.在第三行及以下，输入学生的姓名、课程名称和对应的成绩。

4.使用公式进行动态统计:1.对于每个需要统计的项（例如平均分、最高分、最低分等），在相应的单元格中使用Excel公式。

例如：1.平均分：使用=A VERAGE(范围)公式。

例如，如果您要计算B列的平均分，则输入=A VERAGE(B:B)。

2.最高分：使用=MAX(范围)公式。

例如，要查找C列的最高分，输入=MAX(C:C)。

3.最低分：使用=MIN(范围)公式。

例如，要查找D列的最低分，输入=MIN(D:D)。

5.创建图表:1.选择您想要可视化的数据范围。

2.点击“插入”选项卡，选择“图表”并选择适合您数据的图表类型（例如柱形图、线形图等）。

6.保护工作表:1.如果您不希望其他用户更改已填写的数据或计算结果，您可以保护工作表。

选择“审阅”选项卡，然后选择“保护工作表”。

7.保存工作簿:1.在完成所有操作后，别忘了保存您的工作簿。

点击“文件”>“保存”，并确保您保存在合适的位置。

8.共享工作簿（如果需要）:•如果您与其他人合作，或希望将工作簿共享给他人，可以选择“文件”>“共享”来与其他人共享工作簿或通过电子邮件发送工作簿。

9.自定义和美化表格:•根据需要自定义表格格式，例如更改字体、颜色和边框样式等。

10.定期更新数据:•如果学生的成绩或其他信息发生变化，请随时更新Excel工作簿以保持数据最新。

利用Excel制作功能完备的学生成绩统计通用模板以下是我二年前在初中部和高中部上信息课时做的一些尝试，现在看来很不成熟，但毕竟我当时还是很用心的在做这件事。

希望能激励本人继续努力，也希望能得到大家的批评指正！众所周知，Excel是目前功能最强大的电子表格软件之一。

它的表格生成功能及数据处理能力一直是受人们称道的。

作为老师，在每次考试后，最麻烦的事莫过于统计和分析学生成绩了。

利用Excel的强大的函数功能和各种自动生成功能制作一个学生成绩统计通用模板，可以很好的解决老师们这个烦恼。

一个功能较完备的学生成绩统计模板至少应具备以下功能：1、可统计学生个人总分、平均分；2、可统计学生个人总分排名、平均分等级；3、可统计本班各科总分、平均分；4、可统计各科应考人数、实考人数；5、可统计各科优秀人数、不及格人数、优秀率、及格率；6、可统计各科最高分、最低分；7、能方便地查看各统计结果；8、能有效地防止无效数据的产生，适合多种情况的成绩统计。

针对以上需求，我们可以利用Excel的相关功能逐个解决：一、建立主体框架这一步要完成下面几件工作：1、确立表头，如“潮阳实验学校初二（1）班学生成绩统计表”。

表头位于整个表的最上端，跨越19列，利用合并居中功能，使之居中并设置好字体字号等。

2、确立列标题。

在第2行前两列输入学号和姓名，从第三列开始，添加语文、数学、英语、政治、历史、地理、物理、化学、生物、音乐、体育、美术、信息等科目的列标题，在科目列标题后添加总分、平均分、等级、名次四列。

这样一来，整个统计表一共有19列，13个科目所在的列从C列到O列。

学生具体信息及成绩的输入从第三行开始。

如果实际应用时用不着一些科目，可以选中相应列，将其隐藏，需要时再取消隐藏即可。

如果是应用于单科多次成绩统计，只要更改列标题即可。

3、输入学生学号及姓名。

输入学生学号时可以应用Excel的自动填充功4、在学号姓名下确立几项行标题。

包括各科总分、平均分、最高分、最低分、优秀人数、不及格人数、优秀率、及格率、应考人数、实考人数等常用统计项目。