全屏显示专题章节

Exce l2007除了可以制作一般的表格，还具有强大的计算功能。这是Exce l2007的核心功能，也是其他表格处理软件难以实现的。Exce l2007既可以进行简单的代数运算，又可以创建分析复杂的数学模型，这是由公式和函数提供的，它为用户分析与处理Exce l2007工作表中的数据提供了很大的方便。

公式是函数的基础，是单元格中的一系列值、单元格引用、名称或运算符的组合，利用其可以生成新的值。函数则是Exce l2007预定义的内置公式，可以进行数学、文本、逻辑的运算或者查找工作表的信息。

在工作表中输入数据后，可通过Exce l2007中的公式对这些数据进行自动、精确、高速的运算处理。公式是Excel的重要组成部分，用于对单元格中的数据进行分析和计算。熟练掌握公式可以方便地处理遇到的数据问题，从而大大提高用户的工作效率。

公式是对数据进行计算和分析的运算等式。它由运算项和运算符组成，并且以“＝”开头。运算项可以是常量数值，也可以是单元格或引用的单元格区域、名称和函数等。运算符用来连接要运算的数据，并说明进行了哪种公式运算。公式中的数据可以来自同一工作表中的单元格，也可以来自不同工作表或不同工作簿中的单元格。

1）公式的运算符

运算符是一种符号，用于指明对公式中的元素进行计算的类型。

注意:所有运算符必须是英文状态下的符号。

（1）运算符的种类

Exce l2007中包含有算术运算符、比较运算符、文本运算符和引用运算符4种类型的运算符。

①算术运算符:用于完成基本的数学运算，包括加、减、乘、除、乘方和百分号6种运算符，如表4.1所示。

②比较运算符:用于比较两个数据值的大小，结果成立时返回一个逻辑值TRUE，结果不成立时返回一个逻辑值FALSE，如表4.2所示。

如果比较两个数值，则按其大小比较。例如，5＞3的结果为TRUE。如果比较两个字符串，则按字符的ASCII码从左到右一一比较，直到出现不同的字符为止。如果比较两个日期，则较晚的日期较大。

③文本运算符:只有一个，即连字符＆，用于将一个或多个数据以文本形式连接起来形成一个新文本，如表4.3所示。

如A1单元格内为“计算机”，A2单元格内为“基础”，则公式“＝A1＆A2”将产生一个新文本“计算机基础”。如果连接的是两个字符常量，则必须用双引号括起来，如公式“＝＂计算机＂＆＂基础＂”的结果也为“计算机基础”。

④引用运算符:对工作表的一个或多个单元格进行引用时，需要使用引用运算符，如表4.4所示。

（2）运算符的优先级

Exce l2007对运算符的优先级做了严格的规定，由高到低各个运算符的优先级为:引用运算符之冒号、逗号、空格、算术运算符之负号、百分比、乘幂、乘除同级、加减同级、文本运算符、比较运算同级。在括号内的运算总是最优先的。

如果公式中同时用到多个运算符，Exce l2007将会依照运算符的优先级来依次完成运算。如果公式中包含相同优先级的运算符，如公式中同时包含乘法和除法运算符，则Exce l2007将从左到右进行计算。

2）公式的基本操作

在学习应用公式时，首先应掌握公式的基本操作，包括输入、修改、显示、复制以及删除等。

在Exce l2007的单元格中，输入公式的方法可用前面介绍的数据录入方式中任何一种。但必须注意的是公式一定以“＝”开头，后才跟数值、运算符、函数、单元格引用地址等元素。输入公式时可直接输入单元格引用地址和运算符，如“＝F3＋G3－H3”，也可单击所需的单元格而获得相应的单元格地址。对于函数中使用的连续单元格，可以用鼠标拖动的方式或按Shift键来选择；对于不连续的单元格，则可以按住Ctrl键逐个选择。

例:打开“工资表”，运算所有员工的实发工资。首先计算第一个员工的实发工资，即在N3单元格中输入“＝F3＋G3＋H3＋I3＋J3－K3－L3－M3”后回车；然后再把这个公式复制到其他员工的实发工资对应的单元格里即可，如图4.41所示。

（2）显示和修改公式

如果正确地创建了计算公式，在Exce l2007的默认状态下，其计算值就会显示在单元格中，而公式则显示在编辑栏中。

如果只显示某个单元格内的公式，只需双击该单元格即可。若要求所有公式显示在单元格中，选择“公式”选项卡的“公式审核”组的“显示公式”命令即可。还可按“Ctrl＋′”组合键，使工作表中所有单元格的公式在显示公式内容与显示结果之间相互切换。

若希望两者都显示计算结果，可先选择所在单元格或区域，再右击选择“复制”，接着再右击选择“选择性粘贴”，在对话框的“粘贴”选框中选中“数值”再确定，即可在编辑栏中显示其计算值。

如果要修改某个单元格的公式，可用鼠标单击此单元格，在编辑栏中修改；或者双击单元格（也可按F2键），直接在单元格中修改公式。

（3）移动和复制公式

公式的移动/复制与在单元格中移动/复制数据的方法类似。对于计算方法相同、只是单元格引用地址不同的单元格公式，没有必要逐个单元格地输入公式，可以使用公式复制功能，如图4.41所示。对于公式的复制，可以采用以下两种方法。

①用填充法复制公式:如果公式的复制位置连续，可以采用这种方法。

其操作方法为:选中含有公式的起始单元格，将鼠标指针指向该区域右下角，拖动填充柄向上/向下/向左/向右移动即可实现公式复制，并且系统会自动修改公式中的单元格引用地址。如果在鼠标变成黑色十字形状时双击鼠标，可实现公式向下连续复制。

②复制/粘贴法:如果公式的复制位置不连续，则可以使用“复制”“粘贴”按钮来实现公式的复制。

其操作方法为:先选中含有公式的单元格右击，然后单击浮动工具栏上的“复制”按钮，再选中要复制公式的目的单元格右击，单击浮动工具栏上的“粘贴”按钮，这个公式就被复制到目的单元格中了。其效果与填充出来的公式是一致的，系统也会自动修改公式中的单元格引用地址。

注意:将某个单元格或区域的公式被移动到新位置时，公式中引用的单元格坐标不改变；而将某个单元格或区域的公式被复制或自动填充到新位置时，公式中单元格绝对引用不改变，但相对引用将作相应的自动更改。

3）公式中的引用单元格

我们常常遇到这种情况，如果公式中已经引用的单元格数据发生了变化，则公式的值会自动更新，这一点非常有用。例如，当用Exce l2007建好一个学生成绩表，并且使用公式计算出了每个学生的总分和平均成绩后，如果学生的成绩又发生了变化，则用户只需要修改发生变化的成绩，总分和平均成绩都会根据公式自动更新，而不用重新计算。这是因为输入的公式里用到了单元格的引用。

公式与引用单元格是分不开的，在公式中常常需要引用单元格作为计算项。单元格引用的作用在于标识工作表上的一个或一组单元格，从而告诉公式使用哪些单元格的值。通过引用，可在一个公式中使用工作表不同部分的数据，或者在几个公式中使用同一单元格的数值。

Exce l2007中，在使用单元格引用处理数据时，可以引用同一工作簿中的单元格和其他工作簿中的单元格，而引用其他工作簿中的单元格又被称为链接或外部引用。单元格的引用方式有多种，常用方式包括相对引用、绝对引用及混合引用三种。

相对引用是常用的引用单元格的类型之一，也是在引用单元格进行数据计算时默认的引用方式。相对引用是指用单元格名称引用单元格数据的一种方式，即引用会随公式所在单元格的位置变化而变化的单元格。相对引用形式为“列号＋行号”，例如E2、F2、G2等。

在使用相对引用时，随着结果单元格的变化，其引用的位置也会自动发生变化。前面提到的公式的复制功能就在于此，目标单元格公式中被引用的单元格和目标单元格始终保持这种相对位置。假设E3单元格中的公式为“＝B3＋C3－D3”，如果将该公式复制到E6单元格，则公式自动变为“＝B6＋C6－D6”，这就是相对引用的效果。

绝对引用在公式中引用的单元格是固定不变的，而不考虑包含该公式的单元格位置。绝对引用形式为“﹩列号＋﹩行号”，例如﹩E﹩2、﹩G﹩2等。采用绝对引用的公式，该公式被复制到其他位置，都将与原公式引用相同的单元格，即公式在复制时，绝对引用都不改变单元格引用地址，如图4.42所示。

如果单元格引用中一部分为相对引用，一部分为绝对引用，则称为混合引用。混合引用具有绝对列和相对行，或是绝对行和相对列。绝对引用列采用﹩A1、﹩B1等形式，绝对引用行采用A﹩1、B﹩1等形式。每个单元格的绝对引用和相对引用的转换可按F4键实现，顺序为:A4→﹩A﹩4→A﹩4→﹩A4→A4。

当公式不是被复制到一行或一列，而是一个区域时，常使用混合引用。混合引用在使用时，如果公式所在单元格的位置改变，则相对引用部分改变，而绝对引用部分不变，如图4.43所示。

（4）引用其他工作表中或其他工作簿中的数据

如果在公式中要用到其他工作表中或其他工作簿中的数据，可以直接单击数据所在的单元格，则该单元格的地址将出现在公式中。当然，也可以手动输入其他工作表中或其他工作簿中的单元格地址。

①同一工作簿内其他工作表中的单元格或区域的引用。

其格式为:其他工作表名！单元格或单元格区域引用

②其他工作簿内工作表中的单元格或区域的引用。

其格式为:［其他工作簿］对应的工作表名！单元格或单元格区域引用（其引用一定加美元符号﹩）

图4.44所示为引用其他工作表中的数据。

为了便于计算、统计、汇总和数据处理，Exce l2007提供了大量的函数。Exce l2007将具有特定功能的一组公式组合在一起以形成函数，用户可以方便地使用它将一切复杂的数据计算变得简单。函数实际上是一个预先定义好的内置公式，它既可以单独使用，也可以在公式中使用。与直接使用公式进行计算相比较，使用函数进行数据的计算，可以大大简化公式的输入过程（即计算速度更快），用户只需要设置函数相应的参数就可以进行正确的计算（即减少出错率），从而提高了工作效率。

函数是运用一些被称为参数的特定数据值按特定的顺序或结构进行计算的。在使用函数对数据进行计算之前，需要先了解函数的的定义与结构、函数的参数、函数的种类等。

（1）函数的定义与结构

函数是由系统预先设置好的复杂公式，用于对指定区域内的数据实施一系列的操作，并返回一个或多个值。函数可以简化工作表中的公式，尤其是在用公式执行很长或复杂的计算时。例如，要计算A1-H1所有单元格中数值的和，用公式“＝A1＋B1＋C1＋…＋H1”可以实现，但比较复杂，而使用SUM函数则简单多了。

函数的结构一般包含3个部分:等号、函数名和参数，其表达式为:“＝函数名（参数1，参数2，参数3，…）”。其中，函数名为需要执行运算的函数的名称，每一个函数都有唯一的函数名称；参数为函数运算时参与计算的单元格或者数值，根据函数的不同，参数的个数也有所不同。

（2）函数的参数

Exce l2007所有的函数都要使用到括号（），括号里的内容就是参数。根据函数的种类和使用方法的不同，参数可以是数值、字符和逻辑值，也可以是表达式、单元格地址和区域等。如果一个函数没有参数，称为无参函数，也必须加上括号（）。函数可以出现在公式中，也可以作为其他函数的参数。例如，“＝SUM（A2:A11）”表示求A2:A11单元格区域中的所有数据和；“＝AVERAGE（A2:A11）”表示求A2:A11区域中的数据平均值；“＝SUM（A1:E1，B2:D2）”表示对A1:E1和B2:D2范围内的单元格数据求和。

（3）函数的种类

Exce l2007中常用的函数大概有10类。为了方便用户使用，Exce l2007的“函数库”组中列出了常用的函数，如图4.45所示。

不同函数类型可以进行不同的数据计算。

①统计函数:用于对选定工作表的数据区域进行统计、分析。

②财务函数:用于进行财务分析及财务数据的相关计算。

③查找与引用函数:用于在数据清单中查找特定的数据或引用公式中的特定信息。

④逻辑函数:用于进行逻辑判断或条件查找。

⑤数学和三角函数:用于进行数学或三角方面的数据计算。

⑥日期与时间函数:用于对公式中涉及的日期和时间进行计算、设置或格式化处理。

⑦文本函数:用于对公式、单元格中的字符、文本等进行格式化处理。

“函数库”组中除了常用的函数按钮外，还有3个重要按钮。

①“插入函数”按钮:单击此按钮打开“插入函数”对话框，在里面可以选择函数并编辑函数参数，并且可编辑当前单元格中的公式。

②“自动求和”按钮:紧接所选单元格之后显示所选单元格的求值和等。

③“最近使用的函数”按钮:单击它后，可以从中选择或浏览最近使用的函数列表。

2）函数的基本操作

Exce l2007中，函数的基本操作主要有插入函数与嵌套函数等。通常，函数可以像输入公式、编辑公式一样，直接在编辑栏或者单元格中进行输入和编辑。

输入函数的方法主要有两种:一是与公式一样直接输入，但是要求用户对函数有一定的了解法；二是通过对话框进行插入，适用于对函数还不是很熟悉的用户。

①直接输入法。

选中要输入函数的单元格，直接输入等号“＝”、函数及其参数后按回车键。

②利用“插入函数”向导输入法。

选中要输入函数的单元格，单击编辑栏左侧的“插入函数”按钮

或单击“公式”选项卡的“函数库”组的“插入函数”按钮，弹出“插入函数”对话框。

在“选择类别”下拉列表中选择要插入的函数类型，在“选择函数”列表框中选择要使用的函数。当用户不知道要使用什么函数时，可在“搜索函数”文本框中输入一句自然语言，如“如何得到平均值”，然后单击“转到”按钮，Excel将给出一个用于完成该任务的推荐函数列表，如图4.46所示。

选择函数后（这里选择的是SUM函数），将弹出“函数参数”对话框，其中显示了函数的名称、函数的功能、参数、参数的描述、函数的当前结果等，如图4.47所示。单击“SUM”选项区域中的Number1文本框的“拾取”按钮

，从工作表中选择要将数值相加的单元格，然后再单击

按钮返回，最后单击“确定”按钮即可。公式生成后，也可以根据实际情况修改函数括号内的单元格地址。

Exce l2007中新增加了一个自动完成功能。当在单元格输入了“＝”符号和函数的第一个字母时会出现一个下拉框，所有以该字母开头的函数都会在此显示，接下来可以双击选择要输入的函数。

函数的参数还可以是其他函数，这就称为函数的嵌套使用。在嵌套函数的使用中，函数计算的结果将作为另一个函数的参数进行计算。嵌套函数公式通常包括多个括号，根据嵌套的级别需要输入相应个数的括号，否则将返回错误值，如“＝IF（SUM（B1:B5）＞500，C1，C2）”。

（3）“自动求和”按钮

求和是最常用的运算公式，因此，Exce l2007提供了快捷的自动求和方法。自动求和的使用方法为:首先选择要自动求和的单元格区域或存放求和结果的单元格，然后再单击“公式”选项卡的“函数库”组的“自动求和”按钮，最后按回车键结束。

例如，要求C2-C10单元格的数值之和，可先选中C2:C10单元格区域，然后单击“自动求和”按钮，立即就可以在C11单元格中显示出数值和。如果单击的是“自动求和”按钮下边的下拉按钮，则会弹出最常用的几个函数:求和、平均值、计数、最大值和最小值。选择其中一个函数命令，则会在C11单元格显示出相应的计算结果。选择“其他函数”命令，则弹出“插入函数”对话框，可以用上面讲的向导法输入函数。

3）常用函数应用举例

Exce l2007提供了非常丰富的函数供用户选择使用，如果熟练掌握了这些函数的用法，将会充分领略到Excel的强大功能。每个函数的应用各不相同，下面对几种常用的函数进行讲解。

主要功能:求参数的绝对值。

使用格式:ABS（number）

参数说明:number代表需要求绝对值的数值或引用的单元格。

应用举例:如果在B2单元格中输入公式“＝ABS（A2）”，则A2单元格中无论输入正数还是负数，B2中均显示出正数。

（2）AVERAGE函数

主要功能:计算所有参数的算术平均值。

使用格式:AVERAGE（number1，number2，…）

参数说明:“number1，number2，…”代表需要求平均值的数值、引用单元格或区域，参数不超过30个。

应用举例:在B8单元格中输入公式“＝AVERAGE（B7:D7，F7:H7，7，8）”，按回车键后，即可求出B7:D7区域、F7:H7区域中的数值和7、8的平均值。

注意:如果引用区域中包含“0”值单元格，则计算在内；如果引用区域中包含空白或字符单元格，则不计算在内。

（3）COUNTIF函数

主要功能:统计某个单元格区域中符合指定条件的单元格数量。

使用格式:COUNTIF（range，criteria）

参数说明:range代表要统计的单元格区域；criteria代表指定的条件表达式。

应用举例:在C17单元格中输入公式“＝COUNTIF（B1:B13，＂＜60＂）”，按回车键后，即可统计出B1:B13单元格区域中数值小于60的单元格数量。

注意:允许引用的单元格区域中有空白单元格出现。

主要功能:根据对指定条件逻辑判断的结果，返回相对应的内容。

使用格式:IF（logical，value＿if＿true，value＿if＿false）

参数说明:logical代表逻辑判断表达式；value＿if＿true表示当判断条件为“真（TRUE）”时的显示内容，如果忽略则返回“TRUE”；value＿if＿false表示当判断条件为“假（FALSE）”时的显示内容，如果忽略则返回“FALSE”。

应用举例:在C2单元格中输入公式“＝IF（A2＞＝B2，A2，B2）”，按回车键后，即可在C2单元格中显示A2、B2两个单元格中的较大数。

主要功能:将数值向下取整为最接近的整数。

使用格式:INT（number）

参数说明:number表示需要取整的数值或包含数值的引用单元格。

应用举例:输入公式“＝INT（18.89）”，按回车键后结果为18。

注意:取整时，不进行四舍五入；如果输入公式“＝INT（－18.89）”，则返回结果为－19。

主要功能:求一组数中的最大值。

使用格式:MAX（number1，number2，…）

参数说明:“number1，number2，…”代表需要求最大值的数值、引用单元格或区域，参数不超过30个。

应用举例:输入公式“＝MAX（B2:B14）”，按回车键后，即可显示出B2:B14单元格区域中的最大值。

注意:如果参数中有文本或逻辑值，则不参与计算。

主要功能:求一组数中的最小值。

使用格式、参数说明等与MAX函数完全相同。

主要功能:计算所有数值参数的和。

使用格式:SUM（number1，number2，…）

参数说明:“number1，number2，…”代表需要计算的值，可以是具体的数值、引用单元格或区域等。

应用举例:输入公式“＝SUM（A2:A10，B2:B10）”，按回车键后，即可显示出A2:A10区域、B2:B10区域中的所有数值之和。

注意:如果参数中有空白单元格、逻辑值或文本，则不参与计算。

主要功能:计算符合指定条件的单元格区域内的数值之和，即根据指定条件对若干单元格求和。

使用格式:SUMIF（range，criteria，sum＿range）

参数说明:range为用于条件判断的单元格区域；criteria确定哪些单元格将被相加求和，其形式可以为数字、表达式或文本。例如，条件可以表示为“男”＞“32”；sum＿range是需要求和的数值所在的单元格区域，如果省略了sum＿range，则对range区域中的单元格求和。

应用举例:输入公式“＝SUMIF（A2:A14，＂计算机＂，G2:G14）”，表示在A2:A14区域中找出值为“计算机”的单元格区域，将它们的G列数据求和。

主要功能:返回数字参数的个数。它可以统计数组或单元格区域中含有数字的单元格个数。

使用格式:COUNT（value1，value2，…）

参数说明:value1，value2，…是包含或引用各种类型数据的参数（1～30个），其中只有数字类型的数据才能被统计。

应用举例:如果A1＝90，A2＝人数，A3＝“”，A4＝54，A5＝36，则公式“＝COUNT（A1:A5）”返回3。

主要功能:返回某一正数的算术平方根。

使用格式:SQRT（number）

参数说明:number为需要求平方根的一个正数。

应用举例:如果A1＝81，则公式“＝SQRT（A1）”返回9；“＝SQRT（4＋32）”返回6。

4）Exce l2007中常见的出错信息

在操作Exce l2007的过程中，经常遇到诸如“＃＃＃＃＃！”或“＃VALUE！”之类的出错信息，让用户不知所措。但是只要弄清到底是什么出了错，就能对症下药，解决这些问题。下面就介绍几种常见的错误信息，并归纳产生这些错误信息的原因。

（1）“＃DIV/0！”错误信息

若输入的公式中的除数为0，或在公式中的除数使用了空白单元格（当运算对象是空白单元格时，Exce l2007将此空值解释为零值），或包含零值单元格的单元格引用，就会出现错误信息“＃DIV/0！”。只要修改单元格的引用，或者在用作除数的单元格中输入不为零的值即可解决问题。

（2）“＃VALUE！”错误信息

此情况可能是由以下4个方面的原因之一造成的，一是参数使用不正确；二是运算符使用不正确；三是执行“自动更正”命令时不能更正错误；四是当在需要输入数字或逻辑值时输入了文本，由于Exce l2007不能将文本转换为正确的数据类型，也会出现该提示。这时应确认公式或函数所需的运算符或参数是否正确，并且在公式引用的单元格中是否包含有效的数值。

（3）“＃NAME？”错误信息

这是公式中使用了Excel不能识别的文本而产生的错误，也可能是删除了公式中使用的共同名称或使用了不存在的或拼写错误的名称所致。

从以下几个方面进行检查并纠正错误:

①如果是使用了不存在的名称而产生这类错误，应确认使用的名称确实存在。选择“插入”→“名称”→“定义”命令，如果所需的名称没有被列出，请使用“定义”命令添加相应的名称。

②如果是名称、函数名拼写错误，应修改拼写错误。

③确认公式中使用的所有区域引用都使用了冒号（:）。例如，SUM（A1:C10）。注意将公式中的文本括在双引号中。

（4）“＃N/A”错误信息

这是在函数或公式中没有可用数值时产生的错误信息。

（5）“＃NUM！”错误信息

当函数或公式中使用了不正确的数字时将出现错误信息“＃NUM！”，这时应确认函数中使用的参数类型的正确性，然后修改公式，使其结果在－10307～10307范围内即可。

（6）“＃REF！”错误信息

单元格中出现这样的信息是因为该单元格引用无效的结果。比如，删除了有其他公式引用的单元格，或者把移动单元格粘贴到了其他公式引用的单元格中。

（7）“＃NULL！”错误信息

在单元格中出现此错误信息时，是因为为两个并不相交的区域指定了交叉点。例如，使用了不正确的区域运算符或不正确的单元格引用等。

（8）“＃＃＃＃＃”错误信息

如果整个单元格都出现井字号（＃＃＃＃＃），它的意思是通常是不代表公式出错，表示数据太长不显示，即该列宽度不足。