第三章 公式及名字

1、第三章第三章 公式与名字公式与名字本章学习目标本章学习目标1、领会Excel公式的意义2、理解引用的形式和用法3、掌握数组公式的用法4、掌握名字及其应用一、公式、运算符、公式nExcel的公式由运算符、数值、字符串、变量和函数组成。n公式必须以等号“=”开头，即在Excel的单元格中，凡是以等号开头的输入数据都被认为是公式。在等号的后面可以跟数值、运算符、变量或函数，在公式中还可以使用括号。 n例如：=10+4*6/2+(2+1)*50 就是一个公式，可以在任何单元格中输入此公式Excel会把公式的计算结果显示在相应的单元格中一、公式、运算符2、运算符n在公式中可以使用运算符。nExcel中的

2、运算符运算符运算符运算功能运算功能优先级优先级( )括号括号1-负号负号2%百分号百分号3乘方乘方4*与与/乘、除法乘、除法5+与与-加、减法加、减法6&文本连接文本连接7=、=、等于、小于、大于、小于等于、小于、大于、小于等于、大于等于、不等于等于、大于等于、不等于8一、公式、运算符n说明：说明：n括号的运算级别最高，在Excel的公式中只能使用小括号，无中括号和大括号。小括号可以嵌套使用，当有多重小括号时，最内层的表达式优先运算。同等级别的运算符从左到右依次进行。n“&”为字符连接运算，其作用是把前后的两个字符串连接为一串。n例如，ABC&DEF的结果为“ABCDE

3、F”，财务&经济的结果为“财务经济”。二、引用1、引用的概念n在公式中用到了其他单元格在表格中的位置。引用的作用在于标识工作表中的单元格或单元格区域，并指明公式中所使用的数据的单元格位置。 n引用不同工作簿中的单元格称为链接。2、引用的类型nA1引用nRC引用二、引用3、相对引用、相对引用n相对引用也称为相对地址引用，是指在一个公式中直接用单元格的列标与行号来取用某个单元格中的内容。 二、引用n相对引用的例子n某超市3月份的进货单如下图所示，计算“金额”。在在G3计算公计算公式式=C3*D3*F3然后向下复然后向下复制该公式！制该公式！二、引用4、绝对引用n绝对引用总是在指定位置引用单

4、元格。如果公式所在单元格的位置改变，绝对引用保持不变。 n绝对引用的形式是在引用单元格的列号与行号前面加“$”符号。n比如，$A$1就是对A1单元格的绝对引用。 二、引用n绝对引用的案例n假设LG空调在重庆各商场的销售价格相同，重庆各商场的销售数据如下图所示。计算各商场的销售总额。 在在E8输入公式：输入公式：=$c$5*C8然后向下复制此公式！然后向下复制此公式！二、引用5、混合引用n混合引用具有绝对列和相对行，或是绝对行和相对列。比如，$A1、$B1 n案例二、引用6、内部引用与外部引用 1）引用相同工作表中的单元格，例：=G3+G5+G10*102）引用同一工作簿的不同工作表中的单元格，

5、例=Sheet1!G3+Sheet1!G5+Sheet1!E273）引用不同工作簿中的单元格，例：=Book1Sheet1!$IL$4+Book1Sheet2!$E$74）同一公式中存在几中不同的引用，例：=Book1Sheet1!$A$4+Sheet1!G7+F9二、引用7、公式的使用中的错误信息 错 误 值错错 误误 原原 因因# 单元格所含的数字、日期或时间比单元格宽，或者单元格的日期、时间公式产生了单元格所含的数字、日期或时间比单元格宽，或者单元格的日期、时间公式产生了一个负值，就会产生一个负值，就会产生 # 错误。错误。#VALUE!1. 在需要数字或逻辑值时输入了文本，在需要数字或

6、逻辑值时输入了文本， Excel 不能将文本转换为正确的数据类型。不能将文本转换为正确的数据类型。2. 输入或编辑数组公式时，按了输入或编辑数组公式时，按了Enter键。键。3. 把单元格引用、公式或函数作为数组把单元格引用、公式或函数作为数组常量输入。常量输入。#DIV/O!1. 输入的公式中包含明显的除数为零（输入的公式中包含明显的除数为零（0）#NAME?1. 在公式中输入文本时没有使用双引号。但这些文本又不是名字。在公式中输入文本时没有使用双引号。但这些文本又不是名字。2. 函数名的拼写函数名的拼写错误。错误。3. 删除了公式中使用的名称。删除了公式中使用的名称。4. 名字拼写有错名字

7、拼写有错#N/A1. 在调用函数时参数。在调用函数时参数。2. 在数组的参数的行数或列数与包含数组公式的区域的行数在数组的参数的行数或列数与包含数组公式的区域的行数或列数不一致。或列数不一致。3. 在没有排序的数据表中使用了在没有排序的数据表中使用了VLOOKUP、HLOOKUP或或MATCH工作表函数查找数值工作表函数查找数值#REF!删除了公式中所引用的单元或单元格区域删除了公式中所引用的单元或单元格区域#NUM!1. 由公式产生的数字太大或太小，由公式产生的数字太大或太小，Excel 不能表示。不能表示。2. 在需要数字参数的函数中使在需要数字参数的函数中使用了非数字参数用了非数字参数#

8、NULL! 在公式的两个区域中加入了空格从而求交叉区域，但实际上这两个区域无重迭区在公式的两个区域中加入了空格从而求交叉区域，但实际上这两个区域无重迭区域域三、数组公式及其应用1、数组公式的含义n数组公式能够对两组或两组以上的数据（两个或两个以上的单元格区域）同时进行计算。n在数组公式中使用的数据称为数组参数，数组参数可以是一个数据区域，也可以是数组常量。2、数组公式的建立方法n（1）选中需要保存数组公式结果的单元格或单元格区域。n（2）输入公式的内容。n（3）按Ctrl+Shift+Enter键。三、数组公式及其应用3、用数组公式计算两个数据区域的乘积 【例】 某超市3月份进购的产品如下表所

9、示，已知各种产品的数量、单价和折扣，要计算各种产品的应付金额。 三、数组公式及其应用4、数组扩展 n在公式中用数组作为参数时，所有的数组必须是同维的。如果数组参数或数组区域的维数不匹配，Excel会自动扩展该参数 。n比如，某商场29寸电视机售价为3128元，有一个10天的销售记录，如下图的B、C两列所示。现要计算每天的销售额。 三、数组公式及其应用1、选中、选中F8:F14；2、输入公式：、输入公式：=C8:C14*C53、按、按Ctrl+Shift+Enter三、数组公式及其应用5、二维数组 n涉及许多行(或列)数据处理的数组公式，就是所谓的二维数组。n合理地运用二维数组的运算功能，会提高

10、数据处理的能力，有时在不同工作表之间进行数据汇总时会特别有效 。n案例n某食品商店将每个月的食品销售资料统计在一个工作表中，16月的销售数据分别记录在Book1工作簿的Sheet1Sheet6中，现在要统计每种商品前6个月的销售总量和销售金额。见下图：三、数组公式及其应用四、名字及其应用1、名字概述、名字概述 n可以给一个单元格或单元格区域取一个名字，这个名字可以出现在公式中，用来指代引用的单元格或单元格区域，这会使公式的意义更加明确。n名字其实是一个标识符，由字母或下划线开头的一个或多个字符组成。在Excel中，还可以用汉字字符作为名字。名字不区分字母的大小写，比如ABC、abc、Abc都是

11、同一个名字。n可以为同一单元格或单元格区域定义多个不同的名字。n名字一旦定义就可以在同一工作薄的不同工作表之间共用。四、名字及其应用2、名字的意义n在Excel中，同一个工作簿中的名字是共享的，在一个工作表中定义的名字，可以被另一个工作表的公式引用，这使公式具有更强大的功能，它能够利用单元格的名字在不同的工作表中查找到正确的单元格或单元格区域。n名字给单元格的引用带来了许多方便，因为它使人们不必记住单元格在工作表中的引用位置，用名字就能找到它。四、名字及其应用n名字应用案例名字应用案例n在下图中，B、C列是某商场所有产品的当日单价，E、F列是产品当天的销售记录。销售的产品多达几百种，而每天则可

12、能只卖掉其中的几十种产品，现在要计算当日产品的销售金额。 n若不用名字，单价要从C列的几百个数据中去查找！ 四、名字及其应用3、名字的定义 n在Excel中，可以为一个独立的单元格、连续的单元格区域或许多不连续的单元格构成的单元格组合定义一个名字（或多个名字） n定义名字有多种方法，如定义、粘贴、指定或标志等 四、名字及其应用4、定义单元格或单元格区域的名字 D3单元格的名字定义方法如下：单元格的名字定义方法如下：四、名字及其应用n（1）选择“插入”|“名称”|“定义”菜单命令.n（2）在弹出的“定义名字”对话框的“引用位置” 输入要定义名字的单元格引用位置。n（3）在“在当前工作簿中的名称”

13、 中输入名字 四、名字及其应用5、将工作表的首行（或首列）定义为名字n“指定”的方式一次可定义多个名字：可以将表格的首行或首列指定为相应的列或行的名字。n方法n（1）选中要指定名字的单元格区域。n（2）选择“插入”|“名称”|“指定”菜单项。n（3）在“指定名称”对话框中，选中“首行”、“最左列”的复选框。这样就把该表的首行、首列指定成了相应的列、行的名字这样就把该表的首行、首列指定成了相应的列、行的名字四、名字及其应用n指定首行、首列为名字的案例四、名字及其应用6、 名字的应用名字的应用n1）通过名字在不同的工作表之间传递数据）通过名字在不同的工作表之间传递数据 通过名字查找蔬菜单价通过名字

14、查找蔬菜单价指定指定A2:B12最左边为名字最左边为名字四、名字及其应用n2）名字与Indirect函数结合应用nIndirect函数与名字相结合，可使数据查找更方便。Indirect函数的用法是：Indirect(x)其中的X可以是单元格引用或名字。n若X为单元格引用，它必须用“”引起来，如：=INDIRECT(“A1”)，将返回A1单元格中的内容n若X为单元格名字，它将返回该名字所对应的单元格的内容。四、名字及其应用n某蔬菜商在一个工作表中保存蔬菜的单价，在另一工作表中保存销售记录，现要查找销售记录表中各蔬菜的单价。指定指定A2:B11区域区域的的“最左列最左列”为名为名字字在D2中输入公式：=INDIRECT(B2)向下复制该公式！四、名字及其应用n3应用行列交叉点查找数据n所谓行列交叉点指的是由行的名字 列的名字确定的单元格。 n把一个工作表的首行、首列指定为名字之后，可以使用名字引用行、列交叉点对应单元格中的数据。n案例n某电信公司的电话用户档案，其中有成千上万个电话用户的电话号码。如果知道某