2025年全省残疾人岗位精英职业技能选拔赛计算机操作员(数据处理)Excel操作试题及答案

2025年全省残疾人岗位精英职业技能选拔赛计算机操作员(数据处理)Excel操作试题及答案一、单项实操（共4题，每题8分，合计32分）1.动态汇总在“1_销售明细.xlsx”中，A:H列为2024全年订单，字段依次是“订单ID、区域、省份、城市、产品、单价、数量、折扣”。要求：(1)在新建工作表“汇总”中，使用数据透视表，按“区域+产品”双维度统计“折后销售额”合计（折后销售额=单价数量(1-折扣)）。(2)添加切片器“区域”，样式采用“浅橙-中等着色2”，列数3，按钮高度0.8cm。(3)设置透视表“值显示方式”为“占同列总计百分比”，保留2位小数。(4)透视表名称修改为“ptSales”，整个透视表不允许出现“汇总”字样。2.智能填充打开“2_员工信息.xlsx”，A列含不规则合并单元格，如“A2:A4”合并后写“市场部”，A5:A7写“研发部”。要求：(1)取消所有合并，并在B列用PowerQuery填充空值，使B2:B1000形成连续部门列。(2)在C列添加自定义列“部门编号”，规则：市场部=100，研发部=200，销售部=300，其它=900。(3)将查询加载为新工作表“qryStaff”，保留连接，文件体积不得增加>1MB。3.条件格式在“3_成绩表.xlsx”中，A1:F501为学员成绩，F列“总分”已存在。要求：(1)对F2:F501设置“数据条”，颜色“渐变蓝”，仅显示条不显示数值。(2)对B2:E501设置“三色刻度”，阈值按百分位：0-33%红，34-67%黄，68-100%绿。(3)新建规则：若某行总分<240，则整行字体加粗且字体颜色为“主题色5-深色25%”。(4)所有格式必须应用“整列”而非“选定区域”，确保后续追加数据自动继承。4.高级筛选在“4_采购流水.xlsx”中，字段“日期、供应商、品名、数量、金额”。要求：(1)在空白区域建立条件区域，筛选出“2024年第二季度且金额≥5000”的记录。(2)将结果输出到新建工作表“FilterResult”，保留列顺序，并在G列新增公式计算“月均额”（金额/3）。(3)条件区域需采用“公式条件”，不得使用辅助列。(4)设置“FilterResult”工作表保护，密码“123”，仅允许用户“选定锁定单元格”。二、综合案例（共1题，28分）5.薪酬自动核算系统背景：“5_薪酬原始.xlsx”包含“基本信息、考勤、绩效、社保公积金”四张表。表1基本信息：员工ID、姓名、级别、基础工资、补贴、银行卡号；表2考勤：员工ID、迟到次数、早退次数、病假天数、事假天数；表3绩效：员工ID、绩效系数（0.8-1.5）；表4社保公积金：员工ID、个人养老、个人医疗、个人公积金、单位养老、单位医疗、单位公积金。要求：(1)在“薪酬核算”工作表建立全自动薪酬计算模型，列依次：员工ID、姓名、基础工资、补贴、迟到扣款、早退扣款、病假扣款、事假扣款、绩效奖金、应发工资、社保合计、公积金合计、个税、实发工资。(2)规则：a.迟到扣款=MIN(迟到次数50,基础工资5%)；b.早退扣款=早退次数60；c.病假扣款=病假天数日工资，日工资=基础工资/21.75；d.事假扣款=事假天数日工资2；e.绩效奖金=基础工资(绩效系数-1)，负值表示扣减；f.应发工资=基础工资+补贴-迟到扣款-早退扣款-病假扣款-事假扣款+绩效奖金；g.个税按2024年累计预扣法，起征点5000，使用“LET+LAMBDA”自定义函数“Tax2024”，参数为累计应发工资、累计三险一金、累计专项附加扣除（统一按1000模拟）。(3)使用“数据模型”将四张表建立关系，确保新增员工可一键刷新。(4)在“薪酬核算”表右侧放置数据透视表，统计“级别”维度的平均实发工资，并插入“切片器”选择“级别”。(5)设置“薪酬核算”表样式为“表样式中等深浅11”，并启用“总行”，显示平均实发工资。(6)保存为“5_最终.xlsx”，启用“打开文件时自动刷新所有透视表”。三、函数与图表（共2题，每题10分，合计20分）6.动态数组在“6_库存.xlsx”中，A列“产品”，B列“仓库”，C列“库存量”。要求：(1)在E1单元格输入公式，使用“UNIQUE+SORT”生成不重复产品列表，纵向溢出。(2)在F1单元格输入“=TRANSPOSE(SORT(UNIQUE(B:B)))”，横向溢出仓库列表。(3)在E2单元格使用“MAKEARRAY”构造动态矩阵，行列交叉返回对应库存，若无库存返回0。(4)对矩阵设置“条件格式-色阶”，颜色随库存量渐变，最小值绿色，最大值红色。(5)在E1右下角放置“切片器”连接原始数据，实现产品筛选后矩阵自动收缩。7.高级图表在“7_预算执行.xlsx”中，A:D列为“部门、预算、实际、执行率”。要求：(1)创建“部门”切片器，选择后图表联动。(2)使用“组合图”：预算与实际为“簇状柱形”，执行率为“折线”，折线使用“次坐标轴”。(3)折线数据标签仅显示执行率<90%的点，标签形状“椭圆”，填充“红色-强调文字颜色2”。(4)柱形使用“渐变色-预设彩虹”，间隙宽度30%，系列重叠-10%。(5)图表标题引用单元格“=ChartTitle!1”，B1内容随切片器变化自动更新为“XX部门预算执行情况”。(6)将图表放置在“Dashboard”工作表，设置背景“画布纹理”，并导出为“7_图表.pdf”，嵌入书签。四、宏与自动化（共1题，20分）8.一键报表需求：某基金会每月需汇总全省残疾人培训数据，文件结构固定，但每月新增一个工作表，命名“2025MM”。要求：(1)在“8_模板.xlsm”中编写VBA宏“BatchReport”，功能：a.遍历当前工作簿所有以“2025”开头的工作表；b.将每个表的A1:G100区域合并到“汇总”表，追加方式，首行仅保留一次；c.在“汇总”表H列添加“来源月份”，使用工作表名称；d.对“汇总”表使用“高级布局”：自动调整列宽、添加“表样式浅色17”、冻结首行；e.生成数据透视表“ptMacro”，按“来源月份”统计“培训人数”合计；f.将透视表放置于新工作表“分析”，并插入“月份”切片器；g.全程禁用屏幕刷新，设置状态栏进度，完成后弹出“ReportDone”提示。(2)宏需兼容简体中文Excel2016及以上，错误处理需包含“Err_H:MsgBoxErr.Description”。(3)在“汇总”表B1添加按钮“运行报表”，指定宏“BatchReport”，按钮文字“一键更新”。(4)文件保存为“8_最终.xlsm”，移除个人信息，压缩后体积<500KB。————————参考答案————————【题1】1.插入→数据透视表→选择“销售明细”A:H→放置位置：“汇总”A3。2.拖拽“区域”至行，“产品”至列，“折后销售额”至值。3.在“值字段设置”→“值显示方式”→“占同列总计百分比”。4.分析→插入切片器→“区域”→样式选“浅橙-中等着色2”，列数3，按钮高度0.8cm。5.在数据透视表分析→“透视表名称”输入ptSales；设计→“总计”→“关闭行总计”。【题2】1.数据→获取数据→自工作簿→选择当前文件→选择“员工信息”表→打开PowerQuery。2.主页→“取消合并单元格”→选中A列→“填充”→“向下”。3.添加列→“自定义列”→公式：=if[部门]="市场部"then100elseif[部门]="研发部"then200elseif[部门]="销售部"then300else9004.关闭并加载至→“新工作表”→命名为qryStaff→勾选“保留连接”。【题3】1.选中F列→开始→条件格式→数据条→渐变蓝→勾选“仅显示数据条”。2.选中B:E→条件格式→三色刻度→设置0-33%红色，34-67%黄色，68-100%绿色。3.开始→条件格式→新建规则→“使用公式”→输入：=A:$F。【题4】1.在空白区域如K1:L2建立条件区域：K1留空，K2输入公式：=AND(YEAR(A2)=2024,MONTH(A2)>=4,MONTH(A2)<=6,A2>=5000)。2.数据→高级→列表区域A:E，条件区域K1:L2，复制到“FilterResult”A1。3.在G1输入“月均额”，G2公式=E2/3，双击填充。4.审阅→保护工作表→密码123→勾选“选定锁定单元格”。【题5】1.在“薪酬核算”A1输入列标题，A2公式：=UNIQUE(基本信息[员工ID])，溢出。2.B2=XLOOKUP(A2,基本信息[员工ID],基本信息[姓名])，其余列依次使用XLOOKUP+公式。3.日工资=LET(basic,XLOOKUP(A2,基本信息[员工ID],基本信息[基础工资]),basic/21.75)。4.个税lambda：Tax2024=LAMBDA(income,insurance,deduct,LET(taxable,income-insurance-deduct-5000,IF(taxable<=0,0,LET(rate,IFS(taxable<=36000,0.03,taxable<=144000,0.1,taxable<=300000,0.2,taxable<=420000,0.25,taxable<=660000,0.3,taxable<=960000,0.35,1,0.45),quick,IFS(taxable<=36000,0,taxable<=144000,2520,taxable<=300000,16920,taxable<=420000,31920,taxable<=660000,52920,taxable<=960000,85920,1,181920),MAX(taxablerate-quick,0))));5.在“个税”列调用：=Tax2024(SUMIF(A:A,A2,应发工资),SUMIF(A:A,A2,社保合计)+SUMIF(A:A,A2,公积金合计),1000)。6.实发工资=应发工资-社保合计-公积金合计-个税。7.插入透视表→数据模型→选择“薪酬核算”→行：级别，值：平均实发工资。8.文件→选项→信任中心→宏设置→启用所有宏→保存为5_最终.xlsx。【题6】E1=UNIQUE(SORT(FILTER(A:A,A:A<>"")))F1=TRANSPOSE(SORT(UNIQUE(FILTER(B:B,B:B<>""))))E2=MAKEARRAY(ROWS(E1),COLUMNS(F1),LAMBDA(r,c,LET(p,INDEX(E1,r),w,INDEX(F1,c),SUMIFS(C:C,A:A,p,B:B,w)))【题7】1.插入切片器→“部门”。2.插入→组合图→簇状柱形+折线→折线次坐标。3.选中折线→格式→数据标签→“值来自单元格”→条件<90%列表→设置形状椭圆，填充红色。4.柱形→格式→形状填充→渐变→预设彩虹→系列重叠-10%，间隙宽度30%。5.公式→定义名称ChartTitle=REPT(Sheet1!B1&"部门预算执行情况",1)。6.图表标题→公式栏输入=ChartTitle。7.导出→创建PDF/XPS→选项→发布整个工作簿→勾选“创建书签时使用标题”。【题8】VBA参考：SubBatchReport()Application.ScreenUpdating=FalseOnErrorGoToErr_HDimwsAsWorksheet,destAsWorksheet,ptAsPivotTable,lastRowAsLongSetdest=ThisWorkbook.Worksheets("汇总")dest.Cells.ClearDimheaderCopiedAsBoolean:headerCopied=FalseForEachwsInThisWorkbook.WorksheetsIfws.NameLike"2025"ThenDimsrcAsRange:Setsrc=ws.Range("A1").CurrentRegionIfNotheaderCopiedThensrc.Rows(1).Copydest.Range("A1")dest.Range("A1").EntireRow.Copydest.Range("A2").PasteSpecialxlPasteValuesheaderCopied=TrueEndIfDimdataAsRange:Setdata=src.Offset(1,0).Resize(src.Rows.Count1)lastRow=dest.Cells(dest.Rows.Count,1).End(xlUp).Row+1data.Copydest.Cells(lastRow,1)dest.Range("H"&lastRow&":H"&lastRow+data.Rows.Count1).Value=ws.NameEndIfNextwsdest.Columns.AutoFitdest.ListObjects.Add(xlSrcRange,dest.UsedRange,,xlYes).Name="tblSummary"dest.ListObjects("tblSummary").TableStyle="TableStyleLight17"dest.Rows(1).EntireRow.AutoFilterdest.Range("A2").SelectActiveWindow.FreezePanes=TrueDimpcacheAsPivotCacheSetpcache=ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase,SourceData:=dest.Range("tblSummary"))DimpwsAsWorksheetO