excel在管理咨询中数据分析工具培训.ppt

excel在管理咨询项目中的应用 2014年4月 本次培训的目的 一般管理咨询公司员工级别本次培训的目的 掌握excel这一工具的基本使用方法 以及在项目中如何去应用 目录 excel的特点 excel电子表格系统具有强有力的数库管理功能 丰富的宏命令和函数 是一个强有力的数据分析 决策支持的工具 分析能力 数据库管理能力 图表能力 宏语言功能 对象连接和嵌入功能 excel的特点 连接和合并功能 使用excel首先要理解的概念 单元格公式相对引用与绝对引用 1 简单计算 日常使用频率最高的几个函数a 求和 d1 sum a1 c1 将两个不邻接的区域相加 则用公式对两个区域的求并f1 sum a1 b1 d1 e1 求和演示 excel的常规应用 excel的常规应用 1 简单计算 日常使用频率最高的几个函数b 平均值 average a2 a3 求平均值演示 excel的常规应用 excel的常规应用 1 简单计算 日常使用频率最高的几个函数c 乗法 d2 a2 b2 c2d 除法 d4 a4 b4 c4乗除法演示 excel的常规应用 excel的常规应用 1 简单计算 日常使用频率最高的几个函数e if函数 执行真假值判断 根据逻辑计算的真假值 返回不同结果 c3 if b3 60 及格 不及格 函数if可以嵌套七层if函数演示 excel的常规应用 excel的常规应用 2 转置功能 把横排的表变成竖排的表 把竖排的表变成一个横排的表 便于比较直观的观察数据 a 选择表格 复制b 切换到另一个表格中c 打开选择性粘贴对话 选择 转置 转置功能演示 excel的常规应用 excel的常规应用 3 相对引用和绝对引用相对引用 所引用的单元格和这个单元格距离不变的 即随公式的位置变化 所引用单元格位置也在变化 绝对引用 一直引用一个单元格 即随着公式位置的变化所引用单元格位置不发生变化引用在数据汇总中需要大量使用 但是不同的引用造成的结果不一样 要注意其中的使用区别 excel的常规应用 3 相对引用和绝对引用引用按照引用位置可以分为 表内引用 表外引用 工作簿外引用 设目前工作簿名称为a 另外一个为b 当前工作表为sheet1 sheet2 表内引用 日常使用最多的 例如c1 a1 b1表外引用 工作表sheet1 中c1 sheet2 a1 b1工作簿外引用 引用b工作簿 工作表sheet1 中的a1单元格相对引用a3 b xls sheet1 a1 a2绝对引用a3 b xls sheet1 a 1 a1 excel的常规应用 3 相对引用和绝对引用结论 谁前面带上了 符号 在进行拖动时 谁就不变 如果都带上了 在拖动时 两个位置都不能变 补充 灵活使用功能键f4 在单元格中输入 单击a1单元格 按f4键 则会依次显示a1 a 1 a 1 a1引用演示 excel的常规应用 4 常用公式 对同业对标指标计算排名 需要以下几步 拷贝指标的累计值 与本期值对应标注指标是否越大越好综合应用if rank公式 计算排名rank函数 返回一个数字在数字列表中的排位 rank number ref order rank函数演示指标排名计算 资产经营为例 4 常用公式 在表格或数值数组的首列查找指定的数值 并由此返回表格或数组当前行中指定列处的数值 在vlookup中的v代表垂直 示例 年龄 vlookup f3 b 3 c 9 2 false 对应函数hlookup vlookup函数应用举例 段位环比分析的计算步骤 用vlookup函数把a b c d e转化为4 3 2 1 0段位变化 本期段位值 上一周期段位值分值变化 段位变化 25计算演示 4 常用公式 汇总上表数据的步骤 拷贝各单项指标的排名用sumproduct函数计算第n名的指标个数 sumproduct array1 array2 array3 可计算符合多个判断条件下的指标个数 计算演示 5 绘制图表 对同业对标指标绘制横向比较的图表 需要以下几步 对各单位的指标值进行排序绘制图表 选择图标类型 显示数据表挑勾 图例去掉挑勾 绘制图表演示 6 宏的简单应用 宏是一段定义好的操作 它可以是一段程序代码 也可以是一连串的指令集合 宏的作用可以使频繁执行的动作自动化节省时间 提高工作效率 又能减少失误 开始录制宏 操作步骤 完成宏录制 运行宏 录制宏需要经常重复执行的某项任务 就可以把执行这些任务的步骤全部录制在宏里 把宏变为可自动执行的任务 执行宏的操作执行宏的命令在工具栏上设置宏按钮使用窗体控件使用图形对象执行宏 目录 excel基础知识培训 1 excel在数据分析中的应用 2 excel管理工具的制作 3 数据分析 数据分析是组织有目的的收集数据 分析数据 使之成为信息的过程 是为了提取有用信息和形成结论而对数据加以详细研究和概括总结的过程 数据分析的目的是把隐没在一大批看来杂乱无章的数据中的信息集中 萃取和提炼出来 以找出所研究对象的内在规律 excel是简单实用的数据分析工具 另外统计分析软件一般有jmp和minitab以及spss 不过spss在学校里用得多 minitab在企业里用处很广泛 主要是minitab方便 简单 实用 excel在数据分析中的应用 收集数据 整理数据 统计分析 报告中绘制图表 数据分析的步骤 找结论的支撑找问题的程度 mintab简单介绍 minitab是现代质量管理统计的领先者 全球六西格玛实施的共同语言 以无可比拟的强大功能和简易的可视化操作深受广大质量学者和统计专家的青睐 可以做一些回归 方差分析等较为复杂的数据分析 excel解决不了的 或解决起来比较复杂的时候 请使用minitab 目录 excel基础知识培训 1 excel在数据分析中的应用 2 excel管理工具的制作 3 excel管理工具的制作 项目中形成的excel工具案例介绍 结束语 把 以数据和