Excel操作与数据分析技巧

*,*,培训科 张其建,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,简介,类型,97-2003,2007,不带宏的工作簿,.xls,.xlsx,带宏的工作簿,.xls,.xlsm,文件格式,为方便在使用新版,excle,时，可以设置中默认的文件保存格式,强烈推荐所有正在使用,Excel2003,的同事们，使用,EXCEL2007,或,2010,1,EXCEL,简介,2,EXCEL,常用操作及功能,3,EXCEL,函数使用技巧,4,数据透视表,5,公式及函数,6,数组的运用,报告目录,宏与简单,VBA,2,、,EXCEL,常用操作及功能,数据录入,TAB,与,ENTER,数据录入时检查错误：,让,EXCEL,读出你的输入内容,一般情况下：,TAB-,向右；,ENTER-,向下,可调整,ENTER,属性,选定需要输入区域一直按,ENTER,可顺序输入,在选项栏中选择：,按,ENTER,键开始朗读单元格,相对位置,:,A2 F13 C7,绝对位置,:,列绝对：,$A2,、,$F13,、,$C7,行绝对：,A$2,、,F$13,、,C$7,绝对位置：,$A$2,、,$F$13,、,$C$7,引用,“$”,表示对后面所表达的列或行的锁定。,注意：,引用在我们拖动公式或填充时有重大的影响,只有理解了应用，你才能更好的使用公式,2,、,EXCEL,常用操作及功能,引用名称命名与引用,我们可以对某些单元格定义名称后直接使用名称，以简化公式内容。,2,、,EXCEL,常用操作及功能,填充,当数据量少的时候：直接采用鼠标拖拉即可。,填充时尤其要注意引用方式,右键也是可以填充的,当数据量多的时候：需要采用键盘来操作,（如果采取鼠标填充的方式，就会拖到,”,地久天长,”),具体快捷键：,快速移动：,ctrl+,方向键（）,快速选中：,ctrl+shift+,方向键（）,快速填充：,ctrl+D,（向下）,ctrl+R,（向右）,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,中可运算的数据类型分为：文本，数值、日期、逻辑值、错误值,运算符号,符号类型,符号,案例,算术运算,+,；,-,；,*,；,/,；,=62,=3+2*5,比较运算,;,；,=,；,=,；,=51,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,、公式的查错,错误值,含义,#,列宽不够显示，或使用了负的日期，和时间。,#VALUE!,参数或数值类型错误,#DIV/0!,被零除错误,#NAME,？,无法识别的公式,#N/A,数值对函数或公式不可用,#REF!,单元格引用无效,#NUM!,无效的数字值,#NULL!,计算为空值时，无法得出计算结果；例如：,公式差错的工具：,1,、键盘上的,F9,2,、公式选项卡中的,公式查错时的要诀：,1,、读懂公式,2,、看看公式在拖动过程中的引用方式是否正确,3,、当嵌套太多太复杂时，把公式分层粘到单元格中,四、公式及函数,1,EXCEL,简介,2,EXCEL,常用操作及功能,3,EXCEL,函数使用技巧,4,数据透视表,5,公式及函数,6,数组的运用,报告目录,宏与简单,VBA,数组的概念,五、数组的运用,数组是一个或多个元素按照行业排列方式组成的组合。,在,EXCEl,中使用,表达数组,=1;2;3;4;5,1,、在一个单元格中输入数组,2,、选中,5,个格子，按键盘上的,F2,。,3,、,CTRL+SHIFT+ENTER,按键：,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=$C$1,ROW($2:$10),ROW(1:1),1,EXCEL,简介,2,EXCEL,常用操作及功能,3,EXCEL,函数使用技巧,4,数据透视表,5,公式及函数,6,数组的运用,报告目录,宏与简单,VBA,定义与用途,六、宏与简单,VBA,VBA,是专为,OFFICE,系列开发的操作程序，使用,VBA,必须要具备计算机的编程知识。,可以这么说，在我们工作中,99.9%,的情况下下用不到,VBA,。,而且，在实际工作中能不用就不用，很多工作用函数也能解决问题。,VBA,运用的演示,在此不再细讲,谢谢！,结束语,EXCELHOME,：,推荐书籍：,Excel 2007,实战技巧精粹,