EXCEL数据管理与分析-培训课件.ppt

Excel 数据管理与分析,制作：黄冠,Excel应用,数据管理：存放数据信息， 高效管理 准确、安全管理 数据运算：结果 公式 函数 数据分析：图表、透视 排序、筛选 分类汇总 数据透视表分析 单变量求解、模拟运算表、规划求解 数据图表化 数据共享与协作：运算、分析结果放到文本、幻灯片,相互关系：,运算是电子表格管理主线 数据通过运算的帮忙在管理，通过函数的帮忙来分析 管理和分析是电子表格的重点 协作是将office各种组件的整合,常用操作技巧,窗口冻结变拆分 可以利用单元格的右上角和右下角位置的选择钮,常用操作技巧,行或列的快速移动（shift+鼠标左键） 少量条目的选单（ait+下箭头）,常用操作技巧,排序 定位（只选一格） 点升降,数据管理中几个重要的指标,准确,安全,美观,规范,数据的规范,表格类型： 分类文本(上下相同的不同类别） 序列文本（上下不同的类别） 货币、日期、数值等（综合例表） 虽然组合到一起，却采用不同的管理方法。,编辑栏与单元格的差异,设置格式： 格式菜单 单元格 下拉选项 自定义：成为管理高手的有效工具,自定义的应用,记住四个符号： #-表示保存方式为数字 -表示保存方式为文本或数值 -表示固定的字符 0-表示占位 前缀： AVM-HT2011- # 后缀： 0 ：0.000 工号的制作：格式、自定义。（附表1：综合表）,财务问题,例：金额小写直接转大写加圆整 数字 特殊 大小写转换 自定义圆整 (即：DBNUM2$-804G/通用格式“圆整” ),1203002.02转换为 壹佰贰拾万零叁仟零贰元零贰分,=IF(F17=0,“,(TEXT(ROUNDDOWN(F17,0),“DBNum2“)&“元“&IF(ROUNDDOWN(F17,0)=F17,“整“,IF(ROUNDDOWN(F17*10,0)=F17*10,TEXT(RIGHT(F17*10,1),“DBNum2“)&“角整“,IF(ROUNDDOWN(F17*10,0)=ROUNDDOWN(F17,0)*10,“零“&TEXT(RIGHT(F17*100,1),“DBNum2“)&“分“,TEXT(RIGHT(ROUNDDOWN(F17*10,0),1),“DBNum2“)&“角“&TEXT(RIGHT(F17*100,1),“DBNum2“)&“分“),数据的准确,如何让表格的填写按条件要求规范操作 设置有效性 数据菜单 有效性 更改任何值与提示信息 整数是特殊小数： 即能做整数又能做小数时，设定为小数,有效性的应用,日期 ： “-”、“/”、“nian-yue-ri” 自定义“YYyy.mm.dd” 今天日期：“ctrl+;” 性别： 数据 有效性 序列 男女间用英文逗号（例 表）（同时可以选择现成表单）,年龄设置：综合表,有效性的应用,电话号码的设置： 身份证的设置： 选区 设置文本 自定义 = or(len(预输入格）=15，len(预输入格）=18）,相对地址与绝对地址,相对地址：A1, A2, B1, B2, . 在公式中使用相对地址引用 公式复制过程中引用地址（值）随位置而变 绝对地址：$A$1, $A$2, $B$1, $B$2, . 在公式中使用绝对地址引用， 公式复制过程中引用地址（值）保持不变。 例表：销售统计 （公式 =/ C5 SUM ( $C$4 : $C$7) 中C5使用了相对地址，所以 在计算其他部门时，可自动进行调整； 而求和区域使用了绝对地址“$C$4 : $C$7”，所以在计算时无 论哪个部门都会与相同的区域和进行相除。）,相对地址与绝对地址的应用,本表与跨表引用的方法 (附表1：相对与绝对地址） 选区 = 选表 预选单元格（横拉坚拽） 跨表去掉“”号,多表合并(分表汇总）,表相同 选区 =（选项） 第一表第一格 shift+最后格 最后表 第一表第一格 （附表1：多表合并1、2）,表不同 选区 合并计算 引用 添加 （附表1：多表合并3）,数据的安全,工具菜单 选项 安全性,工作表专业效果的美化,表头 竖排表头、斜线表头 工作表背景 格式、工作表、背景 单元格底纹 工具选项、视图、网格线 框线设置 实线框、虚框设置、三维效果,设置表格美化的经验, 不显示网格线 选用您独特的字体, 如 Tahoma, Verdana (公司另有规定除外) 表头, 标题和正文应选用不同的字号和图案以示区别 规范使用数字格式 没有必要把所有的数据都放在一张表内 尽量纵向安排数据, 以方便浏览 设置合适的显示比例, 以尽量在一屏显示全部数据 (Ctrl+ 鼠标滚动轮) 固定窗格 为每一张工作表命名一个有意义的名称 删除多余的工作表,数据的运算,所有的公式都以等号（=）开始 公式中可使用的运算符有 加（+）、减（-） 乘（*）、除（/） 乘方（ ） 括号（()）（）（） 运算规则和数学中的一样,公式和函数应用技巧, 在公式中尽量使用引用而少输入数值 可用鼠标点击单元格来输入引用，而不必手工输入 适当使用括号或空格以易于阅读和理解 当公式较长时，可用 Alt+Enter 强行换行，以便阅读 按F2 或双击单元格可进入公式编辑状态 可按F9 进行公式的重新计算 既可以复制公式，也可以复制公式的计算值,常用函数,SUM 求和 AVERAGE 求平均值 选项。 （附表2：函数表）,排名函数：rank,=rank(第一格，F4（第一格：最后格） F4：快速将相对地址到绝对地址的转换（附表2：函数表）,数值计数（统计）,（例表2计价与总价， 有多少个日期） =count(第一格：最后格） （例表2计价与总价， 有多少种类，不分文 本与数值）(物料数） （例表2计价与总价， 有多少个日期在 2006-7-6以后种类） =countif(第一格：最后格，2006-7-6),条件计数函数的应用,例：少数民族多少人（非汉族） =countif（第一格：最后格）， han) 注意：格不允许加符号 (附表2：人事表）=COUNTIF(D8:D27,“汉“) 例：年龄大于40且小于50岁（小学数学问题） =countif(预选格40) - countif(预选格50) （附表2：人事表）=COUNTIF(G8:G27,“40“)-COUNTIF(G8:G27,“50“) 高版本：已解决此问题（=countifs),日期相关计算,日期 以数字存储的（日期、格式、改常规 1900-1-1=1） 今天 today( ) 工龄 =round(today( )-入厂日）/365，0） 附表1：综合表. 注：round(四舍五入函数）,定位及快速输入,定位 (二次选择意思）例图表隐藏复制 Ctrl+G或编辑菜单、定位、定位条件 附表2：求平均值 “ctrl+回车”全选 (当然可以查找替换） 快速输入 选区、编辑栏输入、 “ctrl+enten” （标贴制作） 条件格式的应用 选区、格式、条件格式选项。 附表2：条件格式（绩效考评统计）,文本拆分与合并,文本的拆分 全选 数据 分列 分隔符号 空格 文本 附表2：拆分数据（工资条）其他选项的运用 好的管理就是把字段越精细越好 文本的合并 =预合并第一格 &- & 预合并后一格 原位置做复制 选择性粘贴 数值（便于引用） 注：（& 为文本相加函数）,提取函数(无空格拆分),Left:(左)、right(右)、mid(中) 例：zhang123 zhang：=left(提取项，提取字符数） 即:=left(zhang，5） 12：=mid(提取项，分隔位，提取字符数）即：=left(zhang，6，2） 地址的提取 =left(提取项，len(提取项）-提取数位） 例：身份证年龄 =mid(提取项，len(提取项）- 11，8） (附表2：提取),提取查找Vlookup 函数,关键词：提取、匹配、查找 一个值： （和） 一张表： （做对比） 匹配： （找到完全一样的） 提取： （需要的列数） （附表2：提取查找1、2） =VLOOKUP(A3,$A$3:$B$5,2,FALSE),公式错误值的理解,DIV 数字被零除 #NAME？ 在公式中引用的名称不存在 #VALVE 在数字公式中引用了文本项 #REF 在公式中引用了被删除了的单元格区域 #N/A 在公式计算的时候没有可用的信息 #NVM 在函数中使用了错误的函数,数据分析方法,条件格式 单元格数值条件、公式条件 排序 一般排序、高级排序 筛选 自动筛选、高级筛选 分类汇总 一级分类、多极分类 数据透视表 分类汇总、对分类字段分组、计算字段和计算项、数据透视的筛选功能,用数据透视表进行数据分析,对数据表生成数据透视表 对数据字段进行分类 对数据字段进行汇总 对分类字段进行多级分类 制作二维分类表 隐藏汇总行 对多个数据进行汇总 对分类字段进行分组 对数据透视表添加计算字段或计算项,数据透视表,透视表的作用:分类汇总 附表4：思考： 每个职位平均工资、平均年龄多少！ 形成透视表时特别注意： 保证标题完整、名称不重复、不能有合并格 （操作