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

《EXCEL数据管理与分析-培训课件.ppt》由会员分享，可在线阅读，更多相关《EXCEL数据管理与分析-培训课件.ppt（45页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、Excel 数据管理与分析 制作：黄冠Excel应用数据管理：存放数据信息，高效管理准确、安全管理数据运算：结果公式函数数据分析：图表、透视排序、筛选分类汇总数据透视表分析单变量求解、模拟运算表、规划求解数据图表化数据共享与协作：运算、分析结果放到文本、幻灯片相互关系：运算是电子表格管理主线数据通过运算的帮忙在管理，通过函数的帮忙来分析管理和分析是电子表格的重点协作是将office各种组件的整合 常用操作技巧 窗口冻结变拆分可以利用单元格的右上角和右下角位置的选择钮常用操作技巧行或列的快速移动（shift+鼠标左键）少量条目的选单（ait+下箭头）常用操作技巧排序 定位（只选一格）点升降数据管

2、理中几个重要的指标准确安全美观规范数据的规范表格类型：分类文本(上下相同的不同类别）序列文本（上下不同的类别）货币、日期、数值等（综合例表）虽然组合到一起，却采用不同的管理方法。编辑栏与单元格的差异设置格式：格式菜单 单元格 下拉选项 自定义：成为管理高手的有效工具自定义的应用记住四个符号：#-表示保存方式为数字 -表示保存方式为文本或数值 -表示固定的字符 0-表示占位 前缀：AVM-HT2011-#后缀：0：0.000工号的制作：格式、自定义。（附表1：综合表）财务问题例：金额小写直接转大写加圆整数字特殊大小写转换 自定义圆整(即：DBNUM2$-804G/通用格式“圆整”)1203002

3、.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),DBNu

4、m2)&分)数据的准确如何让表格的填写按条件要求规范操作设置有效性 数据菜单 有效性 更改任何值与提示信息 整数是特殊小数：即能做整数又能做小数时，设定为小数有效性的应用日期：“-”、“/”、“nian-yue-ri”自定义“”今天日期：“ctrl+;”性别：数据 有效性 序列 男女间用英文逗号（例 表）（同时可以选择现成表单）年龄设置：综合表有效性的应用电话号码的设置：身份证的设置：选区 设置文本 自定义 =or(len(预输入格）=15，len(预输入格）=18）相对地址与绝对地址相对地址：A1,A2,B1,B2,.在公式中使用相对地址引用 公式复制过程中引用地址（值）随位置而变绝对地址：

5、$A$1,$A$2,$B$1,$B$2,.在公式中使用绝对地址引用，公式复制过程中引用地址（值）保持不变。例表：销售统计 （公式=/C5 SUM($C$4:$C$7)中C5使用了相对地址，所以 在计算其他部门时，可自动进行调整；而求和区域使用了绝对地址“$C$4:$C$7”，所以在计算时无论哪个部门都会与相同的区域和进行相除。）相对地址与绝对地址的应用本表与跨表引用的方法(附表1：相对与绝对地址）选区 =选表 预选单元格（横拉坚拽）跨表去掉“”号多表合并(分表汇总）表相同 选区 =（选项）第一表第一格 shift+最后格 最后表 第一表第一格（附表1：多表合并1、2）表不同 选区 合并计算 引

6、用 添加（附表1：多表合并3）数据的安全工具菜单 选项 安全性工作表专业效果的美化 表头 竖排表头、斜线表头 工作表背景 格式、工作表、背景 单元格底纹 工具选项、视图、网格线 框线设置 实线框、虚框设置、三维效果设置表格美化的经验 不显示网格线 选用您独特的字体,如 Tahoma,Verdana(公司另有规定除外)表头,标题和正文应选用不同的字号和图案以示区别 规范使用数字格式 没有必要把所有的数据都放在一张表内 尽量纵向安排数据,以方便浏览 设置合适的显示比例,以尽量在一屏显示全部数据(Ctrl+鼠标滚动轮)固定窗格 为每一张工作表命名一个有意义的名称 删除多余的工作表数据的运算所有的公式

7、都以等号（=）开始公式中可使用的运算符有 加（+）、减（-）乘（*）、除（/）乘方（）括号（()）（）（）（）运算规则和数学中的一样公式和函数应用技巧 在公式中尽量使用引用而少输入数值 可用鼠标点击单元格来输入引用，而不必手工输入 适当使用括号或空格以易于阅读和理解 当公式较长时，可用 Alt+Enter 强行换行，以便阅读 按F2 或双击单元格可进入公式编辑状态 可按F9 进行公式的重新计算 既可以复制公式，也可以复制公式的计算值常用函数SUM 求和AVERAGE 求平均值 选项。（附表2：函数表）排名函数：rank =rank(第一格，F4（第一格：最后格）F4：快速将相对地址到绝对地址的

8、转换（附表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)

9、-COUNTIF(G8:G27,50)高版本：已解决此问题（=countifs)日期相关计算日期 以数字存储的（日期、格式、改常规 1900-1-1=1）今天 today()工龄 =round(today()-入厂日）/365，0）附表1：综合表.注：round(四舍五入函数）定位及快速输入定位(二次选择意思）例图表隐藏复制 Ctrl+G或编辑菜单、定位、定位条件 附表2：求平均值 “ctrl+回车”全选 (当然可以查找替换）快速输入 选区、编辑栏输入、“ctrl+enten”（标贴制作）条件格式的应用 选区、格式、条件格式选项。附表2：条件格式（绩效考评统计）文本拆分与合并文本的拆分 全选

10、数据 分列 分隔符号 空格 文本附表2：拆分数据（工资条）其他选项的运用好的管理就是把字段越精细越好文本的合并 =预合并第一格&-&预合并后一格 原位置做复制 选择性粘贴 数值（便于引用）注：（&为文本相加函数）提取函数(无空格拆分)Left:(左)、right(右)、mid(中)例：zhang123 zhang：=left(提取项，提取字符数）即:=left(zhang，5）12：=mid(提取项，分隔位，提取字符数）即：=left(zhang，6，2）地址的提取 =left(提取项，len(提取项）-提取数位）例：身份证年龄 =mid(提取项，len(提取项）-11，8）(附表2：提取)提

11、取查找Vlookup 函数关键词：提取、匹配、查找关键词：提取、匹配、查找 一个值一个值：（和）（和）一张表一张表：（做对比）（做对比）匹配：匹配：（找到完全一样的）（找到完全一样的）提取提取：（需要的列数）（需要的列数）（附表（附表2：提取查找：提取查找1、2）=VLOOKUP(A3,$A$3:$B$5,2,FALSE)公式错误值的理解公式错误值的理解DIV 数字被零除#NAME？在公式中引用的名称不存在#VALVE 在数字公式中引用了文本项#REF 在公式中引用了被删除了的单元格区域#N/A 在公式计算的时候没有可用的信息#NVM 在函数中使用了错误的函数 数据分析方法条件格式 单元格数值

12、条件、公式条件排序 一般排序、高级排序筛选 自动筛选、高级筛选分类汇总 一级分类、多极分类数据透视表 分类汇总、对分类字段分组、计算字段和计算项、数据透视的筛选功能用数据透视表进行数据分析对数据表生成数据透视表对数据字段进行分类对数据字段进行汇总对分类字段进行多级分类制作二维分类表隐藏汇总行对多个数据进行汇总对分类字段进行分组对数据透视表添加计算字段或计算项数据透视表透视表的作用:分类汇总 附表4：思考：每个职位平均工资、平均年龄多少！形成透视表时特别注意：保证标题完整、名称不重复、不能有合并格 （操作很简单：拖拽、加右键）数据 透视表格式设置说明：分类是标题，中间为汇总的结果小窍门：多维表中按数据格横拖至汇总格（坚列即变为横列）数据透视图表的应用数据透视图表的应用数据透视图表的应用题目：每年各季度不同地区每个城市总的和平均运费 （附表4：透视表）地区、城市（分类）运货费2次（汇总）日期（右键创建组或组合，改变选项）秒杀！学习和工作方法的确很重要谢谢大家！