Excel操作与数据分析技巧.ppt

培训科 张其建 2012-6-24,EXCEL实战技巧,1,EXCEL简介,2,EXCEL常用操作及功能,3,EXCEL函数使用技巧,4,数据透视表,5,公式及函数,6,数组的运用,报告目录,宏与简单VBA,中级,高级,专家级,初级,功能区：新的控制中心，汇集了一些基本的命令并使这些命令非常直观。,1、EXCEL简介,你用2003？不一样？没有关系，我这里为大家提供一个03-07对照工具,Excel2007中有许多更改。其中以窗口顶部的更改最为明显。,功能区的三个组成部分是选项卡、组和命令。,选项卡：选项卡呈现了可在 Excel 中执行的核心任务。 组：组是指显示在选项卡上的相关命令集。 命令：命令是指按钮、菜单或信息输入框。,1、EXCEL简介,隐藏的命令,功能区上的命令是最常用的命令。,Excel 2007 并不总是显示每一项命令，只有在可能需要使用某些命令时，才会显示这些命令，以响应所执行的操作。,1、EXCEL简介,更多的选项,单击对话框启动器 ，您就会看到一个对话框或任务窗格。,1、EXCEL简介,对话框启动器,在首次启动 Excel 2007 时，快速访问工具栏位于功能区上方。在快速访问工具栏中，命令始终可见. 我们可以把一些常用的命令列入快速工具栏中方便应用。,快速访问工具栏,1、EXCEL简介,键盘快捷方式,功能区上的每一个按钮都有快捷方式。 新快捷方式都有提示。 以 Ctrl 开头的旧键盘快捷方式没有变化。,1、EXCEL简介,新视图,在工作表直接看到：行/列标题、页边距标尺。 在打印之前无需使用“打印预览”来发现问题。 添加页眉和页脚比以前更加简捷。,1、EXCEL简介,兼容性,1、EXCEL简介,文件格式,为方便在使用新版excle时，可以设置中默认的文件保存格式,强烈推荐所有正在使用Excel2003的同事们，使用EXCEL2007或2010,1,EXCEL简介,2,EXCEL常用操作及功能,3,EXCEL函数使用技巧,4,数据透视表,5,公式及函数,6,数组的运用,报告目录,宏与简单VBA,2、EXCEL常用操作及功能,数据录入,TAB与ENTER,数据录入时检查错误： 让EXCEL读出你的输入内容,选定需要输入区域一直按ENTER可顺序输入,在选项栏中选择： 按ENTER键开始朗读单元格,相对位置: A2 F13 C7 绝对位置: 列绝对：$A2、$F13、$C7 行绝对：A$2、F$13、C$7 绝对位置：$A$2、$F$13、$C$7,引用,“$”表示对后面所表达的列或行的锁定。,注意：引用在我们拖动公式或填充时有重大的影响 只有理解了应用，你才能更好的使用公式,2、EXCEL常用操作及功能,引用名称命名与引用,我们可以对某些单元格定义名称后直接使用名称，以简化公式内容。,2、EXCEL常用操作及功能,填充,当数据量少的时候：直接采用鼠标拖拉即可。 填充时尤其要注意引用方式 右键也是可以填充的,2、EXCEL常用操作及功能,左键填充,右键填充,复制,复制公式、复制数据、复制格式,行列互换,不同的复制方式能起到神奇的工效。,2、EXCEL常用操作及功能,选定,遇到数据中太多的空格时，有一种抓狂的感觉,F5：定位条件可以为你带来很多快乐,2、EXCEL常用操作及功能,数据有效性,可以帮助我们设置可选的菜单，避免五花八门的描述产生。,2、EXCEL常用操作及功能,排序,行也是可以排序的,汉字可以按笔划排序,2、EXCEL常用操作及功能,排序自定义序列,OFFICE按钮EXCEL 选项常用编辑自定义列表,设定按照自己顺序的排列,2、EXCEL常用操作及功能,筛选自动筛选和高级筛选,2、EXCEL常用操作及功能,分列,快速的将一些有规律的数据分成不同类别。,2、EXCEL常用操作及功能,分类汇总,2、EXCEL常用操作及功能,单元格格式,快捷键：Ctrl+1,2、EXCEL常用操作及功能,条件格式,开始样式条件格式,条件格式是2007里面新增加的一项功能，按要求对数据进行分类显示,2、EXCEL常用操作及功能,冻结和拆分,这是查看大量数据显示表头的利器,2、EXCEL常用操作及功能,2、EXCEL常用操作及功能,其他简便功能,A、删除重复数据,照相机 -把数据变为图片，防止泄露其他数据,对象选择工具 -选择EXCEL中的图形对象,B、隐藏着的快捷工具,按拼音排序,1,EXCEL简介,2,EXCEL常用操作及功能,3,EXCEL函数使用技巧,4,数据透视表,5,公式及函数,6,数组的运用,报告目录,宏与简单VBA,源数据 -一定是要规则清晰，无空格断行的数据。,源数据的每列都将成为一个字段并且可在报表中使用。 报表字段的的名称来自源数据中的列标题，标题下的其余各行应该包含同一列中的类似项。 删除用于数据块相互隔开的空白行。,数据透视表,3、数据透视表,一种可以动态地改变版面布置，以便按照不同方式分析数据的动态表格。,创建数据透视表,1、插入选项卡 2、选择数据透视表 3、选项框中选择确定,3、数据透视表,数据透视表的使用,把你想透视的内容拖到对应区域,3、数据透视表,3、数据透视表,数据透视表的使用,现在对应的内容就是：谁在哪台机器上的产量是多少。 行和列标签的自由拖动可以创造很多不同的数据表达方式。,3、数据透视表,数据透视表的使用,在图表区域中点击右键，还可以选择其他的内容： 例如：汇总类型（求和，计数等），显示方式(百分比 等等,数据透视图的使用方法和透视表一致，只是多了一个可以筛选的图表,1,EXCEL简介,2,EXCEL常用操作及功能,3,EXCEL函数使用技巧,4,数据透视表,5,公式及函数,6,数组的运用,报告目录,宏与简单VBA,1、公式简介,EXCEL中公式分为了很多类,四、公式及函数,我们学习函数最大的帮助工具是EXCEl自带的帮助,编写公式时你应该具备基础的程序逻辑思考技巧。,四、公式及函数,2、函数与公式的基本概念,EXCEL公式以“=“为引导，后面接上函数，以及函数所使用的参数,=IF(logical_test, value_if_true, value_if_false),参数,数据类型 在EXCEL中可运算的数据类型分为：文本，数值、日期、逻辑值、错误值,运算符号,函数,四、公式及函数,3、参数的类型,不同的引用在我们拖动公式时具有不同的结果。,常量： 无论如何运算，自身都不会改变的值；一般为直接输入的数据 “=1+2*3”虽然这个公式结果不会变，但是它不属于常量.,系统变量： 公式的某些变量来源于计算机系统的自身参数，如机器时间。,地址引用： 对其他工作溥、工作表、区域或单元格的内容的引用这种引用在是根据被引用对象的位置进行的,引用的转换快捷键：F4,四、公式及函数,4、常用函数,以上函数基本能满足我们的一般工作需求。,四、公式及函数,案例演示,四、公式及函数,4、函数的嵌套,IF(A11, SUM(B1:G1),”),在这个公式中，sum函数是2级函数，它是if函数的参数 Excel2007允许64层的嵌套，2003只能7层。,=IF(MOD(ROW(),3)=1, 源数据!A$1, IF( MOD(ROW(),3)=2, INDEX(源数据!$A$1:$G$349,(ROW()+4)/3,COLUMN(), “ “) ),第1个IF的条件参数,当条件为真时返回该值,当条件为假时进入下一个IF,第2个IF的条件参数,当条件为真时进入Index函数,当条件为假时返回空值,5、如何解读函数,=IF(MOD(ROW(),3)=1,源数据!A$1,IF(MOD(ROW(),3)=2,INDEX(源数据!$A$1:$G$349,(ROW()+4)/3,COLUMN(),“ “),复杂案例,A、设计目的： 按规律切割工资条,B、设计思路： 结果的第一行：永远是原数据中的第一行。 结果的第二行：结果第2行对应原数据第2行，结果第5行对应原数据第3行，第8对应第4，第11对应第5。 结果第三行：永远为空。,这里面第二个最难算，我们看看他们之间有什么规律。,案例:工资打印切割公式,=IF(MOD(ROW(),3)=1,源数据!A$1,IF(MOD(ROW(),3)=2,INDEX(源数据!$A$1:$G$349,(ROW()+4)/3,COLUMN(),“ “),四、公式及函数,6、函数是怎么编出来的,案例4.2,5、公式的查错,四、公式及函数,1,EXCEL简介,2,EXCEL常用操作及功能,3,EXCEL函数使用技巧,4,数据透视表,5,公式及函数,6,数组的运用,报告目录,宏与简单VBA,数组的概念,五、数组的运用,数组是一个或多个元素按照行业排列方式组成的组合。,在EXCEl中使用表达数组,=1;2;3;4;5,1、在一个单元格中输入数组,2、选中5个格子，按键盘上的F2。,3、CTRL+SHIFT+ENTER,数组的使用,五、数组的运用,1、频次统计,2、不确定结果的查询,案例,=FREQUENCY(C2:C6,E2:E5),=IF(ROW(1:1)COUNTIF(A:A,$C$1),“,INDEX(B:B,SMALL(IF(A$2:A$10