Excel与数据处理（一）：ch4

本章 学习目标
1,理会 Excel公式的意义
2,理解引用的形式和用法
3,掌握数组公式的用法
4,掌握名字及其应用
4.1 公式、运算符
１, 公式
? Excel的公式由运算符, 数值, 字符串, 变量和函
数组成 。
? 公式必须以等号, =”开头, 即在 Excel的单元格中,
凡是以等号开头的输入数据都被认为是公式 。 在等
号的后面可以跟数值, 运算符, 变量或函数, 在公
式中还可以使用括号 。
? 例如,
=10+4*6/2+(2+1)*50
就是一个公式, 可以在任何单元格中输入此公式, Excel会
把公式的计算结果显示在相应的单元格中,
4.1 公式、运算符
2,运算符
? 在公式中可以使用运算符 。
? Excel中的运算符
运算符 运算功能 优先级
( ) 括号 1
- 负号 2
% 百分号 3
^ 乘方 4
*与 / 乘、除法 5
+与 - 加、减法 6
& 文本连接 7
=,<,>、
<=,>=,<>
等于、小于、大于、小于
等于、大于等于、不等于 8
4.1 公式、运算符
? 说明,
? 括号的运算级别最高, 在 Excel的公式中只能使用
小括号, 无中括号和大括号 。 小括号可以嵌套使用,
当有多重小括号时, 最内层的表达式优先运算 。 同
等级别的运算符从左到右依次进行 。
?, &”为字符连接运算, 其作用是把前后的两个字符
串连接为一串 。
? 例如, "ABC"&"DEF"的结果为, ABCDEF”,"财务 "&"经
济 "的结果为, 财务经济, 。
4.2 引用
1,引用的概念
? 在公式中用到了其他单元格在表格中的位置 。 引用
的作用在于标识工作表中的单元格或单元格区域,
并指明公式中所使用的数据的单元格位置 。
? 引用不同工作簿中的单元格称为链接 。
2,引用的类型
? A1引用
? RC引用
4.2 引用
3,相对引用
? 相 对 引 用 也
称 为 相 对 地
址 引 用, 是
指 在 一 个 公
式 中 直 接 用
单 元 格 的 列
标 与 行 号 来
取 用 某 个 单
元 格 中 的 内
容 。
4.2 引用
? 相对引用的例子
? 某超市 3月份的进货单如下图所示, 计算, 金额, 。
在 G3计算公
式
=C3*D3*F3
然后向下复
制该公式！
4.2 引用
4,绝对引用
? 绝对引用总是在指定位置引用单元格 。 如果公式所
在单元格的位置改变, 绝对引用保持不变 。
? 绝对引用的形式是在引用单元格的列号与行号前面
加, $”符号 。
? 比如, $A$1就是对 A1单元格的绝对引用 。
4.2 引用
? 绝对引用的案例
? 假设 LG 空 调 在
重庆各商场的
销 售 价 格 相 同,
重庆各商场的
销售数据如下
图所示 。 计算
各商场的销售
总额 。
在 E8输入公式,
=$c$5*C8
然后向下复制此公式！
4.2 引用
5,混合引用
? 混合引用具有绝对列和相对行, 或是绝对行和相
对列 。 比如, $A1,$B1
? 案例
4.2 引用
6,内部引用与外部引用
1） 引用相同工作表中的单元格, 例,
=G3+G5+G10*10
2） 引用同一工作簿的不同工作表中的单元格, 例
=Sheet1!G3+Sheet1!G5+Sheet1!E27
3）引用不同工作簿中的单元格，例,
=[Book1]Sheet1!$IL$4+[Book1]Sheet2!$E$7
4）同一公式中存在几中不同的引用，例,
=[Book1]Sheet1!$A$4+Sheet1!G7+F9
4.2 引用
7,公式的使用中的错误信息
错 误 值 错 误 原 因
######## 单元格所含的数字、日期或时间比单元格宽，或者单元格的日期、时间公式产生了
一个负值，就会产生 ##### 错误。
#VALUE! 1,在需要数字或逻辑值时输入了文本,Excel 不能将文本转换为正确的数据类型。 2,
输入或编辑数组公式时，按了 Enter键。 3,把单元格引用、公式或函数作为数组
常量输入。
#DIV/O! 1,输入的公式中包含明显的除数为零（ 0）
#NAME? 1,在公式中输入文本时没有使用双引号。但这些文本又不是名字。 2,函数名的拼写
错误。 3,删除了公式中使用的名称。 4,名字拼写有错
#N/A 1,在调用函数时参数。 2,在数组的参数的行数或列数与包含数组公式的区域的行数
或列数不一致。 3,在没有排序的数据表中使用了 VLOOKUP,HLOOKUP或
MATCH工作表函数查找数值
#REF! 删除了公式中所引用的单元或单元格区域
#NUM! 1,由公式产生的数字太大或太小,Excel 不能表示。 2,在需要数字参数的函数中使用
了非数字参数
#NULL! 在公式的两个区域中加入了空格从而求交叉区域，但实际上这两个区域无重迭区域
4.3 数组公式及其应用
1,数组公式的含义
? 数组公式能够对两组或两组以上的数据 （ 两个或两
个以上的单元格区域 ） 同时进行计算 。
? 在数组公式中使用的数据称为数组参数, 数组参数
可以是一个数据区域, 也可以是数组常量 。
2,数组公式的建立方法
? （ 1） 选中需要保存数组公式结果的单元格或单元
格区域 。
? （ 2） 输入公式的内容 。
? （ 3） 按 Ctrl+Shift+Enter键 。
4.3 数组公式及其应用
3,用数组公式计算两个数据区域的乘积
【 例 4-1】 某超市 3月份进购的产品图 4-5所示, 已知
各种产品的数量, 单价和折扣, 要计算各种产品的
应付金额 。
4.3 数组公式及其应用
4,数组扩展
? 在公式中用数组作为参数时, 所有的数组必须是同
维的 。 如果数组参数或数组区域的维数不匹配,
Excel会自动扩展该参数 。
? 比如, 某商场 29寸电视机售价为 3128元, 有一个
10天的销售记录, 如下图的 B,C两列所示 。 现要
计算每天的销售额 。
4.3 数组公式及其应用
1、选中 F8:F14；
2、输入公式,
=C8:C14*C5
3、按 Ctrl+Shift+Enter
4.3 数组公式及其应用
5,二维数组
? 涉及许多行 (或列 )数据处理的数组公式, 就是所谓
的二维数组 。
? 合理地运用二维数组的运算功能, 会提高数据处理
的能力, 有时在不同工作表之间进行数据汇总时会
特别有效 。
4.3 数组公式及其应用
? 案例
? 某食品商店将每个月的食品销售资料统计在一个工作表
中, 1～ 6月 的 销 售 数 据 分 别 记 录 在 Book1工 作 簿 的
Sheet1～ Sheet6中, 现在要统计每种商品前 6个月的销
售总量和销售金额 。 见下图,
4.4 名字及其应用
1,名字概述
? 可以给一个单元格或单元格区域取一个名字, 这
个名字可以出现在公式中, 用来指代引用的单元
格或单元格区域, 这会使公式的意义更加明确 。
? 名字其实是一个标识符, 由字母或下划线开头的
一个或多个字符组成 。 在 Excel中, 还可以用汉字
字符作为名字 。 名字不区分字母的大小写, 比如
ABC,abc,Abc都是同一个名字 。
? 可以为同一单元格或单元格区域定义多个不同的
名字 。
? 名字一旦定义就可以在同一工作薄的不同工作表
之间共用 。
4.4 名字及其应用
2,名字的意义
? 在 Excel中, 同一个工作簿中的名字是共享的, 在
一个工作表中定义的名字, 可以被另一个工作表的
公式引用, 这使公式具有更强大的功能, 它能够利
用单元格的名字在不同的工作表中查找到正确的单
元格或单元格区域 。
? 名字给单元格的引用带来了许多方便, 因为它使人
们不必记住单元格在工作表中的引用位置, 用名字
就能找到它 。
4.4 名字及其应用
? 名字应用案例
? 在下图中, B,C列是某商场所有产品的当日单价, E,F列是产品
当天的销售记录 。 销售的产品多达几百种, 而每天则可能只卖掉其
中的几十种产品, 现在要计算当日产品的销售金额 。
? 若不用名字, 单价
要从 C列的几百个
数据中去查找 ！
4.4 名字及其应用
3,名字的定义
? 在 Excel中, 可以为一个独立的单元格, 连续的单
元格区域或许多不连续的单元格构成的单元格组合
定义一个名字 （ 或多个名字 ）
? 定义名字有多种方法, 如定义, 粘贴, 指定或标志
等
4.4 名字及其应用
4,定义单元格或单元格区域的名字
D3单元格的名字定义方法如下,
4.4 名字及其应用
? （ 1） 选择, 插入, |“名称, |“定义, 菜单命令,
? （ 2） 在弹出的, 定义名字, 对话框的,引用位置,
输入要定义名字的单元格引用位置 。
? （ 3） 在, 在当前工作簿中的名称, 中输入名字
4.4 名字及其应用
5,将工作表的首行 （ 或首列 ） 定义为名字
?, 指定, 的方式一次可定义多个名字：可以将表格
的首行或首列指定为相应的列或行的名字 。
? 方法
? （ 1） 选中要指定名字的单元格区域 。
? （ 2） 选择, 插入, |“名称, |“指定, 菜单项 。
? （ 3） 在, 指定名称, 对话框中, 选中, 首行,,, 最
左列, 的复选框 。
这样就把该表的首行, 首列指定成了相应的列, 行的名字
4.4 名字及其应用
? 指定首行, 首列为名字的案例
4.4 名字及其应用
6,名字的应用
? 1） 通过名字在不同的工作表之间传递数据
通过名字查找蔬菜单价
指定 A2:B12
最左边为名字
4.4 名字及其应用
? 2） 名字与 Indirect函数结合应用
? Indirect函数与名字相结合, 可使数据查找更方便 。
Indirect函数的用法是,
Indirect(x)
其中的 X可以是单元格引用或名字。
? 若 X为单元格引用，它必须用,” 引起来，如：
=INDIRECT(“A1”)，将返回 A1单元格中的内容
? 若 X为单元格名字，它将返回该名字所对应的单元
格的内容。
4.4 名字及其应用
? 某蔬菜商在一个工作表中保存蔬菜的单价, 在另一
工作表中保存销售记录, 现要查找销售记录表中各
蔬菜的单价 。
指定 A2:B11区域
的, 最左列, 为名
字
在 D2中输入公式,
=INDIRECT(B2)
向下复制该公式！
4.4 名字及其应用
? 3,应用行列交叉点查找数据
? 所谓行列交叉点指的是由 [行的名字 列的名字 ]确定
的单元格 。
? 把一个工作表的首行, 首列指定为名字之后, 可以
使用名字引用行, 列交叉点对应单元格中的数据 。
? 案例
? 某电信公司的电话用户档案, 其中有成千上万个电话用
户的电话号码 。 如果知道某个用户的名字, 那么要在这
样的表中查找该用户的电话号码, 电话类型, 或申请日
期等数据就显得很困难, 因为表中的资料太多了 。 通过
行, 列交叉点的名字引用能很轻松地找到所需的数据 。
4.4 名字及其应用
1指定 A2:E17的
,首行, 最
左列, 为名
字 。
2,在任空白单
元格中输入
,客户名字
库 第二行
任一单元格
标题,, 就
能找到该客
户的对应数
据 。
The End