销售表操作指导.docx
《销售表操作指导.docx》由会员分享,可在线阅读,更多相关《销售表操作指导.docx(11页珍藏版)》请在冰豆网上搜索。
销售表操作指导
销售表操作指导
Excel综合应用1——产品销售数据分析
任务提出
小王在湘潭市开了若干家家电产品销售连锁店,为了提高管理水平,他打算用Excel工作表来管理销售数据。
其中“商品信息”工作表给出了每种家电产品的“单位”、“进价”和“售价”,如图所示。
“销售记录”工作表记录了2010年10月6日各连锁店的销售情况。
图1“商品信息”工作表
图2“销售记录”工作表
目的:
统计各连锁店的销售情况,计算2010年10月6日的销售额和毛利润。
困难:
“销售记录”工作表中只记载了家电名称和销售数量。
每种家电的“进价”和“售价”必须去“商品信息”工作表中一个个查找。
这个工作量实在太大,而且还容易出错。
要求:
输入家电名称后,让Excel根据家电名称自动查找该家电的“单位”、“进价”和“售价”等信息。
其它要求:
为了提高效率、减少失误,前面的“销售记录”工作表改进,可以直接从列表中选择家电名称,并可以自动计算出顾客应交款额和返还款。
实现方法
一、“商品信息表”建立
操作关键:
新建EXCEL工作薄“销售表.xls”,在sheet2建立“商品信息表”
将工作表名“sheet2”重命名为“商品信息表”
标题要求“合并及居中”、
“序号”采用自动序列填充
表格加边框;文字内容上下、左右居中;行高调整合适高度
二、“销售记录表”的建立
(在sheet1中操作,并将“sheet1”重命名为“销售记录表”
1、建立表格“标题”和“表头”,格式要求同上
2、“日期”列可以用快捷键“Ctrl+;”输入当前日期。
3、对“所在区”利用“数据有效性”设置序列自动点击填充。
选中“销售记录表”的“所在区”的B3单元格或B列,然后在菜单栏中选择“数据”→“有效性”命令,打开“数据有效性”对话框。
选择“设置”选项卡,在“允许”下拉列表中选择“序列”,在“来源”中填写“雨湖区,岳塘区”(注意:
中间用英文半角逗号),单击“确定”按钮,如图3所示。
图3设置“所在区”列的数据有效性、
4、用同样的方法,可以对“店名”列进行数据有效性设置。
(板塘店,基建营店,建设路店,金桥店,解放路店,金都店,金湘潭店)
5、“家电名称”列可参照上面方法设置。
也可以先选择要定义的区域,然后在名称框中直接输入定义的名称。
(1)选择“商品信息”工作表,选中家电名称(B3:
B9)。
(2)点击“插入”→“名称”→“定义”命令,打开“定义名称”对话框,在名称框中输入“家电名称”后,单击“添加”“确定”按钮,图4所示
图4
(3)选择“数据”→“有效性”命令,打开“数据有效性”对话框。
选择“设置”选项卡,在“允许”下拉列表中选择“序列”,在“来源”中填写“=家电名称”单击“确定”按钮,如图5所示。
图5
6、对“进价”的自动查找填充。
相关知识点:
VLOOKUP函数
VLOOKUP函数的功能:
查找数据区域首列满足条件的元素,并返回数据区域当前行中指定列处的值。
VLOOKUP函数格式:
VLOOKUP(lookup_value,table_array,col_index,range_lookup)
参数1lookup_value:
查找什么。
参数2table_array:
查找区域。
参数3col_index:
查找区域第几列的值。
参数4range_lookup:
精确查找还是模糊查找?
操作方法:
(1)“价格”区域名称的定义
选择“商品信息”工作表,选中家电名称、单位、进价、售价所在的区域;
选择菜单“插入”→“名称”→“定义”命令,打开“定义名称”对话框,在名称框中输入“价格”后,单击“添加”“确定”,“价格”数据区域名称创建完成,如图6所示。
图6
(2)用VLOOKUP函数查找家电的“进价”
选择“销售记录”工作表,选中目标单元格E3,选择菜单“插入”→“函数”命令,打开“插入函数”对话框,在“或选择类别”下拉列表框中选择“全部”,在“选择函数”列表框中选择“VLOOKUP”,如图7所示。
(2)单击“确定”按钮,弹出“函数参数”对话框,如图8所示。
由于要根据“销售记录”工作表中的“家电名称”(D列)在“商品信息”工作表中的“价格”区域查找“进价”,所以VLOOKUP函数的第一个文本框应该选择销售记录工作表中“家电名称”所在单元格“D3”,。
图7“插入函数”对话框图8“函数参数”对话框
对话框的第二个文本框有边的折叠按钮,选择菜单“插入”→“名称”→“粘贴”命令,打开“粘贴名称”对话框,选中粘贴名称“价格”,单击“确定”按钮,区域名称“价格”被插入到VLOOKUP函数第二个参数的位置,如图8所示。
对话框的第三个文本框,输入是VLOOKUP函数找到匹配家电名称所在行以后,该行的哪列数据被返回,由于“进价”在所引用“价格”区域中的第3列,输入数字3。
由于要求家电名称精确匹配,所以最后一个参数输入“false”。
单击“确定”按钮,可以看到VLOOKUP函数找到了“冰箱”的“进价”是“2900”。
利用填充柄,复制公式。
7、按照上面相同的方法,在G3单元格用VLOOKUP函数查找家电的“售价”。
8、用公式计算“销售额”、“毛利润”、“应收”和“找回”4列的值。
其中:
销售额=售价×数量
毛利润=(售价-进价)×数量
应收=销售额
找回=实收-应收
9、选择“销售记录”工作表中的“进价”、“售价”、“销售额”和“毛利润”等列,将其单元格数字格式设置为“货币”,小数位数设置为“1”。
10、使“销售记录”工作表更完美
上面制作的“销售记录”工作表使用起来虽然方便了很多,但还不是很完美。
因为有记录的行里面有很多错误值“#N/A”,让人看起来很不舒服。
利用IF函数和ISERROR函数的嵌套可以很容易地解决这个问题。
ISERROR(value)函数
函数的功能:
检测一个值是否为错误值。
函数的用法:
当变量value是错误值,如“#N/A”时,返回逻辑真(TRUE),否则返回逻辑假(FALSE)。
当与IF函数结合在一起使用时,可以检查公式是否为错误值。
如上图中,从第4行开始,“家电名称”和“数量”为空时,“进价(F4)”“售价(G4)”等是错误值“#N/A”,即ISERROR(F4)=TRUE。
要让F4是错误值“#N/A”时不显示错误值,而F4不是错误值时显示F4本身,可用IF函数与ISERROR函数的嵌套来实现,即:
=IF(ISERROR(F4),"",F4)
具体操作:
(1)因为F3=VLOOKUP(D3,价格,3,FALSE),所以使用IF函数与IsERROR函数的嵌套后,F3中的公式应写成:
=IF(ISERROR(VLOOKUP(D3,价格,3,FALSE)),"",VLOOKUP(D3,价格,3,FALSE))
(2)在“进价”列双击填充柄复制公式。
(3)按照同样的方法,用IF函数与ISERROR函数的嵌套,其它列的错误值(#Value)不显示。
“售价”列的公式为
=IF(ISERROR(VLOOKUP(D3,价格,4,FALSE)),"",VLOOKUP(D3,价格,4,FALSE))
“销售额”列的公式为:
=IF(ISERROR(G3*E3),"",G3*E3)
“毛利润”列的公式为:
=IF(ISERROR((G3-F3)*E3),"",(G3-F3)*E3)
“应收”列(即J3)中的公式为:
=IF(ISERROR(H3),"",H3)
“找回”列(即K3)中的公式为:
=IF(ISERROR(J3-K3),"",J3-K3)
三、用“分类汇总”统计“销售额”和“毛利润”
图9建立工作表副本
在对“销售额”和“毛利润”进行统计之前,先创建3个“销售记录”工作表的副本。
操作步骤如下:
右键单击“销售记录”工作表标签,在弹出的快捷菜单中选择“移动或复制工作表”,并选中“建立副本”复选框,建立“销售记录”工作表的3个副本“销售记录
(2)”、“销售记录(3)”和“销售记录(4)”,如图9所示。
1.利用“分类汇总,统计各店的家电“销售额”和“毛利润”
“分类汇总”含有两层意思:
按什么分类——“所在区”和对什么汇总——“销售额”和“毛利润”。
在进行“分类汇总”之前,应先对要分类的“所在区”列进行排序,目的是为了把“所在区”相同的记录放到一起,然后再对要汇总列的“销售额”和“毛利润”进行求和。
(1)在“销售记录
(2)”工作表中,对“所在区”字段进行排序。
(2)用“分类汇总”统计各区的“销售额”和“毛利润”。
“数据”→“分类汇总”命令,打开“分类汇总”对话框。
“分类汇总”对话框
四、用“数据透视表”分析各区每种家电销售情况
“分类汇总”的结果虽然给出了各个区每个家电店的家电“销售额”和“毛利润”,但还不能很直观地反映出各个区的家电销售情况,如果用Excel中的“数据透视表”进行数据分析,就可以非常方便地解决这个问题。
1.统计各个区每种家电的销售情况
操作步骤如下:
(1)单击“销售记录”工作表数据清单中的任一单元格,在菜单栏中选择“数据”→“数据透视表和数据透视图”命令,打开“数据透视表和数据透视图向导”对话框。
(2)单击“下一步”按钮,打开“数据透视表和数据透视图向导一3步骤之2”对话框,选择销售数据所在区域。
图7.18“数据透视表和数据透视图向导之2”对话框
(3)单击“下一步”按钮,打开“数据透视表和数据透视图向导—3步骤之3”对话框。
(4)单击“布局”按钮,打开“数据透视表和数据透视表视图向导一布局”对话框,将“家电名称”拖动到左边图形的“行”上,“所在区”拖动到“列”上,“销售额”拖动到“数据”区域中,如图所示。
图7.19数据透视表和数据透视表视图向导一布局
(5)单击“确定”按钮,返回透视表向导对话框,选择“数据透视表显示位置”为“新建工作表”。
(6)单击“完成”按钮,将数据透视表重命名为“销售统计”。
(7)对“总计”列按“降序’’排序,可以找到“销售额”最大的家电,如图所示。
在数据透视表中找出“销售额”最大的家电
五、用“两轴线-柱图”比较“销售额”和“毛利润”
两轴线一柱图是一种组合图表,组合图表使用两种或多种图表类型,以强调图表中含有不同类型的信息。
操作步骤如下:
(1)选中“销售记录
(2)”工作表中的隐藏分类汇总明细数据后的数据,如图7.25所示。
图7.25数据区的选择
(2)在“常用”工具栏中单击“图表向导”
按钮,打开“图表向导-4步骤之l-图表类型”对话框,选择“自定义类型”选项卡,在“图表类型”列表框中选择“两轴线-柱图”。
(3)单击“下一步”按钮,打开“图表向导-4步骤之2-图表源数据”对话框,在“系列产生在”中选择“列”。
(4)单击“下一步”按钮,打开“图表向导-4步骤之3-图表选项”对话框,设置如下:
图表标题为“销售额与毛利润关系图”;分类(X)轴为“所在区”;数值(Y)轴为“销售额”;次数值(Y)轴为“毛利润”,如图所示。
图表类型选择“两轴线-柱图”图表选项的设置
(5)单击“下一步”按钮,在“图表向导-4步骤之4-图表位置”对话框中选择“作为其中的对象插入”,单击“完成按钮,如图7.28所示。
在“图表位置”对话框
(6)结果如图所示。
格式化设置后的图表