真题练习30-Excel电子表格-全国计算机等级考试二级MS Office高级应用与设计考试【汪老师的个人课堂】

视频讲解

真题练习30-Excel电子表格_哔哩哔哩_bilibili

题库下载

全国计算机等级考试题库下载(用电脑下载安装):请点击

题目要求

第30套

人事部专员小金负责本公司员工档案的日常管理,以及员工每年各项基本社会保险费用的计算。按照下列要求帮助小金完成相关数据的整理、计算、统计和分析工作:

1.将考生文件夹下的工作簿文档“Excel素材.xlsx”另存为“Excel.xlsx”(“.xlsx”为文件扩展名),之后所有的操作均基于此文件,否则不得分。操作过程中,不可以随意改变原工作表素材数据的顺序。

2.在工作表“身份证校对”中按照下列规则及要求对员工的身份证号进行正误校对:

①中国公民的身份证号由18位组成,最后一位即第18位为校验码,通过前17位计算得出。第18位校验码的计算方法是:

将身份证的前17位数分别与对应系数相乘,将乘积之和除以11,所得余数与最后一位校验码一一对应。从第1位到第17位的对应系数以及余数与校验码对应关系参见工作表“校对参数”中所列。

②首先在工作表“身份证校对”中将身份证号的18位数字自左向右分拆到对应列。

③通过前17位数字以及工作表“校对参数”中的校对系数计算出校验码,填入V列中。

④将原身份证号的第18位与计算出的校验码进行对比,比对结果填入W列,要求比对相符时输入文本“正确”,不符时输入“错误”。

⑤如果校对结果错误,则通过设置条件格式将错误身份证号所在的数据行以“红色”文字、浅绿类型的颜色填充。

3.在工作表“员工档案”中,按照下列要求对员工档案数据表进行完善:

①输入每位员工的身份证号,员工编码与身份证号的对应关系见工作表“身份证校对”。如果已校对出错误,应将正确的身份证号填写入工作表“员工档案”中(假设所有错误号码都是由于最后一位校验码输错导致的)。

②计算每位员工截止20161231日的年龄,每满一年才计算一岁,一年按365天计算。

③在“工作状态”列的空白单元格中填入文本“在职”。

④计算每位员工在本公司工作的工龄,要求不足半年按半年计、超过半年按一年计,一年按365天计算,保留一位小数。其中,“在职”员工的工龄计算截止于20161231日,离职和退休人员计算截止于各自离职或退休的时间。

⑤计算每位员工的工龄工资,公式:工龄工资=本公司工龄×50

⑥计算员工的工资总额,公式:工资总额=工龄工资+签约工资+上年月均奖金。

4.在工作表“社保计算”中,按照下列要求计算每个员工本年度每月应缴社保金额:

①依据工作表“员工档案”中的数据,筛选出所有“在职”员工的“员工编号”、“姓名”和“工资总额”三列数据,依次填入BCD中,并按员工编号由小到大排序。

②本市上年职工平均月工资为7086元,首先将其定义为常量“人均月工资”,然后依据下列规则计算出每位员工的“社保基数”填入相应E列中,计算时需要在公式中调用新定义的常量“人均月工资”:社保基数最低为人均月工资7086元的60%,最高为人均月工资7086元的3倍:

③每个人每个险种的应缴社保费=个人的社保基数×相应的险种费率,按照工作表“社保费率”中所列险种费率分别计算每位在职员工应缴的各险种费用,包括公司负担和个人负担部分。其中:医疗个人负担=社保基数×医疗个人负担比例+个人额外费用3

④为数据表设置恰当的数字格式,套用一个表格格式并取消自动筛选标记。

5.以工作表“社保计算”的结果为数据源,参照下列图1所示样例,自新工作表“透视分析”的A3单元格开始生成数据透视表,要求如下:

①列标题应与示例图相同。

②按图中所示调整工资总额的数字格式。

③改变数据透视表样式。

1数据透视表样例

文字解析

第30组

1【解析】在考生文件夹中选中“Excel素材.xlsx”文件,选择文件菜单,在弹出的菜单中选中“另存为”命令,然后,将文件名称中的“Excel素材”改成“Excel”,在保存类型中选中“Excel工作簿(*.xlsx)”,单击“保存”按钮。

2【解析】②步骤1:将光标定位于D3单元格中,单击“插入函数”按钮,打开“插入函数”对话框,在选择函数中找到“MID”,单击“确定”按钮,打开“函数参数”对话框;

步骤2:第一个参数是文本参数,表示准备从中提取字符串的文本字符串,这里填“$C3”(为了后面使用智能填充,需要将列标签C加上绝对引用);第二个参数中输入要开始截取的字符的起始位置,这里填“1”;第三个参数中输入要截取的位数,这里填“1”,单击“确定”按钮,公式写成“=MID($C3,1,1)”;

步骤3将第二个参数“1”用函数来代替,这里使用“SUBSTITUTE”函数来计算。

在第1个参数中输入“D$2”,从D2单元格中截取位数,采用绝对引用行,是为了方便后面使用智能填充柄;在第2个参数中输入“”,指定要替换掉的字符;在第3个参数中输入“”””,指定要替换为的字符为空;在第4个参数默认不填。这样就提取到了字符串“1位”。

再次使用该函数将“位”替换掉,就可以获取截取位数数了。最后公式写成“SUBSTITUTE(SUBSTITUTE(D$2,"",""),"","")”。

所以D3单元格最后的公司为“=MID($C3,SUBSTITUTE(SUBSTITUTE(D$2,"",""),"",""),1)”。

步骤4:往右拖动D3单元格智能填充柄至U3单元格,完成其他位填充。

【知识拓展】SUBSTITUTE函数

功能:用于在某一文本字符串中替换指定文本。

语法:SUBSTITUTE(text,old_text,new_text,[instance_num])

说明:参数(Text)为需要替换其中字符的文本,或对含有文本的单元格的引用;参数(Old_text)为需要替换的旧文本;参数(New_text)用于替换 old_text 的文本;参数(Instance_num)为一数值,用来指定以 new_text 替换第几次出现的 old_text。如果指定了 instance_num,则只有满足要求的 old_text 被替换;如果缺省则将用 new_text 替换 Text中出现的所有 old_text

小提示:第二个参数也可以不用函数来计算,直接往后拖动D3单元格智能填充柄,直到U3单元格。根据每位对应的位数,修改函数中的第2个参数(例如E3单元格中的第二个参数应改成2),这样操作速度就慢些;

步骤5选中D3U3单元格区域,双击智能填充柄,即可完成其他行的填充。

步骤1本题首先根据算法规则求出“身份证的前17位数分别与对应系数相乘,将乘积之和”,这里我们可以采用“SUMPRODUCT”函数,公式为:SUMPRODUCT(D3:T3,校对参数!$E$5:$U$5)

步骤2由于要求校验参数数组中的元素必须是数值型,因此需要将数组元素转换为数值型,这里采用“VALUE”函数,公式为:SUMPRODUCT(VALUE(D3:T3),VALUE(校对参数!$E$5:$U$5))

步骤3将前两步所求结果除以11所得余数与最后一位校验码一一对应。这就需要使用到两个函数,我们求余采用“MOD”函数,公式为:MOD(SUMPRODUCT(VALUE(D3:T3),VALUE(校对参数!$E$5:$U$5)),11)

步骤4当求出余数后,要通过对比找出校验码,这里采用VLOOKUP函数。VLOOKUP是一个查找函数,给定一个查找的目标,它就能从指定的查找区域中查找返回想要查找到的值。本题中“VLOOKUP(MOD(SUMPRODUCT(VALUE(D3:T3),VALUE(校对参数!$E$5:$U$5)),11),校对参数!$B$4:$C$15,2,0)”的含义如下:

参数1—查找目标:余数为查找目标,“MOD(SUMPRODUCT(VALUE(D3:T3),VALUE(校对参数!$E$5:$U$5)),11)”。将在参数2指定区域的第1列中查找与余数相同的单元格。

参数2—查找范围:“校对参数!$B$4:$C$15”表示第2个工作表中数据表的全部区域(即B4:C15区域)。注意:查找目标一定要在该区域的第一列。

参数3—返回值的列数:“2”表示参数2中工作表的第2列。如果在参数2中找到与参数1相同的单元格,则返回第2列的内容。

参数4—精确OR模糊查找:“0”。最后一个参数是决定函数精确和模糊查找的关键。精确即完全一样,模糊即包含的意思。

注:参数引用绝对值是考虑到后面要智能填充。

知识拓展:(1SUMPRODUCT函数

功能:返回相应的数组或区域乘积的和。

语法:SUMPRODUCTarray1,array2,array3, ...

说明:Array1,array2,array3, ... 230个数组,其相应元素需要进行相乘并求和。数组参数必须具有相同的维数,否则,函数SUMPRODUCT将返回错误值#VALUE!。函数SUMPRODUCT将非数值型的数组元素作为0处理(数组内必须是数值型数据)。

2VALUE函数

功能:将代表数字的文本字符串转换成数字。

语法:VALUE(text)

说明:Text可以是Microsoft Excel中可识别的任意常数、日期或时间格式。如果Text不为这些格式,则函数VALUE返回错误值#VALUE!

步骤1比较两个单元格内容是否相等,可以使用“EXACT”函数,公式为:EXACT(U3,V3)

步骤2题目要求两个单元格内容符合则显示文本“正确”,不符合则显示文本“错误”。因此可以使用IF判断函数来实现,公式为:=IF(EXACT(U3,V3),"正确","错误")

知识拓展:(1EXACT函数

功能:用于检测两个字符串是否完全相同。

语法:EXACT(text1,text2)

说明:函数不区分文本型和数值型数字。EXACT函数的参数text1text2分别表示需要比较的文本字符串,也可以是引用单元格中的文本字符串,如果两个参数完全相同,EXACT函数返回TRUE值;否则返回FALSE值。

步骤1选中W3W122单元格区域,单击“开始”|“样式”分组中的“条件格式”下拉按钮,在下拉列表中选中“突出显示单元格规则”级联菜单中的“等于”命令,打开“等于”对话框;

步骤2在条件中输入“错误”,在设置为中单击“自定义格式”命令,打开“设置单元格格式”对话框;

步骤3在字体选项卡中设置字体颜色为标准色中的“红色”,在填充选项卡中设置背景色为“浅绿”,单击“确定”按钮,再次单击“确定”按钮。

3【解析】步骤1选中C列,单击“开始”|“数字”分组中的格式下拉按钮,选中其中的“常规”;

步骤2首先要判断员工对应的身份证号码校对是否正确,正确了就用原始身份证号码,否则将最后一位校验码换成正确校验码(即计算校验码列的内容)。因此需要使用IF函数,这里可以写成“IF(校验结果=”正确”,原身份证号码,原身份证号码前17+计算校验码)”;

步骤3要获取员工对应的校验结果,可以通过VLOOKUP函数来实现,这里可以写成:VLOOKUP(A3,身份证校对!$B$3:$W$122,22,0),其中“A3”是要查下的员工编号、“身份证校对!$B$3:$W$122”是查找范围、“22”是校验结果所在列数;

步骤4原身份证号码,也可以通过VLOOKUP函数来实现,这里可以写成:VLOOKUP(A3,身份证校对!$B$3:$W$122,2,0),其中“A3”是要查下的员工编号、“身份证校对!$B$3:$W$122”是查找范围、“2”是原身份证号码所在列数;

步骤5原身份证号码前17位,也可以通过VLOOKUP函数获取原身份证号码,MID函数获取前17位来实现,这里可以写成:MID(VLOOKUP(A3,身份证校对!$B$3:$W$122,2,0),1,17),其中“VLOOKUP(A3,身份证校对!$B$3:$W$122,2,0)”是原身份证号码、“1”是从一个字符开始、“17”是截取17个字符;

步骤6原身份证号码,也可以通过VLOOKUP函数来实现,这里可以写成:VLOOKUP(A3,身份证校对!$B$3:$W$122,21,0),其中“A3”是要查下的员工编号、“身份证校对!$B$3:$W$122”是查找范围、“21”是计算校验码所在列数;

步骤7将原身份证号码前17位和计算校验码链接在一起,可以使用连接符“&”(注:这里不能用“+”号,否则会出错);

步骤8整个计算公式最后写出来就是:=IF(VLOOKUP(A3,身份证校对!$B$3:$W$122,22,0)="正确",VLOOKUP(A3,身份证校对!$B$3:$W$122,2,0),MID(VLOOKUP(A3,身份证校对!$B$3:$W$122,2,0),1,17) & VLOOKUP(A3,身份证校对!$B$3:$W$122,21,0))

步骤1本题要求一年按365天算,也就是要求出出生日期到截止日期之间的总天数,然后除以365得出年数,而又要求没满一年才算一岁,那么就可以直接对除以出来的结果取整即可;

步骤2使用“DATEDIF”函数求出时间期间的天数,公式写成:DATEDIF(E3,"2016-12-31","D"),其中“D3”是起始日期、“"2016-12-31"”是截止日期、“"D"”是计算结果类型表示天数,然后用求出的天数除以365,即“DATEDIF(E3,"2016-12-31","D")/365”;

步骤3使用INT函数对计算结果求整,公式写成:INT(DATEDIF(E3,"2016-12-31","D")/365)

说明:INT函数是将一个要取整的实数向下取整为最接近的整数,唯一的一个参数是要取整的实数。

步骤1单击“工作状态”列的筛选按钮,取消“离职”和“退休”复选框的选中,单击“确定”按钮;

步骤2L10单元格中输入“在职”,然后复制L10单元格内容,选中L列其他空白单元格,然后按<Ctrl+V>组合键;

步骤3单击“工作状态”列的筛选按钮,选中“全选”复选框,单击“确定”按钮。

步骤1使用“DATEDIF”函数求出时间期间的天数,函数公式写成:DATEDIF(J3,IF(K3<>"",K3,"2016-12-31"),"D")

其中,第一个参数“J3”是起始日期,“IF(K3<>"",K3,"2016-12-31")”表示如果已经离职或退休,则按退休或离职算截止日期,否则按“20161231日”算截止日期。“"D"”是计算结果类型表示天数,然后用求出的天数除以365,即“DATEDIF(J3,IF(K3<>"",K3,"2016-12-31"),"D")/365”;

步骤2使用“CEILING”函数向上舍入为最接近的指定基数的倍数,这里写成:CEILING(DATEDIF(J3,IF(K3<>"",K3,"2016-12-31"),"D")/365,0.5)

步骤3双击M3单元格智能填充柄,完成其他单元格本公司工龄填充。

知识拓展:CEILING函数

语法:CEILING(number,significance)

功能:函数向上舍入为最接近指定基数的倍数。

说明:Number 待舍入的数值;Significance基数;将参数Number向上舍入(正向无穷大的方向)为最接近的significance的倍数。

注意:如果参数为非数值型,CEILING返回错误值 #VALUE!;如果numbersignificance符号相同,则对值按远离0的方向进行舍入;如果numbersignificanceCEILING符号相反,则返回错误值#NUM!

步骤1将光标定位于O3单元格,输入计算公式:=M3*50

步骤2双击O3单元格智能填充柄,完成其他单元格工龄工资填充。

步骤1将光标定位于Q3单元格,输入计算公式:=O3+N3+P3

步骤2双击Q3单元格智能填充柄,完成其他单元格工资总额填充。

选中M3:Q122单元格区域,单击“开始”|“数字”分组中的“增加小数位数”和“减少小数位数”按钮调整小数位为“2位”;

4【解析】步骤1在“员工档案”工作表中单击“工作状态”列的筛选按钮,取消“离职”和“退休”复选框的选中,单击“确定”按钮;

步骤2复制筛选后的A10:B122单元格数据到“社保计算”工作表的“B4:C103”单元格区域,复制筛选后的Q10:Q22单元格数据到“社保计算”工作表的“D4:D103”单元格区域;

步骤3返回“员工档案”工作表单击“工作状态”列的筛选按钮,选中“全选”复选框,单击“确定”按钮;

步骤4在“社保计算”工作表中单击“数据”|“排序和筛选”分组中的“排序”按钮,打开“排序”对话框,在主要关键字中选中“员工编号”、次序中选中“升序”,单击“确定”按钮。

步骤1选中任意单元格,然后单击右键,在弹出的菜单中单击“定义名称”命令,打开“新建名称”对话框,将名称改为“人均月工资”、将引用位置中的内容改成“7086”,单击“确定”按钮;

步骤2题中指明,最低基数=人均月工资*60%,最高基数=人均月工资*3;当工资总额小于最低基数时,社保基数为最低基数(即:D4<人均月工资*60%);当工资总额高于最高基数时,社保基数为最高基数(即:D4>人均月工资*3);

步骤3这里通过IF判断工资总额所在范围,然后取社保基数值,公式写为:=IF(D4<人均月工资*60%,人均月工资*60%,IF(D4>人均月工资*3,人均月工资*3,D4))

步骤4双击E4单元格智能填充柄,完成其他社保基数填充。

步骤1将光标定位于F3单元格,输入计算公式:=E4*社保费率!$B$4

步骤2将光标定位于G3单元格,输入计算公式:=E4*社保费率!$C$4

步骤3将光标定位于H3单元格,输入计算公式:=E4*社保费率!$B$5

步骤4将光标定位于I3单元格,输入计算公式:=E4*社保费率!$C$5

步骤5将光标定位于J3单元格,输入计算公式:=E4*社保费率!$B$6

步骤6将光标定位于K3单元格,输入计算公式:=E4*社保费率!$C$6

步骤7将光标定位于L3单元格,输入计算公式:=E4*社保费率!$B$7

步骤8将光标定位于M3单元格,输入计算公式:=E4*社保费率!$C$7

步骤9将光标定位于N3单元格,输入计算公式:=E4*社保费率!$B$8

步骤10将光标定位于O3单元格,输入计算公式:=E4*社保费率!$C$8+3

步骤11选中F3:O3单元格区域,双击智能填充柄,完成其他单元格数据填充。

步骤1选中数据表区域任意一个单元格,然后按<Ctrl+A>组合键,选中所有数据;

步骤2单击“开始”|“样式”分组中的“套用表格格式”下拉按钮,在下拉列表中选中一种样式(例如:表格样式浅色9),弹出“套用表格式”对话框,选中“表包含标题”,单击“确定”按钮;

步骤3单击“数据”|“排序和筛选”分组中的“筛选”按钮,取消其选中状态;

步骤4选中D4:O103单元格区域,单击“开始”|“数字”分组中的“增加小数位数”和“减少小数位数”按钮调整小数位为“2位”,单击“千分位”按钮;

5【解析】步骤1选中“社保计算”数据表区域任意一个单元格,然后按<Ctrl+A>组合键;

步骤2单击“插入”|“表格”分组中的“数据透视表”下拉按钮中的“数据透视表”命令,打开“创建数据透视表”对话框,单击“确定”按钮;

步骤3此时会创建一个带数据透视表的新工作表,将工作表名称改为“透视分析”;

步骤4将“社保基数”字段拖到“行标签”中,在添加的行标签数据上单击鼠标右键,在弹出菜单中单击“组合”命令,打开“组合”对话框;

步骤5设置起始于为“4200”、终止于为“22200”、步长为“3000”,单击“确定”按钮;

步骤6拖动“姓名”字段到“数值”中,拖动“工资总额”字段到“数值”中,再次拖动“工资总额”字段到“数值”中;

步骤7双击“求和项:工资总额2”标题,打开“值字段设置”对话框,将自定义名称改为“工资总额占比”,单击“值显示方式”选项卡,选中“总计的汇总百分比”,单击“确定”按钮;

步骤8双击“求和项:工资总额”标题,打开“值字段设置”对话框,将自定义名称改为“工资总额(元)”,单击“确定”按钮;

步骤9双击“计数项:姓名”标题,打开“值字段设置”对话框,将自定义名称改为“人数”,单击“确定”按钮;

步骤10双击“行标签”标题,将内容改成“社保基数”;

步骤11选中C4:C10单元格区域,单击两次“开始”|“数字”分组中的“增加小数位数”按钮,单击“千分位”按钮;

步骤12选中A3:D10单元格区域,单击“设计”|“数据透视表样式”中的其他下拉按钮,在下拉列表中选中“数据透视表样式中等深浅26”样式。

保存并关闭“Excel.xlsx”文档。

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值