做中学 学中做-Excel 2010案例教程-模块06 函数的应用-制作考试成绩分析表电子课件（电子教案）

做中学学中做-Excel2010案例教程-模块06函数的应用——制作考试成绩分析表电子课件（高教版）模块06函数的应用——制作考试成绩分析表你知道吗？

为了满足财务管理工作中各种数据处理的需求，Excel提供了大量的函数。利用这些函数不但可以解决许多数据处理方面的问题，以节省大量的时间，而且可以简化繁琐的计算过程，使工作变得轻松。应用场景：

在考试过后，成绩

分析师教师的重要工

作，同时也是十分繁

琐复杂的工作，在

Excel中用可以利用

函数的功能来轻松完

成这一工作。如图所

示，是利用Excel2010

的函数功能制作的

考试成绩分析表。背景知识

考试是教学过程中的一个重要环节，教师的教，目的是为了帮助学生更好地学，教学效果的好坏，主要是从学生身上反映出来。对于教师来说在教学过程中可以通过对考试结果的分析，了解学生对教材、教法的使用情况，以便调整教学内容和教学方法，改进教与学的关系，以适应学生的特点，满足学生的需求。对于学生来说学习成绩的分析可以对自己有一个比较全面清晰的认识，认识到自己的优势与不足，实事求是地反思学习成败的原因，有利于改进学习方法。

在利用考试分数或考试结果进行教育管理时，决不能将分数绝对化，不能仅凭考试分数的高低来评估教育质量及学生的学习情况。应对考试的结果给予科学的解释，解释分数不仅要有整体观念，即将个别学生的成绩与全体学生的成绩相比较，以分高下；还要有系统的观念，即要考虑到学生原有的基础水平，判断该学生有无进步与提高。设计思路

在制作考试成绩分析表的过程中，主要应用到使用函数来计算数据，制作考试成绩分析表的基本步骤可分解为：

（1）函数概述

（2）创建函数项目任务6-1函数概述

在应用函数之前我们首先来了解一下函数。动手做1了解函数的定义

在Excel2010中所提的函数其实是一些预定义的公式，它们使用一些称为参数的特定数值，按特定的顺序或结构进行计算。用户可以直接用它们对某个区域内的数值进行一系列运算，如分析和处理日期值和时间值、确定贷款的支付额、确定单元格中的数据类型、计算平均值、排序显示和运算文本数据等等。例如，SUM函数将对单元格或单元格区域中的数据进行加法运算。动手做2了解函数的基本语法Excel2010函数由三部分组成，即函数名称，括号和参数，其结构为以等号“＝”开始，后面紧跟函数名称和左括号，然后以逗号分隔输入参数，最后是右括号。其语法结构为：函数名称（参数1，参数2，……参数N）。动手做3了解函数的分类

在Excel2010中，系统提供了12类函数，这些函数按功能来说分别为数据库函数、日期与时间函数、工程函数、财务函数、信息函数、逻辑函数、查询和引用函数、数学和三角函数、统计函数、文本函数、多维数据集函数、兼容性函数。

项目任务6-2创建函数

在公式中合理地使用函数，可以大大节省用户的输入时间，简化公式的输入。动手做1应用求和函数

由于用户对求和函数比较熟悉，知道求和函数的语法为SUM（Number1，Number2……），因此这里可以采取直接输入的方式创建函数。在考试成绩分析表中应用求和函数的具体操作步骤如下：

（1）选中“G6”单元格。

（2）首先输入“=”号。

（3）然后输入SUM（C6:F6）。

（4）单击编辑栏上的“输入”按钮，即可得出求和的结果。

（5）选中“G6”单元格，将鼠标移到“G6”单元格的右下角的填充柄处，当鼠标变成

状时向下拖动鼠标。

（6）当拖动到目的位置后松开鼠标，此时单元格“G6”中的函数被复制到了填充柄拖过的单元格中。

项目任务6-2创建函数

动手做2应用平均值函数

在考试成绩分析表中应用平均值函数的具体操作步骤如下：

（1）选中“H6”单元格。

（2）在“公式”选项卡下的“函数库”组中单击“自动求和函数”右侧的下三角箭头，打开“自动求和”列表，

（3）在“自动求和”列表中单击“平均值”选项，则在“H6”单元格中出现平均值函数语法“=AVERAGE(C6:G6)”。

（4）将函数中的参数“G6”修改为“F6”，单击编辑栏上的“输入”按钮，即可得出平均值的结果。

（5）选中“H6”单元格的填充柄向下拖动鼠标。

项目任务6-2创建函数动手做3应用COUNTIF函数COUNTIF函数计算区域中满足给定条件的单元格的个数，他的语法为COUNTIF（range,criteria），Range为需要计算其中满足条件的单元格数目的单元格区域，Criteria为确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本。

例如，条件可以表示为10、"10"、">10"或"文本"。

在工作中经常需要统计满足条件的单元格的数量，如在考试成绩分析表中需要统计出不及格的科数，具体操作步骤如下：

（1）选中“I6”单元格。

（2）在“公式”选项卡下的“函数库”组中单击“插入函数”按钮，打开“插入函数”对话框。

（3）在“或选择类别”下拉列表中选择“统计”选项，在“选择函数”列表框中选择“COUNTIF”。

（4）单击“确定”按钮，打开“函数参数”对话框。

（5）在“Range参数框”中输入“C6:F6”，在“Criteria参数框”中输入“<60”。

（6）单击“确定”按钮即可在“I6”单元格中得到统计结果。

（7）选中“I6”单元格的填充柄向下拖动鼠标。

项目任务6-2创建函数动手做4应用RANK函数RANK函数最常用的是求某一个数值在某一区域内的排名。RANK函数语法形式：RANK(number,ref,[order])

函数名后面的参数中number为需要求排名的那个数值或者单元格名称（单元格内必须为数字），ref为排名的参照数值区域，order的为0和1，默认不用输入，得到的就是从大到小的排名，若是想求倒数第几，order的值请使用1。

在Excel2010中RANK函数有两种，一种是RANK.AVG，一种是RANK.EQ，RANK.AVG函数的不同之处在于，对于数值相等的情况，返回该数值的平均排名。而作为对比，RANK.EQ函数对于相等的数值返回其最高排名。动手做5应用IF函数

在逻辑函数中最常用到的为条件函数IF，它可以执行真假值判断，根据逻辑计算的真假值，返回不同结果，他的语法为IF(logical_test,value_if_true,value_if_false)

其中logical_tes表示要选取的条件，value_if_true表示条件为真时返回的值，value_if_false表示条件为假时返回的值。函数IF可以嵌套七层，用value_if_false及value_if_true参数可以构造复杂的检测条件。

项目任务6-2创建函数动手做6应用求最大值函数

在考试成绩分析表中应用求最大值函数的具体操作步骤如下：

（1）选中“C25”单元格。

（2）在“公式”选项卡下的“函数库”组中单击“自动求和函数”右侧的下三角箭头，打开“自动求和”列表。

（3）在“自动求和”列表中单击“最大值”选项，则在“C25”单元格中出现最大值函数语法“=MAX(C6:C24)”。

（4）单击编辑栏上的“输入”按钮，即可得出最大值的结果。

（5）选中“C25”单元格的填充柄向右拖动鼠标。动手做7应用求最小值函数

（1）选中“C26”单元格。

（2）在“公式”选项卡下的“函数库”组中单击“自动求和函数”右侧的下三角箭头，打开“自动求和”列表。

（3）在“自动求和”列表中单击“最小值”选项，则在“C26”单元格中出现最小值函数语法=MIN(C6:C25)。

（4）将函数中的参数“C25”修改为“C24”，单击编辑栏上的“输入”按钮，即可得出最小值的结果。

（5）选中“C26”单元格的填充柄向右拖动鼠标，

项目拓展其他常用函数的应用动手做1求某项投资的未来值FV

在进行财务管理的工作中，经常会遇到要计算某项投资的未来值的情况，此时如果利用Excel提供的FV函数进行计算，则可以帮助用户进行一些有计划、有目的、有效益的投资。FV函数是基于固定利率及等额分期付款方式，它返回的是某项投资的未来值。其语法形式为FV(rate、nper、pmt、pv、type)。其中rate为各期利率，为一个固定值，nper为总投资（或贷款）期，即该项投资（或贷款）的付款期总数，pmt为各期所应付给（或得到）的金额，其数值在整个年金期间（或投资期内）保持不变，通常pmt包括本金和利息，但不包括其它费用及税款，pv为现值，或一系列未来付款当前值的累积和，也称为本金，如果省略pv，则假设其值为零，type为数字0或1，用以指定各期的付款时间是在期初还是期末，1为期初，0为期末，如果省略t，则假设其值为零。

项目拓展其他常用函数的应用动手做2求贷款分期偿还额PMTPMT函数是基于固定利率及等额分期付款方式，它返回的是投资或贷款的每期付款额。PMT函数可以计算为偿还一笔贷款，要求在一定周期内支付完时，每次需要支付的偿还额，也就是平时所说的“分期付款”。比如借购房贷款或其它贷款时，可以计算每期的偿还额。PMT函数的语法形式为：PMT(rate,nper,pv,fv,type)其中，rate为各期利率，是一个固定值，nper为总投资（或贷款）期，即该项投资（或贷款）的付款期总数，pv为现值，或一系列未来付款当前值的累积和，也称为本金，fv为未来值，或在最后一次付款后希望得到的现金余额，如果省略fv，则假设其值为零（例如，一笔贷款的未来值即为零），type为0或1，用以指定各期的付款时间是在期初还是期末。如果省略type，则假设其值为零。

项目拓展其他常用函数的应用动手做3求某项投资的现值PVPV函数是用来计算某项投资的现值。年金现值就是未来各期年金现在的价值的总和。如果投资回收的当前价值大于投资的价值，则这项投资是有收益的。PV函数的语法形式为：PV(rate,nper,pmt,fv,type)。其中Rate为各期利率，Nper为总投资（或贷款）期，即该项投资（或贷款）的付款期总数。pmt为各期所应支付的金额，其数值在整个年金期间保持不变。通常pmt包括本金和利息，但不包括其他费用及税款。fv为未来值，或在最后一次支付后希望得到的现金余额，如果省略fv，则假设其值为零（一笔贷款的未来值即为零）。Type用以指定各期的付款时间是在期初还是期末。

项目拓展其他常用函数的应用动手做4直线折旧函数

企业中不同的折旧方法直接影响到企业利润的大小，折旧费用低估，则净利高估；反之，折旧费用高估，则净利低估。因此在为企业进行固定资产投资分析时，选择不同的折旧方法是投资决策的一个重要因素之一，为此，Excel为方便财务管理人员进行决策分析时，提供了一些折旧函数。

直线折旧法是计算折旧最常用的一种方法，其每期折旧额的公式为：年折旧额＝（原始成本－预计净残值）/使用年限，使用直线折旧法计算折旧时，每期的折旧额都一样。

此函数的语法为：SLN（cost,salvage,life）

功能：返回某项固定资产每期按直线折旧法计算的折旧数额。所有的参数值必须是正数。否则将返回错误值#NUM!。

在函数中各参数的意义如下：Cost为固定资产的原始成本。Salvage为固定资产报废时的预计净残值。Life为固定资产可使用年数的估计数。

项目拓展其他常用函数的应用动手做5年数总和法折旧函数

年数总和法是一种加速折旧方法，它以固定资产的原始成本减去预计残值后的余额乘以一个逐年递减的分数，作为该期的折旧额。这个分数的分母是固定资产使用年限的各年年数之和，分子是固定资产尚可使用年限。假设使用年限为Life，则递减分数的分母为：Life+(life-1)+(life-2)+……+1=life(life+1)/2

自第一期至第life期分子分别为life，life-1，life-2……，1。

年折旧额的计算公式为：

年折旧额＝（原始成本－预计净残值）×尚可使用年限/使用年限之和

在利用年数总和法来计算折旧额时，是使用年数总和法函数SYD（）来计算的。函数语法为：SYD（cost,salvage,life,per）

功能：返回某项固定资产某期间的按年数总和法计算的折旧数额。其所有的参数都应为数，否则将返回错误值#NUM!。

在函数中各参数的意义如下：Cost为固定资产的原始成本。Salvage为固定资产报废时的预计净残值。Life为固定资产可使用年数的估计数。Per为指定要计算第几期折旧数额。

Life与Per参数应采用相同的单位，且per应小于或等于life。

项目拓展其他常用函数的应用动手做6与函数AND

语法：AND（logical1，logical2，…）

功能：逻辑“与”函数，当所有参数的逻辑值均为TRUE时，才返回TRUE值，如果有任何一个参数的逻辑值为FALSE，则返回FALSE。

说明：参数可为1到14，这些参数都是值为逻辑值的表达式。如果所指定的范围中不含逻辑值，AND函数将返回错误值#VALUE!。。动手做7或函数OR

语法：OR（logical1，logical2，…）

功能：逻辑“或”函数，只要所有参数的逻辑值有一个为TRUE，其返回TRUE值，否则将返回FALSE。

说明：参数可为1到14，这些参数都是值为逻辑值的表达式。如果所指定的范围中不含逻辑值，OR函数将返回错误值#VALUE！。动手做8非函数NOT

语法：NOT（logical）

功能：逻辑“非”函数，改变logical参数的逻辑值。

说明：如果logical为TRUE，其返回FALSE，如果logical为FALSE，其返回TRUE。

知识拓展动手做1公式返回的错误信息

在Excel2010工作表的单元格中输入公式以后，如果输入的公式不符合格式或者其它要求，就无法显示运算的结果，该单元格中会显示错误值信息，如“#####！”、“#DIV/0！”、“#N/A#”、“NAME？”、“#NULL！”、“#NUM！”、“#REF！”、“#VALUE！”。了解这些错误值信息的含义可以帮助用户修改单元格中的公式。表列出了Excel2010中的错误值及其含义。知识拓展动手做2保护单元格中的公式

如果单元格中的数据是公式计算出来的，那么当选定该单元格后，在编辑栏上将会显示出该数据的公式。如果用户工作表中的数据比较重要，可以将工作表中单元格中的公式隐藏，这样可以防止其他用户看出该数据是如何计算出的。

对工作表中的公式进行保护，具体步骤如下：

（1）选中要保护的单元格或单元格区域。

（2）单击“开始”选项卡下“单元格”组中的“格式”按钮，在打开的下拉列表中选择“设置单元格格式”选项，打开“单元格格式”对话框，单击“保护”选项卡。

（3）在对话框中如果选中了“锁定”复选框，则工作表受保护后，单元格中的数据不能被修改；如果选中了“隐藏”复选框，则工作表受保护后，单元格中的公式被隐藏。

（4）单击“确定”按钮。

（5）单击“审阅”选项卡下“更改”组中的“保护工作表”按钮，打开“保护工作表”对话框。选中“保护工作表及锁定的单元格内容”复选框，单击“确定”按钮，对工作表设置保护。

设置了隐藏功能后，在选中含有公式的单元格，则不显示公式。

知识拓展动手做3查看当前工作表中的所有公式

利用Excel2010中的“公式审核”工具可以快速的显示出当前工作表中所有的公式，显示工作表中的所有公式的具体操作步骤如下：

（1）切换到要显示公式的工作表中，单击“公式”选项卡，在“公式审核”选项组中单击“显示公式”按钮，即可将工作表中所有公式显示出来。

（2）如果要重新显示出数值，再次单击“显示公式”按钮即可。动手做4追踪引用单元格

所谓追踪引用单元格是指查看当前公式是引用哪些单元格进行计算的。当公式有错误值时，通过该功能也可辅助查找公式错误原因。

追踪引用单元格的具体操作步骤如下