销售表操作指导

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所示23图4（3）选择“数据”→“有效性”命令，打开“数据有效性”对话框。

选择“设置”选项卡，在“允许”下拉列表中选择“序列”，在“来源”中填写“=家电名称”单击“确定”按钮，如图5所示。

图56、对“进价”的自动查找填充。

相关知识点：VLOOKUP 函数VLOOKUP 函数的功能：查找数据区域首列满足条件的元素，并返回数据区域当前行中指定列处的值。

VLOOKUP 函数格式：VLOOKUP(lookup_value,table_array,col_index,range_lookup)参数1 lookup_value ：查找什么。

参数2 table_array ：查找区域。

参数3 col_index ：查找区域第几列的值。

参数4 range_lookup ：精确查找还是模糊查找？操作方法：（1）“价格”区域名称的定义选择“商品信息”工作表，选中家电名称、单位、进价、售价所在的区域；选择菜单“插入”→“名称”→“定义”命令，打开“定义名称”对话框，在名称框中输入“价格”后，单击“添加”“确定”，“价格”数据区域名称创建完成，如图6所示。

图6（2）用VLOOKUP函数查找家电的“进价”选择“销售记录”工作表，选中目标单元格E3，选择菜单“插入”→“函数”命令，打开“插入函数”对话框，在“或选择类别”下拉列表框中选择“全部”，在“选择函数”列表框中选择“VLOOKUP”，如图7所示。

(2) 单击“确定”按钮，弹出“函数参数”对话框，如图8所示。

由于要根据“销售记录”工作表中的“家电名称”(D列)在“商品信息”工作表中的“价格”区域查找“进价”，所以VLOOKUP函数的第一个文本框应该选择销售记录工作表中“家电名称”所在单元格“D3”，。

图7 “插入函数”对话框图8“函数参数”对话框对话框的第二个文本框有边的折叠按钮，选择菜单“插入”→“名称”→“粘贴”命令，打开“粘贴名称”对话框，选中粘贴名称“价格”，单击“确定”按钮，区域名称“价格”被插入到VLOOKUP函数第二个参数的位置，如图8所示。

对话框的第三个文本框，输入是VLOOKUP函数找到匹配家电名称所在行以后，该行的哪列数据被返回，由于“进价”在所引用“价格”区域中的第3列，输入数字3。

由于要求家电名称精确匹配，所以最后一个参数输入“false”。

单击“确定”按钮，可以看到VLOOKUP函数找到了“冰箱”的“进价”是“2900”。

利用填充柄，复制公式。

47、按照上面相同的方法，在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))56 “销售额”列的公式为：=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)三、用“分类汇总”统计“销售额”和“毛利润”在对“销售额”和“毛利润”进行统计之前，先创建3个“销售记录”工作表的副本。

操作步骤如下：右键单击“销售记录”工作表标签，在弹出的快捷菜单中选择“移动或复制工作表”，并选中“建立副本”复选框，建立“销售记录”工作表的3个副本“销售记录(2)”、“销售记录(3)”和“销售记录(4)”，如图9所示。

1. 利用“分类汇总，统计各店的家电“销售额”和“毛利润”“分类汇总”含有两层意思：按什么分类——“所在区”和对什么汇总——“销售额”和“毛利润”。

在进行“分类汇总”之前，应先对要分类的“所在区”列进行排序，目的是为了把“所在区”相同的记录放到一起，然后再对要汇总列的“销售额”和“毛利润”进行求和。

（1）在“销售记录(2)”工作表中，对“所在区”字段进行排序。

（2）用“分类汇总”统计各区的“销售额”和“毛利润”。

“数据”→ “分类汇总”命令，打开“分类汇总”对话框。

“分类汇总”对话框四、用“数据透视表”分析各区每种家电销售情况“分类汇总”的结果虽然给出了各个区每个家电店的家电“销售额”和“毛利润”，但还不能很直观地反映出各个区的家电销售情况，如果用Excel 中的“数据透视表”进行数据分析，就可以非常方便地解决这个问题。

图9 建立工作表副本1. 统计各个区每种家电的销售情况操作步骤如下：(1) 单击“销售记录”工作表数据清单中的任一单元格，在菜单栏中选择“数据”→“数据透视表和数据透视图”命令，打开“数据透视表和数据透视图向导”对话框。

(2) 单击“下一步”按钮，打开“数据透视表和数据透视图向导一3步骤之2”对话框，选择销售数据所在区域。

图7.18 “数据透视表和数据透视图向导之2”对话框(3) 单击“下一步”按钮，打开“数据透视表和数据透视图向导—3步骤之3”对话框。

(4) 单击“布局”按钮，打开“数据透视表和数据透视表视图向导一布局”对话框，将“家电名称”拖动到左边图形的“行”上，“所在区”拖动到“列”上，“销售额”拖动到“数据”区域中，如图所示。

图7.19 数据透视表和数据透视表视图向导一布局(5) 单击“确定”按钮，返回透视表向导对话框，选择“数据透视表显示位置”为“新建工作表”。

(6) 单击“完成”按钮，将数据透视表重命名为“销售统计”。

(7) 对“总计”列按“降序’’排序，可以找到“销售额”最大的家电，如图所示。

7在数据透视表中找出“销售额”最大的家电五、用“两轴线-柱图”比较“销售额”和“毛利润”两轴线一柱图是一种组合图表，组合图表使用两种或多种图表类型，以强调图表中含有不同类型的信息。

操作步骤如下：(1) 选中“销售记录(2)”工作表中的隐藏分类汇总明细数据后的数据，如图7.25所示。

图7.25 数据区的选择(2) 在“常用”工具栏中单击“图表向导”按钮，打开“图表向导-4步骤之l-图表类型”对话框，选择“自定义类型”选项卡，在“图表类型”列表框中选择“两轴线-柱图”。

(3) 单击“下一步”按钮，打开“图表向导-4步骤之2-图表源数据”对话框，在“系列产生在”中选择“列”。