OFFICE应用交流.ppt

2020 2 25 1 MicrosoftOffice应用交流之EXCEL 东风汽车公司李建华2008年6月 2020 2 25 2 概述 2020 2 25 3 办公软件的功能 MicrosoftOffice办公软件是微软公司 Microsoft 开发的一套完整的 功能强大的软件 Access 数据库软件 如客户管理 Excel 电子表格 用于数据处理 Outlook 电子邮件的收发与管理 PowerPoint 演示文稿 Word 文字处理 用于编写报告 简历 传真等 PictureManager 图片管理软件 用于图片裁剪 色彩调整 亮度和对比度调整 2020 2 25 4 常用的几个办公软件 常用的办公软件有 2020 2 25 5 MicrosoftOfficeExcel的应用 以Excel2003为例 2020 2 25 6 目录 术语工作簿 工作表与单元格行标 列标与单元格地址公式编辑栏与填充柄单元格格式设置单元格数据类型条件格式公式输入与函数的一般格式常用函数和SUM 平均值AVERAGE 最大值MAX 和最小值MIN 计数COUNT COUNTA 和条件计数COUNTIF 排序RANK 条件函数IF 日期与日期函数TODAY NOW 函数输入与帮助函数的种类函数的种类复制公式单元格地址的相对引用与绝对引用选择性粘贴排序与筛选常用图表柱状图折线图饼图XY散点图其它条件格式单变量求解工作表保护每页都打印标头冻结窗格在单元格中换行在多个单元格中输入相同数据 2020 2 25 7 术语 工作簿 工作表与单元格 一个EXCEL文件就是一个 工作簿 一个工作簿可以有多个 工作表 工作表由 单元格 组成 工作簿 就像一个 账簿 这个账簿里的每一页就是一个 工作表 工作表标签 工作表1 工作表2 工作表2 单元格 2020 2 25 8 术语 行标 列标与单元格地址 行标 1 2 3 4 65536 最多65536 即216 行列标 A B C Z AA AB AC AZ BA BB BC IV 最多256 即28 列单元格地址 列标 行标 列标在前 行标 列标 选定的单元格 在这里显示选定单元格的 地址 2020 2 25 9 术语 公式编辑栏与填充柄 公式编辑栏 填充柄 2020 2 25 10 单元格的格式 选中要设格式的单元格并右击鼠标 设置单元格格式 单元格的数据类型 常规 自动识别 为默认类型数值货币 输入身份证号码或电话号码时 若单元格为默认数据类型 常规 或 数值 类型 计算机会按数值处理 往往得不到理想的效果 输入时 在号码前面加一个单引号 计算机则会把输入的数字按 文本 处理 当然 可以事先把单元格格式设置为 文本 类型 2020 2 25 11 公式输入与函数的一般格式 1 输入公式必须以 开始 函数的一般格式 函数名 参数或数据列表 如sum A1 C5 由冒号 和逗号 表示数据列表 单元格1 单元格2 表示以单元格1和单元格2为对角线的矩形范围的所有单元格 如D2 F5 表示D2 D3 D4 D5 E2 E3 E4 E5 F2 F3 F4和F5 共12个单元格 单元格1 单元格2 单元格3 表示单元格1 单元格2和单元格3 共3个单元格 D2 F5 2020 2 25 12 公式输入与函数的一般格式 2 冒号 和逗号 可混合使用 数据列表中 可以包含 单元格函数常数 例 SUM A1 F4 5 6 AVERAGE C3 C5 H5 2020 2 25 13 关于函数 Excel中所提的函数其实是一些预定义的公式 就类型来说 参数可以是数字 文本 形如TRUE或FALSE的逻辑值 数组 单元格引用 给定的参数必须能产生有效的值 参数也可以是常量 公式或其它函数 还可以是数组 单元格引用等 单元格引用 用于表示单元格在工作表所处位置的坐标值 例如 显示在第B列和第3行交叉处的单元格 其引用形式为 B3 常量 常量是直接键入到单元格或公式中的数字或文本值 或由名称所代表的数字或文本值 例如 日期10 9 96 数字210和文本 QuarterlyEarnings 都是常量 公式或由公式得出的数值都不是常量 函数的参数可以是函数 说明一个函数可以是另一个函数的参数 这就是嵌套函数的含义 函数的一般格式 函数名 参数或数据列表 例 SUM A1 F4 5 6 AVERAGE C3 C5 H5 2020 2 25 14 常用函数 求和 求和函数 SUM 数据列表 自动求和的快捷钮 例1 2020 2 25 15 常用函数 平均值 最大值和最小值 平均值AVERAGE 数据列表 求指定范围内单元格的算术平均值最大值MAX 数据列表 求指定范围内单元格的最大值最小值MIN 数据列表 求指定范围内单元格的最小值 例1 2020 2 25 16 常用函数 计数值 计数COUNT 数据列表 计算包含数值的单元格和参数列表中的数值个数计数COUNTA 数据列表 计算参数列表所包含的数值个数和非空单元格的数目条件计数COUNTIF 范围 条件 计算某个区域中满足给定条件的单元格数目 条件 为确定哪些单元格将被计算在内的条件 其形式可以为数字 表达式或文本 例如 条件可以表示为32 32 32 或 apples 例 COUNTIF B2 B5 55 表示计算B2到B5范围内值大于55的单元格个数 例1 2020 2 25 17 常用函数 排位 排位函数RANK 数值 数据范围 排序方式 数值 要排位的数值 可以直接是个数值 也可以是一个单元格地址 取单元格里的数 数据范围 排位的基准 排序方式 0或省略 按降序进行排位 不为零 按升序进行排位 举例 RANK I3 I3 I18 0 得到I3单元格在I3到I18 共18个 中排位 而且是按降序排位 最大为第一 J3单元格输入公式 RANK I3 I3 I18 0 表示I3单元格的数473 在I3到I18 473 484 480 共18数 中 按降序的排位数 计算结果为11 说明473在这一组数中排第11位 同理 J15单元格应输入公式 RANK I15 I3 I18 0 注意 排位基准I3 I18不能变 例1 2020 2 25 18 常用函数 条件函数 1 它的语法为IF 条件 条件为真时的值 条件为假时的值 对于 条件 而言 可以是任意值或表达式 数值或表达式的计算结果不等于0为 真 等于0为 假 SUM C2 C8 则以C2到C8的和为条件 和不等于0为 真 等于0为 假 C2 B2 则以C2是否大于B2为条件 C2 B2本身就是一个表达式 若C2大于B2的关系成立 则表达式的结果为 1 1 0 即为 真 若C2大于B2的关系不成立 则表达式的结果为 0 即为 假 这样的符号还有 等于 不等于 大于 小于 小于等于 不大于 2 OR 0 NOT 9 8 10 SUM 2 3 9 15 1 大家有兴趣的话 可以计算一下结果 条件函数用处很大 只要能掌握条件的表达式的编写 用起来就很自如了 2020 2 25 19 常用函数 条件函数 2 条件函数可以套用 如IF A2 90 优秀 IF A2 80 良好 IF A2 60 及格 不及格 翻译 一下就是 A2 90吗 是的 即为 优秀 否则 再进一步看 A2 80吗 是的 即为 良好 否则 再进一步看 A2 60吗 是的 即为 及格 否则 为 不及格 用流程图表示IF A2 90 优秀 IF A2 80 良好 IF A2 60 及格 不及格 A2 80 A2 60 A2 90 结果 优秀 结果 良好 结果 及格 结果 不及格 是 否 否 否 是 是 例2 A2 90 结果 良好 否 是 2020 2 25 20 常用函数 条件函数 3 练习2 求个人所得税 应税收入A 税前收入 1600A 0 500 税率5 税 A 5 A 500 1500 税率10 税 A 10 25A 1500 4000 税率15 税 A 15 100A 4000 20000 税率20 税 A 20 300A 20000 税率25 税 A 25 1300学生成绩分类成绩 90 优秀成绩 80 0 两个不相等的实根 0 两个相等的实根 0 两个不相等的虚根 仅为练习 数据可能与税法不同 例2 2020 2 25 21 常用函数 日期与日期函数 TODAY 得到今天的日期 放日期的单元格的数字类型要注意 格式 单元格 数字 分类中选 日期 若单元格使用了 数值 类型 日期则显示一个整数 这个数等于基准日期到 今天 的总天数NOW 得到当前时间 和日期 单元格的数字类型要注意 格式 单元格 数字 分类中选 时间 若单元格使用了 数值 类型 则显示一个小数 这个小数的整数部分等于基准日期到 今天 的总天数 小数部分为时间转换为 日 的小数 见下表实例 日期序列数基准有两个 1900年日期系统 1900年1月1日为1 但1900年2月误为29天 1904年日期系统 1904年1月1日为0 更改日期序列数基准 工具 选项 重新计算 常用函数 日期与日期函数 练习02 5 2020 2 25 22 函数的输入与帮助 Excel的函数相当多 我们仅介绍几种比较常用的函数使用方法 其他更多的函数您可以从Excel的帮助功能中了解更详细的资讯 选定单元格输入等号 单击下拉箭头 在常用函数中选 若没有 单击其他函数 其他函数对话框 选函数类型 选函数 该函数的简单说明 单击可以查看该函数的详细帮助 2020 2 25 23 复制公式 单元格地址的相对引用与绝对引用 单元格的地址有相对地址和绝对地址两类 用 表示绝对地址 可以用 F4 功能键切换 在Excel中 计算时经常要用到单元格地址 尤其是地址的相对引用和绝对引用 技巧性较强 也容易出错 请多实践 相对地址和绝对地址有如下几种情况 行和列均相对引用 如D9行和列均绝对引用 如 D 9行绝对和列相对引用 如D 9行相对和列绝对引用 如 D9 2020 2 25 24 复制公式 单元格地址的相对引用与绝对引用 G5单元格用公式自动计算 在公式中相对引用了F5 绝对引用了G1 G 1 对于相对引用的F5 计算机只是记住F5相对与G5的相对位置 同一行 前一列 把G5的公式复制到别处 公式中的F5就变了 但相对关系不变 如把G5的公式复制到N10 原公式中的F5就变成M10 与N10在同一行 即第10行 在N10前一列 即M列 保持相同的相对关系 对于绝对引用的G1 无论把G5的公式复制到什么地方 公式中的G 始终不变 对于混合引用 道理是相同的 每一种商品用同样的折扣率 类似这样的 常数 我们要绝对引用 在G5单元格送入公式 F5 1 A 1 把公式复制到G6 公式自动变成 F6 1 A 1 例3 2020 2 25 25 复制公式 选择性粘贴 只粘贴公式 只粘贴数值 只粘贴 在粘贴时 可以进行计算 如要给A1 C5共15个单元格的数据都乘以0 89 操作如下 在一个空单元格中键入0 89 并复制该单元格 选中A1 C5 并右击 选 选择性粘贴 选中 乘 选择性粘贴时 选转置 则粘贴是行变成列 列变成行 2020 2 25 26 排序 选定要排序的区域 数据 菜单 排序 2020 2 25 27 排序 按递增方式排序的数据类型及其数据的顺序为 数字 顺序是从小数到大数 从负数到正数 文字和包含数字的文字 其顺序是 0123456789 空格 ABCDEFGHIJKLMNOPQRSTUVWXYZ逻辑值 False在True之前 错误值 所有的错误值都是相等的 空白单元格 不是空格 单元格总是排在最后 递减排序的顺序与递增顺序恰好相反 但空白单元格将排在最后 日期 时间和汉字也当文字处理 是根据它们内部表示的基础值排序 2020 2 25 28 筛选 筛选是个很有用的功能 进入方式 选中标题行 数据 菜单 筛选 2020 2 25 29 常用图表 选定绘图的 源数据 点击 图表向导 钮 选定图表类型 点击 下一步 例5 2020 2 25 30 图表上插入 趋势线 例5 右击要加 趋势线 的曲线 添加趋势线 根据曲线的走势 选定趋势线的 类型 打开 选项 选择是否显示公式 R值等 确定 R值是趋势线与源曲线的相关系数 R在0 1之间 R 1相关性最好 即趋势线与源曲线重合 2020 2 25 31 条件格式 选定要设置条件格式的单元格 或区域 格式 条件格式 D 设置条件格式 设置条件 单击 格式 设置条件满足时的格式 条件格式1 条件格式2 单击 添加 可以增加条件格式3 例6 2020 2 25 32 条件格式 利用单元格数值 条件格式1 当 单元格数值 大于等于 100 时 按 黑框 黄底 红字 显示 条件格式2 当 单元格数值 介于 90 和 100 之间时 按 黄框 灰底 蓝字 显示 例6 2020 2 25 33 条件格式 利用单元格数值 未设条件格式 已设条件格式 例6 2020 2 25 34 条件格式 利用公式 举例 如图所示 在同一门功课中 如果有相同分数 出现重复数据 就用红色字 黄色底显示 此练习可用于选号中 若后来的人选的号码 输入单元格 与前面的人选定的号码重复 电脑会立即突出显示 例7 2020 2 25 35 条件格式 利用公式 举例 总思路 先设定D3单元格的 条件格式 选定D3 右击 复制 选定D3 H18 选择性粘贴 格式 确定 设置D3单元格的 条件格式 选中D3 格式 条件格式 在条件1下选择 公式 在后面的公式栏输入 COUNTIF D D D3 1 此公式的含义是 在整个D列中 数值等于D3的单元格个数 1 大于1表示有重复的数 单击 格式 设置格式 确定 2020 2 25 36 单变量求解 若已知y f x 给一个x就可以方便地求出y 但是 给定一个Y 求X就复杂了 如y x3 ln x 298sin x x 0 给出x就能求出y 现在要问x为何值时 y等于1000 用单变量求解就可以解决了 例8 2020 2 25 37 单变量求解 工具 单变量求解 设置 目标单元格 目标值 和 可变单元格 确定 注意 目标单元格 Y 和可变单元格 X 之间要有函数关系 本例中目标单元格在C列 可变单元格在B列 选择时必须在同一行 如C6对应B6 C8对应B8等等 例8 2020 2 25 38 工作表保护 为什么要保护工作表防止输入错误保护 知识产权 操作步骤单元格格式设定 选定单元格 右击 设置单元格格式 保护 中设定是否 锁定 和是否 隐藏 锁定 该工作表在保护