任务六 使用公式和函数

一、公式及其应用

1. 公式的概念

公式是Excel 2010中的重要内容之一。充分灵活地运用公式，可以实现数据处理的自动化。公式可以用来执行各种运算，如加法、减法或比较工作表数值。它可以引用同一工作表中的其他单元格、同一工作簿中不同工作表中的单元格，或者其他工作簿的工作表中的单元格。公式由运算符、常量、单元格引用值、名称、工作表函数等元素构成。

（1）运算符。运算符包括算术运算符、比较运算符、文本运算符、括号和引用运算符。

①算术运算符包括+（加号）、−（减号或负号）、*（星号或乘号）、/（除号）、%（百分号）、^（乘方），用于完成基本的数学运算，返回值为数值。例如，在单元格中输入“=5+2^2”后按“Enter”键确认，结果是9。

②比较运算符包括=（等于）、＞（大于）、＜（小于）、＞=（大于等于）、＜=（小于等于）、＜＞（不等于）。符号两边为同类数据时才能比较，其运算结果是True或False。例如，在单元格中输入“=5＜6”，结果是True。

③文本运算符是&（连接），符号两边均为文本型数据才能连接，连接的结果仍是文本型数据。例如，在单元格中输入“="职业"&"学院"”（注意文本输入时需加英文半角引号）后按“Enter”键，结果是“职业学院”。

④括号“()”用于表示优先运算。

⑤引用运算符包括空格、逗号和冒号。空格为交叉运算符，逗号（，）为联合运算符，冒号（：）为区域运算符。

（2）运算符的优先级。按运算类别，以比较运算符、文本运算符、算术运算符、引用运算符和括号为序，优先级越来越高。

对于同类运算符，如上所列，顿号分隔的运算符为相同优先级，以分号为界时为不同优先级，分号右边的运算符比左边的运算符优先。

2. 编辑公式

（1）选定需要输入公式的单元格。

（2）输入公式。输入公式时应以等号（=）或加号（+）开头，然后输入公式名或表达式。输入运算符时，注意优先级和前后数据类型，公式中不能有多余的空格。

（3）按“Enter”键或单击“输入”按钮，即完成输入，单击“取消”按钮则取消输入。

3. 求和公式的使用

求和计算是一种最常用的公式计算，如图1-31所示。操作方法：选中G3，输入“=D3+E3+F3”后按“Enter”键，得到结果为277。

在输入公式时应注意：第一，运算符必须在英文半角状态下输入；第二，公式的运算尽量使用单元格地址，以便于复制引用公式。公式中单元格的地址可以用键盘输入，也可以单击单元格得到相应的单元格地址。在图1-31中，若要继续求其他学生的总分，可使用“自动填充”的方法实现。

如果需要修改某公式，则先单击包含该公式的单元格，再在编辑栏中修改即可；也可以双击该单元格，直接在单元格中修改。

4. 相对引用和绝对引用

单元格的引用是为了把单元格中的数据和公式联系起来，标识工作表中的单元格或单元格区域，指明公式中使用数据的位置。单元格的引用有两种基本方式：相对引用和绝对引用。默认方式是相对引用。

（1）相对引用。相对地址是以某一特定单元格为基准来对其他单元格进行定位。相对地址的表示方法为“A5”“C8”等，用行、列地址作为它的名字。第3列第8行单元格的相对地址为C8，第2列第2行到第8列第12行单元格区域的相对地址为B2:H12。

相对引用是指公式中的参数以单元格的相对地址表示，如复制或移动含公式的单元格时，单元格的引用会随着公式所在单元格位置的变更而改变。例如，A4单元格中用了相对引用，公式为A1至A3求和：A4=A1+A2+A3。将公式复制到B4，则B4单元格中的公式为B4=B1+B2+B3。

（2）绝对引用。绝对地址则为Excel某些单元格在工作表中的确切位置。绝对地址的表示方法为“$A$5”“$C$8”等，用行、列地址加$作为名字。第3列第8行单元格的绝对地址为$C$8。第2列第2行到第8列第12行单元格区域的绝对地址为$B$2:$H$12。

绝对引用是指公式中的参数以单元格的绝对地址表示，复制或移动含公式的单元格时，公式中的绝对引用不会随着公式所在单元格位置的变更而改变。例如，C4单元格中用了绝对引用，公式为$C$1至$C$3求和：C4=$C$1+$C$2+$C$3。将该公式复制到D4，则D4单元格中的公式为D4=$C$1+$C$2+$C$3，没有发生变化。

（3）混合引用。混合引用是指需要固定某行引用而改变列引用，或固定某列引用而改变行引用，如$B5、B$5。混合引用综合了相对引用与绝对引用的效果。例如，E4单元格中用了混合引用，公式为E4=E$1+$E2+E$3，将该公式复制到F5，则F5单元格中的公式为F5=F$1+$E3+F$3。

欲改变引用地址表示法，可将鼠标光标移至编辑栏中所需改变的引用地址，按“F4”键，每按一次“F4”键即改变一次表示方法。

二、常用函数及其应用

1. 函数概念

函数是预定义的内置模式，可以在公式中直接调用。

其格式如：函数名（参数1，参数2，…）。

Excel提供了300多种函数，涉及数学、统计学、财务等各个方面，功能比较齐全，可以进行各种复杂的计算、检索和数据处理。

①数学函数，如ROUND（四舍五入函数）、ABS（取绝对值函数）等。

②统计函数，如AVERAGE（算术平均值函数）、MIN（求最小值函数）。

③日期与时间函数，如TODAY（当前日期函数）、NOW（当前日期和时间函数）等。

④逻辑函数，如AND（逻辑与函数）、NOT（逻辑非函数）、OR（逻辑或函数）等。

2. 函数输入

方法一：直接输入。直接键入的方法是指选中单元格，输入“=”，然后按照函数的语法直接输入。

例如，要求在A6单元格中输入A1到A5的求和函数，操作步骤为选择A6单元格，输入“=SUM（A1:A5）”即可。

方法二：使用工具按钮“fx”。例如，要在B6单元格中输入求B1至B5的平均值函数，操作步骤为：选择B6单元格后，在名称框右侧的工具栏中选择“fx”；在粘贴函数对话框中选中相应的函数AVERAGE；可用鼠标将需求平均值的单元格区域B1:B5选中，单击“确定”按钮即可。

方法三：使用“公式选项卡”下的“插入函数”按钮。例如，要在B6单元格中输入求B1至B5的平均值函数，操作步骤为：选择B6单元格，单击“公式选项卡”下的“插入函数”按钮，打开“插入函数”对话框，其余步骤同上。

3. 常用函数简介

（1）逻辑类函数。常用的逻辑类函数是条件检测函数IF，其格式和功能如下。

IF格式：IF（logical_test,value_if_true,value_if_false）

IF的功能是执行真假值判断，根据逻辑测试的真假值，返回不同的结果。可以用函数IF对数值和公式进行条件检测。

（2）数学与三角类函数。常用的数学与三角类函数是SUM函数。

利用SUM函数可以计算出指定区域中数据的总和。使用这个函数时，要在函数名SUM后面的括号中输入用冒号隔开的地址，如SUM（B4:E4）。冒号前的地址指定区域的起点单元格的地址，冒号后面的地址指定区域的终点单元格的地址。

（3）统计类函数。常用的统计类函数是AVERAGE函数。

利用AVERAGE函数，可以计算指定区域中数据的平均值。输入这个函数时，要在函数名AVERAGE后面的括号中输入用冒号隔开的两个单元格地址，如同求和函数。

项目小结

本项目介绍了Excel 2010的常用操作，包括单元格和工作表编辑的各种操作方法，以及Excel中的计算功能、图表、公式和函数。通过对本项目的学习，你应学会用Excel 2010创建和编辑表格，对数据进行输入、编辑、计算、复制、移动以及设置格式、打印等操作；掌握Excel 2010处理数据和分析数据的功能，可以运用公式和函数处理数据，能对工作表中的数据进行排序、筛选、分类汇总、统计和查询等操作；能够根据工作表中的数据快速生成图表，学会编辑和修改常用图表。

项目实训

1. 实训目的

（1）掌握Excel 2010启动及退出等基本操作。

（2）学会Excel 2010工作表建立及编辑的基本操作方法。

（3）掌握数据填充、筛选、排序等基本操作。

（4）学会图表的创建，掌握常用图表的编辑、修改。

（5）掌握公式输入的格式，能够运用公式进行常规的数据计算。

（6）掌握函数输入的3种基本方法，能熟练使用函数进行数据的统计与分析。

（7）掌握公式输入的方法，能够运用公式进行数据的计算，能运用函数进行数据的统计及分析操作。

（8）掌握打印过程中页面设置、打印区域设置及打印预览的方法。

2. 实训资料

（1）Microsoft Office中的Excel 2010软件。

（2）自行设计的一个班的期末考试成绩统计表，如图1-32所示。

3. 实训要求

（1）启动Excel 2010，建立一个工作簿文件，命名为“成绩统计表”。

（2）把Sheet1更名为“学生成绩表”，输入标题和表头之后输入10条记录。

（3）将第1行的内容作为表格标题居中。

（4）将第3行第A列至第13行第E列区域设置行高为20，列宽为12，字体为“楷体_GB2312”，字号为16。

（5）将自第4行至第13行的第E列以公式“＝C4+D4”填充。

（6）将自第4行至第13行以第E列为关键字按升序方式进行排序。

（7）将A3:E13单元格区域中的内容设为水平居中。

（8）显示学生成绩表中总成绩大于等于80且小于90的记录，然后恢复原状。

（9）在学生成绩表的最后添加一行，利用公式计算各成绩的平均分。

（10）为工作表设置页边距、页眉和页脚。

（11）在工作表上设置打印网格线和行号。

（12）在工作表中插入水平分页符和垂直分页符，并进行分页预览。