4.2制作业务员信息表_Excel 2013基础入门与办公应用-QQ阅读中文科幻网

上QQ阅读APP看书，第一时间看更新

4.2制作业务员信息表

案例背景

“业务员信息表”即业务员个人资料信息表。每个企业都有各自员工的个人信息表，以便了解员工的情况。

最终效果及关键知识点

4.2.1 函数基础

Excel 2013预置了一些已经定义好的公式，被称为函数。这些函数可以单独使用，也可以在公式中使用。

一般情况下，函数是由函数名称和一个或多个参数组成。函数的种类很多，按照功能主要分为以下7种。

文本函数

文本函数主要是用来处理公式中的文本字符串的，例如UPPER、CONCATENATE函数。

日期和时间函数

日期和时间函数是专门用于处理日期和时间数据的函数，例如 YEAR、TODAY函数。

逻辑函数

逻辑函数主要用于在函数公式中对某些条件进行相应的逻辑判断，例如IF函数。

数学与三角函数

数学与三角函数主要用于数学与三角函数方面的计算，例如SUM函数。

查找与引用函数

查找与引用函数用于查找工作表中某些特定的值，例如VLOOKUP函数。

统计函数

统计函数是指用于对数据区域进行统计分析的函数，例如 AVERAGE、RANK 函数。

数据库函数

数据库函数主要用于分析数据清单中的数据是否符合特定的条件。

4.2.2 文本函数

常用的文本函数包括 UPPER、MID、CONCATENATE、TEXT、LEFT等。

UPPER函数

UPPER 函数的功能是将一个字符串中的所有小写字母转换为大写字母。

MID函数

函数功能：从文本字符串中指定的起始位置起返回指定长度的字符。

语法格式：MID(text,start_num, num_chars)

参数说明：text 为包含要提取的字符的文本字符串；start_num为文本中要提取的第一个字符的位置，文本中第一个字符的start_num 为 1，依次类推；num_chars 为指定希望MID从文本中返回字符的个数。

例如使用 MID 函数返回字符串中的前两个字符和后两个字符。其中 B2 引用的公式为“=MID（A1,1,2）”，C2 引用的公式为“=MID（A1,3,2）”。

CONCATENATE函数

函数功能：将多个文本字符串合并为一个文本字符串。

语法格式：CONCATENATE(text1, text2,…)

参数说明：text1，text2，…为要连接的1到255个文本项。

例如将“我”、“爱”、“音乐”、“！”合并为一个文本字符串。

TEXT函数

函数功能：根据指定的数字格式将数值转换为文本。

语法格式：TEXT（value，format_text）

参数说明：value可以是数值、计算结果为数值的公式，或对包含数值的单元格的引用；format_text为使用双引号括起来作为文本字符串的数字格式。

例如将数字“1234”转换为人民币形式。

LEFT函数

函数功能：从一个文本字符串的第一个字符开始返回指定个数的字符。

语法格式：LEFT（text，num_chars）

参数说明：text 为包含要提取的字符的文本字符串；num_chars 为指定要由 LEFT提取的字符的数量。

在使用LEFT函数时需要注意，num_chars必须大于或等于零，如果 num_chars大于文本长度，则 LEFT 返回全部文本；如果省略num_chars，则假设其值为1。

例如使用 LEFT 函数从“I am very happy!”中提取字符，其中单元格B1、B2、B3、B4引用的公式分别为“=LEFT(A1,3)”、“=LEFT(A1,6)”、“=LEFT(A1,9)”、“=LEFT (A1,13)”。

接下来利用提取文本函数编辑“业务员信息表”，并根据身份证号码计算员工性别和出生日期。具体操作步骤如下。

1. 转换大小写函数

1 打开本实例的原始文件，选中单元格B3，切换到【公式】选项卡，在【函数库】组中单击【插入函数】按钮。

2 弹出【插入函数】对话框，在【或选择类别】下拉列表框中选择【文本】选项，然后在【选择函数】列表框中选择【UPPER】函数。

3 设置完毕单击按钮，弹出【函数参数】对话框，在【Text】文本框中将参数引用设置为单元格A3。

4 设置完毕单击按钮，返回工作表中，此时“新编号”栏中的字母变成了大写。

5 将鼠标指针移至单元格B2的右下角，此时鼠标指针变为形状，双击鼠标左键，此时公式填充到选中的单元格区域中。

2. 提取性别

1 在单元格E3中输入函数公式“=IF(MOD(MID(D3,17,1),2)=0,"女","男")”，该公式表示“首先利用MID函数从身份证号码中提出第17位数字，然后利用MOD函数判断该数字能否被2整除，如果能被2整除，则返回性别‘女’，否则返回性别‘男’”。

2 利用快速填充功能向下填充公式。

3. 提取出生日期

1 在单元格 F3 中输入函数公式“=CONCATENATE(MID(D3,7,4),"-",MID (D3,11,2),"-",MID(D3,13,2))”，即利用MID 函数从身份证号码中分别提出年、月、日，然后利用CONCATENATE函数将年、月、日和短横线“-”连接起来。

2 利用快速填充功能向下填充公式。

4.2.3 日期与时间函数

常用的日期与时间函数包括 DATE、YEAR、MONTH、DAY、DAY360、TODAY、NOW、WEEKDAY、DATEDIF等。

1. 日期与时间函数基础

DATE函数

函数功能：返回代表特定日期的序列。如果单元格的格式为“常规”，结果将设为日期格式。

语法形式：DATE(year，month，day)

参数说明：year可以为1～4位的数字，默认情况下Microsoft Excel for Windows使用1900日期系统，即1900年1月1日是第一天，其序列编号为“1”；month 代表一年中从 1 月到 12 月各月的正整数或负整数；day代表一个月中从1日到31日各天的正整数或负整数。

例如使用DATE函数返回日期。

YEAR函数

函数功能：返回某日期对应的年份，返回值为1900～9999之间的整数。

语法格式：YEAR(serial_number)

参数说明：serial_number为一个日期值，其中包含要查找年份的日期，返回某个日期对应的年份。

MONTH函数

函数功能：返回以序列号表示的日期中的月份，月份是介于1～12之间的整数。

语法格式：MONTH(serial_number)

参数说明：serial_number表示一个日期值，其中也含要查找的月份。

DAY函数

函数功能：返回以序列号表示的某日期的天数，天数是介于1～31之间的整数。

语法格式：DAY(serial_number)

参数说明：serial_number为要查找的那一天的日期。

DAY360函数

函数功能：按照一年360天返回两个日期相差的天数。

语法格式：DAY360(start_date，end_date)

参数说明：start_date和end_date表示计算相差天数的起止日期。

TODAY函数

函数功能：返回当前日期的序列号。序列号是Microsoft Excel日期和时间计算使用的日期-时间代码。

语法格式：TODAY( )

参数说明：此函数没有参数。

NOW函数

函数功能：返回当前日期和时间所对应的序列号。

语法格式：NOW( )

参数说明：该函数没有参数。

WEEKDAY函数

WEEKDAY函数的功能是返回某日期的星期数。在默认情况下，它的值为 1（星期天）～7（星期六）之间的一个整数。

语法格式：WEEKDAY(serial_number, return_type)

参数说明：serial_number为要返回日期数的日期；return_type为确定返回值类型。如果return_type为数字1或省略，则1～7表示星期天到星期六；如果return_type为数字2，则 1～7 表示星期一到星期天；如果return_type 为数字 3，则 0～6 代表星期一到星期天。

DATEDIF函数

DATEDIF 函数的功能是返回两个日期之间的年、月、日间隔数。

语法格式：DATEDIF(start_date,end_date,unit)。

参数说明：start_date代表一个时间段内的第一个日期或起始日期；end_date 代表时间段内的最后一个日期或结束日期；unit 表示所需信息的返回类型。其中，“Y”表示时间段中的整年数；“M”表示时间段中的整月数；“D”表示时间段中的天数；“MD”表示start_date与end_date日期中天数的差，忽略日期中的月和年；“YM”表示 start_date 与end_date 日期中月数的差，忽略日期中的日和年；“YD”表示 start_date 与 end_date 日期中天数的差，忽略日期中的年。