EXCEL 案例.doc

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.数据的分类汇总 分类汇总是对数据列表指定的行或列中的数据进行汇总统计，统计的内容可以由用户指定，通过折叠或展开行、列数据和汇总结果，从汇总和明细两种角度显示数据，可以快捷的创建各种汇总报告。分类汇总为分析汇总数据提供了非常灵活有用的方式，它可以完成以下工作。 (1) 显示一组数据的分类汇总及总和。 (2) 显示多组数据的分类汇总及总和。 (3) 在分组数据上完成不同的计算，如求和、统计个数、求平均值（或最大值、最小值）、求总体方差等。 4.数据筛选数据筛选是一种用于查找数据的快速方法，筛选将数据列表中所有不满足条件的记录暂时隐藏起来，只显示满足条件的数据行，以供用户浏览和分析。Excel 提供了自动和高级两种筛选数据的方式。 (1) 自动筛选。筛选条件可以是： a. 全部。显示出工作表中的所有数据，相当于不进行筛选。 b. 前 10 个。该选项表示只显示数据列表中的前若干个数据行，不一定就是 10 个，个数可以修改。 c. 自定义。该选项表示自己可以自定义筛选条件。 d. 单个值。选择其中的某项内容，Excel 就会以所选内容对数据列表进行筛选。 (2) 高级筛选：对于筛选条件较多的情况，可以使用高级筛选功能来处理。使用高级筛选功能，必须先建立一个条件区域，用来指定筛选条件。条件区域的第一行是所有作为筛选条件的字段名，这些字段名与数据列表中的字段名必须一致，条件区域的其它行则输入筛选条件。 4.数据透视表和数据透视图 数据透视表是一种对大量数据快速汇总和建立交叉列表的交互式表格，不仅能够改变行和列以查看数据源的不同汇总结果，也可以显示不同页面以筛选数据，还可以根据需要显示区域中的明细数据。数据透视图则是一个动态的图表，它可以将创建的数据透视表以图表的形式显示出来。 (1) 数据透视表：创建好数据透视表之后，可以根据需要对它的布局、数据项、数据汇总方式与显示方式、格式等进行修改。 (2) 数据透视图：在数据透视图生成之后，也可以修改它的布局、隐藏或显示数据项、汇总方式和数据显示方式等等，对数据透视图的操作会同时修改对应的数据透视表。2.2.4 方案参考步骤 1. 销售记录表中数据列的计算 在计算这些数据列时，我们可以先在某单元格输入单值公式，再使用填充柄完成该列中其余单元格的计算。 (1) “销售单价”列的计算。 在销售记录表中单击单元格 F11 ，输入计算公式：“=VLOOKUP(A11,$L$3:$M$9,2,FALSE)” ，按回车键。该公式的含义为：在单元格区域“L3:M9”即销售价格表中查找单元格 A11 中某商品名称所对应的销售单价。第 3个参数 2表示返回销售价格表中的第 2 列。 使用填充柄“+”下拉至单元格 F40，即可完成所有商品销售单价的计算。 (2) “折扣”列的计算。 在销售记录表中单击单元格 G11 ，输入计算公式：“=IF(E11=$A$6,$B$6,IF(E11=$A$5,$B$5,IF(E11=$A$4,$B$4,$B$3)”，按回车键。该公式的含义为：如果单元格 E11 中商品销售数量大于等于销售折扣表中单元格 A6 所对应的数量，则返回单元格 B6 中的折扣率值；否则再将 E11 与 A5 比较，如果大于等于则返回 B5的值；否则再将 E11 与A4 比较，如果大于等于则返回 B4，否则返回 B3。 使用填充柄“+”下拉至单元格 G40，即可完成所有商品销售折扣的计算。(3) “销售金额”列的计算。 在销售记录表中单击单元格 H11，输入计算公式：“=(E11*F11)*(1-G11)”，按回车键。 使用填充柄“+”下拉至单元格 H40，即可完成所有商品销售折扣的计算。 在完成了这些数据列的计算之后，销售记录表的结果如图 3-12 所示。 图3-12销售记录表计算结果 图3-13销售统计表计算结果2. 销售统计 在销售统计表中单击单元格 M14 ，输入计算公式：“=SUMIF($A$11:$A$40,L14,$E$11:$E$40)”，按回车键。然后使用填充柄“+”下拉至单元格 M20，即可完成所有商品总销售数量的计算。该计算公式的含义为：返回单元格 L14 中的商品在销售记录表中对应的所有销售记录中的数量累加值。 用同样方法，在单元格N14输入计算公式：“=SUMIF($A$11:$A$40,L14,$H$11:$H$40)”，按回车键。然后使用填充柄“+”下拉至单元格 N20，即可完成所有商品总销售金额的计算。 在完成了这些数据列的计算之后，销售统计表的结果如图 3-13 所示。3. 数据排序 选中 Sheet2 中销售记录表的任一单元格，单击菜单栏中的“数据”“排序”，弹出如图 3-14 所示的“排序”对话框。这里我们在“首要关键字”下拉列表中选择“销售地区”，并将“升序”单选钮选中，“次要关键字”则选择“销售金额”，并设置为“降序”，然后单击“确定”按钮，得到排序后的结果，如图 3-15 所示。 图3-14排序对话框 图3-15销售统计表排序结果4. 数据筛选 (1) 查找“销售数量大于等于 400”的商品销售记录。 首先将 Sheet2 复制到 Sheet3 中，然后选中 Sheet3 中销售记录表的任一单元格，单击菜单栏中的“数据”“筛选”“自动筛选”，数据列表中标题行的各列中将分别显示出一个下拉按钮。单击“数量”列的下拉按钮，在弹出的下拉列表中选择“”，弹出“自定义自动筛选方式”，如图 3-16 所示，单击“确定”按钮，得到筛选后的结果，如图 3-17 所示。 图3-16“自定义自动筛选方式”对话框 图3-17自动筛选结果(2) 查找“销售数量大于平均销售数量”的商品销售记录。 首先将 Sheet2 复制到 Sheet4 中，然后在 Sheet4 中创建一个条件区域，输入高级筛选的计算条件。 建立计算条件须满足下列 3 条原则。l 计算条件中的标题可以是任何文本或空白，不能与数据列表中的任一列标相同。 l 必须以绝对引用的方式引用数据列表外的单元格。 l 必须以相对引用的方式引用数据列表内的单元格。 我们依次在单元格 J1（或任一空白单元格）中输入列标：“平均值计算公式”，单元格J2 中输入平均值计算公式“=AVERAGE(E3:E32)”，单元格 J9 中输入列标：“大于平均销售数量”其值需满足上述的第 1 条原则，单元格 J10 中输入计算条件公式“=E3$J$2” ，输入该公式须满足上述的第 2、3 条规则，E3 是数据列表中的单元格，因此只能使用相对引用的方式。J2 包含平均值公式，是数据列表之外的单元格，只能采用绝对引用的方式。 建立好计算条件之后，选中 Sheet4中销售记录表的任一单元格，单击菜单栏中的“数据”“筛选”“高级筛选”，弹出如图3-3所示的“高级筛选对话框” ，在列表区域中输入“A2:H32”（如果前面单击了销售记录表的任一单元格，系统会自动填入，否则需手工输入），条件区域中输入“J9:J10”，单击“确定”按钮，得到筛选后的结果，如图 3-18所示。 图3-18“高级筛选”对话框 图3-19“分类汇总”对话框 5. 各地区不同商品的销售数量汇总 首先将 Sheet2 复制到 Sheet5 中，然后在 Sheet5 中单击销售记录表的任一单元格，选择“数据”“分类汇总”菜单项，打开如图 3-19 所示的“分类汇总”对话框。 依次在“分类字段”下拉列表中选择“销售地区”作为分类字段，“汇总方式”下拉列表中选择“求和”作为汇总方式，“选定汇总项”列表中选择“数量”作为汇总项，单击“确定”按钮得到分类汇总后的结果，如图 3-20 所示。 图3-20分类汇总结果 图3-21 “布局”对话框6. 各地区不同商品的销售总金额统计 选中 Sheet2 销售记录表中的任一单元格，单击菜单栏中的“数据”“数据透视表和数据透视图”，弹出“数据透视表和数据透视图向导”对话框。该向导包括3 个步骤，第 1个步骤是数据源和报表类型的指定，在“所需创建的报表类型”中选择“数据透视图(及数据透视表)”单选钮，单击“下一步”按钮进入第 2 个步骤，这个步骤是数据源区域的选定，系统会自动选定“Sheet2!$A$2:$H$32”单元格区域，单击“下一步”按钮，进入第 3 个步骤。 单击“布局”按钮打开布局对话框，对话框的右半部分列出了数据源中的所有字段，可以将这些字段按钮拖放到左半部分图中的行、列、页和数据上。我们把“销售地区”字段拖放到“行”中，把“商品名称”字段拖放到“列”中，把“销售金额”字段拖放到“数据”中，如图 3-21 所示。 设置完毕后，单击“确定”按钮返回到“布局”对话框，然后单击“布局”对话框中的“确定”按钮返回到“步骤 3”对话框，单击“步骤 3”对话框中的“完成”按钮，系统自动生成数据透视表和图，并分别保存在 Sheet6 和 Chart1中，如图 3-22 和3-23 所示。 图3-22 生成的数据透视表 图3-23 生成的数据透视图2.2.5 总结 在本案例的学习中，我们需要掌握以下内容： 1. 利用自定义公式与常用函数自动计算部分数据列的值，在本案例中我们主要使用了 VLOOKUP、IF、SUMIF 等函数，这些函数在实际应用中使