chapter-3公式名字与函数.ppt

3.9日期及时间函数 案例 【例】某公司将每个员工的加班时间记录在Excel的工作表中，便于计算加班工资。加班工资按小时计算，若加班时间不足1小时，但超过半小时由按1计时计算，不足半小时则不算加班时间。平时的加班工资每小时10元，节假时则加倍为20元每小时 。 假设员工的加班时间如图的A1:C11所示，现要计算加班时长（D列），判断加班时间是星期几和节假日（E、F列），计算工龄（J列）、总加班时间（L列）、节假时加班时间（L列）、工作日加班（M列，本该休息，但加班了），以及加班工资（N列）。 3.9日期及时间函数 用日期、时间函数计算下表D、E、F、J、K、L、M、N列相应数据。 3.10 字符函数 1、概述 Excel提供了接近30个文本、字符方面的函数，用这些函数对工作表中的文本数据进行查找、替换、取子串、数字与文本的转换，以及数据的格式化操作等都有十分重要的作用 。 2、常用文本类函数 RIGHT(text,n) 该函数从text文本的右边取出n个字符。 LEFT(text,n) 该函数从text文本的左边取出n个字符 3.10 字符函数 MID(text,n,m) 该函数从text文本的第n个字符起，取出m个字符。 REPLACE(text1,n,m,text2) 该函数用text2替换text1文本的第n个位置开始的m个字符。 SEARCH(text1,text2,n) 该函数从text1的第n个字符位置开始查找text1中是否包含text2，若找就返回text2在text1中的起始位置。若没有找到则返回错误值“#value!” 3.10 字符函数 TEXT(value,format_text) 该函数指定的格式码format_text将数值型数据Value格式化为文本。其中value可为数字、结果为数字的计算公式、包含数字的单元格引用。 format_text可为任何数字格式（有关格式码请参考第2章的相关内容）。 VALUE(text) 将数字形式的文本转换成数字。 比如：VALUE(32)=32，VALUE(09)=9 3.10 字符函数 案例 【例】文本函数应用举例 某银行要建立下图3.所示的用户资料表。 其中的出生日期从身份证号码中提取； 如果是男性，其尊称就是“X先生”，如果是女性，其尊称就是“X女士”，X是用户的姓氏； 存款帐号是出生日期（去掉出生年的前两位数字）； 存款密码是身份证号码的最后6位数字； 最后要找出解放西路的用户姓名，以便给他们发新的银行卡。 3.10 字符函数 用文本类函数产生E2:I9区域的数据。 3.10 字符函数 （1）产生出生日期 在E2单元格输入公式，然后将它向下复制此公式就能够生成所有人的出生日期。 =MID(D2,7,4)年VALUE(MID(D2,11,2))月MID(D2,13,2)日“ （2）生成用户的尊称 在F2单元格输入下面的公式，并将此公式向下复制，生成所有人的尊称。 =LEFT(A2,1)IF(B2=女,女士,先生) （3）生成存款帐号 在G2单元格输入下面的公式，并将此公式向下复制，生成所有人的存款帐号。 =TEXT(E2,yymmdd) 3.10 字符函数 （4）生成存款密码 在H2中输入下面的公式，并向下复制，产生所有人的存款密码。 =RIGHT(D2,6) （5）找出解放西路的用户姓名 在I2单元格中输入下面的公式，并向下复制 =IF(SEARCH(解放西路,C2),A2) 3.11 错误信息函数 1、 Excel的常见错误信息 错 误 值 错 误 原 因 ##### 单元格所含的数字、日期或时间比单元格宽，或者单元格的日期、时间公式产生了一个负值，就会产生 ##### 错误。 #VALUE! 1. 在需要数字或逻辑值时输入了文本，Microsoft Excel 不能将文本转换为正确的数据类型。 #DIV/O! 1. 输入的公式中包含明显的除数为零（0），如 =5/0。2. #NAME? 1. 在公式中输入文本时没有使用双引号。Microsoft 将其解释为名称，但这些名字没有定义。2. 函数名的拼写错误。 #N/A 1. 内部函数或自定义工作表函数中缺少一个或多个参数。2. 在数组公式中，所用参数的行数或列数与包含数组公式的区域的行数或列数不一致。3. 在没有排序的数据表中使用了VLOOKUP，HLOOKUP或MATCH工作表函数查找数值 #REF! 删除了公式中所引用的单元或单元格区域 #NUM! 1. 由公式产生的数字太大或太小，2. 在需要数字参数的函数中使用了非数字参数 #NULL! 在公式的两个区域中加入了空格从而求交叉区域，但实际上这两个区域无重叠区域。 3.11 错误信息函数 2、 Excel错误