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 A1 50 A1 60 适合 不适合 表示在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 D