《利用ecel进行数据分析》由会员分享，可在线阅读，更多相关《利用ecel进行数据分析（73页珍藏版）》请在金锄头文库上搜索。
1、利用excel进行数据分析 目录 如何成为Excel高手 Excel常用技巧 让机器操作更智能 excel函数 让数据开口说话 如何成为excel高手 Excel的发展历史及内容 Excel的发展历史1 1979年美国青年DanBrick发明了世界上第一个电子表格Visicalc 成为众多人购买计算机的主要目的 2 1983年 美国莲花公司发布了Loutus1 2 3 将表格计算 绘图 数据库分析等功能集于一身 把电子表格软件推向一个巅峰 3 1985年美国微软公司推出了Excel第一个版本 即Excel1 0 随后便一发不可收 在1987年 1990年 1992年 1995年 1997年 1
2、999年 2000年 2002年 2003年 2007年 2010年 2013年和2016年发布了一系列的版本 完全统治了电子表格领域 Excel的发展历史及内容 Excel使用者层级 Excel使用者水平 刚刚接触或工作中使用较少 对Excel表格基本菜单和基本功能不太了解 可以在工作当中运用 大致了解了Excel表格的基本操作方法 会建简单的表格和图表 理解并熟练使用各个菜单命令 熟练使用数据透视表 至少掌握20个常用函数以及函数的嵌套运用 熟练使用各个菜单命令 熟练使用数据透视表 至少掌握20个常用函数以及函数的嵌套运用 能够在某个领取不断开发新的Excel用法的人 如何成为高手 Exc
3、el是个人电脑普及以来用途最广泛的办公软件之一 也是MicrosoftWindows平台下最成功的应用软件之一 在电子表格软件领域 Excel软件唯一的竞争对手就是自己 基于这样的绝对优势地位 Excel已经成为事实上的行业标准 Excel一向具有良好的向下兼容特性 无论你现在正在使用哪个版本的Excel 都不必担心现有的知识会很快被淘汰掉 在软件行业曾有这样一个二八定律 即80 的人只会使用一个软件的20 的功能 但Excel远不止于此比例 它最多有5 的功能被人们所常用 Excel特点 积极的心态所谓兴趣是最好的老师 压力是前进的动力 要想获得一个积极的心态 最好能对学习对象保持浓厚的兴趣
4、 如果暂时实在提不起兴趣 那么请重视来自工作或生活的压力 把它们转化为学习的动力 如何成为高手 Excel联机帮助 互联网 周围人群 书刊杂志 正确的学习方法正确的学习方法能使人不断进步 错误的方法则会让人收效甚微 Excel联机帮助是最有价值 最常用的资源 其知识点系统 而且例子丰富 一是搜索引擎 如Google Baidu等 二是论坛或者社区等交流平台 如ExcelHome等 书刊杂志主要是作为一个积累的过程 通过阅读 可以更多的了解Excel另外95 的功能 通过周围人群认识Excel的强大功能 以及Excel给日常工作带来的方便 从而产生学习的兴趣以及动力 如何成为高手 思考 实践结合
5、条条道路通罗马 我选择最近的一条 并很快地到达 Excel常用技巧 Excel单元格格式 快捷键 Ctrl 1Excel单元格格式种类多 包含常规 数值 货币 会计专用 日期 时间 百分比 分数 科学记数 文本 特殊 自定义等十多种格式 注意 虽然excel格式表现繁多 但基础格式只有两种 数值格式和文本格式 除文本格式以外 其他格式均为数值存在 也就是说可以参与数学运算 文本格式 在excel中 除文本字符使用文本格式外 对于不参与运算的 数值 也尽量多地使用文本格式 如各种工单号 电话号码 身份证号码等其他位数超过15位的 数值 数值格式 文本格式以外的其他内容 根据需求选择不同的表现形式
6、 两类格式的使用 Excel单元格格式 数值格式与文本格式的转换 数值转文本 用于参与匹配及截取有用信息 Ctrl 1 修改为文本Text函数 设置为文本格式 然后粘贴数据 分列 文本 文本转数值 参与计算 Ctrl 1 修改为数值隐式转换 参与数值不变的运算数据 分列 数值 自定义单元格格式 自定义格式 1 录入身份证号码2 尝试多种方式修改员工编码为文本格式3 修改 工单号 为文本格式4 将 工单时限 及 工单历时 修改为常规格式5 修改 开始时间 结束时间 为日期时间格式 yyyy mm ddhh mm ss 并判断工单历时是否正确 单元格格式转换练习 单元格文本换行与批量填充 单元格文
7、本换行Alt Enter 单元格批量填充Ctrl Enter 条件格式 条件格式可以根据excel单元格内容有选择地自动适应格式 达到直观体现数值规律的目的 设置方式 文件 条件格式 突出显示单元格规则 判断重复值 特定内容 标示一组单元格数值的特定范围数据条色阶图标集 条件格式可以帮表格操作人实现的工作 突出显示单元格规则 标示一组单元格数值的特定范围 规则设置及管理 排序 一般排序多条件排序按照特定字段排序排序的延伸 扩展 当前的区别 一般排序 注意 数据是否包含标题区别 多条件排序 自定义序列 按照特定字段排序 数据有效性与限制输入 数据有效性是对单元格或单元格区域输入的数据从内容到数量
8、上的限制 对于符合条件的数据 允许输入 对于不符合条件的数据 禁止输入 这样就可以达到验证数据正确有效性 避免错误或不规范的录入情形 实用条件 分发表格向多个对象搜集数据验证数据录入的准确性 过滤掉不规则数据 验证数据准确性 设置多级菜单 查找内容查找格式模糊查找 通配符 查找与替换 查找内容 查找格式 模糊查找 通配符 只想复制原始区域的数值而不复制公式粘贴具有公式的数据运算转置 选择性粘贴 分列 分列的作用 整理不规则数据强制修改数据格式 主要用于文本与数值转换 定位批注常量公式 数字 文本 逻辑值 错误值 空值对象可见单元格 复杂数据选择 定位 快捷键 CTRL G或F5 让excel更
9、智能 课前甜点 数据科学被炒过的概念 课前甜点 求多个单元格等级连接 IF A1 1 A IF A1 2 B IF A1 3 C IF A1 4 D IF A1 5 E IF A1 6 F IF A1 7 G IF A1 8 H IF A1 9 I IF A1 10 J IF A1 11 K IF A1 12 L IF A1 13 M IF A1 14 N IF A1 15 O IF A1 16 P IF A1 17 Q IF A1 18 R IF A1 19 S IF A1 20 T IF A1 21 U IF A1 22 V IF A1 23 W IF A1 24 X IF A1 25 Y
10、 IF A1 26 Z 实用范围 包括大量错综复杂数据的表格 希望快速整理出一份具有实际意义的报表希望找出同类数据在不同时期的某种特定关系希望对数据进行合理有效地分组需要经常查询分析数据的变化趋势数据源经常变化 然而又需要经常分析和处理最新的数据源 数据透视表 功能展示数据透视表字段列表数据透视表布局及样式数据源的刷新数据透视的组合制作动态数据透视表 数据透视表 数据透视表练习 1 请统计出每个部门工资是多少2 请统计出每个职位在各个部门的工资是多少3 请统计出每个职位都有哪些学历 每个学历都有多少人4 请统计出每个部门的工资占整体工资多少比例5 请对员工年龄分组统计 25 5 Excel公式
11、 Excel公式使得excel拥有运算能力和逻辑判断能力 让工作更智能高效Excel公式都以 开始 Excel运算符 算术运算符比较运算符文本运算符引用运算符 算术运算符 算术运算符返回结果为数值 比较运算符 比较运算符均为二目运算符 返回逻辑值 TRUE或FALSE 文本连接 返回连接后的文本 文本运算符 引用的意义 公式的自我复制 行列相对行列绝对行相对列绝对行绝对列相对 相对引用于绝对引用 财务函数日期时间函数 now today date datedif 文本函数 len left right mid text 查找引用函数 lookup vlookup index match cho
12、ose 逻辑函数 if and or not 数学和三角函数 sum count sumif s countif s 其他函数 函数 日期函数 datedif start date end date unit Y 时间段中的整年数 M 时间段中的整月数 D 时间段中的天数 MD start date与end date日期中天数的差 忽略日期中的月和年 YM start date与end date日期中月数的差 忽略日期中的日和年 YD start date与end date日期中天数的差 忽略日期中的年 now 返回现在的时间日期时间 today 返回今天的日期 date year month
13、 day 返回由给定的年 月 日数字组成的日期 文本长度 len随心截取 left right mid文本比较 exact文本查找 find文本替换 substitute转换大小写 lower upper文本格式转化 text 文本函数 len text 求单元格A1的文本字符长度 不区分单双字节字符集right text num chars 从一个文本字符串的最后一个字符开始返回指定个数 num chars 的字符 Left text num chars 从一个文本字符串的最开始一个字符开始返回指定个数 num chars 的字符 Mid text start num num chars 从
14、文本字符串中指定的起始位置起返回指定长度的字符 注意 num chars 不是截取的最终位置 而是截取的长度 文本截取函数 Find find text within text star num 返回一个字符串在另一个字符串中出现的起始位置 区分大小写 substitute text old text new text instance num 将一个字符串中的部分字符用新字符串替换Text value format text 根据指定的数值格式将数字转成文本 文本查找 替换函数 身份证号与年龄 身份证号110124198508162281 VLOOKUP lookup value table
15、 array col index num range lookup 查找与引用 vlookup Vlookup函数用于搜索表区域满足条件的元素 确定待检索单元格在区域中的行序号 再进一步返回选定单元格的值 默认情况下 表是升序排序的 Lookup value要查找的值KEY两个表共同的点在查询表中选择Table array查找的范围一般情况下全选即可在被查询表中选择 Col index num查询的数据所在列号相对于第一列 查询的值在第几列在被查询表中数Range lookup精确 模糊查询选择False即可 Vlookup查询不成功与不能查询 1 查找范围第一列不为要查找的关键列key2 偏
16、移量超过了查找范围的列数3 查询表与被查询表的关键列key格式不一致4 逆序查询 vlookup函数不能直接逆序 单可以使用构造逆序数组形式 如if及choose函数 5 vlokup只能纵向查询 不能横向 hlookup 6 不能多条件直接查询 直接多条件 Index matchLookup 查找与引用 更为灵活的查找 计数 count条件计数 countif s 求和 sum条件求和 sumif s 数学与三角函数 1 countif函数基本语法解析2 统计等于某值得单元格个数4 统计大 小 于某值单元格个数5 统计数值区间的单元格个数6 单字段模糊条件统计7 单字段并列条件统计个数8 统计文本数据个数9 统计非空数据个数10 统计不重复值个数 countif s countif s COUNTIF range criteria 计数范围 计数条件 COUNTIF E2 E20 本科 COUNTIF E2 E20 高中 大专 COUNTIF H2 H20 4500 sum COUNTIF H2 H20 300 4000 1 1 或 COUNTIF H2 H20 300 COUNTI
