张文是文轩公司的销售部助理,负责对全公司的销售情况进行统计分析,并将结果提交给销售部助理。年底,她将根据各门店提交的销售报表进行统计分析,请你帮助张文完成此项工作。
(1)在考生文件夹下,将“Excel素材.xlsx”文件另存为“Excel.xlsx”(“.xlsx为拓展名),后续操作均基于此文件,否则不得分
考点1:另存为
按住键盘上的f12按钮,将名称修改为“Excel.xlsx”
注意:确保路径是在考生文件夹下
(2)将 “sheet1”工作表命名为“销售情况”,将“sheet2”命名为“平均单价”
考点2:重命名工作表
双击或右键选择“重命名”,将sheet1改为“销售情况”,将sheet2改为“平均单价”
(3)在“店铺”列左侧插入 一个空列,输入列标题为“序号”,并以001、002、003……的方式向下填充该列到最后一个数据行
考点3:自动填充
鼠标放在A列的列标上,右键选择插入,就会在左侧插入一个空列。
输入列标题:序号
注意:在常规格式下,输入001只会显示为1
选中“序号”下方的单元格,按住Ctrl+1快捷键,打开设置单元格格式对话框
方法1:选择文本,此时输入001则可以显示为001
方法2:选择自定义,在类型处输入000(0是数字占位符,代表显示三个数字)
双击该单元格右下角的填充柄,自动填充其余单元格。(假如此时显示的都是001,则点击自动填充柄按钮,选择“填充序列”)
(4)将工作表标题跨列合并后居中并适当调整其字体、加大字号,并改变字体颜色。适当加大数据表行高和列宽,设置对齐方式及销售数额列的数值模式(保留两位小数),并为数据区域增加边框线。
考点4:调整字体字号、行高列宽、设置单元格格式、设置边框
①将工作表标题跨列合并后居中
选中工作表标题,点击合并后居中
注意区分合并后居中和跨列居中,跨列居中不会合并单元格
②调整字体、加大字号并改变字体颜色
③适当加大数据表行高和列宽
选中有数据的行/列,将鼠标放在任意两行/列中间,待鼠标变为黑色十字后,挪动鼠标,即可统一改变行高/列宽
④设置对齐方式及数额列的数值模式(保留两位小数)
框选有数据的区域→单击居中对齐
按住鼠标拖动选择,选中销售额这一列→设置单元格格式→分类选择数值,保留2位小数
⑤为数据区域增加边框线
Ctrl+A全选表格→所有框线
(5)将工作表“平均单价”中的区域B3:C7定义名称为“商品均价”。运用公式计算工作表“销售情况”中F列的销售额,要求在公式中通过VLOOKUP函数自动在工作表“平均单价”中查找相关商品的单价,并在公式中引用所定义的名称“商品均价”。
考点5:定义名称
选中“平均单价”工作表中的区域B3:C7
方法1:在左上角的名称框中命名为“商品均价”
方法2:找到公式选项卡下的“定义名称”按钮,在此修改命名
在公式选项卡下有“名称管理器”,在此处可以查看定义好的名称
使用VLOOKUP函数的时候,如果是已经定义过名称的区域,不需要再按F4锁定了
考点6:VLOOKUP函数的使用
销售额=销售量*单价
由于“销售情况”表中没有商品单价,因此要用VLOOKUP函数来查找
VLOOKUP(商品名称,“商品均价”,第几列,FALSE)
第二个参数“商品均价”不是简简单单的四个字,而是上一步中定义名称的区域。输入第二个参数时,在公式选项卡下,找到“用于公式”按钮,点击“商品均价”即可调取
(6)为工作表“销售情况”中的销售数据创建一个数据透视表,放置在一个名为“数据透视分析”的新工作表中,要求对各类商品比较各门店每个季度的销售额。其中:商品名称为报表筛选字段,店铺为行标签,并对销售额求和。最后对数据透视表进行格式设置,使其更加美观。
考点7:数据透视表
把鼠标放在数据区域的单元格内,切换到插入选项卡→点击数据透视表→选择数据透视表的位置:新工作表
将新工作表命名为“数据透视分析”
将商品名称拖动到筛选器;店铺拖动到行,季度拖动到列,销售额拖动到值
在数据透视表功能区下选择“设计”选项卡,应用一个数据透视表样式
(7)根据生成的数据透视表,在透视表下方创建一个簇状柱形图,图表中仅对各门店四个季度笔记本的销售额进行比较。
考点8:创建簇状柱形图
在数据透视表功能区下选择“分析”选项卡→点击数据透视图→选择簇状柱形图→在商品名称中仅选择笔记本
Ctrl+S快速保存