电子表格的公式和函数教案.docx

电子表格的公式和函数教案.docx

《电子表格的公式和函数教案.docx》由会员分享，可在线阅读，更多相关《电子表格的公式和函数教案.docx（17页珍藏版）》请在冰豆网上搜索。

电子表格的公式和函数教案

项目四：

电子表格处理软件的使用

任务三：

学期成绩汇总表的制作

任务描述

紧张的期末考试结束了，成绩要尽快的统计出来，以便于学生放假的时候可以带回家向家长汇报，班主任的工作比较繁重，所以班长和学习委员被留了下来，帮助老师统计成绩。

在各科成绩都汇总过来以后，他们要负责制作几张表格，分别是每门课程的单科成绩总评表和班级的学生成绩汇总表。

在单科成绩总评表中，要计算每门课程的总评成绩，它们的计算公式是（期中成绩×30%+期末成绩×40%+平时成绩×30%）；在班级学生成绩汇总表中，要计算出每门课程相加的总分和平均分，再根据平均分计算出本学期该学生的学习等次，学习等次的计算方法为（各科成绩平均分大于等于80分，等次为优；各科成绩平均分大于等于60分小于80分的，等次为合格；各科成绩平均分小于60分的，等次为不合格）。

学习目标

知识目标

能力目标

素质目标

1.掌握Excel中公式的输入与计算的方法。

2.掌握公式复制的方法。

3.知道单元格地址引用的原理。

4、掌握一些常用函数的使用方法。

1、能利用Excel制作成绩表。

2、能利用公式在表格中进行计算。

3、能利用函数计算表格中的数据。

1、培养学生信息化素养

2、培养学生团队协作能力

3、具有利用技术形成创新学习能力的意识

知识储备

知识点一：

手动输入公式

1、Excel的公式

Excel公式是对工作表中数值执行计算的等式，公式以“＝”或“+”号开头。

“＝”后面可以由4种元素组成：

运算符、单元格引用、数值或文本、函数。

公式中一律使用小括号，小括号可以有多层嵌套，与数学中的运算顺序相同。

2、Excel的运算符

运算符是作用是对公式中的各元素进行运算操作。

Excel的运算符有算术运算符、比较运算符、文本运算符、引用运算符。

（1）算术运算符：

算术运算符用来完成基本的数学运算，如加法、减法和乘法。

算术运算符有十（加）、一（减）、*（乘）、/（除）、%（百分比）、^（乘方）。

（2）比较运算符：

比较运算符用来对两个数值进行比较，产生的结果为逻辑值True（真）或False（假）。

比较运算符有=（等于）、>（大于）、>=（大于等于）、<=（小于等于）、<>（不等于）。

（3）文本运算符：

文本运算符"&"用来将一个或多个文本连接成为一个组合文本。

例如"Micro"&"soft"的结果为"Microsoft"。

（4）引用运算符：

引用运算符用来将单元格区域合并运算。

引用运算符为：

区域（冒号），表示对两个引用之间，包括两个引用在内的所有区域的单元格进行引用，例如，SUM（BI：

D5）。

联合（逗号），表示将多个引用合并为一个引用，例如，SUM（B5，B15，D5，D15）。

3、运算顺序

如果公式中同时用到了多个运算符，Excel将按下面的顺序进行运算：

如果公式中包含了相同优先级的运算符，例如公式中同时包含了乘法和除法运算符，Excel将从左到右进行计算。

如果要修改计算的顺序，应把公式需要首先计算的部分括在圆括号内。

公式中运算符的顺序从高到低依次为：

（冒号）、（逗号）、（空格）、负号（如一l）、%（百分比）、^（乘幂）、*和/（乘和除）、十和一（加和减）、&（连接符）、比较运算符。

操作步骤：

总评成绩的计算

（1）打开“单科成绩总评表”，打开“计算机基础总评成绩表1”表格。

（2）准备计算第一位同学的总评成绩，计算方法是“总评成绩＝期中成绩×30%+期末成绩×40%+平时成绩×30%”，所以我们先点选第一位同学总评成绩所对应的单元格“G4”，在其中输入“＝D4*0.3+E4*0.3+F4*0.4”，见图4－3－3。

注意，在输入单元格地址“D4”、“E4”、“F4”时，我们可以用点选该单元格的方式输入地址，这样可以有效地避免出错。

图4－3－3手动输入公式

（3）点选编辑栏上的输入按钮“√”或按回车键，完成公式的输入。

想一想、练一练

1．我们在输入公式中引用的数值时，不是直接输入数值，而是输入数值所在的单元格地址，这样做有什么好处吗？

直接输入数值可以吗？

。

2．使用刚才的方法，再计算下面五位同学的总评成绩。

知识点二：

公式的复制

通过上面的公式输入，我们可以计算我们想要的数据了。

为了提高输入的效率，Excel还可以对单元格中输入的公式进行复制。

公式复制的方法有两种：

1、方法一：

使用“复制”和“粘贴”命令，或“ctrl+C”和“ctrl+V”组合键。

2、方法二：

自动填充。

还记得前边学过的自动填充吗？

公式的复制也是可以使用自动填充的，这种方法强烈推荐！

操作步骤：

使用自动填充完成公式复制

1、打开“单科成绩总评表”，打开“计算机基础总评成绩表”表格。

2、在“G4”单元格输入总评成绩的计算公式，确定。

3、选定已经输入公式的“G4”单元格，将鼠标指向该单元格右下角的填充柄，当鼠标变成黑十字的时候，点住鼠标左键向下拖动，如图4－3－4所示，“G4”的公式被复制到了其它单元格中。

想一想、练一练

1、还记得我们在G4单元格中输入的公式吗？

“＝D4*0.3+E4*0.3+F4*0.4”，那么这个公式复制到“G5”单元格中还能计算出正确的结果吗?

2、自动填充公式以后，点选一个复制的公式，观察一下，公式还是原来的公式吗？

结合复制的源单元格地址和目的单元格地址，想一想为什么？

知识点三：

单元格地址的引用

细心的同学可能已经观察到了，公式在经过复制以后，里面单元格地址发生了变化，所以才能够计算出正确的结果。

那么为什么公式被复制以后，里面的单元格地址会变呢？

下面我们来学习一下Excel的单元格地址引用。

Excel的单元格地址引用有三种方式，分别为相对引用、绝对引用和混合引用。

1、相对引用：

就是直接使用单元格地址的方式，这是我们在输入公式时用的最多的一种地址的引用，例如直接在公式中使用“A1”、“C5”、“F9”等等。

这种地址在被引用以后，当公式被复制，公式中的地址就会发生相应的变化。

变化是如何进行的呢，我们要首先了解相对地址这个“相对”的含义。

例如：

我们在“C3”单元格中输入了公式“＝A2+B3”，这时候公式里的相对地址所相对的对象就是“C3”，也就是公式所在单元格地址。

现在我们把这个公式复制到了“D5”单元格，可以看到公式所在的单元格地址由“C3”变化到了“D5”，也就是相对的对象由“C3”变化到了“D5”，这个变化是列标加了1，行号加了2。

那么“D5”单元格公式里的所有相对地址都会发生相应的变化，列标加1，行号加2，也就是公式会变成“＝B4+C5”。

如图4－3－5和图4－3－6。

正是因为有了这个相对地址的引用，所以在单科成绩总评表中，我们才可以使用自动填充的功能快速的复制公式，完成所有总评成绩的计算。

2、绝对引用：

并不是所有公式复制的时候都需要地址发生相应变化的，在很多的时候，我们也需要地址固定不变，这时我们就要用到绝对地址了。

绝对地址的引用，是在相对地址列标和行号的前面加上“$”符号（数字键4的上排键），如：

“$A$1”、“$C$5”、“$F$9”等等。

如果公式中有这种单元格地址，那么无论你把公式复制到什么地方，被引用的地址都不会发生任何变化。

例如上面的那个实例，如果公式的地址是绝对引用，将“C3”单元格中的公式复制到了“D5”单元格，那么两个单元格中的公式还都会是“＝$A$2+$B$3”。

如图4－3－7和图4－3－8。

3、混合引用

混合引用分为列绝对、行相对或列相对、行绝对两种情况。

列绝对，行相对：

复制公式时，列标不会发生变化，行号会发生变化，单元格地址的列标前添加“$”符号，如“$A1”，“$C10”，“$B1：

$B4”。

行绝对，列相对：

复制公式时，行号不会发生变化，列标会发生变化，单元格地址的行号前添加“$”符号，如“A$1”，“C$10”，“B$1：

B$4”。

例如还是上面的那个实例，我们在“C3”单元格中输入的公式是“＝$A2+B$3”，那么公式复制到“D5”单元格以后会变成“＝$A4+C$3”。

如图4－3－9和图4－3－10。

【小贴士】

公式中无论使用何种地址，如果不是进行公式复制，而只是移动公式所在单元格，公式保持不变。

想一想、练一练

1、如果我们在“B4”单元格中输入了公式“＝A1+B2”，当把这个公式复制到“G8”单元格中以后，这个公式会发生怎样的变化？

如果“B4”单元格中的公式是“＝A$1+$B2”呢？

2、如果我们将上面“B4”单元格中的公式“＝A1+B2”复制到了“A3”单元格中，那么公式会变成什么样？

不知道了？

打开Excel做做看吧。

知识点四：

插入函数

通过学习上面的几个知识，我们已经完成了单科成绩总评表的工作，现在要制作班级学生成绩汇总表了。

在班级成绩汇总表中，我们要计算班级学生所有科目的总分、平均分和学习等次。

在这儿我们可以使用另一种公式输入的方法——函数。

公式的输入已经可以为我们的数据统计工作带来很大的便利了，但Excel为我们提供了一个更便利的工具，那就是函数。

Excel中的函数其实是一些预定义的公式，它们使用一些称为参数的特定数值按特定的顺序或结构进行计算。

使用函数功能，我们可以在Excel中很方便地进行各种数据的运算。

Excel函数一共有11类，分别是数据库函数、日期与时间函数、工程函数、财务函数、信息函数、逻辑函数、查询和引用函数、数学和三角函数、统计函数、文本函数以及用户自定义函数。

Excel的数据处理功能在现有的文字处理软件中可以说是独占鳌头，几乎没有什么软件能够与它匹敌。

在我们学会了Excel的基本操作后，是不是觉得自己一直局限在Excel的操作界面中，而对于Excel的函数功能却始终停留在求和、求平均值等简单的函数应用上呢？

难道Excel只能做这些简单的工作吗？

其实不然，函数作为Excel处理数据的一个最重要手段，功能是十分强大的，在生活和工作实践中可以有多种应用，我们甚至可以用Excel来设计复杂的统计管理表格或者小型的数据库系统。

如果同学们想要学习各种函数的用法，可以使用Excel所提供的帮助系统。

在这儿，我们先学习一些常用的函数。

1、“SUM”函数：

SUM函数用来返回某一单元格区域中数字、逻辑值及数字的文本表达式之和。

如果参数中有错误值或为不能转换成数字的文本，将会导致错误。

SUM函数的语法：

SUM（Number1，[Number2]，[Number3]，[Number4]……）

（1）Number为SUM函数的参数，SUM最少要有一个参数，中括号内表示为可选项。

（2）SUM函数的参数可以是数字、逻辑值、日期、单元格引用、区域引用等。

逻辑值“TRUE”和“FALSE”在计算时会分别被换为“1”和“0”。

当参数中有不能被转换为数字的字符时，系统会显示“#NAME”，表示公式中包含不可识别的文本。

例如：

SUM（4，5，6）等于15

SUM（3，true）等于4

SUM（B4：

E8），SUM（A2，D4，F6）

2、“AVERAGE”函数：

AVERAGE函数用来返回参数的算术平均值

AVERAGE函数的语法：

AVERAGE（Number1，[Number2]，[Number3]，[Number4]……）

（1）Number为AVERAGE函数的参数，SUM最少要有一个参数，中括号内表示为可选项。

（2）AVERAGE函数的参数可以是数字、逻辑值、日期、单元格引用、区域引用等。

逻辑值“TRUE”和“FALSE”在计算时会分别被换为“1”和“0”。

当参数中有不能被转换为数字的字符时，系统会显示“#NAME”，表示公式中包含不可识别的文本。

例如：

AVERAGE（3，4，5）等于4

AVERAGE（8，false，1）等于3

AVERAGE（B5：

D6），AVERAGE（A1，B4，F3，G6）

3、“COUNT”函数：

计算参数区域中包含数字的单元格的个数。

“COUNT”函数的语法：

COUNT（value1，value2，...）

（1）参数Value1,value2,...是包含或引用各种类型数据的参数，但只有数字类型的数据才被计数。

（2）函数COUNT在计数时，将把数值型的数字、逻辑值计算进去；但是错误值、空值、文字则被忽略。

例如：

COUNT（32，FF，FALSE）等于2

COUNT（B5：

D6），COUNT（A1，B4，F3，G6）

4、“IF”函数：

IF函数用来根据指定的条件来判断其“真”（TRUE）、“假”（FALSE），根据逻辑计算的真假值，从而返回相应的内容。

可以使用函数IF对数值和公式进行条件检测。

“IF”函数的语法：

IF（logical_test,value_if_true,value_if_false）

（1）logical_test为可以用来判断真假的逻辑表达式。

如“D4>=0”，“A1<>B3”。

（2）value_if_true是当逻辑表达式的值为“真”时函数显示的结果。

（3）value_if_false是当逻辑表达式的值为“假”时函数显示的结果。

例如：

IF（D4>=0,”正数”,”负数”），当D4单元格里的数值为5时，函数显示“正数”，当D4单元格里的数值为－4时，函数显示“负数”。

5、“RANK”函数：

RANK函数用来求某一个数值在某一区域内的排名。

“RANK”函数的语法：

RANK（number，ref，[order]）

（1）number为排名区域中的一个数值或单元格地址引用。

（2）ref：

排名区域

（3）order：

为0或1，排名顺序，如缺省，默认为0，意为降序排序；当为1时，意为升序排序。

例如：

（1）A列从A1单元格起，依次有数据80、98、65、79、65。

在B1中编辑公式RANK（A1，$A$1：

$A$5，0）

回车确认后，向下复制公式到B5单元格。

效果：

从B1单元格起依次返回值为2、1、4、3、4。

（2）还是以上的条件和操作，但B1中的公式为RANK（A1，$A$1：

$A$5，1）

则从B1单元格起依次返回值为4、5、1、3、1。

6、“ROUND”函数：

ROUND函数返回一个数值，该数值是按照指定的小数位数进行四舍五入运算的结果。

“ROUND”函数的语法：

ROUND（number，digits）

（1）number是一个数值或单元格地址的引用。

（2）digits为小数点后保留的位数。

例如：

ROUND（3.1415926，3）等于3.142

7、“SUMIF”函数：

SUMIF函数是根据指定条件对若干单元格、区域或引用求和。

“SUMIF”函数的语法：

SUMIF（range，criteria，sum_range）

（1）Range为条件区域，用于条件判断的单元格区域。

（2）Criteria是求和条件，由数字、逻辑表达式等组成的判定条件。

（3）Sum_range为实际求和区域，需要求和的单元格、区域或引用。

例如：

如图4－3－11所示，求报表中办公软件流量的总合。

选中F2单元格，输入公式：

=SUMIF（B2:

B19，E2，C2:

C19），输入公式完成后，直接按回车键，即可统计出办公软件栏目的总流量。

8、“COUNTIF”函数：

COUNTIF函数用来对指定区域中符合指定条件的单元格计数。

“COUNTIF”函数的语法：

COUNTIF（range，criteria）

（1）range为要计算其中非空单元格数目的区域。

（2）criteria是以数字、表达式或文本形式定义的条件。

例如：

求真空单元格个数：

COUNTIF（数据区，"="）

非真空单元格个数：

COUNTIF（数据区，"<>"）

逻辑值为TRUE的单元格数量：

COUNTIF（数据区，TRUE）

COUNTIF函数的用法比较多，具体可以参看：

9、“MAX”函数：

MAX函数用来在所有参数中返回一个最大值。

“MAX”函数的语法：

MAX（number1，number2，number3，……）

MAX函数的参数可以是数值、单元格引用和区域引用。

例如：

如果A1：

A5包含数字10、7、9、27和2，则：

MAX（A1：

A5）等于27

MAX（A1：

A5，30）等于30

10、“MIN”函数：

MIN函数用来在所有参数中返回一个最小值。

“MIN”函数函数的语法：

MIN（number1，number2，number3，……）

MIN函数的参数可以是数值、单元格引用和区域引用。

例如：

如果A1：

A5包含数字10、7、9、27和2，则：

MIN（A1：

A5）等于2

MAX（A1：

A5，30）等于2

操作步骤：

在班级成绩汇总表中完成总分和平均分的计算

1、打开“班级成绩汇总表”，打开“班级成绩汇总表”表格。

2、选择J5单元格后，在窗口左上角打开“公式”菜单，点击“fx”插入函数按钮，打开“插入函数”对话框。

如图4－3－12。

3、“插入函数”对话框的“选择函数”列表框中，选择“SUM”函数，打开“函数参数”对话框。

如图4－3－13。

4、在number1的输入框中选择C5：

I5区域，点击确定，一位同学的总分计算完成。

5、利用我们前边刚刚学到的自动填充的方式，完成其余同学的总分计算。

6、平均分的计算和总分计算操作差不多，在“班级成绩汇总表”表格中选择K5单元格，同样点击“fx”插入函数按钮，打开“插入函数”对话框，然后在“插入函数”对话框的“选择函数”列表框中，选择“AVERAGE”函数，打开“函数参数”对话框。

然后在number1的输入框中选择C5：

I5区域（注意不能把J5单元格选进去），点击确定，该同学的均分计算完成。

同样利用自动填充的方式，完成其余同学均分的计算。

计算结束后如图4－3－14。

操作步骤：

学生成绩等次的计算

1、成绩等次的计算要用到IF函数，但我们前边所学的IF函数只能有两种情况的输出值，而在我们的成绩汇总表中，成绩等次有“优秀”、“合格”和“不合格”三种情况，所以我们要用到函数的嵌套。

2、在“班级成绩汇总表”中点选L5单元格，点击“fx”插入函数按钮，打开“插入函数”对话框，然后在“插入函数”对话框的“选择函数”列表框中，选择“IF”函数，打开“函数参数”对话框。

3、在“Logical_test”的输入框中输入“K5>=80”

4、在“Value_if_true”的输入框中输入“优秀”

5、在“Value_if_false”的输入框中输入“IF（K5<60,”不合格”,”合格”）”，如图4－3－15。

6、点击确定，完成该同学成绩等次的计算。

7、使用自动填充功能，完成其他同学成绩等次的计算。

如图4－3－16。

【小贴士】

公式和函数中涉及到的字母，大小写通用；但是公式和函数中所出现的符号，必须为英文半角符号。

想一想、练一练

1、在上面计算成绩等次的时候，我们使用了IF函数的嵌套，IF函数的参数设置可以不和课文中的一样吗？

试试看！

2、利用教材提供的表格素材，同学们试着制作一张成绩汇总表，要求不但可以显示总分、均分和成绩等次，还可以显示每个科目的最高分和最低分。

拓展与提高

Excel中大量的公式函数可以应用选择，使用MicrosoftExcel可以执行计算，分析信息并管理电子表格或网页中的数据信息列表与数据资料图表制作，可以实现许多方便的功能，带给使用者方便。

Excel中函数的功能非常强大，我们在上面已经学习了几个常用函数的使用方法，但是要把所有的函数用法都讲出来那是不现实的，下面我们为同学们提供了两个Excel表格，按照要求完成以下操作，不会做吗？

查看Excel的帮助信息啊，实在不行，问问度娘吧！

任务描述：

1、打开素材中的“员工信息表”，先在“员工信息数据表”的C34单元格中计算28岁以上员工的数目，再在C35单元格中计算工龄10年以上的员工数目。

友情提醒，使用COUNTIF函数哦。

2、在“员工信息表”的“晋升考核表”中，计算员工晋升考核的排名，然后根据排名，决定晋升与否，排名前5的晋升，其他人不晋升。

友情提示，要使用RANK函数和IF函数。