4.3 公式和函数

4.3.1 公式

1.公式输入与编辑

在Excel 2010中公式是用户为了减少输入或方便计算而设置的计算式子，它可以对工作表中的数据进行加、减、乘、除等运算。 公式可以由值、单元格引用、名称、函数或运算符组成，它可以引用同一个工作表中的其他单元格、同一个工作簿不同工作表中的单元格，或者其他工作簿的工作表中的单元格。 使用公式有助于分析工作表中的数据。当改变了工作表内与公式有关的数据，Excel 2010会自动更新计算结果。 输入公式的操作类似于输入文本。 不同之处在于，输入公式时要以等号(=)或加号(+)开头。 对公式中包含的单元格或单元格区域的引用，可以直接用鼠标拖动进行选定，或单击要引用的单元格输入引用单元格标志或名称，如“=E4+F4+G4-H4”表示将E4、F4、G4三个单元格求和后减去 H4， 把结果放入当前单元格中。 在公式编辑框()中输入和编辑公式十分方便。

输入公式的步骤如下：

(1)选定要输入公式的单元格。

(2)在单元格中或公式编辑框中输入“=”。

(3)输入设置的公式，按回车键。

例如，在职工工资表中包括基本工资、岗位津贴、差旅补贴、水/电费，现在要求计算每个员工的实发工资(基本工资、岗位津贴、差旅补贴三项之和减去水/电费)，最简便的方法就是设置一个公式，在I4中输入=E4+F4+G4-H4，回车即计算出该员工的“实发工资”结果。 若要计算其他员工的“实发工资”，则将鼠标箭头放在I4的边框右下角的填充柄上，等其显现为“+”时，按住鼠标左键向下拖动扫过所有员工，就可得到其他员工的“实发工资”，如图4-31所示。

2.公式中的运算符

运算符用于对公式中的元素进行特定类型的运算。 Excel 2010包含4种类型的运算符：算术运算符、比较运算符、文本运算符和引用运算符。 算术运算符可以完成基本的数学运算，包括“+”(加号)、“-”(减号)、“*”(乘号)、“/”(除号)、“%”(百分号)和“^”(乘方)，可以连接数字并产生数字结果。 比较运算符也称关系运算符，可以比较两个数值并产生逻辑值True或False，包括“=”(等号)、“〉”(大于号)、“〈”(小于号)、“〉=”(大于等于号)、“〈=”(小于等于号)和“〈〉”(不等于号)。 文本运算符“&”(连字符)将两个文本值连接起来产生一个连续的文本值。 引用运算符有“：”(冒号)、“，”(逗号)和空格，其中，冒号为区域运算符，逗号为联合运算符，可以将多个引用合并为一个引用，空格为交叉运算符，产生对同时属于两个引用的单元格区域的引用。运算符应用可参考表4-1。

3.自动求和

Excel 2010求和操作步骤如下：

(1)选定要自动求和的区域。

(2)单击“开始”选项卡“编辑”组中的“求和”按钮，则在当前单元格中自动插入求和函数(或在“公式”选项卡“函数库”工具组中应用“自动求和”)，也可通过下拉列表选择其他求和功能，操作如图4-32所示。

如果当前单元格正上方的单元格中没有数字，那么自动求和将用类似的方法在当前单元格所在行的左侧搜索并进行求和。

例如，在职工工资表中包括基本工资、岗位津贴、差旅补贴、水/电费，要求使用求和操作计算基本工资、岗位津贴、差旅补贴、水/电费的“总计”“平均值”和“最大值”。

计算总计：选中E14单元格，单击“开始”选项卡“编辑”工具组中的“求和”按钮，再单击回车键即可计算基本工资的总计，向右填充计算其他项的总计。

计算平均值：选中E15单元格，单击“开始”选项卡“编辑”工具组中的“求和”按钮右侧的黑三角，选中下拉列表中的“平均值”选项，通过鼠标拖动选中E4∶E13单元格区域后，再单击回车即可计算基本工资的平均值，向右填充计算其他项的平均值。 同理可以计算出各项的最大值，结果如图4-33所示。

4.单元格引用

单元格引用是对工作表的一个或一组单元格进行标识，它告诉Excel公式使用哪些单元格的值。 通过引用，可以在一个公式中使用工作表不同部分的数据，或者在几个公式中使用同一单元格中的数值。 由于一个工作簿文件可以有多个工作表，为了区分不同的工作表中的单元格，要在地址前面增加工作表的名称，有时不同工作簿文件中的单元格之间要建立连接公式，前面还需要加上工作簿的名称，例如：［工资表］Sheet1! C5指定的就是“工资表”工作簿文件中的“Sheet1”工作表中的“C5”单元格。

单元格的引用可分为相对引用、绝对引用和混合引用。

(1)相对引用。 单元格或单元格区域的相对引用是指相对于包含公式的单元格的相对位置。 例如，单元格B2包含公式 =A1*2，如图4-34所示。

在复制包含相对引用的公式时，Excel将自动调整复制公式中的引用，以便引用相对于当前公式位置的其他单元格。 例如，单元格B2中含有公式：=A1*2，A1是B2左上方的单元格，拖动B2的填充柄将其复制至单元格B4时，单元格B3中的公式为：=A2*2;单元格B4中的公式为：=A3*2，如图4-35所示。

(2)绝对引用。 绝对引用是指引用单元格的绝对名称。 例如，如果公式将单元格A1乘以单元格A2(=A1*A2)放到A4中，现在将公式复制到另一单元格中，则Excel将调整公式中的两个引用。 如果不希望这种引用发生改变，须在引用的“行号”和“列号”前加上“$”符号，这样就是单元格的绝对引用。 A4中输入公式如下∶

=$A$1*$A$2

复制A4中的公式到任何一个单元格其值都不会改变。

(3)混合引用。 混合引用是指在单元格地址的行号或列号前加上“$”符号，如$A1或A$2。 公式复制时，公式的相对地址部分将随位置变化，而绝对地址部分仍保持不变。

例如，在B3单元格中输入公式=A$1+$B2，如果将公式从B3复制到D4单元格，则D4中的公式为=C$1+$B3，如图4-36所示。

三种引用地址输入时可以配合F4键相互转换：在编辑栏中选择要更改的引用并按“F4”键;每次按“F4”键时，Excel会在以下组合间切换。

①绝对列与绝对行(例如，$A$1);②相对列与绝对行(A$1);③绝对列与相对行($A1);④相对列与相对行(A1)。

例如，在公式中选择地址$A$1并按“F4”键，引用将变为A$1。 再一次按“F4”键，引用将变为$A1，以此类推。

5.单元格区域与名称

可以给工作表中单元格、单元格区域定义一个描述性的、便于记忆的名称，使其更直观地反映单元格或单元格区域中的数据所代表的含义。

在“公式”选项卡“定义的名称”组中可以创建管理区域名称。 也可以在编辑栏左端的“名称框”中创建、修改区域名称：选定需要命名的单元格或单元格区域，在“名称框”键入名称，按回车键确认。

4.3.2 函数及应用

1.函数的分类与常用函数

函数是预定义的内置公式。 它有其特定的格式与用法，通常每个函数由一个函数名和相应的参数组成。 参数位于函数名的右侧并用括号括起来，它是一个函数用以生成新值或进行运算的信息，大多数参数的数据类型都是确定的，而其具体值由用户提供。

在Excel 2010中，函数按其功能可分为财务函数、日期时间函数、数学与三角函数、统计函数、查找与引用函数、数据库函数、文本函数、逻辑函数以及信息函数，这里主要介绍常用函数SUM、AVERAGE、COUNT、MAX和MIN的功能和用法，如表4-2所示。

2.输入函数

在Excel 2010中，函数可以直接输入，也可以使用命令输入。

当用户对函数非常熟悉时，可采用直接输入法，首先单击要输入的单元格，再依次输入等号、函数名、具体参数(要带左右括号)，并按回车键或单击按钮以确认即可。

若用户对函数不太熟悉，可利用“粘贴函数”，并按照提示按需选择。

例如，利用函数计算如图4-37所示工资表中基本工资、岗位津贴、差旅补贴、水/电费的“平均值”和“最大值”。

(1)选中D6单元格，然后打开“公式”选项卡，单击“函数库”组中的“插入函数”按钮，弹出“插入函数”对话框，如图4-38所示。

(2)在“选择函数”列表框中选择AVERAGE选项，单击“确定”按钮，弹出“函数参数”对话框，在“Number1”文本框中输入“D2∶D5”，如图4-39所示。

用户可直接在参数框Number1中输入数值、单元格或单元格区域。 当区域范围较大或有多个区域范围时，用户可利用参数框Number右侧的一个带红色箭头的按钮选择区域范围，办法是先单击右侧红色箭头按钮，对话框会自动缩小，此时用鼠标左键在表格工作区拖动数据范围，松开鼠标后该范围在表格中会由动态虚线框(称为点线框)表示，再单击缩小后对话框中的红色箭头按钮，返回参数对话框，所选择的区域会由Excel自动用单元格区域引用的形式表示出来。 用户在参数框Number1中选择了单元格或区域，可再在Num-ber2中选择另一个单元格或区域，继续在Number3、Number4……中选择，Excel共提供了30个用于选择范围的参数框。 所需的多个范围选择完毕后，单击确定即可在所需单元格中得到函数运算结果。

(3)单击“确定”按钮，即可计算出“基本工资”的平均值，使用填充的方法计算工资中其他项目的平均值。 同理，在“插入函数”对话框选择MAX函数，可以计算出“基本工资”的最大值，填充工资中其他项目的最大值，最终效果如图4-40所示。

如果公式中含有函数，当输入函数时可按照以下步骤操作：

(1)直接输入公式函数名格式文本，或者选择编辑框右边的插入函数按钮，在“插入函数”对话框中选择需要的函数。

(2)输入或选择要引用的单元格或单元格区域设置函数的参数，然后单击“确定”即可。

在Excel 2010中共有几百个函数，每个函数都有用法示意说明，用户也可以利用Office助手获得帮助以学习这些函数的用法。

【思考题】

(1)公式与函数有何区别?

(2)举例说明输入函数的步骤。

(3)Excel 2010包含哪几种类型的运算符?

(4)怎样理解单元格地址? 单元格地址引用有哪几种?

(5)在Excel 2010中，函数按其功能可分为哪几类?