企业培训_excel使用技巧培训

EXCEL使用技巧培训 培训目标 课程内容介绍 学习方法技巧 1 1 EXCEL历史 成长 诞生 从6 0开始 Excel放弃以数字作为版本号的方式 开始使用年份定义版本 MicrosoftExcel957 0 1987年第一款适用于Windows系统的Excel2 0 1985年第一款Excel诞生 它只用于Mac系统 Excel20072006年11月发布 在新的面向结果的用户界面中 1 1 EXCEL历史 它的图形用户界面将各种菜单 工具栏展现在屏幕中 允许同时使用鼠标和键盘发布命令 使不具备专业知识的用户也可以轻松自如的操作 它的版面专为表格设计 因而很容易输入原始数据 形成整齐美观的表格 它的运算功能极强 不仅可以进行四则运算 逻辑运算 还可以进行大量的函数运算 所能解决的计算问题已经远远超出了日常工作的范围 它能根据原始数据和计算结果生成100多种图表 并且图表格式可以任意设置修改 它的自动计算 自动更正 自动筛选等功能表现出超常的智能化水平 它可以将数据清单自动视为数据库 对其排序 筛选 汇总 甚至建立数据透视表 它还可以进行模拟运算 变量求解 并通过局域网和Internet与其他用户共享中文版Excel的文档 1 2 功能特点 VBA编程 透视表 函数 图表 基本操作 VBA编程 透视表 函数 图表 基本操作 1 3 EXCEL学习阶梯 1 4 EXCEL2007工作界面简介 1 5 EXCEL2007特点简介 2 1 EXCEL工作簿修复 第一步 在EXCEL中打开工作表 并选择需要设置的区域或整行或整列 再单击 数据 功能区中的 数据有效性 按钮 第二步 在弹出的 数据有效性 对话框中的 设置 选项卡下 选择 允许 下面的 序列 2 2 数据有效性 1 第三步 根据需要设置是否 忽略空值 在下面的 来源 文本框中输入待选的列表项 各数值间以半角逗号分隔 不需要加引号 再单击 确定 按钮即可 第四步 返回工作表 刚才选择的区域中已经有下拉箭头了 点击这个箭头或按ALT键可弹出下拉列表 如果后面的数据和目前单元格的数据一样 则我们可以将光标指向单元格选择框 黑色框 右下角的那一点上 变成十字光标时双击之可快速填充 2 2 数据有效性 2 2 3 实用的F4功能 1 重复操作 如果如需要插入一个新工作表 可以选择当前工作表 右键工作表标签 选择插入 新工作表 如果要插入更多的 可以按F4 插入第二个 再按插入第三个 另外 大部分对表格的操作都可以重复 常用于设置格式 插入 删除行列等 2 转换公式中的单元格及区域的引用方式 比如 对于某单元格所输入的公式为 SUM B4 B8 选中整个公式 按下F4键 该公式内容变为 SUM B 4 B 8 表示对横 纵行单元格均进行绝对引用 第二次按下F4键 公式内容又变为 SUM B 4 B 8 表示对横行进行绝对引用 纵行相对引用 第三次按下F4键 公式则变为 SUM B4 B8 表示对横行进行相对引用 对纵行进行绝对引用 第四次按下F4键时 公式变回到初始状态 SUM B4 B8 即对横行纵行的单元格均进行相对引用 如果继续按F4键 将再次进行循环 需要说明的一点是 F4键的切换功能只对所选中的公式段有作用 注意 需要选择整个公式 然后按F4键才有作用 比如选择整个公式 COUNTIF E3 E160 E158 选择单元格是不行的 然后按下F4键 公式就变成 COUNTIF E 3 E 160 E158 了 2 4 让不同类型数据用不同颜色显示 在工资表中 如果想让大于等于2000元的工资总额以 红色 显示 大于等于1500元的工资总额以 蓝色 显示 低于1000元的工资总额以 棕色 显示 其它以 黑色 显示 我们可以这样设置 第二步 按 添加 按钮 并仿照上面的操作设置好其它条件 大于等于1500 字体设置为 蓝色 小于1000 字体设置为 棕色 设置完成后 点击 确定 第一步 打开 工资表 工作簿 选中 工资总额 所在列 执行 格式 条件格式 命令 打开 条件格式 对话框 单击第二个方框右侧的下拉按钮 选中 大于或等于 选项 在后面的方框中输入数值 2000 单击 格式 按钮 打开 单元格格式 对话框 将 字体 的 颜色 设置为 红色 2 5 连接符 连字符 起着连接的作用我身边曾经有这样的朋友 就是在表格中一行有多个单元格 需要将他们连接在一起 她尽然非常有耐心的在每个格子中双击 然后在最终的单元格不断的粘贴 方法和步骤 1 在新的一个单元格输入 2 点击第一个单元然后按键盘上的 就是shift 7 再继续第二个单元格 如此重复所有的单元格连接起来 第一种 关闭 启动实时预览 以excel2007为例说明 首先打开excel2007 找到界面左上角的excel选项 如图 选择 常用 在 启用实时预览 前取消 2 6 工作表 减肥 1 第二种 设置COM加载项找到界面左上角的excel选项 如图 出现选项的界面后 取消全部的勾选框 确定即可 2 6 工作表 减肥 2 3 1学习EXCEL函数前要记住的事 功能 语法 Range 需要计算其中满足条件的单元格数目的单元格区域 Criteria 确定哪些单元格将被计算在内的条件 其形式可以为数字 表达式或文本 例如 条件可以表示为32 32 32 或 apples COUNTIF range criteria 注意事项 range非空白单元格区域criteria以文本形式表达的表达式 如 90 往往不能用于只为一个简单的条件表达式 不能用于复杂的统计 80 90 计算区域中满足给定条件的单元格的个数 3 2条件统计函数COUNTIF CountIf Range Criteria 例子 1 CountIf A1 A100 D2 2 CountIf A1 A100 2 3 2条件统计函数COUNTIF 解释 1 在A1 A100之中 统计等于D2单元格里数值的个数2 在A1 A100之中 统计大于2的个数 统计区域 条件 函数解释 根据条件H2在统计区域E2 E11中 进行逐一比对 逻辑运算 最终汇总符合条件的个数 条件 A A A A A A A A A A 统计区域 E B E C E A B A D A 运算方式按条件 0 0 0 0 0 1 0 1 0 1 3 2条件统计函数COUNTIF 最后对结果进行求和 如下SUM 0 0 0 0 0 1 0 1 0 1 3 Range 用于条件判断的单元格区域 Criteria 确定哪些单元格将被相加求和的条件 其形式可以为数字 表达式或文本 例如 条件可以表示为32 32 32 或 apples Sum range 需要求和的实际单元格 功能 指定区域中对满足条件的相对应的单元格求和 语法 SUMIF range criteria sum range 3 3条件求和函数SUMIF 只有在区域中相应的单元格符合条件的情下 sum range中的单元格才求和 如果忽略了sum range 则对区域中符合条件的单元格求和 可用数组运算 作为数组运算 只能用列运算 不能用行运算 注意事项 SumIf Range Criteria Sum Range 例子 1 SUMIF A1 A100 D2 B1 B100 2 SUMIF A1 A100 A B1 B100 3 SUMIF A1 A100 3 B1 B100 解释 1 汇总等于D2单元格的所有值2 汇总等于 A 的所有值3 汇总大于3的所有值 3 3条件求和函数SUMIF H7 H19 E E D E E A C B C A D A B M7 A 在函数中 自动转换成数组形式 即 A A A A A A A A A A A A A E E D E E A C B C A D A B A A A A A A A A A A A A A 0 0 0 0 0 1 0 0 0 1 0 1 0 1 按次序逐对进行比较 2 相符 TRUE 为1 不相符 FALSE 为0 第 步得出的结果 再进行下一步运算 需要求A组的段取时间累计 怎么做呢 3 3条件求和函数SUMIF 结果 0 0 0 0 0 0 50 0 0 0 0 50 0 0 33 0 最后求和得 1 33 1 1 10 1 0 3 3条件求和函数SUMIF VLOOKUP lookup value table array col index num range lookup Lookup value 条件值 是值 非区域 比如A1 而非A1 A100 即使A1 A100 也只选择左上角值A1Table array 搜索的区域 范围 是一个多维数组 如A1 D100之类Col index num 需要返回的值所在列 从搜索范围的起始列开始算Range lookup 匹配方式 一般使用精确匹配 即0或FALSE 功能 语法 Vlookup函数能够从其它工作表 或本工作表 将某数据列中的数据查找出需要的数据 3 4查找函数VLOOKUP 注意事项 如果在查找区域table array含有多个与lookup value相等的值 即含有重复项 只能返回首个lookup value值所对应的结果如果table array中没有与lookup value相等的值 函数将返回错误值 N A VLOOKUP lookup value table array col index num range lookup 例子 在Sheet2返回Sheet1的数据 公式可以表示为VLOOKUP A1 Sheet1 A1 D100 3 0 解释 在Sheet1表A1 D100区域的第一列查找Sheet2表单元格A1的值 查到后 返回这一行第3列的值 3 4查找函数VLOOKUP 二维数组 逐一按顺序进行比较 0 0 0 0 1 0 结果 3 4查找函数VLOOKUP 二维数组 第 步结果 0 0 0 0 1 0 与第2列比较 B1 B2 B3 B4 B5 B6 B7 返回1对应的数据 B6 Z 0 3 4查找函数VLOOKUP 4 2 数据透视表制作的注意事项 4 1透视表所需的规范数据源 所谓规范的数据源 1 不能有合并单元格 2 表头必须是一行 且中间不能有空白字段 3 数据的输入要复核一定的规范 4 1数据透视表的功能 对数值数据进行分类汇总和聚合 展开或折叠要关注结果的数据级别 查看感兴趣区域摘要数据的明细 将行移动到列或将列移动到行 或 透视 以查看源数据的不同汇总 对最有用和最关注的数据子集进行筛选 排序 分组和有条件地设置格式 突出显示重要信息 提供简明 带有批注的联机报表或打印报表 数据区域的顶端行为字段名称 标题 避免在数据清单中存在有空行和空列 这里需指明以下 所谓空行 是指在某行的各列中没有任何数据 如果某行的某些列没有数据 但其他列有数据 那么该行就不是空行 同样 空列也是如此 各列只包含一种类型数据 避免在数据清单中出现合并单元格 避免在单元格的开始和末尾输入空格 尽量避免在一张工作表中建立多个数据清单 每张工作表最好仅使用一个数据清单 工作表的数据清单应与其他数据之间至少留出一个空列和一个空行 以便于检测和选定数据清单 4 2数据透视表制作的注意事项 4 3制作透视表的实例演示 假设某电视销售商场有6位职工 分别是劳得诺 令狐冲 陆大安 任我行 韦小宝 向问天 销售的电视产品有TCL 长虹 康佳 创维 熊猫几种品牌 每天的销售数据都记录在Excel的一个工作表中 如图所示 该数据表有300多行数据 完成以下统计分析工作 统计各种品牌的电视的总销售额 统计每位职工的总销售额 统计每位职工销售的各种类型的电视机的销售额 统计各种运输方式的总数 1 建立数据透视表的过程 1 建立源数据表后 单击 插入 选项卡中的 数据透视表 弹出 创建数据透视表 对话框 2 创建数据透视表 对话框 3 指定数据透视表的数据区域后 单击 确定 按钮 将弹出数据透视表的布局设置对话框 4 3制作透视表的实例演示 5 根据第4步设置创建的数据透视表 4 用鼠标将数据字段拖放到行 列标签和数值汇总对应的文本框中 4 3制作透视表的实例演示 其中数据透视表布局中的内容 4 3制作透视表的实例演示 行标签 拖放到行中的数据字段 该字段中的第一个数据项将占据透视表的一行 列标签 与行相对应 放置在列中的字段 该字段中的每个项将占一列 行和列相当于X轴和Y轴 由它们确定一个二维表格 页则相当于Z轴 拖放在页中的字段 Excel将按该字段的数据项对透视表进行分页 报表筛选 数值 进行计数或汇总的字段名称 2 统计各销售员销售数据的透视表布局 4 3制作透视表的实例演示 3 统计各销售员销售数据的透视表 双击任一单元格 会得到这些数据的来源明细数据 4 3制作透视表的实例演示 双击上表E5单元格得到的明细数据表 4 3制作透视表的实例演示 4 利用报表筛选 即页字段 创建分页显示 页字段 用鼠标单击它 从下拉列表中选择某个日期 可以查看此日期的销售数据 4 3制作透视表的实例演示 5 使用多重数据字段建立透视表在前面所讨论的电视销售数据表中 要查看每位职工销售了多少种产品 每种产品的数量各是多少 而且还要查看其销售的产品中 每种运输方式各运输了多少数量 在行标签中放置了 销售员姓名 和 运输方式 两字段 4 3制作透视表的实例演示 6 修改数据透视表的汇总函数数据透视