第51卷 图表与数据透视表《电子表格处理》湖北省技能高考《计算机类考纲百套卷》

编写说明：湖北省技能高考《计算机类考纲百套卷》,依据《湖北省普通高等学校招收中等职业学校毕业生技能高考计算机类技能考试大纲》编写每门课程均分三层训练：基础层(具象化支架)以拆解考点为微目标,紧扣考纲 掌握理解 要求编写考点训练卷;巩固层(关联性支架)强化知识交叉与场景关联,按考纲专题编专题训练卷;应用层(引导性支架)聚焦真题突破,结合知识模块与教材编写课程综合卷 本试卷是第50卷,按《电子表格处理》范围和要求编写具体内容为:图表的创建、编辑和格式设置与修饰;数据透视表和数据透视图的使用以及多个工作表联动操作 湖北省《计算机类考纲百套卷》 第50卷 《电子表格处理》 图表与数据透视表 考点训练卷 考试时间60分钟 满分100分 班级 姓名 学号 成绩 一、电子表格操作题(本大题共10小题,每题10分,共100分) 第1题 (1).打开年度销售数据.xlsx,在Sheet1中根据A1:A17和E1:E17(月份和销售额)两列数据,插入一个簇状柱形图,图表存放于当前工作表中 (2).将生成的簇状柱形图更改为折线图,并将图表标题设置为各月销售额趋势图,字体设为微软雅黑、字号16、加粗 (3).在折线图中添加数据标签,标签位置设置为上方,并将数据标签的字体设为宋体、字号9、颜色为深蓝色 (4).将图表中的水平轴(月份)字体设为黑体、字号10；垂直轴(销售额)字体设为黑体、字号10,并将垂直轴的主要刻度单位设置为50,最小值为0,最大值为300 (5).修改图表数据源,增加F列(利润)数据作为第二个数据系列,将新系列命名为利润,并将图表类型改为组合图(销售额为柱形图、利润为折线图) (6).将组合图中的利润系列设置为次坐标轴,调整次坐标轴刻度范围为0到60,保存文件 第2题 (1).在Sheet1中,根据B1:B17(地区)和E1:E17(销售额)两列数据,插入一个三维饼图,图表标题设为各地区销售额占比,并将图例位置移至右侧 (2).对三维饼图进行分离设置:将华东地区所在扇区分离出来(分离程度设为20%),并将该扇区的填充色设为金色 (3).为饼图添加百分比数据标签,显示类别名称和百分比,标签位置为数据标签外,字体设为楷体、字号10、加粗 (4).设置三维饼图的图表区填充为淡蓝色渐变,图表区边框为深蓝色实线、宽度1.5磅；设置绘图区背景为淡黄色 (5).为饼图添加三维旋转效果:X轴旋转设为30°,Y轴旋转设为15°,透视系数设为80% (6).将三维饼图复制一份到Sheet2的A1起始位置,并将副本的图表类型改为圆环图,圆环内径大小设为50%,保存文件 第3题 (1).在Sheet1中,根据C1:C17(产品类别)和E1:E17(销售额)两列数据,插入一个堆积柱形图,图表标题设为各类产品月度销售额堆积图 (2).为堆积柱形图添加数据表(显示在图表下方),并添加次要网格线(垂直轴方向) (3).在堆积柱形图中,为电子产品系列添加趋势线(线性趋势线),显示趋势线的公式和R²值,趋势线颜色设为红色、宽度2磅 (4).修改堆积柱形图中各系列的填充色:电子产品设为蓝色渐变,家电产品设为绿色渐变,数码产品设为橙色渐变,透明度均设为80% (5).在图表中添加一个垂直参考线(使用直线形状手工绘制),标记销售额200万元的位置,参考线设为红色虚线、宽度2磅 (6).在Sheet1的D20单元格中,使用TREND函数根据现有销售额数据预测下一个月(5月)的销售额,并将预测值以文本形式添加到图表中(插入文本框),保存文件 第4题 (1).在Sheet2中(新建工作表或使用现有Sheet2),基于Sheet1的A1:F17数据区域创建一个数据透视表,放置于Sheet2的A1起始位置,行标签设为地区,列标签设为产品类别,值字段设为销售额(求和) (2).在数据透视表中,将销售额的值显示方式改为行汇总百分比,并将值字段名称修改为行占比% (3).在数据透视表中,添加利润字段作为第二个值字段,将其汇总方式改为平均值,并将值字段名称修改为平均利润 (4).在数据透视表中,对地区行标签进行排序:按销售额从高到低降序排列 (5).在数据透视表中,使用值筛选功能筛选出销售额大于400万元的地区,并隐藏销售额小于400万元的地区行 (6).在数据透视表底部,添加一个计算字段,名称为利润率,公式为=利润/销售额,将计算字段添加到值区域,格式设为百分比保留2位小数,保存文件 第5题 (1).在Sheet3中(新建工作表),基于Sheet1的A1:F17数据创建一个新的数据透视表,行标签设为月份,列标签设为地区,值字段设为销售额(求和)和利润(求和) (2).在数据透视表中,将月份字段分组:将1月、2月、3月组合为Q1,4月组合为Q2(手动分组),并将新组合命名为季度 (3).在数据透视表中,添加销售员字段作为报表筛选字段(筛选器),并在筛选器中仅显示张伟和李娜两位销售员的数据 (4).在数据透视表中,设置报表布局为以表格形式显示,重复所有项目标签,不显示分类汇总行 (5).在数据透视表中,为销售额字段添加数据条格式(蓝色渐变数据条),突出显示数值大小 (6).刷新数据透视表后,将其复制到Sheet4的A1起始位置,在复制后的数据透视表中将利润字段的值显示方式改为列汇总百分比,保存文件 第6题 (1).在Sheet5中(新建工作表),基于Sheet1的A1:F17数据创建一个数据透视图,图表类型为簇状柱形图,将月份字段拖至轴(类别),将地区字段拖至图例(系列),将销售额字段拖至值 (2).将数据透视图的图表标题设为各月份各地区销售额对比,字体设为华文楷体、字号18、加粗、深蓝色 (3).在数据透视图中,添加数据标签,标签位置设为轴内侧,字体设为宋体、字号9、白色 (4).在数据透视图中,将华南系列的颜色改为红色,华东系列改为蓝色,华北系列改为绿色,西南系列改为橙色 (5).在数据透视图右侧,插入一个切片器(字段为产品类别),设置切片器的列为3列,应用切片器样式切片器样式深色3 (6).在数据透视图上方,插入一个日程表(时间线),字段为月份,设置日程表样式为日程表样式浅色2,并调整日程表大小以适应页面,保存文件 第7题 (1).在Sheet6(新建工作表)的A1单元格中输入销售分析报告,合并A1:F1居中,设置字体为黑体22加粗,背景色为深红色,字体颜色为白色 (2).在Sheet6的A3:F3区域中,使用跨表公式引用Sheet1中的数据,依次显示总销售额(SUM)、总利润(SUM)、平均销售额(AVERAGE)、最大单月销售额(MAX)、最小单月销售额(MIN)、利润率(总利润/总销售额) (3).在Sheet6的A5单元格中输入各地区销售汇总,从A5开始创建一个汇总表,行标签为四个地区(华东、华南、华北、西南),使用SUMIFS函数从Sheet1中引用每个地区的总销售额和总利润 (4).在Sheet6的A10单元格中输入各产品类别销售汇总,从A10开始创建第二个汇总表,行标签为三个产品类别(电子产品、家电产品、数码产品),使用SUMIFS函数从Sheet1中引用每个类别的总销售额和总利润 (5).在Sheet6的A15单元格中输入销售员绩效排名,从A15开始创建一个排名表,统计每位销售员的总销售额(使用SUMIFS),并添加RANK函数计算排名,按销售额从高到低排序 (6).将Sheet6中A3:F3、A5:C8、A10:C13三个汇总区域的数值全部设置为货币格式保留0位小数,并添加绿色底纹(浅绿色),保存文件 第8题 (1).在Sheet7(新建工作表)中,基于Sheet1的A1:F17数据创建两个数据透视表:透视表1以地区为行标签、销售额为值；透视表2以产品类别为行标签、利润为值,两个透视表并排放置(透视表1在A1起始,透视表2在E1起始) (2).插入一个切片器,字段选择月份,将该切片器同时连接到上述两个数据透视表(报表连接),实现联动筛选 (3).插入第二个切片器,字段选择销售员,同样连接到两个数据透视表,设置切片器样式为切片器样式彩色2 (4).将两个切片器并排放置于数据透视表上方,调整切片器大小(高度2厘米、宽度6厘米),并将月份切片器的列数设为4列,销售员切片器的列数设为5列 (5).在透视表1中,添加计算项(或计算字段),计算各地区销售额占总销售额的百分比,使用值显示方式中的总计百分比 (6).将两个数据透视表的数据区域套用表格样式表样式中等深浅12,并将整个工作表(包含透视表和切片器)设置为打印区域,页边距设为窄,保存文件 第9题 (1).打开年度销售数据.xlsx,再打开辅助文件产品单价表.xlsx,在年度销售数据.xlsx的Sheet8中,使用外部数据源引用产品单价表.xlsx的A1:B8区域 (2).在Sheet8中,根据Sheet3(产品销售明细)的产品字段和销量字段,结合产品单价表.xlsx中的单价,使用VLOOKUP函数计算每种产品的销售额(销量×单价),生成新的数据区域A1:D49 (3).基于上一步生成的新数据区域(A1:D49),在Sheet8中创建一个数据透视表,以产品为行标签,以日期为列标签(按月份分组),以销售额为值(求和) (4).在Sheet8中,基于上述数据透视表插入一个数据透视图,图表类型为堆积柱形图,图表标题设为各产品月度销售额分布,设置图表样式为样式8 (5).在Sheet8中,为数据透视图添加数据标签,并将图例位置移至底部；设置垂直轴(销售额)的主要单位为20000 (6).断开年度销售数据.xlsx与产品单价表.xlsx的外部链接,保存年度销售数据.xlsx为年度销售分析_完整版.xlsx,关闭所有文件 第10题 (1).打开年度销售数据.xlsx,在Sheet9(新建工作表)中创建一个仪表板页面,将Sheet9重命名为销售仪表板,设置标签颜色为蓝色 (2).在销售仪表板工作表中,插入三个数据透视图(基于Sheet1数据):左上角放置各地区销售额饼图,右上角放置各产品类别销售额柱形图,下方放置月度销售额趋势折线图 (3).插入一个切片器(字段为销售员),连接到三个数据透视图,实现一键联动筛选；设置切片器样式为切片器样式彩色4,列数为5列 (4).在销售仪表板工作表中,插入一个文本框,内容为2024年度销售数据分析报告,字体设为黑体28加粗,颜色为深蓝色,居中放置于三个图表上方 (5).对三个数据透视图进行统一美化:图表区填充均为浅灰色,绘图区填充均为白色,所有图表标题字体设为黑体14加粗,所有数据标签均显示 (6).在销售仪表板工作表中,添加三个形状作为按钮(矩形),分别标注刷新数据导出报告打印仪表板,并为刷新数据按钮指定宏(或刷新全部数据透视表的操作),保存文件 原创精品资源学科网独家享有版权,侵权必究！ 学科网(北京)股份有限公司 学科网(北京)股份有限公司 学科网（北京）股份有限公司 $Sheet1 工号 姓名 部门 入职日期 职级 1001 张伟 销售一部 3/15/18 高级 1002 李娜 销售二部 7/1/19 中级 1003 王强 销售三部 1/10/20 中级 1004 刘洋 销售一部 6/20/21 初级 1005 陈静 销售二部 11/1/17 高级 1006 赵磊 销售三部 8/15/20 中级 1007 孙悦 销售一部 4/1/19 中级 1008 周婷 销售二部 9/10/21 初级 1009 吴昊 销售三部 12/1/18 高级 1010 郑欣 销售一部 2/14/20 中级 1011 林峰 销售二部 10/20/19 中级 1012 郭琳 销售三部 1/5/22 初级 1013 韩雪 销售一部 3/15/21 初级 1014 赵岩 销售二部 6/1/18 高级 1015 许晴 销售三部 11/11/20 中级 Sheet2 Sheet3 $Sheet1 月份 地区 产品类别 销售员 销售额(万元) 利润(万元) 1月 华东 电子产品 张伟 156 31 1月 华南 家电产品 李娜 189 42 1月 华北 数码产品 王强 132 26 1月 西南 电子产品 刘洋 98 18 2月 华东 家电产品 陈静 203 45 2月 华南 数码产品 赵磊 167 33 2月 华北 电子产品 孙悦 145 28 2月 西南 家电产品 周婷 112 22 3月 华东 数码产品 吴昊 178 36 3月 华南 电子产品 郑欣 234 52 3月 华北 家电产品 林峰 156 31 3月 西南 数码产品 郭琳 89 16 4月 华东 电子产品 张伟 198 42 4月 华南 家电产品 李娜 245 55 4月 华北 数码产品 王强 176 35 4月 西南 电子产品 刘洋 134 26 Sheet2 Sheet3 $产品名称单价（元） 智能手机 3200 笔记本电脑 5600 平板电脑 2800 智能手表 1800 冰箱 4200 空调 3800 洗衣机 2900日期 地区 产品销量（台）单价（元） 编写说明：湖北省技能高考《计算机类考纲百套卷》,依据《湖北省普通高等学校招收中等职业学校毕业生技能高考计算机类技能考试大纲》编写每门课程均分三层训练：基础层(具象化支架)以拆解考点为微目标,紧扣考纲 掌握理解 要求编写考点训练卷;巩固层(关联性支架)强化知识交叉与场景关联,按考纲专题编专题训练卷;应用层(引导性支架)聚焦真题突破,结合知识模块与教材编写课程综合卷 本试卷是第50卷,按《电子表格处理》范围和要求编写具体内容为:图表的创建、编辑和格式设置与修饰;数据透视表和数据透视图的使用以及多个工作表联动操作 湖北省《计算机类考纲百套卷》 第50卷 《电子表格处理》 图表与数据透视表 考点训练卷 考试时间60分钟 满分100分 班级 姓名 学号 成绩 一、电子表格操作题(本大题共10小题,每题10分,共100分) 第1题 (1).打开年度销售数据.xlsx,在Sheet1中根据A1:A17和E1:E17(月份和销售额)两列数据,插入一个簇状柱形图,图表存放于当前工作表中 (2).将生成的簇状柱形图更改为折线图,并将图表标题设置为各月销售额趋势图,字体设为微软雅黑、字号16、加粗 (3).在折线图中添加数据标签,标签位置设置为上方,并将数据标签的字体设为宋体、字号9、颜色为深蓝色 (4).将图表中的水平轴(月份)字体设为黑体、字号10；垂直轴(销售额)字体设为黑体、字号10,并将垂直轴的主要刻度单位设置为50,最小值为0,最大值为300 (5).修改图表数据源,增加F列(利润)数据作为第二个数据系列,将新系列命名为利润,并将图表类型改为组合图(销售额为柱形图、利润为折线图) (6).将组合图中的利润系列设置为次坐标轴,调整次坐标轴刻度范围为0到60,保存文件 答案: (1).打开“年度销售数据.xlsx”→选中A1:A17和E1:E17两列数据→切换到插入选项卡点击簇状柱形图按钮→在当前工作表中生成柱形图 (2).右键单击图表区域选择更改图表类型→将柱形图切换为折线图→在图表标题框中输入“各月销售额趋势图”→选中标题在开始选项卡设为微软雅黑16号并加粗 (3).单击图表右上角加号勾选数据标签→在更多选项中将标签位置选为上方→选中数据标签将字体设为宋体9号并改为深蓝色 (4).双击水平轴标签在开始选项卡设为黑体10号→同理设置垂直轴为黑体10号→右键垂直轴选择设置坐标轴格式→将主要刻度单位固定为50、最小值0、最大值300 (5).右键图表选择选择数据→添加新系列命名为“利润”→将系列值指向F2:F17→再次右键更改图表类型→设置销售额为柱形图、利润为折线图→确定完成组合图创建 (6).右键单击利润系列选择设置数据系列格式→勾选次坐标轴选项→双击次垂直轴将刻度范围设为0到60→保存文件 第2题 (1).在Sheet1中,根据B1:B17(地区)和E1:E17(销售额)两列数据,插入一个三维饼图,图表标题设为各地区销售额占比,并将图例位置移至右侧 (2).对三维饼图进行分离设置:将华东地区所在扇区分离出来(分离程度设为20%),并将该扇区的填充色设为金色 (3).为饼图添加百分比数据标签,显示类别名称和百分比,标签位置为数据标签外,字体设为楷体、字号10、加粗 (4).设置三维饼图的图表区填充为淡蓝色渐变,图表区边框为深蓝色实线、宽度1.5磅；设置绘图区背景为淡黄色 (5).为饼图添加三维旋转效果:X轴旋转设为30°,Y轴旋转设为15°,透视系数设为80% (6).将三维饼图复制一份到Sheet2的A1起始位置,并将副本的图表类型改为圆环图,圆环内径大小设为50%,保存文件 答案: (1).框选B1:B17和E1:E17区域→插入选项卡中点击三维饼图→图表标题改为“各地区销售额占比”→在设计选项卡中将图例移至右侧 (2).单击饼图两次选中“华东”所在扇区→向外拖动实现20%分离→右键该扇区设置填充色为金色 (3).勾选数据标签→在更多选项中勾选类别名称和百分比→标签位置设为数据标签外→字体设为楷体10号并加粗 (4).右键图表区设置填充为淡蓝色渐变→边框设为深蓝色实线1.5磅→再选中绘图区将背景填充为淡黄色 (5).右键图表区选择三维旋转→将X轴旋转设为30°、Y轴旋转15°、透视系数80% (6).右键图表选择复制→切换到Sheet2在A1处粘贴→右键副本选择更改图表类型为圆环图→双击圆环在系列选项中内径大小设为50%→保存文件 第3题 (1).在Sheet1中,根据C1:C17(产品类别)和E1:E17(销售额)两列数据,插入一个堆积柱形图,图表标题设为各类产品月度销售额堆积图 (2).为堆积柱形图添加数据表(显示在图表下方),并添加次要网格线(垂直轴方向) (3).在堆积柱形图中,为电子产品系列添加趋势线(线性趋势线),显示趋势线的公式和R²值,趋势线颜色设为红色、宽度2磅 (4).修改堆积柱形图中各系列的填充色:电子产品设为蓝色渐变,家电产品设为绿色渐变,数码产品设为橙色渐变,透明度均设为80% (5).在图表中添加一个垂直参考线(使用直线形状手工绘制),标记销售额200万元的位置,参考线设为红色虚线、宽度2磅 (6).在Sheet1的D20单元格中,使用TREND函数根据现有销售额数据预测下一个月(5月)的销售额,并将预测值以文本形式添加到图表中(插入文本框),保存文件 答案: (1).选中C1:C17和E1:E17→插入堆积柱形图→标题改为“各类产品月度销售额堆积图” (2).点击图表右上角加号勾选数据表→再勾选次要网格线→垂直轴方向网格线自动显示 (3).右键“电子产品”系列添加趋势线→选择线性趋势线→勾选显示公式和R²→将趋势线颜色设为红色、宽度2磅 (4).分别右键各系列设置填充色：电子产品蓝色渐变、家电绿色渐变、数码橙色渐变→透明度统一调至80% (5).插入→形状→直线→在图表200万元位置绘制垂直参考线→将轮廓设为红色虚线、宽度2磅 (6).在D20输入公式“=TREND(E2:E17,A2:A17,5)”预测5月销售额→插入文本框输入预测值并拖至图表中合适位置→保存文件 第4题 (1).在Sheet2中(新建工作表或使用现有Sheet2),基于Sheet1的A1:F17数据区域创建一个数据透视表,放置于Sheet2的A1起始位置,行标签设为地区,列标签设为产品类别,值字段设为销售额(求和) (2).在数据透视表中,将销售额的值显示方式改为行汇总百分比,并将值字段名称修改为行占比% (3).在数据透视表中,添加利润字段作为第二个值字段,将其汇总方式改为平均值,并将值字段名称修改为平均利润 (4).在数据透视表中,对地区行标签进行排序:按销售额从高到低降序排列 (5).在数据透视表中,使用值筛选功能筛选出销售额大于400万元的地区,并隐藏销售额小于400万元的地区行 (6).在数据透视表底部,添加一个计算字段,名称为利润率,公式为=利润/销售额,将计算字段添加到值区域,格式设为百分比保留2位小数,保存文件 答案: (1).切换到Sheet2→选中Sheet1的A1:F17→插入数据透视表→放置于Sheet2的A1→将地区拖至行、产品类别拖至列、销售额拖至值 (2).右键销售额值字段→值显示方式选行汇总百分比→将字段名改为“行占比%” (3).再次将利润拖至值区域→右键设置汇总方式为平均值→重命名为“平均利润” (4).单击地区字段右侧排序按钮→选择降序按销售额排序 (5).点击地区字段筛选器→选择值筛选→大于400万元→自动隐藏不满足条件的地区 (6).在数据透视表分析选项卡中执行计算字段→命名为“利润率”→公式输入“=利润/销售额”→添加到值区域→格式设为百分比2位小数→保存文件 第5题 (1).在Sheet3中(新建工作表),基于Sheet1的A1:F17数据创建一个新的数据透视表,行标签设为月份,列标签设为地区,值字段设为销售额(求和)和利润(求和) (2).在数据透视表中,将月份字段分组:将1月、2月、3月组合为Q1,4月组合为Q2(手动分组),并将新组合命名为季度 (3).在数据透视表中,添加销售员字段作为报表筛选字段(筛选器),并在筛选器中仅显示张伟和李娜两位销售员的数据 (4).在数据透视表中,设置报表布局为以表格形式显示,重复所有项目标签,不显示分类汇总行 (5).在数据透视表中,为销售额字段添加数据条格式(蓝色渐变数据条),突出显示数值大小 (6).刷新数据透视表后,将其复制到Sheet4的A1起始位置,在复制后的数据透视表中将利润字段的值显示方式改为列汇总百分比,保存文件 答案: (1).新建Sheet3→基于Sheet1数据插入数据透视表→月份放行、地区放列→销售额和利润均放值（求和） (2).右键月份字段选择组合→将1、2、3月组合命名为“Q1”→4月单独命名为“Q2” (3).将销售员字段拖至筛选器区域→在筛选器中仅勾选“张伟”和“李娜” (4).切换到设计选项卡→报表布局选以表格形式显示→勾选重复所有项目标签→取消分类汇总 (5).选中销售额数值区域→执行条件格式→数据条→蓝色渐变数据条 (6).右键数据透视表刷新→复制整个透视表到Sheet4的A1→在新透视表中右键利润字段→值显示方式改为列汇总百分比→保存文件 第6题 (1).在Sheet5中(新建工作表),基于Sheet1的A1:F17数据创建一个数据透视图,图表类型为簇状柱形图,将月份字段拖至轴(类别),将地区字段拖至图例(系列),将销售额字段拖至值 (2).将数据透视图的图表标题设为各月份各地区销售额对比,字体设为华文楷体、字号18、加粗、深蓝色 (3).在数据透视图中,添加数据标签,标签位置设为轴内侧,字体设为宋体、字号9、白色 (4).在数据透视图中,将华南系列的颜色改为红色,华东系列改为蓝色,华北系列改为绿色,西南系列改为橙色 (5).在数据透视图右侧,插入一个切片器(字段为产品类别),设置切片器的列为3列,应用切片器样式切片器样式深色3 (6).在数据透视图上方,插入一个日程表(时间线),字段为月份,设置日程表样式为日程表样式浅色2,并调整日程表大小以适应页面,保存文件 答案: (1).新建Sheet5→插入数据透视图→将月份拖至轴、地区拖至图例、销售额拖至值→图表类型默认簇状柱形图 (2).修改图表标题为“各月份各地区销售额对比”→选中标题在开始选项卡设为华文楷体18号、加粗、深蓝色 (3).添加数据标签→位置选轴内侧→标签字体设为宋体9号白色 (4).依次单击各系列柱形→分别将华南改红色、华东改蓝色、华北改绿色、西南改橙色 (5).插入切片器→字段选产品类别→设置为3列→应用切片器样式深色3→放置于图表右侧 (6).插入日程表→字段选月份→应用浅色2样式→调整大小使其不遮挡图表→保存文件 第7题 (1).在Sheet6(新建工作表)的A1单元格中输入销售分析报告,合并A1:F1居中,设置字体为黑体22加粗,背景色为深红色,字体颜色为白色 (2).在Sheet6的A3:F3区域中,使用跨表公式引用Sheet1中的数据,依次显示总销售额(SUM)、总利润(SUM)、平均销售额(AVERAGE)、最大单月销售额(MAX)、最小单月销售额(MIN)、利润率(总利润/总销售额) (3).在Sheet6的A5单元格中输入各地区销售汇总,从A5开始创建一个汇总表,行标签为四个地区(华东、华南、华北、西南),使用SUMIFS函数从Sheet1中引用每个地区的总销售额和总利润 (4).在Sheet6的A10单元格中输入各产品类别销售汇总,从A10开始创建第二个汇总表,行标签为三个产品类别(电子产品、家电产品、数码产品),使用SUMIFS函数从Sheet1中引用每个类别的总销售额和总利润 (5).在Sheet6的A15单元格中输入销售员绩效排名,从A15开始创建一个排名表,统计每位销售员的总销售额(使用SUMIFS),并添加RANK函数计算排名,按销售额从高到低排序 (6).将Sheet6中A3:F3、A5:C8、A10:C13三个汇总区域的数值全部设置为货币格式保留0位小数,并添加绿色底纹(浅绿色),保存文件 答案: (1).新建Sheet6→在A1输入“销售分析报告”→合并A1:F1居中→设为黑体22加粗、深红背景、白色字体 (2).在A3:F3依次输入指标名称→在对应下方单元格输入公式：总销售额“=SUM(Sheet1!E2:E17)”、总利润“=SUM(Sheet1!F2:F17)”、平均销售额“=AVERAGE(Sheet1!E2:E17)”、最大单月销售额“=MAX(Sheet1!E2:E17)”、最小单月“=MIN(Sheet1!E2:E17)”、利润率“=总利润/总销售额” (3).在A5输入“各地区销售汇总”→下方行标签输入四个地区→使用SUMIFS分别统计各地区总销售额和总利润 (4).在A10输入“各产品类别销售汇总”→行标签为三种类别→同样使用SUMIFS计算各类别总销售额和总利润 (5).在A15输入“销售员绩效排名”→使用SUMIFS汇总每位销售员总销售额→用RANK函数计算排名→按降序排列 (6).分别框选A3:F3、A5:C8、A10:C13三个区域→设为货币格式保留0位小数→填充浅绿色底纹→保存文件 第8题 (1).在Sheet7(新建工作表)中,基于Sheet1的A1:F17数据创建两个数据透视表:透视表1以地区为行标签、销售额为值；透视表2以产品类别为行标签、利润为值,两个透视表并排放置(透视表1在A1起始,透视表2在E1起始) (2).插入一个切片器,字段选择月份,将该切片器同时连接到上述两个数据透视表(报表连接),实现联动筛选 (3).插入第二个切片器,字段选择销售员,同样连接到两个数据透视表,设置切片器样式为切片器样式彩色2 (4).将两个切片器并排放置于数据透视表上方,调整切片器大小(高度2厘米、宽度6厘米),并将月份切片器的列数设为4列,销售员切片器的列数设为5列 (5).在透视表1中,添加计算项(或计算字段),计算各地区销售额占总销售额的百分比,使用值显示方式中的总计百分比 (6).将两个数据透视表的数据区域套用表格样式表样式中等深浅12,并将整个工作表(包含透视表和切片器)设置为打印区域,页边距设为窄,保存文件 答案: (1).新建Sheet7→基于Sheet1数据创建两个透视表：透视表1（地区行、销售额值）放A1起始；透视表2（产品类别行、利润值）放E1起始 (2).插入切片器→字段选月份→在切片器工具选项中执行报表连接→同时勾选两个透视表 (3).再插入第二个切片器→字段选销售员→同样连接两个透视表→应用彩色2样式 (4).将两个切片器并排放置在透视表上方→在大小属性中设高度2厘米、宽度6厘米→月份切片器设4列、销售员切片器设5列 (5).右键透视表1的销售额字段→值显示方式选总计百分比→自动显示各地区占比 (6).框选两个透视表数据区域→套用表样式中等深浅12→将整个工作表设为打印区域→页边距选窄→保存文件 第9题 (1).打开年度销售数据.xlsx,再打开辅助文件产品单价表.xlsx,在年度销售数据.xlsx的Sheet8中,使用外部数据源引用产品单价表.xlsx的A1:B8区域 (2).在Sheet8中,根据Sheet3(产品销售明细)的产品字段和销量字段,结合产品单价表.xlsx中的单价,使用VLOOKUP函数计算每种产品的销售额(销量×单价),生成新的数据区域A1:D49 (3).基于上一步生成的新数据区域(A1:D49),在Sheet8中创建一个数据透视表,以产品为行标签,以日期为列标签(按月份分组),以销售额为值(求和) (4).在Sheet8中,基于上述数据透视表插入一个数据透视图,图表类型为堆积柱形图,图表标题设为各产品月度销售额分布,设置图表样式为样式8 (5).在Sheet8中,为数据透视图添加数据标签,并将图例位置移至底部；设置垂直轴(销售额)的主要单位为20000 (6).断开年度销售数据.xlsx与产品单价表.xlsx的外部链接,保存年度销售数据.xlsx为年度销售分析_完整版.xlsx,关闭所有文件 答案: (1).打开“年度销售数据.xlsx”和“产品单价表.xlsx”→切换到Sheet8→通过数据选项卡中的现有连接引用外部数据源选择产品单价表的A1:B8 (2).在Sheet8中根据Sheet3的产品和销量列→使用VLOOKUP从外部文件获取单价→计算销售额“=销量×单价”→生成新数据表A1:D49 (3).基于新数据表创建数据透视表→产品放行、日期（按月份分组）放列、销售额求和放值 (4).插入数据透视图→选择堆积柱形图→标题设为各产品月度销售额分布→应用样式8 (5).添加数据标签→图例移至底部→垂直轴主要单位设为20000 (6).在数据选项卡执行编辑链接→断开与产品单价表的链接→将当前文件另存为“年度销售分析_完整版.xlsx”→关闭所有文件 第10题 (1).打开年度销售数据.xlsx,在Sheet9(新建工作表)中创建一个仪表板页面,将Sheet9重命名为销售仪表板,设置标签颜色为蓝色 (2).在销售仪表板工作表中,插入三个数据透视图(基于Sheet1数据):左上角放置各地区销售额饼图,右上角放置各产品类别销售额柱形图,下方放置月度销售额趋势折线图 (3).插入一个切片器(字段为销售员),连接到三个数据透视图,实现一键联动筛选；设置切片器样式为切片器样式彩色4,列数为5列 (4).在销售仪表板工作表中,插入一个文本框,内容为2024年度销售数据分析报告,字体设为黑体28加粗,颜色为深蓝色,居中放置于三个图表上方 (5).对三个数据透视图进行统一美化:图表区填充均为浅灰色,绘图区填充均为白色,所有图表标题字体设为黑体14加粗,所有数据标签均显示 (6).在销售仪表板工作表中,添加三个形状作为按钮(矩形),分别标注刷新数据导出报告打印仪表板,并为刷新数据按钮指定宏(或刷新全部数据透视表的操作),保存文件 答案: (1).新建Sheet9→重命名为“销售仪表板”→标签颜色设为蓝色 (2).基于Sheet1数据插入三个数据透视图：左上角饼图（各地区销售额）、右上角柱形图（各产品类别销售额）、下方折线图（月度销售额趋势） (3).插入切片器→字段选销售员→连接到三个图表→应用彩色4样式、设置5列 (4).插入文本框→输入“2024年度销售数据分析报告”→设为黑体28加粗、深蓝色→居中放置于三个图表上方 (5).统一美化：各图表区填充浅灰色、绘图区白色→所有图表标题改为黑体14加粗→显示所有数据标签 (6).插入三个矩形形状→分别标注“刷新数据”“导出报告”“打印仪表板”→右键刷新按钮指定宏→保存文件 原创精品资源学科网独家享有版权,侵权必究！ 学科网(北京)股份有限公司 学科网(北京)股份有限公司 学科网（北京）股份有限公司 $季度 目标销售额（万元） Q1 1800 Q2 2100 Q3 2400 Q4 2600