任务3：图表与数据透视表《电子表格》45分钟专题训练

湖北省技能高考《电子表格》45分钟专题训练专辑,共3份试卷试卷命题与高考紧密相关,精选具有代表性和典型性的高考常考题旨在帮助学生系统地复习和巩固计算机类理论知识,熟悉高考题型和命题规律,提高解题能力和应试技巧,为高考取得优异成绩打下坚实的基础,本训练卷适用于即将参加计算机类高考的学生与本专辑配套的还有湖北省技能高考《操作系统的使用》《计算机网络应用》《文字处理》《电子表格处理》《演示文稿制作》《程序设计》《数据库应用》《图像处理》45分钟专题训练专辑,欢迎同学和老师们下载使用 《电子表格》 项目三: 图表与数据透视表 时间:45分钟 总分:100分 班级___________ 姓名__________ 学号__________ 成绩________ 一、演示文稿(共5小题,每小题20分,共100分) 第1题 1).对数据进行多级排序：主要关键字区域升序,次要关键字销售额降序 2).使用自动筛选,筛选出区域为华东且销售额大于100000的记录 3).使用高级筛选,将产品类别为手机或平板且订单数≥30的数据复制到新位置 4).对区域进行分类汇总,计算每个区域的销售额总和与订单数平均值 5).删除分类汇总,使用SUMIFS函数分别计算每个区域的总销售额 6).使用数据验证(有效性),限制产品类别列只能输入手机、电脑、平板三种值,并设置出错警告 答案: 1).选中A1:F11区域 → 数据选项卡 → 排序 → 主要关键字选“区域” → 次序选“升序” → 添加条件 → 次要关键字选“销售额” → 次序选“降序” → 确定 2).选中A1:F11区域 → 数据选项卡 → 筛选 → 点击“区域”下拉箭头 → 取消全选 → 勾选“华东” → 确定 → 点击“销售额”下拉箭头 → 数字筛选 → 大于 → 输入100000 → 确定 3).在空白区域设置条件区域：I1输入“产品类别”，I2输入“手机”，J1输入“产品类别”，J2输入“平板”，K1输入“订单数”，K2输入“>=30” → 数据选项卡 → 高级 → 列表区域选A1:F11 → 条件区域选I1:K2 → 勾选“将筛选结果复制到其他位置” → 复制到选L1 → 确定 4).先按“区域”排序 → 选中A1:F11 → 数据选项卡 → 分类汇总 → 分类字段选“区域” → 汇总方式选“求和” → 选定汇总项勾选“销售额” → 确定 → 再次分类汇总 → 汇总方式选“平均值” → 选定汇总项勾选“订单数” → 取消勾选“替换当前分类汇总” → 确定 5).选中数据区域 → 数据选项卡 → 分类汇总 → 全部删除 → 在空白单元格输入：华东总销售额=SUMIFS(B2:B11,A2:A11,"华东") → 华南总销售额=SUMIFS(B2:B11,A2:A11,"华南") → 华北总销售额=SUMIFS(B2:B11,A2:A11,"华北") → 西南总销售额=SUMIFS(B2:B11,A2:A11,"西南") 6).选中D列（产品类别列） → 数据选项卡 → 数据验证 → 设置 → 允许选“序列” → 来源输入手机,电脑,平板 → 出错警告 → 勾选“输入无效数据时显示出错警告” → 样式选“停止” → 标题输入“输入错误” → 错误信息输入“只能输入手机、电脑或平板” → 确定 第2题 1).使用合并计算功能,将上半年和下半年两个工作表的手机产品销量按求和方式合并到新工作表 2).创建新工作表年度对比,使用跨工作表公式引用计算每种产品下半年的总销量 3).使用条件格式,将手机销量中环比增长超过10%的月份标记为绿色背景 4).创建柱形图,对比三种产品上半年和下半年的总销量,添加数据标签 5).使用MATCH+INDEX函数,找出平板销量最高的月份及其销量 6).使用数据验证创建下拉列表,选择产品名称后自动显示该产品全年销量走势图 答案: 1).新建工作表 → 数据选项卡 → 合并计算 → 函数选“求和” → 引用位置选“上半年”工作表A1:C4 → 添加 → 引用位置选“下半年”工作表A1:C4 → 添加 → 标签位置勾选“首行”和“最左列” → 确定 2).新建工作表命名“年度对比” → A1输入“产品”，B1输入“下半年总销量” → A2输入“手机”，A3输入“电脑”，A4输入“平板” → B2输入=SUM(下半年!B2:G2) → B3输入=SUM(下半年!B3:G3) → B4输入=SUM(下半年!B4:G4) 3).在“上半年”工作表E2输入环比增长率=(D2-C2)/C2 → 选中E2:E7 → 开始 → 条件格式 → 新建规则 → 使用公式确定要设置格式的单元格 → 输入=E2>0.1 → 格式 → 填充选绿色 → 确定 4).准备对比数据：产品列、上半年总和列、下半年总和列 → 选中数据 → 插入选项卡 → 柱形图 → 簇状柱形图 → 右键图表 → 添加数据标签 → 图表工具 → 设计 → 切换行/列 5).在空白单元格输入：月份=INDEX({"1月","2月","3月","4月","5月","6月"},MATCH(MAX(下半年!4:4),下半年!4:4,0)) → 销量=MAX(下半年!4:4) 6).选中H1单元格 → 数据验证 → 允许选“序列” → 来源输入手机,电脑,平板 → 确定 → 定义名称“产品数据”=OFFSET(上半年!A1,MATCH(看板!H1,上半年!A:A,0)-1,1,1,6) → 创建图表引用该名称 第3题 1).创建数据透视表,将区域拖至行区域,产品拖至列区域,销售额拖至值区域(求和) 2).在数据透视表中添加计算字段平均单价,公式：=销售额/数量,并设置为货币格式 3).对数据透视表中的销售额进行值字段设置,同时显示求和和平均值 4).创建数据透视图(簇状柱形图),展示各区域不同产品的销售额对比 5).使用切片器同时控制数据透视表和数据透视图,切片器字段为销售员 6).使用GETPIVOTDATA函数,在透视表外提取华东区域手机产品的销售额 答案: 1).选中A1:G13 → 插入选项卡 → 数据透视表 → 位置选新工作表或现有工作表 → 确定 → 将“区域”拖到行区域 → 将“产品”拖到列区域 → 将“销售额”拖到值区域 2).选中数据透视表 → 数据透视表分析选项卡 → 字段、项目和集 → 计算字段 → 名称输入“平均单价” → 公式输入=销售额/数量 → 确定 → 选中平均单价列 → Ctrl+1 → 货币 → 选择￥ → 确定 3).右键点击值区域的“求和项:销售额” → 值字段设置 → 计算类型选“平均值” → 确定 → 再拖入一次“销售额”字段到值区域 → 右键设置为“求和” 4).选中数据透视表任意单元格 → 插入选项卡 → 数据透视图 → 柱形图 → 簇状柱形图 → 确定 5). 选中数据透视表 → 数据透视表分析选项卡 → 插入切片器 → 勾选“销售员” → 确定 → 右键切片器 → 报表连接 → 勾选数据透视图所在图表 6).在透视表外空白单元格输入=GETPIVOTDATA("销售额",数据透视表,"区域","华东","产品","手机") → 回车 第4题 1).创建新工作表完成率,使用跨工作表引用计算每个区域截至6月的累计实际销售额 2).使用公式计算每个区域的目标完成率(累计实际/目标销售额),并设置为百分比格式 3).使用条件格式(数据条或图标集)可视化展示各区域完成率 4).创建动态图表：使用下拉列表(数据验证)选择区域后,自动显示该区域1-6月的销售额折线图 5).使用OFFSET函数定义动态名称,实现图表数据范围自动扩展 6).设置工作表联动：在销售目标工作表修改目标销售额后,完成率工作表自动更新 答案: 1).新建工作表命名“完成率” → B2输入=SUM(实际销售!B2:B7) → C2输入=SUM(实际销售!C2:C7) → D2输入=SUM(实际销售!D2:D7) → E2输入=SUM(实际销售!E2:E7) → 可使用拖拽填充 2). A列输入区域名称：华东、华南、华北、西南 → B列输入目标销售额 → C2输入=B2/销售目标!B2 → 设置C列 → 开始 → 数字格式选百分比 3).选中C2:C5 → 开始 → 条件格式 → 数据条 → 选择渐变填充 → 或选择图标集 → 方向选“三向箭头” 4).选中F1单元格 → 数据验证 → 允许选“序列” → 来源输入华东,华南,华北,西南 → 确定 → 创建辅助列使用VLOOKUP提取数据 → 插入折线图 → 数据源引用辅助列 5).公式选项卡 → 定义名称 → 名称输入“动态数据” → 引用位置输入=OFFSET(实际销售!$B$2,MATCH(看板!$F$1,实际销售!$A$1:$A$7,0)-1,0,1,6) → 图表数据源引用该名称 6).在“销售目标”工作表修改任意区域的目标销售额 → 切换到“完成率”工作表 → 按F9刷新 → 完成率自动更新 第5题 1).使用高级筛选,筛选出部门=生产部且绩效工资≥1000或加班时长≥20的所有记录 2).对部门进行分类汇总,计算每个部门的平均基本工资和绩效工资总和 3).创建数据透视表,行区域为部门和职位,值区域为基本工资(平均值)和加班时长(总和) 4).基于数据透视表创建簇状柱形图+折线图组合图：柱形图显示平均基本工资,折线图显示总加班时长(用次坐标轴) 5).使用数据验证,为职位列设置下拉选项(主管、员工),并添加输入提示信息 6).将数据透视表和图表复制到新工作表管理层看板,并设置刷新联动(原数据变更后自动更新) 答案: 1).设置条件区域：J1输入“部门”，J2输入“生产部”，K1输入“绩效工资”，K2输入“>=1000”，L1输入“加班时长”，L2输入“>=20” → 注意：同一行表示“与”关系，不同行表示“或”关系 → 数据选项卡 → 高级 → 列表区域选A1:H9 → 条件区域选J1:L3 → 勾选“将筛选结果复制到其他位置” → 复制到选M1 → 确定 2).先按“部门”排序 → 选中数据区域 → 数据选项卡 → 分类汇总 → 分类字段选“部门” → 汇总方式选“平均值” → 选定汇总项勾选“基本工资” → 确定 → 再次分类汇总 → 汇总方式选“求和” → 选定汇总项勾选“绩效工资” → 取消勾选“替换当前分类汇总” → 确定 3).选中数据区域 → 插入选项卡 → 数据透视表 → 确定 → 行区域拖入“部门”和“职位” → 值区域拖入“基本工资”并设置为平均值 → 值区域拖入“加班时长”并设置为总和 4).选中数据透视表 → 插入选项卡 → 数据透视图 → 组合图 → 平均基本工资设为簇状柱形图 → 总加班时长设为折线图并勾选“次坐标轴” → 确定 5).选中职位列 → 数据选项卡 → 数据验证 → 允许选“序列” → 来源输入主管,员工 → 输入信息 → 勾选“选定单元格时显示输入信息” → 标题输入“请选择” → 输入信息输入请选择主管或员工→ 确定 6).选中数据透视表所在工作表 → 右键工作表标签 → 移动或复制 → 勾选建立副本 → 重命名为管理层看板 → 右键数据透视表 → 刷新 → 设置：数据透视表分析 → 数据透视表选项 → 数据 → 勾选“打开文件时刷新数据” → 确定 原创精品资源学科网独家享有版权,侵权必究！ 学科网（北京）股份有限公司 上半年(题二).xlsx Sheet1 产品 1月销量 2月销量 3月销量 4月销量 5月销量 6月销量 手机 1200 1350 1420 1580 1650 1720 电脑 800 850 900 950 1000 1080 平板 600 620 650 680 700 750 下半年(题二).xlsx Sheet1 产品 7月销量 8月销量 9月销量 10月销量 11月销量 12月销量 手机 1800 1750 1700 1650 1600 1550 电脑 1100 1120 1150 1180 1200 1220 平板 780 800 820 850 870 900 员工表(题五).xlsx Sheet1 员工编号 姓名 部门 职位 基本工资 绩效工资 出勤天数 加班时长 E001 张三 生产部 主管 8000 2000 22 15 E002 李四 生产部 员工 5000 1000 20 25 E003 王五 质检部 主管 7500 1800 23 10 E004 赵六 质检部 员工 4800 900 19 30 E005 周七 生产部 员工 5200 1100 21 20 E006 钱八 财务部 主管 7000 1500 22 5 E007 孙九 财务部 员工 4500 800 20 8 E008 吴十 生产部 员工 5100 1050 22 18 实际销售数据(题四).xlsx Sheet1 月份 华东 华南 华北 西南 1月 42000 38000 35000 30000 2月 45000 40000 37000 32000 3月 48000 42000 39000 34000 4月 50000 44000 41000 36000 5月 52000 46000 43000 38000 6月 54000 48000 45000 40000 销售数据表(题一).xlsx 新建 XLS 工作表 销售员 区域 季度 产品类别 销售额 订单数 张三 华东 Q1 手机 120000 45 李四 华南 Q1 手机 98000 32 王五 华东 Q2 平板 85000 28 赵六 华北 Q1 电脑 150000 52 张三 华东 Q2 手机 135000 48 李四 华南 Q2 平板 76000 24 王五 华东 Q1 电脑 142000 50 赵六 华北 Q2 手机 89000 30 周七 西南 Q1 平板 67000 22 周七 西南 Q2 手机 72000 26 销售目标(题四).xlsx Sheet1 区域 目标销售额 负责人 华东 500000 张经理 华南 450000 李经理 华北 400000 王经理 西南 350000 赵经理 销售表(题三).xlsx Sheet1 订单日期 销售员 区域 产品 销售额 数量 客户等级 1/5/25 张三 华东 手机 5000 5 A 1/12/25 李四 华南 电脑 12000 3 B 1/18/25 王五 华东 平板 3000 4 A 2/3/25 张三 华东 手机 6000 6 A 2/14/25 李四 华南 耳机 800 8 C 2/20/25 王五 华北 电脑 11000 2 B 3/7/25 张三 华东 平板 3500 5 A 3/15/25 赵六 西南 手机 4500 4 B 3/22/25 李四 华南 手机 5500 5 A 4/1/25 王五 华北 电脑 13000 3 A 4/12/25 张三 华东 耳机 900 9 C 4/18/25 赵六 西南 平板 2800 4 B $ 湖北省技能高考《电子表格》45分钟专题训练专辑,共3份试卷试卷命题与高考紧密相关,精选具有代表性和典型性的高考常考题旨在帮助学生系统地复习和巩固计算机类理论知识,熟悉高考题型和命题规律,提高解题能力和应试技巧,为高考取得优异成绩打下坚实的基础,本训练卷适用于即将参加计算机类高考的学生与本专辑配套的还有湖北省技能高考《操作系统的使用》《计算机网络应用》《文字处理》《电子表格处理》《演示文稿制作》《程序设计》《数据库应用》《图像处理》45分钟专题训练专辑,欢迎同学和老师们下载使用 《电子表格》 项目三: 图表与数据透视表 时间:45分钟 总分:100分 班级___________ 姓名__________ 学号__________ 成绩________ 一、演示文稿(共5小题,每小题20分,共100分) 第1题 1).对数据进行多级排序：主要关键字区域升序,次要关键字销售额降序 2).使用自动筛选,筛选出区域为华东且销售额大于100000的记录 3).使用高级筛选,将产品类别为手机或平板且订单数≥30的数据复制到新位置 4).对区域进行分类汇总,计算每个区域的销售额总和与订单数平均值 5).删除分类汇总,使用SUMIFS函数分别计算每个区域的总销售额 6).使用数据验证(有效性),限制产品类别列只能输入手机、电脑、平板三种值,并设置出错警告 第2题 1).使用合并计算功能,将上半年和下半年两个工作表的手机产品销量按求和方式合并到新工作表 2).创建新工作表年度对比,使用跨工作表公式引用计算每种产品下半年的总销量 3).使用条件格式,将手机销量中环比增长超过10%的月份标记为绿色背景 4).创建柱形图,对比三种产品上半年和下半年的总销量,添加数据标签 5).使用MATCH+INDEX函数,找出平板销量最高的月份及其销量 6).使用数据验证创建下拉列表,选择产品名称后自动显示该产品全年销量走势图 第3题 1).创建数据透视表,将区域拖至行区域,产品拖至列区域,销售额拖至值区域(求和) 2).在数据透视表中添加计算字段平均单价,公式：=销售额/数量,并设置为货币格式 3).对数据透视表中的销售额进行值字段设置,同时显示求和和平均值 4).创建数据透视图(簇状柱形图),展示各区域不同产品的销售额对比 5).使用切片器同时控制数据透视表和数据透视图,切片器字段为销售员 6).使用GETPIVOTDATA函数,在透视表外提取华东区域手机产品的销售额 第4题 1).创建新工作表完成率,使用跨工作表引用计算每个区域截至6月的累计实际销售额 2).使用公式计算每个区域的目标完成率(累计实际/目标销售额),并设置为百分比格式 3).使用条件格式(数据条或图标集)可视化展示各区域完成率 4).创建动态图表：使用下拉列表(数据验证)选择区域后,自动显示该区域1-6月的销售额折线图 5).使用OFFSET函数定义动态名称,实现图表数据范围自动扩展 6).设置工作表联动：在销售目标工作表修改目标销售额后,完成率工作表自动更新 第5题 1).使用高级筛选,筛选出部门=生产部且绩效工资≥1000或加班时长≥20的所有记录 2).对部门进行分类汇总,计算每个部门的平均基本工资和绩效工资总和 3).创建数据透视表,行区域为部门和职位,值区域为基本工资(平均值)和加班时长(总和) 4).基于数据透视表创建簇状柱形图+折线图组合图：柱形图显示平均基本工资,折线图显示总加班时长(用次坐标轴) 5).使用数据验证,为职位列设置下拉选项(主管、员工),并添加输入提示信息 6).将数据透视表和图表复制到新工作表管理层看板,并设置刷新联动(原数据变更后自动更新) 原创精品资源学科网独家享有版权,侵权必究！ 学科网（北京）股份有限公司 上半年(题二).xlsx Sheet1 产品 1月销量 2月销量 3月销量 4月销量 5月销量 6月销量 手机 1200 1350 1420 1580 1650 1720 电脑 800 850 900 950 1000 1080 平板 600 620 650 680 700 750 下半年(题二).xlsx Sheet1 产品 7月销量 8月销量 9月销量 10月销量 11月销量 12月销量 手机 1800 1750 1700 1650 1600 1550 电脑 1100 1120 1150 1180 1200 1220 平板 780 800 820 850 870 900 员工表(题五).xlsx Sheet1 员工编号 姓名 部门 职位 基本工资 绩效工资 出勤天数 加班时长 E001 张三 生产部 主管 8000 2000 22 15 E002 李四 生产部 员工 5000 1000 20 25 E003 王五 质检部 主管 7500 1800 23 10 E004 赵六 质检部 员工 4800 900 19 30 E005 周七 生产部 员工 5200 1100 21 20 E006 钱八 财务部 主管 7000 1500 22 5 E007 孙九 财务部 员工 4500 800 20 8 E008 吴十 生产部 员工 5100 1050 22 18 实际销售数据(题四).xlsx Sheet1 月份 华东 华南 华北 西南 1月 42000 38000 35000 30000 2月 45000 40000 37000 32000 3月 48000 42000 39000 34000 4月 50000 44000 41000 36000 5月 52000 46000 43000 38000 6月 54000 48000 45000 40000 销售数据表(题一).xlsx 新建 XLS 工作表 销售员 区域 季度 产品类别 销售额 订单数 张三 华东 Q1 手机 120000 45 李四 华南 Q1 手机 98000 32 王五 华东 Q2 平板 85000 28 赵六 华北 Q1 电脑 150000 52 张三 华东 Q2 手机 135000 48 李四 华南 Q2 平板 76000 24 王五 华东 Q1 电脑 142000 50 赵六 华北 Q2 手机 89000 30 周七 西南 Q1 平板 67000 22 周七 西南 Q2 手机 72000 26 销售目标(题四).xlsx Sheet1 区域 目标销售额 负责人 华东 500000 张经理 华南 450000 李经理 华北 400000 王经理 西南 350000 赵经理 销售表(题三).xlsx Sheet1 订单日期 销售员 区域 产品 销售额 数量 客户等级 1/5/25 张三 华东 手机 5000 5 A 1/12/25 李四 华南 电脑 12000 3 B 1/18/25 王五 华东 平板 3000 4 A 2/3/25 张三 华东 手机 6000 6 A 2/14/25 李四 华南 耳机 800 8 C 2/20/25 王五 华北 电脑 11000 2 B 3/7/25 张三 华东 平板 3500 5 A 3/15/25 赵六 西南 手机 4500 4 B 3/22/25 李四 华南 手机 5500 5 A 4/1/25 王五 华北 电脑 13000 3 A 4/12/25 张三 华东 耳机 900 9 C 4/18/25 赵六 西南 平板 2800 4 B $