用Excel电子表格实现学生成绩统计.docx

用Excel电子表格实现学生成绩统计.docx

《用Excel电子表格实现学生成绩统计.docx》由会员分享，可在线阅读，更多相关《用Excel电子表格实现学生成绩统计.docx（24页珍藏版）》请在冰豆网上搜索。

用Excel电子表格实现学生成绩统计

用Excel电子表格实现学生成绩统计

统计学生成绩是教导主任和教师每学期都必须做的常规工作。

传统的做法是：

教师在成绩表上填写学生的平时成绩、考试成绩，然后用计算器按百分比计算每个学生的总评成绩，计算科目总分和平均分，有的还需要列出名次，不但量大，而且容易出错，反复验算是常事。

如果用电脑完成，则方便快捷准确得多，而且无需“重算一遍”。

Excel是美国微软公司推出的基于Windows操作系统的电子表格软件，是目前被广泛使用的现代办公软件之一，本文举例使用的版本是MicrosoftOfficeExcel2003。

1.如何制作成绩统计表

[1.1]输入列标题

开机启动Windows后进入Excel电子表格窗口，在顶端行依次输入表格的列标题：

考号、姓名、语文、数学、英语、总分、平均分、名次等。

[1.2]输入原始数据

考号、姓名、语文、数学、英语的内容属于原始数据，也要依次输入。

考号的输入可采用“自动填充”方法。

先在1、2单元格输入预先设计好的考号后，再选取1、2号单元格后鼠标移动至选定区域的右下角，当出现“+”后往下拖动，所有的考号会自动出现。

[1.3]数据居中对齐

拖动鼠标选定制表区，单击“格式”，再单击“单元格”，再单击“对齐”，水平和垂直对齐均选“居中”，然后单击“确定”。

[1.4]加表格框线

选取需加边框的单元格，单击菜单栏中的“格式”，选择“单元格”，在对话框中选择“边框线”标签，从边框线型栏内，选择较粗的线形加入到“外框”标志栏内，选择较细的线型分别加入到上、下、左、右栏内，按下“确定”。

另一种方法是选择表格后，单击工具栏上“边框”按钮在弹出的选项中选择“所有框线”则可。

至此，除需计算部分外全部输入完成。

2.如何计算总分

[2.1]计算考号1的总分

表格都是由行和列组成的，行由数字表示，列用字母表示。

当活动单元格在F4位置时键入“＝”然后输入SUM（C4:

E4）（表示从C4单元格加到E4单元格），再回车，即得总分。

另一种方法是选择C4到E4这三个单元格后单击工具栏上“自动求和”按钮。

[2.2]其他总分的得出

用鼠标选取已算出的学号1的总分，当出现“+”符号后往下拖动鼠标，所有其他的总评分会自动出现。

3.如何计算学生平均分

[3.1]学生1的平均分：

活动单元格放在需要填写平均分的位置，输入“＝F4/3”（用总分除以科目3）回车，即算出平均分。

[3．2]其他平均分的得出：

用鼠标选取已算出的学生1的平均分，当出现“+”符号后往下拖动鼠标，所有其他的平均分会自动出现。

4.如何排列名次

[4．1]利用菜单排序：

排列名次，首先按总分排序。

从列标题那行一直选取到最后（本表是从第3行到55行），单击菜单“数据”，再单击“排序”，在主要关键字下拉列表中选择“总分”，再选“降序”，按下“确定”键，分数即由高至低排列。

这时再用“自动填充”方法将名次的号（数字）填上。

缺点是并列的名次没有显示出来。

然后再选全表，单击“数据”再单击“排序”，在下拉列表中选主要关键字“考号”，再选“升序”，最后“确定”，即出现按考号排列的名次格式。

[4．2]利用RANK函数排序：

另外一种方法是利用RANK函数进行排名，解决了以上统计方法无并列名次的缺点。

RANK（X，Y，Z）函数，其中有三个参数，第一个参数X为某个学生的总分所在单元格，第二个参数Y为整个班级总分所在的区域，第三个参数Z是可选的，表示统计方式，若省写或写0，则成绩高的名次靠前，一般都使用这种方式，如果写1，则成绩高的名次靠后，这种情况一般较少用。

为了在保持学号顺序不变的前提下进行学生成绩名次排定，可以在F4单元格中输入公式：

=RANK（F4,$F$4:

$F$55,0）

然后，利用自动填充柄将其复制到下方的单元格，统计出所有学生的名次。

注意，这里$F$4:

$F$55用的是绝对地址，是为了保证公式在复制时此处不变，因为作为第二个参数，这里都是指整个班级总分所在的区域，这个区域是相同的。

再按名次排序，可以看出并列的名次也统计出来了。

（见下图）

5.进行科目统计

[5．1]计算科目总分

选取所要求和的“总分”一栏的数据，单击“Σ”按钮，或者是在其中输入公式“＝SUM（C4：

C55）”后回车即算出科目总分。

然后，利用自动填充柄将其复制到右方的单元格，数学、英语总分即可得出。

[5．2]计算科目平均分：

方法同上，只是函数不一样

在要填入语文平均分的单元格内输入公式：

“=AVERAGE（C4:

C55）”即得出语文平均分，然后，利用自动填充柄将其复制到右方的单元格，数学、英语平均分即可得出。

[5．3]统计科目及格人数：

通常，统计不同分数段最好的方法是利用COUNTIF（X，Y）函数。

其中有两个参数，第一个参数X为统计的范围，一般最好用绝对引用;第二个参数Y为统计条件，要加引号。

对于大于等于60分的人数只要用一个COUNTIF（）函数，如在C59单元格中输入公式：

=COUNTIF（$C$4:

$C$55,">=60"）

回车即可得出。

其它两科复制公式后，把统计范围即其中绝对地址分别改为$D$4:

$D$55和$E$4:

$E$55，数学和英语科目及格人数也统计完成。

[5．4]计算单科及格率：

单科及格率等于及格人数除以参考人数。

语文科目已在C59单元格统计出及格人数，参考人数在列标题上方E2单元格内填入，因此在统计及格率C60单元格内写入公式：

=C59/E2回车即得出。

单元格格式可设为“百分比”保留2位小数。

其它两科同样的方法可算出。

[5．5]其它分数段的统计

方法同统计及格人数，优生人数的统计只需把统计条件改为“>=80”差生人数的统计把统计条件改为“<40”。

如果要统计80——89、70——79与60——69分数段的人数，需要用两个COUNTIF（）函数相减。

如统计80——90分数段人数用公式：

=COUNTIF（$C$4:

$C$55,"<90"）-COUNTIF（$C$4:

$C$55,"<80”）

即用小于90的人数减去小于80的人数。

其它分数段统计方法相同只要把统计条件改一下。

[5．6]统计科目最高分和最低分：

利用函数MAX、MIN分别统计最大值和最小值。

如统计语文科目中最高分在C65单元格内输入公式：

＝MAX（C4:

C55）回车即得到87.00。

最低分在C66单元格内输入公式：

=MIN（C4:

C55）回车即可。

[5．7]将百分制转换成不同的等级分：

将百分制转换成不同的等级分有多种不同的划分方法，1是将百分制的分数转换成A（90——100）、B（80——89）、C（70——79）、D（60——69）与E（低于60）五个等；2是将百分制的分数转换成优（90——100）、良（75——89）、中（60——74）与不及格（低于60）四个等级。

具体使用哪种等级划分方法可根据实际情况自己确定。

　　在百分制转换成不同的等级分时，一般使用IF（X，Y，Z）函数。

其中有三个参数，第一个参数X为条件，不能加引号；第二个参数为条件成立时的结果，如果是显示某个值，则要加引号；第三个参数为条件不成立时的结果，如果是显示某个值，同样要加引号。

该函数可以嵌套，即在第二个或第三个参数处可以再写一个IF函数。

为了得到第一种方法所要的等级结果，如语文科目可以在D4单元格中输入公式：

=IF（C4>=90,"A",IF（C4>=80,"B",IF（C4>=70,"C",IF（C4>=60,"D","E"））））然后，利用自动填充柄将其复制到下方的所需单元格。

为了得到第二种方法所要的等级结果，语文科目可以在E4单元格中输入公式：

=IF（C4>=90,"优",IF（C4>=75,"良",IF（C4>=60,"中","不及格"）））

然后，利用自动填充柄将其复制到下方的所需单元格。

[5．8]使不及格的分数以红色显示

统计学生成绩时经常需要将不及格的分数用红色显示，其结果如上图中红色显示部分。

使不及格的分数以红色显示需要使用“格式”菜单中的“条件格式”命令。

该命令会弹出一个对话框，其中要求确认条件与相应的格式。

对于“语文”列，可先选中C4：

C55，然后使用“格式”菜单中的“条件格式”命令，在弹出的对话框中，左边使用默认的“单元格数值”，中间选“小于”，右边填写60，然后单击右边的“格式”按钮，从中选择红色，最后单击两次“确定”按钮。

对于“等级一”列，可先选中D4：

D55，然后使用“格式”菜单中的“条件格式”命令，在弹出的对话框中，左边使用默认的“单元格数值”，中间选“等于”，右边填写E，然后单击右边的“格式”按钮，从中选择红色，最后单击两次“确定”按钮。

“等级二”列类似。

6、结束语

Excel电子表格功能强大，界面直观易学易用，可以轻松地完成学科的总分、平均分的计算；利用RANK函数排序，可以在学生顺序不变的前提下进行学生成绩名次排定，并且可以显示出名次并列的情况，一目了然；COUNTIF（X，Y）函数是统计不同分数段人数的一种好方法；使用IF（X，Y，Z）函数能轻松将百分制转换成不同的等级分；用“条件格式”命令可以用特殊的颜色显示符合条件的一组分数段或某等级；总之，用Excel统计学生成绩，将会大大提高教师的工作效率和工作质量，让我们的教学工作变得更轻松。

参考文献：

[1]周天红.《如何用Excel电子表格统计学生成绩》[J].教师论坛，1997,（11）:

78-79

[2]王冰.用EXCEL进行学生成绩管理小技巧.[EB/OL].eNet论坛，2004-3-15

[3]姬颖玉.Excel统计学生成绩时的四个难题及解决.[EB/OL].兴庆区教育局信息中心，2021-3-27

用Excel电子表格为学生成绩排名次

2021-01-1412:

56:

22来源：

遵义五中浏览：

2424次

　　RANK函数是Excel计算序数的主要工具，它的语法为：

RANK（number，ref，order），其中number为参与计算的数字或含有数字的单元格，ref是对参与计算的数字单元格区域的绝对引用，order是用来说明排序方式的数字（如果order为零或省略，则以降序方式给出结果，反之按升序方式）。

格式：

=RANK（Ａ1，$Ａ$１：

$Ａ$n）

　　其中：

“A1”表示成绩所在列的第一行，“$Ａ$１：

$Ａ$n”表示成绩的第一行到最后行

例：

为下表所示成绩排名次

操作步骤

　一、选中Ｂ１单元格，并在其上输入函数，输入后点“勾”，（如下图所示）。

其中成绩所在的行是A1至A9　　函数式应为=RANK（Ａ1，$Ａ$１：

$Ａ$９）

二、将鼠标放到红线所指的小方点上按下左键往下拖到最后一行即可排出名次，如下图所示。

三、再按Ｂ列排序即得所要的排名次，如下图所示。

邮件合并一步一步学：

软件OFFICE2003，EXCEL2003

1、在excel2003中建一个数据表（联系表.xls）如：

姓名，身份证号，其它编号，职业，联系方式，输入相应数据，保存；

如想在一张页面中打印多个人的信息，就在excel2003多排几列了！

2、打开一个空白word文档（联系表.doc），点击工具-信函与邮件-邮件合并，这时在你的右面就会出现一个任务窗格；

3、在窗格中选择信函，然后点击下一步：

正在启动文档；

4、选择使用当前文档，点击下一步：

选取收件人；

5、选择使用现有列表，点击浏览，选取”联系表.xls”然后在弹出的窗口中选择你建表的工作薄（一般都是sheet1$）。

确定，再确定，点出下一步：

撰写信函；

6、在你的word文件中建好你想把数据插入后的格式；

想在一张页面中打印多个人的信息，就用文本框做好，一张页面多排几张文本框而已。

7、点击右面的项目的中其它项目（当然你也可选择插入地址块、问候语），你就会看到姓名、姓名1、姓名2等等，把你想要的项目插入到对应的位置即可。

8、在工具栏上点右键，点击邮件合并，在弹出的邮件合并工具栏中点击ABC，就会在域与你的实际项目中切换，工具栏上其它键自己试好了，懂中文一看就知！

利用VBA实现Excel电子表格（工资报表）自动分页统计

王志华

摘要：

利用VBA编程，在Excel中启用宏命令，实现工资报表自动分页统计，方便工资统计和查找报表错误，减少统计误差，提高工作效率。

关键词：

VBAExcel工资报表分页统计

一、问题的提出:

随着Excel制作的电子报表越来越多，应用越来越广泛，常常遇到对其项目进行分页统计的问题，尤其是在工资报表系统中，在手动对工资项目进行分页统计时，如果出现人员增加、减少或人员调动产生的变换位置的情况，就得对动辄几十、上百页的报表重新对每页手动设置公式进行分页合计，再最后汇总，给报表的制作带来极大不便，增加了很大的工作量，降低了工作效率。

如果利用MicrosoftVisualBasicProject即VBA编辑宏命令，对报表进行自动分页、每页自动合计和最后总合计，将大大地减少工作强度，提高统计工作的效率和准确性。

二、问题的解决（功能的实现）:

1、打开Excel电子表格应用软件。

2、点击Excel窗口菜单，从下拉菜单中点击取消隐藏菜单项，弹出对话框如图：

3、然后点击确定，Excel自动切换到MicrosoftExcel–PERSONAL编辑中，如图所示：

4、点击Excel菜单栏里的工具菜单，点面下来菜单里的宏选项里的录制宏命令弹出录制新宏对话框，如图所示：

5、点击保存在的下来箭头，选择个人宏工作簿，然后点击确认，开始录制事先编辑好的自动分页汇总和删除分页统计的宏。

6、按Alt+F8键，弹出启动宏命令对话框，如图所示：

7、点击新建宏命令对话框中的编辑按钮，弹出如下对话框：

8、在模块中输入或编辑、调试事先编辑好的宏命令，如图所示：

9、具体自动分页汇总和、删除分页汇总宏命令如下：

Dimi,h,hh,t,l,x,rr,dr,tt,ls,cs,lleft,lrightAsInteger

DimrrrAsString

DimrCurrentCellAsRange'每一页之分页小计所在单元格

Dimr1stSubCellAsRange'小计区域第一个单元格

PublicSub自动分页汇总（）

Cells（1,1）.Select

OnErrorResumeNext

t=2

Do

i=InputBox（"默认为10，不能超过一页的范围！

！

！

","请输入每页拟打印的行数",10）

Ifi<=0Ori=""Then

MsgBox（"每页行数必须大于1！

"）

Else

ExitDo

EndIf

Loop

i=Int（i）

h=InputBox（"起始行数，默认为5","请输入起始行数",5）

x=i+h

lleft=InputBox（"起始列数，默认为2列","请输入起始列",2）

lright=InputBox（"最终列数，默认为倒数第0列","请输入最终列",0）

l=Range（"A65536"）.End（xlUp）.Row'本示例选定包含单元格B4的区域中B列顶端的单元格。

Range（"B4"）.End（xlUp）.Select

'ForRowCount=1ToSelection.Rows.Count'循环选择的每一行。

DoWhilel>=x

Rows（x+1）.InsertShift:

=xlDown'在当前工作表中Rows（x+1）行插入空隔行

Forcolumncount=lleftToSelection.Columns.Count-lright'循环选择的每一列。

Range（Cells（x+1,1）,Cells（x+1,lleft-1））.Merge'合并单元格

Cells（x+1,1）="本页合计"

Cells（x+1,columncount）.Formula="=SUM（R[-"+CStr（i）+"]C:

R[-1]C）"

WithActiveSheet.Range（Cells（x+1,1）,Cells（x+1,Selection.Columns.Count））.Borders'边框设置

.Line=xlBorderLine

.Weight=xlMedium'xlThin细线'xlThick粗线

.ColorIndex=3

EndWith

WithActiveSheet.Range（Cells（x+1,1）,Cells（x+1,Selection.Columns.Count））.Font'字体设置

'.Size=14

.Bold=True

'.Italic=True

.ColorIndex=3

EndWith

WithActiveSheet.Range（Cells（x+1,1）,Cells（x+1,Selection.Columns.Count））.Interior'设置单元格底色

'.ColorIndex=8'为青色

EndWith

Nextcolumncount

ActiveWindow.SelectedSheets.HPageBreaks.AddBefore:

=Rows（x+2）'在当前工作表中Rows（x+2）行插入分隔符

x=（i+1）*t

x=x+h-1

t=t+1

l=l+1

Loop

rr=lMod（i+1）

Rows（l+1）.InsertShift:

=xlDown

SelectCaserr

Caseh+1Toi

hh=2

rr=rr-h

rrr=CStr（（rr））

Forcolumncount=lleftToSelection.Columns.Count-lright'循环选择的每一列。

Range（Cells（l+1,1）,Cells（l+1,lleft-1））.Merge'合并单元格

Cells（l+1,1）="本页合计"

Cells（l+1,columncount）.Formula="=SUM（R[-"+CStr（rrr）+"]C:

R[-1]C）"

WithActiveSheet.Range（Cells（l+1,1）,Cells（l+1,Selection.Columns.Count））.Borders'边框设置

.Line=xlBorderLine

.Weight=xlMedium'xlThin细线'xlThick粗线

.ColorIndex=3

EndWith

WithActiveSheet.Range（Cells（l+1,1）,Cells（l+1,Selection.Columns.Count））.Font'字体设置

'.Size=14

.Bold=True

'.Italic=True

.ColorIndex=3

EndWith

WithActiveSheet.Range（Cells（x+1,1）,Cells（x+1,Selection.Columns.Count））.Interior'设置单元格底色

'.ColorIndex=8'为青色

EndWith

Nextcolumncount

Caseh

hh=1

Case0Toh-1

hh=2

rr=rr+i-h+1

rrr=CStr（（rr））

Forcolumncount=lleftToSelection.Columns.Count-lright'循环选择的每一列。

Range（Cells（l+1,1）,Cells（l+1,lleft-1））.Merge'合并单元格

Cells（l+1,1）="本页合计"

Cells（l+1,columncount）.Formula="=SUM（R[-"+CStr（rrr）+"]C:

R[-1]C）"

WithActiveSheet.Range（Cells（l+1,1）,Cells（l+1,Selection.Columns.Count））.Borders'边框设置

.Line=xlBorderLine

.Weight=xlMedium'xlThin细线'xlThick粗线

.ColorIndex=3

EndWith

WithActiveSheet.Range（Cells（l+1,1）,Cells（l+1,Selection.Columns.Count））.Font'字体设置

'.Size=14

.Bold=True

'.Italic=True

.ColorIndex=3

EndWith

WithActiveSheet.Range（Cells（x+1,1）,Cells（x+1,Selection.Columns.Count））.Interior'设置单元格底色

'.ColorIndex=8'为青色

EndWith

Nextcolumncount

EndSelect

Rows（l+hh）.InsertShift:

=xlDown

Forcolumncount=lleftToSelection.Columns.Count-lright'循环选择的每一列。

Range（Cells（l+hh,1）,Cells（l+hh,lleft-1））.Merge'合并单元格

Cells（l+hh,1）="总合计"

Cells（l+hh,columncount）.Formula="=SUM（R[-"+CStr（l-h+1）+"]C:

R[-1]C）/2"

WithActiveSheet.Range（Cells（l+hh,1）,Cells（l+hh,Selection.Columns.Count））.Borders'边框设置

.Line=xlBorderLine

.Weight=xlMedium'xlThin细线'xlThick粗线

.ColorIndex=3'3红色、4绿色

EndWith

WithActiveSheet.Range（Cells（l+hh,1）,Cells（l+hh,Selection.Columns.Count））.Font'字体设置

'.Size=14

.Bold=True

'.Italic=True

.ColorIndex=3

EndWith

WithActiveSheet.Range（Cells