excel实用技巧.ppt

Excel常用技巧行政管理部,Excel常用技巧,一、常用格式设置二、统计常用方法三、Excel函数,常用格式设置,1.边框及底纹 在excel中，表格都是已经给定的，我们所做的就是在输入数据后，在给定的表格外绘制边框。选定区域后，单击右键-设置单元格格式-边框。根据所需要的边框添加，或者进入格式-自动套用格式，使用喜欢的类型。图表的背景色可以在单元格格式-图案中选择，或者点击工具栏上的填充按钮，选择需要的颜色。,常用格式设置,2.合并及居中 当需要对多个单元格进行合并时，可以将这些单元格选定后，点击工具栏上的合并及居中按钮，如需取消，再点击一次即可。,常用格式设置,3.对齐 在单元格设置-对齐里面，可以设置单元格的对齐方式、文字方向、自动换行等参数。（手动换行需要按Alt+Enter）,常用格式设置,4.数字格式 单元格格式-数字里面，可以设置单元格的格式，方便输入各种数据。例如身份证号码在单元格就会自动用科学计数法，最后几位显示不出来，这就需要把单元格设置成文本格式。,常用格式设置,5.页面设置 页面设置是对需要打印表格进行页面、页边距、页眉页脚的设置，在文件-页面设置里面，这些设置不用详细讲。对于打印范围比较大，需要每页设置标题的文档，我们可以在页面设置-工作表里点击“顶端标题行”，将每页需要添加的标题行选进去，打印出来的文档每页就自动加上了标题行。,常用格式设置,6.数据有效性 点击数据-有效性，可以为单元格设置有效的数据类型，默认的是任何值。我们可以将其进行改成我们所允许的值，在工作中就可以避免输入错误。我们也可以对出错提示自定义。,常用格式设置,7.条件格式 条件格式能够在单元格条件满足设定时，自动变化单元格格式。点击格式-条件格式进入设置，设定条件，在格式中选定满足条件时单元格格式的变化。使用条件格式能够及时为数据变化做出提醒。,常用格式设置,8.格式刷 格式刷能够非常迅速地将已设定好的单元格格式进行批量复制，减少重复工作，提高效率。格式刷在常用工具栏里，使用时先选定被复制格式的单元格，点击格式刷，然后在需要复制的单元格拖动即可。,常用格式设置,9.通配符（?、*、）通配符一般使用在搜索中，?表示一个任意字符，例如搜索“d?f”表示查找以d开头f结尾的任意一个三字母的词；*表示任意个任意字符，例如搜索“d*f”表示查找以d开头，f结尾的任意一个词，不限于三个字母；则用在搜索?和*本身上面，例如搜索“d?f”表示搜索搜索d?f这个词。,统计常用方法,1.筛选（自动筛选、高级筛选）2.分类汇总3.数据透视表4.函数统计（略）,统计常用方法,1.筛选 自动筛选。在数据输入完成后，选定需要筛选的项目一行，点击数据-筛选-自动筛选，选定 的项目就会多出下拉菜单，在其中即可选择需要筛选的条件。取消自动筛选按上述重复。,统计常用方法,1.筛选 高级筛选。高级筛选中在原有区域显示筛选结果和自动筛选差不多，一般用得较多的是将筛选结果复制到其他位置，这样就能对筛选出来的结果进行下一步的处理。,统计常用方法,右边就是对这些区域进行高级筛选，筛选的区域为左边四列，筛选的条件为性别-男性。最后生成的结果如下：,统计常用方法,2.分类汇总 位置在数据-分类汇总里，前提条件是分类的项目必须经过排序。例如下列数据里按性别分类排序，再做计数汇总：,统计常用方法,3.数据透视表 数据透视表为数据汇总及制表提供了一个极其方便的拖拉操作方式，所有需要统计的字段直接在数据透视表的操作界面中操作。,统计常用方法,为了避免破坏原数据，一般选择新建工作表，在布局中直接拖拉需要统计的项目。例如统计不同部门的性别统计。结果如下：,统计常用方法,4.函数统计 函数统计放在后面的excel函数里进行讲解。,Excel函数,Excel中所提的函数其实是一些预定义的公式，它们使用一些称为参数的特定数值按特定的顺序或结构进行计算。Excel函数一共有11类，分别是数据库函数、日期与时间函数、工程函数、财务函数、信息函数、逻辑函数、查询和引用函数、数学和三角函数、统计函数、文本函数以及用户自定义函数。,Excel函数,因为在很多教材中的说法都说的很书面化，所以在这里尽量用通俗的语言来解释常用函数的含义和用法，并举例来说明。在日常工作中，我们不会用到所有分类的函数，而且在所用的分类的函数中，我们用到的也是很少的一部分，所以我们学习的目的是理解函数的思路。理解了函数的思路后，我们碰到新的函数也能迅速明白它的用法并运用到实际工作中去。,Excel函数,算术运算符,Excel函数,文本运算符“&”&可以把文本连接起来，例如A1&B1，可以把两个单元格合并为一个文本。比较运算符,Excel函数,引用运算符,运算顺序默认的运算顺序是乘方乘除加减，相同优先级的按从左到右的顺序运算，若要改变顺序，可以加上圆括号。,Excel函数,单元格引用 一般常用的引用为相对引用，如=A1*2，优点在于拖拉函数的时候对应的引用能够自动变化；绝对引用则是拖动函数填充时，不会自动变化：=$A$1*2;混合引用则是在一个单元格中，既有相对引用，也有绝对引用，如：$A1(行变列不变)A$1(列变行不变)输入函数的方法：1.使用函数编辑栏 2.直接在单元格中输入注意：函数中输入的括号及标点都必须使用英文格式,Excel函数,日常工作中常用的函数有：1.求和函数2.统计函数3.逻辑函数4.文本、日期与时间函数5.查询和引用函数6.多层函数嵌套,Excel函数,1.求和函数sum()和sumif()函数求和函数平时用到的主要是sum()和sumif()函数。Sum()函数大家应用的最多，这里就不多做介绍，它最多支持30组数据相加。如果超过了30组数据也有个小窍门，你可以在外面再加一个括号，那么就相当于把这些数据变成了一个组，那就可以进行求和。,Excel函数,Sumif()函数是条件求和函数，比如说你要对一个区域数据满足条件的进行求和，那就可以用这个函数了。Sumif(条件范围,条件,求和范围)，在括号中隔开一定要用英文的逗号，否则无法识别。例如sumif(A1:A100,”1”,B1:B100)表示在A1到A100范围内大于1的条件下对应的B1:B100范围里求和，条件范围和求和范围一定要相互对应。,Excel函数,2.统计函数count()、counta()、countif()和sumproduct()Count()函数对数字型的数据进行统计，对文本、错误值、无法转换成数字的数据进行忽略，使用方法为count(统计范围)。例如count(A1:A100)表示对A1到A100范围内的所有数字型数据进行统计，如果中间有文本、错误值等，则不进行统计。,Excel函数,Counta()函数则是统计一个范围内的非空单元格，使用方法为counta(统计范围)。例如counta(A1:A100)，表示统计A1到A100范围内的所有非空单元格，如果有单元格为空的，则不进行统计，如果单元格里没有任何数字文本，但是打了一个空格，函数仍然会进行统计，所以这点一定要注意，很容易造成错误。,Excel函数,Countif()函数是条件统计函数，应用的非常广泛，很多报表都可以用它来自动生成，当然这是运用得比较熟练以后的事情，这里主要讲基础用法。用法Countif(统计范围,统计条件)，例如countif(A1:A100,”1”)表示对A1到A100范围内所有大于1的数据进行统计。,Excel函数,Sumproduct()函数是多条件统计函数，可以对需要同时满足多个条件的结果进行统计，具体使用方式为：sumproduct(条件1)*(条件2)*(条件3),Excel函数,3.逻辑函数if()、and()、or()和not()这几个是日常工作中用得比较多的逻辑函数，尤其是if()函数用得最多。用法if(条件,满足条件返回的结果,不满足条件返回的结果)，例如if(A1=60,”及格”,”不及格”)，表示如果A1的数值大于等于60，则返回及格，否则就返回不及格。(注意，函数中使用中文字符的时候一定要用英文冒号隔起来，否则无法识别),Excel函数,这就是使用的实例,Excel函数,and()函数表示“和”的意思，or()函数表示“或者”的意思，这两个函数极少单独运用，一般是和其他函数嵌套在一起，这里只做个大概的说明。例如if(and(A150,A160),”适合”,”不适合”)表示在A1的取值为50到60之间的情况下，返回”适合”，其他情况下返回”不适合”。Not()函数表示对其他逻辑函数的值取反结果Excel函数最多允许嵌套七层。,Excel函数,4.文本、日期与时间函数常用的文本函数有left(),right(),mid(),len(),int()等常用的日期与时间函数有now(),today(),year(),month(),day(),date(),hour(),date(),datedif()等。,Excel函数,Left()函数表示从单元格文本左边开始取一定的位数。Left(取值的单元格,取值的位数)，例如left(A1,2)表示从A1单元格中的文本中取前两位。Right()函数的用法和left()的用法类似，不过是从文本的右边开始取值。,Excel函数,Mid()函数表示的是从单元格文本中从指定的位数开始取指定的长度。Mid(取值的单元格,开始的位数,取值的长度)，例如mid(A1,3,2)表示从A1中文本的第三位开始，取两位的文本。Len()函数表示取单元格中数据的长度。int()函数表示对单元格的数字取整数,Excel函数,Now()和today()表示的是电脑当前的时间，两者没有太大的区别。Year()函数是取一个时间里的年份，例如year(2011-5-19)得出的结果就是2011。Month()函数是取一个时间里的月份，day()函数是取一个时间里的天，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-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表示要查找的值，A2:D7表示查找区域，4表示查找区域中对应的第四列，0表示精确匹配。如果最后使用1或者省略，返回的结果没有匹配数据，将会自动返回最接近的一个数值。,Excel函数,6.多层函数嵌套 多层函数嵌套能够在很大程度上减少我们的计算过程，增强函数的适用性，对基本的函数比较熟练后，可以尝试使用多层函数。（函数嵌套最多支持7层）上面就是根据入职时间，使用一个简单的双层嵌套函数计算工龄的方法。TODAY()时间函数表示的是当前电脑的时间，所以这个函数能够自动刷新，保证计算的结果不出错。,请大家有空多多练习谢谢！,