Excel2007高级使用技巧PPT.ppt

,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,Excel 2007,提升培训班,1,Contents,一：数据录入和格式化工作表,二,：公式与函数的应用及案例,三：图表及打印输出及其它设置,四：数据分析及管理,综合案例练习,2,一、数据录入和格式化工作表,1,、,Excel2007,的数据录入技巧；,2,、自定义数字格式；,3、Excel2007,条件格式设置；,4、,数据有效性及案例；,5,、,EXCEL,多工作表链接、冻结与并排；,6、EXCEL,工作表的数据保护功能；,3,1.1,、数据录入的相关技巧,说明性，解释性的数据描述，非数值类型的。如，员工编号、姓名、出生年月、手机号码,常常用于各种数学计算的数据。如：工资、学生成绩、销售额等,是以,“,=,”,号开头，它可以是简单的数学式，也可以是包含各种,Excel,函数的式子,数值,文本,公式,4,数据录入技巧,灵活使用,TAB,、,ALT,、,ENTER,键,录入分数,：零,+,空格,+,分数,录入文本格式数字（邮编、电话等）：单引号,+,录入内容,输入当前日期、时间：,Ctrl+;,和,Ctrl+Shift+;,录入特殊字符（对号、平方）,;,不连续区域的填充：,Ctrl+Enter,填充柄录入（序列填充）,插入批注,5,数据录入技巧,引入动态数据,导入,WEB,资料,导入外部数据,大型工作表中单元格（区域）选择,使用智能标记进行输入,使用工作表组加快多工作表创建和数据输入速度,数据朗读,6,7,高效的,Excel,快捷键,快捷键,实现操作,Ctrl+,鼠标滚轮,缩放当前视图,Ctrl+Shift+,方向键,选择连续系列数据,Ctrl+A,选择全部数据,Ctrl+;/Ctrl+Shift+;,输入当前日期,/,时间,Shift+F11,新建工作表,Alt+Enter,在单元格内强制换行,7,使用键盘快捷方式定位到所需位置,快捷键,功能描述,Shift+,箭头键,通过按箭头键可逐个单元格地扩展选择范围。,Ctrl+,箭头键,移到数据的边缘。,F5,打开“定位”对话框。,F2,编辑活动单元格，并将插入点放到单元格内容末尾。,Ctrl+End,将插入点移到工作表中最后一个使用的单元格。,Ctrl+Home,将插入点移动到工作表的开头。,Page Up,向上移动一屏。,Page Down,向下移动一屏。,Alt+Page Up,向左移动一屏。,Alt+Page Down,向右移动一屏。,Ctrl+Shift+Page Up,选中当前工作表和上一个工作表。,Ctrl+Shift+Page Down,选中当前工作表和下一个工作表。,8,1.2,、自动限定数据格式,案例分析,（,1,）在数字后面自动添加单位（如：元、千克等）,将文本字符括在双引号,(),内或在单个字符前面添加一个反斜杠,(),（,2,）输入身份证的前几位,51102519,（,3,）输入条形码前的某些重复数字,“999999999”,（,4,）定义小数点对齐、分数对齐,?.?#?/?,（,5,）自定义各种特殊日期格式,mmmm d,yyyy(dddd),December 30,2008(Tuesday),yyyy“年”m“月”d“日”aaaa,2008,年,12,月,30,日星期四,9,选择性粘贴 格式刷,选择性粘贴,公式,数值,格式,批注,转置,粘贴链接,格式刷,10,1.3,、,Excel2007,条件格式设置；,1、EXCEL,表格的排版技巧；,2、EXCEL,条件格式,11,1.3.1、,单元格格式设置,文字的对齐与旋转,合并单元格,使用工具栏上的快捷按钮,格式,单元格,对齐,合并单元格,自动换行,格式,单元格,对齐,自动换行,强制换行,ALT+ENTER,文字旋转,格式,单元格,对齐,文字方向,设定边框和底纹,使用工具栏上的,“,边框,”“,底纹,”,快捷按钮,格式,单元格,边框,图案,12,1.3.2、,条件格式管理器设置,13,什么是条件格式,当单元格的值满足预先设定的条件时，自动将单元格设置成预先设定的格式,多条件格式的规则,按从上到下的顺序判断条件并执行,最多指定三个条件,根据公式自动设置格式,在条件格式中使用公式和函数,实现隔行变色等的特殊格式,掌握,ROW,和,MOD,函数的用法,=mod(row(),2),案例简析,14,1.4,、数据的有效性,提高数据录入准确性,-,有效性检查。什么是数据的有效性？,数据有效性是一个包含帮助你在工作表中输入资料提示信息的工具,.,它有如下功能,：,(1),给用户提供一个选择列表,(2),限定输入内容的类型或大小,(3),自定义设置,数据有效性的设置,提供下拉式菜单的输入,设置输入提示,设置出错警告,自定义有效性条件,15,1.5,、如何处理大型工作表,在,Excel,多个工作表间快速切换,技巧一：用快捷键快速导航工作表,Ctrl+Page Up,移到上一个工作表,Ctrl+Page Down,移到下一个工作表,技巧二：右键菜单准确定位工作表,右击导航状态按钮选择所需工作表标签名,冻结工作表窗口,大型工作表数据的,分组、分级,显示,命名单元格或单元格区域，以便能够快速返回到这些工作表区域,组合工作表，以便同时在多张工作表中输入共用数据,16,并排比较数据,两个工作簿将在同一个窗口中打开。当一个工作簿滚动时，,Excel,将使第二个工作簿自动滚动相应数量的单元格,工作表间的数据链接,创建引导型页面,创建链接按钮,创建超级链接及编辑超级链接,设置工作表及行号列标的隐藏,同时比较两个工作簿可能很困难,1.5,、如何处理大型工作表,17,1.6,、数据保护功能,保护工作表、工作簿,（,1,）为工作簿加密码（,2,）以隐藏方式保存工作簿（,3,）为工作表加密码（,4,）隐藏工作表，并阻止其他人随意取消隐藏（,5,）设置不同区域不同密码（,6,）设置只有部分单元格可编辑,（,7）,隐藏敏感公式,18,设置表格的锁定保护,将允许用户更改的单元格区域设置为解除锁定状态,隐藏单元格的公式,先选中全表,格式,单元格,保护,-,把选定和隐藏上的两个勾去掉。再选中你要隐藏的单元格，格式,单元格,保护,-,把选定和隐藏前的两个勾打上。再选工具,-,保护,-,保护工作表,将工作表进行保护的设置,(,加密,),19,二：图表及打印输出及其它设置,创建,Excel,图表,2.1,编辑,Excel,图表,2.2,打印输出及其它设置,2.3,20,图表在数据分析中的作用,柱形图,：用来显示一段时间内数据的变化或者描述各个项目之间数据比较图表,线性图,：主要用于以等时间间隔显示数据的变化趋势，强调的是时间性和变动率，而不是变动量,饼形图,：显示数据系列中的项目和该项目数值总和的比例关系。它只能显示一个系列的数据比例关系,组合图,21,2.1,创建分析图表,确定分析目标,明确表格数据的取值范围,图表向导,使用,一键成图创建图表,22,2.2,图表的编辑、美化技巧,常规编辑技巧,改变图表大小,移动图表位置,图表内其他对象编辑,图表的特殊编辑,向图表添加数据,删除图表内数据,文字数据修饰,线和阴影修饰,背景修饰,设置图表的序列格式,综合案例分析,23,2.3,打印输出及其它设置,页面设置,工作表的页眉、页脚,分页符和打印选项,工作表参数设置,设置打印区域,自动打印统一表头,打印指定页面,打印连续页码,一次打印多个工作表,使某个工作表不能被打印,24,三：公式与函数的应用及案例,公式的基本用法,3.1,函数分类与常用函数应用举例,3.2,25,3.1,公式的基本用法,双击填充柄复制公式,区别公式中各种单元格引用方式,绝对引用,相对引用,混合引用,三维引用,跨工作表或工作簿的数据计算,不同工作表间数据的计算,不同工作簿间数据的计算,自动求和与自动计算功能的使用,26,3.2,函数应用与常用函数应用举例,函数：是预定义的公式，通过使用一些称为参数的特定值来安特定的顺序和结构执行计算,函数的使用方式,=,函数名称（参数,1,，参数,2,，参数,3),函数的嵌套特性,一个函数可以引用其他函数的结果作为参数,Excel,最多支持七层嵌套函数,逻辑文本类函数应用,日期时间函数应用,查询引用类函数应用,统计计数类函数应用,其他常用函数应用,财务函数应用,27,3.2.1,逻辑文本类函数,IF,ANDOR,LEN,MID LEFT RIGHT,CONCATENATE,EXACT,28,IF,函数,判断,一个条件是否满足，如果满足返回一个值，如果不满足则返回另一个值,语法：,IF(logical_test,value_if_true,value_if_false),条件为真时,条件为假时,29,AND OR,函数,AND,函数的作用,：,是对多个逻辑值进行交集计算，返回逻辑值,语法：,AND(logical1,logical2,),OR,函数的作用,：,是对多个逻辑值进行并集计算，返回逻辑值,语法：,OR(logical1,logical2,),30,MID(MIDB),LEFT,RIGHT,函数,MIDB函数作用自文字指定位置开始向右提取指定长度的字符串，操作对象均为文本，MID以字符为单位，MIDB以字节为单位截取,语法：,MID(text,start_num,num_bytes),LEFT返回文本字符串中的第一个或前几个字符，RIGHT返回文本字符串中的最后一个或后几年字符,语法：,LEFT(text,num_chars),语法：,RIGHT(text,num_chars),31,CONCATENATE,函数,CONCATENATE,函数的作用是将几个字符串合并为一个文本字符串,语法：,CONCATENATE(text1,text2,),也可使用,“,”,运算符代替实现文本的合并,32,EXACT,函数,EXACT,函数的作用：,测试两个字符串是否完全相同。如果它们完全相同，则返回TRUE;否则返回FALSE。EXACT函数能区分大小写，但忽略格式上的差异,语法：EXACT(text1，text2),参数：Text1是待比较的第一个字符串，Text2是待比较的第二个字符串,33,3.2.2,日期与时间函数：,DATE,：返回代表特定日期的序列号,语法：,DATE(year,month,day),NOW,：返回计算机内部时钟的当前日期和时间,语法：,NOW(),TODAY,：返回当前日期的序列编号,语法：,TODAY(),注意：序列号是,Microsoft Excel,日期和时间计算使用的日期,-,时间代码。,Microsoft Excel,可将日期存储为可用于计算的序列号。默认情况下，,1900,年,1,月,1,日的序列号是,1,，而,2008,年,1,月,1,日的序列号是,39448,，这是因为它距,1900,年,1,月,1,日有,39448,天。,34,3.2.3,查询和引用类函数,LOOKUP,HLOOKUP,VLOOKUP,INDEX,MATCH,35,LOOKUP,使用,LOOKUP,函数是在单行区域或单列区域中查找数值，然后返回第二个单行区域或单列区域中相同位置的数值,LOOKUP(lookup_value,lookup_vector,result_vector),说明：,lookup_vector,为第一个包含一行或一列的区域；,result_vector,为第二个包含一行或一列的区域，其大小必须与参数,lookup_vector,相同,36,HLOOKUP_,实现水平查找,使用,HLOOKUP,函数可以在表格的首行查找指定的数值,并由此返回指定行处与该数值同一列的数值，当比较值位于数据表首行时，可使用此函数,语法：,HLOOKUP(lookup_value,table_array,row_index_num,range_lookup),37,VLOOKUP_,实现竖直查找,在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。当比较值位于数据表首列时,可以使用此函数,语法：,VLOOKUP(lookup_value,table_array,col_index_num,range_lookup),38,INDEX,INDEX,函数用于返回表或者区域中的值或值的引用,语法：,INDEX(reference,row_num,column_num,area_num)ref:,表示对一个或多个单元格区域的引用，如是多个区域需用括号括起来；,rowcolumn,：表示引用中某行,列的序号；,area,：表示选择引用中的一个区域（对于多区域引用）,39,MATCH,MATCH,用来返回在指定方式下与指定数值相匹配的数组中元素的相应位置,(,返回的不是数值本身,),语法：,MATCH(lookup_value,lookup_array,match_type)L_value,：为需要在数据表中查找的数值,L_array,：表示包含参数,L_value,的连续单元格区域,M_type,：指明如何查找值，可取,-1,、,0,或,1,三个值：,-1,：查找大于或等于,Value,的最小值，,array,须降序排列,0,：查找等于,Value,的第一个值，,array,可按任意顺序排列,1,：查找小于或等于,Value,的最大值，,array,须按升序排列,40,3.2.4,统计计数类函数,COUNT,COUNTIF,SUMIF,FREQUENCY,RANK,41,COUNT,COUNT,函数的作用：,返回包含数字以及包含参数列表中的数字的单元格的个数。利用函数 COUNT 可以计算单元格区域或,数字,数组中数字字段的输入项个数。,语法,：,COUNT(value1,value2,.),参数：,Value1,value2,.为包含或引用各种类型数据的参数（1 到 30个），但只有数字类型的数据才被计算。,说明,函数 COUNT 在计数时，将把数字、日期、或以文本代表的数字计算在内；但是错误值或其他无法转换成数字的文字将被忽略。,如果参数是一个数组或引用，那么只统计数组或引用中的数字；数组或引用中的空白单元格、逻辑值、文字或错误值都将被忽略。,42,COUNTIF,函数,COUNTIF,函数可累计满足某一条件的对象在单元格区域中出现的次数，该条件可以是数值、文本或表达式，在人事、工资和成绩统计中有广泛用途。,语法：,COUNTIF,（,range,criteria),43,SUMIF,函数,SUMIF,函数可对满足某一条件的单元格区域求和，该条件可以是数值、文本或表达式。,语法：,SUMIF,（,range,criteria,sum_range),44,频率分布函数,以一列垂直数组返回某个区域中数据的频率分布。例如，使用该函数可以计算在给定的分数范围内测验分数的个数。由于函数返回一个数组结果，所以必须以数组公式的形式输入公式。,语法：,FREQUENCY(data_array,bins_array),45,RANK,函数,RANK,函数返回一个数字在数字列表中的排位语法：,RANK(number,ref,order)order,：为,0,或省略，返回对,ref,降序排列的列表,46,3.2.5,其它常用函数,INT,MOD,ROUND,RANK,47,INT,INT,函数的作用：,将数字向下舍入到最接近的整数。,语法,：,INT(number),参数：,n,umber,是,需要进行向下舍入取整的实数。,48,MOD,返回两数相除的余数。结果的正负号与除数相同。,语法,MOD,(,number,divisor,),Number,为被除数。,Divisor,为除数。,注解,如果,divisor,为零，函数,MOD,返回错误值,#DIV/0!,。,函数,MOD,可以借用函数,INT,来表示,:,MOD(n,d)=n-d*INT(n/d),49,ROUND,ROUND,函数的作用：,按指定位数四舍五入某个数字。,语法：ROUND(number，num_digits),参数：Number是需要四舍五入的数字;Num_digits为指定的位数，Number按此位数进行处理,注意:如果num_digits大于0，则四舍五入到指定的小数位;如果num_digits等于0，则四舍五入到最接近的整数;如果num_digits小于0，则在小数点左侧按指定位数四舍五入。,50,RANK,函数,RANK,函数返回一个数字在数字列表中的排位语法：,RANK(number,ref,order)order,：为,0,或省略，返回对,ref,降序排列的列表,51,3.2.6,财务函数及案例应用,PMT（）,贷款函数；,基于固定利率及等额分期付款方式，返回贷款的每期付款额。,语法,PMT,(,rate,nper,pv,fv,type),Rate,贷款利率。,Nper,该项贷款的付款总数。,Pv,现值，或一系列未来付款的当前值的累积和，也称为本金。,Fv,为未来值，或在最后一次付款后希望得到的现金余额，如果省略,fv,，则假设其值为零，也就是一笔贷款的未来值为零。,Type,数字,0,或,1,，用以指定各期的付款时间是在期初还是期末。,注解,PMT,返回的支付款项包括本金和利息，但不包括税款、保留支付或某些与贷款有关的费用。,应确认所指定的,rate,和,nper,单位的一致性。例如，同样是四年期年利率为,12%,的贷款，如果按月支付，,rate,应为,12%/12,，,nper,应为,4*12,；如果按年支付，,rate,应为,12%,，,nper,为,4,。,52,四：数据分析及管理,数据的筛选,4.2,数据的分类汇总,4.3,数据透视表,4.4,4.1,数据的,排序,4.1,删除重复项,4.5,53,4.1,排序数据,排序时，一般不要选择数据范围，以防止破坏数据的结构,针对多列进行排列使用排序对话框进行相应操作,简单排序,多字段排序,按行排序,自定义序列排序,54,4.2,数据的筛选分析,自动筛选,高级筛选,条件区域的设置原则,工作表中指定一个空白区域作条件区域,条件区域第一行是条件名,(,字段名,),行，第二行是输入的条件,条件区域中不能包含有空行或空列，单元格中不能包含空格,字符字段的条件可以使用通配符*和,?,注意：在具体写条件时，我们要分析好条件之间是与关系还是或关系。若是与关系，条件要写到同一行中，若是或关系，条件要写到不同行中,55,4.3,数据分类汇总,分类汇总之前必须之操作,注意：使用“分类汇总”功能时，首先要确定分类的依据，然后按选定的分类依据将数据排序！,建立分类汇总,使用分级功能查看汇总结果,56,4.4,数据透视表,透视表的布局及更新,数据刷新,添加字段,数据的分组,格式化报表,创建数据透视图,57,4.5,删除重复项,筛选唯一值和删除重复值是两个紧密相关的任务，这是因为所显示的结果是相同的：一个唯一值的列表。但它们差别仍很大：筛选唯一值时，将临时隐藏重复的值，但删除重复值时，则会永久删除重复值。,重复值是行中的所有值与另一个行中的所有值完全匹配的值。重复值是由单元格中显示的值确定的，它不必是存储在单元格中的值。例如，如果不同的单元格中有相同的日期值，一个格式为,“,3/8/2006,”,，另一个格式为,“,Mar 8,2006,”,，则值是唯一的。,最好先筛选唯一值，以便在删除重复值之前确认结果是所需的。,58,Thank You!,59,