Excel的数据类型操作.

本章学习目标1、了解Excel的数据类型2、掌握不同类型数据的输入方法3、掌握复制公式输入数据的方法4、掌握大批量相同数据的输入方法5、掌握通过自定义格式减少数据输入的方法2.1Excel的数据类型1、Excel的常见数据类型数字型、日期型、文本型、逻辑型数据数字型表现形式多样：货币、小数、百分数、科学计数法、各种编号、邮政编码、电话号码等多种形式2.1Excel的数据类型2、Excel数值型数据的取值范围功能最大限制数字精度15位单元格中可键入的最大数值9.99999999999999E307最大正数1.79769313486231E308最小负数-2.2250738585072E-308最小正数2.229E-308最大负数-2.2250738585073E-3082.2基本数据的输入1、数据输入的一般过程（1）选定要输入数据的单元格。（2）从键盘上输入数据。（3）按Enter键后(注意：按ESC键的作用）2、输入数值（1）正数的输入：+234，13,333,333，12E3（2）负数的输入：-234，(234)，-12e2（3）分数的输入:22/3,03/4（4）货币数据的输入:￥123，$212.2基本数据的输入3、输入文本字符文本应逐字输入数字文本以‘开头输入，或用=“数字”方式输入。输入文本32，可输入：=“32”或输入：'32文本形式的公式，要通过“插入”|“对象”菜单命令如22221),,(xexf2.2基本数据的输入4、输入日期以yy-mm-dd形式，或mm-dd形式输入通过格式化得到其它形式的日期，这样可减少输入格式方法是选择“格式”|“单元格”|“数字”|“日期”2.2基本数据的输入5、输入公式公式是对工作表中的数值进行计算的等式。公式要以等号（＝）开始。例如在A3输入：=5+2*3，结果等于2乘3再加5。A3中将显示112.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输入1003020102（2）选中A2：A3（3）向下拖动A3单元格的填充柄。2.4编号的输入2、填充产生连续编号填充序列方式适用于输入较多的连续编号数据。比如要输入电话号码：663321240~663328000，如右图所示，方法如下。在第一个单元格中输入起始号码选择“编辑”|“填充”|“序列”菜单命令。在Excel弹出的“序列”对话框中指定“列”在步长中输入1在终止值中输入最后一个号码。2.4编号的输入3、利用自定义格式产生特殊编号人们常会遇到各种具有一定规则的特殊格式的编号。如右图的保险号。1、选中要输入保险号的区域2、选择“格式”|“单元格”菜单命令。3、选中“自定义”4、输入自定格式：p00-000-00002.4编号的输入用自定义格式输入大数字编号当在单元格中输入一个位数较多的数值时（比如超过15位的整数），Excel会自动将输入的数据显示为科学记数法，这可能并不符合我们的需要。产生这种问题的原因是数值精度问题，Excel的默认精度是15位，如果一个数值的长度超过15位，则15位之后的数字都被视为0。这种位数较多的大数字编号，可以用自定义格式进行输入2.4编号的输入用自定义格式输入大数字编号建立右图所示的电话用户档案，其中身份证号为18位，且前14位全部相同。若直接输入，不仅数据多，且要出错，比如在G4输入：510214196012301000,Excel会显示为5.1021E17解决方法是定义G列保存身份证号的单元格的自定义格式为：510214196012300000设置自定义格式后，只需要输入身份证的最后4位编号2.4编号的输入选中D2单元格，选择“格式”|“单元格”菜单项“类型”文本框中输入：5102141960123000002.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(textn1,n2)其中：left截取text文本左边的n个字符；比如left(1234,2)=12right截取text文本右边的n个字符；比如right(1234,2)=34mid从text的第n1字符开始，取n2个字符；比如MID(1234,2,2)=232.5组合多个单元格数据3、用&和文本函数的结合案例某电话公司的缴费帐号由身份证号的后15位（全长18位），后接电话号码构成。如下图所示。用&和文本函数可以很快建立缴费帐号。在I3输入公式“=Right(G3,15)&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(32,“right”,“error”)”的结果是“right”，而IF(32,right,error)的结果应该是“error”因为32是正确的，函数的结果就是“条件正确时的取值”。与之相对应，而32是错误的，所以……。2.7利用公式与函数进行查找输入IF函数可以嵌套使用，即在IF函数中还可以使用IF函数，最多可以嵌套7层。比如：IF(A289,“A”,IF(A279,“B”))，如果A2等于93，则该函数的结果是“A”；如果A2等于80，则函数的结果是内层IF(A279,“B”)的结果，显然8079，所以结果为“B”；如果A2等于65呢？函数的结果就是IF(A279,B)不成立时的取值，这时该函数的结果就是一个空值，因为IF(A279,B)没有给出A279不成立时的取值，在这种情况下，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实行的是近似值查找。函数的意义：在table的第一列查找值为x的数据，如果找到，就返回该table中第n列且值与x同行的单元格中的数据。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之间小数。