精品excel中级培训教程(从入门到精通)

公式与函数基础 10Min P2 P8认识公式公式 函数的输入与编辑单元格和范围的引用文本的处理 5Min P9 P13字符串的编辑字符串的截取 返回相应值使用函数 45Min P14 P28日期及时间函数 5Min 查询函数 10Min 逻辑判断函数 10Min 数学函数 5Min 统计函数 5Min 函数使用范例 10Min 数据筛选 10Min P29 P31自动筛选高级筛选数据有效性设置及应用数据汇总 5Min P32 P36分类汇总的计算方法数据的组合与取消合并计算制作与使用图表 5Min P37使用数据透视表 15Min 创建数据透视表编辑数据透视表宏的录制与运用 10Min 宏的功能录制与运行宏Excel小技巧分享与讨论 10Min 课程大纲 前言 Excel用户层次分类原则 刚刚开始接触 基本掌握常用功能 熟练使用常用功能 数据透视表 公式 数组公式 VBA简单编程 Excel技能 专业知识 行业经验 新手 初级用户 中级用户 高级用户 专家 一 公式与函数基础 10Min 1 2 公式中的运算符 1 算术运算符 1 认识公式 1 1 常量 指直接输入公式中的数字或者文本值 即不用计算的值 1 2 公式中的运算符 一 公式与函数基础 10Min 1 认识公式 3 比较运算符 2 文本运算符 号 可以将文本连接起来 一 公式与函数基础 10Min 1 2 公式中的运算符4 引用运算符 实例中应该是C列 不是D列 2 公式的输入与编辑 2 1 公式的输入将光标移到单元格中 在其中输入等号和公式内容即可 输入过程中 左下图 输入的字符会同时显示在单元格和公式编辑栏 输入完成后按enter键 再次选中该单元格 可见只有在编辑栏才会显示公式 在单元格内则只显示结果 右下图 一 公式与函数基础 10Min 2 函数的输入与编辑 2 2 函数的记忆式键入1 将光标移到单元格中 在其中输入等号和公式内容的前几位字母时 会在单元格下方显示与这些字母相关的有效函数 左下图 2 如果不清楚出现的这些函数如何选择 亦可直接在编辑栏点击2003版本的 号 2007版本的 fx 在弹出的对话框中根据函数解释进行选择 一 公式与函数基础 10Min 2 公式的输入与编辑 2 3 公式编辑1 双击公式所在的单元格 在单元格内直接更改2 选中公式所在的单元格 然后单击公式编辑栏 在编辑栏内重新输入正确的公式3 选中公式所在的单元格 按F2键激活单元格的公式 在其中进行修改 一 公式与函数基础 10Min 一 公式与函数基础 10Min 3 单元格和范围的引用 实例 旧的美元与人民币汇率为6 8 在下表中要求计算每行对应的人民币金额 当汇率发生变化时 只能将每行的公式重新输入 此时使用单元格的引用更高效 3 1 相对引用相对引用指公式中的单元格位置将随着公式单元格的位置而改变3 2 绝对引用绝对引用是指公式和函数中的位置是固定不变的 绝对引用是在列字母和行数字之前都加上美元符号 如 A 4 C 63 3 混合引用混合引用是指在一个单元格引用中 既有绝对引用 也有相对引用 1 字符串的编辑 实例1 工作中经常使用除法进行两个数据的相除 得到的结果往往不是整数 此时如果要显示固定位数怎么办 常规工具条中的减少小数位数只能在显示上 欺骗 视觉 实际在单元格内还是多位数 字符串的编辑函数FIxed是解决方法之一 二 文本的处理 5Min 当结果是非数字类型时 乘1即可变为数字类型 1 字符串的编辑 实例2 统计并显示到岗人员 表格中显示每一天的到岗数 需要展示为 某日到岗人员为 人 使用CONCATENATE函数合并字符串 二 文本的处理 5Min 实例2 运算符 的功能与此相似 但需手工输入 逻辑性较差该功能在后续的多重条件VLOOKUP中可以起到很好的辅助作用 1 字符串的编辑 实例3 我司的新物料编码规则已经限制了编码中的字符数量 新编码是12位 旧编码17位 当面临大量编码时 如何快速区分新旧编码与错误编码 使用Len函数统计字符长度 二 文本的处理 5Min 2 返回相应值 实例1 仍然以物料编码为例 新物料编码规则中 前三位代表物料三级分类 比如 主机面壳都是以BCA开头 如何在大量编码中 按照物料三级分类统计数量 Left函数 二 文本的处理 5Min 前三位代码截取OK后 如何进行数量统计 参考数据汇总章节 2 返回相应值 实例2 right与MID函数 与Left函数类似 截取数据右侧或者中间固定位数的数据 实例3 在公司的物料编码描述中 可以看到完整的物料描述非常长 如果只想保留最开始的中文描述 怎么办 二 文本的处理 5Min 方法1 方法2 数据 分列 1 日期及时间函数 很多时候 我们需要了解当前日期 时间 但又不能老是手动调整 怎么办 方法1 Today 函数与Now 函数 三 使用函数 40Min 方法2 使用快捷键1 Ctrl 显示当前日期2 Ctrl shift 显示当前时间3 Ctrl 然后按空格键最后按 Ctrl Shift 显示当前日期和时间 区别 Now和Today函数是动态的 但快捷键得到的是静态的 2 查询函数 无作业工时的统计界面如下 如果想知道某个部门在某一个月的无作业工时 如何解决 Hlookup 水平查找 三 使用函数 40Min 需要在数据区域首行搜索的值 原始的数据区域 满足条件2的单元格在数据区域的行数 0 找不到时 返回 N A 1 找不到时 返回0 2 查询函数 如果无作业工时的统计界面如下 想知道某个部门在某一个月的无作业工时 又如何解决 Vlookup 竖直查找 三 使用函数 40Min 需要在数据区域首列搜索的值 PMC部 原始的数据区域 满足条件2的单元格在数据区域的列数 2 查询函数 Hlookup Vlookup使用技巧1 如何获取查询满足条件的列序列号 三 使用函数 40Min 2 查询函数 Hlookup Vlookup使用技巧2 当行 列序列号不同时 重新输入公式比较慢 如何解决 三 使用函数 40Min 首先确保数据区域已经被全部选中 鼠标点击单元格右下角 填充公式后 手动更改行数 列数即可 2 查询函数 手中有一个部门的电话号码清单 现在有其中一个人的电话号码 需要知道这个人是谁 查找指定数值 lookup函数 三 使用函数 40Min 注意 数据区域的值需要以升序排序 否则可能无法提供正确的值 3 逻辑判断函数 三 使用函数 40Min 1 逻辑判断入门 3 逻辑判断函数 三 使用函数 40Min 1 取交集进行逻辑判断 同时满足所有条件才Ture 2 取并进行逻辑判断 只要其中一个满足就Ture 3 根据不同条件返回不同值 4 数学函数 三 使用函数 40Min 1 举例 产品标准重量85KG 计算多件产品与标准重量的偏差 ABS 取绝对值 4 数学函数 三 使用函数 40Min 3 两数相除的整数部分 QUOTIENT函数 2 举例 计算两数相除的余数 MOD成品的包装运输有数量限制 假如一个卡通箱只能包60PCS 客户的订单情况如下 想知道整包装出货后仍有多少数量剩余 4 数学函数 三 使用函数 40Min 4 条件求和 SUMIf各部门Q1无作业工时汇总如下 如何快速取得一个或多个部门的Q1无作业工时之和 4 数学函数 三 使用函数 40Min 5 舍入和取整函数 Ceiling Floor 按条件向上 下舍入 Even ODD 按条件向上舍入为偶数 奇数 INT 向下取整 5 统计函数 三 使用函数 40Min 1 获取数组的最大 最小值 MAX MAXA MIN MINAMAX与MIN功能是获取所有数据的最小值 MAXA与MINA功能是获取所有非空单元格的最小值 2 单元格的统计函数COUNT与COUNTA统计单元格个数 COUNTBLANK统计空白单元格个数 COUNTIF 按条件统计 COUNTIFS 按多重条件统计 3 计算条件平均值 AVERAGEIF 1 自动筛选 快捷键 Alt D F F 四 数据筛选 10Min 2 高级筛选数据 筛选 高级筛选 方式1 在原有区域直接显示筛选结果 不满足条件部分隐藏不显示 列表区域 原始数据区域 条件区域 筛选条件区域 2 高级筛选 通配符 以下通配符可作为筛选以及查找和替换内容时的比较条件 3 数据有效性设置及应用举例 在意见收集时 经常碰到收集意见太发散的情况 比如以下情况 对一次Teambuilding进行意见收集 得到左下图的结果 作为一个活动组织者 如何统计最终意见 四 数据筛选 10Min 解决办法 在发出意见收集表之前 使用数据有效性设置功能 给出规定的几类选项 要求Team成员做 选择题 而非 自由回答 设置方法 数据菜单 数据有效性 选择设置 序列 1 分类汇总的计算方法分类汇总是对数据清单进行数据分析的一种方法 在数据中实现对符合某一个相同特性的数据行 按照特性信息进行数据汇总的功能 四 数据汇总 10Min 举例 还是无作业工时 如果希望按照车间 来统计损耗工时以及损耗工时 HR 1 首先按照车间进行排序2 数据菜单 选择分类汇总 四 数据汇总 10Min 举例 希望按照车间 来统计损耗工时以及损耗工时 HR 点击按钮2 点击按钮1 2 数据的组合与取消在同一个Excelsheet表中 当数据行 列非常多 对于大部分客户来说 只希望阅读到重点字段 可使用组合按钮 使报表外观更直观 简洁 四 数据汇总 10Min 举例 在下表中 如果只需要关注车间及损耗工时字段 除了使用隐藏按钮外 亦可使用组合按钮 选中 车间 与 损耗工时 之间的数据列 单击数据 组及分级显示 组合按钮 点击 损耗工时 上方按钮 自动合并上一步骤选中列 按钮由 变成 3 合并计算当数据行不多时 合并计算是数据汇总的又一个好工具 四 数据汇总 10Min 举例 按照车间 汇总 损耗工时 以及 损耗工时 HR 1 鼠标点击一处空白区域 存放合并计算结果 注意该区域不能和数据区域重叠 2 点击数据菜单 合并计算 引用位置中起始位置为 车间 B列 结束位置为 损耗工时 HR H列 标签位置 选中 首行 及 最左列 确定 3 合并计算当数据行不多时 合并计算是数据汇总的又一个好工具 数据菜单 合并计算 四 数据汇总 10Min 举例 按照车间 汇总 损耗工时 以及 损耗工时 HR 3 得到如下结果 增添标题行即可得到汇总结果 4 在同一个sheet表对同一份数据二次合并计算时 注意需要删除 所有引用位置 内数据 重新选择引用位置 线柱图 MTB中的柏拉图举例 在KPI指标的分解中 常需要了解各种不同因素的现状 柱图 饼图 以及对结果的百分比影响 在Excel中可以使用线柱图达到一图展示的效果 五 制作与使用图表 5Min 线柱图 MTB中的柏拉图举例 在KPI指标的分解中 常需要了解各种不同因素的现状 柱图 饼图 以及对结果的百分比影响 在Excel中可以使用线柱图达到一图展示的效果 五 制作与使用图表 5Min Excel2003版本 1 选中数值区域为名称 金额 及累计百分比 2 在 插入 菜单 图表 选项下选择 自定义类型 两轴线柱图 线柱图 MTB中的柏拉图举例 在KPI指标的分解中 常需要了解各种不同因素的现状 柱图 饼图 以及对结果的百分比影响 在Excel中可以使用线柱图达到一图展示的效果 五 制作与使用图表 5Min Excel2003版本 3 在图表向导中完善各选项 标题 坐标轴等 点击 下一步 完成图表绘制 线柱图 MTB中的柏拉图举例 在KPI指标的分解中 常需要了解各种不同因素的现状 柱图 饼图 以及对结果的百分比影响 在Excel中可以使用线柱图达到一图展示的效果 五 制作与使用图表 5Min Excel2007版本 1 选中数据 点击 插入 柱形图 二维柱形图 线柱图 MTB中的柏拉图 五 制作与使用图表 5Min Excel2007版本 2 直方图出现了 果然没有折线 关键的步骤来了 在直方图上点击右键 选择 设置数据系列格式 线柱图 MTB中的柏拉图 五 制作与使用图表 5Min Excel2007版本 3 在 系统选项 系列绘制在 中 选择 次坐标轴 看到了吗 表示累计百分比的红色方出现了 线柱图 MTB中的柏拉图 五 制作与使用图表 5Min Excel2007版本 4 在红色方上单击右键 选择 更改系列图表类型 5 在 更改图标类型 中 选择 折线图 线柱图 MTB中的柏拉图 五 制作与使用图表 5Min Excel2007版本 6 折线出现啦 再在图上单击右键 添加数据标签 线柱双轴图大功造成 1 数据透视表的强大功能 六 制作与使用数据透视表 15Min 还是用5月无作业工时作为基础数据分析 应用数据透视表 将实现强大的数据分析及展示能力 A 各车间的损耗工时和HR损耗工时各是多少 B 各车间的在5月份各停线多少单 C 各车间停线工时占5月百分比情况 D 各机型停线情况如何 E 2 创建数据透视表 六 制作与使用数据透视表 15Min 1 鼠标点击数据区域 2 数据 菜单 点击 数据透视表和数据透视图 按钮 六 制作与使用数据透视表 15Min 3 按照统计要求拖放相关字段到行 列 页 数据区域 3 编辑数据透视表 六 制作与使用数据透视表 15Min 数据透视表可通过拖放与隐藏字段实现按照不同字段汇总及比对 1 宏的功能 七 宏的录制与运用 10Min 在Exc