第53卷 课程综合卷《电子表格处理》湖北省技能高考《计算机类考纲百套卷》

日期 工号 姓名 打卡时间 状态null 编写说明:湖北省技能高考理论考试《计算机网络技术(计算机基础)考纲百套卷》,依据《湖北省技能高考计算机专业考试大纲专业课考试纲要》编写每门课程均分三层训练:基础层(具象化支架)以拆解考点为微目标,紧扣考纲 掌握理解 要求编写考点训练卷;巩固层(关联性支架)强化知识交叉与场景关联,按考纲专题编专题训练卷;应用层(引导性支架)聚焦真题突破,结合知识模块与教材编写课程综合卷 本试卷是第53卷,按《电子表格处理》范围和要求编写。具体内容为:单元格引用（相对、绝对、混合引用，工作表之间、工作簿之间引用）和计算，公式及数组公式的应用，常用函数的使用；图表的创建、编辑和格式设置与修饰;数据透视表和数据透视图的使用以及多个工作表联动操作;工作表单元格格式化操作,包括单元格和单元格区域的命名、保护,批注,条件格式等格式的设置;工作表的插入、复制、移动、更名、保存等基本操作数据的排序、筛选、对比、分类汇总、数据合并计算﹑数据有效性检查; 湖北省《计算机类考纲百套卷》第53卷 《电子表格处理》 考试时间60分钟 满分100分 班级 姓名 学号 成绩 一、电子表格操作题(本大题共10小题,每题10分,共100分) 第1题 (1).打开人力资源综合数据.xlsx,将Sheet1中的A1:M21区域整体复制到Sheet2的A1起始位置,将Sheet2重命名为薪酬计算表,标签颜色设为橙色;将Sheet1重命名为员工档案,标签颜色设为蓝色。 (2).在员工档案工作表中,将标题行A1:M1的字体设为黑体、字号12、加粗、白色,填充色设为深蓝色;设置A2:M21区域的外边框为黑色双实线,内边框为灰色单实线;将A列(工号)的列宽设为8,B列(姓名)设为10,D列(部门)设为12,G列(绩效得分)设为10;冻结首行窗格。 (3).在员工档案工作表中,为C2:C21(性别)列设置数据有效性,限制只能输入男或女,输入提示为请选择性别:男或女,出错警告为性别只能为男或女;为L2:L21(学历)列设置数据有效性,下拉选项为博士、硕士、本科、大专、中专。 (4).在员工档案工作表中,将B2:B21区域命名为姓名列表,将G2:G21区域命名为绩效列表,将D2:D21区域命名为部门列表;保护整个工作表,允许用户选择未锁定单元格,密码设为protect123。 (5).在员工档案工作表中,使用条件格式:将绩效得分≥90的单元格填充浅绿色并加粗;将绩效得分<70的单元格填充浅红色并加粗斜体;将出勤天数<20的单元格添加红色边框;将缺勤天数≥3的单元格字体颜色设为红色。 (6).在员工档案工作表中,为G2:G21(绩效得分)列添加数据条(蓝色渐变),为H2:H21(出勤天数)列添加色阶(红-黄-绿三色),为I2:I21(缺勤天数)列添加图标集(三个圆形图标),保存文件。 答案: (1).打开“人力资源综合数据.xlsx”→选中Sheet1的A1:M21区域执行复制操作→切换到Sheet2在A1位置执行粘贴→右键单击Sheet2标签选择重命名为“薪酬计算表”→再次右键将标签颜色设为橙色→右键单击Sheet1标签重命名为“员工档案”→标签颜色设为蓝色 (2).切换到“员工档案”工作表→框选A1:M1标题行区域→在开始选项卡中设为黑体12号加粗、白色字体→填充色设为深蓝色→选中A2:M21数据区域→打开边框下拉菜单选择其他边框→外边框设为黑色双实线、内边框灰色单实线→分别调整A列列宽为8、B列10、D列12、G列10→切换到视图选项卡执行冻结首行窗格 (3).框选C2:C21性别区域→切换到数据选项卡执行数据验证→允许中选择序列→来源输入“男,女”→输入信息中添加提示“请选择性别：男或女”→出错警告中设置标题和内容→同理选中L2:L21学历列→数据验证选择序列→来源输入“博士,硕士,本科,大专,中专”→确认完成 (4).选中B2:B21→在公式选项卡中执行定义名称命名为“姓名列表”→同理将G2:G21命名为“绩效列表”→D2:D21命名为“部门列表”→切换到审阅选项卡执行保护工作表→输入密码“protect123”→勾选“选定未锁定单元格”→确认完成 (5).选中G2:G21绩效得分区域→执行条件格式中的突出显示单元格规则→大于等于90时填充浅绿色并加粗→再次执行条件格式设置小于70填充浅红色并加粗斜体→选中H2:H21出勤天数→条件格式突出显示小于20添加红色边框→选中I2:I21缺勤天数→条件格式突出显示大于等于3字体设为红色 (6).选中G2:G21→执行条件格式中的数据条选择蓝色渐变→选中H2:H21→执行条件格式中的色阶选择红-黄-绿三色→选中I2:I21→执行条件格式中的图标集选择三个圆形图标→保存文件 第2题 (1).在员工档案工作表右侧(N列至Q列)分别插入四列新列,列标题依次为:绩效奖金岗位津贴工龄工资应发工资;使用相对引用公式绩效奖金=绩效得分×50计算每位员工的绩效奖金(N2:N21),使用VLOOKUP函数从Sheet2中查找对应岗位津贴(O2:O21)。 (2).在薪酬计算表工作表中,使用混合引用计算工龄工资(将Sheet1中的入职日期与当前日期2024-12-31的差额折算成年数,乘以从公司薪酬标准.xlsx中绝对引用的工龄津贴标准值$B$6),结果保留整数。 (3).在薪酬计算表工作表中,使用数组公式{=SUM((部门="技术部")*应发工资)}计算技术部的应发工资总和,结果存放于R2单元格;使用数组公式{=AVERAGE(IF(部门="市场部",绩效得分))}计算市场部的平均绩效得分,结果存放于R3单元格。 (4).在薪酬计算表工作表中,使用IF函数根据绩效得分评定等级(≥90为A,80-89为B,70-79为C,60-69为D,<60为E),结果存放于S2:S21;使用COUNTIFS函数统计各部门中绩效等级为A的人数,结果存放于T2:T5区域。 (5).在薪酬计算表工作表中,在U列插入实际到手工资,使用跨工作簿引用从公司薪酬标准.xlsx中引用社保比例(8%)和公积金比例(5%),计算公式为=应发工资×(1-社保比例-公积金比例)+全勤奖+交通补贴(全勤奖和交通补贴从外部文件绝对引用)。 (6).在薪酬计算表工作表中,使用RANK函数对实际到手工资进行降序排名(引用区域使用混合引用$U$2:$U$21),结果存放于V2:V21;使用LARGE函数找出实际到手工资前三名,存放于W2:W4;使用SMALL函数找出后三名,存放于X2:X4,保存文件。 答案: (1).在员工档案工作表右侧N列至Q列依次插入四列→分别在N1至Q1输入“绩效奖金”“岗位津贴”“工龄工资”“应发工资”→在N2输入公式“=G2*50”并双击填充柄向下复制→在O2输入公式“=VLOOKUP(E2,Sheet2!$A$2:$D$8,4,0)”并双击填充柄向下复制至O21 (2).切换到“薪酬计算表”工作表→在P2列输入计算工龄工资的公式“=INT(DATEDIF(J2,DATE(2024,12,31),"Y")*[公司薪酬标准.xlsx]Sheet1!$B$6)”→双击填充柄向下复制至P21→确保结果保留整数 (3).在R2单元格输入数组公式“{=SUM((D2:D21=“技术部”)*M2:M21)}”→按Ctrl+Shift+Enter确认→在R3输入数组公式“{=AVERAGE(IF(D2:D21=“市场部”,G2:G21))}”→按Ctrl+Shift+Enter确认 (4).在S2单元格输入公式“=IF(G2>=90,"A",IF(G2>=80,"B",IF(G2>=70,"C",IF(G2>=60,"D","E"))))”→双击填充柄向下复制至S21→在T2输入“=COUNTIFS(D:D,"技术部",S:S,"A")”→向下填充至T5统计各部门A级人数 (5).在U1输入“实际到手工资”→在U2输入公式“=M2*(1-[公司薪酬标准.xlsx]Sheet1!$B$2-[公司薪酬标准.xlsx]Sheet1!$B$3)+[公司薪酬标准.xlsx]Sheet1!$B$4+[公司薪酬标准.xlsx]Sheet1!$B$5”→双击填充柄向下复制至U21 (6).在V1输入“排名”→在V2输入公式“=RANK(U2,$U$2:$U$21,0)”→双击填充柄向下复制→在W2输入“=LARGE($U$2:$U$21,ROW(A1))”向下复制至W4找出前三名→在X2输入“=SMALL($U$2:$U$21,ROW(A1))”向下复制至X4找出后三名→保存文件 第3题 (1).在员工档案工作表左侧插入一列(A列前),列标题为序号,使用填充柄从1到20填充;将员工档案工作表复制一份到Sheet3之前,重命名为备份-员工,标签颜色设为绿色,然后将备份-员工工作表隐藏。 (2).在员工档案工作表中,对数据按部门进行排序(自定义顺序:技术部、市场部、人事部、财务部),部门相同则按绩效得分从高到低排序,部门相同、绩效得分相同则按入职日期从早到晚排序。 (3).在员工档案工作表中,应用高级筛选:筛选条件为部门为技术部或市场部且绩效得分≥85,将筛选结果复制到以A23为起始位置的新区域。 (4).在员工档案工作表中,使用分类汇总功能按部门对基本工资进行平均值汇总,再对绩效得分进行最大值汇总,汇总结果显示在每组数据下方,并分页显示每个部门的汇总结果。 (5).在员工档案工作表中,取消分类汇总状态,使用合并计算功能(引用位置为A1:M21),以部门为标签位置,计算各部门的基本工资平均值、绩效得分平均值和出勤天数平均值,将结果存放于Sheet3的A1起始位置。 (6).在Sheet3中,将合并计算结果区域设置为货币格式保留2位小数,添加外边框为蓝色双实线,内边框为蓝色单实线;将A1:D1标题行填充为深灰色,字体设为白色加粗,保存文件。 答案: (1).选中A列整列执行插入新列→在新A1输入“序号”→在A2输入1、A3输入2后向下填充至A21→右键Sheet1标签执行移动或复制→勾选建立副本并选择位置在Sheet3之前→重命名为“备份-员工”→标签颜色绿色→右键该标签选择隐藏 (2).选中数据区域A1:M21→执行排序操作→主要关键字选“部门”→在排序对话框中选择自定义序列按“技术部、市场部、人事部、财务部”顺序排列→添加条件次要关键字“绩效得分”降序→再添加条件“入职日期”升序→确认完成 (3).在空白区域构建条件区域（部门为“技术部”或“市场部”且绩效得分≥85）→执行高级筛选→选择“将筛选结果复制到其他位置”→列表区域选A1:M21→条件区域选刚构建的条件区域→复制到选A23起始位置→确认完成 (4).选中数据区域→切换到数据选项卡执行分类汇总→分类字段选“部门”→汇总方式选“平均值”→选定汇总项勾选“基本工资”→再次执行分类汇总→汇总方式改为“最大值”→选定汇总项勾选“绩效得分”→勾选“每组数据分页”→确认完成 (5).在分类汇总结果中点击“全部删除”取消分类汇总状态→切换到数据选项卡执行合并计算→引用位置选A1:M21→标签位置选“最左列”→勾选首行和最左列→计算方式选“平均值”→确认后将结果存放于Sheet3的A1起始位置 (6).在Sheet3中选中合并计算结果→设为货币格式保留2位小数→添加外边框蓝色双实线、内边框蓝色单实线→选中A1:D1标题行填充深灰色→字体白色加粗→保存文件 第4题 (1).在员工档案工作表中,为E2:E21(岗位)列创建批注,内容为该岗位职级对应系数请参照Sheet2,批注框大小设为宽3厘米、高1.5厘米,批注字体设为宋体、字号9。 (2).在员工档案工作表中,使用条件格式将合同期限≥5年的单元格填充金色并加粗;将学历为硕士的单元格填充淡紫色,字体为蓝色;将培训次数≥4次的单元格添加绿色边框。 (3).在员工档案工作表中,创建两个命名区域:工资数据(包含F2:F21基本工资和N2:Q21应发工资相关列)和绩效数据(包含G2:G21绩效得分和S2:S21绩效等级);锁定工资数据区域,设置密码为salary2024,允许用户查看但不能修改。 (4).在薪酬计算表工作表中,使用SUMIFS函数计算技术部中工程师岗位的基本工资总和,结果存放于Z2单元格;使用AVERAGEIFS函数计算市场部中绩效得分≥80的员工平均基本工资,结果存放于Z3单元格。 (5).在薪酬计算表工作表中,使用MAXIFS函数查找人事部中最高绩效得分对应的员工姓名(需结合INDEX+MATCH),结果存放于Z4单元格;使用MINIFS函数查找财务部中最低基本工资对应的员工姓名,结果存放于Z5单元格。 (6).在薪酬计算表工作表中,将Z2:Z5区域命名为关键指标,设置该区域的数字格式为货币格式保留0位小数(姓名列除外),然后保护该命名区域,密码为key123,保存文件。 答案: (1).在员工档案工作表中选中E2:E21岗位区域→切换到审阅选项卡执行新建批注→输入内容“该岗位职级对应系数请参照Sheet2”→右键批注框选择设置批注格式→在大小选项卡中设宽3厘米高1.5厘米→切换到字体选项卡设为宋体9号 (2).选中K2:K21合同期限区域→执行条件格式突出显示大于等于5时填充金色并加粗→选中L2:L21学历区域→条件格式突出显示等于“硕士”时填充淡紫色、字体蓝色→选中M2:M21培训次数→条件格式突出显示大于等于4时添加绿色边框 (3).框选F2:F21和N2:Q21区域→在公式选项卡中定义名称为“工资数据”→框选G2:G21和S2:S21区域→定义名称为“绩效数据”→选中“工资数据”区域→右键设置单元格格式→在保护选项卡中勾选锁定→审阅中保护工作表→密码“salary2024”→允许用户选择单元格但不允许修改 (4).切换到薪酬计算表→在Z2输入公式“=SUMIFS(员工档案!F:F,员工档案!D:D,"技术部",员工档案!E:E,"工程师")”→在Z3输入公式“=AVERAGEIFS(员工档案!F:F,员工档案!D:D,"市场部",员工档案!G:G,">=80")” (5).在Z4输入数组公式“{=INDEX(员工档案!B:B,MATCH(MAXIFS(员工档案!G:G,员工档案!D:D,"人事部"),员工档案!G:G,0))}”→按Ctrl+Shift+Enter确认→在Z5输入公式“{=INDEX(员工档案!B:B,MATCH(MINIFS(员工档案!F:F,员工档案!D:D,"财务部"),员工档案!F:F,0))}”→按Ctrl+Shift+Enter确认 (6).选中Z2:Z5区域→在公式选项卡中定义名称为“关键指标”→将数值单元格设为货币格式保留0位小数（姓名Z4和Z5除外）→审阅中保护工作表→密码“key123”→保存文件 第5题 (1).在Sheet4中(新建工作表),基于员工档案工作表的A1:M21数据创建一个数据透视表,行标签为部门,列标签为学历,值字段为基本工资(平均值),将数据透视表放置于Sheet4的A1起始位置。 (2).在数据透视表中,添加绩效得分作为第二个值字段(汇总方式为最大值),将值字段名称分别修改为平均工资和最高绩效,并设置平均工资为货币格式保留0位小数。 (3).在数据透视表中,将学历列的顺序调整为:硕士、本科、大专;将行的总计和列的总计均显示,为数据透视表套用样式数据透视表样式中等深浅6。 (4).在Sheet4中,基于上述数据透视表插入一个数据透视图,图表类型为堆积柱形图,图表标题设为各部门各学历平均工资分布,图表样式设为样式7。 (5).在数据透视图中,添加数据标签(仅显示值),将图例位置设为底部,设置图表区填充为淡蓝色渐变,绘图区填充为白色,垂直轴主要单位设为5000。 (6).在数据透视图中,插入一个切片器(字段为性别),将切片器连接到数据透视图,设置切片器样式为切片器样式彩色4,列数为2列,将切片器放置于图表右侧,调整大小使整体布局协调,保存文件。 答案: (1).新建Sheet4→选中员工档案的A1:M21→执行插入数据透视表→放置于Sheet4的A1起始位置→将“部门”拖至行标签→“学历”拖至列标签→“基本工资”拖至值区域并设为平均值 (2).将“绩效得分”拖入值区域→右键执行值字段设置→汇总方式选“最大值”→分别重命名为“平均工资”和“最高绩效”→选中平均工资数值设为货币格式保留0位小数 (3).拖动列标签学历调整顺序为“硕士、本科、大专”→在设计选项卡中勾选显示行总计和列总计→套用样式“数据透视表样式中等深浅6” (4).切换到插入选项卡执行数据透视图→选择堆积柱形图→图表标题设为“各部门各学历平均工资分布”→在设计选项卡中应用样式7 (5).执行添加数据标签仅显示值→将图例位置移至底部→设置图表区填充为淡蓝色渐变→绘图区填充为白色→垂直轴主要单位设为5000 (6).插入切片器选择“性别”字段→在切片器工具选项中连接到数据透视图→应用彩色4样式→列数设为2列→放置于图表右侧调整大小→保存文件 第6题 (1).在Sheet5中(新建工作表),基于员工档案工作表的B2:M21数据创建一个新的数据透视表,行标签为入职日期(按年份分组),列标签为部门,值字段为工号(计数),计算各部门各年度入职人数。 (2).在数据透视表中,将入职日期的分组更改为按季度分组(不按年),并重命名分组标签为Q1Q2Q3Q4;添加合同期限字段作为筛选器(报表筛选),仅显示合同期限≥5年的记录。 (3).在数据透视表中,添加培训次数字段作为第二个值字段(汇总方式为平均值),将值字段名称修改为平均培训次数,设置数字格式保留1位小数。 (4).在数据透视表中,设置报表布局为以表格形式显示,重复所有项目标签,显示分类汇总(仅显示总计),并将整个数据透视表区域套用样式数据透视表样式深色3。 (5).在Sheet5中,基于上述数据透视表插入两个图表:图表1为簇状柱形图(显示各部门各季度入职人数),图表2为折线图(显示各季度平均培训次数趋势),两个图表垂直排列。 (6).在图表1中,添加数据标签,设置图表标题为各部门季度入职人数统计;在图表2中,添加趋势线(线性),显示公式和R²值,设置图表标题为培训次数季度趋势分析,调整两个图表的大小和位置使其在页面中美观分布,保存文件。 答案: (1).新建Sheet5→基于员工档案的B2:M21数据创建数据透视表→将“入职日期”拖至行标签（自动按年分组）→“部门”拖至列标签→“工号”拖至值区域（计数） (2).右键入职日期选择组合→将分组改为按季度分组→重命名分组标签为“Q1”“Q2”“Q3”“Q4”→将“合同期限”拖至筛选器→仅显示≥5年的记录 (3).将“培训次数”拖入值区域→汇总方式选平均值→重命名为“平均培训次数”→数字格式保留1位小数 (4).在设计选项卡中报表布局选以表格形式显示→勾选重复所有项目标签→显示分类汇总仅总计→套用样式“数据透视表样式深色3” (5).基于透视表插入两个图表：图表1选簇状柱形图显示各部门各季度入职人数→图表2选折线图显示各季度平均培训次数趋势→两个图表垂直排列 (6).图表1添加数据标签→标题“各部门季度入职人数统计”→图表2添加线性趋势线显示公式和R²→标题“培训次数季度趋势分析”→调整大小和位置使页面美观→保存文件 第7题 (1).在员工档案工作表中,根据B1:B21(姓名)和F1:F21(基本工资)两列数据,插入一个条形图,图表标题设为员工基本工资排名,将图例隐藏,并将水平轴(基本工资)的主要单位设为2000,最小值为0,最大值为15000。 (2).在条形图中,将数据系列颜色设置为从蓝色到红色的渐变(根据数值大小自动变色),并添加数据标签显示在条形内部(右侧),标签字体设为宋体、字号8、白色。 (3).在条形图中,将垂直轴(姓名)的字体设为黑体、字号9,将水平轴的字体设为宋体、字号9,为图表添加背景(图表区填充设为淡绿色渐变,绘图区填充设为白色)。 (4).在员工档案工作表中,根据D1:D21(部门)和F1:F21(基本工资)两列数据,插入一个三维饼图(显示各部门基本工资总和占比),将技术部所在扇区分离15%,并将该扇区填充色设为深蓝色。 (5).在三维饼图中,添加百分比数据标签,显示类别名称和百分比,标签位置为数据标签外,字体设为黑体、字号10、加粗;将图表标题设为各部门工资占比分布,图例位置设为右下角。 (6).在员工档案工作表中,根据G1:G21(绩效得分)和N1:N21(绩效奖金)两列数据,插入一个散点图(显示绩效得分与绩效奖金的关系),添加趋势线(多项式趋势线,阶数为2),显示公式和R²值,图表标题设为绩效与奖金关系分析,保存文件。 答案: (1).选中B1:B21姓名和F1:F21基本工资→插入条形图→图表标题“员工基本工资排名”→隐藏图例→双击水平轴设主要单位2000、最小0、最大15000 (2).右键数据系列设置渐变填充从蓝到红→添加数据标签位置在条形内部右侧→标签字体宋体8号白色 (3).垂直轴姓名设为黑体9号→水平轴宋体9号→图表区填充淡绿色渐变→绘图区白色背景 (4).选中D1:D21和F1:F21→插入三维饼图→将“技术部”扇区分离15%并填充深蓝色→标题“各部门工资占比分布” (5).添加数据标签显示类别名称和百分比→位置数据标签外→字体黑体10加粗→图例右下角 (6).选中G1:G21和N1:N21→插入散点图→添加多项式趋势线阶数2→显示公式和R²→标题“绩效与奖金关系分析”→保存文件 第8题 (1).在Sheet6中(新建工作表),将Sheet6重命名为联动分析,在A1单元格中输入人力资源联动分析报告,合并A1:F1居中,字体设为黑体22加粗,背景色为深绿色,字体颜色为白色。 (2).在联动分析工作表中,使用跨表引用从员工档案中获取以下信息:A3为员工总数(使用COUNTA),B3为男性人数(使用COUNTIF),C3为女性人数,D3为平均绩效得分,E3为最高工资,F3为最低工资,并设置相应格式。 (3).在联动分析工作表中,从A5开始创建一个部门统计表,行标签为四个部门,分别使用SUMIFS和AVERAGEIFS从员工档案中引用各部门的基本工资总和、绩效得分平均值、出勤天数平均值、培训次数总和。 (4).在联动分析工作表中,从A10开始创建一个岗位分布表,行标签为Sheet2中的六个岗位,使用VLOOKUP+SUMIFS组合从员工档案和薪酬计算表中引用各岗位的人数、平均基本工资、平均应发工资。 (5).在联动分析工作表中,从A15开始创建一个绩效等级分布表,统计各部门中各绩效等级(A、B、C、D、E)的人数,使用COUNTIFS数组公式实现(各部门×各等级),添加百分比行显示各等级占比。 (6).在联动分析工作表中,将A3:F3、A5:D8、A10:D15、A15:F20四个区域分别添加边框和不同颜色的底纹(区域间用颜色区分),设置打印区域为A1:F25,页边距为适中,添加页眉公司人力资源部和页脚第&P页,保存文件。 答案: (1).新建Sheet6→重命名为“联动分析”→在A1输入“人力资源联动分析报告”→合并A1:F1居中→黑体22加粗深绿背景白色字体 (2).在A3输入“员工总数”→B3输入“=COUNTA(员工档案!B:B)-1”→C3“男性人数”用COUNTIF统计→D3“女性人数”同理→E3“平均绩效得分”用AVERAGE→F3“最高工资”用MAX→G3“最低工资”用MIN→设置对应格式 (3).从A5创建部门统计表→行标签四个部门→使用SUMIFS计算各部门基本工资总和→AVERAGEIFS计算绩效得分平均值、出勤天数平均值→SUMIFS计算培训次数总和 (4).从A10创建岗位分布表→行标签Sheet2中六个岗位→使用VLOOKUP+SUMIFS组合统计各岗位人数、平均基本工资、平均应发工资 (5).从A15创建绩效等级分布表→使用COUNTIFS数组公式统计各部门各等级人数→添加百分比行显示各等级占比 (6).为A3:F3、A5:D8、A10:D15、A15:F20四个区域添加不同颜色边框和底纹→设置打印区域A1:F25→页边距适中→页眉“公司人力资源部”→页脚“第&P页”→保存文件 第9题 (1).在Sheet7中(新建工作表),基于员工档案工作表的A1:M21数据创建一个数据透视表,同时使用部门作为行标签和列标签(行标签显示部门,列标签也显示部门),值字段为工号(计数),生成一个部门交叉计数表(即各部门之间的员工流动或对比矩阵)。 (2).在数据透视表中,使用计算字段功能添加一个新字段绩效系数,公式为=绩效得分/100,将该字段添加到值区域,汇总方式设为平均值;再添加一个计算字段工资占比,公式为=基本工资/SUM(基本工资)。 (3).在数据透视表中,插入一个日程表(时间线),字段为入职日期,设置日程表显示为季度层级,日程表样式为日程表样式深色2。 (4).在Sheet7中,基于上述数据透视表插入一个数据透视图,图表类型为百分比堆积条形图,图表标题设为各部门绩效系数与工资占比分析,图表样式设为样式10。 (5).在数据透视图中,将绩效系数系列的颜色设为橙色渐变,将工资占比系列的颜色设为蓝色渐变;添加数据标签(显示百分比),标签位置为数据标签内,字体设为白色、字号8。 (6).在数据透视图中,插入切片器(字段为学历),连接到数据透视图,设置切片器列数为3列;调整日程表、切片器和数据透视图的布局,使其在Sheet7中整齐排列,保存文件。 答案: (1).新建Sheet7→基于员工档案A1:M21创建数据透视表→将“部门”同时拖至行标签和列标签→“工号”拖至值区域（计数）生成部门交叉计数表 (2).在数据透视表分析选项卡中执行计算字段→新建“绩效系数”公式“=绩效得分/100”添加到值区域（平均值）→再建“工资占比”公式“=基本工资/SUM(基本工资)”添加到值区域（平均值） (3).插入日程表字段选“入职日期”→显示季度层级→应用“日程表样式深色2” (4).插入数据透视图选百分比堆积条形图→标题“各部门绩效系数与工资占比分析”→应用样式10 (5).绩效系数系列填充橙色渐变→工资占比系列蓝色渐变→添加数据标签显示百分比位置数据标签内→字体白色8号 (6).插入学历切片器→连接到数据透视图→列数3列→调整日程表、切片器、图表布局整齐排列→保存文件 第10题 (1).打开人力资源综合数据.xlsx,将整个工作簿另存为人力资源综合分析报告.xlsx;在最后新建一个工作表,重命名为总览仪表板,标签颜色设为红色。 (2).在总览仪表板工作表中,使用跨表引用从薪酬计算表中引用实际到手工资的总和、平均值、最大值、最小值,放置于A1:D2区域,设置货币格式保留0位小数,添加金色底纹。 (3).在总览仪表板工作表中,创建三个图表(均基于员工档案数据):左上角放置各部门人数分布饼图,右上角放置各学历人数柱形图,下方中间放置各月入职人数折线图(按入职日期月份统计)。 (4).在总览仪表板工作表中,插入四个切片器,分别连接三个图表,字段为部门性别学历岗位,四个切片器水平排列于图表上方。 (5).在总览仪表板工作表中,添加一个文本框,内容为2024年度人力资源综合报告,字体设为黑体30加粗,颜色为深蓝色,居中放置于页面顶部;在页面底部添加三个形状按钮:刷新数据导出PDF打印预览。 (6).在总览仪表板工作表中,统一美化所有图表:图表区边框均为深蓝色实线(1磅),字体统一为微软雅黑,图表标题字号14加粗;设置所有切片器样式为切片器样式彩色2;调整整体页面布局,确保打印预览时所有内容在一页内完整显示,保存并关闭所有文件。 答案: (1).执行文件另存为“人力资源综合分析报告.xlsx”→在最后新建工作表重命名为“总览仪表板”→标签颜色红色 (2).在A1:D2区域使用跨表引用从薪酬计算表中引用实际到手工资的总和、平均值、最大值、最小值→设置货币格式保留0位小数→添加金色底纹 (3).基于员工档案数据创建三个图表：左上部门人数分布饼图→右上各学历人数柱形图→下方各月入职人数折线图 (4).插入四个切片器字段为“部门”“性别”“学历”“岗位”→全部连接到三个图表→水平排列于图表上方 (5).插入文本框输入“2024年度人力资源综合报告”→黑体30加粗深蓝色居中顶部→底部添加三个矩形按钮“刷新数据”“导出PDF”“打印预览” (6).统一美化：图表区边框深蓝1磅→字体微软雅黑→标题14加粗→切片器样式彩色2→调整布局确保打印预览一页完整显示→保存并关闭所有文件 原创精品资源学科网独家享有版权,侵权必究！ 学科网(北京)股份有限公司 学科网(北京)股份有限公司 学科网（北京）股份有限公司 学科网（北京）股份有限公司 $Sheet1 岗位 岗位系数 绩效奖金基数 岗位津贴 专员 1 1500 300 工程师 1.3 2000 500 主管 1.6 2500 800 经理 2 3000 1200 高工 1.8 2800 1000 会计 1.2 1800 400 Sheet2 Sheet3 $薪酬项目 标准值 社保比例 8.00% 公积金比例 5.00% 餐补标准（元/天） 20 交通补贴（元/月） 200 全勤奖（元/月） 300 工龄津贴（元/年） 50部门 年度目标（万元） 季度目标（万元） 技术部 1200 300 市场部 1500 375 人事部 600 150 财务部 800 200 编写说明:湖北省技能高考理论考试《计算机网络技术(计算机基础)考纲百套卷》,依据《湖北省技能高考计算机专业考试大纲专业课考试纲要》编写每门课程均分三层训练:基础层(具象化支架)以拆解考点为微目标,紧扣考纲 掌握理解 要求编写考点训练卷;巩固层(关联性支架)强化知识交叉与场景关联,按考纲专题编专题训练卷;应用层(引导性支架)聚焦真题突破,结合知识模块与教材编写课程综合卷 本试卷是第53卷,按《电子表格处理》范围和要求编写。具体内容为:单元格引用（相对、绝对、混合引用，工作表之间、工作簿之间引用）和计算，公式及数组公式的应用，常用函数的使用；图表的创建、编辑和格式设置与修饰;数据透视表和数据透视图的使用以及多个工作表联动操作;工作表单元格格式化操作,包括单元格和单元格区域的命名、保护,批注,条件格式等格式的设置;工作表的插入、复制、移动、更名、保存等基本操作数据的排序、筛选、对比、分类汇总、数据合并计算﹑数据有效性检查; 湖北省《计算机类考纲百套卷》第53卷 《电子表格处理》 考试时间60分钟 满分100分 班级 姓名 学号 成绩 一、电子表格操作题(本大题共10小题,每题10分,共100分) 第1题 (1).打开人力资源综合数据.xlsx,将Sheet1中的A1:M21区域整体复制到Sheet2的A1起始位置,将Sheet2重命名为薪酬计算表,标签颜色设为橙色;将Sheet1重命名为员工档案,标签颜色设为蓝色。 (2).在员工档案工作表中,将标题行A1:M1的字体设为黑体、字号12、加粗、白色,填充色设为深蓝色;设置A2:M21区域的外边框为黑色双实线,内边框为灰色单实线;将A列(工号)的列宽设为8,B列(姓名)设为10,D列(部门)设为12,G列(绩效得分)设为10;冻结首行窗格。 (3).在员工档案工作表中,为C2:C21(性别)列设置数据有效性,限制只能输入男或女,输入提示为请选择性别:男或女,出错警告为性别只能为男或女;为L2:L21(学历)列设置数据有效性,下拉选项为博士、硕士、本科、大专、中专。 (4).在员工档案工作表中,将B2:B21区域命名为姓名列表,将G2:G21区域命名为绩效列表,将D2:D21区域命名为部门列表;保护整个工作表,允许用户选择未锁定单元格,密码设为protect123。 (5).在员工档案工作表中,使用条件格式:将绩效得分≥90的单元格填充浅绿色并加粗;将绩效得分<70的单元格填充浅红色并加粗斜体;将出勤天数<20的单元格添加红色边框;将缺勤天数≥3的单元格字体颜色设为红色。 (6).在员工档案工作表中,为G2:G21(绩效得分)列添加数据条(蓝色渐变),为H2:H21(出勤天数)列添加色阶(红-黄-绿三色),为I2:I21(缺勤天数)列添加图标集(三个圆形图标),保存文件。 第2题 (1).在员工档案工作表右侧(N列至Q列)分别插入四列新列,列标题依次为:绩效奖金岗位津贴工龄工资应发工资;使用相对引用公式绩效奖金=绩效得分×50计算每位员工的绩效奖金(N2:N21),使用VLOOKUP函数从Sheet2中查找对应岗位津贴(O2:O21)。 (2).在薪酬计算表工作表中,使用混合引用计算工龄工资(将Sheet1中的入职日期与当前日期2024-12-31的差额折算成年数,乘以从公司薪酬标准.xlsx中绝对引用的工龄津贴标准值$B$6),结果保留整数。 (3).在薪酬计算表工作表中,使用数组公式{=SUM((部门="技术部")*应发工资)}计算技术部的应发工资总和,结果存放于R2单元格;使用数组公式{=AVERAGE(IF(部门="市场部",绩效得分))}计算市场部的平均绩效得分,结果存放于R3单元格。 (4).在薪酬计算表工作表中,使用IF函数根据绩效得分评定等级(≥90为A,80-89为B,70-79为C,60-69为D,<60为E),结果存放于S2:S21;使用COUNTIFS函数统计各部门中绩效等级为A的人数,结果存放于T2:T5区域。 (5).在薪酬计算表工作表中,在U列插入实际到手工资,使用跨工作簿引用从公司薪酬标准.xlsx中引用社保比例(8%)和公积金比例(5%),计算公式为=应发工资×(1-社保比例-公积金比例)+全勤奖+交通补贴(全勤奖和交通补贴从外部文件绝对引用)。 (6).在薪酬计算表工作表中,使用RANK函数对实际到手工资进行降序排名(引用区域使用混合引用$U$2:$U$21),结果存放于V2:V21;使用LARGE函数找出实际到手工资前三名,存放于W2:W4;使用SMALL函数找出后三名,存放于X2:X4,保存文件。 第3题 (1).在员工档案工作表左侧插入一列(A列前),列标题为序号,使用填充柄从1到20填充;将员工档案工作表复制一份到Sheet3之前,重命名为备份-员工,标签颜色设为绿色,然后将备份-员工工作表隐藏。 (2).在员工档案工作表中,对数据按部门进行排序(自定义顺序:技术部、市场部、人事部、财务部),部门相同则按绩效得分从高到低排序,部门相同、绩效得分相同则按入职日期从早到晚排序。 (3).在员工档案工作表中,应用高级筛选:筛选条件为部门为技术部或市场部且绩效得分≥85,将筛选结果复制到以A23为起始位置的新区域。 (4).在员工档案工作表中,使用分类汇总功能按部门对基本工资进行平均值汇总,再对绩效得分进行最大值汇总,汇总结果显示在每组数据下方,并分页显示每个部门的汇总结果。 (5).在员工档案工作表中,取消分类汇总状态,使用合并计算功能(引用位置为A1:M21),以部门为标签位置,计算各部门的基本工资平均值、绩效得分平均值和出勤天数平均值,将结果存放于Sheet3的A1起始位置。 (6).在Sheet3中,将合并计算结果区域设置为货币格式保留2位小数,添加外边框为蓝色双实线,内边框为蓝色单实线;将A1:D1标题行填充为深灰色,字体设为白色加粗,保存文件。 第4题 (1).在员工档案工作表中,为E2:E21(岗位)列创建批注,内容为该岗位职级对应系数请参照Sheet2,批注框大小设为宽3厘米、高1.5厘米,批注字体设为宋体、字号9。 (2).在员工档案工作表中,使用条件格式将合同期限≥5年的单元格填充金色并加粗;将学历为硕士的单元格填充淡紫色,字体为蓝色;将培训次数≥4次的单元格添加绿色边框。 (3).在员工档案工作表中,创建两个命名区域:工资数据(包含F2:F21基本工资和N2:Q21应发工资相关列)和绩效数据(包含G2:G21绩效得分和S2:S21绩效等级);锁定工资数据区域,设置密码为salary2024,允许用户查看但不能修改。 (4).在薪酬计算表工作表中,使用SUMIFS函数计算技术部中工程师岗位的基本工资总和,结果存放于Z2单元格;使用AVERAGEIFS函数计算市场部中绩效得分≥80的员工平均基本工资,结果存放于Z3单元格。 (5).在薪酬计算表工作表中,使用MAXIFS函数查找人事部中最高绩效得分对应的员工姓名(需结合INDEX+MATCH),结果存放于Z4单元格;使用MINIFS函数查找财务部中最低基本工资对应的员工姓名,结果存放于Z5单元格。 (6).在薪酬计算表工作表中,将Z2:Z5区域命名为关键指标,设置该区域的数字格式为货币格式保留0位小数(姓名列除外),然后保护该命名区域,密码为key123,保存文件。 第5题 (1).在Sheet4中(新建工作表),基于员工档案工作表的A1:M21数据创建一个数据透视表,行标签为部门,列标签为学历,值字段为基本工资(平均值),将数据透视表放置于Sheet4的A1起始位置。 (2).在数据透视表中,添加绩效得分作为第二个值字段(汇总方式为最大值),将值字段名称分别修改为平均工资和最高绩效,并设置平均工资为货币格式保留0位小数。 (3).在数据透视表中,将学历列的顺序调整为:硕士、本科、大专;将行的总计和列的总计均显示,为数据透视表套用样式数据透视表样式中等深浅6。 (4).在Sheet4中,基于上述数据透视表插入一个数据透视图,图表类型为堆积柱形图,图表标题设为各部门各学历平均工资分布,图表样式设为样式7。 (5).在数据透视图中,添加数据标签(仅显示值),将图例位置设为底部,设置图表区填充为淡蓝色渐变,绘图区填充为白色,垂直轴主要单位设为5000。 (6).在数据透视图中,插入一个切片器(字段为性别),将切片器连接到数据透视图,设置切片器样式为切片器样式彩色4,列数为2列,将切片器放置于图表右侧,调整大小使整体布局协调,保存文件。 第6题 (1).在Sheet5中(新建工作表),基于员工档案工作表的B2:M21数据创建一个新的数据透视表,行标签为入职日期(按年份分组),列标签为部门,值字段为工号(计数),计算各部门各年度入职人数。 (2).在数据透视表中,将入职日期的分组更改为按季度分组(不按年),并重命名分组标签为Q1Q2Q3Q4;添加合同期限字段作为筛选器(报表筛选),仅显示合同期限≥5年的记录。 (3).在数据透视表中,添加培训次数字段作为第二个值字段(汇总方式为平均值),将值字段名称修改为平均培训次数,设置数字格式保留1位小数。 (4).在数据透视表中,设置报表布局为以表格形式显示,重复所有项目标签,显示分类汇总(仅显示总计),并将整个数据透视表区域套用样式数据透视表样式深色3。 (5).在Sheet5中,基于上述数据透视表插入两个图表:图表1为簇状柱形图(显示各部门各季度入职人数),图表2为折线图(显示各季度平均培训次数趋势),两个图表垂直排列。 (6).在图表1中,添加数据标签,设置图表标题为各部门季度入职人数统计;在图表2中,添加趋势线(线性),显示公式和R²值,设置图表标题为培训次数季度趋势分析,调整两个图表的大小和位置使其在页面中美观分布,保存文件。 第7题 (1).在员工档案工作表中,根据B1:B21(姓名)和F1:F21(基本工资)两列数据,插入一个条形图,图表标题设为员工基本工资排名,将图例隐藏,并将水平轴(基本工资)的主要单位设为2000,最小值为0,最大值为15000。 (2).在条形图中,将数据系列颜色设置为从蓝色到红色的渐变(根据数值大小自动变色),并添加数据标签显示在条形内部(右侧),标签字体设为宋体、字号8、白色。 (3).在条形图中,将垂直轴(姓名)的字体设为黑体、字号9,将水平轴的字体设为宋体、字号9,为图表添加背景(图表区填充设为淡绿色渐变,绘图区填充设为白色)。 (4).在员工档案工作表中,根据D1:D21(部门)和F1:F21(基本工资)两列数据,插入一个三维饼图(显示各部门基本工资总和占比),将技术部所在扇区分离15%,并将该扇区填充色设为深蓝色。 (5).在三维饼图中,添加百分比数据标签,显示类别名称和百分比,标签位置为数据标签外,字体设为黑体、字号10、加粗;将图表标题设为各部门工资占比分布,图例位置设为右下角。 (6).在员工档案工作表中,根据G1:G21(绩效得分)和N1:N21(绩效奖金)两列数据,插入一个散点图(显示绩效得分与绩效奖金的关系),添加趋势线(多项式趋势线,阶数为2),显示公式和R²值,图表标题设为绩效与奖金关系分析,保存文件。 第8题 (1).在Sheet6中(新建工作表),将Sheet6重命名为联动分析,在A1单元格中输入人力资源联动分析报告,合并A1:F1居中,字体设为黑体22加粗,背景色为深绿色,字体颜色为白色。 (2).在联动分析工作表中,使用跨表引用从员工档案中获取以下信息:A3为员工总数(使用COUNTA),B3为男性人数(使用COUNTIF),C3为女性人数,D3为平均绩效得分,E3为最高工资,F3为最低工资,并设置相应格式。 (3).在联动分析工作表中,从A5开始创建一个部门统计表,行标签为四个部门,分别使用SUMIFS和AVERAGEIFS从员工档案中引用各部门的基本工资总和、绩效得分平均值、出勤天数平均值、培训次数总和。 (4).在联动分析工作表中,从A10开始创建一个岗位分布表,行标签为Sheet2中的六个岗位,使用VLOOKUP+SUMIFS组合从员工档案和薪酬计算表中引用各岗位的人数、平均基本工资、平均应发工资。 (5).在联动分析工作表中,从A15开始创建一个绩效等级分布表,统计各部门中各绩效等级(A、B、C、D、E)的人数,使用COUNTIFS数组公式实现(各部门×各等级),添加百分比行显示各等级占比。 (6).在联动分析工作表中,将A3:F3、A5:D8、A10:D15、A15:F20四个区域分别添加边框和不同颜色的底纹(区域间用颜色区分),设置打印区域为A1:F25,页边距为适中,添加页眉公司人力资源部和页脚第&P页,保存文件。 第9题 (1).在Sheet7中(新建工作表),基于员工档案工作表的A1:M21数据创建一个数据透视表,同时使用部门作为行标签和列标签(行标签显示部门,列标签也显示部门),值字段为工号(计数),生成一个部门交叉计数表(即各部门之间的员工流动或对比矩阵)。 (2).在数据透视表中,使用计算字段功能添加一个新字段绩效系数,公式为=绩效得分/100,将该字段添加到值区域,汇总方式设为平均值;再添加一个计算字段工资占比,公式为=基本工资/SUM(基本工资)。 (3).在数据透视表中,插入一个日程表(时间线),字段为入职日期,设置日程表显示为季度层级,日程表样式为日程表样式深色2。 (4).在Sheet7中,基于上述数据透视表插入一个数据透视图,图表类型为百分比堆积条形图,图表标题设为各部门绩效系数与工资占比分析,图表样式设为样式10。 (5).在数据透视图中,将绩效系数系列的颜色设为橙色渐变,将工资占比系列的颜色设为蓝色渐变;添加数据标签(显示百分比),标签位置为数据标签内,字体设为白色、字号8。 (6).在数据透视图中,插入切片器(字段为学历),连接到数据透视图,设置切片器列数为3列;调整日程表、切片器和数据透视图的布局,使其在Sheet7中整齐排列,保存文件。 第10题 (1).打开人力资源综合数据.xlsx,将整个工作簿另存为人力资源综合分析报告.xlsx;在最后新建一个工作表,重命名为总览仪表板,标签颜色设为红色。 (2).在总览仪表板工作表中,使用跨表引用从薪酬计算表中引用实际到手工资的总和、平均值、最大值、最小值,放置于A1:D2区域,设置货币格式保留0位小数,添加金色底纹。 (3).在总览仪表板工作表中,创建三个图表(均基于员工档案数据):左上角放置各部门人数分布饼图,右上角放置各学历人数柱形图,下方中间放置各月入职人数折线图(按入职日期月份统计)。 (4).在总览仪表板工作表中,插入四个切片器,分别连接三个图表,字段为部门性别学历岗位,四个切片器水平排列于图表上方。 (5).在总览仪表板工作表中,添加一个文本框,内容为2024年度人力资源综合报告,字体设为黑体30加粗,颜色为深蓝色,居中放置于页面顶部;在页面底部添加三个形状按钮:刷新数据导出PDF打印预览。 (6).在总览仪表板工作表中,统一美化所有图表:图表区边框均为深蓝色实线(1磅),字体统一为微软雅黑,图表标题字号14加粗;设置所有切片器样式为切片器样式彩色2;调整整体页面布局,确保打印预览时所有内容在一页内完整显示,保存并关闭所有文件。 原创精品资源学科网独家享有版权,侵权必究！ 学科网(北京)股份有限公司 学科网(北京)股份有限公司 学科网（北京）股份有限公司 $