ExcelExpertDataAnalysisA.pdf

Excel专业技能（三）专业技能（三） 数据分析（上）- 纯数学计算 要点要点 简单的数据分析 数据排序 (Sort) 数据筛选 (Filter) 分类汇总 (Subtotal) 合并计算 (Consolidate) 使用数据透视表 (PivotTable) 数据排序数据排序 (Sort) 简单排序 按多列进行排序 使用自定义排序 避免排序后导致数据错误 数据排序：简单排序数据排序：简单排序 升序、降序 三个排序关键字 有无标题行 排序选项 Custom Sort Order: 自定义排序次序 Case Sensitive: 大小写字母敏感 Orientation: 方向（行、列） Method: 字母排序，笔划排序 Demo: Sort 数据排序：按多列排序数据排序：按多列排序 默认情况下，最多只能对三列数据进行 排序 若想对3个以上条件排序 先对最次要的3列数据进行排序 再对次要的列进行排序，依次类推 最后对最重要的列排序 Demo: Sort by 4 columns 数据排序：使用自定义排序数据排序：使用自定义排序 自定义序列：Tools Options Custom Lists 自定义序列：导入比输入好 特别提示：自定义序列除用于数据排序 外，还可用于数据有效性等其它功能上 Demo: Sort by custom order list 数据排序：避免排序后的错误数据排序：避免排序后的错误 排序的单元格中使用了公式，而公式引 用了排序区域外的单元格数据，请使用 绝对引用 对行排序，避免使用引用其它行的单元 格的公式 对列排序，避免使用引用其它列的单元 格的公式 Demo: Error 数据筛选数据筛选 (Filter) 筛选的实质：隐藏了不符合条件的行， 即将不符合条件的行的高度变为0 自动筛选 使用高级筛选 数据筛选：自动筛选数据筛选：自动筛选 多列组合筛选 如：生产部女员工的奖 金 Top 10 如：奖金最低的5位女员工 自定义条件筛选 如：奖金在 1000015000元之间的男性、姓张的员 工的奖金 复制和删除自动筛选后的数据：不影响 被隐藏的行 Demo: A01 数据筛选：使用高级筛选数据筛选：使用高级筛选 高级筛选可以 设置更复杂的筛选条件 将筛选出来的结果存放在不同位置 指定计算的筛选条件 筛选出不重复的记录项 设置条件区域 使用计算条件 数据筛选：使用高级筛选数据筛选：使用高级筛选 (2) 设置条件区域的要点 条件区域放在数据列表的上面或下面 条件区域通常至少要包含两行 第一行是列标题：建议复制 第二行是筛选条件 条件区域仅需含有与筛选有关的列 一个列标题可以多次出现在条件区域 列间运用“与”条件，行间运用“或”条 件 Demo: A02A03 数据筛选：使用高级筛选数据筛选：使用高级筛选 (3) 使用计算条件 条件区域使用空白标题或新标题，不允许 使用与数据列表中同名的字段标题 使用数据列表中首行数据来创建计算条件 的公式，数据引用要使用相对引用 如果计算公式引用到数据列表外的同一单 元格数据，公式中要使用绝对引用 Demo: A04 数据筛选：使用高级筛选数据筛选：使用高级筛选 (4) 筛选结果复制到其它地方（只能是当前 Worksheet） 选择不重复的记录 Demo: A05 分类汇总分类汇总 (Subtotal) 简单分类汇总 对某列字段使用一个以上的分类汇总 取消或替换当前的分类汇总 Demo: A01A02 合并计算合并计算 (Consolidate) 从不同的工作表或工作簿中提取数据进 行统一计算 适用场景：每月工资汇总、各分公司销 售业绩汇总等 Demo 数据透视表数据透视表 关于数据透视表 创建数据透视表 改变数据透视表的布局 数据透视表的刷新 设置数据透视表的格式 在数据透视表排序 数据透视表的项目组合 在数据透视表中执行计算 获取数据透视表数据 数据透视表：概述数据透视表：概述 有机结合了排序、筛选、分类汇总等数 据分析的优点 快速分析大量数据的交互式动态表格 解决函数公式速度瓶颈的有效手段 数据透视表：创建数据透视表：创建 Step 1: 指定数据源的类型 Step 2: 指定数据源的位置（通常会自动识别数据源 所在区域） Step 3: 指定数据透视表的显示位置 本步骤中可使用对话框布局数据透视表 也可以使用PivotTable Field List来布局 创建动态的数据透视表 数据透视表完成后，如果数据源增加了新的行或列，即使刷 新，新增的数据仍无法出现在数据透视表中 所以可以定义名称来创建动态的数据透视表 定义名称时要用到函数OFFSET 数据透视表：重新布局数据透视表：重新布局 布局分四个区域：Page Field, Row Field, Column Field, Data Items 使用页面区域 如果不显示Page Field区域，在第一行前插入两行 空白行，便会显示（见下页图示） 显示页面区域中字段的汇总：如销售地区和季度 分页显示功能：如将不同销售地区的汇总数据自 动生成并显示在不同的Worksheet上（见下页图 示） 数据透视表：重新布局数据透视表：重新布局 (2) 数据透视表：重新布局数据透视表：重新布局 (2) 整理字段 整理复合字段：如同时汇总数量和金额， 可将此两项按行或按列显示 重命名字段：如将“Sum of 数量”改成 “总数量” 注意：数据透视表中每个字段的名称必须 唯一，Excel不接受同名的两个字段 数据透视表：重新布局数据透视表：重新布局 (3) 整理数据 显示/隐藏数据项：点击字段右侧的小箭头，可选 择/取消选择部分或全部数据项 隐藏页字段数据：【方法1】双击页字段名称，在 弹出的对话框时操作；【方法2】将页字段拖至行 区域，单击该字段下拉按钮，取消部分勾选，然 后再将该字段拖回页区域原位置。此时该字段的 选项由(All)变成了(Multiple Items) 清除数据透视表原有数据项：拖走该行字段，刷 新数据，再将该字段拖回行区域 数据透视表：刷新数据透视表：刷新 源数据发生变化后，数据透视表不会自 动刷新，只能手动刷新 在打开文件时刷新（在Table Options中 设置） 刷新时会引起所有链接在一起的数据透 视表进行刷新 刷新引用外部数据的数据透视表 定时刷新（亦仅对外部数据适用） 数据透视表：设置格式与排序数据透视表：设置格式与排序 设置格式：与普通单元格格式化相同， 自动套用格式或手动设置 排序：与普通数据排序方法基本相同 数据透视表：项目组合数据透视表：项目组合 组合指定项 举例：将销售表中除广州/上海/北京外的 其它所有地区统称为“其它地区”，并将 数据汇总 方法：选中除广州/上海/北京外的所有城 市，Right Click Group & Show Detail Group。然后自动增加一列“销售地区 2”，项目名称为“Group1” 数据透视表：项目组合数据透视表：项目组合 (2) 数字项组合 举例：将销售表中1、2季度的销售金额相 加，称为“上半年” 方法：光标移至数据透视表中销售季度的 数据单元格上， Right Click Group & Show Detail Group。然后修改Starting at（起始于）、Ending at（终止于）和By （步长）中的By为2 数据透视表：项目组合数据透视表：项目组合 (3) 日期项组合 举例：如将采购订单按年和月汇总 方法：光标移至采购订单数据透视表中订 单日期的数据单元格上， Right Click Group & Show Detail Group。然后修改 Starting at（起始于）、Ending at（终止 于）和By（步长）中的By为Year和Month 数据透视表：执行计算数据透视表：执行计算 对同一字段应用多种汇总方式 自定义计算 创建计算字段 (Calculated field) 如计算销 售人员的提成 光标移至销售人员数据单元格，点PivotTable菜单 - Formulas - Calculated Field 在Name处输入“提成”，在Formula处输入“= 销售金额*0.08”，点击Add按钮，则增加了一个 Field：提成 数据透视表：执行计算（数据透视表：执行计算（2） 创建计算项 (Calculated item) 如计算 每个销售人员电脑和笔记本之间的业绩 差异 光标移