企业培训_excel常用技能炫酷培训课件

1 ExcelArt 图表改变工作生活陕西黄马甲物流配送股份有限公司经营中心 杨莉2011 11 XX 2 目录 EXCEL基本功能演示EXCEL图形图标演示公式的应用键盘的使用技巧表间的链接应用数据透视表 图的制作 3 EXCEL基本功能演示 图形图标演示 4 EXCEL公式的应用 常用公式演示 5 COUNT 统计函数 可以计算数组或单元格区域中数字项的个数 语法COUNT value1 value2 6 函数COUNT在计数时 将把数字 空值 逻辑值 日期或以文字代表的数计算进去 但是错误值或其他无法转化成数字的文字则被忽略 所以 COUNT的单纯应用很少 一般都会选择COUNTIF公式 并和IF套用 COUNT例子 Count data 7 COUNTIF 数字和三角函数 计算给定区域内满足特定条件的单元格的数目 语法COUNTIF range criteria Range为需要计算其中满足条件的单元格数目的单元格区域 Criteria为确定哪些单元格将被计算在内的条件 其形式可以为数字 表达式或文本 8 上述公式 我们可以看出 COUNTIF是作为IF语句的判断条件出现的 它表示计算C2到F2区域内值为 1 的单元格个数 COUNTIF例子 IF COUNTIF C2 F2 1 0 0 ROUND B2 COUNTIF C2 F2 1 2 公式 9 IF If是逻辑函数 执行真假值判断 指定要执行的逻辑检测 根据逻辑测试的真假值返回不同的结果 语法IF logical test value if true value if false 函数IF可以嵌套七层如果要计算单元格区域中某个文本串或数字出现的次数 则可使用COUNTIF工作表函数 10 IF B2 C2 超过预算 OK 判断当B2单元格的值大于C2单元格时 提示超出预算 IF B2 60 及格 IF 70 90 优 不及格 三层判断 第二个IF语句是第一个IF语句的 假 值 实例如下 IF例子 一般及嵌套的应用 11 如上两公式 公式一选择了嵌套SUM公式 公式二选择了嵌套COUNTIF及ROUND两个公式 公式一是通过判断王一在3天里每天是否有消费 消费的总额是多少 而公式二是通过判断在某一天是否有人消费 如果有 算出人均消费额度 并取小数点后两位保留 其中 值 1 表示该人是否消费 表格从A1单元格开始排列 IF例子 以其他公式为参数 IF C2 B2 SUM C2 F2 0 IF C3 B3 SUM C3 F3 0 IF C4 B4 SUM C4 F4 0 IF COUNTIF C2 F2 1 0 0 ROUND B2 COUNTIF C2 F2 1 2 公式一 公式二 12 HLOOKUP 在表格或数值数组的首行查找指定的数值 并由此返回表格或数组当前列中指定行处的数值 语法结构 HLOOKUP lookup value table array row index num range lookup lookup value 需要在数据表第一行中进行查找的数值 table array 为需要在其中查找数据的数据表 row index num 为table array中待返回的匹配值的行序号 range lookup 为一逻辑值 指明函数HLOOKUP查找时是精确匹配 还是近似匹配 如果为TRUE或省略 则返回近似匹配值 13 上述公式是指针对B2单元格的数值 21566 在E1 J3区域范围内搜索出近似的值 因为range lookup参数被忽略 找到后显示E1 J3区域表中第三行的对应值 HLOOKUP例子 HLOOKUP B2 E1 J3 3 14 VLOOKUP 在表格或数值数组的首行查找指定的数值 并由此返回表格或数组当前列中指定行处的数值 语法结构 VLOOKUP lookup value table array col index num range lookup lookup value 需要在数据表第一列中进行查找的数值 table array 为需要在其中查找数据的数据表 col index num 为table array中待返回的匹配值的列序号 range lookup 为一逻辑值 指明函数VLOOKUP查找时是精确匹配 还是近似匹配 如果为TRUE或省略 则返回近似匹配值 15 上述例子是一个收入税率表 可以看到不同的收入等级的收入税率 公式是指针对B2单元格的数值 21566 在D2 F7区域范围内搜索出近似的值 因为range lookup参数被忽略 找到后显示D2 F7区域表中第三列的对应值 VLOOKUP例子 VLOOKUP B2 D2 F7 3 16 HLOOKUP及VLOOKUP 当比较值位于数据表的首行 并且要查找下面给定行中的数据时 请使用函数HLOOKUP 当比较值位于要进行数据查找的左边一列时 请使用函数VLOOKUP 如果 row col index num小于1 函数 H VLOOKUP返回错误值 VALUE 如果 row col index num大于table array的列数 函数 H VLOOKUP返回错误值 REF 17 注意事项及文件间的操作 以下案例均直接由EXCEL中复制粘贴 可在PPT中双击调用EXCEL格式编辑 相关公式可由编辑栏直接查看 由于WORD EXCEL及PPT是OFFICE组件 它们之间的调用很方便 两种方式 同上所说 直接复制粘贴就可以了 还能保持用原格式再次嵌套编辑 选择 插入 对象 在对话框中选择 由文件创建 不选中 显示为图标 选项即可 方法可适用于上述工具任一种 18 公式使用小技巧 当同一列 行 所有单元格使用的公式相同 可以使用以下两种快捷方式 使用拖动柄 在完成首行 列 单元格公式后选中右下角的拖动柄 填充所要应用公式的所有单元格 使用EXCEL自带的记忆功能 在第一个单元格完成公式后 填充第二个单元格 再以第二个单元格填充第三个 以第三个填充第四个 到第五个开始就无须做任何操作 公式对应的值一出现 第五个单元格自动填充 这种方式适用于复杂公式及大数据量 以免上种方式由于操作失误造成漏项 19 EXCEL键盘使用技巧 快捷键 20 快捷键 箭头键 CTRL 选择框移动到当前数据区域的边缘 F11或ALT F1 使用当前区域的数据创建图表 CTRL END 移动到工作表的最后一个单元格 这个单元格位于数据区的最右列和最底行的交叉处 右下角 或是与首单元格 一般为A1 相对的单元格 ALT ENTER 在单元格中折行 CTRL ENTER 用当前输入项填充选定的单元格区域 适用于在选定区域或单元格内需要输入同一值时使用 SHIFT ENTER 完成单元格输入并在选定区域中上移 SHIFT TAB 完成单元格输入并在选定区域中左移 CTRL SHIFT A 在公式中键入函数名之后 无须打开函数面板 自动为该函数插入变量名和括号 F4 在编写公式中使用F4可以为所选单元格添加固定符号 点击单元格右下角的十字可以将当前单元格的内容延续下去 21 EXCEL表间的链接应用 数据源链接等 22 方法 要使多张表同时关联 首先要确定源数据的位置 源数据位置一定 在其他任何需要和该数据值相关的位置上全部使用调用公式 即用 来直接调用该源数据或加上函数及参数 使该位置的数据可以针对源数据进行相关处理 23 费用总表中 本周已消费 列的值是从费用明细表中 本周消费小计 行中读取的 例如 王一的 本周已消费 公式为 费用明细表 xls 费用明细 C 5可以把上两表拷贝到不同EXCEL表中 注意都从A1单元格开始 当打开相关联的任何一张表时 可以同时关联很多张表 会自动弹出下列对话框 选择是 表间的链接就建立了 例子 费用明细表 费用总表 A1 24 EXCEL数据透视表的制作 透视 25 透视表的特点 一个数据透视表实际上是一个产生于数据库的动态总结报告 数据透视表的最新概念是它的交互特性 最大的缺点是在修改原数据时 数据透视表不会自动更新 但可以单击数据透视表工具栏上的 刷新 按钮迫使透视表使用最新数据 26 注意事项 不是所有的数据都可以创建数据透视表 所要总结的数据必须以数据库的形式存在 当然 我们可以把数据库存储在工作表中 即常说的列表 一个单独的数据库表格一般应包含数据字段和类别字段 数据字段用于总结 类别字段的数值可以作为行 列或页出现在数据透视表上 数据透视表的特性对不包含实际值字段的数据库也有效 可通过方法而不是求和来总结数据透视表上的信息 27 制作步骤 第一步 定义数据位置第二步 定义数据第三步 实现数据透视表使用对话框规划数据透视表使用透视表字段列表工具栏规划数据透视表完成数据透视表的定义 28 用于创建透视表的数据列表 29 选择 数据 数据透视表和数据透视图 第一步 定义数据位置 30 Excel列表或数据库 存储在工作表中的数据库受不超过65535个记录和256个字段的限制 第一行必包含字段名称 数据可以是数值 文本或公式 外部数据源 使用Query获得数据 可以使用dBASE文件 SQLServer数据或其他系统能够建立访问的数据 多重合并计算数据区域 可以从多表格创建数据透视表 等价于合并表格中的信息 另一数据透视表或图 从已经存在的透视表使用过的数据创建新透视表 注 从内存使用方面考虑 如希望从同一组数据中创建多个透视表 最好先建立第一个透视表 然后把该透视表当作后续透视表的数据源 向导之步骤一 31 第二步 定义数据 要转移到向导下一步 单击 下一步 按钮 向导二提示我们需要数据 但注意 对话框依赖于你在向导一上的选择有所变化 从现在开始 我们分别通过三个完整的创建案例来具体了解选择不同类型的效果及特点 32 在步骤一选择该类型 步骤二为下面的对话框 注 上图中选定区域已存在 原因是在选择创建数据透视表时 单元格指针指在了数据列表内的任一位置 Excel自动识别了原数据范围 也可以使用 浏览 按钮打开不同工作表并且选择范围 例1 使用Excel列表或数据库 33 第三步 实现数据透视表 选择 现有工作表 选项 可以在下面的选择范围内定义起始单元格位置 选择 新建工作表 选项 自动插入一个新的工作表 注 从步骤2到步骤3时 如果该工作薄已存在数据透视表 会自动弹出下面的提示框 选择 是 自动弹出透视表列表 选择后确定才会出现步骤3的对话框 34 规划数据透视表 选择 版式 出现版式对话框 35 关于规划 版式 对话框中右下角的选项是表单中的标题栏名称 可以用鼠标点击选中拖动到左边的图示相应位置 表单中各标题下的数据就会自动统计到该图示对应的单元格中 如果想改动选项位置 用鼠标在图示中选中拖到图示框外即可取消该项设置 然后把需要的选项再次拖入就可以了 如果不想在该处调整版式 可以在确定数据透视表建立位置后直接点 完成 出现下页中的工具栏 36 调整数据透视表设置 同版式里方法一样 用鼠标选中工具栏上的各名称 拖动到相应位置就可以了 如果生成表后还想调整 屏幕上又没有显示 数据透视表 的工具栏 可以单击 视图 工具栏 数据透视表 即可 37 根据需要确定选项后 点击完成 就可以生成下面的数据透视表了 例 使用上页信息的透视表 简单数据透视表 38 在步骤一选择该类型 步骤二为下面的对话框 例2 使用另一数据透视表 注 上图对话框中出现了两个数据表项是EXCEL自动记录了当前工作薄中已经存在的所有数据透视表名称 并提供选择 39 第三步 实现数据透视表 这里我们选择数据透视表1做为参照对象 选择 下一步 选择 现有工作表 选项 可以在下面的选择范围内定义起始单元格位置 选择 新建工作表 选项 自动插入一个新的工作表 40 根据需要确定选项后 点击完成 就可以生成下面的数据透视表了 例 使用上页信息的透视表 使用页字段的数据透视表 这里我们添加了页字段 使用这种方式对于想对同一表单建立多张数据透视表很有好处 可以节约内存资源 提高数据透视表分析的效率 41 在步骤一选择该类型 步骤二为下面的对话框 例3 多重合并计算数据区域 缺省选择 下一步出现右图对话框 每选定一个区域后选择添加 在所以区域里可以看见选择了的区域位置及区域范围 如果选错了 可以选择后删除 42 第三步 实现数据透视表 这步选择同其他两个例子一样 我们都选择新建工作表 然后完成 这里注意 多重合并最好不要在版式里调整选项 到数据透视表的工具栏中再做调整 43 数据透视表工具栏设置 44 上图中出现的不再是单表单时的标题栏名称 而是行 列 值 页 行和列指的是两张表单中的所有标题行名称及第一列的所