赠李白古诗:用Excel制作考试系统示例（一）

随着计算机的广泛普及，许多学校越来越多地开始利用计算机辅助建立考试系统，目前市面上这类考试系统很多，但若需使用就必须得购买。其实用 Excel 2007 也可以轻松地制作出一个类似的考试系统，而且根本不需要编程。这样的考试系统同样具备自动出题、验证考号、答题后自动评分、保存记录等功能。
如果你有兴趣，并且具备一定的计算机操作技术，对Excel 也并不陌生，不妨按下面的流程去试着自己制作一个考试系统(以下讲解基于Excel 2007)。
一、Excel 2007 公式和函数基础
在本考试系统中要使用较多的公式，而公式又离不开函数，为了能方便地设计出考试系统，首先向大家介绍一下公式和函数的基本使用方法。
1. 基本概念
公式：公式是对工作表中的数值执行计算的等式，以等号“ ＝” 开头。
常量：常量是指在运算过程中不发生变化的量，如数字20 以及“ 月平均收入” 等都是常量。
函数：函数是预先编写的公式，可以对一个或多个值执行运算，并返回一个或多个值。函数可以简化和缩短工作表中的公式，特别是在用公式执行很长或复杂的计算时。
运算符：运算符指一个标记或符号，指定表达式内执行的计算类型。
2. 公式的组成
公式可以包括函数、单元格引用、运算符和常量等。
等号“ ＝” ：这是公式开头的符号，不可缺省。
函数：如SUM(A1:A5)，函数返回值为A1 到A5 单元格区域中数值之和。引用：指定的某个或者某些单元格中的数据，用单元格地址来指定。如“ A2 ” 返回第A 列、第2 行单元格中的值。
常量：直接输入公式中的值。
运算符：指明运算类型的符号，如“ ** ” 表示将数字乘方，“ * ” 表示相乘。
3. 运算符的种类
在Excel 中有算术运算符、比较运算符、文本连接运算符和引用运算符等类型。
(1)算术运算符
若要完成基本的数学运算(如加法、减法、乘法等）、合并数字以及生成数值结果，可使用以下算术运算符，具体见下表。
运算符
含义
示例
+（加号）
加法
13+31
-（减号）
减法、负数
31-21、-81
*（星号）
乘法
13*3
/（左斜杠）
除法
42/14
%（百分号）
百分比
55%
^或**
乘方
9^2或9**2
(2)比较运算符
比较运算符用来比较两个值，结果为逻辑值“ TRUE ” (真）或“ FALSE ” (假）。比较运算符见下表。
(3)文本连接运算符
可以使用与号“ & ” 连接两个或多个字符串，生成一新的字符串，具体如下表所示。
(4)引用运算符
使用下表中的引用运算符可对单元格区域进行合并计算，具体如下表所示。
(5)运算的顺序
执行计算的顺序会影响公式的返回值，因此，了解如何确定计算顺序以及如何更改顺序以获得所需结果非常重要。
计算顺序： Excel 2007 中的公式始终以等号“ = ” 开头，这个等号告诉Excel 随后的字符组成一个公式。等号后面是要计算的元素(即操作数），各操作数之间由运算符连接。Excel 按照公式中每个运算符的特定顺序从左到右依次计算。
运算符优先级：如果一个公式中有若干个不同优先级的运算符，则按下表中从上到下的顺序进行运算；如果一个公式中的若干个运算符具有相同的优先级，则从左到右依次计算。
使用括号：若要更改运算的顺序，可将公式中要先运算的部分用一对括号括起来。例如，公式= 5+2*3 的结果是11，因为 Excel2007 先进行乘法运算后进行加法运算；如果用括号将其更改为=( 5+2)*3，将先求出5 加2 之和，再用结果乘以3 得21。
在下面的公式中，前面的括号强制先计算B4+25，然后再除以单元格D5 、E5 和F5 中的值之和。前后两个括号的意义不一样，前者用来改变运算顺序，而后者是函数的组成部分。
=( B4+25)/SUM(D5:F5)
4. 常用函数
Excel 2007 提供了成百上千个函数，不可能也没必要全都掌握，常用的也不是很多，这里介绍一些常用的函数。格式中用方括号[ ] 括起来的参数项为可选项，要根据情况确定用不用可选项。参数中的省略号… 表示还可以有若干个类似的选项。
(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 - 12 20: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. 函数的输入
如果创建带函数的公式，可直接在当前单元格输入，在函数编辑框和单元格中都会显示输入的公式和函数，而利用“ 插入函数” 对话框将有助于输入函数。在公式中输入函数时，“ 插入函数” 对话框将显示在当前单元格的下方，会显示出函数的名称、各个参数、各个参数的说明、函数的当前结果以及整个公式的当前结果，在对话框中单击某个函数会出现该函数的解释。
当输入“ = ” 和开头的几个字母或显示触发字符之后， Excel 2007 会在单元格的下方显示一个动态下拉列表，该列表中包含与这几个字母或该触发字符相匹配的有效函数、参数和名称。然后就可以将该下拉列表中的一项插入公式中。
参数可直接从键盘输入，也可引用单元格区域中的数据(见后文“ 单元格数据的引用” 部分），或者用鼠标单击单元格(注意，若要输入多个单元格的数据则要正确输入相应的分隔符号），这要根据实际情况灵活运用。
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 ”，只是列变了而行不变。