任务2：公式与函数应用《电子表格》45分钟专题训练

湖北省技能高考《电子表格》45分钟专题训练专辑,共3份试卷。试卷命题与高考紧密相关,精选具有代表性和典型性的高考常考题。旨在帮助学生系统地复习和巩固计算机类理论知识,熟悉高考题型和命题规律,提高解题能力和应试技巧,为高考取得优异成绩打下坚实的基础,本训练卷适用于即将参加计算机类高考的学生。与本专辑配套的还有湖北省技能高考《操作系统的使用》《计算机网络应用》《文字处理》《电子表格处理》《演示文稿制作》《程序设计》《数据库应用》《图像处理》45分钟专题训练专辑,欢迎同学和老师们下载使用。 《电子表格》 项目二: 公式与函数应用 时间:45分钟 总分:100分 班级___________ 姓名__________ 学号__________ 成绩________ 一、演示文稿(共5小题,每小题20分,共100分) 第1题 1).在D2单元格使用相对引用计算提成金额(=销售额×提成比例),然后向下填充到D6 2).在F2单元格使用混合引用(固定提成比例基准单元格如C1)重新计算提成金额,验证结果一致 3).使用绝对引用,在G2单元格计算实发提成(=提成金额+排名奖),排名奖在E列,向下填充 4).在H列使用数组公式(Ctrl+Shift+Enter)计算:如果销售额≥10000,提成比例按0.1计算,否则按0.08计算,一次性返回所有结果 5).使用SUMIF函数计算销售额≥10000的总提成金额 6).使用AVERAGEIFS函数计算销售额≥8000且≤20000的平均销售额 答案: 1).选中D2单元格 → 输入=B2*C2 → 回车 → 选中D2 → 双击右下角填充柄向下填充到D6 2).选中F2单元格 → 输入=B2*$C$1 → 回车 → 双击填充柄向下填充到F6 → 与D列结果对比验证一致 3).选中G2单元格 → 输入=D2+E2 → 回车 → 双击填充柄向下填充到G6 4).选中H2:H6单元格区域 → 输入=IF(B2:B6>=10000,0.1,0.08)*B2:B6 → 按Ctrl+Shift+Enter → 公式两端自动出现{ }大括号 5).选中任意空白单元格 → 输入=SUMIF(B2:B6,">=10000",B2:B6*C2:C6) → 按Ctrl+Shift+Enter → 或输入=SUMPRODUCT((B2:B6>=10000)*B2:B6*C2:C6) 6).选中任意空白单元格 → 输入=AVERAGEIFS(B2:B6,B2:B6,">=8000",B2:B6,"<=20000") → 回车 第2题 1).在新建工作表工资汇总中,使用跨工作表引用,从基础工资表中引入基本工资和岗位工资 2).使用VLOOKUP函数(跨工作表)根据工号从绩效表引入绩效工资和扣款 3).计算应发工资(=基本工资+岗位工资+绩效工资-扣款),使用混合引用锁定工资汇总表的固定单元格(如个税起征点B10) 4).使用SUMIF跨工作表汇总所有员工基本工资总额 5).使用COUNTIFS跨工作表统计绩效工资≥2000的人数 6).使用三维引用(工作表组引用)计算多个工作表相同位置单元格的和 答案: 1).新建“工资汇总”工作表 → 选中A1单元格 → 输入=基础工资表!A2 → 向右拖动填充到D列 → 选中A1:D4 → 双击填充柄 2).选中E2单元格 → 输入=VLOOKUP($A2,绩效表!$A$2:$D$5,COLUMN(B1),FALSE) → 向右拖动复制到F2 → 双击填充柄向下填充 3).选中G2单元格 → 输入=B2+C2+E2-F2-$B$10 → 回车 → 双击填充柄向下填充 → B10单元格输入“5000”作为个税起征点 4).选中任意空白单元格 → 输入=SUMIF(基础工资表!A:A,"<>",基础工资表!C:C) → 回车 5).选中任意空白单元格 → 输入=COUNTIFS(绩效表!C:C,">=2000") → 回车 6).选中任意空白单元格 → 输入=SUM(基础工资表:绩效表!C2) → 回车 → 注意两边工作表名称需连续 第3题 1).在新工作簿对账表.xlsx中,使用跨工作簿引用从销售明细.xlsx引入订单号和金额 2).使用VLOOKUP跨工作簿从回款明细.xlsx引入回款金额 3).计算差异金额(=金额-回款金额),使用混合引用固定列(如金额列B2) 4).使用IFERROR函数处理找不到订单号的情况,显示未回款 5).使用SUMIFS跨工作簿统计指定销售员的总销售金额 6).使用数组公式跨工作簿计算总回款率(回款总额/销售总额) 答案: 1).新建“对账表.xlsx”并打开 → 选中A2单元格 → 输入=[销售明细.xlsx]Sheet1!$A$2 → 向右拖动复制到B2 → 选中A2:B6 → 双击填充柄 2).选中C2单元格 → 输入=VLOOKUP(A2,[回款明细.xlsx]Sheet1!$A$2:$B$6,2,FALSE) → 回车 → 双击填充柄向下填充 3).选中D2单元格 → 输入=B2-C2 → 回车 → 双击填充柄向下填充 → 如需固定B列：输入=B2-$B$2（根据实际需求） 4).选中C2单元格 → 修改公式为=IFERROR(VLOOKUP(A2,[回款明细.xlsx]Sheet1!$A$2:$B$6,2,FALSE),"未回款") → 双击填充柄 5).选中任意空白单元格 → 输入=SUMIFS([销售明细.xlsx]Sheet1!$B$2:$B$6,[销售明细.xlsx]Sheet1!$C$2:$C$6,"张三") → 回车 6).选中任意空白单元格 → 输入=SUM([回款明细.xlsx]Sheet1!B2:B6)/SUM([销售明细.xlsx]Sheet1!B2:B6) → 按Ctrl+Shift+Enter 第4题 1).计算期末库存(=期初库存+入库数量-出库数量),使用相对引用填充 2).使用IF函数判断是否需要补货:如果期末库存<安全库存,显示需要补货,否则显示充足 3).使用SUMIFS计算产品A和C的总出库数量 4).使用数组公式(Ctrl+Shift+Enter)计算所有产品中期末库存大于100的总数量 5).使用LARGE函数找出期末库存第2大的值 6).使用INDEX+MATCH组合(混合引用)根据产品名称查找对应的期初库存 答案: 1).选中F2单元格 → 输入=B2+C2-D2 → 回车 → 双击填充柄向下填充到F7 2).选中G2单元格 → 输入=IF(F2<E2,"需要补货","充足") → 回车 → 双击填充柄向下填充到G7 3).选中任意空白单元格 → 输入=SUMIFS(D2:D7,A2:A7,"A",A2:A7,"C") → 注意：同一列不能同时等于A和C → 正确写法：=SUMIF(D2:D7,"A",D2:D7)+SUMIF(D2:D7,"C",D2:D7) 4).选中任意空白单元格 → 输入=SUM(IF(F2:F7>100,1,0)) → 按Ctrl+Shift+Enter 5).选中任意空白单元格 → 输入=LARGE(F2:F7,2) → 回车 6).选中任意空白单元格 → 输入=INDEX(B2:B7,MATCH("C",A2:A7,0)) → 回车 → “C”可替换为单元格引用 第5题 1).在E列计算完成率(=销售额/100000),使用混合引用锁定目标销售额单元格B10 2).使用IFS函数(或嵌套IF)根据完成率自动判定奖金系数:≥1.2为2.0,≥1.0为1.5,≥0.8为1.2,<0.8为0.8 3).计算最终奖金(=基础奖金基数5000 × 完成率 × 奖金系数),基础奖金使用绝对引用 4).使用SUMPRODUCT数组公式计算一部和二部的总奖金 5).使用INDIRECT函数实现动态工作表引用,根据单元格内容(如一部)引用对应工作表中的数据 6).使用CHOOSE+MATCH组合实现水平查找,根据排名返回对应等级(金牌/银牌/铜牌) 答案: 1).选中E2单元格 → 输入=C2/$B$10 → 回车 → 双击填充柄向下填充 → B10单元格输入“100000” 2).选中F2单元格 → 输入=IFS(E2>=1.2,2,E2>=1,1.5,E2>=0.8,1.2,TRUE,0.8) → 回车 → 双击填充柄向下填充 3). 选中G2单元格 → 输入=$B$11*E2*F2 → 回车 → 双击填充柄向下填充 → B11单元格输入“5000” 4). 选中任意空白单元格 → 输入=SUMPRODUCT((B2:B7="一部")*G2:G7)+SUMPRODUCT((B2:B7="二部")*G2:G7) → 回车 5). 选中任意空白单元格 → 输入=INDIRECT(A1&"!B2") → 回车 → 假设A1单元格输入工作表名称如“一部” 6). 选中任意空白单元格 → 输入=CHOOSE(MATCH(A1,{1,2,3},0),"金牌","银牌","铜牌") → 回车 → A1单元格输入排名数字1/2/3 原创精品资源学科网独家享有版权,侵权必究！ 学科网（北京）股份有限公司 回款明细(题三).xlsx Sheet1 订单号 回款金额 回款日期 1 5000 5/1/25 2 3000 5/2/25 3 7800 5/3/25 5 6100 5/5/25 基础工资表(题二).xlsx 基础工资表(题二) 工号 姓名 基本工资 岗位工资 101 张三 5000 1500 102 李四 4500 1200 103 王五 5200 1800 104 赵六 4800 1300 库存表(题四).xlsx Sheet1 产品 期初库存 入库数量 出库数量 安全库存 是否需要补货 A 100 50 80 30 B 200 30 150 50 C 50 100 40 60 D 300 20 250 80 E 150 80 120 40 F 80 60 30 50 绩效表(题二).xlsx Sheet1 工号 姓名 绩效工资 扣款 101 张三 2000 300 102 李四 1500 200 103 王五 2500 400 104 赵六 1800 150 销售提成表(题一).xlsx 销售提成表(题一) 姓名 销售额 提成比例 提成金额 排名奖 实发提成 张三 12000 0.08 500 李四 8500 0.08 0 王五 20000 0.1 1000 赵六 7500 0.08 0 周七 18000 0.1 500 销售明细(题三).xlsx Sheet1 订单号 金额 销售员 1 5000 张三 2 3200 李四 3 7800 王五 4 4500 张三 5 6100 赵六 销售表(题五).xlsx Sheet1 销售员 部门 销售额 完成率 奖金系数 最终奖金 张三 一部 120000 李四 二部 80000 王五 一部 150000 赵六 二部 95000 周七 三部 60000 钱八 一部 180000 $ 湖北省技能高考《电子表格》45分钟专题训练专辑,共3份试卷。试卷命题与高考紧密相关,精选具有代表性和典型性的高考常考题。旨在帮助学生系统地复习和巩固计算机类理论知识,熟悉高考题型和命题规律,提高解题能力和应试技巧,为高考取得优异成绩打下坚实的基础,本训练卷适用于即将参加计算机类高考的学生。与本专辑配套的还有湖北省技能高考《操作系统的使用》《计算机网络应用》《文字处理》《电子表格处理》《演示文稿制作》《程序设计》《数据库应用》《图像处理》45分钟专题训练专辑,欢迎同学和老师们下载使用。 《电子表格》 项目二: 公式与函数应用 时间:45分钟 总分:100分 班级___________ 姓名__________ 学号__________ 成绩________ 一、演示文稿(共5小题,每小题20分,共100分) 第1题 1).在D2单元格使用相对引用计算提成金额(=销售额×提成比例),然后向下填充到D6 2).在F2单元格使用混合引用(固定提成比例基准单元格如C1)重新计算提成金额,验证结果一致 3).使用绝对引用,在G2单元格计算实发提成(=提成金额+排名奖),排名奖在E列,向下填充 4).在H列使用数组公式(Ctrl+Shift+Enter)计算:如果销售额≥10000,提成比例按0.1计算,否则按0.08计算,一次性返回所有结果 5).使用SUMIF函数计算销售额≥10000的总提成金额 6).使用AVERAGEIFS函数计算销售额≥8000且≤20000的平均销售额 第2题 1).在新建工作表工资汇总中,使用跨工作表引用,从基础工资表中引入基本工资和岗位工资 2).使用VLOOKUP函数(跨工作表)根据工号从绩效表引入绩效工资和扣款 3).计算应发工资(=基本工资+岗位工资+绩效工资-扣款),使用混合引用锁定工资汇总表的固定单元格(如个税起征点B10) 4).使用SUMIF跨工作表汇总所有员工基本工资总额 5).使用COUNTIFS跨工作表统计绩效工资≥2000的人数 6).使用三维引用(工作表组引用)计算多个工作表相同位置单元格的和 第3题 1).在新工作簿对账表.xlsx中,使用跨工作簿引用从销售明细.xlsx引入订单号和金额 2).使用VLOOKUP跨工作簿从回款明细.xlsx引入回款金额 3).计算差异金额(=金额-回款金额),使用混合引用固定列(如金额列B2) 4).使用IFERROR函数处理找不到订单号的情况,显示未回款 5).使用SUMIFS跨工作簿统计指定销售员的总销售金额 6).使用数组公式跨工作簿计算总回款率(回款总额/销售总额) 第4题 1).计算期末库存(=期初库存+入库数量-出库数量),使用相对引用填充 2).使用IF函数判断是否需要补货:如果期末库存<安全库存,显示需要补货,否则显示充足 3).使用SUMIFS计算产品A和C的总出库数量 4).使用数组公式(Ctrl+Shift+Enter)计算所有产品中期末库存大于100的总数量 5).使用LARGE函数找出期末库存第2大的值 6).使用INDEX+MATCH组合(混合引用)根据产品名称查找对应的期初库存 第5题 1).在E列计算完成率(=销售额/100000),使用混合引用锁定目标销售额单元格B10 2).使用IFS函数(或嵌套IF)根据完成率自动判定奖金系数:≥1.2为2.0,≥1.0为1.5,≥0.8为1.2,<0.8为0.8 3).计算最终奖金(=基础奖金基数5000 × 完成率 × 奖金系数),基础奖金使用绝对引用 4).使用SUMPRODUCT数组公式计算一部和二部的总奖金 5).使用INDIRECT函数实现动态工作表引用,根据单元格内容(如一部)引用对应工作表中的数据 6).使用CHOOSE+MATCH组合实现水平查找,根据排名返回对应等级(金牌/银牌/铜牌) 原创精品资源学科网独家享有版权,侵权必究！ 学科网（北京）股份有限公司 回款明细(题三).xlsx Sheet1 订单号 回款金额 回款日期 1 5000 5/1/25 2 3000 5/2/25 3 7800 5/3/25 5 6100 5/5/25 基础工资表(题二).xlsx 基础工资表(题二) 工号 姓名 基本工资 岗位工资 101 张三 5000 1500 102 李四 4500 1200 103 王五 5200 1800 104 赵六 4800 1300 库存表(题四).xlsx Sheet1 产品 期初库存 入库数量 出库数量 安全库存 是否需要补货 A 100 50 80 30 B 200 30 150 50 C 50 100 40 60 D 300 20 250 80 E 150 80 120 40 F 80 60 30 50 绩效表(题二).xlsx Sheet1 工号 姓名 绩效工资 扣款 101 张三 2000 300 102 李四 1500 200 103 王五 2500 400 104 赵六 1800 150 销售提成表(题一).xlsx 销售提成表(题一) 姓名 销售额 提成比例 提成金额 排名奖 实发提成 张三 12000 0.08 500 李四 8500 0.08 0 王五 20000 0.1 1000 赵六 7500 0.08 0 周七 18000 0.1 500 销售明细(题三).xlsx Sheet1 订单号 金额 销售员 1 5000 张三 2 3200 李四 3 7800 王五 4 4500 张三 5 6100 赵六 销售表(题五).xlsx Sheet1 销售员 部门 销售额 完成率 奖金系数 最终奖金 张三 一部 120000 李四 二部 80000 王五 一部 150000 赵六 二部 95000 周七 三部 60000 钱八 一部 180000 $