Excel与数据处理（一）：ch2

本章 学习目标
1,了解 Excel的数据类型
2,掌握不同类型数据的输入方法
3,掌握复制公式输入数据的方法
4,掌握大批量相同数据的输入方法
5,掌握通过自定义格式简少数据输入的方法
6,掌握用 IF,Vlookup等函数查找输入数据的方
法
7,掌握用 Rand和 int函数产生大量模拟数据的方
法 。
2.1 Excel的数据类型
1,Excel的常见数据类型
? 数字型, 日期型, 文本型, 逻辑型数据
? 数字型表现形式多样：货币, 小数, 百分数, 科学计数法,
各种编号, 邮政编码, 电话号码等多种形式
2.1 Excel的数据类型
? 2,Excel数值型数据的取值范围
功能 最大限制
数字精度 15 位
单元格中可键入的最大数值 9.99999999999999E307
最大正数 1.79769313486231E308
最小负数 -2.2250738585072E-308
最小正数 2.229E-308
最大负数 -2.2250738585073E-308
2.2 基本数据的输入
1,数据输入的一般过程
（ 1） 选定要输入数据的单元格 。
（ 2） 从键盘上输入数据 。
（ 3） 按 Enter键后
2,输入数值
（ 1） 正数的输入,+234,13,333,333,12E3
（ 2） 负数的输入,-234,(234),-12e2
（ 3） 分数的输入, 2 2/3,0 3/4
（ 4） 货币数据的输入,￥ 123,$21
2.2 基本数据的输入
3,输入文本
? 字符文本应逐字输入
? 数字文本以 ‘ 开头输入, 或用 =“数字, 方式输入 。
? 输入文本 32,可输入,=“32” 或输入,'32
? 文本形式的公式, 要通过, 插入, |“对象, 菜单命
令
? 如
? ?
???
?
???
? ?
?
2
2
2
2
1),,( ?
?
??
??
x
exf
2.2 基本数据的输入
4,输入日期
? 以 yy-mm-dd形式, 或 mm-dd形式输入
? 通过格式化得到其它形式的日期, 这样可减少输入
? 格式方法是选择, 格式, |“单元格, |“数字, |“日
期,
2.2 基本数据的输入
5,输入公式
? 公式是对工作表中的数值进行计算的等式 。 公式要
以等号 （ ＝ ） 开始 。
? 例如在 A3输入,=5+2*3,结果等于 2 乘 3 再加 5。
A3中将显示 11
2.3 相同数据的输入
1,复制相同数据
? 建立具有相同数据的不同工作表, 可用复制方法 。
? 某班主任建立相同的成绩表头, 可用此法 。
复制一班表头
2.3 相同数据的输入
2,填充复制相同数据
? 设要建立下图所示, 学生档案, 表 。 其中入学时间,
班级, 系都是相同数据 。
（ 1）在 G2中输入
,计算机通信,,然
后 Enter。
（ 2）单击 G2,G2右
下角会出现黑色小方
块，它就是 填充柄。
（ 3） 向下拖动填充柄，
拖过的单元格都被填
入了, 计算机通信, 。
2.3 相同数据的输入
3,用 Enter输入相同数据
? 设要建立下图所示, 学生档案, 表 。 其中入学时间,
班级, 系都是相同数据 。
（ 1）选中 F2,F11
（ 2）输入,JK001
（ 3） 按 Ctrl+Enter
可用同样方法输
入 C,G列数据
2.4 编号的输入
1,复制输入连续的编号
? 连续编号 （ 或等差, 等比性质的数据 ） 应该采用复
制或序列填充的方式进行输入 。
? 比如公职或单位的职工编号, 电话号码, 手机号码,
零件编号等 。
? 输入右图 A列的学号
（ 1）在 A2输入 1003020101
在 A3输入 1003020101
（ 2）选中 A2,A3
（ 3）向下拖动 A3单元格的填充柄。
2.4 编号的输入
2,填充产生连续编号
? 填充序列方式适用于输入较多
的连续编号数据 。
? 比 如 要 输 入 电 话 号 码,
663321240~663328000，
如右图所示, 方法如下 。
? 在第一个单元格中输入起始号
码
? 选择, 编辑, |“填充, |“序列,
菜单命令 。
? 在 Excel弹出的, 序列, 对话
框中指定, 列,
? 在步长中输入 1
? 在终止值中输入最后一个号码 。
2.4 编号的输入
3,利用自定义格式
产生特殊编号
? 人们常会遇到各种具
有一定规则的特殊格
式的编号 。 如右图的
保险号 。
1、选中要输入保险号的区域 2、选择, 格式, |,单元格,
菜单命令。
3、选中, 自定义,
4、输入自定格式,
p00-000-0000
2.4 编号的输入
? 用自定义格式输入大数字编号
? 当在单元格中输入一个位数较多的数值时 （ 比如超
过 15位的整数 ）, Excel会自动将输入的数据显示
为科学记数法, 这可能并不符合我们的需要 。
? 产生这种问题的原因是数值精度问题, Excel的默
认精度是 15位, 如果一个数值的长度超过 15位,
则 15位之后的数字都被视为 0。
? 这种位数较多的大数字编号, 可以用自定义格式进
行输入
2.4 编号的输入
? 用自定义格式输入大数
字编号
? 建立右图所示的电话用户档
案, 其中身份证号为 18位,
且前 15位全部相同 。
? 若直接输入，不仅数据多，
且要出错，比如在 G4输入：
510214196012301000,Exce
l会显示为 5.1021E17
? 解决方法是定义 G列保存身份证号
的单元格的自定义格式为,
"51021419601230"0000
设置自定义格式
后，只需要输入
身份证的最后 4
位编号
2.4 编号的输入
? 选中 D2单元格, 选择, 格式, |“单元格, 菜单项
?, 类型, 文本框中输入,"51021419601230"0000
2.5 组合多个单元格数据
1,用 &运算符组合数据
?, &”为字符连接运算, 其作用是把前后的两个字符串 （ 也
可以是数值 ） 连接为一个字符串 。 如, ADKDKD”&“DKA”的
结果为 ADKDKDDKA。 123&45&678的结果为, '12345678”
? 设下图 E列数据由 C,D列数据组合而成 。
? 在 E2中输入公式 =C2&D2,然后向下复制 ！
2.5 组合多个单元格数据
2,用 &和文本函数的结合
? &常和文本函数 left,right,mid等合用, 这几个函
数都很简单, 其调用语法如下,
? left(text,n)
? right(text,n)
? mid(text n1,n2)
? 其中,
? left截取 text文本左边的 n个字符；比如 left("1234",2)= "12 "
? right截取 text文本右边的 n个字符；比如 right("1234",2)= "34 "
? mid从 text的第 n1字符开始, 取 n2个字符；
比如 MID("1234",2,2)= "23"
2.5 组合多个单元格数据
3,用 &和文本函数的结合案例
? 某电话公司的缴费帐号由身份证号的后 15位 （ 全长 18位 ）,
后接电话号码构成 。 如下图所示 。 用 &和文本函数可以很快
建立缴费帐号 。
? 在 I3输入公式, =Right(G3,17)&H3”, 然后向下填充复制
该公式到 I列的其余单元格
2.6 采用下拉列表进行数据选择
1,下拉列表的适用范围
? 项目个数少而规范的数据, 比如 职称, 工种, 单位
及 产品类型 等, 这类数据适宜采用 Excel的, 数据
有效性, 检验方式, 以下拉列表的方式输入 。
? 例如,
某校教师档案如右图所
示，其中的职称列数据
只能从, 讲师、教授、
副教授、助教, 中选择。
这类数据用下拉列表输
入，方便而准确。
2.6 采用下拉列表进行数据选择
2,下拉列表建立方法
（ 1） 选中要建立下拉列
表的单元格区域 。
（ 2） 选择, 数据, |“有
效性, 菜单项 。
（ 3） 选择, 设置, 标签,
然后从, 允许, 下拉列
表中选择, 序列, 选项 。
（ 4） 在, 来源, 文本框
中输入职称名字, 助教,
讲师,副教授,教授,
2.7 利用公式与函数进行查找输入
? 1,查找输入的意义
? 如果表格中的不同数据列之间存在一定的依赖关系,
那么用 IF公式进行数据的转换输入是一种效率较高
的输入方法 。
? 如果某些数据已经在其它数据表中建立好了, 查询
输入可以提高效率, 且能减少输入错误 。
2.7 利用公式与函数进行查找输入
? 2,IF函数的应用
? IF函数又称为条件函数, 它能够对给出的条件进行判断,
并根据判断结果的正误执行不同的运算 。
? IF函数的调用形式如下,
IF(条件式,条件正确时函数的取值,条件错误时函数的取值 )
? 比如,
? IF(3>2,“right”,“error”)”的结果是, right”,
? 而 IF(3<2,"right","error")的结果应该是, error”
? 因为 3>2是正确的，函数的结果就是, 条件正确时的取值, 。
与之相对应，而 3<2是错误的，所以 …… 。
2.7 利用公式与函数进行查找输入
? IF函数可以嵌套使用, 即在 IF函数中还可以使
用 IF函数, 最多可以嵌套 7层 。 比如,
? IF(A2>89,“A”,IF(A2>79,“B”)),
? 如果 A2等于 93,则该函数的结果是, A”；
? 如果 A2等于 80,则函数的结果是内层 IF(A2>79,“B”)的
结果, 显然 80>79,所以结果为, B”；
? 如果 A2等于 65呢？ 函数的结果就是 IF(A2>79,"B")不成
立时的取值, 这时该函数的结果就是一个空值, 因为
IF(A2>79,"B")没有给出 A2>79不成立时的取值, 在这种
情况下, Excel将返回一个空值 （ 即什么结果都没有 ！ ）
给该函数 。
2.7 利用公式与函数进行查找输入
? 3,IF函数查找转换案例
? 某学校年度奖金如下图所示, 奖金根据职称确定 。 教授：
2000元, 副教授,1500,讲师,1000,助教：是 500元 。
? 在 F3 输入公式,=IF(E3=" 教授 ",2000,IF(E3=" 副教授
",1500,IF(E3="讲师 ",1000,IF(E3="助教 ",500))))
2.7 利用公式与函数进行查找输入
? 公式含义分析
2.7 利用公式与函数进行查找输入
4,Vlookup函数查找
? Vlookup函数能够从其它工作表 （ 或本工作表 ） 将某数据列
中的数据查找出需要的数据 。 VLOOKUP函数的用法如下,
? VLOOKUP（ x,table,n,r）
? 其中, x是要查找的值, table是一个单元格区域 （ 即由
工作表的 1列或多列组成 ） 。 Excel将在 table的第一列查
找有无 x这样的值 （ 只在 table的第 1列查找有无 x）
? n是 table中待返回的匹配值的列序号 。。
? r可以取 0或 1,当 r为 0时, 该函数将进行精确匹配查找；
? 当 r=1时, table的第一列应按升序排列, 否则找到的结
果可能不正确, r=1表示 Excel实行的是近似值查找 。
2.7 利用公式与函数进行查找输入
5,Vlookup函数查找案例
（ 1） 问题描述,某移动公司更换了新系统, 使原有
的部分电话号码不能使用, 同时又新增加了许多电
话号码 。 系统变化后, 大部分用户仍然使用原来的
用户档案 （ 电话号码和缴费合同号都没有改变 ）,
而那些不能使用的电话号码和新增加的电话号码则
需要重新建立用户档案
2.7 利用公式与函数进行查找输入
? （ 2） 帐号查找过程
? 在需要提取帐号的新
号 码 工 作 表 的 B3输
入查找公式, 并向下
复制此公式 。
=VLOOKUP(A3,旧帐号 !A$2:$B$6884,2,0)
旧帐号是原来的
工作表
B列是从旧
帐号的 B列
找到的数据
2.8 快速输入数据填充序列
1,数据序列的含义
? 序列就是一组数据, 这组数据有先后顺序, 内容固
定, 在应用中常常以一组数据的整体形式出现 。
2,Excel内置序列
? 对于经常使用的一些数据序列, 比如月份, 星期,
季度等, Excel已经将它们内置在系统中, 在输入
这些数据时, 只需要输入第一个数据, 其余的可以
采用填充复制的方法由 Excel自动产生 。
2.8 快速输入数据填充序列
3,自定义序列
? 用户可以将经常使用的数据序列添加到 Excel系统
中, 该序列就会拥有与 Excel内置序列相同功能 。
比如, 输入该序列的第一项内容后, 其后各项都可
以通过填充复制产生 。
? 案例
某大学中有, 计算机学院, 通信学院, 经济管理学
院, 法律学院, 中药学院, 电子技术学完, 等多所
学院, 学校办公室经常要用到这些名字 。 将这组
数据定义为自义序列 。
2.8 快速输入数据填充序列
? 建立方法
（ 1） 选择, 工具, |“选项, |“自定义序列, 。
（ 2） 在, 选项, 对话框的, 输入序列, 中依次输入
序列内容 。
（ 3）建立输入
序列后，输入第
一项，即, 计算
机学院, 后，通
过填充复制就能
产生其它各项内
容。
2.9 限定输入数据的长度，显示提示和出错信息
? 对输入到单元格中的数据进行一定程度的限制, 比
如限制小数位数, 日期和时间的范围, 字符的个数
等 。
? 当输入的数据不符合限定规则时, Excel就显示一
些警告信息, 并且不接受输入的数据 。
? 如果工作表的数据是由其他人输入的, 则有必要在
别人输入数据时给出一些简要的提示信息, 或对数
据输入的规则作些说明
2.9 限定输入数据的长度，显示提示和出错信息
? 案例
? 是某俱乐部的会员档案表, 现以此为例说明限定单
元格输入位数, 显示提示和告警信息的方法 。
2.10用 RAND和 INT函数产生实验数据
1,Rand 函数
? 又称为随机函数, 它产生一个介于 0~1之间小数 。
? 对 Rand函数的结果进行放大, 能产生任意范围内
的数据 。 如,
? RAND()*100,可以产生 0~100之间的数据
? RAND()*50+50,可以产生 50~100之间的数据 。
2,Int函数
? 又称取整函数, 将数字向下舍入到最接近的整数 。
? 比如,INT(8.9) 的结果是 8
? INT(-8.9) 的结果 是 -9
2.10用 RAND和 INT函数产生实验数据
? 案例
? 建立如图所示的学生成
绩表, 用 INT 函数和
RAND函数产生 C2:J11区
域的成绩, 设 成 绩 在
20~100之间 。
? (1)选中 C2:G11区域
? (2)输入公式：
=20+int(rand()*80
? (3)按 Ctrl+Enter
说明：用 rand产生的数据会随时发生变化，可
将它贴粘为数据，它就不会发生变化了。其方
法是,
（ 1）选中 C3:G11区域，然后选择, 复制,
（ 2）选择, 编辑, |,选择性贴粘,
（ 3）从弹出的对话框中选择, 数值, 。
The End