EXCEL 案例1.docVIP

Excel案例1 食品公司销售分析 2.2.1 问题描述 某食品公司主要经营各类干货的零售和批发业务，各类干货均有零售指导价，即销售单价，批发价根据购买数量的多少有相应的折扣。该公司销售部使用 Excel软件对销售情况进行管理，每月将销售数据记录在销售记录表中，如图 3-11 所示。请你帮助公司销售部对各类干货的销售记录进行计算和统计，完成下列操作： 1. 对 Sheet1 中销售记录表的“销售单价”和“折扣”列进行自动填充。 2. 利用公式，计算 Sheet1中销售记录表的“销售金额”列。 3. 统计各种商品的销售总数量和销售总金额，将结果保存在 Sheet1 中的“销售统计 表”中。 4. 对 Sheet2 中的销售记录表根据“销售地区”升序和“销售金额”降序排序。 5. 将 Sheet2 复制到 Sheet3，并对 Sheet3 中的销售记录表进行自动筛选，找出“销售 数量大于等于 400”的商品销售记录。 6. 将 Sheet2 复制到 Sheet4，并对 Sheet4 中的销售记录表进行高级筛选，找出“销售 数量大于平均销售数量”的商品销售记录。 7. 将 Sheet2 复制到 Sheet5，并对 Sheet5 中的销售记录表进行分类汇总，汇总各地区 不同商品的销售数量。 8. 根据 Sheet2，分别在 Sheet6 和 Chart1中新建数据透视表和图，统计各地区不同商 品的销售总金额。 图3-11食品公司销售记录表 2.2.2 解决思路 1. 销售记录表中各数据列的计算 (1) “销售单价”列的计算。可根据“销售价格表”中的“销售单价”列，使用 VLOOKUP 函数，自动填充到“销售记录表”中的“销售单价”列中。 (2) “折扣”列的计算。可根据“销售折扣表”中的折扣率，使用 IF 函数，将其折扣 率自动填充到“销售记录表”中的“折扣”列中。 (3) “销售金额”列的计算。可使用公式来计算，计算方法为： 销售金额=(销售单价*销售数量)*(1-折扣)。 2. 销售统计 可以使用 SUMIF 函数，统计出各种商品的销售总数量和销售总金额，并将结果保存在Sheet1 中的“销售统计表”中。 3. 数据排序 在 Sheet2 中，可以使用数据的排序功能，首要关键字选择“销售地区”，次要关键字选择“销售金额”，数据顺序分别选择降序和升序。 4. 数据筛选 (1) 查找“销售数量大于等于 400”的商品销售记录。在 Sheet3 中，可以使用自动筛选的自定义筛选方式，输入“销售数量=400”筛选条件，得到结果。 (2) 查找“销售数量大于平均销售数量”的商品销售记录。在 Sheet4 中，利用高级筛选的计算条件功能，建立条件区域进行高级筛选，得到结果。 5. 各地区不同商品的销售数量汇总 在 Sheet5 中，可以使用数据的分类汇总功能，分类字段选择“销售地区”，汇总方式选择“求和”，汇总项选择“数量”，就可以汇总得到各地区不同商品的销售总数量。 6. 各地区不同商品的销售总金额统计 可以使用数据的数据透视表和数据透视图功能，在 Sheet6 中新建数据透视表和数据透视图，选择“销售地区”作为行，选择“商品名称”作为列，选择“销售金额”作为数据，就可以统计出各地区不同商品的销售总金额。选择生成数据透视图（及数据透视表）即可生成数据透视图和表。 2.2.3 知识要点 1.Excel 常用函数 本案例中需要使用 VLOOKUP、IF、SUMIF 等常用函数进行数据列的计算。 (1) VLOOKUP 函数，是 Excel 最常用、最有效的查找函数，可以在一个表格或指定的 区域中查找某一指定的值，并由此返回该值对应当前行中指定列的数值。 (2) IF 函数，可以用来对某一条件执行的真假值进行判断，根据逻辑计算的真假值，返 回不同结果。如果结果为真，则返回一个真，如果为假，则返回另一值。 (3) SUMIF 函数，根据指定条件对指定的单元格区域求和。 2.数据排序 数据排序是按一定的规则对数据进行整理和排列，为进一步处理数据做好准备。Excel 2003 提供了多种对数据列表进行排序的方法，既可以按升序或降序进行排序，也可以按用户自定义的方式进行排序。 (1) 普通排序：可以选择主要关键字、次要关键字、第三关键字按升序或降序对数据列表进行排序。 (2) 自定义排序：可以按用户自定义的顺序进行排序，需要先建立一个自定义序列。 3.数据的分类汇总 分类汇总是对数据列表指定的行或列中的数据进行汇总统计，统计的内容可以由用户指定，通过折叠或展开行、列数据和汇总结果，从汇总和明细两种角度显示数据，可以快捷的创建各种汇总报告。分类汇总为分析汇总数