搜索
提高统计分析工作效能国防科工局信息中心统计出王岳——Excel使用方法交流培训方法：1、不必记录，以实际操作为主；2、重点讲解基本规律和原则，修炼内功。探讨“Excel最多能做什么？”、”我会用了什么？”3、推荐自学资源，未来工作中积累和提高。数据收集运算处理数据分析图表展现Page3引言：为什么要强调Excel数据分析的能力一方面，数据是管理的基础，几乎每一项政策的出台都离不开如下三类分析数据：1.历史的：数据的历史趋势、增长变化情况。2.横向的：数据的横向对比和关联情况。3.结构的：分析问题的构成，找出主要影响因素。另一方面，管理决策对于数据的需求越来越灵活，越来越快速，企业的统计信息系统过于庞大和稳定，往往不能满足分析需要。因而，统计人员需要强大而灵活的分析工具——EXCEL。Page4培训目标：1.报表制作快速、规范、美观。杜绝制表中的手工重复劳动，统计人员减少加班；2.从此以后，掌握多个重要公式、多个快捷键操作；并在单位内部推广。3.掌握分析工具——数据透视表，开始规划个人业务的基础数据；Excel数据分析四个阶段报表设计和定义排序、筛选、汇总、公式数据透视表图形和描述数据收集运算处理数据分析图表展现第一章数据收集一、报表制作规范二、报表打印规范三、数据校验规则四、常用快捷操作数据收集运算处理数据分析图表展现Page7一、报表制作规范1.常用语定义报表的基本构成部分：标题、上方说明、行列表头、表体、表底说明。Excel报表术语定义：由大到小依次为：1)工作簿：指某一个Excel数据文件；2)工作表：指工作簿中某一个sheet页签中的表格；3)区域：由多个的单元格组成的范围；区域的分类：连续/非连续、已激活/未激活；4)单元格：存放数据的最小单元。第一章数据收集Page8菜单按钮按钮连续区域非连续区域工作表工作簿单元格Page9一、报表制作规范2.报表制作的常见错误不能漏掉的内容：1)时间范围。2)度量单位。3)统计口径。第一章数据收集统计口径时间说明计量单位Page10一、报表制作规范3.报表制作中的常见缺陷单元格排版规则：第一章数据收集类型位置说明文字说明居左便于突出文字层次属性字段居中为视觉留出空白数值居右便于直接分辨数量级Page11一、报表制作规范3.报表制作中的常见缺陷单元格格式设置：1)数据要统一小数位。2)数值单元格类型要正确设置：数值存成文本型将导致系统无法自动分析。3)要敢于使用日期型单元格：系统默认起始日期为1899-12-31，显示出数字41079恰好为截止到今天经过的天数。第一章数据收集Page12一、报表制作规范3.报表制作的常见缺陷(续)有两种格式上的小问题会造成大的错误1)未锁定行列表头：为阅读带来困惑。注意陷阱：资产负债表应在哪里锁定表头？2)未设置字体自动适应单元格大小：避免丢失信息，设置自动换行或缩小字体。注意陷阱：过长的内容被遮挡住。第一章数据收集Page13一、报表制作规范4.定义个人工作岗位专用的问题检查表1)按照统一规范设定检查项目。2)制作人自查，审核人复查，逐项打钩。3)检查后签字确认，责任清晰。第一章数据收集对于重要工作，建议由单位统一制定检查表规范。对于一般工作，个人心中要有一张自查表。检查表是历史经验的沉淀，集中反映了管理的精细程度。Page14二、报表打印规范1.设置打印区域：全选报表范围，设置为打印区域；2.页面设置：让Excel自动分页：设置为1页宽，不设定页高，则系统会自动分配纵向的分页。3.设置页边距、页眉页脚；4.设置标题行每页都打印：设置顶端标题行、设置左侧标题列。第一章数据收集Page15二、报表打印规范四步打印法：1.设置打印区域；2.设置Excel自动分页；3.设置下方页码；4.设置标题行每页都打印标题行、列。第一章数据收集Page16三、数据校验规则1.设定数据由下拉列表选择：选择一个区域，设置[数据]—[数据有效性]设置为序列：1)直接输入数据序列，以英文逗号间隔。2)引用本表内区域内容。3)引用其他表格区域内容，2003版本需使用indirect()函数或设置为名称；违反约束后的处理：不允许录入：设置为“停止”允许录入：设置为“警告”或“信息”。第一章数据收集Page17三、数据校验规则数据有效性设定：引用其他表格区域内容时，2010版本可以直接引用;2003和2007版本:引用时外加indirect(“Sheet2!A1:A10”)函数。第一章数据收集Page18三、数据校验规则2.数据有效性复制：复制一个区域的数据；在另一区域选择性粘贴——数据有效性验证；3.如何能不允许别人删除数据有效性：保护工作表+保护工作簿1)不允许其他用户编辑，则采取默认设置保护工作表保护。2)允许其他用户编辑的区域，设置【单元格格式】——【保护】——取消“锁定”的勾选；第一章数据收集Page19三、数据校验规则4.个人文档三层防护：1）保护工作表：设置部分单元格不允许读、写、复制2）保护工作簿：设置工作簿不能新增、删除、隐藏3）设置打开密码：[选项]——[安全性]设置打开文件所需密码以上方法“只防君子不防小人”，间谍可以通过底层代码轻易破解第一章数据收集Page20四、常用简便操作常用快捷键：记住三组常用快捷键操作区域选择：按住Shift选择最后一个单元格(工作表、文件)Ctrl+方向键：跳转到连续区域的边缘Shift+方向键：沿该方向连续选中单元格Ctrl+Shift+方向键：跳转到连续区域边缘的同时全部选中第一章数据收集Page21四、常用简便操作（一）数据录入：问题1：如何批量录入或填充单元格方法一：拖拽填充;方法二：双击填充;方法三：F2编辑第一个单元格再Ctrl+Enter。第一章数据收集Page22四、常用简便操作（一）数据录入：问题2：如何快速插入100行，新增10张表。重复操作：F4或Ctrl+Y第一章数据收集问题3：如何录入左右方向上很长的表格？使用[Tab]键向右跳转Page23四、常用简便操作（一）数据录入：问题4：如何快速将计量单位从“元”转换为“万元”。选择性粘贴：通过选择性粘贴可以进行简单的四则运算1.在任意单元格输入数字10000；2.复制该单元格；3.选中要转换的数据区域；4.右键“选择性粘贴”，勾选“除”，点击确定。则该区域数据全部除以10000第一章数据收集Page24四、常用简便操作（二）数据查找查找：可以通过选中区域来限定查找范围；*表示多个字符，?代替单个字符替换：不仅可以替换文本，而且支持公式内容的替换定位：可以定位到公式、空白、有效性第一章数据收集Page25四、常用简便操作：快捷键列表(1)第一章数据收集类型操作说明区域选择Ctrl+方向键跳转到连续区域的边缘Shift+方向键沿该方向连续选中单元格Ctrl+Shift+方向键跳转到连续区域边缘的同时全部选中文件操作Ctrl+A全选Ctrl+S，Ctrl+W保存，关闭Ctrl+N，Ctrl+O新建，打开Ctrl+Z，Ctrl+Y撤销、重做Ctrl+P打印单元格编辑F2编辑当前单元格Ctrl+~显示全部单元格的公式Ctrl+C；Ctrl+V复制、粘贴Ctrl+F；Ctrl+H查找、替换Ctrl+G或F5定位连续输入Alt+I+R插入行(插入列则输入C)Page26四、常用简便操作：快捷键列表(2)第一章数据收集类型操作说明单元格格式Ctrl+1打开单元格格式设置Ctrl+2或Ctrl+B字体加粗，再按一次取消Ctrl+3或Ctrl+i字体倾斜，再按一次取消Ctrl+4字体加下划线，再按一次取消Ctrl+5字体加删除线，再按一次取消Ctrl+9/Ctrl+0隐藏当前行/列Ctrl+Shift+9/0取消隐藏行/列双击格式刷可以一直持续刷，直到保存或取消页签跳转Ctrl+PageUp(Down)跳转到前一个(后一个)工作表Ctrl+Tab只在Excel工作簿文件之间切换Atl+PageUp(Down)在工作表内横向翻页快速输入Ctrl+分号输入当前日期Ctrl+shift+分号输入当前时间Page27四、常用简便操作：快捷键列表(2)第一章数据收集类型操作说明计算公式输入函数名后按Ctrl+A打开函数向导对话框引用单元格区域后按F4在单元格行列前加$符号，多按几下有惊喜Alt+“+”对上方、左侧区域自动求和Page28四、常用简便操作：总结第一章数据收集EXCEL中没有体力活！引出的一个大原则:凡是重复性的工作,一定有改良的方法。如果感觉到被简单体力劳动透支了精力，那就要警觉：一定存在什么地方可以优化和改进。第二章数据处理一、前提条件二、数据排序三、数据筛选四、分类汇总五、函数公式数据收集运算处理数据分析图表展现Page30一、前言：基本原则对一张报表能做些什么处理？第二章数据处理表格汇总查询遍历运算行和行之间的运算关系：分类汇总求和公式数据透视表列和列之间的运算关系：四则运算、函数、VBA程序排序查找单个：查找、定位查找多个：筛选、查询函数Page31一、前言(2)：准备条件使用Excel分析数据前要进行预处理1.确定数据类型正确。2.取消合并。合并单元格影响排序、筛选，是分析数据的大忌，只应出现在打印成果中。3.取消空行，空列。空行影响自动筛选，空列影响生成透视表。4.换个地方写标题。表格标题可以放入页签名中，使横表头出现在第一行。第二章数据处理Page32一、前言(3)：优先原则使用功能的优先原则是：系统功能公式函数VBA编程1.能够使用系统功能自动完成的，就不要用公式去计算。2.能够使用系统自带的公式和函数，就不要自己去编写VBA程序。第二章数据处理Page33二、数据排序排序前的好习惯：添加序号列记录原始顺序排序基本操作：操作并不复杂：不必选择区域，点击任意单元格即可按照该列进行排序。多字段组合排序；第二章数据处理Page34二、数据排序（2）让Excel智能地识别新的顺序：1.录入职称顺序；2.【筛选】——【自定义排序】窗口找到【自定义序列】，输入职称顺序，点击【添加】；3.排序窗口选择关键字段，按自定义排序次序。第二章数据处理Page35三、数据筛选自动筛选：Alt+D+F+FCtrl+Shift+L(2007版)高级筛选条件筛选后的陷阱：筛选区域“只出不进”：可以复制筛选区域，不可以向筛选区域粘贴。第二章数据处理Page36四、分类汇总排序是使用前提：先排序，然后才能使用分类汇总功能。合计行显示在下方：取消勾选“在下方显示合计行”对分类汇总功能的综合评价：优势：生成报表适合阅读，求和方式较为直观。劣势：操作复杂，必须先排序再汇总；无法继续扩充数据行。生成了多余的合计行，扰乱了数据源，不能进一步生成数据透视表。第二章数据处理Page37概述：Excel函数公式分类1.四则运算：简单加减乘除；2.数学计算：乘方、连加、绝对值、sum(),sumif(),count(),average()；3.字符处理：处理文本，进行字符截取、合并等操作；4.时间日期：处理时间日期数据；5.逻辑判断：与、或、非，if...then...else..6.查找和引用：名次、排序、查找返回数据第二章数据处理五、函数运算Page38五、函数运算常用函数目录：基础运算：sum(),sumif(),count(),average()检查重复值:countif()显示名次：rank()截取和拼接字符串：left(),right(),mid(),len()条件判断公式：if()，if()的嵌套使用匹配、替换：vlookup，substitute第二章数据处理Page39五、函数运算（一）引用：引用一个单元格：=A1取得A1单元格的值=sheet2！A1取得sheet2表格中A1单元格的值引用一个区域：=MAX(A1:C10)在A到C列，1到10行组成的数组取最大值绝对引用和相对引用：在行号＼列号前加$进行锁定，拖动复制时不会自动变化。第二章数据处理Page40五、函数运