电子产品销售统计表教学课件PPT.ppt

项目7 电子产品销售统计,大连理工大学出版社,7.1项目的提出,小李在淄博市开了四家电子产品连锁店，为了提高管理水平，他打算用Excel工作表来管理销售数据。图7-l是他制作的各电子产品店的销售记录流水账表。其中“销售记录”工作表记录了2010年11月9日各连锁店的销售情况。现在小李希望统计出2010年11月9日各个店的电子产品的“销售额”和“毛利润”。这个可以用“排序”与“分类汇总”来实现。,7.1项目的提出,此外，他还希望能直观地反映出各个店的电子产品销售情况，如果用Excel中的“数据透视表”和“图表”进行数据分析，就可以非常方便地解决这个问题。最后，小李希望能查看到“毛利润”不小于4000元的电子产品的信息，可以用到Excel的“数据筛选”功能。销售记录表如图7-1所示。,图7-1“销售记录”工作表,7.2相关知识点,1记录排序利用数据中的排序命令，让工作表按照某关键字进行“升序”或“降序”排列。可以对一列或多列中的数据按文本(升序或降序)、数字(升序或降序)以及日期和时间(升序或降序)进行排序。还可以按自定义序列(如大、中和小)或格式(包括单元格颜色、字体颜色或图标集)进行排序。大多数排序操作都是针对列进行的，但是，也可以针对行进行。,2分类汇总分类汇总是指对工作表中的某一项数据进行分类，再对需要汇总的数据进行汇总计算。在分类汇总前要先对分类字段进行排序。3数据透视表数据透视表是一种交互式工作表，用于对现有工作表进行汇总和分析。创建数据透视表后，可以按不同的需要、依不同的关系来提取和组织数据。,4图表我们生活的这个世界是丰富多彩的，几乎所有的知识都来自于视觉。也许无法记住一连串的数字，以及它们之间的关系和趋势。但是可以很轻松地记住一幅图画或者一个曲线。因此由于使用图表，会使得用Excel编制的工作表更易于理解和交流。5记录筛选利用Excel的筛选功能可以只显示出符合筛选条件的行，而暂时隐藏其他行。,7.3项目实施,从前面的分析可以看出，本项目要解决如下几个问题：1先用“数据排序”按照“产品店”这一关键字段进行排序。2再用“分类汇总”统计出各连锁店中各种电子产品的“销售额”和“毛利润”。3用“数据透视表”分析各个店每种电子产品的销售情况。4制作一个“销售额和毛利润关系图”图表来比较“博山店”、“桓台店”、“张店店”和“淄川店”的销售。5用“数据筛选”筛选出毛利润大于等于4000元的产品信息。,7.3.1记录的“排序”,对Excel数据进行排序是数据分析不可缺少的组成部分。可能需要执行以下操作：将名称列表按字母顺序排列；按从高到低的顺序编制产品存货水平列表，按颜色或图标对行进行排序。对数据进行排序有助于快速直观地显示数据并更好地理解数据，有助于组织并查找所需数据，有助于最终做出更有效的决策。,7.3.1记录的“排序”,排序条件随工作簿一起保存，这样，每当打开工作簿时，都会对Excel表(而不是单元格区域)重新应用排序。如果希望保存排序条件，以便在打开工作簿时可以定期重新应用排序，最好使用表。这对于多列排序或花费很长时间创建的排序尤其重要。,下面小李要根据“销售记录”工作表统计出2010年11月9日各个连锁店的“销售额”和“毛利润”。在对“销售额”和“毛利润”进行统计之前，先创建3个“销售记录”工作表的副本。,创建“销售记录”工作表的副本，操作步骤如下：右击“销售记录”工作表标签，在弹出的快捷菜单中选择“移动或复制工作表”，并选中“建立副本”复选框，建立“销售记录”工作表的副本“销售记录(2)”，并用同样的方法建立副本“销售记录(3)”和“销售记录(4)”，如图7-2所示。在进行“分类汇总”之前应先对要分类的“产品店”列进行排序，目的是为了把“产品店”相同的记录放到一起，然后再对要汇总列的“销售额”和“毛利润”进行求和。,在“销售记录(2)”工作表中，对“产品店”字段进行排序，方法如下：1单击“销售记录(2)”工作表中的任意一个单元格。2执行【数据】【排序】命令，打开【排序】对话框，在【主要关键字】下拉列表中选择“产品店”，选中【升序】单选按钮(默认值)，如图7-3所示。若要依据一个以上的字段来排序，可以单击【添加条件】按钮来增添附加列。对于每个关键字，再选择【升序】或者【降序】，以指定该列值的排序方式。如果在数据清单中的第一行中包含列标记，在“数据包含标题”前打上对勾，以使该行排除在排序行之外；若不选中“数据包含标题”，则该行也参加排序。,图7-2建立工作表副本,图7-3“排序”对话框,3单击【确定】按钮，则“销售记录(2)”工作表中的记录按“产品店”升序排序。4对数据进行排序，除了利用【排序】命令以外，还可以利用工具栏上的两个排序按钮“升序”和“降序”来进行。注意：执行【数据】菜单中的命令时，通常要先选择【数据清单】中的任一单元格，而不要选择一行或一列。检查所有数据是否存储为文本 如果要排序的列中包含的数字既有作为数字存储的，又有作为文本存储的，则需要将所有数字均设置为文本格式；否则，作为数字存储的数字将排在作为文本存储的数字前面。,7.3.2用“分类汇总”统计“销售额”和“毛利润”,前面已经学习了“排序”，但在数据表格中只知道这个操作是不够的。分类汇总是一种很重要的操作，它是分析数据的一个有力的工具，在Excel2007中使用分类汇总可以十分轻松地汇总数据。,1利用“分类汇总”统计各个店的电子产品“销售额”和“毛利润”“分类汇总”含有两层意思：按什么分类“产品店”和对什么汇总“销售额”和“毛利润”。,在“销售记录（2）”工作表中，用“分类汇总”计算各个店的电子产品“销售额”和“毛利润”。操作步骤如下：1）单击“销售记录(2)”工作表数据清单中的任意一个单元格。2）执行【数据】【分类汇总】命令，打开【分类汇总】对话框。3）在【分类字段】下拉列表框中选择“产品店”，在【汇总方式】下拉列表框中选择想用来汇总数据的函数。Excel提供的分类汇总好书有“求和”、“平均值”、“最大值”、“最小值”、“计数”等十多个，默认的选项是“求和”。此时应该选择【求和】，在【选定汇总项】列表框中选择“销售额”和“毛利润”，如图 7-4 所示。,图7-4“分类汇总”对话框,图7-5 按“产品店”汇总“销售额”和“毛利润”,4）单击【确定】按钮，单击分级显示符号，隐藏分类汇总表中的明细数据行，就可以看到如图7-5所示的分类汇总的结果。,工作表的左边显示了分类的层次，可单击层次号查看分类汇总的结果。本例中有3个层次：明细、分类汇总、总计。下面介绍分级视图中的各个按钮的功能。一级数据按钮：只显示数据表格中的列标题和汇总结果，该级为最高级。二级数据按钮：显示分类汇总结果，即二级数据。三级数据按钮：显示所有的详细数据，即三级数据。分级显示按钮：表示高一级向低一级展开显示。分级显示按钮：表示低一级折叠为高一级数据显示。,分级显示是相对汇总数据而言的，位于汇总数据的上面，就是数据表格中原始记录。单击按钮或按钮，出现，列出明细记录和汇总值；出现，只列出汇总值。对于不再需要或者错误的分类汇总，可以将之取消，步骤很简单，如下：选中分类汇总后的数据表格中的任意单元格。执行【数据】|【分类汇总】命令。在弹出的“分类汇总”对话框中单击【全部删除】命令即可实现。,7.3.3用“数据透视表”分析销售情况,分类汇总适合于按一个字段进行分类，数据透视表适合于按多个字段进行分类汇总，数据透视表主要用来分析不同字段数据间的关系。数据透视表一般由7部分组成：页字段、页字段项、数据字段、数据项、行字段、列字段和数据区域，它们的概念如下：页字段：在数据透视表中被指定为页方向的源数据表或表格中的字段。页字段项：源数据表或表格中的每个字段，列条目或数值都成为页字段列表中的一项。,7.3.3用“数据透视表”分析销售情况,数据字段：含有数据的源数据表或表格中的字段项。行字段：在数据透视表中被指定为行方向的源数据表或表格中的字段。列字段：数据透视表中被指定为列方向的源数据表或表格中的字段。数据区域：含有汇总数据的数据透视表中的一部分。数据项：数据透视表中的各个数据。,小李想知道各个店的电子产品销售情况，上面“分类汇总”的结果虽然给出了各个店的电子产品“销售额”和“毛利润”，但还不能很直观地反映出各个店的电子产品销售情况，如果用 Excel 中的“数据透视表”进行数据分析，就可以非常方便地解决这个问题。,数据透视表是一种交互式工作表，可以用于对现有工作表进行汇总和分析。创建数据透视表后，可以按不同的需要、依不同的关系来提取和组织数据。在建立数据透视表之前，先介绍数据透视表的数据源。,数据透视表的数据源是透视表的数据来源。数据源可以是Excel的数据表格，也可以是外部的数据表和Internet上的数据源，还可以是经过合并计算的多个数据区域以及另一个数据透视表。,1用“数据透视表”统计各个店每种电子产品的销售情况。1）单击【销售记录】工作表数据清单中的任一单元格，执行【插入】【数据透视表】|【数据透视表】命令，如图7-6所示。注意：一定要先选择【数据清单】中的任一单元格(不要选中一行或一列)此时，系统将自动选择销售数据所在区域。打开【数据透视表】对话框,如图 7-7 所示。,图7-6【插入】【数据透视表】,图7-7“数据透视表”对话框,图7-11 数据透视表,7.3.4用“图表”制作“销售统计图”,现在，小李想制作一个“两轴线-柱图”的图表来比较“博山店”、“桓台店”、“张店店”和“淄川店”的销售。操作步骤如下：1选中“销售记录（2）”工作表中的隐藏分类汇总明细数据后的数据，可按住Ctrl键依次选中“产品店”、“销售额”、“毛利润”等列，“总计”行不打算出现在图表中，可以不选，如图7-12所示。注意：在图7-12中，数据区是打算在图表中出现的区域，不用全部都选中。：,图7-12数据区的选择,注意：在图7-12中，数据区是打算在图表中出现的区域，不用全部都选中。2在【插入】选项卡的【图表】项中选择【柱形图】中的任意一种，结果如图7-13所示：,图7-13图表类型选择“柱形图”后生成的图表,3单击选中想设置为次坐标轴的系列，右击在弹出对话框中选择【设置数据系列格式】里选择【次坐标轴】。如图7-14所示：在“设置数据系列格式”对话框中，即可对图表的“系列选项”、“填充”、“边框颜色”、“边框样式”、“阴影”、“三维格式”等显示内容进行修改。调整图表的位置、大小与在Word文档中对对象的调整一样。实际上，对选定图表的移动、复制、缩放和删除操作与任何图形操作相同：拖动图表进行移动；Ctrl+拖动对图表进行复制；拖动8个方向的句柄之一进行缩放；按Delete键为删除。当然也可以通过“开始”菜单中的复制、剪切和粘贴命令对图表在同一工作表或不同工作表间进行移动和复制。,图7-14设置数据系列格式,4 对图表进行格式化设置，结果如图 7-15所示。,图7-15“两轴线-柱图”,5若要修改图表的布局，可以单击菜单栏中的【设计】选项卡中的【图表布局】，选择“布局2”，效果如图7-16所示：注意：在工作表里对用来制作图表的数据源中的数据进行修改，则图表会随之被修改。,图7-16选择“布局2”后的图表效果,7.3.5记录的“筛选”,利用Excel的筛选功能可以只显示出符合筛选条件的行，而暂时隐蔽其他行。执行【数据】|【筛选】命令可实现筛选。“筛选”命令有两个命令，即“筛选”与“高级筛选”。一般情况下，“筛选”命令能满足大部分的筛选需要。现在，小李想查看各个店“毛利润”超过4000元的产品信息。这就需要用到Excel的【数据筛选】，操作步骤如下：,7.3.5记录的“筛选”,1打开“销售记录（3）”工作表，任意单击一个单元格，执行【数据】【筛选】命令。此时，在数据清单中每一个字段名的旁边都显示出一个三角形的下拉菜单按钮。单击想要用于筛选的数列“毛利润”中的三角形下拉菜单按钮，可以看到一个下拉列表。如图7-17所示：,图7-17单击“毛利润”数列的下三角按钮,2从【数字筛选】后的【菜单】中选择【大于或等于】按钮时，会弹出【自定义自动筛选方式】对话框，如图7-18所示,图7-18“自定义自动筛选方式”对话框,在第一个下拉列表框中选择要使用的运算符，单击第二个下拉列表旁边的下拉列表按钮选中要使用的数值，或输入数据。例如，若要筛选以字母“J”开头的文本，请输入“J”，或者，若要筛选在文本中任意位置有“Bell”的文本，请输入“Bell”。如果需要查找某些字符相同但其他字符不同的文本，请使用通配符。以下通配符可以用作文本筛选的比较条件。若要查找任何单个字符可用“？”。例如，“Sm?th”可找到“Smith”和“Smyth”等；要找任何数量的字符可用“*”。例如，“*east”可找到“Northeast”和“Southeast”等。,若需符合两个条件，可通过选中【与】或【或】单选按钮建立复合条件，第二条件输入的方法同第一个条件输入的方法相同。条件输入完毕后，单击【确定】按钮，即可在工作表中看到【自定义】筛选结果。,7.4知识拓展,拓展1：在7.3.2中还可以继续创建多级分类汇总。在Excel 2007中可以创建嵌套的分类汇总。上面创建了按“产品店”的分类汇总，可以称之为一级分类汇总。如果希望再对“日期”进行分类汇总，则可按如下步骤操作：执行【数据】|【分类汇总】命令。在“分类字段”下拉列表框中选择“日期”，表示以“日期”进行分类汇总。在“汇总方式”下拉列表框中选择“求和”。在“选定汇总项”列表框中选择“毛利润”。取消对“替换当前分类汇总”复选框的选择。单击【确定】按钮，结果如图7-19所示，建立了两级分类汇总。,图7-19设置二级分类汇总的结果,拓展2：在7.3.3中如何设置数据透视表选项。右击数据透视表中数据区域的任意单元格，选择【数据透视表选项】命令，弹出“数据透视表选项”对话框，如图7-20所示。在这个对话框中可以根据需要进行设置，例如给数据透视表取个有意义的名字，并且让数据区域的空白单元格显示“0”，设置完成后单击【确定】按钮。,图7-20“数据透视表选项”对话框,拓展3：在7.3.3中虽然数据透视表具有较全面的分析汇总功能，但是对于一般使用人员来说，它显得布局太零乱，很难一目了然。而采用数据透视图，则可以让人非常直观地了解所需要的数据信息，也可以采用数据透视图的方式，来显示2010年9月份各个店的销售业绩的，而且操作非常简单。,图7-21数据透视图,1）单击“销售记录”工作表数据清单中的任一单元格，在菜单栏的【插入】选项卡下执行【数据透视表】|【数据透视图】命令，如图7-21所示。,2）从右侧面板中的“数据透视表字段列表”中的“产品店”、“销售额”、“毛利润”前分别打上对勾。最终效果如下图7-22所示。,图7-22运用“数据透视图”后的效果,3）此外，在“数据透视表字段列表”中，在“数值”中有“求和项：销售额”、“求和项：毛利润”都可以从旁边的黑箭头下拉菜单里，选择“值字段设置”修改它的汇总方式。如图7-23、7-24所示。4）为了使数据透视图更加美观，还可以应用某种精美的样式。在【格式】菜单下的“图表样式”组中选择一种样式，就会发现原来枯燥的数据透视图也可以很靓丽！,图7-23“数据透视表字段列表”,图7-24“值字段设置”对话框,拓展4：在7.3.4中若要改变图表类型，可在图表空白地方或在系列上，单击右键，选择【更改系列图表类型】选项，于对话框中选择相应的图表类型。如图7-25所示：,图7-25更改图表类型,也可以在菜单栏的【设计】选项卡下选择【更改图表类型】选项。如图7-26所示拓展5：若要更改数据源，在图表空白地方右击，选择【选择数据】，出现如图7-27所示的对话框。,图7-26【设计】选项卡,图7-27【选择数据源】对话框,拓展7：在7.3.5中若要通过复杂的条件可选择【高级筛选】。(条件：为限制查询结果集中包含的记录而指定的条件。)例如，欲查询销售额的值大于400000的记录或者毛利润大于30000数据信息来筛选单元格区域，请使用【数据】选项卡上【排序和筛选】组中的【高级】命令。【高级】命令的工作方式在几个重要的方面与【筛选】命令有所不同。它显示了【高级筛选】对话框，而不是【自动筛选】菜单。可以在工作表以及要筛选的单元格区域或表上的单独条件区域中键入高级条件。Microsoft Office Excel 将“高级筛选”对话框中的单独条件区域用作高级条件的源。,图7-29条件区域所在位置,1）如果用户要使用高级筛选，一定要先建立一个条件区域。条件区域用来指定筛选的数据必须满足的条件。要条件区域的首行中包含的字段名必须拼写正确，要与数据表中的字段名一致。条件区域中并不要求包含数据表中所有的字段名，只要求包含作为筛选条件的字段名，条件区域与数据表之间至少空一行（主要是用了与条件区域区分开）。条件区域必须具有列标签。如图7-29所示：,2）在列标签下面的行中，键入所要匹配的条件。要找的是电子产品销售额超过400000，或者是毛利润大于30000的电子产品所在信息，写法如上图，“30000”不能与“400000”同行，若在同一行，则表示“且”的关系，不同行表示“或”的关系。3）在【数据】选项卡上的【排序和筛选】组中，单击【高级】。打开如下图7-30所示的对话框。4）在“条件区域”框中，输入条件区域的引用，其中包括条件标签。也可单击“条件区域”文本框后的“折叠对话框”按钮，选择工作表中的引用区域，如图7-31所示。若要通过隐藏不符合条件的行来筛选区域，请单击“在原有区域显示筛选结果”。,在高级筛选中还能把结果复制到表的其他位置，即在工作表中既显示原始数据，又显示筛选后的结果，操作步骤与上面讲的相同，只需在“高级筛选”对话框中选中【将筛选结果复制到其他位置】单选按钮，并单击“复制到”文本框右侧的按钮，然后在工作表数据区域外单击任一单元格，结果如图7-32所示。,图7-30高级筛选对话框 7-31修改条件区域,图7-32复制后的筛选结果5）若要更改筛选数据的方式，可更改条件区域中的值，然后再次筛选数据。,75课后练习,图7-33学生成绩表,制作一个如上图所示的学生成绩表Sheet1。1）对表格中除“性别”“总分”列以外所有数据，在当前工作表中创建折线图.2）应用【设计】选项卡下的“布局10”给图表添加标题“学生成绩”，横坐标轴标题“姓名”，纵坐标轴标题“分数”。3）设置图表区格式：填充效果执行【渐变】|【预设颜色】|【漫漫黄沙】4）设置绘图区域格式：填充效果选择【纹理】|【新闻纸】。,2将学生成绩表复制一份到Sheet2中。1）按“性别”进行升序排序。2）“性别”相同的，按“总分”降序排。3）筛选出总分小于300及大于330的男生记录。3将Sheet1复制到Sheet3中。1）按“性别”进行升序排序。2）再原有分类汇总的基础上再汇总出男生和女生的人数。汇总结果放在“性别”数据的下方。,