Excel数据高效分析.pdf

Excel 数据高效分析 让报告更有说服力 培训课程 Excel 数据高效分析 让报告更有说服力 培训课程 学员专用教材 日程安排 日程安排 时时 间间 内内 容容 8 20 9 00 签到签到 9 00 10 30 Excel 数据高效分析 让报告更有说服力 一 数据高效分析 让报告更有说服力 一 10 30 10 45 课间休息课间休息 10 45 12 00 Excel 数据高效分析 让报告更有说服力 二 数据高效分析 让报告更有说服力 二 12 00 13 30 午餐午餐 13 30 15 30 Excel 数据高效分析 让报告更有说服力 三 数据高效分析 让报告更有说服力 三 15 30 15 45 课间休息课间休息 15 30 16 30 Excel 数据高效分析 让报告更有说服力 四 数据高效分析 让报告更有说服力 四 温馨提示 温馨提示 1 1 为保证课堂秩序 上课时请自觉将手机调整为静音或者震动状态 2 为保证课堂秩序 上课时请自觉将手机调整为静音或者震动状态 2 课上积极参与和讨论 教学相长 充分交流 3 课上积极参与和讨论 教学相长 充分交流 3 学习期间 有任何意见或建议 欢迎随时提出宝贵意见 学习期间 有任何意见或建议 欢迎随时提出宝贵意见 北京博思嘉业企业管理咨询有限公司 二 一二年七月二十六日 北京博思嘉业企业管理咨询有限公司 二 一二年七月二十六日 i Excel 数据高效分析 1 Part1 复杂条件下的统计方法 1 1 1 以一当十 数据库函数 D 函数 详解 1 1 1 1 函数的共同特点 1 1 1 2 数据库函数列表 1 1 1 3 数据库函数的参数含义 1 1 1 4 举例说明 2 1 1 5 将公式结果作为条件 2 1 2 利用频率函数统计数据的分布状况 3 Part2 利用数据透视表进行快速分析 3 2 1 多维度观察与分析数据 3 2 2 对数据进行筛选与多级汇总 3 2 3 Drill down 显示底层明细数据 4 2 4 对数据进行排序 4 2 5 观察数据在各种时间周期上的变动情况 4 2 6 利用分组分析数据的分布状况 4 2 7 局部与整体的比率分析 更好的理解数据的含义 4 2 8 利用计算字段进行账龄分析 5 2 9 利用透视表制作试算平衡表 5 2 10 进行销售趋势分析 6 2 11 生成透视图 进行动态图表分析 6 2 12 数据的自动更新 7 2 13 调用透视表数据制作灵活的分析图表 7 2 14 闪电汇总跨表数据 7 Part3 顶级商务图表的制作 8 3 1 选择恰当图表类型的原则 实例分析 8 3 2 制作图表的信噪比原则 8 3 3 亮出图表的观点 8 3 4 强化要说明的主题的方法 8 北京博思嘉业企业管理咨询有限公司 Excel数据高效分析 让报告更有说服力 培训 精彩资料与文章 www 5i 尽在我爱培训网1 ii 3 5 经典商务图表观摩与分析 8 3 6 专业商务图表的制作 9 3 6 1 让图表元素的布局更加合理 9 3 6 2 顶级商务图表的重要标志 配色 方法与工具 9 3 6 3 小改动 大起色 各类必要格式调整 9 3 6 4 控制线 趋势线等新的图表元素的添加 10 3 6 5 在图表上显示重要数据 10 3 7 不同数量级的图表比较 指数化 10 3 8 高速图表的途径 10 3 8 1 快速复制图表样式 10 3 8 2 将满意的图表存为模版 11 Part4 各类经典图表的制作 11 4 1 双坐标图表 11 4 2 用甘特图表示项目进程 12 4 3 动态图表观摩与思路分析 14 4 3 1 了解窗体的制作与使用方法 菜单 按钮 滚动条等 14 4 3 2 动态图表制作的总体思路 16 4 3 3 掌握动态图表制作的必要函数与工具 16 4 4 动态图表实战 17 4 4 1 任意选择业务与区域比较的动态图表 17 4 4 2 切换年度的费用分析图表 19 北京博思嘉业企业管理咨询有限公司 Excel数据高效分析 让报告更有说服力 培训 精彩资料与文章 www 5i 尽在我爱培训网2 Excel 数据高效分析 Part1 复杂条件下的统计方法 1 1 以一当十 数据库函数 D 函数 详解 在 Microsoft Excel 中包含了一些工作表函数 它们用于对存储在数据清单或数据库中的 数据进行分析 这些函数统称为数据库函数 Dfunctions 1 1 1 函数的共同特点函数的共同特点 这一类函数具有一些共同特点 1 每个函数均有三个参数 database field 和 criteria 这些参数指向函数所使用的 工作表区域 2 除了 GETPIVOTDATA 函数之外 其余十二个函数都以字母 D 开头 3 如果将字母 D 去掉 可以发现其实大多数数据库函数已经在 Excel 的其他类型函数中出 现过了 比如 DAVERAGE 将 D 去掉的话 就是求平均值的函数 AVERAGE 1 1 2 数据库函数列表数据库函数列表 在 Excel 包含的数据库函数及其应用为 1 1 3 数据库函数的参数含义数据库函数的参数含义 由于数据库函数具有相同的三个参数 因此笔者将首先介绍一下该类函数的几个参数 然后 再以具体示例来说明数据库函数的应用方法 该类函数的语法形式为 函数名称 database field criteria 北京博思嘉业企业管理咨询有限公司 Excel数据高效分析 让报告更有说服力 培训 精彩资料与文章 www 5i 尽在我爱培训网3 2 Database 为构成数据清单或数据库的单元格区域 数据库是包含一组相关数据的数据清单 其中包含相关信息的行为记录 而包含数据的列为字段 数据清单的第一行包含着每一列的 标志项 Field 为指定函数所使用的数据列 数据清单中的数据列必须在第一行具有标志项 Field 可 以是文本 即两端带引号的标志项 如 使用年数 或 产量 此外 Field 也可以是代表数 据清单中数据列位置的数字 1 表示第一列 2 表示第二列 等等 Criteria 为一组包含给定条件的单元格区域 可以为参数 criteria 指定任意区域 只要它至 少包含一个列标志和列标志下方用于设定条件的单元格 1 1 4 举例说明举例说明 例 某果园的果树的高度 使用年数 产量与利润的统计数据表如图所示 希望求出 1 有多少种苹果树的树高在 10 16 英尺之间 2 苹果树与梨树的最大利润值是多少 3 高度大于 10 英尺的苹果树的最小利润是多少 4 苹果树的总利润 5 高度大于 10 英尺的苹果树的平均产量 6 果园中所有树种的平均使用年数 7 求苹果树和梨树产量的估算标准偏差 真实标准偏差 估算方差 真实方差 求解步骤 1 创建空白工作簿或工作表 将数据录入 Excel 中 2 由于第一问需要求解有多少种苹果树的树高在 10 16 英尺之间 因此建立查询条件高 度在 10 与 16 之间 3 利用函数 DCOUNT 求解满足条件的单元格数来计算高度在 10 16 英尺的苹果树的种类 公式为 DCOUNT A4 E10 使用年数 B1 G2 求出有一种苹果树满足条件 1 1 5 将公式结果作为条件将公式结果作为条件 与高级筛选的把公式结果作为条件使用方法相同 以下是注意事项 1 使用第 1 条记录的对应值引用列标题 北京博思嘉业企业管理咨询有限公司 Excel数据高效分析 让报告更有说服力 培训 精彩资料与文章 www 5i 尽在我爱培训网4 2 公式中的其他引用必须是绝对引用 3 定义数据库函数的第 3 个参数时 列标题为空 但不可以没有 4 直接键入文本为模糊匹配 以该文本开始的项目都会被选择 如果要精确匹配 则 需要输入 查询内容 5 可以使用以下通配符进行模糊匹配 单个字符 example p nk 将会查询 pink 和 pank 等 任意字符 example chin 将会查询 china 和 chinese 或 将会查询 或 example abc 将会查询 abc 1 2 利用频率函数统计数据的分布状况 对数据进行区间统计 此函数需要和数组公式一起使用 此函数是根据数值的大小划分区间 进行统计的 列出需要分析的工资区间 选中需要计算统计结果的单元格区域 输入函数 按下组合键 ctrl shift enter Part2 利用数据透视表进行快速分析 2 1 多维度观察与分析数据 透视表的优势在于其灵活性 通过鼠标拖放的简单动作即可重新组织数据 帮助我们从各个 角度来观察和分析数据 从而为经营决策提供数据支持 2 2 对数据进行筛选与多级汇总 选择 插入 数据透视表 选择正确的数据范围 将字段列表里的字段拖入下方 4 个相应区域内 北京博思嘉业企业管理咨询有限公司 Excel数据高效分析 让报告更有说服力 培训 精彩资料与文章 www 5i 尽在我爱培训网5 4 生成透视表后 将鼠标悬停在想要移动的字段上 鼠标左键按下 将字段拖放到其他区域 生成自己需要的数据显示 筛选数据 点击字段名称后面的下拉菜单 可以对字段内容进行筛选 2 3 Drill down 显示底层明细数据 双击需要查看明细的数据 在新的工作表上将显示构成此汇总数据的所有明细数据 2 4 对数据进行排序 右键菜单 排序 可以进行排序设置 2 5 观察数据在各种时间周期上的变动情况 1 将日期放入行区域 右键菜单 组合 2 在 步长 中选择需要的分组标准 2 6 利用分组分析数据的分布状况 1 将销售收入放入行区域 右键菜单 组合 2 在 步长 中选择需要的分组标准 2 7 局部与整体的比率分析 更好的理解数据的含义 利用数据透视表字段列表将销售收入两次拖入数据区域 将其中一个销售收入改为百分比显示 右键菜单 值字段设置 值显示方式 将数据显示方式改为 占同列数据总和的百分比 北京博思嘉业企业管理咨询有限公司 Excel数据高效分析 让报告更有说服力 培训 精彩资料与文章 www 5i 尽在我爱培训网6 在报表项目上输入新的名称可以修改项目名称 将报表项目拖拽到列区域位置 可以将数值和百分比改为按列排列 2 8 利用计算字段进行账龄分析 在 名称 框定义计算字段的名称 在 公式 框输入计算字段的计算公式 可以引用 字段 框中的字段 其中 小于 30 天的账龄的公式可以定义如下 IF AND 2008 6 1 欠款日期 0 2008 6 1 欠款日期 30 应收账款余额 0 其中 2008 6 1 为基准日期 2 9 利用透视表制作试算平衡表 将数据源表中的数据进行如下布局 即可生成试算平衡表 需要注意的是 如果默认出现的计算类型不是求和 而是计数 那么可以通过点击 右键 北京博思嘉业企业管理咨询有限公司 Excel数据高效分析 让报告更有说服力 培训 精彩资料与文章 www 5i 尽在我爱培训网7 6 值字段设置 选项将计算类型改为求和 2 10 进行销售趋势分析 可以添加趋势线等报表元素来进行趋势分析 2 11 生成透视图 进行动态图表分析 可以单独生成数据透视图 也可以基于现有的透视表生成透视图 如果已经有现成的透视表 通过点击功能区上的数据透视图按钮就可以生成一个透视图 生成透视图之后 在菜单栏上会出现几个新的菜单选项 可以帮助我们对透视图进行设置 透视图生成后 会有筛选窗格帮助我们进行显示内容的筛选 通过选择 分析 菜单 可以调用字段列表对透视图的结构进行调整 北京博思嘉业企业管理咨询有限公司 Excel数据高效分析 让报告更有说服力 培训 精彩资料与文章 www 5i 尽在我爱培训网8 如果有些数据无法在数据透视表内部分析 可以引用透视表的数据作为分析的基础 2 12 数据的自动更新 右键 数据透视表选项 打开文件时刷新数据 可以让报表始终有更新后的数据显示 2 13 调用透视表数据制作灵活的分析图表 在透视表内容进行运算不方便时 可以将透视表的数据调出到其他单元格区域进行运算 并 据此制作各种灵活的报表 2 14 闪电汇总跨表数据 1 对于分布在同一文件中的表格汇总 首先创建格式完全一致的汇总表的表结构 在汇总表需要汇总的项目上输入 sum 第 1 个 表 最后 1 个表 需要汇总数据的单元格 将该公式复制到其他需要汇总的单元格即可实 现整张表的汇总 2 对于分布在不同文件中的表格汇总 首先创建格式完全一致的汇总表的表结构 将光标置于起始报表项目上 选择 数据 合 并计算 引用位置 选择各个需要被合并的报表 点击 添加 直至所有需要被合并的报 表添加完成 北京博思嘉业企业管理咨询有限公司 Excel数据高效分析 让报告更有说服力 培训 精彩资料与文章 www 5i 尽在我爱培训网9 8 需要删除一张表格时 只需要在合并计算界面上选中该表格 点击删除按钮 当子表格数据发生变化需要更新汇总表数据时 只需执行 数据 合并计算 进入合并 计算界面后直接点击确定退出该界面即可完成刷新数据的操作 Part3 顶级商务图表的制作 3 1 选择恰当图表类型的原则 实例分析 图表的选择应该遵循越简单越好的原则 应放弃那些过于复杂的图表 图表的主要作用是给 阅读者留下一个快速的视觉印象 而非展现细节或数据的复杂程度 一般来说 饼图 用来展示局部与整体的比例关系 条形图 用于对比不同的项目 柱形图 用于时间序列上的对比 项目较少时 也用于频率分布 折线图 用于时间序列上的对比 项目较多时 面积图 折线图添加填充色之后的变体 散点图 用于相关性关系的展现 组合图表 用于表现多个数据系列时的选择之一 3 2 制作图表的信噪比原则 减少图表中的噪声 突出最重要的内容 去除不必要的格式与修饰 减轻阅读者的认知负担 3 3 亮出图表的观点 同一个图表 可以表达多种观点 我们应将需要表达的观点明确 这样才有利于图表的信息 传递 3 4 强化要说明的主题的方法 用色彩来进行区分和强调 添加必要的元素 比如箭头 线条等 来明确信息 3 5 经典商务图表观摩与分析 向世界顶级商务图表的制作者学习 先模仿 然后谋求超越 观摩顶级商务图表 是迅速提 高报表制作水准的一种有效方法 北京博思嘉业企业管理咨询有限公司 Excel数据高效分析 让报告更有说服力 培训 精彩资料与文章 www 5i 尽在我爱培训网10 获取商业周刊风格的图表样本 在 google 上搜索图片 chart site 3 6 专业商务图表的制作 3 6 1 让图表元素的布局更加合理让图表元素的布局更加合理 图表元素的布局应符合眼球测试得出的规律 3 6 2 顶级商务图表的重要标志顶级商务图表的重要标志 配色 方法与工具 配色 方法与工具 1 利用 colorpix 软件获取商业周刊图表配色 RGB 值 2 调整 Excel 色彩为商业周刊风格的彩色设置 图表工具 格式选项卡 形状填充 其他填充颜色 自定义 对其 RGB 的值进行 设定 3 6 3 小改动 大起色小改动 大起色 各类必要格式调整各类必要格式调整 1 将图表西文和数字字体改为 Arial 2 将横坐标刻度线删除 选中横坐标 选择 图表工具 布局选项卡 设置所选内容格式 主要刻度线类型 选择 无 3 数据系列分类间距改为 40 选中某数据系列 右键 设置数据系列格式 4 对数据排序后再制作柱形图 5 消除折线图两端的空白 选中横坐标 右键 设置坐标轴格式 将位置坐标轴改 为在刻度线之上选项 北京博思嘉业企业管理咨询有限公司 Excel数据高效分析 让报告更有说服力 培训 精彩资料与文章 www 5i 尽在我爱培训网11 10 3 6 4 控制线 趋势线等新的图表元素的添加控制线 趋势线等新的图表元素的添加 控制线通常为一条水平线 它可以是平均值线 也可以是预算线 或者某个特定的值 用以评估实际情况是否超过了平均水平或者达到了预算 并以直观的形式提供预警 制作方法 1 利用 average 函数得出样本数据的平均值 并以该数值在数据表中添加一列辅助数 据 然后利用数据表制作柱形图 2 选中红色 average 数据系列 调整其图表类型为折线图 即可得到水平的控制线 3 6 5 在图表上显示重要数据在图表上显示重要数据 可以为某些重要的数据 比如最大值 最小值 初始值或终止值设置为显示选项 让其显示 在图表上以引起重视 3 7 不同数量级的图表比较 指数化 用于比较不同数量级的变化趋势 用各期数据 基期数据之后得到的指数做图表 其中 基期是第一期数据 3 8 高速图表的途径 3 8 1 快速复制图表样式快速复制图表样式 如果需要制作一个和现有图表完全相同的图表 不需要重复对各类格式进行设置 只需复制 图表 选择性粘贴 格式 即可 北京博思嘉业企业管理咨询有限公司 Excel数据高效分析 让报告更有说服力 培训 精彩资料与文章 www 5i 尽在我爱培训网12 3 8 2 将满意的图表存为模版将满意的图表存为模版 如果希望将制作好的图表保存下来供今后调用 选择 另存为模版 调用时 选择 更改 图表类型 在 模版 类别中选择自己的模版即可 Part4 各类经典图表的制作 4 1 双坐标图表 利用下面的数据制作双坐标图表 选择 插入 菜单 选择图表区域中的柱形图 第一种子图表类型簇状柱形图 点击横坐标上 计划数 右侧位置 则可以选中完成率的数据系列 点击右键 在右键菜单中选择 设置数据系列格式 北京博思嘉业企业管理咨询有限公司 Excel数据高效分析 让报告更有说服力 培训 精彩资料与文章 www 5i 尽在我爱培训网13 12 在数据系列格式界面上 将系列绘制在由 主坐标轴 改为 次坐标轴 再次选中系列 完成率 右键菜单选择 更改系列图表类型 选择折线图 点确定 双坐标图表制作完成 4 2 用甘特图表示项目进程 制作甘特图 需要以下 4 列数据 首先制作堆积条形图 然后点击 设计 菜单下的 选择数据 选中 结束日期 系列 按 删除 按钮将其删除 北京博思嘉业企业管理咨询有限公司 Excel数据高效分析 让报告更有说服力 培训 精彩资料与文章 www 5i 尽在我爱培训网14 在 布局 菜单下 利用当前所选内容功能区的下拉菜单 选择 垂直轴 然后点击 设 置所选内容格式 按钮 在出现的 设置坐标轴格式 对话框上 将 逆序类别 选项打勾 将横坐标轴交叉选项改 为 最大分类 选项 将纵坐标的项目排列次序反转 选中开始日期数据系列 右键点击 选择 设置数据系列格式 选项 数据系列格式对话框中将边框和填充都改选为无 北京博思嘉业企业管理咨询有限公司 Excel数据高效分析 让报告更有说服力 培训 精彩资料与文章 www 5i 尽在我爱培训网15 14 此时开始日期数据系列隐藏 此时需要将横坐标开始日期改为真正的项目开始日期 在 excel 中 每一个日期都对应一个数值 选中 B2 右键点击 选择 设置单元格格式 将其数字类型改为 常规 查看 2008 7 1 对应数值为 39630 点击 取消 退出 选中横坐标 点击右键 选择 设置坐标轴格式 切换到 刻度 将最小值改为 39630 同样将横坐标最大值改为真正的项目结束日期 39680 甘特图制作完成 4 3 动态图表观摩与思路分析 4 3 1 了解窗体的制作与使用方法 菜单 按钮 滚动条等 了解窗体的制作与使用方法 菜单 按钮 滚动条等 菜单 按钮 滚动条等效果的制作菜单 按钮 滚动条等效果的制作 选择 开发工具 插入 表单控件 选择 组合框 北京博思嘉业企业管理咨询有限公司 Excel数据高效分析 让报告更有说服力 培训 精彩资料与文章 www 5i 尽在我爱培训网16 按下鼠标左键 拖放出合适的大小后释放鼠标 出现下拉菜单形状 选中该下拉菜单 右键 选择 设置控件格式 在 控制 菜单上 去选择相应的数据源 三维阴影 选项是下拉菜单显示效果设定 按钮 窗体 的制作 选择 开发工具 插入 表单控件 选择工具条上的命令按钮 绘制出按钮的基本形状 后 自动跳出 指定宏 对话框 点击 新建 按钮 出现 VBA 编程界面 北京博思嘉业企业管理咨询有限公司 Excel数据高效分析 让报告更有说服力 培训 精彩资料与文章 www 5i 尽在我爱培训网17 16 在 Sub 和 End Sub 之间输入 sheets 要跳转的工作表名称 select 关闭该窗口 选 中该按钮 右键菜单 编辑文字 可以修改按钮名称 从窗体中获取数据到单元格并参与运算从窗体中获取数据到单元格并参与运算 单元格链接 该处指定的单元格中将存储在控件中被选中的项目在数据源中的序列号 我们经常利用该序列号引用控件选中的值 还可以利用该值制作动态图表 4 3 2 动态图表制作的总体思路动态图表制作的总体思路 利用查询函数 通常为 vlookup index offset 和窗体控件作为图表的组件 实现鼠标动作与 图表数据变化之间的联动效果 4 3 3 掌握动态图表制作的必要函数与工具掌握动态图表制作的必要函数与工具 1 VLOOKUP VLOOKUP 在表格或数值数组的首列查找指定的数值 并由此返回表格或数组当前行中 指定列处的数值 VLOOKUP 中的 V 代表垂直 VLOOKUP lookup value table array col index num range lookup Lookup value 为需要在数组第一列中查找的数值 Lookup value 可以为数值 引用 或文本字符串 也可以理解为 两表共有的索引字段 Table array 为需要在其中查找数据的数据表 可以使用对区域或区域名称的引用 例如数据库或列表 必须使得共有字段位于该范围的第一列 col index num 为需要调转的数据位于第二个参数中定义的范围的第几列 必须为 单纯数值 range lookup 定义大致匹配或精确匹配 False 或 0 精确匹配 true 或忽略或 1 如果无法找到精确匹配的值 那么就查找并匹配比查找值小的最近似的值 2 Index Index 函数帮助我们在一个数据范围内找到符合条件的记录 Index 数据范围 所需查找的数据所在的行 所需查找的数据所在的列 可选 3 Offset Offset 函数通过制定起始位置和位移量 来获得对某个区域的引用 offset 起始点 需要移动的行 需要移动的列 范围的行数 范围的列数 4 定义名称 名称可以代表一个单元格或者一个单元格区域 或者是常量 公式 名称的定义 选中需要命名的单元格或区域 在界面左上角名称框中输入名称后回车 名称的删除 插入 名称 定义 选中需要删除的名称点击删除按钮 点确定 名称的引用 需要引用某单元格时输入该单元格的名称 名称 北京博思嘉业企业管理咨询有限公司 Excel数据高效分析 让报告更有说服力 培训 精彩资料与文章 www 5i 尽在我爱培训网18 第 1 步 选中需要命名的某个单元格或单元格区域 第 2 步 在左上角名称框输入命名后回车 删除已有命名 选择 插入 名称 定义 选中需要删除的名称 点击 删除 按钮 对公式进行命名 选择 插入 名称 定义 进入如下界面 在 当前工作簿中的名称 处输入公式的名称 在 引用位置 处输入公式内容 上图中公式的含义是 取以 A1 单元格为起始单元格 动 态扩展的 3 列宽的一个区域 其中 counta 函数的作用是计算非空单元格个数 当记录数 增加 该函数自动将单元格区域扩展为新增记录到达的范围 此名称既是一个公式的名称定 义 同时也是一个动态引用的名称 4 4 动态图表实战 4 4 1 任意选择业务与区域比较的动态图表任意选