《EXCEL案例食品公司销售分析.docVIP

Excel案例 食品公司销售分析 问题描述： 某食品公司主要经营几类干货的零售和批发业务，几类干货均有零售指导价，即销售单价，批发价根据购买数量的多少有相应的折扣。该公司销售部使用 Excel软件对销售情况进行管理，每月将销售数据记录在销售记录表中，如下图所示。请你帮助公司销售部对各类干货的销售记录进行计算和统计，完成下列操作： 1、按照下图表格中的内容，在EXCEL中将该表严格按照原表要求建立。 2、将“销售记录表”中的“销售单价”和“折扣”列的数据补充完整。 要求：使用VLOOKUP和IF函数完成，具体操作如下： (1) “销售单价”列的计算。 在销售记录表中单击单元格 F13 ，输入计算公式： “=VLOOKUP(A13,$F$3:$G$9,2,FALSE)”，按回车键。该公式的含义为：在单元格区域“F3:G9”即在“销售价格表”中查找单元格 A13 中商品名称所对应的销售单价。第3个参数 “2”表示返回“销售价格表”中的第 2 列的值。 使用填充柄“+”下拉至单元格 F30，即可完成所有商品销售单价的计算。 (2) “折扣”列的计算。 在销售记录表中单击单元格 G13 ，输入计算公式： “=IF(E13$A$4,$B$3,IF(E13$A$5,$B$4,IF(E13$A$6,$B$5,$B$6)))”，按回车键。该公式的含义为：如果单元格 E13 中商品销售数量小于“销售折扣表”中单元格 A4所对应的数量，则返回单元格 B3中的折扣率值；否则再将 E13与 A5 比较，如果小于A5的值则返回 B4的值；否则再将 E13 与A6比较，如果小于A6则返回 B5的值，否则返回 B6的值。 使用填充柄“+”下拉至单元格 G30，即可完成所有商品销售折扣的计算。 3、利用公式，计算出“销售记录表”的“销售金额”列的值。公式为：=(销售单价*销售数量)*(1-折扣)。 4、将“销售统计表”中的各类商品的“总销售数量”和“总销售金额”计算出来。 （1）“总销售数量”列的计算。 在销售统计表中单击单元格 K13 ，输入计算公式： “=SUMIF($A$13:$A$30,J13,$E$13:$E$30)”，按回车键。该公式的含义为：返回单元格 J13 中的商品在“销售记录表”中对应的所有销售记录中的“数量”累加值。 使用填充柄“+”下拉至单元格 K19，即可完成所有商品“总销售数量”的计算。 （2）“总销售金额”列的计算。 在销售统计表中单击单元格 L13 ，输入计算公式： “=SUMIF($A$13:$A$30,J13,$H$13:$H$30)”，按回车键，使用填充柄“+”下拉至单元格 L19，即可完成所有商品“总销售金额”的计算。 5、将“销售记录表”复制到 Sheet2中（点击A1单元格直接粘贴），并对sheet2表中的“销售记录表”根据“销售地区”升序和“销售金额”降序同时进行排序。 6、将“销售记录表”复制到 Sheet3中（点击A1单元格直接粘贴），并对 Sheet3 中的“销售记录表”进行自动筛选，找出“数量”大于等于 400的商品销售记录。 7、将“销售记录表”复制到 Sheet4中（点击A1单元格直接粘贴），并对 Sheet4 中的“销售记录表”进行分类汇总，汇总出各地区不同商品的销售数量。操作方法：在 Sheet4中先将“销售地区”列进行升序排列，可以使用数据的分类汇总功能，分类字段选择“销售地区”，汇总方式选择“求和”，汇总项选择“数量”，就可以汇总得到各地区不同商品的销售总数量。 8、将“销售记录表”复制到 Sheet5中（点击A1单元格直接粘贴），并对 Sheet5中的“销售记录表”进行数据透视表和数据透视图设计。具体操作如下： 选中 Sheet5销售记录表中的任一单元格，单击菜单栏中的“数据”→“数据透视表和数据透视图”，弹出“数据透视表和数据透视图向导”对话框。该向导包括3 个步骤，第 1个步骤是数据源和报表类型的指定，在“所需创建的报表类型”中选择“数据透视图(及数据透视表)”单选钮，单击“下一步”按钮进入第 2 个步骤，这个步骤是数据源区域的选定，系统会自动选定“Sheet5!$A$1:$H$20”单元格区域，单击“下一步”按钮，进入第 3 个步骤。 单击“布局”按钮打开布局对话框，对话框的右半部分列出了数据源中的所有字段，可以将这些字段按钮拖放到左半部分图中的行、列、页和数据上。我们把“销售地区”字段拖放到“行”中，把“商品名称”字段拖放到“列”中，把“销售金额”字段拖放到“数据”中，如下图。