excel实用技巧.ppt

《excel实用技巧.ppt》由会员分享，可在线阅读，更多相关《excel实用技巧.ppt（45页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

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

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

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

4、符（?、*、）通配符一般使用在搜索中，?表示一个任意字符，例如搜索“d?f”表示查找以d开头f结尾的任意一个三字母的词；*表示任意个任意字符，例如搜索“d*f”表示查找以d开头，f结尾的任意一个词，不限于三个字母；则用在搜索?和*本身上面，例如搜索“d?f”表示搜索搜索d?f这个词。统计常用方法o1.筛选（自动筛选、高级筛选）o2.分类汇总o3.数据透视表o4.函数统计（略）统计常用方法o1.筛选 自动筛选。在数据输入完成后，选定需要筛选的项目一行，点击数据-筛选-自动筛选，选定 的项目就会多出下拉菜单，在其中即可选择需要筛选的条件。取消自动筛选按上述重复。统计常用方法o1.筛选 高级筛选。高

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

6、。例如统计不同部门的性别统计。结果如下：统计常用方法o4.函数统计 函数统计放在后面的excel函数里进行讲解。Excel函数oExcel中所提的函数其实是一些预定义的公式，它们使用一些称为参数的特定数值按特定的顺序或结构进行计算。Excel函数一共有11类，分别是数据库函数、日期与时间函数、工程函数、财务函数、信息函数、逻辑函数、查询和引用函数、数学和三角函数、统计函数、文本函数以及用户自定义函数。Excel函数o因为在很多教材中的说法都说的很书面化，所以在这里尽量用通俗的语言来解释常用函数的含义和用法，并举例来说明。o在日常工作中，我们不会用到所有分类的函数，而且在所用的分类的函数中，我们

7、用到的也是很少的一部分，所以我们学习的目的是理解函数的思路。理解了函数的思路后，我们碰到新的函数也能迅速明白它的用法并运用到实际工作中去。Excel函数算术运算符算术运算符算术运算符含义示例+加2+3=5-减3-2=1*乘2*3=6/除10/2=5乘方43=64%百分比50%Excel函数o文本运算符文本运算符“&”&可以把文本连接起来，例如A1&B1，可以把两个单元格合并为一个文本。o比较运算符比较运算符比较运算符比较运算符含义含义示例示例=等于B1=C1，如果两单元格相等，返回TURE，如果不相等，FALSE大于略=大于等于略=小于等于略不等于略Excel函数o引用运算符引用运算符引用运算

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

9、A$1*2;混合引用则是在一个单元格中，既有相对引用，也有绝对引用，如：混合引用则是在一个单元格中，既有相对引用，也有绝对引用，如：$A1(行变列不变行变列不变)A$1(列变行不变列变行不变)输入函数的方法：输入函数的方法：1.使用函数编辑栏使用函数编辑栏 2.直接在单元格中输入直接在单元格中输入注意：函数中输入的括号及标点都必须使用英文格式注意：函数中输入的括号及标点都必须使用英文格式Excel函数日常工作中常用的函数有：o1.求和函数o2.统计函数o3.逻辑函数o4.文本、日期与时间函数o5.查询和引用函数o6.多层函数嵌套Excel函数1.求和函数sum()和sumif()函数o求和函数

10、平时用到的主要是sum()和sumif()函数。oSum()函数大家应用的最多，这里就不多做介绍，它最多支持30组数据相加。如果超过了30组数据也有个小窍门，你可以在外面再加一个括号，那么就相当于把这些数据变成了一个组，那就可以进行求和。Excel函数oSumif()函数是条件求和函数，比如说你要对一个区域数据满足条件的进行求和，那就可以用这个函数了。oSumif(条件范围,条件,求和范围)，在括号中隔开一定要用英文的逗号，否则无法识别。例如sumif(A1:A100,”1”,B1:B100)表示在A1到A100范围内大于1的条件下对应的B1:B100范围里求和，条件范围和求和范围一定要相互对

11、应。Excel函数o2.统计函数count()、counta()、countif()和sumproduct()oCount()函数对数字型的数据进行统计，对文本、错误值、无法转换成数字的数据进行忽略，使用方法为count(统计范围)。例如count(A1:A100)表示对A1到A100范围内的所有数字型数据进行统计，如果中间有文本、错误值等，则不进行统计。Excel函数oCounta()函数则是统计一个范围内的非空单元格，使用方法为counta(统计范围)。例如counta(A1:A100)，表示统计A1到A100范围内的所有非空单元格，如果有单元格为空的，则不进行统计，如果单元格里没有任何数

12、字文本，但是打了一个空格，函数仍然会进行统计，所以这点一定要注意，很容易造成错误。Excel函数oCountif()函数是条件统计函数，应用的非常广泛，很多报表都可以用它来自动生成，当然这是运用得比较熟练以后的事情，这里主要讲基础用法。o用法Countif(统计范围,统计条件)，例如countif(A1:A100,”1”)表示对A1到A100范围内所有大于1的数据进行统计。Excel函数oSumproduct()函数是多条件统计函数，可以对需要同时满足多个条件的结果进行统计，具体使用方式为：sumproduct(条件1)*(条件2)*(条件3)Excel函数o3.逻辑函数if()、and()、

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

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

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

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

17、)最后的结果为24，表示两个时间之间的差为24个月。o datedif(2008-5-6,2010-5-8,”d”)最后的结果为732，表示两个时间之间的差为732天。odatedif(2008-5-6,2010-5-8,”ym”)最后的结果为0，表示两个时间在一年中的差为0个月。odatedif(2008-5-6,2010-5-8,”md”)最后的结果为2，表示两个时间在一个月中的差为2天。Excel函数o5.查询和引用函数。vlookup()函数在实际运用中比较多，一般结合数据库来使用。所谓数据库就是把所有的数据分门别类，全部定好格式集中到一起的就算是一个数据库了。oVlookup(要查找

18、的值,查找区域,返回区域对应的栏数,是否模糊匹配)Excel函数o此处G2单元格根据F2单元格的姓名使用Vlookup函数自动匹配了部门，在函数中，F2表示要查找的值，A2:D7表示查找区域，4表示查找区域中对应的第四列，0表示精确匹配。o如果最后使用1或者省略，返回的结果没有匹配数据，将会自动返回最接近的一个数值。Excel函数o6.多层函数嵌套 多层函数嵌套能够在很大程度上减少我们的计算过程，增强函数的适用性，对基本的函数比较熟练后，可以尝试使用多层函数。（函数嵌套最多支持7层）上面就是根据入职时间，使用一个简单的双层嵌套函数计算工龄的方法。TODAY()时间函数表示的是当前电脑的时间，所以这个函数能够自动刷新，保证计算的结果不出错。请大家有空多多练习谢谢！