电子产品销售统计表教学课件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中还可以继续创建多级分类汇总 在excel2007中可以创建嵌套的分类汇总 上面创建了按 产品店 的分类汇总 可以称之为一级分类汇总 如果希望再对 日期 进行分类汇总 则可按如下步骤操作 执行 数据 分类汇总 命令 在 分类字段 下拉列表框中选择 日期 表示以 日期 进行分类汇总 在 汇总方式 下拉列表框中选择 求和 在 选定汇总项 列表框中选择 毛利润 取消对 替换当前分类汇总 复选框的选择 单击 确定 按钮 结果如图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数据信息来筛选单元格区域 请使用 数据 选项卡上 排序和筛选 组中的 高级 命令 高级 命令的工作方式在几个重要的方面与 筛选 命令有所不同 它显示了 高级筛选 对话框 而不是 自动筛选 菜单 可以在工作表以及要筛选的单元格区域或表上的单独条件区域中键入高级条件 microsoftofficeexcel将 高级筛选 对话框中的单独条件区域用作高级条件的源 图7 29条件区域所在位置 1 如果用户要使用高级筛选 一定要先建立一个条件区域 条件区域用来指定筛选的数据必须满足的条件 要条件区域的首行中包含的字段名必须拼写正确 要与数据表中的字段名一致 条件区域中并不要求包含数据表中所有的字段名 只要求包含作为筛选条件的字段名 条件区域与数据表之间至少空一行 主要是用了与条件区域区分开 条件区域必须具有列标签 如图7 29所示 2 在列标签下面的行中 键入所要匹配的条件 要找的是电子产品销售额超过400000 或者是毛利润大于30