C19-【计算机二级】Excel操作题(19)工资奖金的计算以及工资条的制作

每年年终，太平洋公司都会给在职员工发放年终奖金，公司会计小任负责计算工资奖金的个人所得税并为每位员工制作工资条。按照下列要求完成工资奖金的计算以及工资条的制作:

1. 将“Excel素材.xlsx”文件另存为“Excel.xlsx”(“.xlsx”为扩展名），后续操作均基于此文件，否则不得分。

2.在最左侧插入一个空白工作表，重命名为“员工基础档案”，并将该工作表标签颜色设为标准红色。

• 考点1：创建新工作表

• 考点2：修改工作表标签颜色

3.将以分隔符分隔的文本文件“员工档案.csv”自A1单元格开始导入到工作表“员工基础档案”中。将第1列数据从左到右依次分成“工号”和“姓名”两列显示;将工资列的数字格式设为不带货币符号的会计专用、适当调整行高列宽;最后创建一个名为“档案”、包含数据区域A1:N102、包含标题的表，同时删除外部链接。

①将以分隔符分隔的文本文件“员工档案.csv”自A1单元格开始导入到工作表“员工基础档案”中。

数据→从文本/csv→找到考生文件夹下的员工档案→导入

由于身份证号一栏的数字较多，因此必须替换成文本格式，否则无法正常显示

在身份证号单元格处右键，更改类型→文本

替换当前更改

点击左上角的“关闭并上载至”

选择加载到“现有工作表”的A1单元格

在表设计中清除表格样式，取消勾选筛选按钮

②将第1列数据从左到右依次分成“工号”和“姓名”两列显示

• 考点4：数据分列

Ⅰ、在A列右侧插入一列空白列

Ⅱ、选中A列，点击数据选项卡下的分列按钮

Ⅲ、由于没有分隔符号，因此选用固定宽度分列

Ⅳ、在工号和姓名之间加入分割线

按下一步直至分隔完成

③将工资列的数字格式设为不带货币符号的会计专用、适当调整行高列宽

• 考点5：调整单元格格式

全选表格，当鼠标放置在两列/两行中间的灰色部分，拖动即可修改行高/列宽

④最后创建一个名为“档案”、包含数据区域A1:N102、包含标题的表，同时删除外部链接

• 考点6：创建表

选中A1：N102单元格，套用一种表格格式

将表的名称修改为“档案”

4.在工作表“员工基础档案”中，利用公式及函数依次输入每个学生的性别“男”或“女”，出生日期“××××年××月××日”，每位员工截止2015年9月30日的年龄、工龄工资、基本月工资。其中:

①身份证号的倒数第2位用于判断性别，奇数为男性，偶数为女性；

本题的思路是，先用MID函数提取身份证号的倒数第二位，再用MOD函数判断其是奇数还是偶数，最后用IF函数判断其为男性或女性

• 考点7：MID函数、MOD函数和IF函数嵌套

用mid函数提取倒数第二位数字

方法一：

用mod函数判断该数字是奇数还是偶数，如果显示的是1则为奇数，显示的是0则为偶数

如果是奇数，显示为“男”，如果是偶数，显示为“女”

方法二：

用ISODD函数判断该数字是奇数还是偶数，如果是TRUE则为奇数，如果是FALSE则为偶数

再用IF函数来判断男女，如果是TURE为男生，如果是FALSE则为女生

②身份证号的第7~14位代表出生年月日；

• 考点8：MID函数

• 考点9：TEXT函数

③年龄需要按周岁计算，满1年才计1岁，每月按30天、一年按360天计算；

【每位员工截止2015年9月30日的年龄】

• 考点10：DATEDIF函数(开始日期,“截止日期”, “显示方式”)

第三个参数代表以“年”的形式显示。

④工龄工资的计算方法、本公司工龄达到或超过30年的每满一年每月增加50元、不足10年的每满一年每月增加20元、工龄不满1年的没有工龄工资，其他为每满一年每月增加30元；

本题的思路是，第一年没有工龄工资，从第二年开始到第九年，每年的工龄工资=年*20，从第10年到第29年，每年的工龄工资=年*30，从第30年往后，每年的工龄工资=年*50

【虽然我对这个计算方法也觉得很奇怪，但是参考答案就是这么写的】

Ⅰ、列出工龄与工龄工资对应的表格，尽量多写几年的，以免对应不上

Ⅱ、用VLOOKUP函数查找工龄对应的工龄工资

这里也可以用IF函数，但是我觉得还是列表比较快

⑤基本月工资=签约月工资+月工龄工资。

• 考点11：简单计算

5.参照工作表“员工基础档案”中的信息，在工作表“年终奖金”中输入与工号对应的员工姓名、部门、月基本工资;按照年基本工资总额的15%计算每个员工的年终应发奖金。

• 考点12：VLOOKUP函数

①查找姓名

②查找部门

③查找月基本工资

④计算每个员工的年终应发奖金

月基本工资填充完成后，全年应发奖金、月应税所得额、实发奖金就自动计算完毕了

6.在工作表“年终奖金”中，根据工作表“个人所得税税率”中的对应关系计算每个员工年终奖金应交的个人所得税、实发奖金，并填入G列和H例。年终奖金目前的计税方法是：

①年终奖金的月应税所得额=全部年终奖金÷12

②根据步骤①计算得出的月应税所得额在个人所得税税率表中找到对应的税率

③年终奖金应交个税=全部年终奖金×月应税所得额的对应税率-对应速算扣除数

• 考点13：IF函数嵌套

根绝个人所得税税率表中的条件，月应税所得额不超过1500的情况，应交个税=全年应发奖金*3%-0；月应税所得额在大于1500但不超过4500的情况，应交个税=全年应发奖金*10%-105……以此类推

由于条件比较多，因此采用IF函数嵌套

④实发奖金=应发奖金-应交个税

7.根据工作表“年终奖金”中的数据，在“12月工资表”中依次输入每个员工的“应发年终奖金”、“奖金个税”，并计算员工的“实发工资奖金”总额。(实发工资奖金=应发工资奖金合计-扣除社保-工资个税-奖金个税)

12月工资表中的实发年终奖金=应发工资奖金合计-扣除社保-工资个税-奖金个税

8.基于工作表“12月工资表”中的数据，从工作表“工资条”的A2单元格开始依次为每位员工生成样例所示的工资条，要求每张工资条占用两行、内外均加框线，第1行为工号、姓名、部门等列标题，第2行为相应工资奖金及个税金额，两张工资条之间空一行以便剪裁、该空行行高统一设为40默认单位，自动调整列宽到最合适大小，字号不得小于10磅。

本题思路：可以采用函数、宏、排序，其中最简单的就是排序的方法

样例图

1. 排序

经过观察，样例图中第一行为空行，第二行为标题，第三行为员工信息

通过计数可知一共有68位员工，因此在“工资条”工作表的第69行开始粘贴标题行，共粘贴68行

然后再粘贴员工信息，选择粘贴为值

在表格后一列编写1-68的序号，并复制粘贴两次

根据题目要求，每张工资条占用两行、内外均加框线，空行行高统一设为40默认单位，自动调整列宽到最合适大小，字号不得小于10磅

Ⅰ、调整空行行高为40

Ⅱ、调整工资条的框线、字号

设置内外框线

设置无填充

字体大小不小于10磅

再调整列宽

最后再根据第N列来降序排序

排序结束后删掉第N列数值

但是此时排序结束后发现框线没有跟着走，而是还在下面的位置

先统一把整个表格改为无框线

• 考点14：定位条件

按Ctrl+G打开定位条件

定位“常量”，就选中了所有有数据的单元格

为其添加所有框线

接下来要调整空行的行高

首先先点击自动调整行高，将数据列的行高恢复正常

随后再按Ctrl+G打开定位条件，定位到空值

将行高调整为40磅

9.调整工作表“工资条”的页面布局以备打印:纸张方向为横向，缩减打印输出使得所有列只占一个页面宽(但不得改变页边距)，水平居中打印在纸上。

• 考点15：打印设置

调整页面

调整居中方式

查看打印预览

Ctrl+S快速保存