EXCEL在教学中的应用.docx

EXCEL在教学中的应用
在学校的教学过程中，对学生成绩的处理是必不可少的，为了在教学中提高成绩，我们需要对学生的 考试成绩进展认真的分析，这就要求我们算出与之相关的一些数值：像每一个同学的总分及班名次、 级名次，各科分数的平均分，务科的优秀率及及格率等等，如果用Excel来处理这些数据那么非常简单， 下面就我在实际工作中的一点儿经历，简单谈一谈用Excel处理学生成绩。<BR><BR>&nbsp;&nbsp;〈BR> 一、排列名次<BR><BR〉要用到RANK函数，它是Excel中计算序数的主要工具，它的语法为：
RANK(Number,Rcf.Ordcr),其中Number为参与计算的数字或含有数字的单元格，Ref是对参与计算的数字 单元格区域的绝对引用，Order是用来说明排序方式的数字(如果Order为零或省略，那么以降序方式给出 结果，反之按升序方式)。例如：在E2:E5。单元格区域中存放假设某一个班的总分，那么计算总分名次的方 法是：在F2单元格中输入JRANK(E2,SE$2:SE$5O)"按回车键可算出E2单元格内总分在班内的名次，我 们再选定F2单元格，把鼠标指针移动到填充柄上按下鼠标左键向下拖动鼠标即可算出其他总分在班内的 名次。<BR><BR>在计算的过程中我们需要注意两点：首先当RANK函数中的Number不是一个数时，其返回值为“#VALUE!”,影响美观。另外，Excel有时将空白单元格当成是数值“0”处理，造成所有成绩 空缺者都是最后一名，看上去也很不舒服。此时，可将上面的公式“=RANK(E2、SE$2:$E$50)"改为 "=IF(ISNUMBER(E2),RANK(E2,SeS2:$ES50),"")"。其含义是先判断E2单元格里面有没有数值，如果有那 么计算名次，没有那么空白。其次当使用RANK函数计算名次时，一样分数算出的名次也一样，这会造成 后续名次的空缺，但这并不影响我们的工作。同样的道理，我们也可以算出一个学生的总分在年级内的名 次以及各科的班名次和年级名次，但是必须注意参与计算的数字单元格区域不一样。<BR><BR> 二、 求各种分数<BR><BR>求总分：主要用SUM函数，其语法格式为SUM(Ref),此处Ref为参与计算的
单元格区域。例如：SUM(B2:E2)是表示求B2、C2、D2、E2四个单元格内数字的和。另外还用到SUMIF 函数，语法格式为SUMIF(Range,Criteria,Sum_range),其功能是根据指定条件对假设干单元格求和，参数 Range表示引用，用于条件判断的单元格区域。Criteria表示数字、表达式或文本，指出哪些单元格符合被 相加求和的条件。Sum-range表示引用，需要求和的实际单元格。注意：Criteria如果是文本，那么引号应 该是半角的，而不是全角的，否那么会出错！ <BR><BR> 求平均分：用A VERAGE函数，其语法格式 为AVERAGE(Ref).此处Ref为参与计算的单元格区域。例如AVERAGE(F2:F50)是求F2:F50区域内数字 的平均值。默认情况下，Excel 2002会忽略掉空白的单元格，但是它不忽略数值为。的单元格，要想忽略 数值为0的单元格需要用到COUNTIF函数，其语法为COUNTIF(Range,Criteria),其含义是计算某个区域 中满足给定条件的单元格数目。本例求F2:F50的平均分，如果忽略数值为。的单元格可以这样计算： SUM(F2:F50)/COUNTIF(F2:F50."&lt:&gt:0,,)o另外如果要求去掉几个最高分和几个最低分然后取平均分的 话，用到LARGE和SMALL函数，其语法格式为LARGE(array,k),含义是返回数组中第k个最大值， SMALL(array.k)的含义是返回数组中第k个最小值。如果我们求F2:F50中去掉两个最高分和两个最低分之 后的平均分可以这样计算：
“SUM(F2:F50)-LARGE(F2:F50,1 )-LARGE(F2:F50,2)-SM ALL(F2:F5(), 1 )-SM ALL(F2:F50,2))/COUNTIF(F2:F5 0-4)" o <BR><BR>求最高分、最低分：MAX和MIN函数，语法格式分别为MAX(Ref)和MIN(Ref),如上例中求F2:F50的最高分和最低分，应该这样：MAX(F2:F50)和M【N(F2:F50)。<BR><BR> 三、求 及格率、优秀率<BR><BR>求及格率：及格率即-•个班级中某科大于等于60分的比例，例如：B2:B50
中是某一个班的语文成绩，可以这样求及格率：COUNTIF(B2:B50.,,&gl;=60,,)/COUNT(B2:B50)o <BR><BR> 求优秀率：例如：B2:B50存放的是初一班的语文期末考试成绩，B2:B5OO存放的是初全年级语文考试 的成绩，如果规定全年级20%的学生为优秀，那么初一一班语文的优秀率应该这样计算： “COUNTIF(B2:B50.”&gt;="&amp：&anip:LARGE(B2:B500.INT(0.2 COUNT(B2:B500))))/COUNT(B2:B50) ”,其中LARGE(B2:B500,INT(0.2 COUNT(B2:B500)))所求的是全年级语文分数前20%中最低的一个同学 的分数,COUNTIF(B2:B50；,&gt;=,,&amp;&amp;LARGE(B2:B500,INT(0.2*COUNT(B2:B500))))那么是求出 了初一-•班语文高于或等于这个同学分数的人数，最后再除以初--•班的总人数COUNT(B2:B50),所得就 是初一一班的语文优秀率，如果想求其他班其他科目的优秀率，道理都是一样。<BR>
翻开第二个班的成绩文件后，选中C72单元格使其成为活动单元格，选择菜 单“工具一宏一宏〃，出现“宏〃对话框，在“宏名〃列表框中选择刚建立的宏 文件名“高一〃，单击执行按钮，宏命令就会运行。运行完毕后，第二个班的 成绩就计算完了。同样，其他各班的成绩都可以这样来处理。
数据的归类
把各班平均分和各科分数段分别应用“选择性粘贴〃中的“数值"复制到 对应的工作表或工作簿中，就得到了完整的各班平均分〔或者各班前N名的平均 分）和各分数段人数的统计表。
在录制“宏命令〃的过程中要注意，录制的都是所有成绩文件具有共性的内 容。如果是某个文件所特有的，如按“高一・1班成绩〃为名来保存文件，就不 能出现在录制宏的过程中，否那么就会出现错误。录制好宏命令后，在以后的考 试成绩处理中，就可以直接用来处理这个年级的成绩文件了。这个宏命令也可以 用来处理其他年级的成绩文件，如果科目数量不同或要求不同，按照这一方法再 建立一个新的宏命令就可以了。虽然建立宏命令的过程有些麻烦，但是如果能够 很好利用的话，会起到事半功倍的效果。
注：本文中有些公式用到“$",在复制公式时引用范围不会发生变化。如 计算达优率时在C74单元格中输入公式后，复制公式至E74,然后再依次修改对 应的内容即可。如采用复制公式的方法，在D74中只需把“"> = 120〃 〃改为
>=80〃 "即可。
Excel统计学生成绩时的四个难题及解决
一、前言
对于教师而言，经常需要用Excel进展学生成绩统计，会被一些常见问题难 住。碰到的难题主要有：如何统计不同分数段人数、如何在保持学号不变前提下 进展排名、如何将百分制转换成不同分数段与如何用红色显示不及格的分数等， 本文着重对这些问题的解决方法与技巧加以分析和讨论。
本文假设读者已对Excel的根本操作已经有一定根底，已经掌握如何进展求 和、求平均和如何使用自动填充柄进展复制公式等操作，本文对这些一般性操作 不做详细介绍，仅对一些难度较大的操作技巧进展讨论。
二、Excel统计学生成绩时的四个难题
假设在统计学生成绩时，我们需要统计出如图1所示的相关结果。
X Bicrosofl Excel - exeel演示
刃文件9 位© 视图。插入Q）格式⑵数角仙 窗口地）M的QI）
]]□咨0 J X 电 cao
■O ■ 「袒_
•夸3

& 0

B 7 U

A18二|
1 s

A
B
C
DE
F
G 三
1
学号
姓名
1成绩
等级1
等级2
名次
2
1
王一军
76
C
良
9

3
2
张小梅
78
C
良
7

4
3
李兰
84
B
良
4

5
4
何彬彬
62
D

11

6
5
张海
92
A
优
1

7
6
周平
88
B
良
3

8
7
陈志东
84
B
良
4

9
8
赵国泉
78
C

7

10
9
蒋去平
90
A

2

11
10
于连鸡
80
B
良
6

12
11
王萍萍
59
E
不及格
12

13
12
何琼
66

14

▲——一 _. ■ . J
■ Hl乞室-
15
9CM00 分
8。~89 分
70〜79分

16
2
4

—1▼ 1厂一77.段 I

17
► ►iNSheetl /Sheet2/Sheet3>

一 K—

, ■ _ ■ ,

JIMyesky! com^；
图1学生成绩统计所需要的结果图
这里，假设学号、姓名、成绩等列及行15都己经事先输好，需要让Excel 统计其他的相关数据结果。这时，成绩统计中主要难解决的问题及它们在图中的 位置如下：
问题1：如何统计不同分数段的学生人数？（图中A16〜E16）
问题2：如何在保持学号顺序不变的前提下进展学生成绩名次排定？（图中 F2~F13）
问题3：如何将百分制转换成不同的等级分？（图中“等级1〃与“等级2〃 列）
问题4：如何使不及格的分数以红色显示？（图中红色显示局部，即第12行）
三、解决统计学生成绩时的四个难题的方法
下面，针对上面提出的四个难题分别讨论解决的方法与技巧。
1、统计不同分数段的学生人数
统计不同分数段的学生人数是非常常见的需求，其所需结果如图1中 A16~E16所示。这里，假设需要统计90~100、80~89、70~79、60~69及低于60 分五个不同分数段的人数。
通常，统计不同分数段最好的方法是利用COUNTIF(X, Y)函数。其中有两个 参数，第一个参数X为统计的范围，一般最好用绝对引用；第二个参数Y为统计 条件，要加引号。
对于小于60分的人数只要用一个C0UNTIF()函数，如在E16单元格中输入 公式：=C0UNTIF($C$2:$C$13,〃<60〃)。
对于其他在两个分数之间的分数段的人数统计，需要用两个C0UNTTF()函 数相减。如在A16单元格中输入公式：
=COUNTIF($C$2:$C$13, 〃<=100〃)-COUNTIF($C$2:$C$13, 〃<90〃)，即用小于等于 100的人数减去小于90的人数。
如果要统计80、89、70~79与60~69分数段的人数，只要利用自动填充柄将 该公式复制到右边三个单元格，再把〃<=100〃与〃<90〃作相应的修改，就可以得到 正确的结果。
2. 保持学号顺序不变的前提下进展成绩排名
学生成绩排定在学生成绩统计中经常用到。特别要强调的是，这里所谈的方 法不是一般的排序，因为那样会使学生的学号顺序发生变化。这里所需要的是在 保持学号顺序不变的情况下进展学生成绩名次排定的功能，其所需结果如图1 中F2、F13所示。
要进展保持学号顺序不变的情况下进展学生成绩名次的排定，最好使用 RANK(X, Y, Z)函数。其中有三个参数，第一个参数X为某个学生的成绩所在单 元格;第二个参数Y为整个班级成绩所在的区域;第三个参数Z是可选的，表示统 计方式，假设省写或写0,那么成绩高的名次靠前，一般都使用这种方式，如果 写1,那么成绩高的名次靠后，这种情况一般较少用。
为了在保持学号顺序不变的前提下进展学生成绩名次排定，可以在F2单元 格中输入公式：二RANK(C2,$C$2:$C$13,0),然后，利用自动填充柄将其复制到下 方的几个单元格。注意，这里$C$2:$C$13用的是绝对地址，是为了保证公式在 复制时此处不变，因为作为第二个参数，这里都是指整个班级成绩所在的区域， 这个区域是一样的。
3、将百分制转换成不同的等级分
将百分制转换成不同的等级分有多种不同的划分方法，其所需结果如图1 中“等级1〃与“等级2〃列所示。这里，“等级1〃列是将百分制的分数转换 成 A "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函数。
为了得到“等级1〃列所要的等级结果，可以在D2单元格中输入公式： =IF(C2>=90, "A”, IF(C2>=80, 〃B〃, IF(C2>=70, "C", IF(C2>=60, 〃D〃，〃E〃))))，然 后，利用自动填充柄将其复制到下方的几个单元格。
为了得到“等级2”列所要的等级结果，可以在E2单元格中输入公式： =IF(C2>=90,"优〃，IF(C2>=75,〃良〃，IF(C2>=60, 〃中〃，〃不及格〃)))，然后,利用 自动填充柄将其复制到下方的几个单元格。
4、使不及格的分数以红色显示
统计学生成绩时经常需要将不及格的分数用红色显示，其结果如图1中红色 显示局部(如第12行)。
使不及格的分数以红色显示需要使用“格式〃菜单中的“条件格式〃命令。 该命令会弹出一个对话框，其中要求确认条件与相应的格式。
对于“成绩〃列，可先选中C2： C13,然后使用“格式〃菜单中的“条件格 式〃命令，在弹出的对话框中，左边使用默认的“单元格数值〃，中间选“小 于〃，右边填写60,然后单击右边的“格式〃按钮，从中选择红色，最后单击 两次“确定〃按钮。
对于“等级1〃列，可先选中D2： D13,然后使用“格式〃菜单中的“条件 格式"命令，在弹出的对话框中，左边使用默认的“单元格数值〃，中间选“等 于〃，右边填写E,然后单击右边的“格式〃按钮，从中选择红色，最后单击两 次“确定〃按钮。“等级2〃列类似。
对于其他的一些统计计算要求，如怎样计算各分数段的百分比、如何计算机 优良率与合格率等功能，应该比较简单，本文此处不赘述。
EXCEL公式•函数应用大全1、SUMPRODUCT函数：该函数的功能是在给定的几组数组中将数组间对应的元索相乘并 返回乘积之和。例如：如图1,如果想计算B3： C6和C3： E6这两组区域的值，可以用以 下公式：u=Sumproduct(B3:C6,D3:E6)/，。
图12、ABS函数：如果在Al、B1单元格中分别输入120、90,那么如果要求A1与B1之间的 差的绝对值，可以在CI单元格中输入以下公式："=ABS(A1-B1)”.
3、IF函数：如图2,如果C3单元格的数据大于D3单元格，那么在E3单元格显示“完成任 务，超出："，否那么显示“未完成任务，差额：”，可以在E3单元格中输入以下公式：
"=IF(C3>D3, “完成任务，超出："，"未完成任务，差额：""o图2
4、Ceiling函数：该数值向上舍入根底的倍数.如图3,在C3单元格中输入以下公式： ”=CEILING(B3,C3)"；而''=FLOOR(B3,C3)〃 那么是向下舍入。
图35、GCD函数：该函数计算最大公约数。如图4,如果要计算B3： D3这一区域中3个数字 的最大公约数，可以在E3单元格中输入以下公式："=GCD(B3,C3,D3)〃。
图46、INT函数：该函数是向下舍入取整函数。如图5,如果要计算显示器和机箱的购置数量, 可以在E3单元格中输入以下公式："=INT(D3/C3)”。
图57、LCM函数：该函数是计算最小公倍数。如图6,如果要计算B3： D3这一区域中3个数 字的最小公倍数，可以在E3单元格中输入以下公式："=LCM(B3,C3,D3)”。
图68、LN函数：该函数是计算自然对数，公式为："=LN(B3)”。
9、LOG函数：该函数是计算指定底数的对数，公式为："=LOG10(B3)"。
10、MOD函数：该函数是计算两数相除的余数。如图7,判断C3能否被B3整除，可以在 D4单元格中输入以下公式："=IF(MOD(B3,C3)=0,”是”，”否")〃。
图711、PI函数：使用此函数可以返回数字即数学常量PI,可准确到小数 点后14位。如图8,计算球体的面积,可以在C4单元格中输入以下公式:“=PI()*(B3&)*4)"； 计算球体的体积，可以在D4单元格中输入以下公式：“=依3人3)* (4* PI())) /3”。
图812、POWER函数：此函数用来计算乘幕。如图9,首先在单元中输入底数和指数，然后在 D3中输入以下公式："=POWER(B3,C3)"。
图913、PRODUCT函数：此函数可以对所有的以参数形式给出的数字相乘，并返回乘积。例如： 某企业2005年度贷款金额为10000()元，利率为1.5%,贷款期限为12个月。如图10所示， 直接在单元格E4中输入以下公式："=PRODUCT(B4,C4,D4)“。
图1014、RADIANS函数：此函数是用来将弧度转换为角度的。可以在C3单元格中输入以下公 式：“=RADIANS (B3)”。
15、RAND函数：此函数可以返回大于等于0及小于I的均匀分布随机数，每次计算工作 表时都将返回一个新的数值。如果要使用函数RAND生成一个随机数，并且使之不随单元 格的计算而改变，可以在编辑栏中输入"=RAND0”，保持编辑状态，然后按［玲］键，将公 式永久性地改为随机数。例如：在全班50名同学中以随机方式抽出20名进展调查，如图 11，在单元格中输入开场号码以及完毕号码，然后在单元格B4中输入以下公式： "=1+RAND()*49"。
图1116、ROUND函数：此函数为四舍五入函数。如图12,例如：将数字“12.3456”按照指定的 位数进展四舍五入，可以在D3单元格中输入以下公式："=ROUND(B3,C3)“。
17、ROUNDDOWN函数：此函数为向卜.舍入函数。例如：出租车的计费标准是：起步价为 5元，前10公里每一公里跳表一次，以后每半公里就跳表一次，每跳一次表要加收2元。 输入不同的公里数，如图13所示，然后计算其费用。可以在C3单元格中输入以下公式： "=IF(B3<=I(),5+ROUNDDOWN(B3,0)*2,20+ROUNDDOWN((B3-IO)*2,0)*2)"。
图1318、ROUNDUP函数：此函数为向上舍入函数。例如：现在网吧的管理一般是采用向上舍 入法，不满一个单元按照一个单位计算。现假设每30分钟计价0.5元，请计算如图14中所 示的上网所花费的费用。1)计算上网天数：首先在单元格C3中输入以下公式：“=B3-A3”； 2)计算上网分钟数：上网分钟数实际上就等于上网夭数乘以60再乘以24,所以应在单元 格D3中输入以下公式：“=C3*60*24”； 3)计算计费时间：本例中规定每30分钟计费-•次, 不满30分钟以30分钟计价，所以应在单元格E3中输入以下公式:“=ROUNDUP(D3/30,0)”； 4)计算上网费用:在单元格G3中输入以下公式：“=E3*F3"。
图1419、SUBTOTAL函数：使用该函数可以返回列表或者数据库中的分类汇总。通常利用［数 据］-［分类汇总］菜单项可以很容易地创立带有分类汇总的列表。
Func(ion_num
函数返回值
Function_num
函数返回值
Funclion_num
函数返回值
1
Average
5
Min
9
Sum
2
Count
6
Product
10
Var
3
Counta
7
Sldev
11
warp
4
max
8
Stdcvp

例如某班局部同学的考试成绩如图15, 1)显示最低的语文成绩：首先在单元格B9中输入“显 示最低的语文成绩”的字样，然后在单元格E9中输入以下公式："=SUBTOTAL(5,C3:C7)"； 2)显示最高的数学成绩：首先在单元格B10中输入“显示最高的数学成绩”的字样，然后
在单元格E10中输入以下公式："=SUBTOTAL(4,D3沙)"。
图1520、计算库存量和奖金：假设某公司在月底要根据员工的业绩发放工资并进展产品的库存统 计，本例中规定员工的根本工资为600元，奖金按照销售业绩的8%提成，总工资等于根本 工资与奖金之和。如图16, 1)在工作表中输入相应的数据信息；2)计算“现存库量”：在 单元格C15中输入以下公式：“=C14-SUM(C3:C9)" ； 3)计算“销售业绩”：在单元格G3 中输入以下公式：“=SUMPRODUCT(C3:F3,$CS13.F$13)”,函数 SUMPRODUCT 是计算数组 C3 : F3与数组$C$13 . F$13乘积的和，用数学公式表示出来就是： “=10*3050.5+10* 1560.99+5*4489.9+2()*2119”； 4)计算奖金：奖金是按照销伯:业绩的 8%提 成得到的，这样计算出来的结果可能会是小数，不好找零钱，所以这里采用向上舍入的方式 得到整数，在单元格H3中输入以下公式："=ROUNDUP(G3*8%,0)" ； 5)计算总工资：巾 于总工资=根本工资+奖金，所以在单元格J3中输入以下公式："=SUM(H3:I3)"。
图1621、计算工资和票面金额：假设某公司的销售人员的销售情况如图17所示，按照销售业绩 的5%计算销售提成，下面需要结合上例中的函数来计算销售人员的销售业绩以及奖金工资， 然后再计算出发放工资时需要准备的票面数量。1)计算销侣业绩：在单元格H13中输入以 下公式："=SUMPRODUCT(C3:G3,$CS11.G$I1)" ； 2)计算提成：在本例中假设提成后出现 小于1元的金额那么舍入为1,所以需要使用ROUNDUP函数，在单元格13中输入以下公 式：“=ROUNDUP(H3*5%,0)" ； 3)计算工资：在单元格K3中输入以下公式："=I3+J3”； 4) 计算100元的面值：在单元格L3中输入以下公式："=INT(K3/$L$2)“ ； 5)计算50元的面 值：在单元格M3中输入以下公式："=INT(MOD(K3,$L$2)/$M$2)“，此公式是使用MOD 函数计算发放“MOD(K3,SLS2)”张100元后剩下的工资，然后利用取整函数INT得到50元 票面的数量；6 )计算10元的面值：在单元格N3中输入以下公式： “=INT(MOD(K3,SMS2)/$N$2)" ； 7)计算5元的面值：在单元格03中输入以下公式： “=INT(MOD(K3,$N$2)/$O$2)“ ； 8)计算1元的面值：在单元格P3中输入以下公式： “=INT(MOD(K3,$O$2)/$P$2)〃 。
图1722、DATE函数：在实际工作中经常会用到此函数来显示口期。例如：如图18,在单元格 中输入相应的年、月和图书馆日等信息，然后在单元格E3中输入以下公式： “=DATE(B3,C3,D3)”。
图1823、DATEIF函数：假设有两个日期一一开场日期和截止日期，那么可以利用DATEIF函数 来计算它们之间相差的年数、月数或者天数等。如图19,在单元格D3中输入以下公式： “=DATEDIF(B3,C3,”y”)"。
图1924、DAYS360函数：该函数计算两个日期之间的天数，在财务中经常会用到，如果财务系 统是基于•年12个月并且每月30天，可以使用该函数帮助计算借款天数或者支付款项等。 例如：某企业不同时间的贷款如图20所示，然后利用DAYS360函数来计算其借款的时间， 并且计算出还款利息。1 )计算“借款天数"：在单元格D3中输入以下公式： “=DAYS36()(B3,C3)” ； 2)计算“还款利息〃:在单元格G3中输入以下公式:“=D3*E3*F"。 图20
25、WEEKDAY函数：使用此函数可以返回某个日期为星期几。语法：WEEKDAY (serial_number,retum_type):其中参数serial_number代表要查找的那一天的日期,参数rcturn_typc为确定返回值类型的数字，详细内容如下表：
参数值函数返回值1或者省略返回数字1〔星期日)到数字7〔星期六)之间的数字。
2 返回数字1〔星期一)到数字7〔星期日)之间的数字。
3 返回数字0〔星期一)到数字6〔星期日)之间的数字。
例如：计算当前日期是星期几：如图21所示，在单元格B3中输入计算当前日期的公式： “=WEEKDAY(B3,2)”。
图2126、WEEKNUM函数：使用此函数可以计算一年中的第儿周。例如：2006年6月9日是星 期五，下面利用WEEKNUM函数计算在参数不同的情况下返回的周数。如图22所示，在 单元格B3中输入计算当前日期的公式：“=WEEKNUM(B3,C3)”。
图2227、WORKDAY函数：使用此函数可以返回某个日期〔起始日期)之前或之后相隔指定工 作日的某一日期的日期值，工作日不包括周末和专门指定的日期。假设某出版社要求某个编 辑从2006年3月1日起开场写稿，利用80天将其完成〔其中不包括三天节假日)，此时可 以利用WORKDAY函数计算出完成日期。如图23所示，在单元格中输入上述信息，然后 在单元格C7中输入以下公式："=WORKDAY(C2,C3,C4:C6)”。
图2328、计算年假天数和工龄补贴：假设某公司规定，员工任职满1年的开场有年假，第1至5
年每年7天，第6年开场每年10天。截止到2005年6月9日，以工龄计算每年补贴100 元，任职缺乏一年的按每人50元计算。如图24所示：1)首先在工作表中输入数据信息， 然后根据公司规定的内容在单元格F5中输入以下公式： "=IF(DATEDIF($D5,TODAY(),”y”)<l,” 入 职 不 够 一 年 ”,IF(DATE(CS2,MONTH($D5),DAY($D5))>TODAY(),” 今 年 没至 U期 ”,IF(DATEDIF($D5,TODAY(),”y”)v6,7,l())))”,以此可以计算出员工的休假天数；2)在单元 格 G5 中 输 入 以 下 公 式： "=lF(DATED【F(SD5,DATE($C$2,6,9),”y”)>=l,DATEDIF($D5,DATE($C$2,6,9),”y”)*10(),50)" ,以此可计算出员工的工龄补贴。
图2429、计算火车站存放包裹费用：在火车站存放包裹是按小时数收费的。有些按整小时计数, 有些按半小时计数，没有超过半小时的以半小时计，半小时以上一小时以内的按一小时计。 同时包裹的大小不同收费也不同，在本例中假设大的每小时6元，中型的每小时4元，小型 的每小时2元，计算在火车站存放包裹的费用。如图25所示：1)计算存放天数：首先输入 相关的信息，然后在单元格 E4 中输入以下公式： “=IF(TIME(HOUR(C4),MINUTE(C4),SECOND(C4))>TIME(HOUR(D4),MINUTE(D4),SECO ND(D4)),DATE(YEAR(D4),MONTH(D4),DAY(D4))-DATE(YEAR(C4),MONTH(C4),DAY(C4) )-l,DATE(YEAR(D4),MONTH(D4),DAY(D4))-DATE(YEAR(C4),MONTH(C4),DAY(C4)))", 此时可计算出所有型号的包裹存放的天数，在此公式中用到了 IF函数，函数中的条件为 “TIME(HOUR(C4),MINUTE(C4),SECOND(C4))>TIME(HOUR(D4),MINUTE(D4),SECOND( D4)”,它是用来判断取走时间是否超过了存放时间，如果条件为真那么表示还没有超过一 天， 那 么 存 放 的 天 数 就 是 “DATE(YEAR(D4),MONTH(D4),DAY(D4))-DATE(YEAR(C4),MONTH(C4),DAY(C4))-1”，即 走取的日期减去存放的日期再减1，如果时间超过了，那么存放的天数就是 “DATE(YEAR(D4),MONTH(D4),DAY(D4))-DATE(YEAR(C4),MONTH(C4),DAY(C4))”,即 取走的日期与存放时的日期之差；2)计算存放小时数：在单元格F4中输入以下公式： "=IF(TIME(HOUR(C4)、MINUTE(C4),SECOND(C4))>TIME(HOUR(D4),MINUTE(D4),SECO ND(D4)),HOUR(1-TIME(HOUR(C4),MINUTE(C4),SECOND(C4))+TIME(HOUR(D4),MINUT E(D4),SECOND(D4))),HOUR(TIME(HOUR(D4),MINUTE(D4),SECOND(D4))-TIME(HOUR( C4),MINUTE(C4),SECOND(C4))))",此公式中的IF函数中的条件与计算天数时的条件是一 样的，也是判断取走时间是否超过了存放时间，如果没有超过小时数那么为 “TIME(HOUR(C4),MINUTE(C4),SECOND(C4))>TIME(HOUR(D4),MINUTE(D4),SECOND( D4))”,其中“TIME(HOUR(C4),MINUTE(C4),SECOND(C4)”表示存放时间的序列数，其中 “TIME(HOUR(D4),MINUTE(D4),SECOND(D4)"表示取走时间的序列数。再通过加减计算 得到小时数，如果超过了 小时数那么为 “HOUR(TIME(HOUR(D4),MINUTE(D4),SECOND(D4))-TIME(HOUR(C4),MINUTE(C4),SEC 0ND(C4)))”,即直接用取走时间减去存在时间，取小时数；3)计算存放分钟数：在单元格 G4中 输 入 以 下 公 式：
“=IF(TIME(HOUR(C4),MINUTE(C4),SECOND(C4))>TIME(HOUR(D4),MINUTE(D4),SECO ND(D4)),MINUTE(1-TIME(HOUR(C4),MINUTE(C4),SECOND(C4))+TIME(HOUR(D4),MIN UTE(D4),SECOND(D4))),MINUTE(TIME(HOUR(D4),MINUTE(D4),SECOND(D4))-TIME(H OUR(C4),MINUTE(C4),SECOND(C4))))“,此时即可计算出所有型号的包裹存放的分钟数， 其公式形式和计算小时数的公式相似，只是将HOUR换成了 MINUTE,其判断条件和前面 的一样，如果取走时间没有超过存放时间，分钟数那么为 “MINUTE(1-TIME(HOUR(C4),MINUTE(C4),SECOND(C4))+TIME(HOUR(D4),MINUTE(D4) ,SECOND(D4)))"。 如果超过了， 分钟数那么为 “MINUTE(TIME(HOUR(D4),MINUTE(D4),SECOND(D4))-TIME(HOUR(C4),MINUTE(C4),S ECOND(C4)))”,即直接用取走时间减去存放时间，取分钟数；4)计算存放的累计小时数： 在单元格H4中输入以下公式:“=E4*24+F4+IF(G4=0,0,IF(G4v=30,0.5,1))”,在该公式中, “E4*24”表示将天数转换为小时数，在将分钟转换为小时数时，使用IF函数来判断分钟数的 范围，假设分钟数小于等于30那么返回0.5小时，否那么返回1小时，然后将所有的小时 数相加即可得到累计小时数；5)计算存放总费用：在单元格J4中输入以下公式：“=I4*H4”, 此时即可计算出存放包裹的费用。
图2530、AND函数：当所有参数的逻辑值为真时，AND函数的返I可值为TRUE；只要有一个参 数的逻辑值为假，该函数的返回值那么为FALSEo例如：假设有•组民意调查数据或者调 查结果，如图26所示，下面根据各个年龄段〔18〜34、35〜49、50〜64和65以上)对数 据进展分类，以判断出各个年龄段的调查结果。1)统计年龄在18〜34岁之间的人的调查结 果，在单元格E7中输入以下公式:“=IF(AND(C7>=18,C7v=34),D7,””)"，在该公式中使用 AND函数判断单元格C7中的值是否在18〜34岁之间，然后根据返回的逻辑值再利用IF 函数得到结果，即如果为真那么返回单元格D7中的值，否那么返回空值；2)统计年龄在 35〜49岁之间的人的调查结果，在单元格F7中输入以下公式： "=IF(AND(C7>=35,C7〈=49),D7,””)" ； 3)统计年龄在65岁以上的人的调查结果，在单元格 H7 中输入以下公式："=IF(AND(C7>=50,C7<=64),D7,””)"。
图2631、OR函数：判断逻辑值并集的计算结果，在所有的参数中只要有一个逻辑值为TRUE, 该函数的返回值即为TRUE。例如某企业的员工姓名和出生年份两列值，如图27所示，然 后根据输入的年份判断员工中是否有这一年出生的人，并且统计出共有几个。1)在单元格 D3中输入判断值“1975”，即判断是否有1975年出生的人，然后在单元格E3中输入以下公 式：“{=OR(D3=C3:C8}”,在该公式中，表示将D2单元格中的值与数据区域“C3:C8”中的 每一个值作比较，判断是否相等。如果任何一人比较结果为真，函数OR那么返M TRUE, 也就是D3单元格中的值位于这个列表中。由于是在一个数组中查找是否存在某个指定的值， 所以公式要以数组的形式输入，输入公式后要按[Ctrl]+[Shifl]+[Enter]组合键完成；3)计算 1975年出生的人数，在单元格E3中输入以下公式：“{=SUM(IF(D3=C3:C8,l,0)}”,在该公 式中先使用IF函数将单元格D3中的值与数据区域“C3:C8”中的每一个值进展比较，如果两 个值相等那么返回1,否那么返回0。然后利用SUM函数对所有的返回值求和，最后得到 的数据就是“1975”出现的次数，即有几个人是1975年出生的。该公式要以数组公式的形式 输入。
图2732、ADDRESS函数：该函数使用方法如图28所示。
图2833、AREAS函数：该函数使用方法如图29所示。
图2934、CHOOSE函数：例如评定学生成绩，利用该函数可以评定销仍人员的业务能力，还可 以返回成绩的档次以及是否及格等，其计算方法都是一样的。下面以学生成绩表为例看一下 CHOOSE函数的应用方法。1)首先在工作表中输入如图30所示的学生成绩，然后在单元格 F3中输入以下公式："=SUM(C3:E3)/3”，此时即可计算出学生的平均成绩；2)利用CHOOSE 函数计算成绩名次，在 G3单元格中输入以下公式： “=CHOOSE(IF(F3>=90,1