《Word 2003、Excel 2003案例教程》第14章 EXCEL 2003公式和函数的使用

第14章EXCEL2003公式和函数的使用

——制作“期末考试成绩分析表”

建立公式常用函数单元格引用

RANK函数

COUNT函数

COUNTIF函数1.公式和函数

用Excel制作的表格往往需要有数据的计算和统计，如求学生考试总成绩、求平均成绩、求学生考试成绩的最高分、最低分等。Excel2003具有非常强的计算和统计功能，从简单的四则运算，到复杂的财务计算、统计分析，都能轻松解决。（1）公式

在Excel2003中，所有的计算都可以利用“公式”来完成，Excel2003的公式就是对工作表中的数值进行计算的表达式，它以“=”开头，并由一些数值和运算符号组成，这里所说的数值，不仅包括普通的常数，还包括单元格名称及Excel函数，而运算符号不仅包括算术运算符（加减乘除），还包括比较运算符和文本运算符。每个公式都能根据参加运算的数据计算出一个结果。Excel2003中公式的概念与数学公式的概念基本上是一致的。通常情况下，一个公式是由各种运算符、常量、函数以及单元格引用组成的合法运算式，而运算符则指定了对数据进行的某种运算处理。

运算符根据参与运算数据的个数分为单目运算符和双目运算符。单目运算符只有一个数据参与运算，双目运算符有两个数据参与运算。运算符根据参与运算的性质分为算术运算符、比较运算符和文字连接符三类。

①算术运算符：算术运算符用来对数值进行算术运算，结果还是数值。算术运算符的优先级由高到低为：-（求负）、%、^、*和/、+和-。如果优先级相同（如*和/），则按从左到右的顺序计算。

②比较运算符：比较运算符用来比较两个文本、数值、日期、时间的大小，结果是一个逻辑值（TRUE或FALSE）。比较运算的优先级必算术运算的低。各种类型数据的比较规则如下。数值型数据的比较规则是：按照数值的大小进行比较。日期型数据的比较规则是：昨天<今天<明天。时间型数据的比较规则是：过去<现在<将来。文本型数据的比较规则是：按照字典顺序比较。

③文字连接符：文字连接符只有一个“&”，是双目运算符，用来连接文本或数值，结果是文本类型。文字连接的优先级比算术运算符的低，但是比比较运算符的高。如：“计算机”&“应用”，其结果是“计算机应用”；“总分是”&80+70+60，其结果是“总分是210”。

要判断一个单元格的内容是不是公式很简单，只要看到最前面有一个“=”号，那肯定就是公式，同样，如果要在一个单元格中创建公式，一定不要忘了应以“=”号开始。输入公式的方法是：

①单击将要在其输入公式的单元格，该单元格将存放公式的计算结果。

②输入一个“=”号，然后在其后输入公式。

③公式输入完后，单击回车键，该公式的计算结果会出现在选定的单元格中。

如果要编辑和修改公式，只需在编辑栏中修改即可，要删除公式，也可在编辑栏中进行。复制公式的方法：

与数据的复制一样，单元格中的公式也是可以复制的，单击输入有公式的单元格，鼠标指向其右下角的填充柄

，按住左键拖动，可把该公式的格式复制到所选择的单元格里。修改公式的方法：双击存放公式的单元格，插入点位于该单元格内，单元格和编辑框内都可以看到相应的公式，可直接在单元格内修改其中的公式。（2）函数

函数是Excel2003中已经定义好的计算公式，Excel2003提供了大量的、实用的函数，基本满足了财务、统计及各管理部门日常统计和计算工作的需要。

一个函数由两部分组成，函数名和函数参数。函数的格式是：函数名（参数1，参数2，。。。）函数名表明函数的功能，函数的参数是参与运算的数值及范围和条件。如SUM是求和的函数，SUM（10，20，30）的意思是将括号中的三个数求和，SUM（A3:D3）是将A3:D3单元格区域中的数值求和。2.常用函数的使用

在日常的统计和计算工作中，用的最多的还是如求和、求平均、求最大最小等计算，Excel2003为这些常用的计算设置了最方便的操作方法。（1）求和（SUM）对工作表某些单元格中数据求和的步骤如下：

①选择准备放求和结果的F3单元格。

②单击常用工具栏下的“自动求和”按钮

→“求和”命令

。

③用鼠标在欲进行求和计算的单元格上拖动.

④按回车键，求和结果出现在单元格中.

⑤鼠标指向F3单元格右下角的填充柄

，向下拖动，可将公式复制到F列的所有单元格中.

（2）求平均值（AVERAGE）求平均值与求和的操作方法类似：

①选择准备放平均值结果的单元格。

②单击常用工具栏下的“自动求和”按钮

，在弹出的下拉菜单中选择“平均值”命令。

③用鼠标在欲进行求平均值的单元格上拖动。

④按回车键，所求平均结果出现在单元格中。

（3）计数（COUNT）所谓计数，是统计所选单元格的个数。在日常的工作中，计数也是经常要做的操作之一，如统计学生人数，统计课程门数等。计数的操作步骤如下：选择准备放计数结果的单元格。

②单击常用工具栏下的“自动求和”按钮

，在弹出的下拉菜单中选择“其他函数”命令，在“插入函数”对话框中选择“COUNT”函数，单击确定按钮。

③用鼠标在欲进行计数统计的单元格上拖动。

④按回车键，所求的计数结果出现在单元格中。

（4）求最大(MAX)或最小值(MIN))求最大(MAX)或最小值(MIN))也是常用的操作，如求学生考试的最高分、最低分等。求最大（或最小）值的操作步骤如下：选择准备放最大（或最小）值结果的单元格。

②单击常用工具栏下的“自动求和”按钮

，在弹出的下拉菜单中选择“最大值（或最小值）”命令。

③用鼠标在欲进行求最大（或最小）值的单元格上拖动。

④按回车键，所求最大（或最小）值结果出现在单元格中。

（5）根据条件确定单元格的取值（IF函数）

有时我们需要根据条件来确定单元格的取值，如根据学生的成绩确定是否录取，这是需要用到条件函数（IF）。操作步骤如下：

①选择准备存放结果的单元格

②单击常用工具栏下的“自动求和”按钮

，在弹出的下拉菜单中选择“其他函数”命令，在“插入函数”对话框中选择“IF”函数③在“选择类别”中选择“常用函数”，在“选择函数”中选择“IF”，单击“确定”后，打开IF函数的“函数参数”对话框④在Logical_test中输入判定条件“F3>=280”，意为总分大于或等于280分，在Value_IF_true中输入条件为真时的值“录取”；在Value_IF_False中输入条件为假时的值“不录取”，如图所示。

⑤单击“确定”按钮，此时根据判断在F3单元格中填为“录取”，⑥鼠标指向G3单元格右下角的填充柄

，向下拖动，可将公式复制到G列的所记录的“是否录取”一列中填入录取或不录取，如图所示。（6）统计满足条件的单元格数目（COUNTIF函数）

在工作中我们经常需要统计出满足条件的单元格的数量，如统计出录取的人数是多少，这就需要用到条件统计函数。操作步骤如下：选择准备存放结果的单元格。

②单击常用工具栏下的“自动求和”按钮

，在弹出的下拉菜单中选择“其他函数”命令，弹出“插入函数”对话框。

③在“选择类别”中选择“统计”，在“选择函数”中选择“COUNTIF”，单击“确定”后，打开“COUNTIF”函数的“函数参数”对话框，在Range中输入统计范围“G3:G7”，在Criteria中输入条件“录取”，如图所示。④单击“确定”按钮后，即可统计出在G3:G7单元格区域内值为“录取”的单元格是多少，如图中G9单元格中的数字2。3．单元格地址的引用

（1）A1引用类型Excel2003中默认的状态是A1引用类型，它使用行标和列标的组合方式表示单元格的引用名称。如“B5”表示引用了B列5行交叉处的单元格，“A1:C7”表示引用了A1单元格至C7单元格所构成的矩形区域。单元格区域引用可用公式表示为“区域左上角的单元格名称+英文半角冒号（：）+区域右下角单元格名称”（其中“+”号不输入）。（2）相对引用相对引用也称为相对地址，它用列标与行标直接表示单元格，如果某个单元格内的公式被复制到另一个单元格，原来单元格内的地址在新单元格中发生相应的变化，就需要用相对引用来实现。如果在D4单元格中输入“=C4”，那么将D4向下拖动到D5时，D5中的公式是“=C5”，将D4向右拖动到E4，E4中的公式变成了“=D4”

（3）绝对引用如果希望在移动或复制公式后，仍然使用原来单元格或单元格区域中的数据（如给班级中每个同学排名次），就需要使用绝对引用。在使用单元格的绝对引用时须在单元格的列标与行标前加“$”符号。如在D4单元格中输入“=$C4”，将D4向右拖动到E4，E4中的公式还是“=$C4”，而向下拖动到D5时，D5中的公式就成了“=$C5”。（4）混合引用单元格如果将相对引用与绝对引用混合使用，即为混合引用。在混合引用中绝对引用部分保持不变，而相对引用的部分将发生相应的变化。如在D4单元格中输入“=C$4”，那么将D4向右拖动到E4时，E4中的公式变为“=D$4”，而将D4向下拖动到D5时，D5中的公式还是“=C$4”。4.使用工作表的引用进行多张工作表计算

在实际工作中，有时需要引用多张工作表中的数据进行计算。比如，在“汇金电脑科技有限公司员工工资表”中，要求计算1月至12月每月实发工资的总和，具体操作步骤如下：（1）打开“汇金电脑科技有限公司员工工资表”，如图所示。（2）选择存放12个月实发工资总和的单元格（“一月”工作表的L24单元格），单击菜单“插入”→“函数”命令，在弹出的“