项目六 公式与函数.docx
《项目六 公式与函数.docx》由会员分享，可在线阅读，更多相关《项目六 公式与函数.docx（21页珍藏版）》请在冰点文库上搜索。
项目六公式与函数
项目六公式与函数的使用
一、简案
课题
公式与函数的使用
课型
项目式教学
执教
许史萍
时间
2010.3.14
班级
0913
课时数
4
学习目标
掌握单元格、行和列的基本操作
熟练使用公式和函数进行数据运算
学会基本的数据统计
课前准备
PC机，OFFICE工具，电源，相关展示内容
教学步骤
教学内容
讲解示范
布置任务
实施任务
评价任务
小结
作业布置
展示课前准备的职工档案表
完成职工档案表的输入，并进行公式和函数的计算
学生根据要求进行操作，老师巡回指导
完成工作表输入的同时理解各种数据类型
对完成单元格、行和列的基本操作，公式和函数的使用等及时进行方法归纳，小结。
巩固训练本课所学。
完成实训报告
学习准备：
图4-10学生成绩统计表
观察上表,如图4-10所示,完成如下要求；
打开第一节中保存的Book1.XLS；
将Sheet1中的数据复制到Sheet2中；
在姓名列前插入一空列,在A1中输入序号（自动填充为1-13）
在F1和G1中输入总分和平均分（利用公式求出每个学生的总分和平均分）
在B15中输入最高分（利用函数求出各科最高分）
学习过程：
1、编辑单元格
编辑单元格包括对单元格及单元格内数据的操作.其中,对单元格的操作包括移动和复制单元格、插入单元格、插入行、插入列、删除单元格、删除行、删除列等；对单元格内数据的操作包括复制和删除单元格数据、清除单元格内容、清除单元格格式等。
（1）移动和复制单元格
①选定需要移动和复制的单元格；
②将鼠标指向选定区域，此时鼠标形状为向左上方的箭头；
③如果要移动选定的单元格，则用鼠标将选定区域拖到粘贴区域的左上角单元格，然后松开鼠标，Excsl将以选定区域替换粘贴区域中现有数据。
如果要复制单元格，则需要按住Ctrl键，在拖动鼠标进行其后的操作。
如果要在已有单元格间插入单元格，需要按住Shift键；复制需要按住Shift+Ctrl键，进行拖动，要注意的是：
必须先释放鼠标再松开按键。
如果要将选定区域拖到其他工作表上，需按住Alt键，然后拖到目标工作表标签上。
（2）选择性粘贴
除了复制整个单元格区域外，Excel还可以通过“选择性粘贴”命令对所选单元格中的特定内容如数值、格式等进行移动或复制。
其步骤如下；
①选定需要移动或复制的单元格；
②单击“常用”工具栏上的“剪切”或“复制”按钮；
③选定粘贴区域的左上角单元格；
④单击“编辑”采单上“选择性粘贴”命令，出现如图4-11所示的对话框；
⑤单击“粘贴”选项区中所需选项，再单击“确定”按钮；
图4-11选择性粘贴
（3）插入单元格、行或列
可以根据需要插入空单元格、行或列，并对其进行填充。
插入单元格：
利用“插入”菜单上的“单元格”命令可以插入空单元格。
①在需要插入空单元格处选定相应的单元格区域，选定的单元格数量应与待插入的空单元格的数量相等。
②在“插入”菜单上单击“单元格”命令，出现如图4-12所示的对话框。
图4-12插入单元格、行或列
③在对话框中选定应用的“插入”方式选项。
④单击“确定”按钮。
插入行：
在“插入”菜单上单击“行”命令可以插入新行。
①如果需要插入一行，则单击需要插入的新行之下相邻行中的任意单元格；如果要插入多行，则选定需要插入的新行之下相邻的若干行，选定的行数应与待插入的空行的数量相等。
②在“插入”菜单上单击“行”命令。
可以用类似的方法在表格中插入列。
如果呀插入一列。
，则单击需要插入的新列右侧相邻列中的任意单元格；如果要插入多列，则选定需要插入的新列右侧相邻的若干列，选定的列数应与待插入的新列的数量相等。
（4）删除单元格、行或列
将选定的单元格中从工作表中移走，并自动调整周围的单元格填补删除后的空格。
操作步骤如下：
①选定需要删除的单元格、行或列。
②执行“编辑”采单上“删除”命令。
（5）清除单元格、行或列
将选定的单元格中内容、格式后批注等从工作表中删除，单元格仍保留在工作表中。
操作步骤如下：
①选定需要清除的单元格、行或列；
②选中“编辑”菜单上的“清除”命令，出现如图4-13所示的级联菜单，在菜单中选择相应命令执行即可。
（6）对单元格中数据进行修改
首先使需要编辑的单元格成为活动单元格，如果是要重新输入内容，则直接输入新内容；若只是修改部分内容，按F2功能键后鼠标双击活动单元格，在单元格内或编辑栏右边的编辑框利用←、→键移动光标或按Del键删除不需要的数据完成对数据的修改，按Enter键或Tab键表示修改结果。
图4-13“清除”级联菜单
2.使用公式和函数
函数和公式是Excel的核心.在单元格中输入正确的公式和函数后，会立即在单元格中显示计算出来的结果,如果改变了工作表中与公式有关或作为函数参数的单元格中的数据,Excel会自动更新计算结果。
实际工作往往有许多数据项是相关联的,通过规定多个单元格数据间关联的数学关系,能充分发挥电子表格的作用。
（1）单元格地址及引用
单元格地址:
每个单元格在工作表中都有一个固定的地址,这个地址一般通过其坐标来表示。
如在一个工作表中，B6指定的单元格就是第“6”行与第“B”列交叉位置上的那个单元格，这是相对地址；指定一个单元格的绝对位置需在行、列号前加上符号“$”，例如：
“$B$6”。
由于一个工作簿文件可以有多个工作表，为了区分不同的工作表中的单元格，还要在地址前面增加工作表的名称。
例如：
[Book1]Sheet1!
B6指定的就是“Book1”工作簿文件“Sheet1”工作表中的“B6”单元格。
单元格引用：
引用是对工作表的一个或一组单元格进行标识，它告诉Excel公式使用哪些单元格的值。
通过引用，可以在一个公式中使用工作表不同部分的数据或者在几个公式中使用同一单元格中的数值。
同样，可以对工作簿其他工作表中的单元格进行引用，甚至对其他工作簿或其他应用程序中的数据进行引用。
单元格的引用可分为相对地址引用和绝对地址引用；对其他工作簿中的单元格的引用称为外部引用，对其他应用程序中的数据的引用称为远程引用。
标签和名称：
工作表每一列的首行和每一行的最左边通常含有标签以描述数据，当公式需要引用工作表中的数据时，可以使用行、列标签来引用相应的数据。
如图4-4中“电视机”为第二行的行标签，“计算机”为第六行的行标签，“商品名称”为第A列的列标签，“二季度销售额”为C列的列标签，通过输入公式“=计算机二季度销售额”可以查看C2列的数据；如果待操作的数据没有标签或者需要使用同一过工作簿不同工作表中的数据，可以给单元格或单元格区域定义一个描述性的、便于记忆的名称，使其更直观的反映单元格或单元格区域中数据所代表的含义，如图4-5中，可将A列中有学号的区域A2：
A7选定并定义“学号”这个名称，以后要引用该区域单元格时就可以用“学号”代替A2：
A7，使其更易懂。
使用“标签区域”对话框可以为区域添加标签，其步骤如下：
①打开“插入”菜单，将鼠标移至“名称”上即出现级联菜单，单击级联菜单上的“标签”命令，出现如图4-14所示的“标签区域”对话框；
②定义行标签或列标签。
在工作表中单击含有要添加标签文本的单元格，这时在“添加标签区域”出现所选定的单元格，如果要将其设为行标签，则选中“行标签”单选按钮，否则选中“列标签”单选按钮；
③单击“添加”按钮；
④重复步骤
（2）和（3）直至添加了所有的行、列标签；
⑤单击“确定”按钮。
图4-14“标签区域”对话框
为单元格或单元格区域命名的步骤如下：
①选定需要命名的单元格、单元格区域；
②单击编辑栏左端的“名称”框；
③为所选定的单元格或单元格区域键入名称；
④按ENTER键。
也可以使用已有的行、列标签为单元格命名，其步骤如下：
①选定需要命名的区域，把行、列标签包含进去；
再“插入”菜单中指向“名称”向即出现级联菜单，单击“指令”命令出现如图4-15所示的对话框；
②在“名称创建于”选项框中，通过选定“首行”、“最左列”、“末列”、“最右列”复选框来指定包含标签的名称。
使用这个过程指定的名称只引用包含数值的单元格，而不包含现有的行、列标签。
③在Excel中，可以修改或删除已有的名称，检查名称所引用的对象等。
图4-15为单元格命名
（2）公式
公式是用户为了减少输入或方便计算而设置的计算式，它可以对工作表中的数据进行加、减、乘、除等运算。
公式由值、单元格引用、名称、函数或运算符组成，它可以引用同一个工作表中的其他单元格、同一个工作簿不同工作表中的单元格、或者其他工作簿工作表中的单元格。
运算符对公式中的元素进行特定类型的运算，是公式中不可缺少的组成部分，Excel.包含四种类型的运算符：
算术运算符：
+、-、*、/、%、（乘幂）。
用于连接数字并产生计算结果，计算顺序为先乘除后加减。
比较运算符：
=、>、<、>=、<=、<>。
用于比较两个数值并产生一个逻辑值TRUE或FALSE。
文本运算符：
&,将两个文本值连接起来产生一个连续的文本值。
引用运算符号包括：
冒号、逗号、空格，用于将单元格区域合并运算。
其中“：
”为区域运算符，如C2：
C10是对单元格C2-C10（包括C2和C10的所有单元格的引用；“，”为联合运算符、可将多个引用合并为一个引用，如SUM（B5，C2：
C10）是对B5及C2-C10（包括C2和C10）的所有单元格求和；空格为交叉运算符产生同时隶属于两个引用的单元格区域的引用，如SUM（B5：
E10，C2：
D8）是对B5-D8区域求和。
Excel中运算符的优先级如表4-2所示。
表4-2运算符的优先级
运算符
说明
；，空格
引用运算符
-
负号
%
百分比
乘幂
*/乘除
+-加减
&连接两段文本
=<<=>=>比较运算符
如果要改变运算的顺序，可以使用刮号把公式中优先级低的运算刮起来，但不能将负号刮起来。
在Excel中，负号应该放在数值的前面。
使用公式有一定的规则，即必须以“=”开始。
为单元格设置公式，应先在单元格中或编辑栏中输入“=”，然后直接输入所设置的公式，对公式中包含的单元格或单元格区域的引用可以直接用鼠标拖动进行选定，也可单击要引用的单元格，或输入引用单元格标志、名称，如“=（C2+D2+E2）/3”表示将C2、D2、E2、三个单元格中的数值求和并除以三，把结果放入当前单元格中。
在公式选项板中输入和编辑公式十分方便，它特别有助于输入工作表函数。
在单元格中输入公式的步骤如下：
①选定要输入公式的单元格；、
②在单元格中或编辑栏中输入“=”；
③输入所设置的公式,按Enter键。
如果所输入的公式中包括有函数,则函数的输入可按照以下步骤进行：
①在“函数”下拉列表框中选中函数名称,出现公式选项板,选择所需的函数名,如“SUM”；
②输入函数中要引用的单元格或单元格区域,即添加好函数的各个参数;
③单击”确定”按钮。
下面举例来说明公式的使用方法。
如图4-16所示的学生成绩表，包括英语、数学、计算机和政治等4门课程成绩，现在要求每门课程的平均成绩，最简便的方法就是设置一个公式，在存放成绩的单元格中输入公式：
=（C2+C3+C4+C5+C6+C7+C8+C9+C11）/10。
设置公式的步骤如下：
①单击C12单元格使其成为活动单元格；
②在“数据编辑区”，输入公式“=（C2+C3+C4+C5+C6+C7+C8+C9+C11）/10”后回车，得到C12的值为82.1；
③将鼠标放在C12边框右下角的填充柄上，等其显现为“+”使，按住鼠标左键向右拖动扫过有成绩的区域，就可得到四门课程的平均成绩，如图4-16所示。
图4-16利用设置的公式求值
（3）函数
在Excel中函数就是预定义的内置公式，它使用参数并按特定的顺序进行计算。
函数的参数是函数进行计算必需的初始值。
用户把参数传递给函数，函数按特定的指令对参数进行计算，把计算的结果返回给用户。
Excel含有大量的函数，可以帮助用户进行数学、文本、逻辑、在工作表内查找信息等计算工作，使用函数可以加快数据的录入和计算速度。
Excel2000除了自身带有内置函数外，还允许用户自定义函数。
函数的一般格式为：
函数名（参数1，参数2，参数3，…..）
求和函数SUM（）
函数格式：
SUM（number1，number2，…..）
参数说明：
number1，number2…..是需要求和的1~30的参数。
该函数的功能是对所选定的单元格或区域进行求和，参数可以为一个常数、一个单元格引用、一个区域引用或一个函数。
例如要求图4-16中每个学生所有课程成绩的总和，并把结果放在G列单元格中，则操作步骤如下：
①单击G2单元格使其变成活动单元格；
②然后单击工具栏中的“函数”按钮，出现对话框如图4-17所示；
③单击“函数名”列表框中的“SUM”选项；
图4-17插入函数对话框
④单击“确定”按钮，显示如图4-18所示的函数对话框。
图4-18SUM函数对话框
⑤Excel2000会根据活动单元格所在位置与行、列的关系，自动赋予number1一个求值范围。
如本例中，系统就给number1自动赋予了C2：
F2，并且给出了求和结果376，用鼠标单击对话框中的“确定”按钮
当然求置范围也可自行定义，方法是：
单击选项板参数输入框右边的工作表按钮
，在工作表中用鼠标来选定求值范围，也可直接在参数输入框中输入求值范围。
⑥G列的单元格的数据是有规律的，它是同一行中C、D、E、F四列的数据之和，因此可利用自动填充方式填充其他学生的总分，结果如图4-19所示。
求平均值的函数AVERAGE（）
函数格式：
AVERAGE（number1,number2…）
参数说明：
number1,number…为需要求平均值的1-30个参数。
这是一个求平均值函数，要求参数必须是数值。
图4-19SUM（）函数求和
图4-20AVERAGE（）函数求平均数
在图4-16中，要求出每个学生的平均成绩并放入H列中，即求出C列、D列、E列、F列的平均值并放入H列中，这时可利用AVERAGE（）函数。
求平均值的步骤同步求和基本相同：
选中H2为活动单元格，输入“＝”，按“函数”命令，在“常用函数”“函数名”中选择“AVERAGE”函数，按“确定”按钮后，系统将显示number1为C2：
G2，这时应该将其修改为C2：
F2，再按“确定”按钮，就可得到所求结果，如图4-20所示。
INT（）函数
功能：
返回不大于参数的最大整数值
格式：
INT（number）
参数说明：
number是需要取整的实数
要将用AVERAGE函数求出的每个同学的平均分四舍五入后取整，则先在H2单元格中利用如下INT函数求出结果：
＝INT（AVERAGE（C2：
F2）+0.5），然后在H列中利用自动填充功能求出其他同学平均成绩的取整值。
AND（）函数
功能：
所有参数的逻辑值为真时返回TRUE；只要一个参数的逻辑值为假即返回FALSE。
格式：
AND（logical1,logical2…）
参数说明：
logical1,logical2…为被检测的条件，各条件的值应为逻辑值TRUE或FALSE。
对如图4-16所示的数据，要求没科均在80以上的值TRUE，否则填FALSE，则在I2单元格中利用AND函数来实现=AND（C2＞=80，D2＞=80，E2＞=80，F2＞=80），然后在I列中利用自动填充功能即可。
OR（）函数
功能：
在其参数组中，只要有任何一个参数逻辑值为TRUE，即返回TRUE。
格式：
OR（logical1,logical2…）
参数说明：
logical1,logical2…为被检测的条件，各条件的值应为逻辑值TRUE或FALSE。
对如图4-16所示的数据，要求只要有一门课程达到90分即填TRUE，否则填FALSE。
则该OR函数为：
=OR（C2＞=90，D2＞=90，E2＞=90，F2＞=90）。
IF（）函数
功能：
执行真假值判断，根据逻辑测试的真假值，返回不同的结果。
格式：
IF（logical_test,value_if_true,value_if_false）
参数说明：
logical_test计算机结果为逻辑值的表达式。
value_if_true当logical_test为TRUE时函数的返回值。
value_if_false当logical_test为FALSE时函数的返回值。
例如，将成绩划分等级，平均分90分以上为A等；80-90分为B等；60-79分为C等；小于60分为D等。
则可用下列嵌套的IF函数作为公式：
=IF（H2＞=90，“A”，IF（H2＞=80，“B”，IF（H2＞=60，“C”，“D”）））
综合训练：
课堂练习
1．在Excel2000中的内容是6，B3中的内容是8，在A5中输入=A3＆“+”＆B3，A5单元格将显示（）
A．6+8B.14C.6+8=14D.A3+B3
2．在Excel2000中，若单元格C1中公式为＝A1+B2,将其复制到单元格E5,则E5中的公式是（）
A.=C3+A4B.=C5+D6C.=C3+D4D.=A3+B4
3.在同一工作簿中,sheet1工作表中的D3单元格要引用sheet3工作表F6单元格中的数据,其引用表述为（）
A.=F6B.=sheet3!
F6
C．=F6！
sheet3D.=sheet3#56
4.在Excel2000中，Sheet2！
$A$4表示（）
A．工作表sheet2中的A4单元格绝对引用
B．A4单元格绝对引用
C．sheet2单元格同A4单元格进行！
运算
D．sheet2工作表同A4单元格进行！
运算
5．假设B1为文字“100”，B2为数字“3”，则COUNT（B1：
B2）等于（）
A．103B.100C.3D.1
6．当在某单元格内输入一个公式并确认后，单元格内容显示为#REF！
，它表示（）。
A．公式引用了无效的单元格B.某个参数不正确
C．公式被零除D.单元格太小
7．已知A1单元格中的公式为=AVERAGE（B1：
F6），将B列删除之后，A1单元格中的公式将调整为（）
A．=AVERAGE（#REF）B.=AVERAGE（C1：
F6）
C．=AVERAGE（B1：
E6）D.=AVERAGE（B1：
F6）
8．如下所示的表格区域，如果要算出各数在总数中所占的比例，可在A2单元格中输入（）之后再复制到区域B2：
D2中。
A．B.C.D.
20.617.48.813.2
A．=A1/SUM（$A1:
$D1）B.=A1/SUM（A1:
$D$1）
C．=A1/SUM（A$1:
E$1）D.=A1/SUM（A1:
D1）
请你操作：
新建一工作簿，完成下列操作：
1．在A17中输入：
10；在B17输入；=2*a17*pi（）+128中输入：
Ilove；在D17中输入：
China。
2．熟练地掌握算术运算符号：
%（百分比）、^（乘方）、*（乘）、/（除）、+（加）和—（减）；文本运算符号；＆（连接两个字符串）；比较运算符号：
=（等于）、<（小于）、>（大于）、<=（小于等于）、>=（大于等于）、<>（不等于）。
在A18输入公式：
=2^10+5%+（65-30）;并说明该公式中各个运算符号的运算顺序;
3．在B18中输入:
=“Computer”&“center”；C18中输入：
=A17>B17
4．将A18复制到区域A19：
A21；将B17复制到区域B19：
B21。
观察目标单元格的数值及公式的变化。
用绝对地址和混合地址复制公式情况分别又与此有何不同？
5．在C19输入公式；=A18+10，将C19移动到D20。
观察目标单元格的数值及其公式的变化。
用绝对地址和混合地址移动公式情况分别又与此有何不同？
将A18移动到E18，公式有变化吗？
6．常用函数的使用方法的练习
（1）=SUM（5，2）。
（2）=SUM（“5”，2，FALSE）（等于7。
将“5”转换成5，逻辑值“FALSE”转换成0）。
（3）假设单元格B2，B4中数据分别为`7，“TRUE”，那么SUM（B2，B4，3）等于3，参数为单元格引用时，非数值型数据不被转换成数值型数据。
（4）假设B3：
F3单元格的值分别为2、4、6、8，则SUM（B3：
D3）+10等于22。
（5）计算“职工档案表”中男职工的工资和
=SUMIF（C2：
C4，“男”，G2：
G4）
职工档案表
A
B
C
D
E
F
G
1
序号
姓名
性别
出生日期
职称
工作年月
基本工资
2
1
张长荣
男
1975年10月1日
助教
1997年9月1日
380
3
2
沈丽
女
1969年8月20日
讲师
1982年7月1日
450
4
3
冯志林
男
1975年3月8日
讲师
1997年9月1日
580
（6）设“职工档案表”中G2：
G4单元格分别为：
￥400、￥600、￥800。
=MAX（G2：
G4）
=MIN（G2：
G4，￥100）
（7）=ROUND（3.25，1）
=ROUND（-0.236，2）
=ROUND（3.49，0）
=ROUND（567.8，-2）
（8）统计参数（“职工档案表”中指定区域）中数字项的个数。
=COUNT（A3：
G3）
=COUNT（A4：
G4，30）
（9）求“学生成绩表”中每人的平均分
=AVERAGE（B2：
D2）
=AVERAGE（C2：
C4）
（10）求“学生成绩表”指定区域中，满足条件的单元格的个数。
=COUNTIF（B2：
D2，“>80”）
=COUNTIF（C2：
C4，“80”）
（11）根据逻辑测试的真假值、返回不同的结果（利用“学生成绩表”）。
=IF（B2>=90,“优秀”，“良好”）
=IF（B2+C2+D2<200,“良好”，“及格”）
7．在学生成绩表中，计算平均成绩2=35%*计算机+30%*英语+35%*数学，按四舍五入规则取整。
8．在本次考试中，为每位同学的数学成绩加5分
知识拓展：
Excel中的函数介绍
Excel中所谈的函数其实是一些预定义的公式，它们使用称为参数的特定数值按特定的顺序或结构进行计算。
用户可以直接用它们对某个区域内的数值进行一系列运算，如分析和处理日期值和时间值、确定贷款的支付额、确定单元格中的数据类型、计算平均值、排序显示和运算文本数据等。
例如，用SUM函数对单元格或单元格区域进行加法运算。
函数是否可以是多重的呢？
也就是说一个函数是否可以是另一个函数的参数呢？
当然可以，这就是嵌套函数。
所谓嵌套函数，就是指在某些情况下，可能需要将某函数作为另一个函数的参数使用。
例如图4-21中所示的公式使用了嵌套的AVERAGE函数，并将结果与50相比较。
这个公式的含义是：
如果单元格F2~F5的平均值大于50，则求F2到F5的和，否则显示数值0。
图4-21嵌套函数
在学习Excel函数之前，我们需要对函数的结构做必要的了解。
如图4-22所示，函数的结构以函数名称开始，后面是左圆括号、以逗号分隔的参数和右圆括号。
如果函数以公式的形式出现，应在函数名称前面键入等号（=）。
在创建包含
升级会员 