EXCEL办公应用教程.ppt

excel高级应用 办公应用教程 excel高级应用 excel高级应用基础excel函数高级应用excel图表高级应用数据透视表与数据透视图 excel数据录入excel数据处理excel安全设置与数据保护excel与其它应用程序的数据共享 第一讲excel高级应用基础 一 excel数据录入1 数据输入技巧相关知识 excel支持的主要数据类型 n t d l单元格格式自动更正数据有效性键盘快捷键 第一讲excel高级应用基础 实例练习1 对 绩效考核 表快速录入数据工作表要求 某车间进行绩效考核 需要每天统计甲 乙 丙 丁四个班组的实绩产量和计划产量 并要标注是否完成任务 工作表包括 日期 班组 实产量 计划产量 是否完成任务 五列 第一讲excel高级应用基础 1 选定单元格区域 右击 设置单元格格式 第一讲excel高级应用基础 2 简化重复录入文本 工具 自动更正选项 第一讲excel高级应用基础 3 设置数据有效性 数据 有效性 日期列有效性 班组列有效性 第一讲excel高级应用基础 4 填充 是否完成任务 列 选定单元格 在编辑栏输入公式 如 if c3 d3 是 否 注意 文本型数据在if函数中使用时要加双引号定界 第一讲excel高级应用基础 一 excel数据录入2 数据导入技巧相关知识 access数据库 数据表数据源导入外部数据数据刷新 第一讲excel高级应用基础 实例练习2 将access数据库表导入excel中 1 启动access 建立 产品库 mdb 并在其中利用向导建立 产品明细表 2 在excel中 数据 导入外部数据 导入数据 第一讲excel高级应用基础 定位数据源 二 excel数据处理1 数据高级筛选技巧相关知识 高级筛选条件区域条件区域的构造规则 数据 筛选 高级筛选 第一讲excel高级应用基础 实例练习3 或 条件筛选练习在 员工通讯簿 按下列条件筛选数据 1 筛选条件是 部门 列中包括 人事部 或 营销部 的数据行 2 筛选条件是 部门 为 开发部 或 到职日期 为2003年的数据行 第一讲excel高级应用基础 构造条件区域 数据 筛选 高级筛选 第一讲excel高级应用基础 1 同一列中的 或 条件 第一讲excel高级应用基础 2 多列中的 或 条件 条件必须在不同行 第一讲excel高级应用基础 实例练习4 与 条件筛选练习在 员工通讯簿 按下列条件筛选数据 筛选条件是 部门 列中包括 营销部 性别 为 男 且 到职日期 为2003年1月1日以后的数据行 第一讲excel高级应用基础 同一行的多个 与 条件 第一讲excel高级应用基础 实例练习5 与 或 复合条件筛选练习在 员工通讯簿 按下列条件筛选数据 筛选条件是 部门 为 营销部 且 到职日期 为2003年1月1日以后 部门 为 开发部 且 到职日期 为2003年1月1日以前的数据行 第一讲excel高级应用基础 条件区域 第一讲excel高级应用基础 实例练习6 将公式作为条件的高级筛选练习在 员工信息簿 中按下列条件筛选数据 筛选条件 部门 为 营销部 或 编辑部 工资值小于数据表中所有员工平均工资的员工信息 第一讲excel高级应用基础 1 筛选条件 筛选出低于平均工资的员工信息 条件区域 c 2 d 3 在d3单元格中输入 average f11 f110 在c3单元格中输入 f11 d 3 第一讲excel高级应用基础 2 数据 筛选 高级筛选 在 高级筛选对话框中设置列表区域和条件区域 第一讲excel高级应用基础 3 将筛选结果复制到 输出表 中 数据区域 a11 g59 这时可以返回源数据表 执行 数据 筛选 全部显示 将数据恢复全部显示状态 第一讲excel高级应用基础 4 筛选条件 在输出表中构建条件区域 或 条件 第一讲excel高级应用基础 5 数据 筛选 高级筛选 在 高级筛选对话框中设置列表区域和条件区域 第一讲excel高级应用基础 6 筛选结果如下 第一讲excel高级应用基础 二 excel数据处理2 数据有效性设定与规则相关知识 设置数据类型和有效范围有效性检验 对已有数据审核 圈释错误数据 第一讲excel高级应用基础 实例练习7 某单位要统计岗位考试成绩 为方便输入时更加准确 对表中的数据输入类型及范围设置相应的有效性 具体要求如下 考号为4位字符 不能多于或少于4位 笔试和口试成绩为0 100之间的数字 输入成绩时 显示输入提示信息 输入错误时显示出错警告 第一讲excel高级应用基础 打开 实例表 岗位考试成绩 xls 1 在 实例练习7 工作表中 选中考号区域 a3 a14 数据 有效性 右击 设置单元格格式 数字 第一讲excel高级应用基础 2 设定成绩的数据类型和有效性 选中 e3 f14 区域 数据 有效性 分别设置最大值 最小值 提示 出错等信息 第一讲excel高级应用基础 实例练习8 对 岗位考试成绩表 利用设置有效性规则 利用数据审核工具找出错误的数据并标明 设置笔试和口试成绩为0 100之间的数字 找出已输入的错误数据 第一讲excel高级应用基础 打开 实例表 岗位考试成绩 xls 1 在 实例练习8 工作表 选中 e3 f14 区域 第一讲excel高级应用基础 2 圈定错误数据 工具 公式审核 显示 公式审核 工具栏 单击 公式审核 工具栏中的 圈释无效数据 按钮 第一讲excel高级应用基础 二 excel数据处理3 数据分类汇总相关知识 排序求和 平均值 最大值 最小值分类汇总合并计算 第一讲excel高级应用基础 实例练习9 对 3a公司员工基本信息表 进行分类汇总以统计各部门的收入情况 具体要求如下 统计各个部门员工的平均工资 注意 要先排序后汇总 第一讲excel高级应用基础 打开 实例表 3a公司员工基本信息表 xls 1 在 实例练习9 工作表 对各部门员工工资分类汇总 单击 部门 列任一单元格 再单击常用工具栏上 升序 按钮 单击数据表的任一单元格 数据 分类汇总 第一讲excel高级应用基础 2 单击分级显示按钮 第一讲excel高级应用基础 实例练习10 对 3a公司员工基本信息表 进行分类汇总以统计各部门的收入情况 具体要求如下 统计各部门男 女员工奖金的最大值 将分类汇总结果分页打印出来 注意 要先对部门分类汇总 再对性别分类汇总 第一讲excel高级应用基础 打开 实例表 3a公司员工基本信息表 xls 1 在 实例练习10 工作表 统计各部门男 女奖金的最大值 单击 部门 列任一单元格 数据 排序 第一讲excel高级应用基础 2 单击数据表的任一单元格 数据 分类汇总 先对 部门 汇总最大值 部门汇总结果 第一讲excel高级应用基础 3 再单击数据表的任一单元格 数据 分类汇总 再对 性别 汇总最大值 性别 汇总结果 第一讲excel高级应用基础 4 分页显示汇总结果 再单击数据表的任一非空单元格 数据 分类汇总 选中 每组数据分页 单击 确定 按钮 第一讲excel高级应用基础 4 文件 页面设置 工作表 打印预览 第一讲excel高级应用基础 删除分类汇总表的方法 数据 分类汇总 单击 全部删除 按钮 第一讲excel高级应用基础 二 excel数据处理4 数据的合并计算相关知识 链接 数据 合并计算 第一讲excel高级应用基础 实例练习11 对 r公司工资簿 通过合并计算得出一季度工资汇总表 要求 当修改了某个月份某项工资数值时 工资汇总表的数据随之更新 第一讲excel高级应用基础 打开 实例表 r公司工资簿 xls 1 将工资表的标记性文字复制到 工资汇总单 表中 并将表头更改为 r公司一季度工资单 第一讲excel高级应用基础 2 单击汇总表中 c3 i20 单元格区域 数据 合并计算 确定要合并的数据源位置 并添加到列表中 选中 创建连至源数据的链接 复选框 确定 通过折叠钮选择引用数据位置 单击 添加 按钮 依次处理三个月的数据 第一讲excel高级应用基础 三 excel安全设置与数据保护对工作表 工作簿 单元格的保护相关知识 文件保存加密保护单元格区域 第一讲excel高级应用基础 实例练习12 对7高炉8月硅数统计表进行保护 具体要求 全部工作表中只有d列数据可以修改 对工作表设置密码 123 对整个工作簿设置密码 456 第一讲excel高级应用基础 打开 实例表 7高炉8月硅数统计 xls 1 设置单元格保护选择d列数据 右击 设置单元格格式 保护 取消 锁定 复选框 确定 第一讲excel高级应用基础 2 设置单元格保护 工具 保护 允许用户编辑区域 第一讲excel高级应用基础 第一讲excel高级应用基础 3 设置工作簿保护 工具 选项 安全性 第一讲excel高级应用基础 撤消对工作表的保护 2 工具 保护 撤消工作表保护 第一讲excel高级应用基础 隐藏工作表的数据右击工作表名称 查看代码 第一讲excel高级应用基础 四 excel与其它应用程序的数据共享相关知识 源数据 目标数据 选择性粘贴 粘贴链接更新 第一讲excel高级应用基础 实例练习13 excel与word数据共享excel与powerpoint数据共享以绩效考核 xls为例 第一讲excel高级应用基础 excel高级函数入门介绍excel函数的应用 第二讲excel函数高级应用 一 excel高级函数入门介绍1 函数及其结构2 输入公式与插入函数3 巧用excel帮助理解函数 第二讲excel函数高级应用 一 excel高级函数入门介绍1 函数及其结构相关知识 函数的定义参数 常量 公式 函数 数组 单元格引用等嵌套函数结构 函数名 参数列表 第二讲excel函数高级应用 函数结构说明 第二讲excel函数高级应用 一 excel高级函数入门介绍 第二讲练习表 函数结构说明 xls 第二讲excel函数高级应用 一 excel高级函数入门介绍2 输入公式与插入函数相关知识 自动计算功能及公式的输入方法单元格引用 相对应用 绝对引用 混合引用函数向导 第二讲excel函数高级应用 一 excel高级函数入门介绍2 输入公式与插入函数自动计算功能及公式的输入方法 excel中公式以 开头 第二讲excel函数高级应用 一 excel高级函数入门介绍2 输入公式与插入函数单元格引用 相对引用 绝对引用 混合引用引用方式和引用实例使用 符号来锁定引用 相对引用 a1 b5绝对引用 a 5 c 2快捷键为f4混合引用 a2 d 1 第二讲excel函数高级应用 实例练习2 1 在第二讲练习表 xls中 对 2 1 表中数据进行计算 要求 计算进货数量和销货数量的小计值 计算出每种产品的存货数量和销货金额 统计表中产品的种类数 第二讲excel函数高级应用 实例练习2 2 在第二讲练习表 xls中 对 2 2 表中数据进行计算 要求 该表为一个对外投资收益表 按所给公式计算 填表 其中 对外投资比重 投资收益率 数据格式设置为百分比形式 且保留两位小数 将合计值添入 第二讲excel函数高级应用 实例练习2 3 在第二讲练习表 xls中 在 2 3 中构造一个次幂表 要求 例 b4单元格对应2 0的0次幂 c4对应2 0的1次幂 d4单元格对应的是2 0的平方值 d8单元格对应2 4平方值 e6对应2 2的立方值 以此类推 利用公式计算 结果保留一位小数 第二讲excel函数高级应用 实例练习2 4 在 2 1 表中 利用插入函数的方法插入打印日期 第二讲excel函数高级应用 一 excel高级函数入门介绍3 巧用excel帮助理解函数相关知识 f1键获取帮助office助手搜索方式打开 帮助 系统 第二讲excel函数高级应用 二 excel函数的应用1 日期时间函数2 逻辑函数3 查找与引用函数 第二讲excel函数高级应用 二 excel函数的应用1 日期与时间函数相关知识 day month now today year weekdayhour minute second time 第二讲excel函数高级应用 实例练习2 5 在2 5表中 插入星期的数据 公式为 today 第二讲excel函数高级应用 实例练习2 6 在2 5表中 计算时间总计数据 编辑栏中输入 60 确定 第二讲excel函数高级应用 确定 此例最简单的方法是 1 单击d4单元格 2 在编辑栏中输入公式 hour c4 b4 60 minute c4 b4 3 将d4单元格的数字格式设置为 常规 第二讲excel函数高级应用 确定 第二讲excel函数高级应用 二 excel函数的应用2 逻辑函数相关知识 逻辑判断if函数countif函数sumif函数 第二讲excel函数高级应用 二 excel函数的应用2 逻辑函数if函数if函数有三个参数 if 关系或逻辑表达式 值1 值2 当表达式为真时 结果为值1 当表达式为假时 结果为值2 第二讲excel函数高级应用 实例练习2 7 在2 7 xls中 判断月指标完成情况 要求 当实产量的和大于计划产量和时 标明 已完成 当实产量的和小于计划产量和时 标明 未完成 第二讲excel函数高级应用 二 excel函数的应用2 逻辑求和函数sumif函数计算符合条件的单元格的求和语法 sumif 第二讲excel函数高级应用 实例练习2 8 在第二讲练习表 xls的2 8表中 计算男女生的奖学金总计数值 range 作为条件判断的单元格区域criteria 输入条件数据sum range 要计算合计的单元格范围 编辑栏公式为 第二讲excel函数高级应用 二 excel函数的应用 2 条件统计函数countif函数用于单个条件的统计countif 条件范围 统计条件 功能 对区域中满足单个指定条件的单元格进行计数 说明条件范围只能是单元格引用 统计条件可以使用引用 字符串表达式或数组 同时支持 和 的统计 第二讲excel函数高级应用 实例练习2 9 在 第二讲练习表 xls 的2 9countif练习表中 按要求进行统计 公式结果为 第二讲excel函数高级应用 问题 countif函数能够进行两个条件的统计吗 例 如何统计c列数据中大于等于5而且小于10的数值个数 输入 countif c c 5 countif c c 10 第二讲excel函数高级应用 二 excel函数的应用3 excel查询与引用函数相关知识 lookup hlookup vlookupindexoffset 第二讲excel函数高级应用 二 excel函数的应用3 excel查询与引用函数查找函数 lookup函数lookup 查找值 查找范围 目标范围 通常情况下查找范围的首列或首行要求升序排列 第二讲excel函数高级应用 二 excel函数的应用3 excel查询与引用函数查找函数 vlookup函数vlookup 查找值 数据表 列序号 匹配条件 匹配条件 0 表示 精确查找 且支持无序查找 1 表示 近似查找且首列升序排列进行查找 第二讲练习表 xls vlookup和lookup函数表 第二讲excel函数高级应用 实例练习2 10 为 第二讲练习表 xls 中2 10表制作一个简单的成绩查询器 要求 在查询器的考号栏内输入职工考号 该职工的姓名 职称 成绩就能够自动显示出来 原表 在 a19 d20 构造查询表 第二讲excel函数高级应用 方法1 利用数组形式 b20输入 c20输入 d20输入 方法2 利用向量形式 b20输入 c20输入 d20输入 第二讲excel函数高级应用 二 excel函数的应用3 excel查询与引用函数查找函数 hlookup函数hlookup 查找值 查找范围 行序号 通常用于水平方向查询 第二讲excel函数高级应用 实例练习2 11 为 第二讲练习表 xls 的2 11表按其下方的 评语对照表 为 评语 列填入评语 原表 e3输入 第二讲excel函数高级应用 二 excel函数的应用3 excel查询与引用函数引用函数 index函数功能 返回数组或单元格区域中指定单元格或单元格数组的数值 如引用的区域互不相连 需用括号括起来 array数组reference单元格区域 第二讲excel函数高级应用 实例练习2 12 第二讲练习表 xls 的2 12表 要求 在f3中显示数据表第四行第二列的内容 即 李四 第二讲excel函数高级应用 二 excel函数的应用3 excel查询与引用函数choose语法 choose index num value1 value2 其中 index num为待选参数的序号 取1 29的数字如取1 则choose返回value1 取2 则choose返回value2 第二讲excel函数高级应用 实例练习2 13 为 第二讲练习表 xls 的2 13表的 是否合格 列填入数据 在e3单元格输入 第二讲excel函数高级应用 二 excel函数的应用3 excel查询与引用函数引用函数 offset函数将源引用根据制定的行 列偏移来产生新的单元格引用offset 基点 行数 列数 高度 宽度 函数特征 高度 宽度 用于制定目标的单元格区域大小 高度 宽度 允许用户使用负数作为参数 第二讲excel函数高级应用 公式 offset a5 2 2 5 3 c 7 e 11 下图展示将a5区域进行偏移后产生的新引用c7 e11公式 offset a5 2 2 5 3 c 7 e 11 5行 3列 offset的最终结果 第二讲练习表 xls offset函数练习 第二讲excel函数高级应用 二 excel函数的应用3 excel查询与引用函数row函数被用来返回给定引用的行号row 参数 函数特征参数为希望得到行号的单元格或者单元格区域 第二讲excel函数高级应用 二 excel函数的应用3 excel查询与引用函数column函数被用来返回给定引用的列标column 参数 函数特征参数为希望得到列标的单元格或者单元格区域 第二讲excel函数高级应用 二 excel函数的应用3 excel查询与引用函数roundup函数向上舍入数字 远离零值roundup 数值 小数位数 函数特征将数值按给定的小数位数向上舍入如roundup 3 2 0 的结果为4 第二讲excel函数高级应用 函数综合练习 生成工资条主要利用引用函数借助row函数产生循环序列的方式来实现 效果如下图 第二讲练习表 xls 生成工资条 第二讲excel函数高级应用 excel图表基础excel动态图表 第三讲excel图表高级应用 一 excel图表基础建立和编辑图表相关知识 嵌入式和独立图表图表包括图表区和绘图区图表向导使用 第三讲excel图表高级应用 实例练习3 1 利用 第三讲练习表 xls 中3 1数据制作三维饼图 要求 为 去年销售额 制作三维饼图 并添加类别名称数据标志 选择数据区域 a2 f3 第三讲excel图表高级应用 单击 图表向导 按钮 第三讲excel图表高级应用 效果图 右击图表的某个对象区 可以进行细致编辑如下图右击图表区域快捷菜单 第三讲excel图表高级应用 实例练习3 2 为 第三讲练习表 xls 中3 2表中的已有图表添加新数据列 要求 为 去年销售额 图表添加 今年预测销售量 数据行 使用 源数据 对话框添加数据 在图表区内右击 源数据 系列 单击 添加 按钮 第三讲excel图表高级应用 在 名称 文本框中选择a5单元格 即 今年预测销售量 在 值 文本框中选择b5 f5单元格区域 单击 确定 按钮 第三讲excel图表高级应用 二 excel动态图表一般的图表是静态图表 如何动态地显示一行或一列数据 1 自动筛选方法2 利用组合框选项实现 第三讲excel图表高级应用 实例练习3 3 在 第三讲练习表 xls 的3 3表以 北分区店 列为类别制作动态柱形图 选择a2 a9单元格区域 数据 筛选 自动筛选 第三讲excel图表高级应用 选择a2 g9单元格区域 单击图表向导按钮 完成 按钮 第三讲excel图表高级应用 在自动筛选列选择某个项 图表数据动态改变 第三讲excel图表高级应用 实例练习3 4 在 第三讲练习表 xls 的3 4表制作动态变化的饼图 右击excel工具栏任意处 选择 窗体 命令 在 窗体 工具栏中选择 组合框 在图中位置拖动得到一个组合框 第三讲excel图表高级应用 右击组合框 设置控件格式 确定数据源区域 a 3 a 9 单元格链接框 a 15 单击 确定 效果图 第三讲excel图表高级应用 将b2 g2的数据标签复制到b17 g17 如图 在a18单元格输入 index a3 a9 a 15 回车确认 效果如下 第三讲excel图表高级应用 将公式通过拖动复制到b18 g18 选择a17 g18区域 单击 图表向导 按钮 在图表向导对话框中 选择 饼图 单击 完成 按钮 第三讲excel图表高级应用 通过组合框选择不同店名 可动态显示该店的饼图 第三讲excel图表高级应用 小结 图表向导自动筛选制作动态图表组合框控制图表数据 第三讲excel图表高级应用 数据透视 数据透视表用来分析 汇整大量数据的互动数据分析工具 包括 分页字段 数据项 行字段 列字段 项目等元素 数据项 列字段 页字段 行字段 第四讲数据透视表和数据透视图 数据透视表的建立数据透视表的编辑数据透视图的建立与查看数据透视表应用实例 第四讲数据透视表和数据透视图 一 数据透视表的建立以 4 1 xls 为例 操作方法如下 1 鼠标定位在数据表中 数据 数据透视表及数据透视图 第四讲数据透视表和数据透视图 2 3 4 第四讲数据透视表和数据透视图 5 出现 数据透视表字段列表任务 窗格后 分别拖动字段名称到工作表的列 行 数据项位置 如图 通过此表 我们可以知道每个销售人员销售产品的日期 及当天的销售金额 页字段 类别 行字段 销售日期 列字段 销售人员 第四讲数据透视表和数据透视图 删除数据透视表的方法 利用数据透视表工具栏的 数据透视表 按钮 选定 整张表格 再利用 编辑 清除 全部 第四讲数据透视表和数据透视图 二 数据透视表的编辑1 将项目组成组合 以 4 2 xls 为例 操作方法如下 1 选择a5 a8 右击 组及显示明细数据 组合 选择要组合的项目 第四讲数据透视表和数据透视图 2 在 数据组1 中输入组合的名称 2003年11月 在 销售日期 处输入字段的名称 月份 第四讲数据透视表和数据透视图 3 重复 1 2 步 分别将同一月份的日期设定成组合 并依序命名 设定为组合的数据 按相同的步骤 选择 取消组合 命令 即可显示所有的个别项目了 第四讲数据透视表和数据透视图 二 数据透视表的编辑2 新建和删除字段 以 4 3 xls 为例 操作方法如下 1 选择要新建的字段名称 拖动到数据透视表 新添加的字段 第四讲数据透视表和数据透视图 二 数据透视表的编辑2 新建和删除字段 以 4 3 xls 为例 操作方法如下 2 删除 第四讲数据透视表和数据透视图 二 数据透视表的编辑3 显示和隐藏明细数据 以 4 4 xls 为例 操作方法如下 1 右击要显示明细数据的字段 组合及显示明细数据 显示明细数据 第四讲数据透视表和数据透视图 2 出现 显示明细数据 窗口 在明细数据的字段列表中选择要显示的字段名 确定 第四讲数据透视表和数据透视图 隐藏明细数据的操作 1 右击要隐藏明细数据的字段 组合及显示明细数据 隐藏明细数据 选择要隐藏明细的字段 第四讲数据透视表和数据透视图 二 数据透视表的编辑4 分页查看数据透视表 以 4 6 xls 为例 操作方法如下 1 单击页字段上类别字段右边的按钮 选择要查看的类别名称单击 确定 第四讲数据透视表和数据透视图 2 如下为 化妆品 类别的数据显示在数据透视表中 第四讲数据透视表和数据透视图 二 数据透视表的编辑5 显示多个页面的数据透视表 以 4 6 xls 为例 操作方法如下 1 单击数据透视表工具栏的按钮 选择 分页显示 第四讲数据透视表和数据透视图 2 在 分页显示 窗口中 选择要分页显示的字段名称 单击 确定 第四讲数据透视表和数据透视图 二 数据透视表的编辑6 改变数据汇总的方式 以 4 7 xls 为例 求出每位销售人员每月平均销售的金额 操作方法如下 1 在 求和项 金额 字段上 右击 字段设置 第四讲数据透视表和数据透视图 2 在 数据透视表字段 窗口中 选择汇总方式为 平均值 单击 确定 设置数字格式 第四讲数据透视表和数据透视图 二 数据透视表的编辑7 自定义公式 以 4 8 xls 为例 按销售人员的销售金额多少 设定 奖金百分比 的值 金额大于5000的设为10 否则为5 操作方法如下 1 单击按钮 公式 计算字段 第四讲数据透视表和数据透视图 2 在 插入计算字段 窗口中 输入名称 奖金百分比 输入公式 if 金额 5000 10 5 单击 确定 结果 第四讲数据透视表和数据透视图 3 设置 奖金百分比 字段数字格式 在 奖金百分比 项上右击 字段设置 命令 第四讲数据透视表和数据透视图 4 最后效果 第四讲数据透视表和数据