企业培训_excel实战技巧培训1

Excel实战技巧培训 2014 1 09 Herotian 2 目录 前言 操作与技巧 原来可以如此简单 表格之美 表格也是门艺术 函数与公式 Excel的精髓之处 1 2 4 3 00 01 02 03 04 05 06 07 图表 秀的是专业 拼的是创意 5 6 函数综合应用 3 前言 Excel学习进级有哪些阶段 4 前言 学习的烦恼 困惑与疑问 5 前言 成为 高手 的捷径 心态 方法 坚持 No 3持之以恒的努力 每天进步一点点 No 2正确的方法循序渐进 先易后难寻找资源 广泛摄取思路与技巧学习与实践相结合 解决工作实际问题 No 1积极的心态 兴趣是最好的老师 压力也是前进的动力 6 前言 关于本次内容说明 7 目录 前言 操作与技巧 原来可以如此简单 表格之美 表格也是门艺术 函数与公式 Excel的精髓之处 1 2 4 3 00 01 02 03 04 05 06 07 图表 秀的是专业 拼的是创意 5 6 函数综合应用 8 操作与技巧基础 进级之路的起点 内容1 排序2 筛选3 查找与替换4 粘贴5 工作表 工作簿的保护6 数据有效性7 条件格式8 名称的运用 9 操作与技巧基础 排序 10 操作与技巧基础 筛选 自动筛选 11 操作与技巧基础 查找与替换 12 操作与技巧基础 选择性粘贴 13 操作与技巧基础 效率加倍的快捷键 14 目录 前言 操作与技巧 原来可以如此简单 表格之美 表格也是门艺术 函数与公式 Excel的精髓之处 1 2 4 3 00 01 02 03 04 05 06 07 图表 秀的是专业 拼的是创意 5 6 函数综合应用 15 操作与技巧进阶 单元格格式 单元格格式 EXCEL单元格式有 数字格式 对齐 字体 边框 填充 保护6种属性快速套用单元格样式修改内置样式自定义单元格样式数字格式及自定义日期格式 16 操作与技巧进阶 工作表 工作簿的保护 工作表 工作薄的保护 工作薄的保护工作表 单元格的保护总的来说利用设置密码只能对EXCEL表格提供一般安全性保护 安全等级并不高 17 操作与技巧进阶 数据有效性 数据有效性 数据有效性的作用 限制单元格的输入或圈识无效数据 做单元格下拉表利用数据有效性对单元格输入限制单元格下拉表是数据有效性应用的重要内容如一级下拉表 二级下拉表 三级下拉表 18 操作与技巧进阶 条件格式 条件格式 利用条件格式主要功用 按规则突出显示按规则建立标示显示 19 操作与技巧进阶 名称的运用 名称是什么东东 名称 实际上是Excel 对象 的命名 而所谓对象可以是 单元格单元格区域数值 数组常量公式宏表函数 名称的运用 20 操作与技巧进阶 名称的运用 名称的运用 为什么要使用名称 21 操作与技巧进阶 名称的运用 名称的运用 关于名称的约定 22 操作与技巧进阶 名称的运用 名称的运用 定义名称的三种方法 方法1 插入名称 方法2 使用名称框 方法3 根据所选内容创建 批量 23 操作与技巧进阶 为你的工作薄 减肥 工作薄 减肥 工作表中存在大量的细小图形对象对策 按F5 在定位对话框中单击定位条件 选中对象确定检查是否有细小图形 选中图形进行删除 较大区域内设置了单元格格式或条件格式对策 查看工作表滚动条 查看滑标是否很小 且拖动滑标向小或向右到底可以到达很大的行号或列标 如果这样说明有相当大的一块区域设置了单元格格式或条件格式 选择区域单元格式设置为常规 大量区域包含数据有效性对策 在很大区域包含不必要的数据有效性也会造成文件体积很大 在数据有效性查看并消除不必要区域的数据有效性 包含大量复杂公式对策 优化公式的使用包含大容量图片元素对策 对图片进行压缩 共享工作薄引起的体积虚增对策 共享工作薄会产生许多过程数据 时间长了文件体积会增长几倍甚至十几倍 优化方法 取消共享工作薄设置 然后再重新保存文件 当需要共享时 再打开设置 改变工作薄文件存储格式对策 将文件保存为二进制工作薄文件 24 目录 前言 操作与技巧 原来可以如此简单 表格之美 表格也是门艺术 函数与公式 Excel的精髓之处 1 2 4 3 00 01 02 03 04 05 06 07 图表 秀的是专业 拼的是创意 5 6 函数综合应用 25 函数与公式 热身运动Excel运算符 让我们一起来看看Excel的常用运算符 1 26 函数与公式 热身运动Excel运算符 让我们一起来看看Excel的常用运算符 2 27 函数与公式 热身运动公式 函数 公式 公式 是以等号 开头 通过使用运算符将数据 函数等元素按一定顺序连接在一起 从而实现对工作表中的数据执行计算的等式 例如C1 A1 A2 A3 A4 B1 B2 B3 B4 28 函数与公式 函数的结钩分析 29 函数与公式 热身运动快速输入函数 30 目录 前言 操作与技巧 原来可以如此简单 表格之美 表格也是门艺术 函数与公式 Excel的精髓之处 1 2 4 3 00 01 02 03 04 05 06 07 图表 秀的是专业 拼的是创意 5 6 函数综合应用 31 函数起步 F1 不离不弃的好兄弟 自学函数的捷径全在这里了记住 excel本身就是最好的参考书 F1 哪里不会点哪里 32 函数起步 引用 相对 绝对 由你说了算 33 函数起步 TRUEvsFALSE Excel的逻辑值只有2个 TRUEFALSE TRUEFALSE与运算符进行各种计算时 请牢记 TRUE 1FALSE 0 请务必牢记 34 函数起步 逻辑判断是函数的灵魂 串联开关等同于逻辑函数AND 即多个条件同时满足 比如 TRUE TRUE 并联开关等同于逻辑函数OR 即只要满足一个条件 比如 TRUE FALSE 35 函数知多少 常用函数简介 ANDORFALSEIFNOTTRUE 逻辑函数 数学函数 COUNTCOUNTIFCOUNTACOUNTBLANKMODE 统计函数 ABSSUMSUMIFSUMPRDUCTMAXMINSMALLAVERAGEINT 查找与引用函数 ADDRESSCHOOSECOLUMNCOLUMNSHLOOKUPLOOKUPVLOOKUPTRANSPOSEINDEXMATCHINDIRECTOFFSETROWFIND LEFTLENLOWERUPPERMIDREPLACESEARCHSUBSTITUTETEXTVALUE 文本函数 DATEDATEIFMONTHYEARHOURTODAYMINUTEWEEKDAY 日期函数 CELLCOUNTBLANKISERRISNA 信息函数 36 函数应用层次 从简单到复杂的演变之路 37 函数介绍 求和类函数 求和类函数最主要的有4个函数SUM是最常用的求和函数 但其用法却并不简单 SUMIF SUMPRDUCT是条件求和函数 是求和函数中两个耀眼的明星 其应用十分多变灵活 SUMIFS是多条件函数 其应用相对SUMIF是要广泛 38 函数介绍 计数类函数 计数类函数最主要的有4个函数COUNT是最常用的对数字类单元格计数的函数 COUNTA是对文本类单元格进行计数的函数 COUNTIF是按条件对单元格进行计数的函数 其应用在计数问题中十分广泛 COUNTIFSExcel2007中新增函数 为COUNTIF的扩展 用法与COUNTIF类似 但COUNTIF针对单一条件 而COUNTIFS可以实现多个条件同时求结果 39 函数介绍 查找与引用类函数 查找与引用类函数最主要的有9个函数LOOKUP HLOOKUP VLOOKUP是最常用的查找函数 FIND INDEX MATCH INDIRECT也是较常用的函数 但用法广泛且灵活多变 OFFSET是一个动态查找函数 其用法十分灵活 ROW与OFFSET搭配使用的情况较多 且多以复合加数组的形式运用 40 函数介绍 文本类函数 文本类函数最常用的有4个函数LEN函数是用于文本的统计文本长度的 LEFT函数是用于返回字符函数MID函数也是一个返回字符的函数 TEXT函数被称为 文本之王 足见其应用之广泛灵活 41 函数介绍 日期 时间类函数 日期与时间类函数常用的有5个函数LOOKUP HLOOKUP VLOOKUP是最常用的查找函数 FIND INDEX MATCH INDIRECT也是较常用的函数 但用法广泛且灵活多变 OFFSET是一个动态查找函数 其用法十分灵活 ROW与OFFSET搭配使用的情况较多 且多以复合加数组的形式运用 42 函数介绍 信息类函数 ISNA ISERR N CELL 信息类函数常用的有4个函数CELL函数是函数返回有关单元格的格式 位置或内容的信息 N函数是返回转化为数值后的值 ISNA函数是如果值为错误值 N A 则返回TRUE 常用于处理消除 N AISERR函数如果值为除 N A以外的任何错误值 则返回TRUE 常用于处理消除 N A 43 函数应用实战 SUM函数 SUM函数是最常见的求和函数 很多人以为这个函数很简单 但实际是这个函数用法也是很丰富的 下面就主要用法介绍一下 行 列 区域求和 这是SUM函数最常见的用法按条件求和或文本计数 数组公式的运用与IF COUNTIF SUMIF等函数组合使用 44 函数应用实战 countif函数 COUNTIF函数是个按条件计数类函数 其基本语法看似很简单 但参数表达形式十分灵活 针对不同情况计数 另外也常与SUM搭档使用 COUNTIF按条件计数与SUM等函数组合使用COUNTIF在2007版后多了一个兄弟 COUNTIFS 是对多条件计数 45 函数应用实战 SUMIF函数 SUMIF函数是在常用函数中绝对可以算是一个 明星 函数 应用十分广泛 单就其语法而言是很简单 同样其参数样式也是变化多端的 与其它函数搭配使用也是它大显身手的地方 SUMIF按条件求和与SUM等函数组合使用SUMIF在也有一个同胞兄弟 SUMIFS 是对多条件求和其适用范围比SUMIF要广泛得多 46 函数应用实战 SUMPRODUCT函数 SUMPRODUCT函数是在常用函数中也算是一个 明星 函数 应用十分灵活 与其它函数搭配使用也是它大显身手的地方 SUMPRODUCT多条件求和 基本用法两列 行相乘相加 变形用法 这一用法在数据累计求和 单一求和十分便捷 在按条件计数问题 不重复文本计数个数等问题均有应用 47 函数应用实战 VLOOKUP函数 VLOOKUP函数是在查找函数中最常用的一个 与LOOKUP HLOOKUP一起成为查找 三剑客 与其它函数搭配使用也是它大显身手的地方 VLOOKUP基本用法 精确匹配查询 数值近似匹配查询 文本近似匹配查询 按部分内容模糊查询VLOOKUP从右向左查询VLOOKUP HLOOKUP LOOKUP用法各有区别 48 函数应用实战 INDEX MATCH INDEX MATCH函数被称为 查找双雄 其功用十分强大INDEX MATCH MATCH单条件矩阵查找 多条件矩阵查找 求和问题需其他方法 INDEX MATCH单条件行与行 列与列查找INDEX MATCH INDEX MATCH MATCH与SMALL ROW COLUMN等函数组合运用 49 函数应用实战 TEXT函数 TEXT函数有 文本之王 之称 足见其功用之强大TEXT函数基本语法不复杂 但其参数规则变化多端 其运用十分灵活TEXT函数基本用途 条件式格式 强制式格式 提取型式格式TEXT函数与其它函数组合运用 50 函数应用实战 MID函数 MID函数是一个文本取值类函数 用于提取出字符中的指定字符 MID函数单独使用功用并不大 常与之搭配使用的函数有 LEN LEFT TEXT FIND RIGHT等使用 用于解决一些复杂情况下的文本提取问题 51 典型计算问题处理方法 极值问题 问题背景 在很多情况下我们需要求极值 如最大值或最小值 单一数据来求这类问题是很简单的 一旦面对的是按条件求极值问题 情况就要复杂多 如果平常没有这类问题解决方法的积累或是不会找资源来解决 通常你就只能望题兴叹了 最大值与最小值问题按条件来求解最大值与最小值的问题 52 典型计算问题处理方法 均值问题 问题背景 在很多情况下我们需要求极值 但也有需要求均值的情形 单一数据来求均值是很简单的问题 实际中往往我们面对的都是按条件求均值问题 简单的来说就是按条件来求平均值 该怎么办 面对这一问题解决的方法有很多 求平均值问题按条件求平均值问题 53 典型计算问题处理方法 排名问题 问题背景 对于排名问题可能很多人都熟悉会用RANK函数 但是不是RANK函数能满足所有情况下的排名问题呢 答案显然是 NO RANK函数本身有其局限性 对于特定要求的排名问题就无能为力 RANK函数用法对于含有重复值排名问题 54 典型计算问题处理方法 日期最早与最晚问题 问题背景 在很多情况下我们需要求极值 日期计算最早与最晚问题也是求极值问题当中的一类问题 单纯对一列日期求最早与最晚是很简单的问题 但实际中往往我们碰到都是按条件求极值问题 日期的这类问题也不例外 日期最早与最晚数组公式在条件求极值问题中的运用 55 典型计算问题处理方法 纯数字日期处理问题 问题背景 在有信息系统的公司或工厂 很多数据需要从系统中导出再做处理 而在这当中几乎所有的日期都是纯数字格式如20130812 当我们计算需要用到日期时 这时就需要对日期格式进行转换 转换成我们可操作的常规日期格式 纯数字日期转换的几种方法TEXT在转换公式中的用法 56 典型计算问题处理方法 反向查询问题 问题背景 有时在计算过程中我们需要将目标数字进行从小到大或从大到小排列 但普通公式对此却是无能为力 看似小问题倒成了大的计算难题 横向从小到大和从大到小排列列向从小到大和从大到小排列 57 典型计算问题处理方法 数字按大小排序问题 问题背景 有时在计算过程中我们需要将目标数字进行从小到大或从大到小排列 但普通公式对此却是无能为力 看似小问题此时倒成了大难题 横向从小到大和从大到小排列列向从小到大和从大到小排列 58 典型计算问题处理方法 24小时制排班工时计算问题 问题背景 在没有信息考勤系统的公司或工厂 从纸质考勤过渡到EXCEL格式 怎么样快速统计人员出勤状况就成了人事部门面前的一个计算难点 两个24小时制时间段内的工时计算 考勤中文字部分的处理 逻辑计算与表达的设计按条件求和公式的运用 59 函数内功的修炼 日积月累 学以致用 函数是Excel中最核心的部分 是技巧 图表 透视表 VBA的基本功 同时函数本身内容也相当博大精深 值得我们不断的去学习 思考如何让工作更轻松 同时 由于函数内容太多 不需要大家把每个函数都去掌握由于函数很有深度 很多函数都值得反复的去学习 去发现由于许多函数在理解上比较困难 不可能短期内达到某个高度 因此 建议大家要 日积月累 学以致用 60 表格秀 欧风图表 如何制作好的报表图表学习资源介绍 目录 前言 操作与技巧 原来可以如此简单 表格之美 表格也是门艺术 函数与公式 Excel的精髓之处 1 2 4 3 00 01 02 03 04 05 06 07 图表 秀的是专业 拼的是创意 5 6 函数综合应用 61 表格秀 欧风 报表也可以这样做 62 表格秀 欧风 报表也可以这样做 63 表格秀 欧风 报表也可以这样做 64 表格秀 欧风 报表也可以这样做 65 表格秀 欧风 报表也可以这样做 66 如何制作好的报表 表哥 表姐 的内功修炼 简约清爽 高度集成 艺术美感 67 目录 前言 操作与技巧 原来可以如此简单 表格之美 表格也是门艺术 函数与公式 Excel的精髓之处 1 2 4 3 00 01 02 03 04 05 06 07 图表 秀的是专业 拼的是创意 5 6 函数综合应用 68 图表 图表的制作过程 理解数据分析数据提炼信息明确主题 提炼信息 选择图表 制作图表 数据图表 确定比较关系确定图表类型确定图表处理方式 准备做图数据制作美化图表检查确认图表 69 图表 图表类型的选择 70 图表 图表秀 商风 71 图表 图表秀 商风 72 图表 图表秀 变形图 73 图表 精雕细琢方显专业气质 74 图表 商业风格图表基本样式 色彩 布局之美 享受而非污染 简约而不