用excel轻松制作考试系统学习资料.docx
《用excel轻松制作考试系统学习资料.docx》由会员分享，可在线阅读，更多相关《用excel轻松制作考试系统学习资料.docx（25页珍藏版）》请在冰豆网上搜索。
用excel轻松制作考试系统学习资料
用Excel2007轻松制作考试系统
随着计算机的广泛普及，许多学校越来越多地开始利用计算机辅助建立考试系统，目前市面上这类考试系统很多，但若需使用就必须得购买。
其实用Excel2007也可以轻松地制作出一个类似的考试系统，而且根本不需要编程。
这样的考试系统同样具备自动出题、验证考号、答题后自动评分、保存记录等功能。
如果你有兴趣，并且具备一定的计算机操作技术，对Excel也并不陌生，不妨按下面的流程去试着自己制作一个考试系统（以下讲解基于Excel2007）。
一、Excel2007公式和函数基础
在本考试系统中要使用较多的公式，而公式又离不开函数，为了能方便地设计出考试系统，首先向大家介绍一下公式和函数的基本使用方法。
1.基本概念
公式：
公式是对工作表中的数值执行计算的等式，以等号“＝”开头。
常量：
常量是指在运算过程中不发生变化的量，如数字20以及“月平均收入”等都是常量。
函数：
函数是预先编写的公式，可以对一个或多个值执行运算，并返回一个或多个值。
函数可以简化和缩短工作表中的公式，特别是在用公式执行很长或复杂的计算时。
运算符：
运算符指一个标记或符号，指定表达式内执行的计算类型。
2.公式的组成
公式可以包括函数、单元格引用、运算符和常量等。
等号“＝”：
这是公式开头的符号，不可缺省。
函数：
如SUM（A1:
A5），函数返回值为A1到A5单元格区域中数值之和。
引用：
指定的某个或者某些单元格中的数据，用单元格地址来指定。
如“A2”返回第A列、第2行单元格中的值。
常量：
直接输入公式中的值。
运算符：
指明运算类型的符号，如“**”表示将数字乘方，“*”表示相乘。
3.运算符的种类
在Excel中有算术运算符、比较运算符、文本连接运算符和引用运算符等类型。
（1）算术运算符
若要完成基本的数学运算（如加法、减法、乘法等）、合并数字以及生成数值结果，可使用以下算术运算符，具体见下表。
（2）比较运算符
比较运算符用来比较两个值，结果为逻辑值“TRUE”（真）或“FALSE”（假）。
比较运算符见下表。
（3）文本连接运算符
可以使用与号“&”连接两个或多个字符串，生成一新的字符串，具体如下表所示。
（4）引用运算符
使用下表中的引用运算符可对单元格区域进行合并计算，具体如下表所示。
（5）运算的顺序
执行计算的顺序会影响公式的返回值，因此，了解如何确定计算顺序以及如何更改顺序以获得所需结果非常重要。
计算顺序：
Excel2007中的公式始终以等号“=”开头，这个等号告诉Excel随后的字符组成一个公式。
等号后面是要计算的元素（即操作数），各操作数之间由运算符连接。
Excel按照公式中每个运算符的特定顺序从左到右依次计算。
运算符优先级：
如果一个公式中有若干个不同优先级的运算符，则按下表中从上到下的顺序进行运算；如果一个公式中的若干个运算符具有相同的优先级，则从左到右依次计算。
4.常用函数
Excel2007提供了成百上千个函数，不可能也没必要全都掌握，常用的也不是很多，这里介绍一些常用的函数。
格式中用方括号[]括起来的参数项为可选项，要根据情况确定用不用可选项。
参数中的省略号…表示还可以有若干个类似的选项。
（1）ABS函数
返回数值参数的绝对值。
格式为：
ABS（数值表达式）
例如，单元格A2中有数值95，单元格B2中有数值-2，函数“ABS（A2*B2）”的返回值为数值190。
（2）AND函数
仅当所有参数的结果值均为逻辑真（TRUE）时返回逻辑真（TRUE），否则返回逻辑假（FALSE）。
格式为：
（AND逻辑表达式1[,逻辑表达式2]…）例如，函数“AND（8=4*2,9>6,3*6<4^2）”先计算出算术运算的结果值，再进行比较运算得出各个表达式的逻辑值（依次为“逻辑真”，“逻辑真”，“逻辑假”），最后判断出结果是FALSE（逻辑假）。
（3）AVERAGE函数
返回所有参数的算术平均值。
格式为：
AVERAGE（数值表达式1[,数值表达式2]…）
例如，工作表中输入的全是数值数据，函数“AVERAGE（A1:
C18）”返回A1、A2……A18，B1、B2……B18，C1、C2……C18一共54个单元格中的数值的算术平均值。
（4）COLUMN函数
返回所引用的单元格的列标号顺序值。
格式为：
COLUMN（单元格地址）例如，函数“COLUMN（AE11）”的返回值为AE列的顺序为31，即第31列。
（5）CONCATENATE函数
将多个文本字符串者或单元格中的文本数据连接成一个新字符串并显示在一个单元格中。
格式为：
CONCATENATE（文本表达式1[,文本表达式2]…）
例如，函数“CONCATENATE（‘中国’,‘人民’,‘万岁！
’）”的返回值是文本字符串“中国人民万岁！
”。
（6）COUNTIF函数
返回某个单元格区域中符合指定条件的单元格数目。
格式为：
COUNTIF（单元格区域引用,条件表达式）
例如函数“COUNTIF（A1:
C6,NUMBER>5）”返回A1至C6单元格区域中数字大于或等于5的单元格数目。
（7）DATE函数
返回指定数值的日期。
格式为：
DATE（年份,月份,日子）
例如，函数“DATE（2007,02,28）”将返回日期数据2007-2-28。
注意，输入年份参数时，若是20世纪及以前的则可以只输入后2位数字，也可以输入4位数字；若是21世纪及以后的年份则要输入4位数字。
（8）DAY函数
返回参数中指定的日期或者引用日期数据单元格中的日子（1～31）。
格式为：
DAY（日期表达式）
例如，在D1单元格中有日期数据“1988-2-26”，函数“DAY（D1）”将返回26。
（9）IF函数
对于给出的条件表达式进行逻辑判断，结果若为逻辑真（TRUE）则返回第二个参数的值，判断结果若为逻辑假（FALSE）则返回第三个参数的值。
格式为：
IF（条件表达式,表达式1,表达式2）
例如，假设在单元格C8、C9、C10中分别有数值数据，在单元格D8、D9、D10中分别有文本字符串。
函数“IF（A1<>0,SUM
（C8:
C10）,D8&D9&D10）”判断A1的数值是否等于0，若不等于0（即满足表达式的意义，为逻辑真）则计算C8、C9、C10三个单元格中的数值之和并返回，若等于0（即不满足条件，为逻辑假）则返回D8、D9、D10单元格中文本字符的连接结果。
（10）INDEX函数
返回指定的单元格区域中，由给定的行序号和列序号交叉处的单元格的值或者引用。
格式为：
INDEX（单元格区域引用,行序号数字[,列序号数字]）
例如，函数“INDEX（A1:
D8,5,3）”将返回A1到D8区域中第五行与第三列交叉处的单元格（C5）的值或者引用。
（11）INT函数
将数值参数的小数部分去掉，且不进行四舍五入，只返回整数部分，称为“取整”。
格式为：
INT（数值表达式）
例如，函数“IN（52.9992）”将返回数值52。
（12）ISERROR函数
用于测试指定单元格的值是否有错。
如果有错，则函数返回逻辑值TRUE，无错则返回逻辑值FALSE。
格式为：
ISERROR（单元格引用）例如，A3单元格中若显示“#NAME?
”，函数“ISERROR（A3）”返回逻辑值TRUE。
说明A3有错误。
（13）ISNUMBER函数
测试参数是否是数值，若是数值则返回逻辑真值TRUE，否则返回逻辑假值FALSE。
格式为：
ISNUMBER（参数表达式）
例如，函数“ISNUMBER（”中国”）”将返回FALSE。
（14）LEFT函数
从一个文本字符串的第一个字符开始，返回指定数值的子字符串。
格式为：
LEFT（文本表达式,数值表达式）
例如，函数“LEFT（“GDTYWUI”,4）”将返回子字符串“GDTY”。
（15）LEN函数
返回文本字符串中字符的个数。
格式为：
LEN（文本表达式）
例如，在A5单元格中存有文本数据“KJDU上下左右！
”，函数“LEN（A5）”将返回数值11。
注意，一个标点符号及空格也要算作一个字符。
（16）MAX或MIN函数
求出一组数中的最大值或者最小值。
格式为：
MAX（数值表达式1[,数值表达式2]…）或者MIN（数值表达式1[,数值表达式2]…）
例如，函数“MAX（3/2，3，6/3）”将返回数值3.
（17）MID函数
从一个文本字符串的指定位置开始，返回指定个数的子字符串。
格式为：
MID（文本表达式,数值表达式1,数值表达式2）
例如，在A2单元格中有文本数据“中华人民共和国”，函数“MID（A2,3,2）”将返回文本字符串“人民”。
（18）MOD函数
返回两数相除的余数。
格式为：
MOD（数值表达式1,数值表达式2）例如，在A1单元格中有数值123，在A2单元格中有数值12，函数“MOD（A1，A2）”将返回123除以12所得的余数3。
（19）MONTH函数
返回指定日期或引用单元格中的日期数据的月份（1~12）。
格式为：
MONTH（日期数据或日期单元格引用）
例如，在A1单元格中有日期数据1988-11-1，函数“MONTH（A1）”将返回月份11。
（20）NOW函数
返回系统的当前日期和时间。
格式为：
NOW（）
例如，假设现在是2007年6月12日晚上8点35分，函数“NOW（）”将返回这个年月日和时间2007-6-1220:
35。
（21）OR函数
在给出的所有参数中，当其值均为逻辑假（FALSE）时返回逻辑假（FALSE），只要有一个参数的值为逻辑真则返回逻辑真（TRUE）。
格式为：
OR（逻辑表达式1[,逻辑表达式2]…）
例如，函数“OR（3>2,25<3*8,54<>36+18）”将返回逻辑真（TRUE），因为第一个参数结果为TRUE。
（22）RIGHT函数
返回从一个文本字符串的最后一个字符开始，向左截取指定个数的子字符串。
格式为：
RIGHT（文本表达式,数值表达式）
例如，在A3单元格中有文本数据“ABSDEFG”，函数“RIGHT（A3,9/3）”将返回字符串“EFG”。
（23）ROW函数
返回一个引用的行序号，在代码编写中会用到这个函数。
格式为：
ROW（单元格引用）
（24）ROWS函数
返回指定的单元格区域或者数组的行数，在代码编写中会用到这个函数。
其格式为：
ROWS（数组名或者区域引用）
例如，函数“ROWS（A1:
A258）”将返回258。
（25）INDIRECT函数
返回由文本字符串指定的引用。
此函数立即对引用进行计算，并显示其内容。
格式为：
INDIRECT（单元格引用字符串,逻辑值）
说明：
单元格的引用可以包含像A1样式的引用、R1C1样式的引用、定义为引用的名称或对文本字符串单元格的引用。
如果不是合法的单元格的引用，函数INDIRECT返回错误值#REF!
。
逻辑值指明包含在单元格引用中的引用类型。
如果为TRUE或省略，单元格引用被解释为A1样式的引用。
如果为FALSE，单元格引用被解释为R1C1样式的引用。
例如，有如下的工作表数据：
函数“INDIRECT（$A$1）”将返回25；函数“INDIRECT（$A$2）”将返回ABCD。
（26）RAND函数
返回大于等于0及小于1的均匀分布随机实数，每次计算工作表时都将返回一个新的随机实数。
格式为：
RAND（）
说明：
若要生成a与b之间的随机实数，可写成RAND（）*（b-a）+a的形式；如果要使用函数RAND（）生成一随机数，并且使之不随单元格计算而改变，可以在编辑栏中输入“=RAND（）”，保持编辑状态，然后按F9，将公式永久性地改为随机数。
例如，函数“RAND（）”将返回介于0到1之间的一个随机数（变量）；函数“RAND（）*100将返回大于或者等于0但小于100的一个随机数（变量）；函数“RAND（）*（10-5）+5”将返回介于5和10之间的随机数。
（27）SUM函数
返回所有数值参数值的和。
格式为：
SUM（数值表达式1[,数值表达式2]…）
说明：
数值表达式可以是直接给出的数值，也可以是单元格引用。
（28）SUMIF函数
返回符合指定条件的单元格区域内的数值之和。
格式为：
SUMIF（单元格区域,相加的条件[,相加的实际单元格]）
说明：
“单元格区域”中的单元格都必须是数字和名称、数组和包含数字的引用。
空值和文本值将被忽略。
“相加的条件”的形式可以是数字、表达式或文本。
例如，条件可以表示为32、"32"、">32"，如果是表达式或文本必须用引号括起来。
如果省略参数“相加的实际单元格”则当“单元格区域”中的单元格符合条件时，它们既按条件计算，也执行相加。
“相加的实际单元格”与“单元格区域”的大小和形状可以不同。
例如，有下列工作表数据：
函数“SUMIF（A2:
A5,">200000",B2:
B5）”在A2至A5区域中判断金额高于200,000元的单元格是A3至A5，返回对应的佣金（B3至B5）之和为64,000元。
函数“SUMIF（A2:
A5,">200000"）”在A2至A5区域中判断金额高于200,000元的单元格是A3至A5，因省略了“实际求和的单元格”参数，故返回A2至A5区域中满足条件的单元格（A3至A5）的金额之和为980,000元。
函数“SUMIF（A2:
A5,"=310000",B2:
B3）”返回“单元格区域”中金额等于310,000元的佣金之和为22,000元。
（29）TEXT函数
根据指定的数值格式将相应的数字转换为文本型数据。
（30）TODAY函数
返回系统日期。
（31）VALUE函数
将一个由数字组成的文本字符串转换为数值型数据。
（32）WEEKDAY函数
返回指定日期所对应的星期几。
5.函数的输入
如果创建带函数的公式，可直接在当前单元格输入，在函数编辑框和单元格中都会显示输入的公式和函数，而利用“插入函数”对话框将有助于输入函数。
在公式中输入函数时，“插入函数”对话框将显示在当前单元格的下方，会显示出函数的名称、各个参数、各个参数的说明、函数的当前结果以及整个公式的当前结果，在对话框中单击某个函数会出现该函数的解释。
当输入“=”和开头的几个字母或显示触发字符之后，Excel2007会在单元格的下方显示一个动态下拉列表，该列表中包含与这几个字母或该触发字符相匹配的有效函数、参数和名称。
然后就可以将该下拉列表中的一项插入公式中。
参数可直接从键盘输入，也可引用单元格区域中的数据（见后文“单元格数据的引用”部分），或者用鼠标单击单元格（注意，若要输入多个单元格的数据则要正确输入相应的分隔符号），这要根据实际情况灵活运用。
6.函数的嵌套
有时需要将某函数作为另一函数的参数使用，这就形成了函数的嵌套。
嵌套函数最多可以嵌套64个级别的函数。
下面的公式仅在一组数值（A2A5）的平均值小于90时返回0，否则返回另一组数值（D3D7）的和。
IF（AVERAGE（A2A5）90,0,SUM（D3D7））
AVERAGE和SUM函数嵌套在IF函数中。
当嵌套函数作为参数使用时，它返回的值的类型必须与参数使用的类型相同。
例如，如果参数返回一个逻辑值TRUE或者FALSE，那么嵌套函数也必须返回一个TRUE或者FALSE。
否则，将显示#VALUE!
错误信息。
7.单元格数据的引用
引用的作用在于标识工作表上的单元格或单元格区域，并告知Excel在何处查找公式中所使用的数据。
通过引用，可以在一个公式中使用工作表不同部分中包含的数据，或者在多个公式中使用同一个单元格的数值，还可以引用同一个工作簿中其他工作表上的单元格和其他工作簿中的数据。
引用其他工作簿中的单元格被称为“链接”或“外部引用”。
（1）引用的样式
单元格引用有以下几种样式：
①一个单元格的引用就用它的地址标识，如A列第10行处的单元格就可输成“A10”。
②不连续的单元格引用“,”运算符分隔各单元格，如
“A2,A5,B12,D58”。
③同一行中连续单元格的引用可利用“:
”运算符，如在A列第10行到第20行之间的单元格引用就输成“A10:
A20”。
④同一列中连续单元格的引用也可利用“:
”运算符，如第15行的A列到G列之间的单元格引用可输入“A15:
G15”。
⑤某行中的所有单元格引用只需要输入行标识就行了，如第5行中的全部单元格输入“5:
5”，第2行到第8行之间的全部单元格则输入“2:
8”。
某列中的全部单元格引用则只用列标识，如第C列中的全部单元格引用输入“C:
C”，第B列到第G列之间的全部单元格引用输入“B:
G”。
⑥某个单元格区域的引用包括以指定的两个单元格连线为对角线所围成的矩形区中所有的单元格的数据。
如第F列第5行到第H列第10行之间的单元格区域引用输入“F5:
H10”。
⑦引用同一个工作簿中另一个工作表上的单元格区域，需要在单元格区域前面加上工作表的名称和一个感叹号。
例如当前工作表是Sheet1，现要引用Sheet2中单元格区域，应输入下面的格式，感叹号的作用是将工作表引用与单元格区域引用分开。
“＝SUM（Sheet2!
A2:
B10）”
这个公式是计算工作表Sheet2中A2到B10单元格区域（包括A2和B10）的数值之和，并显示在工作表Sheet1的当前单元格中。
（2）相对引用、绝对引用和混合引用
①相对引用：
公式中的相对单元格引用是基于包含所引用的单元格的相对位置。
如果公式所在单元格位置改变（如复制公式到另一单元格），引用地址也随之改变。
如果在多行或多列中复制或填充公式，引用会自调整。
默认情况下，复制的公式使用相对引用。
例如，如果将单元格B2中的公式“=A1”复制或填充到单元格B3，将自动从“=A1”变成“=A2”，因为复制的公式下移了一行，引用的单元格也自动增加一个行标号。
②绝对引用：
公式中的绝对单元格引用总是在特定位置引用单元格，如果公式所在单元格的位置改变，所引用的单元格将保持不变。
如果在多行或多列复制或填充公式，绝对引用将不作任何调整。
默认情况下，复制的公式使用相对引用，可以将它们转换为绝对引用。
例如，如果将前例中的公式输成“=$A$1”，从单元格B2中复制或填充该公式到单元格B3，则在两个单元格中的结果完全一样，都是引用的A1单元格的数据。
③混合引用：
混合引用包括有“绝对列和相对行”或者“绝对行和相对列”两种情况。
混合引用采用如“$A1”、“B$”等形式。
前者属于“绝对列相对行”引用，后者属于“相对列绝对行”引用。
如果公式所在单元格位置改变，则相对引用将改变，而绝对引用不变。
如果在多行或多列中复制或者填充公式，相对引用将自动调整，而绝对引用不作任何调整。
例如，如果将一个混合引用“＝A$1”从A2单元格复制到B3单元格，它将自动由“=A$1”调整成“=B$1”，只是列变了而行不变。
二、设计考试系统
1.创建题库
①启动Excel2007后，双击工作表标签“sheet1”并将其更名为“单选题”。
②在“单选题”工作表的A2单元格中输入公式“=ROW（）-1&"."”，用以自动生成序号，在D2单元格中输入公式“=IF（C2="","",RAND（））”。
注意：
在单元格中输入任何公式、函数时，所有的标点符号以及运算符号、斜线等均应在英文标点符号状态下输入，否则不被认可。
③选中A2:
D2区域并拖动其填充柄竖直向下复制到其他单元格。
至于复制多少行，可由自己确定，够用就行了，并且以后还可以再复制，如图1所示。
图1
④按住Ctrl键并用鼠标拖动“单选题”工作表标签复制出两份，分别重命名为“多选题”和“判断题”。
2.答题卷制作
①建立“试卷”工作表的简单方法是将标签“sheet2”更名为“试卷”就可以了。
②按需要设置试题量，并输入单选题、多选题、判断题三个大标题，多选题需注明“错选、漏选、多选均不得分”字样，并留出相应的试题位置。
比如设计单选题30个（所占单元格区域是A5:
C34），多选题40个（所占单元格区域是A36:
C75），判断题30个（单元格区域是A77:
C106）。
③在功能区打开“插入”选项组，单击“形状”选项，在下拉列表中选择“自选图形”→“基本形状”→“棱台”，移动鼠标到A1单元格按住鼠标左键画出一个按钮。
选中按钮后在名称框中输入“出题按钮”四个字后回车，把它命名为“出题按钮”。
④右击刚才绘制的出题按钮并选择“添加文字”快捷菜单命令，然后在按钮上输入“出题”两个字。
⑤按照同样的方法再设计一个按钮“评分按钮”，添加文字“评分”，如图2所示。
图2
3.在答卷中显示试题
①在A5单元格输入公式“=INDIRECT（"单选题!
R"&ROW（）-3&"C"&COLUMN（）,0）”，选中A5单元格并复制，再选中区域A5:
C34进行粘贴。
②在A36单元格中输入公式：
“=INDIRECT（"多选题!
R"&ROW（）-34&"C"&COLUMN（）,0）”并复制到区域A36:
C75。
③在A77单元格输入公式“=INDIRECT（"判断题!
R"&ROW（）-
75&"C"&COLUMN（）,0）”并复制到区域A77:
C106。
④在E4单元格输入公式“=IF（D4=C4,1,0）”，这个公式用来判断D4中的答案与C4的标准答案是否一致，一致就得1分，否则为0分。
这里说明一下，A5的公式表示对单选题工作表中同列、行数差3的单元格（A2）的引用，公式中使用了“R1C1”格式的单元格编号，其中3、34、75是由第一题所在的行数减2得来。
这个公式实现的是绝对位置引用，即不管在单选题工作表中进行插入、删除行或排序，A5显示的始终都是你从单选题A2中直接看到的内容。
因此A5:
C34、A36:
C75、A77:
C106三个区域将分别与相应题库中最前面的30（或40）个题的内容保持一致。
4.答案的限制
①选中单元格区域D5:
D34，在功能区打开“数据”选项组，单击“数据/有效性”选项，在下拉列表中选择“数据有效性”选项，将弹出“数据有效性”对话框。
在“设置”选项卡的“允许”下拉列表中选择“序列”项，再选中“提供下拉箭头”复选项，在“来源”文本框中输入“A,B,C,D”，如图3所示。
注意：
不输入引号，并且其中的逗号为英文标点状态的格式。
图3
②切换到“出错警告”选项卡，选中“输入无效数据时显示警告”复选项，再在“错误信息”编辑框中输入出错时的提示信息为“只能输入A、B、C、D中的一项或多项，其他任何字符均为非法字符。
”，选择“样式”为“停止”，单击“确定”完成设置，如图4所示。
图4
③同样对多选题的D36:
D75区域，判断题的D77:
D106区域设置数据有效性，只是多选题的“来源”要换成
“A,B,C,D,AB,AC,AD,BC,BD,CD,ABC,ABD,ACD,BCD,ABCD”，判断题的“来源”要换成“√,×”。
当然“出错警告”下的“出错信息”也要作相应修改。
这里要着重强调的是多选题的每一个答案，一定要说明必须按字母ABCD的顺序输入多选项，否则无法正确评分，如“ACD”不能输成“CAD”或者“CDA”或者“DAC”或者“DCA”。
标准答案也是一样的要求。
如果感觉到多选题要输入这么一大串有效性设置太麻烦，那么不设置有效性的选项也可以，只是要在多选题的要求中注明“不按字母顺序输入答案的不给分”就行了。
5.验证考生准考证号
①建一个“考生名单”工作表。
在A、B、C列分别输入序号、准考证号、考生姓名，其中序号和准考证号的输入可采用序列填充的方法完成。
②选中B列，在名称框中输入“考号”，回车确定，把B列命名为准考证号，如图5所示。
图5
③回到“试卷”工作表中，选中要输入准考证号的D2单格，执行菜单命令“数据/有效性”，在“设置”选项卡的“允许”下拉列表中选择“序列”，取消“提供下拉箭头”复选项，在“来源”文本框中输入公式“=INDIREC
升级会员 