excel应用技能培训课件

EXCEL应用技能 X EXCEL2010培训 课程目的 一 了解excel基本功能操作和常用格式 二 掌握excel操作技巧和基本公式插入运用 三 灵活运用excel工具实现多种表单处理 课程目录 一 EXCEL基础功能介绍 三 EXCEL常用公式 四 EXCEL基础技巧 二 EXCEL常用快捷键 五 EXCEL常用格式 课程目录 一 EXCEL基础功能介绍 三 EXCEL常用公式 四 EXCEL基础技巧 二 EXCEL常用快捷键 五 EXCEL常用格式 1 实现分层分级显示 组合 一 EXCEL基础功能介绍 点击 数据 创建组 即可将所选中区域组合 2 隐藏 3 插入 快捷键 Ctrl Shift 同时选定N行 或N列 右键点击 插入 即可在所选定的最上一行 或最左一列 的前边插入N行 或N列 在选定的行或列上点击右键选择 隐藏 即可隐藏该行或该列 同时选定被隐藏的行或列的前后相邻两行 或前后相邻两列 右键点击 取消隐藏 即可显示已经隐藏的行或列 若按CTRL同时选定不相邻的行或列再点击 插入 则可以在每个选定的行或列前方插入一个行或列 4 查找 替换 快捷键 Ctrl F 点击 开始 查找 或 替换 即可实现对工作薄中 或选定范围内 某些特定内容的查找或替换功能 可以同时选定多个工作簿一起进行 5 筛选 快捷键 Alt D F F 6 冻结 当你希望表头部分一直出现在视野范围内 不随右方或者下方的滑动键拖动而消失时 可对这部分内容使用冻结功能 选定所要筛选的区域 点击 数据 筛选 即可 筛选不能横向进行 操作 选择需冻结的区域下一行 或下一列 点击 视图 冻结窗格 冻结拆分窗格 即可 当选定单元格 点击 冻结拆分窗格 时 将对此单元格上方和左方的区域同时冻结 7 排序 点击 选项 可以选择更多排序方法 点击 选项 可以选择更多排序方法 点击 数据 排序 选择数据包含标题 按标题内容进行排序 取消选择数据包含标题 按列进行排序 注意 排序功能只能将选定的区域进行排序 如选择不完全则容易出现错误 8 工作表保护 保护工作表 点击 审阅 保护工作表 在弹出的窗口中输入保护密码即可完成对工作表的保护 允许用户编辑区域 点击 设置单元格格式 保护 取消 锁定 勾选 允许用户编辑区域 保护工作表 保护工作簿 点击 审阅 保护工作薄 保护工作薄 允许用户编辑区域 保护工作表 一 EXCEL基础功能介绍 三 EXCEL常用公式 四 EXCEL基础技巧 二 EXCEL常用快捷键 五 EXCEL常用格式 课程目录 EXCEL常用快捷键 Alt Enter 作用 在同一个单元格内换行 切忌使用空格换行 Ctrl a s f z x c v 全选 保存 查找撤回 剪切 复制 粘贴 F2 编辑当前单元格 Shift F2 插入 修改批注按一下Esc调整批注位置按两下退出编辑 Ctrl d 向下复制 F4 重复前一步操作 课程目录 一 EXCEL基础功能介绍 三 EXCEL常用公式 四 EXCEL基础技巧 二 EXCEL常用快捷键 五 EXCEL常用格式 数学函数逻辑函数查找与引用数据透视表其他函数 数学函数逻辑函数查找与引用数据透视表其他函数 1 SUM number1 number2 求和函数 数字区域中的单元格可以相邻或不相邻 以下图为例 公式 SUM B3 D8 E8单元格将对B3 D8区域内数字求和 与求和函数操作类似的还有 平均值 AVERAGE 记数 COUNT 最大值 MAX 最小值 MIN 2 条件求和 SUMIF range criteria 例如 SUMIF D2 D99 成都 H2 H99 表示对城市为 成都 的销量进行加总 SUMIF H2 H99 10 H2 H99 表示对销量大等于10的部分进行加总 注意 但是SUMIF条件求和局限于单个条件 若要进行多个条件下的求和可以用SUMPRODUCT实现 例1 SUMPRODUCT D2 D99 成都 G2 G99 H2 H99 G2 G99 表示对城市为 成都 并且销量大等于目标销量的部分进行加总 SUMPRODUCT 1 将把每个区域中对应单元格的数值相乘 再求和 公式写法为 SUMPRODUCT 区域1 区域2 区域3 例如 SUMPRODUCT A1 A3 C1 C3 等同于 A1 C1 A2 C2 A3 C3 2 此外还可以进行多个条件下的求和 例2 SUMPRODUCT D2 D99 成都 G2 G99 10 G2 G99 表示对城市为 成都 并且销量大等于10的部分进行加总 数学函数逻辑函数查找与引用数据透视表其他函数 IF 逻辑判断 满足条件时的值 不满足条件时的值 可以嵌套使用用法 1 IF 如果满足某一条件 则返回某一值 否则返回另一值 IF嵌套 当需要的条件较多时 IF函数中可以建立子IF函数 如上图所示 成为嵌套 IF函数最多可嵌套7层 根据销售数据进行判断 对于销售 100判断为 100 50判断为 50 10判断为 10 不满足以上两个条件的判断为 10 AND 所有参数的逻辑值为真时 返回TRUE 只要一个参数的逻辑值为假 即返回FALSEOR 任何一个参数逻辑值为TRUE 即返回TRUE 任何一个参数的逻辑值为FALSE 即返回FALSENOT 如果逻辑值为FALSE 返回TRUE 如果逻辑值为TRUE 返回FALSE 2 AND OR NOT 可以检验数值的类型并根据参数取值返回TRUE或FALSE 3 IS函数 数学函数逻辑函数查找与引用数据透视表其他函数 VLOOKUP 将在查找引用区域的第1列中搜索查找值 再查找引用区域指定列中对应单元格的值 操作方法 公式写法为 VLOOKUP 查找值 查找引用区域 指定列号 匹配方式 lookup value table array col index range lookup 1 VLOOKUP 查找型函数 29 例如 在需要在 华南区大学生通讯录 中找到参加促销PK赛人员的联系方式 则可以用VLOOKUP 具体操作如下 表1 华南区大学生通讯录 表2 华南区参加促销PK赛人员信息表 第一步 选择VLOOKUP的公式 点击确定 第二步 选中填充位置G4 按照公式 VLOOKUP 查找值 查找引用区域 指定列号 匹配方式 确定 查找值 点击 确定 第三步 选定 查找引用 第四步 选定 指定列号 第五步 成果呈现 数学函数逻辑函数查找与引用数据透视表其他函数 1 数据透视表 Excel中非常强大的工具从数据中生成的一个动态汇总的报表用户可以按照任何想要的方式重新安排信息 更好地理解数据数据透视表可以使用外部数据当用户更改源数据时 数据透视表不会自动更新 1 数据透视表基本概念 列标签 行标签 数据透视表中拥有列 行方向的字段总计 数据透视表中一行或者一列所有单元格显示总和组 作为单一项目看待的一组项目的集合项目 字段中的一个元素 在数据透视表中作为行或列的标签显示源数据 用来创建数据透视表的数据 可以是外部数据数值区域 数据透视表中含汇总数据的单元格 2 适合数据透视表的数据库数据 每行都是一条数据记录 包括数据类别和数值类别 首行是对每列数据的分类描述 适合建立数据透视表 不适合建立数据透视表 3 创建数据透视表选中数据源之后点击 插入 数据透视表 键入或选定要建立数据透视表的数据源区域 选择透视表显示位置 新建工作表或现有工作表 点击 确定 4 设置数据透视表的属性右击数据透视表 点击 数据透视表选项 5 设置数据透视表的布局将项目拖至字段列表下的不同区域 6 设置数据透视表的样式选中数据透视表 点击菜单 数据透视表工具 设计 可以调整数据透视表的样式 7 完成数据透视表设置完成后 数据透视表就会按给定的汇总方式和格式要求显示出数据 举例 利用数据透视表分析数据可以通过筛选按钮 选择需要的数据 数学函数逻辑函数查找与引用数据透视表其他函数 1 条件记数 COUNTIF range criteria range用于条件计算的单元格区域 求销售额大于12000的清洁顾问人数 Criteria为确定哪些单元格将被计算在内的条件 其形式可以为数字 表达式或文本 2 日期函数 一 EXCEL基础功能介绍 三 EXCEL常用公式 四 EXCEL基础技巧 二 EXCEL常用快捷键 五 EXCEL常用格式 四 EXCEL基础技巧 操作方法 1 条件格式 使用条件格式可以帮助你可视化的探索和分析数据 查找关键问题 以及识别模式和趋势 条件格式还可以方便的调整工作表的格式 增强表格的易读性 第一步 选择要设置条件格式的区域 操作方法 第二步 选择要设置条件格式的区域 操作方法 第三步 选择 条件格式 突出显示单元格规则 小于 可以按照需要可视化符合条件格式的数据 例如 对于使用率小于90 的数据 红色标注 2 数据有效性 使用数据有效性来控制数据的类型或者用户输入的数值的类型 例如你可以只允许输入某个范围的日期 只允许输入正数 或者使用下拉列表限制选项 操作方法 选择要输入的单元格序列 点击 数据 数据有效性 在 设置 中的 允许 菜单中选择 序列 第一步 在单元格中创建下拉列表先将下拉列表的选项输入到工作表中选中要制作下拉列表的单元格区域 打开 数据有效性 对话框 允许 选择序列 勾选 忽略空值 和 提供下拉箭头 单击 确定 按钮 A 从下拉表中导入 B 手动输入 注意 手动输入时 分隔号为英文输入法下的 逗号 第二步 输入提示的使用在下拉列表单元格处于活动状态时 显示输入提示 选中有下拉列表的单元格区域 打开 数据有效性 对话框 选择 输入信息 标签 在 标题 和 输入信息 中写入提示信息 单击 确定 按钮 第二步 出错提醒的使用在下拉列表单元格内容不符合要求时 显示出错警告 选中有下拉列表的单元格区域 打开 数据有效性 对话框 选择 出错警告 标签 在 标题 和 输入信息 中写入警告信息 单击 确定 按钮 操作方法 动态数据源的下拉列表当下拉列表来源是一个动态增减的单元格区域时 就会用到与创建动态名称技巧类似的方法 前几步与制作普通下拉列表完全一致 不过 来源 不能直接选中 而要输入公式 offset B 2 counta B B 1 单击 确定 按钮 操作方法 录入符合某种规范的数据只允许录入首位以字母开头 长度为8的数据 选中作为数据有效性来源的单元格区域 点击菜单 数据 有效性 在 数据有效性 对话框中 选择允许 自定义 在 公式 框中输入 AND AND LEFT A1 1 a LEFT A1 1 Z LEN A1 8 单击 确定 按钮 3 自定义序列 开始 排序和筛选 自定义排序 自定义序列 中输入常用的序列 后期应用时 只要输入该序列第一个值 自动填充下拉 即可得到输入的序列 第一步 选中待排序名单 第二步 自定义排序 第三步 自定义排序设置 主要关键字 条件筛选列 次序 自定义序列 点击 确定 输入序列 点击 添加 确定 第四步 成果展示 注意 1 选择排序区域时 不包括表头 2 自定义排序时 分隔号使用英文输入法下的 逗号 4 格式刷 5 选择性粘贴 A单击格式刷 单击格式刷 然后单击选择的区域 则两者格式完全相同 B双击格式刷 双击格式刷 然后单击选择某个内容 则两者格式完全相同 可以连续使用 直到再次单击格式刷图标 鼠标恢复正常形状 A将表格的行列互换 复制要转置的部分数据 单击右键 选择性粘贴 命令 选中 转置 命令 则完成操作 B粘贴数值 复制部分数据 单击右键 选择性粘贴 命令 选择 数值 或 123 则完成操作 一 EXCEL基础功能介绍 三 EXCEL常用公式 四 EXCEL基础技巧 二 EXCEL常用快捷键 五 EXCEL常用格式 课程目录 1 文件命名要反映文件核心内容 使人不用打开文件就明白文件的主要思想 标题格式 五 EXCEL常用格式 2 文件命名须包含标题 版本和日期 Eg Excel文件规范要求V1 20131118 3 打开文件后 文件内容必须有标题 标题居中 4 所有sheet表格必须命名 命名与内容标题一致 5 空白sheet表格须删除 不要保留 内容格式 1 去掉网格线 去掉网格线可使整个界面看起来简洁大方 2 表头须冻结 此端冻结 右端表头可自由移动 以免往下拉表格的时候 失去表头指引 而表格比较宽的 还需要冻结左侧表头 以便上下左右拖动都可以看到主文字说明 3 重点表达项加粗 需要保留小数点时 建议保留2位小数 5 保留小数点 对想要凸显的内容进行加粗或更改字体颜色 从而吸引阅读者眼球 4 用颜色区分不同项 6 数字靠右排列 当表内数字较多 数位不一致时 建议将数字 右对齐 不仅使界面整齐 同时便于阅读者进行对比分析 7 内容中若涉及到数据或是资料 须注明数据或是资料的来源等 8 尽量在一屏内显示所有内容 内容实在是比较多的 可以调整列宽度 以及用组合键收起 9 当一个表