我的书签
添加书签
移除书签实例六 制作员工信息表
6.1案例简介
6.1.1 案例需求与展示
天空广告公司人事部为了方便员工管理、实现档案电子化，需要将2020年入职员工的基本信息录入电脑。人事部劳资科的秘书小王利用Excel2016的相关操作，很快完成了这项任务。效果如图6-1所示。
图6-1“员工信息表”效果图
6.1.2知识技能目标
本案例涉及的知识点主要有：Excel工作簿文件的新建与保存、自定义单元格格式、单元格数据录入与格式设置、设置数据验证、验证数据有效性、设置自定义序列等。
知识技能目标：
- 掌握Excel中单元格的自定义格式。
- 掌握单元格的格式设置。
- 掌握数据验证的设置。
- 掌握自定义数据序列。
6.2案例实现
6.2.1建立员工信息基本表格
由于员工信息表中包含字段较多，在向表格中录入数据之前，需要创建一个基本表格，包括表格的标题和表头。具体操作步骤如下：
1）启动Excel2016，创建一个空白的工作簿文件，将Sheet1工作表重命名为“员工基本信息录入表”，如图6-2所示。之后将工作簿保存为“员工信息表.xlsx”。
图6-2重命名工作表
2）选择单元格A1，并在其中输入文字“员工基本信息表”。在单元格区域A2：I2中依次输入“工号”、“姓名”、“性别”、“年龄”、“部门”、“学历”、“身份证号码”、“工资”、“联系方式”，合并单元格区域A1：I1，效果如图6-3所示。
图6-3员工信息表的标题与表头
6.2.2自定义员工工号格式
员工的工号对员工的管理起着一定的作用，天空广告公司员工的工号格式为“员工进公司日期+三位编号”作为员工的编号，如“2020001”表示2020年的入职的编号为1的员工。可以利用Excel中“设置单元格格式”的“自定义”实现员工编号的快速输入。操作步骤如下：
1）选择单元格区域A3：A12，单击“开始”选项卡“数字”功能组的对话框启动器按钮，打开“设置单元格格式”对话框。
2）选择“数字”选项卡、“分类”列表框中的“自定义”选项，在右侧“类型”下方的文本框中输入“2020000”，如图6-4所示。
图6-4设置“自定义”格式
3）单击“确定”按钮，返回工作表中，在单元格A3中输入“1”，按
图6-5“自定义编号”完成后效果图
4）在单元格区域B3：B12中输入如图6-6所示的员工姓名。
图6-6员工姓名
【自定义员工工号格式】视频教程：
6.2.3 制作性别、部门、学历下拉列表
Excel中的“数据验证”功能可以制作出下拉列表，供用户选择内容在单元格中显示，方便快速输入信息。操作步骤如下：
1）选中单元格区域C3：C12，切换到“数据”选项卡，单击“数据工具”组中的“数据验证”下拉按钮，从下拉列表中选择“数据验证”命令，如图6-7所示。打开“数据验证”对话框
图6-7“数据验证”命令
2）在“设置”选项卡中，单击“允许”下方的下拉按钮，从下拉列表中选择“序列”选项，在“来源”下方的文本框中，输入文本“男，女”，如图6-8所示。注意：文本中的逗号为英文状态下的符号。单击“确定”按钮，返回工作表。
图6-8“数据验证”对话框 图6-9选择输入“性别”
3）此时单元格C3右侧出现下三角按钮，单击此按钮，即可在下拉列表中选择“性别”选项，如图6-9所示。
4）使用同样的方法，选择单元格区域E3：E12，打开“数据验证”对话框，设置序列“来源”为“行政部，宣传部，企划部，财务部，市场部，人事部”，如图6-10所示。
图6-10设置“部门”列的“数据验证”
5）单击“确定”按钮，返回工作表，在随后的单元格中选择员工所对应的部门，效果如图6-11所示。
图6-11设置列表选择完成后效果图
6）使用同样的方法，为“学历”列设置“数据验证”序列来源为“大专，本科，硕士”，根据效果图6-1，设置员工的“学历”列内容。
6.2.4设置年龄数据验证
由于公司人员较多，利用“数据验证”功能可以限制用户输入的内容，帮助用户快速输入。如案例中的“年龄”列，对于2019年的入职员工来说，员工的年龄要求在18-35岁之间，并且输入的年龄必须为整数，此时就可以使用“数据验证”功能限制年龄的输入，避免出错。操作步骤如下：
1）选择单元格区域D3：D12，单击“数据”选项卡下的“数据验证”按钮，打开“数据验证”对话框。
2）在“设置”选项卡中，设置“允许”下拉列表的值为“整数”、“数据”下拉列表的值为“介于”、“最小值”为“18”、“最大值”为“35”，如图6-12所示。
图6-12设置输入值类型与范围 图6-13设置“输入信息”
3）切换到“输入信息”选项卡，在“输入信息”下方的文本框中输入相关信息，如图6-13所示。
4）切换到“出错警告”选项卡，从“样式”下拉列表中选择“警告”选项，在“错误信息”下方的文本框中输入信息的内容“输入的年龄有误，年龄必须在18-35之间！”，如图6-14所示。
图6-14设置“出错警告”
5）单击“确定”按钮，返回工作表，可看到如图6-15所示的提示信息。
图6-15“行高”对话框
6）如果在单元格中输入了小于18或大于40的数据，将弹出如图6-16所示的提示框，单击“否”按钮，可重新输入数据。“年龄”列数据输入完成后的效果如图6-17所示。
图6-16“警告”提示框
图6-17“年龄”列输入完成后效果图
【设置年龄数据验证】视频教程：
6.2.5输入身份证号与联系方式
身份证号码是由数字组成的文本型数据，没有数值的意义，所以在输入数据前，要设置单元格的格式，操作步骤如下：
1）使用
图6-18“设置单元格格式”对话框
2）单击“确定”按钮，完成所选区域的单元格格式设置。
现在身份证号码统一为18位，对于案例中的“身份证号码”列，就可以使用“数据验证”功能校验已输入的身份证号码位数是否正确。操作步骤如下：
1）选择单元格区域G3：G12，打开“数据验证”对话框，设置“允许”为“文本长度”、“数据”为“等于”、“长度”为18，如图6-19所示，单击“确定”按钮，完成设置。
图6-19限定文本长度
2）根据效果图6-20所示，输入员工身份证信息与联系方式。
图6-20身份证号码与联系方式输入完成后效果图
6.2.6输入员工工资
员工的工资为货币型数据，在输入之前需要先设置单元格格式。操作步骤如下：
1）选择单元格区域H3：H12，打开“设置单元格格式”对话框，在“数字”选项卡的“分类”列表框中选择“货币”选项，保持其他默认值不变，如图6-21所示。单击“确定”按钮，完成所选区域的单元格格式设置。
图6-21设置货币型数据类型
2）输入每位员工的工资，效果如图6-22所示。
图6-22“工资”列数据输入完成后效果图
注意：“工资”列输入数据后，由于“身份证号码”列较窄，部分数据无法显示，此时可以双击G列与H列间的边框线，自动调整列宽。使用同样的方法调整I列的列宽。
3）保存工作簿文件，至此员工信息表制作完成。
6.3实例小结
本案例通过制作员工信息表讲解了Excel2016中新建表格时的单元格格式设置、自定义单元格格式、设置数据验证、验证数据有效性、设置自定义序列等内容。在实际操作中大家还需要注意以下问题：
（1）单元格中可以存放各种类型的数据，Excel2016中常见的数据类型有以下几种：
- 常规格式：是不包含特定格式的数据格式，是Excel中默认的数据格式。
- 数值格式：主要用于设置小数点位数，还可以使用千位分隔符。默认对齐方式为右对齐。
- 货币格式：主要用于设置货币的形式，包括货币类型和小数位数。
- 会计专用格式：主要用于设置货币的形式，包括货币类型和小数位数。与货币格式的区别是，货币格式用于表示一般货币数据，会计专用格式可以对一列数值进行小数点对齐。
- 日期和时间格式：用于设置日期和时间的格式，可以用其他的日期和时间格式来显示数字。
- 百分比格式：将单元格中的数字转换为百分比格式，会自动在转换后的数字后加“%”。
- 分数格式：使用此格式将以实际分数的形式显示或键入数字。如在没有设置分数格式的单元格中输入“3/4”，单元格中将显示为“3月4日”的日期格式。要将它显示为分数，可以先应用分数格式，再输入相应的数值。
- 文本格式：文本格式包含字母、数字和符号等，在文本格式的单元格中，数字作为文本处理，单元格中显示的内容与输入的内容完全一致。
- 自定义格式：当基本格式不能满足用户要求时，用户可以设置自定义格式。如案例中的员工编号，设置了自定义格式后，既可以简化输入的过程，又能保证位数的一致。
（2）Excel的“数据验证”功能中还提供了“圈释无效数据”功能，可以帮助用户查看错误数据。以案例中的“身份证号码”列为例，操作方法如下：
1）选择设置了数据验证的单元格区域G3：G12，切换到“数据”选项卡，单击“数据验证”右侧的箭头按钮，从下拉列表中选择“圈释无效数据”命令，如图6-23所示。
图6-23“圈释无效数据”命令
2）当身份证位数不正确时，不正确的数据被圈了出来，如图6-24所示。用户将错误数据修改正确即可。
图6-24圈释错误数据效果图
6.4经验技巧
6.4.1在Excel中输入千分号（‰）
千分号（‰）是在表示银行的存、贷款利率或财务报表的各种财务指标中经常用到的符号，在单元格的格式设置中并没有这个符号，我们可以通过插入特殊符号实现，操作步骤如下：
1）将光标定位到需要插入千分号（‰）的位置。
2）切换到“插入”选项卡，单击“符号”功能组中的“符号”按钮，打开“符号”对话框，在“字体”下拉列表框中选择“普通文本”选项、在“子集”下拉列表框中选择“广义标点”选项，如图6-25所示。在显示的列表框中找到“‰”，单击“确定”按钮，即可完成千分号的插入。在此需要注意的是：插入的千分号只用于显示，不可用于计算。
图6-25“符号”对话框
6.4.2快速输入性别
在输入员工信息时，对于“性别”列，如果用“0”或“1”来代替汉字“男”、“女”，可使输入的速度大大加快，利用格式代码中使用条件判断，可实现根据单元格的内容显示不同的性别，以案例中的“性别”列为例，可进行如下的操作：
1）选择单元格区域C3：C12，打开“设置单元格格式”对话框，选择“数字”选项卡，在“分类”列表框中选择“自定义”选项，在“类型”下方的文本框中输入如图6-26所示的格式代码。
图6-26自定义格式代码
2）单击“确定”按钮，返回工作表，在所选单元格区域中输入“0”或“1”，即可实现性别的快速输入。代码中的符号均为英文状态下的符号。
在Excel中，对单元格设置格式代码需要注意以下几点：
1）自定义格式中最多只有3个数字字段，且只能在前两个数字字段中包括2个条件测试，满足某个测试条件的数字使用相应段中指定的格式，其余数字使用第3段格式。
2）条件要放到方括号中，必须进行简单比较。
3）创建条件格式可以使用6种逻辑符号来设计一个条件格式，分别是大于（>）、大于等于（>=）、小于（<）、小于等于（<=）、等于（=）、不等于（<>）。
4）代码 [=1]“男”;[=0]“女”解析：表示若单元格的值为1，则显示“男”，若单元格的值为0，则显示“女”。
6.4.3查找自定义格式单元格中的内容
自定义格式只是改变了数据的显示外观，并不改变数据的值。在查找自定义格式单元格中的内容时，以员工信息表为例，可进行如下的操作：
1）选择“开始”选项卡，单击“编辑”功能组中的“查找和选择”按钮，从下拉列表中选择“查找”命令，如图6-27所示。打开“查找和替换”对话框。
图6-27“查找”命令 图6-28“查找和替换”对话框
2）在“查找内容”后的文本框中输入“9”，单击“选项”按钮。在“查找范围”下拉列表中选择“公式”选项，选中“单元格匹配”复选框，如图6-28所示。
3）单击“查找全部”按钮，即可查找到员工工号为“2020009”的单元为A11单元格。
6.5拓展练习
永明电器公司为了统计2020年的电器销售情况，需要制作一个销售业绩表（效果如图6-29所示），具体要求如下：
1）根据效果图，新建工作簿文件“销售业绩表.xlsx”，将Sheet1工作表重命名为“销售业绩统计”，并向工作表中添加表格的标题和表头。
2）根据效果图，自动填充“序号”列，向“姓名”、“品名”列添加文本内容。
3）设置“工号”列为文本型数据，利用自定义设置工号格式。
4）设置添加“金额”列数据，并根据效果图设置数据类型，保留两位小数，设置千分位。
5）向“日期”列添加数据，并设置日期格式。
6）设置“销售方式”列数据为序列选择方式，序列内容为代理和直销两种。
图6-29销售业绩表效果图
