excel实用技巧

1、Excel,常用技巧,行政管理部,Excel,常用技巧,一、常用格式设置,二、统计常用方法,三,Excel,函数,常用格式设置,1,边框及底纹,在,excel,中，表格都是已经给定的，我们所做的,就是在输入数据后，在给定的表格外绘制边框,选定区域后，单击右键,设置单元格格式,边框,根据所需要的边框添加，或者进入格式,自动套,用格式，使用喜欢的类型,图表的背景色可以在单元格格式,图案中选择,或者点击工具栏上的填充按钮，选择需要的颜,色,常用格式设置,2,合并及居中,当需要对多个单元格进行合并时，可以,将这些单元格选定后，点击工具栏上的,合并及居中按钮，如需取消，再点击一,次即可,常用格式设置,3

2、,对齐,在单元格设置,对齐里面，可以设置单元,格的对齐方式、文字方向、自动换行等,参数。（手动换行需要按,Alt+Enter,常用格式设置,4,数字格式,单元格格式,数字里面，可以设置单元格,的格式，方便输入各种数据。例如身份,证号码在单元格就会自动用科学计数法,最后几位显示不出来，这就需要把单元,格设置成文本格式,常用格式设置,5,页面设置,页面设置是对需要打印表格进行页面、页边距,页眉页脚的设置，在文件,页面设置里面，这些,设置不用详细讲。对于打印范围比较大，需要,每页设置标题的文档，我们可以在页面设置,工,作表里点击“顶端标题行”，将每页需要添加,的标题行选进去，打印出来的文档每页就自动

3、,加上了标题行,常用格式设置,6,数据有效性,点击数据,有效性，可以为单元格设置有效的数,据类型，默认的是任何值。我们可以将其进行,改成我们所允许的值，在工作中就可以避免输,入错误。我们也可以对出错提示自定义,常用格式设置,7,条件格式,条件格式能够在单元格条件满足设定时，自动,变化单元格格式。点击格式,条件格式进入设置,设定条件，在格式中选定满足条件时单元格格,式的变化。使用条件格式能够及时为数据变化,做出提醒,常用格式设置,8,格式刷,格式刷能够非常迅速地将已设定好的单元格格,式进行批量复制，减少重复工作，提高效率,格式刷在常用工具栏里，使用时先选定被复制,格式的单元格，点击格式刷，然后在

4、需要复制,的单元格拖动即可,常用格式设置,9,通配符,通配符一般使用在搜索中,表示一个任意字,符，例如搜索,d?f,表示查找以,d,开头,f,结尾的,任意一个三字母的词,表示任意个任意字符,例如搜索,d*f,表示查找以,d,开头,f,结尾的任,意一个词，不限于三个字母,则用在搜索,和,本身上面，例如搜索,d?f,表示搜索搜索,d?f,这个词,统计常用方法,1,筛选（自动筛选、高级筛选,2,分类汇总,3,数据透视表,4,函数统计（略,统计常用方法,1,筛选,自动筛选。在数据输入完成后，选定需要筛选,的项目一行，点击数据,筛选,自动筛选，选定,的项目就会多出下拉菜单，在其中即可选择需,要筛选的条件

5、。取消自动筛选按上述重复,统计常用方法,1,筛选,高级筛选。高级筛选中在原有,区域显示筛选结果和自动筛选,差不多，一般用得较多的是将,筛选结果复制到其他位置，这,样就能对筛选出来的结果进行,下一步的处理,统计常用方法,右边就是对这些区域进行高,级筛选，筛选的区域为左边,四列，筛选的条件为性别,男,性。最后生成的结果如下,统计常用方法,2,分类汇总,位置在数据,分类汇总里，前提条件是分类的,项目必须经过排序。例如下列数据里按性别分,类排序，再做计数汇总,统计常用方法,3,数据透视表,数据透视表为数据汇总及制表提供了一个极其,方便的拖拉操作方式，所有需要统计的字段直,接在数据透视表的操作界面中操作

6、,统计常用方法,为了避免破坏原数据，一,般选择新建工作表，在布,局中直接拖拉需要统计的,项目。例如统计不同部门,的性别统计。结果如下,统计常用方法,4,函数统计,函数统计放在后面的,excel,函数里进行讲,解,Excel,函数,Excel,中所提的函数其实是一些预定义的,公式，它们使用一些称为参数的特定数,值按特定的顺序或结构进行计算,Excel,函数一共有,11,类，分别是数据库函数,日期与时间函数、工程函数、财务函数,信息函数、逻辑函数、查询和引用函数,数学和三角函数、统计函数、文本函数,以及用户自定义函数,Excel,函数,因为在很多教材中的说法都说的很书面,化，所以在这里尽量用通俗的

7、语言来解,释常用函数的含义和用法，并举例来说,明,在日常工作中，我们不会用到所有分类,的函数，而且在所用的分类的函数中,我们用到的也是很少的一部分，所以我,们学习的目的是理解函数的思路。理解,了函数的思路后，我们碰到新的函数也,能迅速明白它的用法并运用到实际工作,Excel,函数,算术运算符,算术运算符,含义,示例,加,2+3=5,减,3-2=1,乘,2*3=6,除,10/2=5,乘方,43=64,百分比,50,Excel,函数,文本运算符,可以把文本连接起来，例如,A1&B1,可以把两个单元格合并为一个文本,比较运算符,比较运算符,含义,示例,等于,B1=C1,如果两单元格相等，返,回,TU

8、RE,如果不相等,FALSE,大于,略,小于,略,大于等于,略,小于等于,略,不等于,略,Excel,函数,引用运算符,引用运算符,含义,示例,区域运算符，计算一,个区域之间的数据,Sum(A1:D5,联合运算符，将多个,引用结合起来,Sum(A1:D5,E2:F7,空格,交叉运算符，计算多,个引用的交叉区域,Sum(A1:E5 B3:F7,运算顺序,默认的运算顺序是乘方,乘除,加减，相同优先级的按从左,到右的顺序运算，若要改变顺序，可以加上圆括号,Excel,函数,单元格引用,一般常用的引用为相对引用，如,A1*2,优点在于拖拉函数的时候,对应的引用能够自动变化,绝对引用则是拖动函数填充时，

9、不会自动变化,A$1*2,混合引用则是在一个单元格中，既有相对引用，也有绝对引用，如,A1,行变列不变,A$1,列变行不变,输入函数的方法,1,使用函数编辑栏,2,直接在单元格中输入,注意：函数中输入的括号及标点都必须使用英文格式,Excel,函数,日常工作中常用的函数有,1,求和函数,2,统计函数,3,逻辑函数,4,文本、日期与时间函数,5,查询和引用函数,6,多层函数嵌套,Excel,函数,1,求和函数,sum(,和,sumif(,函数,求和函数平时用到的主要是,sum(,和,sumif(,函数,Sum(,函数大家应用的最多，这里就不多,做介绍，它最多支持,30,组数据相加。如,果超过了,

10、30,组数据也有个小窍门，你可,以在外面再加一个括号，那么就相当于,把这些数据变成了一个组，那就可以进,行求和,Excel,函数,Sumif(,函数是条件求和函数，比如说你,要对一个区域数据满足条件的进行求和,那就可以用这个函数了,Sumif,条件范围,条件,求和范围,在括号,中隔开一定要用英文的逗号，否则无法,识别。例如,sumif(A1:A100,1,B1:B100,表示在,A1,到,A100,范围内大于,1,的条件下,对应的,B1:B100,范围里求和，条件范围和,求和范围一定要相互对应,Excel,函数,2,统计函数,count(,counta(,countif(,和,sumprodu

11、ct(,Count(,函数对数字型的数据进行统计，对文本,错误值、无法转换成数字的数据进行忽略，使,用方法为,count,统计范围,例如,count(A1:A100,表示对,A1,到,A100,范围内的所有数字型数据进,行统计，如果中间有文本、错误值等，则不进,行统计,Excel,函数,Counta(,函数则是统计一个范围内的非空,单元格，使用方法为,counta,统计范围,例如,counta(A1:A100,表示统计,A1,到,A100,范围内的所有非空单元格，如果有,单元格为空的，则不进行统计，如果单,元格里没有任何数字文本，但是打了一,个空格，函数仍然会进行统计，所以这,点一定要注意，很

12、容易造成错误,Excel,函数,Countif(,函数是条件统计函数，应用的非,常广泛，很多报表都可以用它来自动生,成，当然这是运用得比较熟练以后的事,情，这里主要讲基础用法,用法,Countif,统计范围,统计条件,例如,countif(A1:A100,1,表示对,A1,到,A100,范,围内所有大于,1,的数据进行统计,Excel,函数,Sumproduct(,函数是多条件统计函数，可,以对需要同时满足多个条件的结果进行,统计，具体使用方式为,sumproduct,条件,1)*,条件,2)*,条件,3,Excel,函数,3,逻辑函数,if(,and(,or(,和,not(,这几个是日常工作

13、中用得比较多的逻辑,函数，尤其是,if(,函数用得最多,用法,if,条件,满足条件返回的结果,不满足,条件返回的结果,例如,if(A1=60,及,格,不及格,表示如果,A1,的数值大,于等于,60,则返回及格，否则就返回不,及格,注意，函数中使用中文字符的时,候一定要用英文冒号隔起来，否则无法,识别,Excel,函数,这就是使用的实例,Excel,函数,and(,函数表示“和”的意思,or(,函数表示,或者”的意思，这两个函数极少单独运用,一般是和其他函数嵌套在一起，这里只做个大,概的说明。例如,if(and(A150,A160,适合,不适合,表示在,A1,的取值为,50,到,60,之间的情,

14、况下，返回”适合”，其他情况下返回”不适,合,Not(,函数表示对其他逻辑函数的值取反结果,Excel,函数最多允许嵌套七层,Excel,函数,4,文本、日期与时间函数,常用的文本函数有,left(),right(),mid(),len(),int(,等,常用的日期与时间函数有,now(),today(),year(),month(),day(),date(),ho,ur(),date(),datedif(,等,Excel,函数,Left(,函数表示从单元格文本左边开始取,一定的位数,Left,取值的单元格,取值的,位数,例如,left(A1,2,表示从,A1,单元格中,的文本中取前两位,Ri

15、ght(,函数的用法和,left(,的用法类似,不过是从文本的右边开始取值,Excel,函数,Mid(,函数表示的是从单元格文本中从指,定的位数开始取指定的长度,Mid,取值的单元格,开始的位数,取值的长,度,例如,mid(A1,3,2,表示从,A1,中文本的,第三位开始，取两位的文本,Len(,函数表示取单元格中数据的长度,int(,函数表示对单元格的数字取整数,Excel,函数,Now(,和,today(,表示的是电脑当前的时间,两者没有太大的区别,Year(,函数是取一个时间里的年份，例如,year(2011-5-19,得出的结果就是,2011,Month(,函数是取一个时间里的月份,d

16、ay(,函数是取一个时间里的天,hour(,函,数是取一个时间里的小时，用法和,year(,类似,Excel,函数,Date(,函数是把几个数字生成一个时间,用法为,date,年,月,日,例如,date(2008,9,9,最后生成的结果是,2008-9-9,Datedif(,函数是求两个时间之间的差值,用法为,datedif,开始时间,结束时间,时间计,算方式,其中时间计算方式有年、月,日、一年中相差的月份、一月中相差的,天数这五种方式,Excel,函数,例如,datedif(2008-5-6,2010-5-8,Y,最后的结果就是,2,表示两个时间之间的差为两年,datedif(2008-5-

17、6,2010-5-8,m,最后的结果为,24,表示两个时间之间的差为,24,个月,datedif(2008-5-6,2010-5-8,d,最后的结果为,732,表示两个时间之间的差为,732,天,datedif(2008-5-6,2010-5-8,ym,最后的结果为,0,表,示两个时间在一年中的差为,0,个月,datedif(2008-5-6,2010-5-8,md,最后的结果为,2,表,示两个时间在一个月中的差为,2,天,Excel,函数,5,查询和引用函数,vlookup(,函数在实际运用,中比较多，一般结合数据库来使用。所谓数据,库就是把所有的数据分门别类，全部定好格式,集中到一起的就算是一个数据库了,Vlookup,要查找的值,查找区域,返回区域对应的,栏数,是否模糊匹配,Excel,函数,此处,G2,单元格根据,F2,单元格的姓名使用,Vlookup,函数自动匹配了部门，在函数中,F2,表