任务六　使用公式和函数

一、公式及其应用

1．公式的概念

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

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

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

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

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

括号（）用于表示优先运算。

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

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

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

2．编辑公式

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

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

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

3．求和公式的使用

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

图1-28　求和公式

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

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

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后面的括号中输入用冒号隔开的两个单元格地址，如同求和函数。

三、打印管理

工作表创建好后，为了提交或留存查阅方便，经常需要把它打印出来，操作步骤一般是：先进行页面设置，再进行打印预览，最后打印输出。

1．设置打印区域和分页

选择要打印的区域的方法是：用拖动鼠标光标来选择要打印的区域，单击“页面布局”选项卡下“页面设置”分组里的“打印区域”按钮，再单击“设置打印区域”的命令，选定区域的边框上出现虚线，表示打印区域已设置好，打印时只有被选定的区域中的数据才能打印。

工作表较大时，Excel 2010一般会自动为工作表分页，如果用户不满意这种分页方式，可以根据自己的需要对工作表进行人工分页。

分页包括水平分页和垂直分页。水平分页的操作步骤如下：单击要另起一页的起始行行号，单击“页面布局”选项卡下“页面设置”分组里的“分隔符”按钮，选择“插入分页符”命令，在起始行上出现一条水平虚线，表示分页成功。

垂直分页是必须单击另起一页的起始列列标或选择该列最上端的单元格，分页成功后将在该列左边出现一条垂直分页虚线。如果选择的不是最左或最上的单元格，插入分页符将在单元格上方和左侧各产生一条分页虚线。

删除分页符可选择分页虚线的下一行或右一列的任一单元格，单击“页面布局”选项卡下“页面设置”分组里的“分隔符”按钮，选择“删除分页符”命令；也可以选中整个工作表或选中任一单元格，然后选择“分隔符”按钮下的“重设所有分页符”命令，可以删除工作表中的所有人工分页符。

分页后单击状态栏右侧视图切换按钮中的“分页预览”按钮，可进入分页预览视图。单击“普通”按钮，可以结束分页预览回到普通视图中。

2．页面设置

Excel 2010具有默认页面设置功能，用户因此可以直接打印工作表。如有特殊要求，使用页面设置可以设置工作表的打印方向、缩放比例、纸张大小、页边距、页眉、页脚等。单击“页面布局”选项卡下“页面设置”分组右侧的按钮，打开如图1-29所示的“页面设置”对话框，该对话框共有4个选项卡：页面、页边距、页眉/页脚和工作表。

图1-29　“页面设置”对话框

（1）“页面”“页边距”选项卡。在图1-29所示的“页面”选项卡中可以设置纸张方向、缩放比例、纸张大小、打印质量、起始页码；“页边距”选项卡如图1-30所示，可设置页面4个边界的距离、页眉和页脚的上下边距等。

图1-30　“页边距”选项卡

（2）“页眉/页脚”选项卡。单击图1-30中的“页眉/页脚”标签，会出现图1-31所示的对话框。

图1-31　“页眉/页脚”选项卡

如果设置页眉和页脚，可单击“页眉”和“页脚”的下拉列表，选择内置的页眉和页脚格式，也可分别单击“自定义页眉”“自定义页脚”按钮，在相应的对话框中自己定义。设置好后单击“确定”按钮即可。

（3）“工作表”选项卡。“工作表”选项卡如图1-32所示。

图1-32　“工作表”选项卡

打印区域：若不设置，则打印当前整个工作表；若需设置，则单击“打印区域”右侧的折叠按钮，在工作表中拖动选定打印区域后，再单击“打印区域”右侧的折叠按钮，返回对话框，单击“确定”按钮。

打印标题：如果要使每一页上都重复打印列标志，则单击图1-32中的“顶端标题行”编辑框，然后输入列标志所在行的行号；如果要使每一页上都重复打印行标志，则单击“左端标题列”编辑框，然后输入行标志所在列的列标。

每页都打印行号和列标：选中图1-32中的“行号列标”复选框即可。

3．打印预览

单击视图切换按钮中的“分页预览”按钮，进入“分页预览”视图，用鼠标光标拖动分页虚线可直接改变分页的位置。

4．打印输出

经过设置打印区域、页面设置、打印预览后，工作表可以正式打印了。打印方法如下：选择“页面设置”对话框中的“打印”按钮，或选择“文件”菜单下的“打印”命令，即可打印输出。