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技 能 +专 业 知 识 +行 业 经 验 新 手初 级 用 户中 级 用 户高 级 用 户专 家 2Excel中级培训教程 一 、 公 式 与 函 数 基 础 （ 10Min）1.2、 公 式 中 的 运 算 符 ： 1） 算 术 运 算 符算 术 运 算 符 号 运 算 符 含 义 示 例+(加 号 ) 加 2+3=5-(减 号 ) 减 3-1=2*(星 号 乘 3*2=6 /(斜 杠 ) 除 6/2=3%(百 分 号 ) 百 分 号 50%(脱 字 号 ) 乘 方 43=43=64； 24=161、 认 识 公 式1.1、 常 量 ： 指 直 接 输 入 公 式 中 的 数 字 或 者 文 本 值 ， 即 不 用 计 算 的 值 ； 3Excel中级培训教程 1.2、 公 式 中 的 运 算 符比 较 运 算 符 运 算 符 含 义 示 例=(等 号 ) 相 等 B1=C1,若 B1中 单 元 格 内 的 值 确 实 与 C1中的 值 相 等 ,则 产 生 逻 辑 真 值 TRUE,若 不 相等 ,则 产 生 逻 辑 假 值 FALSE(小 于 号 ) 小 于 B1(大 于 号 ) 大 于 B1C1,若 B1中 数 值 为 6,C1中 数 值 为 4,则条 件 成 立 产 生 逻 辑 真 值 TRUE,否 则 产 生 逻辑 假 值 FALSE=(大 于 等 于 号 ) 大 于 等 于 B1=C1 (不 等 号 ) 不 等 于 B1C1=(小 于 等 于 号 ) 小 于 等 于 B1=C1 一 、 公 式 与 函 数 基 础 （ 10Min）1、 认 识 公 式3） 、 比 较 运 算 符2） . 文 本 运 算 符 ： “ ” ， 然 后 按 空 格 键最 后 按 “ 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） ： 取 交 集 进 行 逻 辑 判 断 同 时 满 足 所 有 条 件 才 Ture2） ： 取 并 进 行 逻 辑 判 断 只 要 其 中 一 个 满 足 就 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、高级筛选请 使 用 若 要 查 找?（ 问 号 ） 任 何 单 个 字 符例 如 ， sm?th 查 找 “ smith” 和 “ smyth”*（ 星 号 ） 任 何 字 符 数例 如 ， *east 查 找 “ Northeast” 和“ Southeast”（ 波 形 符 ） 后 跟 ?、 * 或 问 号 、 星 号 或 波 形 符例 如 ， “ fy91?” 将 会 查 找 “ fy91?”通 配 符 ： 以 下 通 配 符 可 作 为 筛 选 以 及 查 找 和 替 换 内 容 时 的 比 较 条 件 : 3、 数 据 有 效 性 设 置 及 应 用 举 例 ： 在 意 见 收 集 时 ， 经 常 碰 到 收 集 意 见 太 发 散 的 情 况 ； 比 如 以 下 情 况 ； 对 一次 Team building 进 行 意 见 收 集 ； 得 到 左 下 图 的 结 果 。 作 为 一 个 活 动 组 织 者 ， 如何 统 计 最 终 意 见 ？ 四 、 数 据 筛 选 （ 10Min）解 决 办 法 ： 在 发 出 意 见 收 集 表 之 前 ， 使 用 数 据 有 效 性 设 置 功 能 ， 给 出 规 定 的 几 类选 项 ， 要 求 Team成 员 做 “ 选 择 题 ” 而 非 “ 自 由 回 答 ”设 置 方 法 ： 数 据 菜 单 数 据 有 效 性 选 择 设 置 序 列 1、 分 类 汇 总 的 计 算 方 法 分 类 汇 总 是 对 数 据 清 单 进 行 数 据 分 析 的 一 种 方 法 .在 数 据 中 实 现 对 符 合 某 一 个相 同 特 性 的 数 据 行 ， 按 照 特 性 信 息 进 行 数 据 汇 总 的 功 能 ；四 、 数 据 汇 总 （ 10Min）举 例 ： 还 是 无 作 业 工 时 ： 如 果 希 望 按 照 车 间 ， 来 统 计 损 耗 工 时 以 及 损 耗 工 时 （ HR） ；1） 首 先 按 照 车 间 进 行 排 序2） 数 据 菜 单 -选 择 分 类 汇 总 四 、 数 据 汇 总 （ 10Min）举 例 ： 希 望 按 照 车 间 ， 来 统 计 损 耗 工 时 以 及 损 耗 工 时 （ HR） ；点 击 按 钮 2点 击 按 钮 1 2、 数 据 的 组 合 与 取 消 在 同 一 个 Excel sheet表 中 ； 当 数 据 行 /列 非 常 多 ， 对 于 大 部 分 客 户 来 说 ， 只 希望 阅 读 到 重 点 字 段 ， 可 使 用 组 合 按 钮 ； 使 报 表 外 观 更 直 观 ， 简 洁 ；四 、 数 据 汇 总 （ 10Min）举 例 ： 在 下 表 中 ， 如 果 只 需 要 关 注 车 间 及 损 耗 工 时 字 段 ； 除 了 使 用 隐 藏 按 钮 外 ；亦 可 使 用 组 合 按 钮选 中 “ 车 间 ” 与 “ 损 耗 工 时 ” 之 间 的 数 据 列单击 数据-组及分级显示-组合按钮点 击 ： “ 损 耗 工 时 ” 上 方 按 钮 ， 自 动 合 并 上 一 步 骤 选 中 列 ； 按 钮 由 变 成 + 3、 合 并 计 算当 数 据 行 不 多 时 ， 合 并 计 算 是 数 据 汇 总 的 又 一 个 好 工 具 ；四 、 数 据 汇 总 （ 10Min）举 例 ： 按 照 车 间 ， 汇 总 “ 损 耗 工 时 ” 以 及 “ 损 耗 工 时 （ HR）1） 鼠 标 点 击 一 处 空 白 区 域 ， 存 放 合 并 计 算 结 果 ； 注 意 该 区 域 不 能 和 数 据 区 域 重 叠2） 点 击 数 据 菜 单 ： 合 并 计 算 ：*引 用 位 置 中起 始 位 置 为 ： “ 车 间 ” （ B列 ） 结 束 位 置 为 ： “ 损 耗 工 时 （ HR)” (H列 ） *标 签 位 置 ： 选 中 “ 首 行 ” 及 “ 最 左 列 ”确 定 3、 合 并 计 算当 数 据 行 不 多 时 ， 合 并 计 算 是 数 据 汇 总 的 又 一 个 好 工 具 ； 数 据 菜 单 ， 合 并 计 算 ；四 、 数 据 汇 总 （ 10Min）举 例 ： 按 照 车 间 ， 汇 总 “ 损 耗 工 时 ” 以 及 “ 损 耗 工 时 （ HR）3） 得 到 如 下 结 果 ； 增 添 标 题 行 即 可 得 到 汇 总 结 果 ；4） 在 同 一 个 sheet表 对 同 一 份 数 据 二 次 合 并 计 算 时 ， 注 意 需 要 删 除 “ 所 有 引 用 位 置 ” 内 数 据 ； 重 新 选 择 引 用 位 置 ； 线 柱 图 /MTB中 的 柏 拉 图举 例 ； 在 KPI指 标 的 分 解 中 ， 常 需 要 了 解 各 种 不 同 因 素 的 现 状 （ 柱 图 /饼 图 ） 以 及对 结 果 的 百 分 比 影 响 ； 在 Excel中 可 以 使 用 线 柱 图 达 到 一 图 展 示 的 效 果 ；五 、 制 作 与 使 用 图 表 （ 5Min） 线 柱 图 /MTB中 的 柏 拉 图举 例 ； 在 KPI指 标 的 分 解 中 ， 常 需 要 了 解 各 种 不 同 因 素 的 现 状 （ 柱 图 /饼 图 ） 以 及对 结 果 的 百 分 比 影 响 ； 在 Excel中 可 以 使 用 线 柱 图 达 到 一 图 展 示 的 效 果 ；五 、 制 作 与 使 用 图 表 （ 5Min）Excel 2003版 本1） 选 中 数 值 区 域 为 名 称 ， 金 额 ， 及 累 计 百 分 比 2） 在 “ 插 入 ” 菜 单 ， “ 图 表 ” 选 项 下 选 择 自 定 义 类 型 “ 两 轴 线 柱 图 线 柱 图 /MTB中 的 柏 拉 图举 例 ； 在 KPI指 标 的 分 解 中 ， 常 需 要 了 解 各 种 不 同 因 素 的 现 状 （ 柱 图 /饼 图 ） 以 及对 结 果 的 百 分 比 影 响 ； 在 Excel中 可 以 使 用 线 柱 图 达 到 一 图 展 示 的 效 果 ；五 、 制 作 与 使 用 图 表 （ 5Min）Excel 2003版 本3） 在 图 表 向 导 中 完 善 各 选 项 （ 标 题 ， 坐 标 轴 等 ）点 击 “ 下 一 步 ” 完 成 图 表 绘 制 线 柱 图 /MTB中 的 柏 拉 图举 例 ； 在 KPI指 标 的 分 解 中 ， 常 需 要 了 解 各 种 不 同 因 素 的 现 状 （ 柱 图 /饼 图 ） 以 及对 结 果 的 百 分 比 影 响 ； 在 Excel中 可 以 使 用 线 柱 图 达 到 一 图 展 示 的 效 果 ；五 、 制 作 与 使 用 图 表 （ 5Min）Excel 2007版 本1） 选 中 数 据 ， 点 击 “ 插 入 ” “ 柱 形 图 ” “ 二 维 柱 形 图 ” ； 线 柱 图 /MTB中 的 柏 拉 图五 、 制 作 与 使 用 图 表 （ 5Min）Excel 2007版 本2） 直 方 图 出 现 了 ， 果 然 没 有 折 线 关 键 的 步 骤 来 了 ， 在 直 方 图 上 点 击 右键 ， 选 择 “ 设 置 数 据 系 列 格 式 ” ； ； 线 柱 图 /MTB中 的 柏 拉 图五 、 制 作 与 使 用 图 表 （ 5Min）Excel 2007版 本3） 在 “ 系 统 选 项 ” “ 系 列 绘 制 在 ” 中 ， 选 择 “ 次 坐 标 轴 ” 。 看 到 了 吗？ 表 示 累 计 百 分 比 的 红 色 方 出 现 了 ； 线 柱 图 /MTB中 的 柏 拉 图五 、 制 作 与 使 用 图 表 （ 5Min）Excel 2007版 本4）在红色方上单击右键，选择“更改系列图表类型”； ； 5） 在 “ 更 改 图 标 类 型 ” 中 ， 选 择 “ 折 线 图 ” ； 线 柱 图 /MTB中 的 柏 拉 图五 、 制 作 与 使 用 图 表 （ 5Min）Excel 2007版 本6）折线出现啦再在图上单击右键“添加数据标签”，线柱双轴图大功造成！ ； 1、 数 据 透 视 表 的 强 大 功 能六 、 制 作 与 使 用 数 据 透 视 表 （ 15Min） 还 是 用 5月 无 作 业 工 时 作 为 基 础 数 据 分 析 ； 应 用 数 据 透 视 表 ， 将 实 现 强 大 的 数 据 分 析及 展 示 能 力A： 各 车 间 的 损 耗 工 时 和 HR损 耗 工 时 各 是 多 少 ？B： 各 车 间 的 在 5月 份 各 停 线 多 少 单C： 各 车 间 停 线 工 时 占 5月 百 分 比 情 况 D： 各 机 型 停 线 情 况 如 何 ？E： 2、 创 建 数 据 透 视 表六 、 制 作 与 使 用 数 据 透 视 表 （ 15Min）1） 鼠 标 点 击 数 据 区 域 ；2） “ 数 据 ” 菜 单 点 击 “ 数 据 透 视 表 和 数 据 透 视 图 ” 按 钮 六 、 制 作 与 使 用 数 据 透 视 表 （ 15Min）3） 按 照 统 计 要 求 拖 放 相 关 字 段 到 行 /列 /页 /数 据 区 域 ； 3、 编 辑 数 据 透 视 表六 、 制 作 与 使 用 数 据 透 视 表 （ 15Min）数 据 透 视 表 可 通 过 拖 放 与 隐 藏 字 段 实 现 按 照 不 同 字 段 汇 总 及 比 对 1、 宏 的 功 能七 、 宏 的 录 制 与 运 用 （ 10Min） 在 Excel中 ， 宏 是 指 一 系 列 Excel能 够 执 行 的 VBA语 句 ； 在 语 言 结 构 中 ， 宏 由 一 系 列的 命 令 和 函 数 组 成 ， 保 存 在 VBA的 模 块 中 。 可 以 根 据 用 户 需 要 及 时 运 行 ， 执 行 对 应的 任 务 。VBA： 是 一 种 功 能 强 大 的 脚 本 边 写 语 言 ， 主 要 功 能 是 实 现 应 用 程 序 的 自 动 化 ；VBA中 可 以 包 含 宏 ， 但 不 仅 仅 由 宏 构 成 ；当 需 要 重 复 执 行 一 个 或 多 个 简 单 命 令 时 ： 使 用 宏 可 提 高 工 作 效 率 1、 宏 的 功 能七 、 宏 的 录 制 与 运 用 （ 10Min） 举 例 ： 将 以 下 数 据 统 一 更 改 字 体 为 “ 黑 体 ” ， 字 体 大 小 设 置 为 9号 ； 增 加 边 框 ；并 且 将 “ 车 间 ” 列 填 充 为 黄 色 ； 在 第 一 次 操 作 时 使 用 宏 录 制 功 能 ， 当 以 上 设 置 完 成 后 ， 停 止 宏 录 制 ；则 在 数 据 内 容 更 改 后 ， 重 新 运 行 宏 能 瞬 间 完 成 相 同 的 设 置 ， 而 不 是 重 新 手 动 设 置 ； 2、 录 制 与 运 行 宏Excel 2003“ 工 具 ” 菜 单 ； “ 宏 ” -“ 录 制 宏 ” ；Excel 2007“ 视 图 ” 菜 单 ； “ 宏 ” -“ 录 制 宏 ” 七 、 宏 的 录 制 与 运 用 （ 10Min）1） 、 进 入 右 图 界 面 ：宏 名 ： 任 意 名 称快 捷 键 ： 运 行 该 宏 时 使 用保 存 在 ： 默 认 当 前 工 作 表说 明 ： 任 意 备 注2） 、 确 认 后 ： 开 始 进 行 正 常 的 数 据 处 理 ； 如 ， 全 选 数 据 ， 增 加 边 框 ；更 改 字 体 类 型 及 大 小 ， 填 充 颜 色 ； 3） 、 从 菜 单 进 入 “ 宏 ” ， 选 择 “ 停 止 录 制 ” ；4） 、 当 数 据 区 域 数 据 变 更 后 ， 从 菜 单 进 入 “ 宏 ” ， 选 择 “ 查 看 宏 ” “ 执 行 ” ； 或 直 接运 行 快 捷 键 ， 可 运 行 该 宏 ； 2、 录 制 与 运 行 宏 打 开 一 个 包 含 宏 的 Excel文 档 ， 出 现 以 下 对 话 框 时 ， 从 工 具 菜 单 -选 项 -安 全 性 -宏 安 全 性 中 ， 将 安 全 级 别 调 低 即 可 运 行 ；七 、 宏 的 录 制 与 运 用 （ 10Min） 八 、 Excel小 技 巧 分 享 与 讨 论 （ 15Min）