Transcript Excel实战培训
Excel实战技巧培训 2011-09-10 Jim.Zhang 目录 00 01 02 前言 操作与技巧 ——原来可以如此简单 03 04 05 06 函数与公式 数据透视表 图表 总结 ——Excel的精髓之处 ——百变星君 ——专业的我 与作 也可以做到 1 Excel 实战技巧培训 2 07 3 4 5 业 6 2 前言——Excel有些什么 VBA编程 VBA编程 透视表 透视表 函数、图表 函数、图表 基本操作 基本操作 Excel 实战技巧培训 3 前言——烦恼、困惑与疑问 Excel中如此多的功能、 菜单、函数、图表类 型、VBA代码，如何 做到灵活运用？ 工作中天天用到Excel,但 除了简单的操作,其他技 巧怎么也掌握不了.重复 的操作耗费大量的时间、 精力！ 我对Excel很感兴趣、 可不知从何学起？ 有没有一套有效的、 快速的学习Excel技能 的方法？ 疑问 困惑 烦恼 Excel 实战技巧培训 4 前言——成为“高手”的捷径 No.1 积极的心态 “兴趣是最好的老师、压力是前进的动力” 心态 方法 No.2 正确的方法 • 循序渐进 • 善用资源、学以致用 • 阅读与实践的结合 坚持 No.3持之以恒的努力，每天进步一点点 Excel 实战技巧培训 5 前言——关于本次培训 构成 知识点 20% 实例讲解 80% 注重 解决问题的思路 学习Excel的方法 说明 采用Excel 2010 进行讲解，尽量向 低版本兼容 Excel 实战技巧培训 6 前言——起步之前让我们回顾几个场景 场景1 Excel+计算器 场景2 Excel取代计算器 场景4 Vlookup + sumif 场景3 单元格表间运算 场景5 高级公式+透视表 场景6 Excel+数据库 Excel 实战技巧培训 …... 7 目录 00 01 02 前言 操作与技巧 ——原来可以如此简单 03 04 05 06 函数与公式 数据透视表 图表 总结 ——Excel的精髓之处 ——百变星君 ——秀的是专 与作 业、拼的是创 意 1 07 2 3 4 5 业 6 基础中的基础 排序 筛选 查找与替换 定位 选择性粘贴 效率加倍的快捷键 Excel 实战技巧培训 8 操作与技巧基础——排序 排序的基本操作 案例1:如何按汉字笔画排序 案例2:如何用排序制作工资条 案例3:如何按行排序 案例4:如何对合并单元格相邻的数据区域排序 案例5:如何按单元格颜色排序（2007以上版本) Excel 实战技巧培训 9 操作与技巧基础——筛选（自动筛选) 筛选的基本操作 案例1:如何对双行标题列表进行筛选 案例2:筛选中数据区域的注意事项 案例3:如何进行反向选择 案例4:如何不增加辅助列对日期按星期筛选 Excel 实战技巧培训 10 操作与技巧基础——查找与替换 查找与替换基本操作 案例1:如何清除或修改特定格式的单元格内容 案例2:如何查找批注中的内容 案例3:如何在整个工作簿查找 案例4:如何删除工作表中的通配符和特定字符 Excel 实战技巧培训 11 操作与技巧基础——定位 定位的概念和基本操作 案例1:如何披量清除工作表上的各种对象 案例2:如何运用定位行列差异单元格 案例3:如何利用定位将数据填充完整（测试题1) Excel 实战技巧培训 12 操作与技巧基础——选择性粘贴 选择性粘贴的基本操作和意义 案例1:如何将公式转为数值 案例2:如何直接对某个区域进行加减乘除运算 案例3:如何只复制单元格中的有效性设置 案例4:关于转置功能的运用 案例5:关于跳过空单元格 Excel 实战技巧培训 13 操作与技巧基础——效率加倍的快捷键 Excel 实战技巧培训 14 目录 00 01 02 前言 操作与技巧 ——原来可以如此简单 03 04 05 06 函数与公式 数据透视表 图表 总结 ——Excel的精髓之处 ——百变星君 ——秀的是专 与作 业、拼的是创 意 1 07 2 3 4 5 业 6 基础中的进阶 基础中的基础 单元格格式 排序 保护工作表/工作簿 筛选 名称的运用 查找与替换 数据有效性 定位 条件格式 选择性粘贴 高级筛选 效率加倍的快捷键 Excel 实战技巧培训 15 操作与技巧进阶——单元格格式 单元格格式概述（Ctrl+1） 案例1:来浏览一些“千奇百怪”的自定义格式 案例2:如何对手机号码进行分段显示 案例3:如何对不同范围的数值使用不同格式 案例4:如何将自定义格式的显示转换成真实数据 Excel 实战技巧培训 16 操作与技巧进阶——工作表/工作簿的保护 保护工作表/保护工作簿 案例1:保护工作表基础操作 案例2:如何限定用户只能选择可编辑的区域 案例3:如何在已保护的工作表中运用筛选 Excel 实战技巧培训 17 操作与技巧进阶——数据有效性 数据有效性的概述 案例1:如何利用数据有效性控制单元格的输入 案例2:如何限制重复值的录入 案例3:用有效性制作二级下拉菜单 案例4:利用有效性圈释不符合要求的数据 Excel 实战技巧培训 18 操作与技巧进阶——条件格式 条件格式概述 案例1:利用条件格式标注达成率 案例2:用条件格式判断列是否有重复 案例3:用条件格式自动实现间隔底纹 案例4:用条件格式自动为小计总计设定格式 简单介绍2010的条件格式、迷你图 Excel 实战技巧培训 19 操作与技巧进阶——名称的运用 名称是什么东东？ 名称：实际上是对Excel“对象”的命名。而 所谓对象可以是： • 单元格 • 单元格区域 • 数值、数组常量 • 公式 • 宏表函数 Excel 实战技巧培训 20 操作与技巧进阶——名称的运用 为什么要使用名称 名称的优点： • 使公式、有效性、条件格式等等易于理解，比如 用“税率”代替单元格引用. • 简化公式 • 利于安全，易于维护 • 可以在条件格式、有效性中引用其他工作表单元 格或常量数组 • 调用Excel4.0宏表函数必须使用名称 • 可以在控件中调用单元格内容 • Excel的几乎任何地方那个都可以使用名称 Excel 实战技巧培训 21 操作与技巧进阶——名称的运用 关于名称的约定 名称不得与单元格地址相同。包括R1C1样式。 名称不得使用空格。 名称不得以数字开头或单独使用数字。 Excel识别名称时，不区分大小写。 名称是一种特殊形式的“公式”，因此其长度、 内部嵌套等同样受到限制，比如内部长度最大 16,384个字符。 • 名称所用字符不能超过255个字符。 • 当名称引用单元格区域时，要考虑相对引用和绝 对引用。 • • • • • Excel 实战技巧培训 22 操作与技巧进阶——名称的运用 定义名称的三种方法 方法1:插入名称 案例2:使用名称框 案例3:根据所选内容创建（批量） Excel 实战技巧培训 23 操作与技巧进阶——名称的运用 关于名称的案例 案例1:在名称中使用常量 案例2:在名称中使用函数 案例3:动态名称的运用 案例4:如何一次性删除所有定义的名称 Excel 实战技巧培训 24 操作技巧进阶——综合案例分析 一份市场活动赠品申请表的编制案例 单纯的表格 Excel 实战技巧培训 VS 名称+数据有效性+条件 格式+工作表保护的表格 25 操作与技巧进阶——高级筛选 高级筛选及举例 参考一份网络上的教程。请看案例 Excel 实战技巧培训 26 目录 00 01 02 前言 操作与技巧 ——原来可以如此简单 03 04 05 06 函数与公式 数据透视表 图表 总结 ——Excel的精髓之处 ——百变星君 ——秀的是专 与作 业、拼的是创 意 1 07 2 3 4 5 业 6 热身运动，现在开始 Excel运算符及其 含义 什么是公式、什么 是函数 如何快速、准确找 到适合的函数 Excel 实战技巧培训 27 函数与公式——热身运动 Excel运算符 让我们一起来看看Excel的常用运算符 Excel 实战技巧培训 28 函数与公式——热身运动 公式？函数？ 公式：是以等号“=”开头，通过使用运算符将数据、函数等元素 按一定顺序连接在一起，从而实现对工作表中的数据执行计算的等 式。例如 C1=A1+A2+A3+A4+B1+B2+B3+B4 函数：简而言之，函数就是预先定义好了的公式。比如上例中的公式 就可以用函数C1=SUM(A1:B4)代替。 函数不仅可以简化公式，而且具有仅用运算符连接的一般公式 所不能替代的功能。例如查找引用，逻辑判断等等。 Excel 实战技巧培训 29 函数与公式——函数的结钩分析 Excel 实战技巧培训 30 函数与公式——热身运动 快速选择函数 快速选择、输入函数的方法 通过“公式库”功能区快速选择输入 通过开头字母的提示快速输入 通过插入函数输入 直接输入 Excel 实战技巧培训 31 目录 00 01 02 前言 操作与技巧 ——原来可以如此简单 03 04 05 06 函数与公式 数据透视表 图表 总结 ——Excel的精髓之处 ——百变星君 ——秀的是专 与作 业、拼的是创 意 1 2 07 3 4 5 业 6 记住三个F吧，他们很棒 热身运动，现在开始 F1,不离不弃的好兄 Excel运算符及其含义 弟 什么是公式、什么是函 数 F4,相对？还是绝对？ 由你说了算 如何快速、准确找到适 F9,一把“庖丁解牛” 合的函数 的宝刀 Excel 实战技巧培训 32 函数起步——F1,不离不弃的好兄弟 F1，F1 哪里不会点哪里 自学函数的捷径全在 这里了 记住：excel本身就是 最好的参考书 Excel 实战技巧培训 33 函数起步——F4,相对？绝对？由你说了算 何为引用，有何影响？ 案例1:三种引用的同时理解 案例2:为什么vlookup会出现错误值 案例3:综合案例 Excel 实战技巧培训 34 函数起步——F9,一把“庖丁解牛”的宝刀 F9——应用举例 解析统计函数:IF+COUNTIF+SUMIF 的综合 应用 解析文本函数:FIND+MID的综合应用 解析复杂数组公式:其实就是这么简单 Excel 实战技巧培训 35 目录 00 01 02 前言 操作与技巧 ——原来可以如此简单 03 04 05 06 函数与公式 数据透视表 图表 总结 ——Excel的精髓之处 ——百变星君 ——秀的是专 与作 业、拼的是创 意 1 2 07 3 4 5 业 6 记住三个F吧，他们很棒 函数如此简单 热身运动，现在开始 TRUE vs FALSE F1,不离不弃的好兄 什么是函数 弟 逻辑判断是函数的灵魂 如何快速、准确找 F4,相对？还是绝对？ 数据汇总统计函数 到适合的函数 由你说了算 查找引用函数 兵马未动，逻辑先 F9,一把“庖丁解牛” 文本处理函数 行 的宝刀 璀璨明珠—Sumproduct Excel 实战技巧培训 36 函数如此简单——TRUE vs FALSE Excel的逻辑值只有2个：TRUE FALSE TRUE FALSE是函数的关键，可以这么说：任何 函数都是在解决TRUE 还是 FALSE的问题 TRUE FALSE 与运算符进行各种计算时，请牢 记：TRUE=1 FALSE=0，请务必牢记 通过示例文件，我们来详细解释一下TRUE 和 FALSE Excel 实战技巧培训 37 函数如此简单——逻辑判断是函数的灵魂 串联开关等同于逻辑函数 AND,即多个条 件同时满足： 比如——TRUE*TRUE Excel 实战技巧培训 并联开关等同于逻辑函数 OR,即只要满 足一个条件： 比如——TRUE+FALSE 38 函数如此简单——逻辑判断是函数的灵魂 通过案例，轻松掌握逻辑运算 案例1:是否能申请经济适用房 案例2:如何根据性别和职务计算补贴 案例3:多层判断——彻底理清逻辑关系 Excel 实战技巧培训 39 函数如此简单——数据汇总统计类函数 注：数据汇总统计类将全部采用案例形式进行讲解 汇总求和类函数，关键词:sum、sumif、sumifs、subtotal 案例1:汇总多个表格中相同单元格的数据（sum) 案例2:如何汇总某品名的销售数量 （sumif) 案例3:深入理解sumif函数的“相对位置” 案例4:如何汇总月薪在2000-4000元的工资总额（sumifs) 案例5:如何忽略隐藏的行汇总数据(subtotal) 案例6:如何始终生成连续的商品序号(subtotal) Excel 实战技巧培训 40 函数如此简单——数据汇总统计类函数 数据计数类函数，关键词:count,countif,countifs 案例1:如何统计成绩表中的及格人数(countif,countifs) 案例2:如何统计产品规格中带“*”号的产品个数(countif) 案例3:如何统计35岁以上有房有车的人员名单 案例4:如何统计不重复的数据个数 Excel 实战技巧培训 41 函数如此简单——数据汇总统计类函数 极值均值类函数，关键词:min,max,median,large,small,average 案例1:如何设置提成奖金的上下限（max,min,mdeian) 案例2:如何不计重复返回一组数据中次大，次小值(large,small) 案例3:如何统计前十名成绩的平均分(average,large) 案例4:如何根据条件计算平均分(averageif,averageifs) Excel 实战技巧培训 42 函数如此简单——查找引用类函数 注：查找引用类将采用函数解析+案例的方式 VLOOKUP函数 动态解析 (*办公室函数使用率之最*） MATCH函数 动态解析 案例1:根据不同的商品不同的订货量计算单价及折扣 案例2:输入工号后可自动显示相关的人事资料（测试题) 案例3:如何处理VLOOKUP函数找不到对应数据产生的错误 Excel 实战技巧培训 43 函数如此简单——查找引用类函数 INDXE函数 动态解析 OFFSET函数 动态解析 （初次接触比较难理解） 案例4:如何从右往左查询对应的数据（测试题） 案例5:如何根据品牌和规格双条件查找单价 案例6:如何保证新增加的销量自动求和(offset+counta动态引用) Excel 实战技巧培训 44 函数如此简单——文本函数 我们一起来学习几个最常用的文本处理函数： LEFT,MID,RIGHT “三个兄弟，本事各不同” FIND——在文本中找文本 SUBSTITUTE——替换函数 案例1:判断产品缺陷中是否含某个字符 案例2:不通过分列如何将数据分成2列 案例2:如何查找某字符在字符串中第N次出现的位置 Excel 实战技巧培训 45 SUMPRODUCT——函数中一颗璀璨的明珠 *千万不会要去迷恋Excel帮助文件中对此函数的描述。也许是因为SUMPRODUCT 函数能力太过超群，Excel帮助文件都嫉妒了它的才能。^_^ 将汇总数相乘？？ =SUMPRODUCT((数据1)*(数据2)) 是这样吗？ 将乘积的结果汇总! Good！！ SUMPRODUCT 数据1 数据1 运算符 运算符 数据2 数据2 结果 结果 数据2 11 ** 111 11 22 ** 222 44 33 ** 333 99 16 =SUMPRODUCT((数据1)*(数据2)*(数据3)*(数据4)……*(数据N)) 44 ** 444 16 55 ** 555 25 25 =SUMPRODUCT((数据1)+(数据2)+(数据3)+(数据4)……+(数据N)) 合计: 55 Excel 实战技巧培训 46 SUMPRODUCT——函数中一颗璀璨的明珠 在了解的基本原理后，我们还是通过案例来讲解SUMPRODUCTDE的常规用法： 案例1:起步,让一般统计类函数汗颜~~^_^ 案例2:奔跑,让多条件统计类函数折服~~^_^ Hi，不小心，我们已经打开了统计类“数组公式”的潘多拉盒子。 函数“美丽”（魅力）绽放，你发现了吗？ Excel 实战技巧培训 47 SUMPRODUCT——函数中一颗璀璨的明珠 很遗憾，没有任何事物是完美的，SUMPRODUCT也有自己的缺憾： 1）在用我们今天所讲的 =SUMPRODUCT((数据1)*(数据2))的方式时，计算 项，求和时出现文本、错误值、逻辑值，将会出错。 2）用SUMPRODUCT，计算很大的表、很复杂的逻辑时，运行速度会比较慢。 因为该函数内部执行的是数组运算，会相对耗时。（当然这个耗时只是相对 而言，比如像对 SUMIFS，DSUM 等等） Excel 实战技巧培训 48 我和函数的约定——日积月累、学以致用 函数是Excel中最核心的部分，是技巧、图表、透视表，VBA的基本功。 同时函数本身内容也相当丰富，值得大家不断的去学习、思考如何让 工作更轻松。 同时： 由于函数内容太多， 不需要大家把每个函数都去掌握 由于函数很有深度，很多函数都值得反复的去学习、去发现 由于许多函数在理解上比较困难，不可能短期内达到某个高度 因此，建议大家“日积月累、学以致用” Excel 实战技巧培训 Lookup Text 查找之王 文本之王 49 目录 00 01 02 前言 操作与技巧 ——原来可以如此简单 03 04 05 06 函数与公式 数据透视表 图表 总结 ——Excel的精髓之处 ——百变星君 ——秀的是专 与作 业、拼的是创 意 1 2 07 4 3 业 5 6 认识数据透视表 创建第一个数据透视表 无拘无束的数据透视表 如虎添翼的计算字段、 计算项 动态数据透视表 介绍多重合并和外部引 用创建透视表＊ 介绍数据透视图＊ Excel 实战技巧培训 50 数据透视表——认识数据透视表 Excel数据 清单 数据透视表 数据透视表 数据透视表 数据库 Excel数据 透视表引擎 文本文件 数据透视图 数据透视图 数据透视图 其他关系型数 据文件 Excel 实战技巧培训 51 数据透视表——了解数据透视表的结构 Excel 实战技巧培训 52 数据透视表——认识数据透视表结构 报表筛选区域 列区域 行区域 数值区域 Excel 实战技巧培训 53 数据透视表——创建一个透视表 • 选择区域中任意单元格 • 插入透视表 • 选择列、行、值 Excel 实战技巧培训 54 数据透视表——无拘无束的数据透视表(1) 筛选字段、列、行字段任意拖拽的无拘无束 字段汇总方式的无拘无束 字段显示方式的无拘无束 修改数据透视表的字段名称 灵活处理错误值和空白单元格 Excel 实战技巧培训 55 数据透视表——无拘无束的数据透视表(2) 谁先谁后由决定，展示字段先后顺序任意指定 数据透视表的排序 数据透视表的筛选 数据透视表分组，组合方式由我定 更改数据源和移动数据透视表 Excel 实战技巧培训 56 数据透视表——如虎添翼的计算字段和计算项 首先理解计算字段、计算项 案例１：用计算项计算销售提成 案例２：用计算项计算年销售差异 Excel 实战技巧培训 57 数据透视表——动态创建数据透视表 方法１：定义名称法创建动态的数据透视表 方法２：使用表功能创建动态的数据透视表 方法３：导入外部数据创建动态数据透视表 Excel 实战技巧培训 58 数据透视表——多重合并、外部引用、透视图 引用外部数据源 的介绍 多重合并的介绍 简单介绍数据透 数据源５ 视图 数据透视表的内容，比我们想象的还要多， 今天我们仅仅见识了他的“冰山一角” Excel 实战技巧培训 59 目录 00 01 02 前言 操作与技巧 ——原来可以如此简单 03 04 05 06 函数与公式 数据透视表 图表 总结 ——Excel的精髓之处 ——百变星君 ——秀的是专 与作 业、拼的是创 意 1 2 07 3 4 业 5 6 图表秀 图表的制作过程 打破常规方可彰显 专业气质 动态图表的基本制 作思路 获取专业图表参考 的方法 Excel 实战技巧培训 60 图表——图表秀 Excel 实战技巧培训 61 图表——图表秀 Excel 实战技巧培训 62 图表——图表的制作过程 •原始数据 •原始文件 提炼信息 •理解数据 •分析数据 •提炼信息 •明确主题 Excel 实战技巧培训 选择图表 制作图表 •确定比较关系 •准备做图数据 •确定图表类型 •制作美化图表 •确定图表处理方 式 •检查确认图表 数据图表 63 图表——图表类型的选择 Excel 实战技巧培训 64 图表——打破常规方可彰显专业气质 液晶显示器市场占有率 年份 索尼 2006年 2007年 2008年 2009年截止3季度 17.1% 17.1% 17.8% 14.4% 三星 15.3% 18.7% 23.1% 22.7% 《商业周刊》 Excel常规 VS Excel 实战技巧培训 65 图表——打破常规 打破颜色的限制 ▲打破图表的束缚▲ 主标题区 简单唯美，惜墨如金 副标题区 图例区（可省略） 图表区 脚注区 Excel 实战技巧培训 66 图表——动态图表的基本制作思路 方法1 方法2 方法3 辅助区域法 定义名称 图片引用法 Excel 实战技巧培训 67 图表——获取专业图表参考的方法 Excel 实战技巧培训 68 目录 00 01 02 前言 操作与技巧 ——原来可以如此简单 03 04 05 06 函数与公式 数据透视表 图表 总结 ——Excel的精髓之处 ——百变星君 ——秀的是专 与作 业、拼的是创 意 1 Excel 实战技巧培训 2 07 3 4 5 业 6 69 不是 结束 是开始 谢谢各位！ QQ:6972477