销售部助理小王需要针对公司上半年产品销售情况进行统计分析,并根据全年销售计划执行进行评估。按照如下要求完成该项工作:(1) 在考生文件夹下,打开“Excel素材.xlsx”文件,将其另存为“...
(1) 在考生文件夹下,打开“Excel素材.xlsx”文件,将其另存为“Excel.xlsx”(“.xlsx”为扩展名),之后所有的操作均基于此文件,否则不得分。
(2)在“销售业绩表”工作表的“个人销售总计”列中,通过公式计算每名销售人员1月~6月的销售总和。
(3) 依据“个人销售总计”列的统计数据,在“销售业绩表”工作表的“销售排名”列中通过公式计算销售排行榜,个人销售总计排名第一的,显示“第1名”;个人销售总计排名第二的,显示“第2名”;以此类推。
(4)在“按月统计”工作表中,利用公式计算1月~6月的销售达标率,即销售额大于60000元的人数所占比例,并填写在“销售达标率”行中。要求以百分比格式显示计算数据,并保留2位小数。
(5)在“按月统计”工作表中,分别通过公式计算各月排名第1、第2和第3的销售业绩,并填写在“销售第一名业绩”、“销售第二名业绩”和“销售第三名业绩”所对应的单元格中。要求使用人民币会计专用数据格式,并保留2位小数。
(6)依据“销售业绩表”中的数据明细,在“按部门统计”工作表中创建一个数据透视表,并将其放置于A1单元格。要求可以统计出各部门的人员数量,以及各部门的销售额占销售总额的比例。数据透视表效果可参考“按部门统计”工作表中的样例。
(7)在“销售评估”工作表中创建一标题为“销售评估”的图表,借助此图表可以清晰反映每月“A类产品销售额”和“B类产品销售额”之和,与“计划销售额”的对比情况。图表效果可参考“销售评估”工作表中的样例。
参考解析: (1)【解题步骤】
步骤1:打开考生文件夹下的“Excel素材.docx”文件。
步骤2:单击【文件】选项卡下的“另存为”按钮,弹出“另存为”对话框,在该对话框中将“文件名”设为“Excel”,将其保存于考生文件夹下。
(2)【解题步骤】
步骤1:选中“销售业绩表”中的J3单元格。
步骤2:在J3单元格中输入公式“=SUM(D3:I3)”,按Enter键确认输入。
步骤3:使用鼠标拖动J3单元格右下角的填充柄,向下填充到J46单元格。
(3)【解题步骤】
步骤1:选中“销售业绩表”中的K3单元格。
步骤2:在K3单元格中输入公式“="第"&RANK.EQ([@个人销售总计],[个人销售总计])&"名"”,按Enter键确认输入。
步骤3:使用鼠标拖动K3单元格右下角的填充柄,向下填充到K46单元格。
(4)【解题步骤】
步骤1:选中“按月统计”工作表中的B3:G3单元格区域。
步骤2:单击鼠标右键,在弹出的快捷菜单中选择“设置单元格格式”命令,弹出“设置单元格格式”对话框,在“数字”选项卡中选择“分类”列表框中的“百分比”,将右侧的“小数位数”设置为“2”,单击“确定”按钮。
步骤3:选中B3单元格,输入公式“=COUNTIF(表1[一月份],">60000")/COUNT(表1[一月份])”,按Enter键确认输入。
步骤4:使用鼠标拖动B3单元格的填充柄,向右填充到G3单元格。
(5)【解题步骤】
步骤1:选中“按月统计”工作表中的“B4:G6”区域。
步骤2:单击鼠标右键,在弹出的快捷菜单中选择“设置单元格格式”命令,弹出“设置单元格格式”对话框,在“数字”选项卡中选择“分类”列表框中的“会计专用”,将右侧的“小数位数”设置为“2”,“货币符号(国家/地区)”设置为人民币符号“¥”,单击“确定”按钮。
步骤3:选中B4单元格,输入公式“=LARGE(表1[一月份],1)”,按ENTER键确认输入。
步骤4:使用鼠标拖动B4单元格的填充句柄,向右填充到G4单元格。
步骤5:选中B5单元格,输入公式“=LARGE(表1[一月份],2)”,按ENTER键确认输入。
步骤6:使用鼠标拖动B5单元格的填充句柄,向右填充到G5单元格,然后把E5单元格中公式“=LARGE(表1[四月份],2)”,改为“=LARGE(表1[四月份],3)”。
步骤7:选中B6单元格,输入公式“=LARGE(表1[一月份],3)”,按ENTER键确认输入。
步骤8:使用鼠标拖动B6单元格的填充句柄,向右填充到G6单元格,然后把E6单元格中公式“=LARGE(表1[四月份],3)”,改为“=LARGE(表1[四月份],4)”。
说明:本题修改E5和E6单元格中公式,是因为销售第一名业绩有两位,为了数据不重复,E5和E6分别取第三名和第四名的业绩。
(6)【解题步骤】
步骤1:选中“按部门统计”工作表中的A1单元格。
步骤2:单击【插入】选项卡下【表格】组中的“数据透视表”按钮,弹出“创建数据透视表”对话框,单击“表/区域”文本框右侧的“折叠对话框”按钮,使用鼠标单击“销售业绩表”并选择数据区域A2:K46,按Enter键展开“创建数据透视表”对话框,最后单击“确定”按钮。
步骤3:拖动“按部门统计”工作表右侧的“数据透视表字段列表”中的“销售团队”字段到“行标签”区域中。
步骤4:拖动“销售团队”字段到“数值”区域中。
步骤5:拖动“个人销售总计”字段到“数值”区域中。
步骤6:单击“数值”区域中的“个人销售总计”右侧的下拉三角形按钮,在弹出的快捷菜单中选择“值字段设置”命令,弹出“值字段设置”对话框,选择“值显示方式”选项卡,在“值显示方式”下拉列表框中选择“全部汇总百分比”,单击“确定”按钮。
步骤7:双击A1单元格,输入标题名称“部门”;双击B1单元格,在弹出的“值字段设置”对话框中,在“自定义名称”文本框中输入“销售团队人数”,单击“确定”按钮;同理双击C1单元格,在弹出的“值字段设置”对话框中,在“自定义名称”文本框中输入“各部门所占销售比例”,单击“确定”按钮。
(7)【解题步骤】
步骤1:选中“销售评估”工作表中的A2:G5单元格区域。
步骤2:单击【插入】选项卡下【图表】组中的“柱形图”按钮,在列表框中选择“堆积柱形图”。
步骤3:选中创建的图表,在【图表工具】|【布局】选项卡下,单击【标签】组中的“图表标题”下拉按钮,选择“图表上方”。选中添加的图表标题文本框,将图表标题修改为“销售评估”。
步骤4:单击【图表工具】|【设计】选项卡下【图表布局】组中的“布局3”样式。
步骤5:单击选中图表区中的“计划销售额”图形,单击鼠标右键,在弹出快捷菜单中选择“设置数据序列格式”命令,弹出“设置数据序列格式”对话框,选中左侧列表框中的“系列选项”,拖动右侧“分类间距”中的滑动块,将比例调整到25%;同时选择将“系列绘制在”选项组中的“次坐标轴”。
步骤6:单击选中左侧列表框中的“填充”,在右侧的“填充”选项组中选择“无填充”。
步骤7:单击选中左侧列表框中的“边框颜色”,在右侧的“边框颜色”选项组中选择“实线”,将颜色设置为标准色的“红色”。
步骤8:单击选中左侧列表框中的“边框样式”,在右侧的“边框样式”选项组中将“宽度”设置为2磅,单击“关闭”按钮。
步骤9:单击选中图表右侧出现的“次坐标轴垂直(值)轴”,使用Delete将其删除。
步骤10:适当调整图表的大小及位置。