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)  在分组数据上完成不同的计算，如求和、统计个数、求平均值（或最大值、最小值）、
求总体方差等。
sumif函数的使用方法绝对引用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 所示。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。