EXCEL_第6章 数据透视表和透视图.ppt

Excel数据处理与分析 第6章数据透视表和透视图 引言 我们在工作当中经常会碰到 将目标数据从大到小的排序排序可以得出某种趋势显示我们想要的结果 并且隐藏不需要的结果筛选就是显示想要的隐藏不想要的汇总出我们需要的数据分类汇总就是按类别统计计算 引言 以上方法的小总结 你的要求总是在不断的变化之中总是结合excel提供的不同的功能实现运算总是需要反复的撤销恢复源数据最终被你整理的 面目全非 工作效率低下 如何在不破坏源数据的情况下又能实时的比较随意的获取某些结果 数据透视表 初识透视表 数据透视表是集排序 筛选 组合 汇总等多个功能于一体的集合体它是一种交互式报表它有强大 灵活的处理数据的功能熟练掌握该项功能能给你的工作效率显著的提高透视表 可以透过放置在特定表 特定区域 上的数据 拨开它们看似无关的组合得到某些内在的联系 从而得到某些可供研究的结果 这就是透视表概念的由来 内容提要 一 概述 二 数据透视表 创建数据透视表数据透视表的操作 三 数据透视图 创建数据透视图数据透视图的操作 什么是数据透视表什么是数据透视图 6 1概述 数据透视表是Excel2007提供的一种交互式报表 是一种动态数据分析工具 可以根据用户不同的目的进行汇总 分析 浏览数据 得到想要的分析结果 在数据透视表中可以交换行和列来查看原数据的不同汇总结果 数据透视表 数据透视图则是将数据透视表中的数据图形化 能方便地查看比较 分析数据的模式和趋势 数据透视图 创建数据透视表的步骤更改数据来源刷新数据清除和删除数据透视表 6 2创建数据透视表 数据透视表的结构创建数据透视表的步骤 这4个区域都可以包容一个或多个源数据表中的字段信息 但是由于它们的位置不同 所以它们的名称和作用则完全不同 页区域行区域列区域值区域 在数据透视表的空框架中 一共有4个不同的区域 6 2创建数据透视表 数据透视表的结构 作用是分类 作用是汇总 作用是分类筛选 6 2创建数据透视表 创建透视表的步骤 准备数据源创建数据透视表 6 2 1创建数据透视表 准备数据源 透视表数据源 若要创建数据透视表 您必须连接到一个数据源 可以从下3种数据源创建数据透视表的方法 从Excel工作表中创建数据透视表从外部数据源创建数据透视表从合并计算多个数据区域创建数据透视表 6 2 1创建数据透视表 准备数据源 透视表数据源要求透视表对数据源规模的规定 6 2 1创建数据透视表 准备数据源 透视表数据源要求透视表数据源的格式要求 标准数据库数据表要求规范的行列构成的二维表原始数据表必须有列标题设置不存在合并单元格不存在空格 空行 空列 6 2 1创建数据透视表 创建数据透视表 创建数据透视表的步骤 准备好数据后 请单击数据中的任意位置在 插入 选项卡上的 表 组中 单击 数据透视表 然后再次单击 数据透视表 打开 创建数据透视表 对话框 选择数据 表 区域 及放置数据透视表的位置单击 确定 Excel将空的数据透视表添加至指定位置并显示数据透视表字段列表指定数据透视表框架中四个区域内的数据内容 6 2 1创建数据透视表 创建数据透视表 选择数据区域中的一个单元格 6 2 1创建数据透视表 创建数据透视表 进入 创建数据透视表 对话框选择数据 表 区域 及放置数据透视表的位置 单击 确定 Excel会将空的数据透视表添加至指定位置并显示数据透视表字段列表 以便添加字段 创建布局以及自定义数据透视表 6 2 1创建数据透视表 创建数据透视表 指定数据透视表框架中四个区域内的数据内容 6 2 1创建数据透视表 创建数据透视表 无论是哪个区域 操作都是相同的 都是将字段列表中的 字段名 拖拽至相应的位置即可 在拖拽这些字段至透视表时 是没有一个定式的 要根据数据分析的目的 选择需要的字段拖拽 但是由于各个区域的作用是不同的 在数据分析时 只有将数据拖到了合适的位置时 才能得到正确的结果 否则不会得到正确的结果 这确实需要些经验 指定数据透视表框架中四个区域内的数据内容 每个字段都可以构成筛选 具体使用哪个字段需要考虑实际问题的需求 例如地区筛选 目的是需要提供在不同地区的客户订单状况说明 行标签决定数据表需要显示那些明细数据内容 例如 应用客户字段作为行标签是为了说明每个客户的订单状况 选择作为列标签的字段 决定了每一行数据对象的信息需求 例如选择 客户 作为行标签 订单交期 作为列标签 是为了得到每个 订单交期 每个 客户 的情况 在数值区域内指定对由 行 列 标签所决定的全部数据所需要进行的数据计算 6 2 1创建数据透视表 创建数据透视表 6 2 2更改数据来源 创建完数据透视表后 还可改变其数据来源 步骤 单击数据透视表在 数据透视表工具 选项 数据 组中 单击 更改数据源 按钮打开命令列表 选择 更改数据源 命令 将打开 更改数据透视表数据源 对话框要使用其他的Excel表或单元格区域 单击 折叠 按钮 选取表或区域 6 2 3刷新数据 更改源数据将导致用于分析的数据发生变化 用户可以通过刷新报表 使用与原始数据连接信息类似的新数据来更新数据透视表或数据透视图 操作步骤 在 数据透视表工具 选项 数据 组中单击 刷新 按钮 弹出下拉命令列表 选择 刷新 或 全部刷新 命令来刷新数据 如图所示 6 2 4清除数据透视表 如果要将数据透视表框架中四个区域内的数据内容清除 然后重新开始设计布局 请使用 全部清除 命令 该命令可有效地重新设置数据透视表 但数据透视表的数据连接 位置和缓存仍保持不变 具体操作步骤如下 单击数据透视表 在 数据透视表工具 选项 操作 组中 单击 清除 然后单击 全部清除 6 2 5删除数据透视表 数据透视表作为一个整体 要删除整个数据透视表 具体操作如下 单击数据透视表在 数据透视表工具 选项 操作 组中 单击 选择 按钮打开命令列表 选择 整个数据透视表 命令 即选择了整个数据透视表按键删除即可 数据透视表的基本操作数据透视表的筛选和排序数据透视表的计算多重合并计算数据区域创建数据透视表 6 3数据透视表的基本操作 数据透视表的字段操作移动数据透视表及显示明细数据 筛选和排序数据透视表的项组合 6 3数据透视表的基本操作 6 3 1字段的操作 字段设置添加字段重新排列字段删除字段 数据透视表字段的操作 6 3数据透视表的基本操作 6 3 1字段的操作 创建数据透视表之后 可以使用数据透视表字段列表对字段进行操作 如添加字段 删除字段 重新排列等 默认情况下 数据透视表字段列表显示分为两部分 上方的字段部分 用于添加和删除报表字段 下方的布局部分 用于重新排列和重新定位字段 数据透视表字段的操作 数据透视表字段的操作字段设置 使用 字段设置 对话框可以控制数据透视表中字段的各种格式 分类汇总和筛选设置 字段设置在 数据透视表工具 选项 活动字段 组中 如图所示 6 3数据透视表的基本操作 6 3 1字段的操作 例如 按学院统计各类不同职称的论文篇数的平均值 6 3数据透视表的基本操作 6 3 1字段的操作 要将字段添加到报表 主要有以下几种方法 在 数据透视表字段列表 中的字段部分选中各字段名称旁边的复选框 字段放置在布局部分的默认区域 用鼠标右键单击字段名称 在 添加到报表筛选 添加到列标签 添加到行标签 和 添加到值 中进行选择 将该字段放置在布局部分的特定区域中 用户还可以单击并按住某个字段名 在字段部分与布局部分中的某个区域之间拖动该字段 数据透视表字段的操作添加字段 6 3数据透视表的基本操作 6 3 1字段的操作 例如 统计某个 客户 订购数量占总订购数量的百分比 6 3数据透视表的基本操作 6 3 1字段的操作 6 3数据透视表的基本操作 6 3 1字段的操作 用户可以通过使用 数据透视表字段列表 或利用 报表筛选 列标签 行标签 数值 4个区域之一来重新排列现有字段或重新放置这些字段 如图所示 用户还可以单击并按住字段名 在字段与布局部分之间以及不同的区域之间拖动该字段 6 3数据透视表的基本操作 6 3 1字段的操作 数据透视表字段的操作重新排列字段 要删除字段 可以在数据透视表中右键单击 使用快捷菜单中的删除命令删除字段 也可以在布局区域之一中单击字段名称 从打开的菜单中选择 删除字段 命令即可 或者清除字段部分中各个字段名称旁边的复选框 6 3数据透视表的基本操作 6 3 1字段的操作 数据透视表字段的操作删除字段 移动数据透视表用户如果要移动数据透视表 其操作步骤如下 单击数据透视表在 数据透视表工具 选项 操作 组中 单击 移动数据透视表 按钮 将显示 移动数据透视表 对话框在 选择放置数据透视表的位置 对话框中 如果选择 新工作表 则将数据透视表放入一个新的工作表 如果选择 现有工作表 则将数据透视表放入现有工作表 6 3 2移动数据透视表数据及显示明细数据 6 3 2移动数据透视表数据及显示明细数据 显示或隐藏明细数据为了便于查看 可以在一个操作中显示或隐藏到数据明细的任意级别 甚至数据明细的所有级别 具体操作方法 单击数据透视表在 数据透视表工具 选项 显示 隐藏 组中 单击相关按钮即可显示或隐藏相关项目 6 4数据透视表的排序和筛选 6 4 1筛选数据 通过筛选数据可以筛选出符合用户指定条件的数据 并隐藏不想显示的数据 在数据透视表或数据透视图中 筛选是累加式的 也就是说 每次增加筛选都是基于当前筛选并进一步减小显示的数据 筛选标签值筛选按选定内容筛选删除筛选 6 4数据透视表的排序和筛选 6 4 2排序数据 对数据进行排序是数据分析中不可或缺的组成部分 对列或行标签中的数据进行排序对值区域中的数据进行排序 6 4 3数据透视表中的组合项 在数据透视表中 可以组合数字项 组合日期或时间和组合选定项 6 5数据透视表的计算 数据透视表和数据透视图提供了多种计算方式 如果汇总函数和自定义计算不能满足自己的需要 则可利用Excel提供的计算字段和计算项中创建自己的公式 添加计算字段添加计算项显示公式列表编辑公式删除公式 6 5数据透视表的计算 计算字段与计算项简介 计算字段 通过对数据透视表内现有字段进行计算创建的一个字段 可以将计算字段看作是添加一个虚拟列到数据集中 计算项 通过对数据字段内现有的数据项进行计算所创建的一个数据项 可以将计算项看作是添加一个虚拟行到数据集中 有了计算字段和计算项 可以向数据透视表中插入公式 创建自己的自定义字段或者数据项 6 6多重合并计算数据区域创建数据透视表 要汇总各个单独工作表区域中的结果 可将单独工作表区域中的数据合并到主工作表的数据透视表中 单独工作表区域可与主工作表位于相同工作簿中 也可位于不同工作簿中 合并数据时 将对其进行组合 以便更加轻松地聚合数据 6 6多重合并计算数据区域创建数据透视表 不使用任何页字段合并数据使用单页字段合并数据 6 6多重合并计算数据区域创建数据透视表 单击任意一个单元格 启动 数据透视表和数据透视图向导 按 Alt D P 组合键 在向导的 步骤1 页面上单击 多重合并计算数据区域 然后单击 下一步 不使用任何页字段合并数据 6 6多重合并计算数据区域创建数据透视表 在向导的 步骤2a 页面上单击 自定义页字段 然后单击 下一步 6 6多重合并计算数据区域创建数据透视表 在向导的 步骤2b 页面上执行下列操作 选定包含汇总数据的工作表数据区域 然后单击 添加 如果单元格区域在另一个工作簿中 请先打开工作簿以便选择数据 所有汇总数据区域依次添加完后 在 请先指定要建立在数据透视表中的页字段数目 下 选择 0 然后单击 下一步 6 6多重合并计算数据区域创建数据透视表 在向导的 步骤3 页面 选择数据透视表的位置 然后单击 完成 6 6多重合并计算数据区域创建数据透视表 两个数据表合并计算后 其数据透视表如左图所示 可以通过单击鼠标右键 在快捷菜单中选择 数据透视表选项 汇总和筛选 取消 显示行总计 不显示行总计后的透视表如右图所示 6 6多重合并计算数据区域创建数据透视表 使用单页字段合并数据使用多页字段合并数据 6 7创建数据透视图 数据透视图是数据透视表的图形显示效果 创建数据透视图主要有以下两种方法 直接通过数据表中的数据创建数据透视图通过已有数据透视表创建数据透视图 6 7 1通过数据区域创建数据透视图 通过数据区域创建数据透视图的具体步骤如下 选择数据区域中的一个单元格在 插入 选项卡的 表 组中 单击 数据透视表 在命令列表中选择 数据透视图 命令 Excel会显示 创建数据透视表及数据透视图 对话框将打开 创建数据透视表及数据透视图 对话框 首先选择要分析的数据区域 再选择放置数据透视图的位置设置好参数后 单击 确定 按钮即可创建一个空的数据透视表及数据透视图采用与创建数据透视表相同的方法 在右侧的 数据透视表字段列表 中选择相应的字段 将其添加到下方的各列表框中 6 7 1通过数据区域创建数据透视图 6 7 1通过数据区域创建数据透视图 6 7 2通过数据透视表创建数据透视图 根据数据透视表的现有数据来创建数据透视图 具体操作步骤如下 单击所