第5章应用公式与函数

图5-2 输入等号
图5-3 输入数值
步骤3 单击C3单元格，输入第二个参数，如图5-4所示。 步骤4 输入运算符“+”，单击D3单元格，输入第三个参数，如图5-5所示。 步骤2 输入第一个参数，并在数值后输入运算符“+”，如图5-3所示。 步骤5 公式输入完毕后，按下Enter键即可在E3单元格中得出计算结果了。同时，鼠 标光标向下移动到E4单元格中，如图5-6所示。
图5-16 单击“插入函数”按钮
图5-17 “插入函数”对话框
步骤3 弹出“函数参数”对话框，然后单击Number1文本框设置参与运算的单元格地 址，如图5-18所示。
步骤4 单击“确定”按钮，计算结果如图5-19所示。
图5-18 “函数参数”对话框
图5-19 查看计算结果
2．使用功能区中的函数命令
图5-23 添加“评级”列
图5-24 使用嵌套公式
步骤3 向下填充公式，对其他店铺进行评级，如图5-25所示。
图5-25 填充公式
● 5.4 审核公式与函数
在 Excel 中使用公式和函数时，不一定每次返回的都是正确的结果，也有可能出现 “###”、“#NUM!”、“#NAME?”、“#N/A”等错误值，这时就需要用户检查公式和 函数，找出出错原因，彻底解决问题。
优先级
1 2 3 4 5 6 7 8 9 10
逻辑运算
等于、小于、大于、小于等于、大于等于、不来自百度文库于
11
● 5.1.3 输入公式
Excel中的公式书写顺序与大家日常习惯的公式书写略有不同，需要用户先输入一个 等号(=)，然后才输入运算式。 步骤1 启动Excel软件，然后在Sheet工作表中输入如图5-2所示的数据，接着选中E3 单元格，并输入等号(=)。 步骤2 输入第一个参数，并在数值后输入运算符“+”，如图5-3所示。
图5-4 输入第二个参数
图5-5 输入第三个参数
图5-6 得出公式结果
● 5.1.4 编辑公式
在确认输入的公式后，如果发现公式中的参数或运算符有误，用户可以使用下述几 种方法进行修改。
1．鼠标双击法
双击含有错误公式的单元格，这时会显示出公式，并进入公式的编辑状态，接着修 改公式中的错误，按下Enter键确认修改后的公式即可。
函数：是指使用一些称为参数的特定数值来按特定的顺序或结构执行计算的计算 式。在Excel程序中预定义了很多函数，如用于财务的，有计算日与时间的，有数 学与三角函数等。
● 5.1.2 了解公式运算符及其优先级
Excel公式中的各种运算符及其运算中的优先级顺序，如表5-1所示。
表5-1 Excel公式中的运算符优先等级顺序 运算类型 运算符
嵌套函数是指在某些情况下，用户可能需要将某函数作为另一函数的参数使用，这 一函数就是嵌套函数。下面以在IF函数中嵌套函数为例进行介绍，具体操作步骤如下。
步骤1 在F2单元格中输入“评级”，如图5-23所示。 步骤2 在F3单元格中输入嵌套公式“=IF(E3>=240000,”优“,(IF(AND(E3>=200000, E3 <230000),”良“,”一般“ ) ) )”，按下Enter键确认，如图5-24所示。
●5.3 使用函数 ●5.3.1 什么是函数
●5.5 定义与使用名称
●5.5.1 定义单元格名称 ●5.5.2 应用名称 ●5.5.3 管理名称
●5.3.2 插入函数
● 5.1 使用公式
计算是Excel程序的主要功能之一，Excel的计算功能需要借助公式函数来实现。那么 ，在Excel中使用的公式与我们日常使用的公式有没有区别呢？该如何向Excel中输入、修 改公式呢？这就是本节要介绍的内容。
图5-13 输入含有混合引用地址的公式
图5-14 复制公式发生变化
● 5.3 使用函数
使用Excel 2010的内置函数，用户可以把结构复杂的计算公式简单化。
● 5.3.1 什么是函数
函数是Excel预先定义好的特殊公式。一般由函数名称、“(”、函数参数和“)”组 成，结构形式如下。 函数名称(Number1,Number2, ...) 如果在函数前面加上符号“=”，它就变成公式了。如图5-15所示，是在E3单元格中 使用SUM函数计算的结果。
图5-15 使用SUM函数
在Excel 2010中提供了300多个内置函数，为了方便用户使用，下面把这些函数划分 为12种类型，如表5-2所示。
表5-2 Excel 2010的函数类型
函数类型
财务函数 日期与时间函数 数学与三角函数 统计函数 查找与引用函数
功 能
进行财务运算，如确定债券价值、固定资产年折旧额等 实现日期和时间的自动更新，或者在公式中分析处理日期与时间值 进行数学计算，包括取整、求和、求平均数以及计算正/余弦值等三 角函数 对选中的单元格区域进行统计分析 在指定区域中查找指定数值或查找一个单元格引用
图5-1 Excel公式的结构

常量数值：是指值不能改变的量。 运算符：指公式中对各元素进行计算的符号，如+、—、*、/、%、<、>等。 单元格引用：用于表示单元格在工作表中所处位置的坐标集，如A3、D5等。 单元格区域引用：是指左上角的单元格与右下角的单元格地址所组成的矩形方阵 ，如B2:C3单元格区域包含B2、B3、C2、C3四个单元格。
函数类型
文本函数 逻辑函数 信息函数 工程函数 多位数据集函数
功 能
用于对字符串进行提取、转换等 用于逻辑判断或者复合检验 用于确定存储在单元格中的数据类型 用于工程分析 用于联机分析处理(OLAP)数据库
数据库函数
按照特定条件分析数据
加载宏和自动化函数
用于加载宏、自定义函数等
● 5.3.2 插入函数
3．直接输入函数
如果用户对要使用的函数非常了解，可以在单元格或编辑栏中直接输入函数。例如 选中F5单元格，然后在编辑栏中输入“=SUM(”，这时，就会出现该函数的语法提示，如 图5-22所示，根据提示输入参数即可。
图5-20 单击“自动求和”按钮
图5-21 插入SUM函数
图5-22 直接输入函数
● 5.3.3 应用嵌套函数
第5章 应用公式与函数
●5.1 使用公式
●5.1.1 什么是公式 ●5.1.2 了解公式运算符及其优 先级 ●5.1.3 输入公式 ●5.1.4 编辑公式 ●5.1.5 使用数组公式 ●5.2 单元的格引用 ●5.3.3 应用嵌套函数 ●5.4 审核公式与函数 ●5.4.1 公式与函数运算错误简 介 ●5.4.2 监视窗口 ●5.4.3 追踪引用与从属单元格 ●5.4.4 显示与隐藏公式 ●5.4.5 错误检查
在前面学习了公式的使用方法。那么，如何在Excel 2010中插入函数呢？下面介绍三 种方法供用户使用。
1．使用”插入函数”对话框
使用“插入函数”对话框插入函数的操作步骤如下。 步骤1 选中要插入函数的单元格，然后在“公式”选项卡下的“函数库”组中单击 “插入函数”按钮，如图5-16所示。 步骤2 弹出“插入函数”对话框，然后在“或选择类别”下拉列表框中选择“常用 函数”选项，接着在“选择函数”列表框中选择SUM选项，单击“确定”按钮，如图5-17 所示。
使用功能区中的函数命令插入函数的方法如下所示。 步骤1 在工作表中选中要输入公式的单元格，然后在“公式”选项卡下的“函数库 ”组中单击“自动求和”按钮，如图5-20所示。 步骤2 在E4单元格中插入SUM函数，默认参数为B4:D4，如图5-21所示。要想改变该 函数的默认参数，拖动鼠标重新选择单元格区域，按下Enter键确认公式即可。
● 5.1.1 什么是公式
公式是用数学符号表示几个量之间关系的式子。在Excel中，公式是指可以对工作表 进行计算和处理的计算式。一个完成的公式，需要有等号(=)、运算符和参数，该参数可 以是常量数值，也可以是单元格引用或单元格区域引用，或是函数，如图5-1所示的公式 。其作用是计算A3单元格中的数值乘以 3.256与单元格区域B2:C3中所有数据的最小值的 和。
图5-7 输入数组公式
图5-8 查看数组公式的计算结果
● 5.2 单元格引用
在公式中使用单元格引用共有3种情况，分别是相对引用、绝对引用和混合引用，下 面将逐一进行介绍。
1．相对引用
在公式中的相对单元格引用包含公式和单元格引用的相对位置。如图5-9所示，在H3 单元格中输入公式“=B3+C3+D3+E3+F3+G3”，这时表示Excel将在H3单元格左边的第2个单 元格、第3个单元格、第4个单元格、第5个单元格、第6个单元格和第7个单元格中查找数 据，并把它们相加，然后把相加的值赋予H3。此处，B3、C3、D3、E3、F3、G3就是相对于 公式所在的单元格H3数据的相对位置。 如果公式所在的单元格的位置改变，引用也随之改变。如果多行或多列地复制公式 ，引用会自动调整。在默认情况下，新公式会使用相对引用。例如，将H3单元格内的公式 复制到H4单元格，将自动从“=B3+C3+D3+E3+F3+G3”调整为“=B4+C4+D4+E4+F4+G4”，如 图5-10所示。
这里输入合计计算公式为 “=B3+C3+D3+E3+F3+G3”
复制公式时， 相对单元格地址随着改变， 公式变为“=B4+C4+D4+E4+F4+G4”
图5-9 输入公式
图5-10 复制公式
2．绝对引用
绝对引用和相对引用的不同之处在于：复制公式时使用绝对引用，单元格引用不会 发生变化。在列号和行号前分别加上符号“$”，例如在H5单元格内输入公式 “=$B$5+$C$5+$D$5+$E$5+$F$5+$G$5”，表示对B5、C5、D5、E5、F5、G5单元格的绝对引 用，如图5-11所示。
● 5.4.1 公式与函数运算错误简介
默认情况下，在Excel 2010中进行数据计算时，Excel会使用一些规则来帮助用户检查 公式中的错误，如果公式错误，会在每个存在错误公式的单元格的左上角上出现一个绿三 角，对不同错误，出现不同的提示符。那么这些错误值是如何产生的？如何解决呢？这就 是本节要介绍的内容了。
2．利用编辑栏
选中含有错误公式的单元格，这时会在编辑栏中显示公式，将鼠标光标定位到编辑 栏中，接着修改公式，再按下Enter键确认。
3．按功能键F2
选中含有错误公式的单元格，按下F2键在单元格中显示出公式，并进入公式的编辑 状态，接着修改公式，再按下Enter键确认。
● 5.1.5 使用数组公式
数组公式是一个典型的公式，它可以进行多个计算，并返回多个结果。数组公式需 要有两组或两组以上的数组参数，并且每组数组参数必须具有相同数目的行和列。使用数 组公式的方法如下。 步骤1 选中E3:E6单元格区域，然后再输入数值公式“=B3:B6+C3:C6+D3:D6”，如 图5-7所示。 步骤2 按下Ctrl+ Enter组合键结束输入，得出计算结果，如图5-8所示。
如果公式所在的单元格位置改变，引用不改变。例如，将H5单元格内的公式复制到 H6 单元格，公式不发生变化，还是“=$B$5+$C$5+$D$5+$E$5+$F$5+$G$5 ”，如图 5-12 所示。
图5-11 输入含有绝对引用地址的公式
图5-12 公式没有变化
3．混合引用
混合引用包含相对引用和绝对引用，即具有绝对列和相对行，或者绝对行和相对列 。绝对引用列采用“$A1”等形式，绝对引用行采用“A$1”等形式。如果公式所在的单元 格的位置改变，则相对引用改变，而绝对引用不变。 例如在H7单元格中输入公式“=$F3*G$3”，如图5-13所示。如果公式所在单元格的 位置改变，则相对引用改变，而绝对引用不变。例如，将H7单元格内的公式复制到H8单元 格中，公式中相对引用改变，而绝对引用不变，公式变为“=$F4*G$3”，如图5-14所示。
：(冒号) 引用运算 (空格) ，(逗号) 数学运算 () % 数学运算 ^ ×和/ +和文本运算 & = 、 < 、 > 、 <= 、 >= 、 <>
运算功能
区域运算符，用于引用单元格区域，例如A1:B2，注意，C:C用来引用整个C列；4:4用来引用整个第4行 交叉运算符，用于引用两个单元格区域相交部分 联合运算符，用于把两个单元格区域合并在一起 括号，可改变Excel的内置优先顺序，括号中的内容最先计算 负号 百分号 求幂，公式“=x^y”的含义就是求数值x的y次幂。例如，公式“=2^4”的结果为16(2×2×2×2＝16) 乘和除 加和减 文本连接，例如A1 中是“新年”，A2 中是“快乐”，那么输入公式“=A1&A2 ”后，返回的就是“新 年快乐”