《计算机基础和office高级应用》-第8章

8.1案例1—员工档案表

(2)“&”这个符号可以用来连接两个单元格内容(两个文本、两个符号、两个数字、一个文本和一个数字、一个数字一个文本、两个需要相连接的内容)。如果用函数表示则是CONCATENATE，比如:A1和B1的单元格内容相连，输入“=A1&B1”，用函数公式则是“=CONCATENATE(A1,B1)"。本文中只需用“&”这个符号就可以。

2.分步提取出生日期(1)制作初始档案表。如图8-1所示员工档案表中已经有员工的身份证信息，我们只需要根据每个员工的身份证信息提取出出生日期信息。

(2)提取出生日期信息。为便于理解，在出生日期列前面插人三列，分别为年、月、日。身份证号中的出生日期是从第7位开始4位为年，第11位开始两位为月，第13位开始两位为日。上一页下一页返回8.1案例1—员工档案表如图8-2所示，在E3单元格中输入=MID(D3,7,4)&“年”，得到出生年为1972年。MID从第7位开始提取长度为4位的字符串。(3)完成出生日期提取与连接。现在用&把三个公式连接成一个公式就可以了。如图8-3所示，在H3中输入=MID(D3,7,4)&“年”&MID(D3,11，2)&“月”&MID(D3,13,2)&“日”，可以得到该员工的出生日期。这个公式看起来比较长，通过上一步骤的分解，可以看到实际上是MID和&的重复使用。删除前面分解的三列，使用填充柄工具完成其他员工的出生日期的提取。8.1.2性别信息提取1.函数准备上一页下一页返回8.1案例1—员工档案表

(1)MID提取身份证信息的第17位数字。身份证号中第17位为性别验证码，奇数为男，偶数为女。

(2)取余函数MOD。MOD(number,divisor)结果返回余数，两个参数分别为被除数和除数。

(3)IF函数判断余数，结果显示性别。2.分步识别与提取性别信息(1)MID提取第17位数字。如图8-4所示在性别列前面插入三列(这里是为了便于理解，掌握后删除插入列)，选中C3单元格，输入=MID(G3,17,1)，结果为1.(2)MOD取余。上一页下一页返回8.1案例1—员工档案表在D3单元格中输入=MOD(MID(G3,17,1),2}，这是嵌套函数，如图8-5所示，公式中的MID(G3,17,1)作为被除数，除数为2，结果返回1。(3)IF判断奇偶。(4)完成性别的判断与提取。如图8-6所示，删除性别列的前面三列，在C3中输入=IF(MOD(MID(G3，17，1)，2)=1，“男”、“女”)完成性别的提取，并填充整表。8.1.3年龄提取1.函数准备(1)NOW()。返回日期时间格式的当前日期和时间，读取系统时间。上一页下一页返回8.1案例1—员工档案表(2)YEAR(serial_number)。返回日期的年份值。类似的函数有MONTH,DAY。(3)DATE(year,month,day)。返回日期中的数字。2.提取年龄信息(1)年龄的计算。年龄计算为当前年减去出生年加上1.(2)当前时间计算。公式为=YEAR(NOW())，结果返回系统设置时间的年。(3)出生年提取。公式为=MID(D3,7,4)。(4)完成计算。如图8-7所示，在I3单元格中输入=YEAR(NOW())-MID(D3,7,4)+1，结果为43。上一页下一页返回8.1案例1—员工档案表8.1.4生日提示1.函数准备(1)VALUE(text)。将代表数值的字符串转换成数值。身份证号在Excel中输入一般是采用文本的格式，所以MID(D3,11,2)提取出来的是文本字符串而非数值在进行数值之间的判断时需要转换。(2)MONTH(NOW())。提取当前日期时间中的月。2.当月生日提示计算

(1)身份证号中的出生月的提取与转换。使用公式=VALUE(MID（D3,11,2))完成生日的月的提取。(2)IF判断是否为本月生日。上一页下一页返回8.1案例1—员工档案表如图8-8所示选中单元格J3，输入公式=IF(VALUE(MID(D3,11,2))=MONTH(NOW()),“本月生日”当前系统时间为7月。条件判断出生月与系统当前日期中的月是否相同，相同则显示“本月生日”提示，否则为空。

(3)生日快乐提醒。生日快乐提醒比本月生日提醒稍复杂一些，主要是条件判断需要用到AND设置多条件判断，即出生月与当前月、出生日与当前日判断，完全相同为生日。如图8-9所示在K3中输入公式判断当天是否为生日并提示出来。上一页返回8.2案例2—图书销售信息表8.2.1LOOKUP查找店铺信息

1.函数准备:LOOKUP函数LOOKUP函数的功能是返回向量(单行区域或单列区域)或数组中的数值。函数LOOKUP有两种语法形式:向量和数组。函数LOOKUP的向量形式是在单行区域或单列区域(向量)中查找数值，然后返回第二个单行区域或单列区域中相同位置的数值;函数LOOKUP的数组形式在数组的第一行或第一列查找指定的数值，然后返回数组的最后一行或最后一列中相同位置的数值。2.图书销售店铺信息查找与引用

(1)员工号中的店铺信息。下一页返回8.2案例2—图书销售信息表如图8-10所示，图书销售信息表中第一列为销售员工号，员工号中的第三、四位对应店铺信息。(2)LOOKUP查找店铺信息。选中C3单元格，输入公式=LOOKUP(MID(A3,3,2){“01”,“02”,“03”,“04”，“05“}，{“前进店”，“亭林店”，“城东店”，“马鞍山”，“城南店”})，填充公式，结果如图8-11所示店铺名称完成查找。8.2.2LOOKUP查找图书名称和单价信息1.函数准备:VLOOKUP函数VLOOKUP函数在财务管理与分析中是一个经常用到的函数，因此，熟悉它会带来很大便利。上一页下一页返回8.2案例2—图书销售信息表VLOOKUP函数的功能是在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。公式为:=VLOOKUP(lookup_value,table_array,col_in-dex_num,range_lookup)，公式中range_lookup一逻辑值，指明函数VLOOKUP返回时是精确匹配还是近似匹配，如果其为TRUE或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于lookup_value的最大数值;如果range_value为FALSE，函数VLOOKUP将返回精确匹配值。如果找不到，则返回错误值#N/A。2.图书名称和单价信息的查找与引用

(1)图书信息对照表制作。上一页下一页返回8.2案例2—图书销售信息表VLOOKUP查找和引用图书名称和单价。要实现图书名称和单价的查找和引用，我们首先要完成图书信息对照表的制作(一般是仓储部门完成这个表格)。如图8-12所示，新建一工作表:图书信息对照表。该表主要有三列内容:图书编号、图书名称、单价。同销售信息表中有关联的是图书编号，这样我们通过图书编号可以查找到图书名称和单价，这类操作一般多用于财务和库房管理。(2)查找与引用图书名称和单价。图书名称查找结果如图8-13所示。图书单价查找结果如图8-14所示。

(3)完成小计计算。即单价*数量。选中单元格H3，输入公式=F3*G3，回车，填充到表格的最后一行，图书销售信息表制作结果如图8-15所示。上一页返回图8-1登云公司员工档案返回图8-2MID函数提取出生日期返回图8-3连接MID函数提取出生日期返回图8-4MID提取性别验证码返回图8