Excel做成绩表时自动实现同分同名次
2007-08-24 09:57:18　来源: 天极　网友评论 42 条 进入论坛
在用Excel统计成绩时，我们一般会根据成绩高低进行排序，然后按序列自动填充出名次。这种方法得出的名次与总分没有关联，即使成绩相同，也会填充出不同的名次。
如果数据较少，我们可以采用手动的方法将成绩相同的人员改成相同的名次，但数据较多时就很麻烦了。经过实践，以下三种方法可以自动实现同分同名次的操作。假设有1000名考生，字段位置如上图所示。
一、使用排位函数RANK
在H2单元格中输入函数“=RANK(G2,G$2:G$1001,0)”，回车后H2单元格中会出现名次“1”，然后选定H2，拖动其填充柄向下填充即可自动实现。
该函数的功能是返回一个数字(上述函数中的G2，此处采用相对应用，填充时随行的变化而变化)在数字列表(上述函数中的G$2:G$1001，此处采用绝对应用，填充时不发生变化)中的排位。数字的排位是其大小与列表中其他值的比值。该函数在使用时，即使总分没有排序，它也可以直接求出所对应总分的名次;如果总分已排过序，则数字的排位就是它当前的位置。上述函数中最后的“0”指明排位的方式，当其为 0或省略时，Excel 对数字的排位是按照降序排列的。 如果不为零，excel做成软件录入界面 Excel 对数字的排位则是按照升序排列的。该函数对重复数的排位相同，而下一名次则将前面的重复数计算在内。
二、使用计数函数COUNTIF
在H2单元格中输入函数“=COUNTIF(G$2:G$1001,">"&G2)+1”，然后向下自动填充即可实现该操作。
该函数的功能是计算区域中满足给定条件的单元格个数。上述函数中采用绝对引用指定计算区域G$2:G$1001，名次所对应的单元格G2采用相对引用。即求出在G2:G1001区域中大于名次所对应总分的单元格的个数，然后加上1就可以得出该单元格中数值的名次。该操作不管有几个重复的，大于它的个数却是固定的，所以重复数据的名次也就是相同的了。同RANK函数一样，该函数不管是否已经按总分进行排序均可进行上述操作。
三、采用IF语句加COUNT函数
该操作与上述两种方法不同，必须先将总分按照降序排列。然后将第一名的名次“1”输入，再在H3单元格中输入函数“=IF(G3=G2,H2,COUNT($G$2:G3))”，确定后向下自动填充也可实现同分同名次的操作。
COUNT函数的功能是返回指定区域中数字单元格的个数。因为G列全是数字单元格，所以该总分处在第几位上名次就是几。上述语句的意思就是如果当前总分和上一个总分相同，则输入上一个总分的名次，否则执行COUNT语句，统计个数。
以上三种方法均可自动实现同分同名次操作。本文虽然有孔乙己先生关于“回”字有四种写法的嫌疑，但笔者的目的却只有一个，那就是抛砖引玉，让Excel函数更好的为我们服务。
可以通过以下的操作进行： STEP 1：录入学生的考试成绩，在F3单元格处输入公式“=sum(D3:E3)”，然后拖动填充柄向下填充，便得到了每人的总分。 STEP 2：在G3单元格处输入公式“=average(D3:E3)”，然后拖动填充柄向下填充，便得到了每人的平均分。 S
TEP 3：在H3单元格处输入公式“=RANK (G3,G$3:G$11,0)”，然后拖动填充柄向下填充，即可得到每人在班中的名次。 STEP 4：选中D3:E11，然后执行“格式”菜单下“条件格式”命令，弹出“条件格式”对话框。把条件设为小于95分的用红显示。再单击“确定”按钮。
多事情在没有用到但知道即将用到时就会开始了解。如做成绩表，开家长会以及做一顿像样的饭等。
总会通过多学多问，了解一些捷径再加上自己摸索，就足于应付一些简单的工作，好比这个EXCEL。在当班主任之前，EXCEL在电脑里潜伏了三年；当了班主任之后，就陆陆续续的到网上看一些视频，并向文印室及电脑老手请教。
于昨天终于较顺利了一分成绩分析表。
班主任每次月考总难免得用EXCEL对学生的成绩进行分析：
如何在又快又熟练的使用EXCEL呢？下面对我昨天一的劳动成果做个记录，做个相关的积累，以便下次不会忘记这些相关的方法和公式。
EXCEL有很多比WORD表格方便的地方。
1、排序降序：工具栏—“数据”—“排序”
2、名次（rank函数的使用）：AAA的英语名次为2，点击放置欲放置于名次的单元格，键
入“=rank(G3,G:G)”G3表示所成绩所在单元格的位置，有点儿像直角坐标系中的横纵坐标。G：G表示所在的列都会自动执行这个命令。一会只需向下拖动右下角黑的小加号。整列即可得到相应该“名次”。
这个我一开始最没明白的。
3、很多操作都依赖于“格式”中的“单元格格式”。
⑴加边框：如加上“边框”后，EXCEL表格和WORD中的表格看起来就没什么两样了
(2)合并单元格：在"单元格格式"----"对齐"----"合并单元格"
（3）给数字加括号：如名次外都会加个括号，例（2）表示第二名。
只需在单元格格式——数字——“自定义”中改下数字的表示形式。如下图所示。
会了这些再多加练习就能应付做成绩表的问题了。
每次考试结束之后，最让老师头痛的就是成绩查询了。现在，我们可以利用Excel 2000的系统函数建立一个成绩查询系统，只要您在指定单元格中输入欲查询成绩的学生姓名，他的各科考试成绩和名次就会自动显示出来。非常便利。
  在Excel中，双击“Sheet1”，将其命名为“成绩统计”，然后建立一个如图1所示的成绩统计表，输入全部学生的各科成绩备用。用常规的方法计算出各学生的总分，并排出名次（这里就不详述做法了）。
图1
  双击“Sheet2”，将其命名为“成绩查询”。在“成绩查询”工作表中，建立如图2所示的表格。单击B2单元格，输入欲查询成绩的学生姓名。单击B3单元格，在其中输入“=VLOOKUP($B$2,成绩统计!$B $3:$I$56,2,FALSE)”，按回车键，则可以得到该名学生的语文成绩；单击B4单元格，在其中输入“=VLOOKUP($B$2,成绩统计!$B$3:$I$56,3,FALSE)”，按回车键，得到数学成绩；单击B5单元格，在其中输入“=VLOOKUP($B$2,成绩统计!$B$3:$I$56,4,FALSE)”，按回车键，得到英语成绩；单击B6单元格，在其中输入“=VLOOKUP($B$2,成绩统计!$B$3:$I$56,5,FALSE)”，按回车键，得到机械成绩；单击B7单元格，在其中输入“=VLOOKUP($B$2,成绩统计!$B$3:$I$56,6,FA
LSE)”，按回车键，得到电工成绩；单击B8单元格，在其中输入“=VLOOKUP($B$2,成绩统计!$B$3:$I$56,7,FALSE)”，按回车键，得到总分；单击B9单元格，在其中输入“=VLOOKUP($B$2,成绩统计!$B$3:$I$56,8,FALSE)”，按回车键，得到名次。到此，我们就可以得到该名学生的全部考试成绩以及总分、名次了。
图2
  　每到学期结束时，教师的一项重要工作就是要统计学生的成绩，在电脑逐步普及的今天，我们就把这项繁杂的工作交给Excel去自动完成吧。
本节任务：制作一个学生成绩自动统计表，可以自动统计最高分、最低分、总分、平均分、名次、三率等数据信息，还可以根据自定条件以不同的颜显示分数。自动统计表做好以后还可以保存成模板，以便以后使用。
涉及术语：单元格、工作表、工作薄、引用(相对/绝对)、自动填充、排序、条件格式等。
涉及函数：AVERAGE、COUNTIF 、MAX、MIN、RANK、SUM
任务一：统计最高分、最低分、总分、平均分、名次、三率等数据信息。
1、启动Excel，同时选中A1至L1单元格，按“格式”工具条上的“合并及居中”按钮，将其合并成一个单元格，然后输入统计表的标题“高一（1）班期末成绩统计表”（参见图1）。

2、根据统计表的格式，将有关列标题及相关内容输入到相应的单元格中（参见图1）。
提示：其中学号的输入可通过“填充柄”快速完成。
3、选中K3单元格，输入公式：=SUM(C3:J3)，用于计算第一位学生的总分。
4、选中L3单元格，输入公式：=RANK(K3,$K$3:$K$12)，计算出第一位学生总分成绩的名次（此处，假定共有10位学生）。
5、同时选中K3和L3单元格，将鼠标移至L3单元格右下角的成“细十字”状时（通常称这种状态为“填充柄”状态），按住左键向下拖拉至L12单元格，完成其他学生的总分及名次的统计处理工作。
6、分别选中C16、C17单元格，输入公式：=MAX(C3:C12)和=MIN(C3:C12)，用于统计“语文”学科的最高分和最低分。
7、选中C18单元格，输入公式：=AVERAGE(C3:C12)，用于统计“语文”学科的平均分。
注意：如果成绩表中没有输入成绩时，这一公式将显示出一个错误的值“#DIV/0!”,这个错误代码将在数据输入后消失。
8、选中C19单元格，输入公式：=SUM(C3:C12)，用于统计“语文”学科的总分。
9、选中C20单元格，输入公式：=COUNTIF(C3:C12,">=80")/COUNTIF(C3:C12,">0")，用于统计“语文”学科的优秀率。同样在C21内输入相应公式统计良好率。
10、同时选中C16至C21单元格，用“填充柄”将上述公式复制到D16至J21单元格中，完成其它学科及总分的最高分、最低分、平均分、总分、优秀率和良好率的统计工作。
至此，一个基本的成绩统计表制作完成，下面我们来进一步处理一下。
任务二：根据自定条件以不同的颜显示分数。
（在此例中，让每科分数高于等于平均分的分数显示蓝，低于的则显示红）
11、选中C3单元格，执行“格式、条件格式”命令，打开“条件格式”对话框（如图2），在中间方框选中“大于或等于”，在右侧的方框中输入公式：=C18 (平均分所在单元格)，然后按“格式”按钮，打开“单元格格式”对话框，将字体颜设置为“蓝”。再按“添加”按钮，仿照上面的操作，设置小于平均分的分数字体颜为“红”（参见图2）。
注意：经过这样的设置后，当学生的“语文”成绩大于或等于平均分时，显示蓝，反之显示红。
12、再次选中C3单元格，按“格式”工具条上的“格式刷”按钮，然后在C3至J12单元格区域上拖拉一遍，将上述条件格式复制到相应的区域中，完成其他学科及总分的条件格式设置工作。
把学生的成绩填入到表格中试试看，效果不错吧。
任务二：将制作完成的统计表保存为模板。
  如果你经常要统计学生的成绩，我们将其保存为模板，方便随时调用。
  13、将工作表中的学生成绩等内容删除，执行“文件、保存（另存为）”命令，打开“另存为”对话框（如图4），按“保存类型”右侧的下拉按钮，在随后出现的下拉列表中，选“模板（*.xlt）”选项，然后给定一个名称（如“成绩统计”），按下保存按钮。

  14、以后需要统计成绩时，启动Excel，执行“文件、新建”命令，展开“新建工作簿”任务窗格（如图5），点击其中的“本机上的模板”选项，打开“模板”对话框，双击“成绩统计”模板，即可新建一份新的成绩统计表。

  15、将学生的成绩填入相应的单元格中，取名保存即可快速完成成绩统计处理工作。
在Excel中用宏实现自动生成简单的成绩条
Excel具有强大的数据处理和打印输出功能，并且易学易用，是广大用户喜欢使用的电子表格处理软件。现在一些学校人员喜欢用Excel打印本学校的学生成绩条，但在Excel中要将成绩总表(手工地转换为成绩条则是一件比较烦琐的事，下面是我编写的一个Excel宏，运行这个宏就可将编辑好了的成绩总表很方便地转换为成绩条打印输出。
在Excel中新建一个文件，将其命名为“成绩条”，在工作表“sheet1”中输入并编辑成绩表,成绩共30条，如图：
然后，点击“工具”菜单→“宏”→“宏…”→输入宏名“编辑成绩生成成绩条”→创建，输入如下的宏的各行文本，输入完成后保存该宏：
Sub Macro1()
Worksheets("Sheet1").Activate
For hang = 1 To (30 - 1) * 3
Rows("1:1").Select
Selection.Copy
Cells(hang + 2, 1).Select
Selection.Insert Shift:=xlDown
Selection.EntireRow.Insert
hang = hang + 2
Next hang
End Sub
最后执行宏，成绩条如下：

以后每月要打印成绩条时，只需将“30”改成总成绩表的记录数就可以了。
用Excel的VBA来制作精美学生成绩条。
  解决问题的思路
  因为每个学生只有一个学号，所以学号是惟一的，根据学号惟一性这一特点，使用VBA里的判断语句，如果学号不同，就在两者之间插入一个空白行，然后再在每个空白行粘贴复制的表头，最后使用循环语句，自动制作每个学生成绩记录的表头。
  解决问题的方法
  打开学生的成绩表，我们需要另存为另外一个表来制作成绩条，以免影响成绩表的原貌。
  在VBA的工程资源管理器中双击Sheet1，然后出现代码窗口，在代码窗口输入如下代码：
  Sub cjt()
  Application.ScreenUpdating = False
  Sheets(1).[A1].CurrentRegion.Copy Sheets(2).[A1]
  '将表一的成绩表复制到表二
  a=(Application.WorksheetFunction.CountA(Sheets(2).[b2:b2000]))*2
  'sheets(1).[b2:b2000]的字符数的2倍
  Sheets(2).[A1:R1].Borders(xlEdgeTop).LineStyle = xlDouble
  'sheets(2).[a1:r1]的下边框是双线
  For i = 2 To a
  If Sheets(2).Cells(i, 3) <> Sheets(2).Cells(i + 1, 3) And (Sheets(2).Cells(i, 3) <> "") Then
  Sheets(2).Rows(i + 1).Insert
  End If
  '如果第三列的上下单元格的值不相等，则在它们之间插入一个空白行
  If Sheets(2).Cells(i, 3) = "" Then
  Sheets(2).[A1:R1].Copy Sheets(2).Cells(i, 1)
  End If
  '如果第三列中的单元格是空的，则将Sheets(2).[A1:R1]复制到此行
  Next
  Application.ScreenUpdating = True
  End Sub
EXCEL2003
2009年04月21日
第一节、Excel的工作界面
说明：Excel软件是办公自动化中应用最广泛的软件之一，它的基本职能是对数据进行记录、计算与分析。在实际应用中，它小到可以充当一般的计算器，或者记算个人收支情况，计算贷款或储蓄等等；大到可以进行专业的科学统计运算，以及通过对大量数据的计算分析，为公司财政政策的制定，提供有效的参考。
lExcel的工作界面
1、标题栏：BOOK（工作簿）
2、菜单栏：
3、工具栏：
4、编辑栏：名称框、文本框
5、工作区：以表格的形式展现，实际上一张白纸，表格制作完成应设置边框，打印后是真正的表格。
6、滚动条：
7、状态栏：
第二节、工作表的操作
1、工作簿是一种由Excel创建的文件，而工作表则是工作簿的组成部分。可以这样形象的理解，工作簿是一个笔记本，而工作表是这个笔记本里的每页纸。操作和使用Excel，绝大部分工作是在工作表中进行的。
2、工作簿的相关操作（略）
3、一张工作簿中，默认有3张工作表；一张工作簿可以容纳1—255张工作表。
4、工作表的操作
A、选择工作表：单击工作标签可以选定一张工作表；配合Shift键可以选定连续的工作表；配合Ctrl键可以选定不连续的多张工作表；在工作表标签上右击，在菜单选择“选定全部工作表”命令可以选定工作簿中所有的工作表。
B、插入工作表：在工作表标签上右击，选择“插入”命令。如果需要同时添加多张工作表，应首先选定相等数量的工作表，然后在任意一张工作表标签上右击，执行“插入”命令。
*、系统不允许用CTRL键选定工作表后添加工作表。
C、删除工作表：在工作表标签上右击，选择“删除”命令。如果需要同时删除多张工作表……。
D、重命名工作表：在工作表标签上双击，输入新的名称（在工作表标签上右击，选择“重
命名”命令，输入新的名称即可。）
E、移动工作表：
a、同一张工作簿中移动数据：选定工作表，按住左键拖动到所需位置，松开左键即可。（在工作表标签处右击→移动或复制工作表……。）
b、在不同工作簿中移动数据：在工作表标签处右击→移动或复制工作表→“将选定工作表移至”、“工作簿”在下拉框选择新工作簿的名称……。
F、复制工作表：
a、同一张工作簿中复制数据：选定工作表，按下Ctrl键的同时拖动左键到所需位置，松开左键即可。（在工作表标签处右击→移动或复制工作表……→在“建立副本”前打勾。）
b、在不同工作簿中复制数据：在工作表标签处右击→移动或复制工作表→“将选定工作表移至”、“工作簿”在下拉框选择新工作簿的名称，同时在“建立副本”前打勾。
G、在多张工作表中同时输入相同的数据：配合Shift或Ctrl键选中这些工作表→输入数据→输入完成后，在任意一张工作表标签上单击左键即可。
*、编辑菜单下的工作表的相关的操作（略）
第三节、单元格的使用
一、单元格的地址：
1、行与列交叉形成一个单元格，表示时首先输入列号，再输入行号。例：A1。
2、区域：两个或两个以上的单元格称为一个区域。其表示方法为：先输入区域中左上角第一个单元格的地址，再输入冒号（：），最后输入区域中右下角单元格的地址。例：A1：B5。
二、单元格的选择：
1、单个单元格的选择：
2、多个单元格的选择：
A、框选：按住左键拖过需要选择的单元格即可。
B、连选：Shift+左键。
C、间选：Ctrl+左键。
*、在编辑栏中选择单元格：“名称框”内直接输入区域，再敲回车键，可快速选中想要的区域。（例：A1:F5、A1:B4,D5:F7)
三、编辑单元格：
1、修改单元格的内容：
A、重新输入新的内容：选定单元格后，直接输入新的内容即可。
B、对单元格的内容予以修改：在单元格内双击，编辑内容。
C、删除单元格的内容：选定单元格，按Del键。（右击→清除内容或进入编辑菜单选择清除命令……。）
2、删除单元格：选定单元格→右击→删除……。
3、插入单元格：插入菜单→单元格……（选定单元格，右击，插入……）。
4、移动单元格：
A、选定单元格，剪切→粘贴。
B、选定单元格，将鼠标指针放在单元格的边框处，出现向左的箭头符号时，拖动左键到目标位置即可。
5、复制单元格：
A、选定单元格，按住Ctrl键的同时拖动左键到目标位置即可。
B、用复制、粘贴命令。
*、技巧：
1、用编辑菜单下的填充命令快速复制内容：在一个单元格内输入内容→选定要复制内容的
所有单元格→编辑菜单→填充……。
2、选定所有内容相同的单元格→输入内容→Ctrl+Enter。（快速输入多个内容相同的单元格）
3、自定义序列
第四节、数据的录入
一、数据类型：
1、字符型数据：默认显示格式为左对齐，一般不参与运算。
2、逻辑型数据：用来判断事物真与假的数据，默认显示格式为居中对齐。例：true（真的），false（假的）。
3、数值型数据：默认显示格式为右对齐，都要参与运算。
二、特殊数据的处理：
1、特殊代码（号）的输入：
例：001、身份证号码……。
方法一、在中文输入法状态（符号为英文状态）或者是英文输入法，首先输入单引号（‘），
再输入数字代码。
方法二、选定单元格，右击→设置单元格格式→数字选项卡，选择“文本”→确定→回到表格中输入数据即可
2、分数的输入：
方法一、整数部分+空格键+分数部分。例：1/2 0+空格键+1/2。
方法二、选定单元格，右击→设置单元格格式→数字选项卡，选择“分数”，设置分数的类型→确定→回到表格中输入数据即可。
3、负数的输入：减号+数字。
例：负5 -5。
负数格式的设置:
选定单元格，右击→设置单元格格式→数字选项卡，选择数值，设置负数格式。（红、数字加有括号）
4、科学记数：（略）
5、日期和时间的输入：
A、日期的输入：年月日之间用“-”或“/”连接。例：2006年11月8日，表示方法为：2006-11-
8或2006/11/8。
*、输入系统日期的组合键：Ctrl+；
B、时间：
a、十二小时制：输入时间+空格键+输入A（表示上午），输入P（表示下午或晚上）例：上午8：30，表示方法为8：30 A
b、二十四小时制：直接输入时间即可。
*、输入系统时间的组合键：Ctrl+Shift+；