[计算机软件及应用]第6章 电子表格软件.ppt

第6章电子表格软件 掌握工作簿 工作表 单元格的概念与基本操作掌握工作表的编辑掌握数据图表化掌握数据清单的建立与编辑掌握数据排序 数据筛选 数据分类汇总 能力考查要求 学习目标 熟练掌握电子表格的建立 保存 编辑 格式化等操作熟练掌握电子表格的图表化操作熟练掌握数据列表的数据管理操作 工作簿 工作表 单元格的基本操作数据的录入 复制 移动 公式和函数的使用工作表的格式化 设置字符格式 数字格式 对齐方式 边框和底纹 格式工具栏的使用 复制格式和自动套用格式设置打印格式 打印预览 打印工作表创建图表 嵌入图表 创建图表工作表 编辑图表与格式化数据清单的建立与编辑数据排序 数据筛选 数据分类汇总操作 操作考查点 6 1Excel2003基本知识6 2Excel基本操作6 3数据输入6 4公式与函数6 5工作表的格式化6 6数据的图表化6 7数据管理与分析6 8页面设置与打印 第6章电子表格软件 6 1Excel2003基本知识 目前 世界上较为流行的电子表格软件有Microsoft公司的Excel和IBM公司的Lotus1 2 3等 Lotus1 2 3是电子表格软件的先驱 现在仍有一定的市场 Excel2003是Office2003软件包中的应用程序之一 它是一个功能强大的电子表格处理软件 不仅具有一般电子表格所包含的处理数据 绘制图表和图形的功能 还具有智能计算和数据库管理功能 内涵丰富的宏命令和函数 是集表格处理 图形图表处理以及数据分析于一体的集成软件 并可以通过Web页来发布和共享数据 Excel2003还具有良好的兼容性 可以与许多流行软件共享数据 因此它被广泛应用于财务 统计 审计 金融 分析以及办公事务的日常管理等诸多方面 Excel的工作窗口 工作簿 Book Excel中用来存储并处理工作数据的文件 以 xls扩展名保存 Excel窗口含有标题栏 菜单栏 工具栏 Excel术语 行号和列号行号用数字表示 从1 65536 列号用英文大写字母表示 由A B Z AA AB AZ IA IB IV组成 共256列 单元格单元格是Excel工作表的基本元素 也是Excel2003独立操作的最小单位单元格是根据其位置来命名的 如E5单元格就是第E列和第5行相交处的单元格 单元格区域是一组被选中的单元格 对单元格区域的操作就是对该区域内的所有单元格的操作 可以给单元格区域起个名字 即单元格区域的命名 它显示在地址栏中 工作表工作表是Excel完成一个完整作业的基本单位 工作表通过工作表标签来标识 用户可以通过单击工作表标签使之成为当前活动工作表 工作簿工作簿是Excel存储在磁盘上的最小独立单位 工作簿默认含有3张工作表 最多可以包含255张工作表 它所包含的工作表以标签的形式排列在状态栏的上方 6 2Excel基本操作 6 2 1Excel的启动和退出6 2 2工作簿基本操作6 2 3工作表基本操作6 2 4单元格基本操作6 2 5行和列基本操作 6 2 1Excel的启动和退出 1 Excel的启动 1 单击 开始 菜单 所有程序 MicrosoftOffice MicrosoftOfficeExcel2003 便可启动Excel 2 如果桌面上生成有Excel的快捷方式 则可以双击该快捷方式的图标以启动Excel 3 打开资源管理器 双击任意一个以 xls 为扩展名的Excel文件 可以在启动Excel的同时打开该Excel文件 2 Excel的退出退出Excel的方式主要有以下几种 1 单击窗口右上角的 关闭 按钮 2 单击 文件 菜单 退出 3 使用组合键Alt F4 6 2 2工作簿基本操作 用Excel软件建立的文件称为 工作薄文件 工作薄文件扩展名为xls一个工作薄文件默认含三张工作表工作簿基本操作 新建工作簿保存工作簿打开工作簿 新建工作簿右击桌面空白处 在快捷式样菜单中点击 新建 MiicrosoftExcel工作表 给桌面出现的Excel文件图标重命名后 双击该图标点击菜单 文件 新建 打开 新建工作簿 任务窗格 选择 空白工作簿 选项 生成名为 book1 工作簿文件保存工作簿点击菜单 文件 保存 点击菜单 文件 另存为 在文本框中输入文件名 再点击 保存 按钮即可打开工作簿点击菜单 文件 打开 打开 新建工作簿 任务窗格 在 打开 对话框中 点击 我的最近文档 从中找到要打开的文件 6 2 3工作表的基本操作 工作表是由若干行和列组成的表格工作表列号用字母来区分 A IV Z列之后是AA列 AZ列之后是BA 以此类推 工作表行号用数字来区分 1 65536行和列将工作表切割成许多单元格每张工作表可有256 65536单元格工作表的基本操作插入工作表删除工作表重命名工作表移动或复制工作表隐藏或显示工作表设置工作表标签颜色 插入工作表点击菜单 插入 工作表 即在当前工作表左侧插入一张新工作表右击 工作表标签 在弹出的快捷菜单中选择 插入 即在光标所指工作表左侧插入一张新工作表删除工作表选择好要删除的工作表 点击菜单 编辑 删除工作表 右击 工作表标签 在弹出的快捷菜单中选择 删除 即将光标所指工作表删除重命名工作表右击 工作表标签 在弹出的快捷菜单中选择 重命名 即可将光标所指工作表重新命名 移动或复制工作表右击 工作表标签 要移动的工作表 在弹出的快捷菜单中选择 移动或复制工作表 在弹出的 移动或复制工作表 对话框中选择好要移动的工作薄和工作表 点击确定 确定 按钮在 工作表标签 中选好要移动的工作表 点击菜单 编辑 移动或复制工作表 在弹出的 移动或复制工作表 对话框中选择好要移动的工作薄和工作表 点击确定 确定 按钮在 移动或复制工作表 对话框中将 建立副本 复选框找勾 则在移动工作表的同时 在原位复制一张原工作表副本隐藏或显示工作表在 工作表标签 中选好要隐藏的工作表 点击菜单 格式 工作表 隐藏 即可点击菜单 格式 工作表 取消隐藏 在弹出的 取消隐藏 对话框中选好要取消隐藏的工作表 点击 确定 按钮即可设置工作表标签颜色右击 工作表标签 在弹出的快捷菜单中选择 工作表标签颜色 在弹出的对话框中选好颜色 点击 确定 按钮 则光标所指工作表标签颜色被改在 工作表标签 中选好工作表 点击菜单 格式 工作表 工作表标签颜色 在弹出的对话框中选好颜色 点击 确定 按钮 则光标所指工作表标签颜色被改 6 2 4单元格基本操作 1 单元格选取用鼠标选取 单个单元格 单击所需的单元格连续多个单元格 单击首单元格 Shift 单击末单元格多个不连续单元格 Ctrl 单击单元格整行或整列 单击行头或列头全部单元格 单击行头 列头相交的全选按钮用名称框选取 在名称框输入要选取的单元格的行列号 名字框 单元格的插入在工作表中 右击要插入单元格的位置 在弹出快捷菜单中点击 插入 在弹出 插入 对话框选择好插入方式 点击 确定 按钮单元格的删除在工作表中 右击要删除单元格的位置 在弹出快捷菜单中点击 删除 在弹出 删除 对话框选择好删除方式 点击 确定 按钮 2 单元格的插入 删除 移动单元格在工作表中 右击要移动单元格的位置 在弹出快捷菜单中点击 剪切 或按Ctrl X键右击新位置 在弹出快捷菜单中点击 粘贴 或按Ctrl V键 或点击 选择性粘贴 在弹出的 选择性粘贴 对话框中选择恰当选项在工作表中 选好要移动的单元格 当光标变为十字交叉的箭头时 按下鼠标左键不放 并拖动鼠标到目标位置放手 3 移动 复制单元格 复制单元格在工作表中 右击要复制单元格的位置 在弹出快捷菜单中点击 复制 或按Ctrl C键右击新位置 在弹出快捷菜单中点击 粘贴 或按Ctrl V键 或点击 选择性粘贴 在弹出的 选择性粘贴 对话框中选择恰当选项在工作表中 选好要移动的单元格 当光标变为十字交叉的箭头时 按下鼠标左键和Ctrl不放 并拖动鼠标到目标位置放手 4 合并单元格 选定要合并的连续多个单元格点击菜单 格式 单元格 或右击弹出快捷菜单 点击 设置单元格格式 在打开的 单元格格式 对话框中选择 对齐 选项卡将 合并单元格 复选框打勾 6 2 5行和列基本操作 1 行 或列 的选取 单击行选按钮 或列选按钮 即选中该行 或列 全部2 行 或列 的插入 选定要插入列右侧一列点击菜单 插入 列 或右击 在弹出快捷菜单中点击 插入 命令 单击 格式刷 在弹出菜单中选定合适格式行的插入操作与列的插入操作类似 不再赘述 3 行 或列 的删除 选定要删除的列点击菜单 插入 列 或右击 在弹出快捷菜单中点击 插入 命令 或点击菜单 编辑 删除 在 删除 对话框中选定 整列 单选按钮行的删除操作与列的删除操作类似 不再赘述说明 在工作表中插入或删除行或列 工作表的行数 65536 及列数 256 都保持不变 仅是删除或添加上最后一行或列而已 4 行 或列 的隐藏 选定要隐藏的列选按钮点击菜单 格式 列 隐藏 或右击 在弹出菜单中点击 隐藏 该列即被隐藏行的隐藏操作与列的隐藏操作类似 不再赘述5 行 或列 的显示 选定跨跃被隐藏列的两列或多列 点击菜单 格式 列 取消隐藏 或右击 在弹出菜单中点击 取消隐藏 该列即被取消隐藏行的取消隐藏操作与列的取消隐藏操作类似 不再赘述 6 列宽 行高 的调整 若数据长度超过单元格宽度 则数据将显示为 这里需调整单元格列宽方式一 将鼠标移到要调整的列首单元格边缘 当光标变为横箭十字时 拖动鼠标 即可改变列宽将鼠标移到要调整的行首单元格边缘 当光标变为竖箭十字时 拖动鼠标 即可改变行高方式二 选定要调整的单元格点击菜单 格式 列 列宽 或 格式 行 行高 在对话框中输入列宽 行高 数据 按 确定 按钮 6 3数据输入 6 3 1基本类型数据输入6 3 2输入数据技巧6 3 3数据填充6 3 4数据记录单输入 6 3数据输入 6 3 1基本类型数据输入 选定要输入数据的单元格 使其变为活动单元格 在单元格中输入数据 输入内容同时出现在活动单元格和编辑栏上 若出现差错 可在确认前按Backspace键删去最后字符 或单击按钮 放弃输入项 或按Esc键删除单元格中的内容 单击按钮 或按Enter键可把输入到编辑栏上的内容放到活动单元格 可直接将单元格光标移到下一个单元格 准备输入下一个输入项 单击单元格直接在单元格输入数据双击单元格在编辑框内输入数据 单元格数据的输入 输入文本文本是指键盘上可键入的任何符号 文本输入时向左对齐输入数值在默认状态下 所有数值在单元格中均右对齐输入分数方法 在输入分数之前先输入0 再输入分数 例 08 9单元格将显示分数 但编辑栏将显示小数输入负数方法 在输入数值之前先输入负号 或将数值用一对圆括号括起 例 100或 100 在单元格中输入数字时 应注意以下几点 数字前面的正号 被忽略 数字项中的单个 作为小数点处理 在负数前面冠以减号 或将其放在括号 内 可以使用小数点 可以在千位 百万位等处加 千位分隔符 逗号 输入带逗号的数字时 单元格中显示时带有逗号 编辑栏显示时不带逗号 例如 输入1 234 56 编辑栏中将显示1234 56 为避免将分数视作日期 分数前冠以0 零 如01 2表示二分之一 若数字项以百分号结束 该单元格将应用百分号格式 例如 在应用百分号的单元格中输入26 编辑栏中显示0 26 单元格中显示26 若数字项中用 且字符串不可能被理解为日期型数据 认为该项为分数 例如 223 4 编辑栏中显示22 75 单元格中显示223 4 输入日期在系统默认的情况下 日期按年 月 日或日 月 年 英文 的顺序输入 中间用连字符 或者 分割设定日期格式点击菜单 格式 单元格 或右击 在弹出菜单中选择 设置单元格式 在弹出 单元格格式 对话框中 选择 数字 选项卡 在 分类 列表框中选择 日期 在 类型 列表框中选择合适的日期显示格式 输入时间系统默认的时间是按24小时制输入的 如果按12小时制输入时间 应在时间数字后空一个 并键入字符A 上午 或者P 下午 如果要输入当前时间 可按组合键输入符号选定单元格点击菜单 插入 符号 在弹出的 符号 对话框中选择 符号 选项卡 在 字体 下拉框和 子集 下拉框中选择合适的符号集从中选定并插入所需符号或在弹出的 符号 对话框中选择 特殊符号 选项卡 从中选定并插入所需特殊符号 例 插入符号 的操作 输入自定义类型数据选定要输入数据的单元格区域点击菜单 格式 单元格 在打开的 单元格格式 对话框中选择 数字 选项卡再在 分类 下拉框中选择 自定义 在 类型 下拉框中选择数据类型格式 或在 类型 文本框中输入数据类型格式 点击 确定 按钮在选定单元格区域输入具体数据 6 3 2输入数据技巧 记忆式输入功能 在单元格A7输入 聂小倩 再在单元格A8输入 聂 Excel自动提示 小倩 并以反白显示 按回车 即在单元格A8输入 聂小倩 数据自动更正功能 点击菜单 工具 自动更正选项 在弹出 自动更正 对话框中的 自动更正 选项卡的替换文本区中分别输入 替换 和 替换为 的内容 完成后点击 确定 按钮在工作表单元格中输入 替换 数据 Excel自动将其替换为 替换为 的数据例 在 替换 框中输入 zy 在 替换为 输入 专业 则以后在要输入 专业 单元格中 只要输入 zy Excel就自动转换为 专业 6 3 3数据填充 如果输入的是一组相同数据或一组有规则数据 可用Excel提供的数据填充功能数据填充类型 1 相同数据填充2 日期格式数据填充3 序列数据填充4 自定义数据填充 1 相同数据填充 文本类型数据只能是复制填充方式一 在首单元格中输入数据将鼠标移到首单元格右下角当光标变为黑十字 填充柄 时 将鼠标沿着要填充的方向拖动被鼠标拖过的单元格即被自动填充上相同数据方式二 在首单元格中输入数据选定要填充的单元格区域点击菜单 编辑 填充 选定与所选区域相同的方向 2 日期格式数据填充 默认日期数据填充 在首单元格中输入日期值沿要填充的单元格区域拖动填充柄将按日期递增顺序 每单元格增一日 填充特定日期数据填充 在首单元格中输入指定日期选定要填充的单元格区域点击菜单 编辑 填充 序列 在打开的 序列 对话框中设置 序列产生在 行 列 只能向下 向右 类型 日期 日期单位 日 工作日 月 年 步长值 终止值 点击 确定 的按钮沿要填充的单元格区域拖动填充柄 3 序列数据填充 在首单元格中输入指定数据选定要填充的单元格区域点击菜单 编辑 填充 序列 在打开的 序列 对话框中的选择适当的 类型 单选按钮设置 步长值 和 终止值 沿要填充的单元格区域拖动填充柄 有规律的数据 例如等差 等比数列可自动填充 拖曳填充柄到结束 4 自定义数据填充 点击菜单 工具 选项 自定义序列 在打开的 选项 对话框的 自定义序列 列表框中 Excel提供了一些特殊序列可在右侧的 输入序列 中添加自定义序列 如 aa bb cc 只要在首单元格输入自定义序列首项 拉动填充柄 即可填充 系统提供的序列数据例 甲 乙 丙 星期一 星期二 方法 输入一个数据 利用自动填充柄拖曳到结束填充 6 3 4数据记录单输入 若工作表有字段 在窗口中无法全部显示出来 则输入数据时需不断拖动水平滚动条 十分不便可改用 数据记录单 方式输入数据点击菜单 数据 记录单 6 4公式与函数 公式是进行计算和分析的等式 可以对数据进行加 减 乘 除等运算 也可以对文本进行比较 函数是Excel预定义的内置公式 可以进行数学 文本 逻辑运算或查找工作表中的信息6 4 1公式的使用6 4 2公式中的引用6 4 3函数的使用 6 4 1公式的使用 Excel具有强大的计算和统计功能 将数据输入到工作表以后 可以用公式进行计算 可以通过加入加号 减号 乘号 除号 幂符号等运算符构成公式 公式是一个等式 是一组数据和运算符组成的序列 Excel公式可以包括数 运算符 单元引用和函数等 其中 单元引用可以得到其它单元格数据计算后得到的值 公式通常以符号 开始 也可用 开始 输入公式时 同样要先选择活动单元格 然后再输入 公式形式 操作数和运算符操作数 单元格 数字 字符 区域名 区域 函数算术运算符 字符运算符 关系运算符 公式的使用 公式的使用 1 公式中的运算符运算符一般有算术运算符 比较运算符 文本运算符和引用运算符 算术运算符算术运算符连接数字并产生计算结果 算术运算符包括 加 减 乘 除 幂 负号 百分号 等 例如 公式 30 2 20 先分别求30的平方 20 然后再两者相乘 公式的值是180 公式的使用 比较运算符比较两个数值的大小并返回逻辑值True 真 和False 假 比较运算符包括 等于 大于 小于 小于等于 例 若单元格A1数值小于25 公式 A1 25的逻辑值True 否则False 文字运算符文本运算符 将多个文本 字符串 连接成一个连续的字符串 组合文本 例 设单元格A1中的文字为 广州市 公式 广东省 A1的值为 广东省广州市 公式的使用 引用运算符可以将单元格区域合并运算 引用运算符包括冒号 逗号 和空格 其中 冒号 是区域运算符 可对两个引用之间 包括这两个引用在内 的所有单元格进行引用 例如 A1 H1是引用从A1到H1的所有单元格 逗号 是联合运算符 可将多个引用合并为一个引用 例如 SUM A1 H1 B2 F2 是将A1 H1和B2 F2两个单元格区域合并为一个 空格 是交叉运算符 可产生同时属于两个引用的单元格区域的引用 例如 SUM A1 H1B1 B4 只有B1同时属于两个引用A1 H1和B1 B4 公式的使用 2 运算符的运算顺序如果一个公式中含有多个运算符号 其执行的先后顺序为 冒号 逗号 空格 负号 百分号 幂 乘 除 加 减 比较 括号可以改变运算的先后顺序 3 公式编辑单元格中的公式可以进行修改 复制 移动等编辑操作 修改公式 在输入公式过程中发现有错误 可以选中公式所在的单元格 然后在编辑栏中进行修改 修改完毕 按Enter键 移动和复制公式 单元格的操作相同 但是 复制 移动公式有单元格地址的变化 对结果将产生影响 提示 Excel自动调整所有移动单元格的引用位置 使这些引用位置仍引用到新的同一单元格 若单元格移动到已被其它公式引用位置 由于原有单元格已经被移动过来的单元格代替 公式产生错误值 EFF 公式的使用 4 显示公式一般情况下 在单元格中不显示实际的公式 而是显示计算的结果 只有选择单元格为活动单元格 才可在编辑栏上看到活动单元格的公式 在单元格中显示公式的方法 在菜单栏上选择 工具 选项 选项 弹出 选项 对话框 在 视图 选项卡的 窗口选项 框中选择 公式 复选框 单击 确定 按钮 此时 在工作表的单元格中不再显示公式的计算结果 而是显示公式本身 提示 按Ctrl 键 在1键的左边 可以在 显示公式值 和 显示公式 两者之间切换 公式的使用 5 复杂公式的使用 公式的数值转换在公式中 每个运算符与特定类型的数据连接 如果运算符连接的数值与其所需的类型不同 Excel将自动更换数值类型 日期和时间的使用Excel中显示时间和日期的数字是以1900年1月1日星期日为日期起点 数值设定为1 以午夜零时 0 00 00 为时间起点 数值设定为0 0 范围是24小时 日期计算中常常用到两个日期之差 例如 公式 99 9 10 99 9 1 计算结果为9 此外 也可以进行其它计算 例如 公式 99 9 10 99 9 1 计算结果为72817 公式的使用 注意 输入日期时 若以短格式输入年份 即年份输入两位数 Excel2003将作如下处理 若年份在00至29之间 作为2000至2029年处理 例如 输入10 11 2 Excel认为该日期是2010年11月2日 若年份在30至99之间 作为1930至1999年处理 例如 输入89 8 10 Excel认为该日期是1989年8月10日 公式返回错误值及其产生原因使用公式时 出现错误将返回错误值 表6 1列出了常见的错误值及其产生的原因 表6 1公式返回的错误值及其产生的原因 公式的使用 6 数组公式的使用数组是由一组数据记录组成的数据集合 用数组公式可以执行多个计算并返回多个结果 数组公式作用于两个或多组被称为数组参数的数值 每组数组参数必须具有相同数目的行和列 创建数组公式 如果希望数组公式返回一个结果 则单击输入数组公式的单元格 如果希望数组公式返回多个结果 则选定输入数组公式的单元格区域 输入公式的内容 按Ctrl Shift Enter键 注意 若数组公式返回多个结果 则删除数组公式时必须删除整个数组公式 公式的使用 数组公式中除可以使用单元格引用外 也可以直接输入数值数组 直接输入的数值数组称为数组常量 在公式中建立数组常量的方法 直接在公式中输入数值 并用大括号 括起来 不同列的数值用逗号分开 不同行的数值用分号分开 应用数组公式在图4 1所示学生成绩工作表中 可以用数组公式计算总分 方法 选定要用数组公式计算结果的单元格区域F2 F9 输入公式 C2 C9 D2 D9 E2 E9 按Ctrl Shift Enter键结束输入并返回计算结果 图6 1在Excel中建立的学生成绩表 例 创建数组创建一维横向数组 2 4 6 8 选定四个横向连续的单元格在编辑栏输入 2 4 6 8 按 Ctri Shift Enter 键确认创建一维纵向数组 2 4 6 8 选定四个纵向连续的单元格在编辑栏输入 2 4 6 8 按 Ctri Shift Enter 键确认 公式的使用 公式的使用 7 中文公式的使用复制 使用函数以及对工作表中的某些内容进行修改时 涉及到单元格或单元格区域 为简化操作 允许对单元格或单元格区域命名 从而可以直接使用单元格或单元格区域的名称来规定操作对象的范围 单元格或单元格区域命名是给工作表中某个单元格或单元格区域取一个名字以后的操作中 涉及到已被命名的单元格或单元格区域时 只要使用名字即可操作 不再需要进行单元格或单元格区域的选定操作 公式的使用 菜单栏上选择 插入 名称 选项 可为单元格 单元格区域 常量或数值表达式建立名字 建立名字后 可直接用来引用单元格 单元格区域 常量或数值表达式 可以更改或删除已经定义的名字 也可以预先为以后要用的常量或计算的数值定义名字 建立名字后 若选定一个命名单元格或已命名的整个区域 名字出现在编辑栏的引用区域 输入公式和函数选择单元格 然后键入 等号 若单击了 编辑公式 按钮或 粘贴函数 按钮 Excel将插入一个等号 接着输入公式内容后 按Enter键 6 4 2公式中的引用 Excel公式一般不是指出哪几个数据间的运算关系 而是计算哪几个单元格中数据的关系 需要指明单元格的区域 即引用 在公式中经常需要引用单元格Excel提供三种不同的引用类型 相对引用 绝对引用和混合引用 在实际应用中 要根据数据的关系决定采用那种引用类型 单元格引用概念相对引用 公式中引用的单元格地址在公式复制 移动时自行调整绝对引用 公式中引用的单元格地址在公式复制 移动时保持不变单元格引用表示方式相对引用 列坐标行坐标例B6 A4 C5 F8绝对引用 列坐标 行坐标例 B 6 A 4 C 5 F 8混合引用 列坐标 行坐标例B 6 A 4 C 5 F 8 列坐标行坐标例 B6 A4 C5 F8按F4键可在相对引用和绝对引用之间切换 公式中的引用 公式中的引用 相对引用 直接引用单元格区域名 不需要加 符号 例如 公式 A1 B1 C1中的A1 B1 C1都是相对引用 使用相对引用后 系统记住建立公式的单元格和被引用单元格的相对位置 复制公式时 新的公式单元格和被引用的单元格之间仍保持这种相对位置关系 绝对引用 绝对引用的单元格名中 列 行号前都有 符号 例如 上述公式改为绝对引用后 单元格中输入的公式应为 A 1 B 1 C 1 使用绝对引用后 被引用的单元格与引用公式所在单元格之间的位置关系是绝对的 无论这个公式复制到任何单元格 公式所引用的单元格不变 因而引用的数据也不变 混合引用 混合引用有两种情况 若在列号 字母 前有 符号 而行号 数字 前没有 符号 被引用的单元格列的位置是绝对的 行的位置是相对的 反之 列的位置是相对的 行的位置是绝对的 例如 A1是列绝对 行相对 A 1是列相对 行绝对 公式中的引用 以下图所示工作簿文件为例 单元格区域A1 D2中存放的是常数 在E1 E2 E3三个单元格中输入含有相同单元格位置但引用类型不同的三个公式 E1 A1 B1 C1 D1E2 A 2 B 2 C 2 D 2E3 A 2 B 2 C 2 D 2将E1复制到F1 公式变为 B1 C1 D1 E1E2复制到F2 公式不变 E3复制到F3 公式变为 B 2 B 2 D 2 D 2可知 相对引用要随着目标单元格的变化而变化 绝对引用却不随目标单元格而变化 始终保持不变 三种引用类型的示意图 公式中的引用 引用同一工作簿中其它工作表的单元格在同一个工作簿中 可以引用其它工作表的单元格 设当前工作表是Sheet1 要在单元格A1中求Sheet2工作表单元格区域C3 C9中数据之和 方法一 在Sheet1中选择单元格A1 输入公式 SUM Sheet2 C3 C9 按回车键 方法二 在Sheet1中选择单元格A1 输入 SUM 或单击常用工具栏中的自动求和按钮 再选择Sheet2表标签 在Sheet2中选择单元格区域C3 C9 最后在编辑栏中补上 或直接按回车键 由系统自动加上 公式中的引用 引用其它工作簿的单元格同样道理 也可以引用其它工作簿中单元格的数据或公式 例如 设当前工作簿是Book1 要在工作表Sheet1的单元格B1中求工作簿文件AAA XLS中单元格区域 B 1 F 1的数据之和 设工作簿文件AAA XLS在C盘目录下 方法一 移动单元格光标到B1 输入公式 SUM C AAA XLS Sheet1 B 1 F 1 然后按回车键 方法二 移动单元格光标到B1 输入 SUM 或单击常用工具栏中的自动求和按钮在菜单栏上选择 文件 打开 选项 打开AAA XLS工作簿 在AAA XLS工作簿Sheet1中选择单元格区域 B 1 F 1 按回车键 关闭工作簿文件AAA XLS 公式中的引用 注意 如果在单元格中出现 ERR 可能发生以下错误 用零作除数 使用空白单元格作为除数 引用空白单元格 删除在公式中使用的单元格或包括显示计算结果的单元格引用 引用名字若单元格已经命名 在引用单元格时可以引用其名字 循环引用当一个公式直接或间接地引用了该公式所在的单元格时 产生循环引用 计算循环引用的公式时 Excel需要使用前一次迭代的结果计算循环引用中的每一个单元格 迭代是指重复计算 直到满足特定的数值条件 如果不改变迭代的默认设置 Excel将在100次迭代后或两次相邻的迭代得到的数值相差小于0 001时停止迭代运算 公式中的引用 迭代设置可以根据需要改变 改变默认迭代设置的方法 在菜单栏上选择 工具 选项 选项 弹出 选项 对话框 在 重新计算 选项卡中单击 反复操作 复选框 根据需要在 最多迭代次数 和 最大误差 文本框中输入新的设置值 单击 确定 按钮 6 4 3函数的使用 函数是Excel内部已经定义的公式 对指定的值区域执行运算 Excel提供的函数包括数学与三角 时间与日期 财务 统计 查找和引用 数据库 文本 逻辑 信息和工程等 为数据运算和分析带来极大的方便 函数的有关概念函数的一般形式为 函数名 参数 函数名是函数的名称 不同的函数具有不同的功能 函数名不可随意更改 参数为可选项 如果具有参数 则它为函数的计算提供必需的数据 根据函数的不同 其参数也具有不同情况 有些函数没有参数 而有些函数带有参数 有些函数带有1个参数 而有些函数带有2个或者更多的参数 本案例中 计算总分可用函数 SUM C2 E2 不必输入公式 C2 D2 E2 计算平均分可用函数 AVERAGE C2 E2 不必输入公式 F2 3 函数的使用 方法与步骤 在单元格F2输入函数 SUM C2 E2 按回车键 或在编辑栏左边单击 按钮 在单元格G2输入函数 AVERAGE C2 E2 按回车键 或在编辑栏左边单击 按钮 知识拓展 1 函数的语法函数由函数名和参数组成 函数名通常以大写字母出现 用以描述函数的功能 参数是数字 单元格引用 工作表名字或函数计算所需要的其它信息 例如 函数SUM A1 A10 是一个求和函数 SUM是函数名 A1 A10是函数的参数 函数的使用 函数的语法规定 函数必须以 开头 例如 SUM A1 A10 函数的参数用圆括号 括起来 其中 左括号必须紧跟在函数名后 否则出现错误信息 个别函数如PI等虽然没有参数 也必须在函数名之后加上空括号 例如 A2 PI 函数的参数多于一个时 要用 号分隔 参数可以是数值 有数值的单元格或单元格区域 也可以是一个表达式 例如 SUM SIN A3 pi 2 COS A5 PI B6 B6 D6 文本函数的参数可以是文本 该文本要用英文的双引号括起来 例如 TEXT NOW 年度核算 函数的使用 函数的参数可以是已定义的单元格名或单元格区域名 例如 若将单元格区域E2 E20命名为Total 则公式 SUM Total 是计算单元格区域E2 E20中数值之和 函数中可以使用数组参数 数组可以由数值 文本和逻辑值组成 可以混合使用区域名 单元格引用和数值作为函数的参数 2 函数的参数类型 数字 如21 7 37 25等 文字 如 a Word Excel 等 若在文字中使用双引号 则在每个双引号处用两个双引号 如 TEXT 逻辑值 如TRUE FALSE或者计算时产生逻辑值的语句 如A10 35 错误值 如 REF 单元格引用 如D11 C5 C10 函数的使用 3 组合函数函数参数也可以是其它函数 从而构成组合函数 组合函数可以充分利用工作表中的数据 还可以充分发挥Excel快速计算和重复计算公式的能力 函数被用作参数时 不需要前置 号4 函数的输入方法 插入函数 选定要输入函数的单元格 可以输入单个函数 或将函数作为公式的一部分 在菜单栏上选择 插入 函数 选项 弹出 插入函数 对话框 图4 42 插入函数 对话框 函数的使用 在 函数分类 列表框中选择函数类型 如 常用函数 从 函数名 列表框中选择要输入的函数 单击 确定 按钮 弹出 函数参数 对话框 如图4 43所示 以SUM函数为例 在参数框中输入数据或单元格引用 可单击参数框右侧的 折叠对话框 按钮 暂时折叠起对话框 在工作表中选择单元格区域后 单击折叠后的输入框右侧按钮 即可恢复参数输入对话框 输入函数的参数后 单击 确定 按钮 在选定的单元格中插入函数并显示结果 提示 折叠对话框 按钮可将对话框折起而不妨碍单元格区域的选取 这种折叠是暂时的 通过单击折叠后的输入框右侧按钮 可以恢复对话框 函数参数 对话框 函数的使用 使用公式选项板可以用公式选项板输入函数 单击编辑栏中的 fx 按钮 可以显示公式选项板 公式选项板中显示函数的名称 参数和计算结果 以及整个公式的计算结果和该函数的简单说明 操作方法如下 建立一张新的工作表 在单元格A1中输入100 选定单元格A2 输入 A1 单击编辑栏上的 按钮 位于函数名的右边 在下拉函数名列表中选择MAX函数 该函数将返回输入参数的最大值 在函数选项板MAX函数的每一行参数栏中分别输入1 2 3 图4 44 观察显示在参数栏下方的函数计算值 显示在公式选项板下方的公式计算值为10 单击 确定 按钮 A2单元格中显示公式计算结果10 用公式选项板输入函数 函数的使用 直接输入函数选定单元格 直接输入函数 按回车键得出函数结果 函数输入后 如果需要修改 可以在编辑栏中直接修改 也可以用 粘贴函数 按钮或编辑栏的 按钮进入参数输入框进行修改 如果要换成其它函数 应先选定要更换的函数 再选择其它函数 否则会将原函数嵌套在新函数中 5 使用 自动求和 工具按钮求和是常用函数之一 常用 工具栏上的自动求和按钮 可以快速输入求和函数 自动求和 按钮可将单元格中的累加公式转换为求和函数 例如 某单元格中输入公式 A1 B1 C1 D1 E1 选定该单元格后 在工具栏上单击 按钮 可将该公式转换为函数 SUM A1 E1 函数的使用 如果要对一个单元格区域中各行 列 数据分别求和 可先选定该区域及其右侧一列 下方一行 单元格 然后在常用工具栏上单击 自动求和 按钮 各行 各列 数据之和分别显示在右侧一列 下方一行 单元格中 例如 在学生成绩表中求各学生三门课程的总分 操作步骤如下 选定单元格区域C2 F9 单击 按钮 求和函数显示在F2 F9单元格区域中 计算结果如图4 45所示 自动求和 功能示例 使用求和函数 双击 看到计算结果 2 求和 单击 看到计算公式 1 选取存放结果的单元格 使用函数 函数形式 函数名 参数表 参数 常量 单元格 区域 区域名 公式或其他函数 函数输入 粘贴函数法直接输入法 6 5格式化工作表 在最初建立的工作表中输入时 所有的数据都使用默认的格式 如文字左对齐 数字右对齐 字体采用五号宋体黑色字等 这样的工作表一般不能符合要求 因此 创建工作表后还需要进行对工作表进行格式化 Excel2003提供丰富的的格式化功能 可以完成对数字显示格式 文字对齐方式 字体 字型 框线 图案和颜色等设置 使工作表表达得更加清晰 美观 6 5格式化工作表 本节内容 6 5 1对工作表的格式进行编排6 5 2对工作表的文字进行修饰6 5 3设置单元格的数字格式6 5 4设置工作表的背景和边框6 5 5条件格式与格式刷的使用 6 5 1对工作表的格式进行编排 任务描述掌握编排工作表格式的方法案例4 8学生成绩表刚创建时 输入的数据都是使用默认格式 现要求改变工作表的格式 使其达到图4 25所示的效果 设置格式后的学生成绩表 6 5 1对工作表的格式进行编排 相关知识与技能 工作表中的标题一般都是居中的 当一个单元格中的内容比较多时 单元格的行宽和列高可能需要调整 工作表中一般包含文字 数字等不同类型的数据 往往希望不同类型的数据采用不同的数据格式与对齐方式 上图所示工作表中 标题居中 第二行数据项目名称和学生姓名 B列 居中 所有数字左对齐 6 5 1对工作表的格式进行编排 方法与步骤 设置标题居中方法一 选中单元格区域A1 H1 在 格式 工具栏上单击 合并及居中 按钮 方法二 选中单元格区域A1 H1 在菜单栏上选择 格式 单元格 选项 弹出 单元格格式 对话框 选择 对齐 选项卡 在 水平对齐 下拉列表框中选择 居中 选项 垂直对齐 下拉列表框中选择 居中 选项 选中 合并单元格 复选框 在 文字方向 下拉列表框中选择 根据内容 选项 单击 确定 按钮 单元格格式 对话框 6 5 1对工作表的格式进行编排 选择单元格区域A2 H2 在 格式 工具栏上单击 居中 按钮 选择单元格区域B2 B10 在 格式 工具栏上单击 居中 按钮 选择单元格区域A3 A10 C3 H10 在 格式 工具栏上单击 左对齐 按钮 6 5 1对工作表的格式进行编排 知识拓展 1 设置工作表的列宽先选定需要设置列宽的列 然后用以下方法设置列宽 方法一 在菜单栏上选择 格式 列 列宽 选项 或快捷菜单中选择 列宽 选项 在 列宽 对话框中输入列宽值 单击 确定 按钮 提示 在工作表中 每列宽度的默认值为8 38 方法二 在菜单栏上选择 格式 列 最适合的列宽 选项 所选列的列宽自动调整至合适的列宽值 然后单击 确定 按钮 方法三 将鼠标移到所选列标的右边框 鼠标指针变为一条竖直黑短线和两个反向的水平箭头 按住鼠标左键 拖动该边框 向右拖动加宽 向左拖动变窄 改变列宽度 或双击鼠标 该列的宽度自动设置为最宽项的宽度 6 5 1对工作表的格式进行编排 2 设置工作表行高输入数据时 根据输入字体大小自动调整行的高度 使其能够容纳行中最大字体 也可根据需要设置行高 可以一次设置一行或多行的高度 先选定需要设置行高的行 然后用以下方法设置行高 方法一 在菜单栏上选择 格式 行 行高 选项 或在快捷菜单中选择 行高 选项 在 行高 对话框中输入要设置的行高值 0 409之间的整数 代表行高的点数 然后单击 确定 按钮 提示 在工作表中 每行宽度的默认值为14 25 方法二 在菜单栏上选择 格式 行 最适合的行高 选项 所选行的行高自动调整至适合行高值 然后单击 确定 按钮 方法三 将鼠标移到所选行标的下边框 鼠标指针变为一条水平黑短线和两个反向的垂直箭头 用鼠标拖动该边框改变行的高度 或双击鼠标 该行的高度自动设置为最高项的高度 6 5 1对工作表的格式进行编排 3 单元格内容的对齐 用菜单命令设置 选定单元格区域 在菜单栏上选择 格式 单元格 选项 或在快捷菜单中选择 设置单元格格式 选项 弹出 单元格格式 对话框 在 对齐 选项卡内选择需要的选项 用格式工具栏设置 选定单元格区域 在 格式 工具栏上单击 左对齐 居中 右对齐 合并及居中 按钮 6 5 2对工作表的文字进行修饰 任务描述掌握修饰工作表文字的方法 案例4 9对学生成绩表中的文字进行修饰 效果如图4 28所示 要求 标题文字为 华文细黑 字体 字号16点 蓝色 第二行数据项目名称文字为 隶书 字体 字号14点 黑色 其余单元格中的文字为 楷体 GB2312 字体 字号12点 梅红色 文字进行修饰后的学生成绩表 6 5 2对工作表的文字进行修饰 相关知识与技能 新建工作表中 数字和文字都采用默认的五号宋体字 一张工作表中 如果全部文字和数字都采用默认的五号宋体字 不仅使整个工作表看起来乏味 而且数据不突出 也不利于阅读 Excel2003中对于单元格中使用的字体 字号 文字颜色等 既可以在数据输入前设置 也可以在数据输入完成后进行设置 6 5 2对工作表的文字进行修饰 方法与步骤 选中标题所在单元格A1 用 格式 工具栏设置字体为 华文细黑 字号为 16 字体颜色为 蓝色 选中数据项目名所在单元格区域A2 H2 用 格式 工具栏设置字体为 隶书 字号为 14 字体颜色为 黑色 选中单元格区域A3 H10 用 格式 工具栏设置字体为 楷体 GB2312 字号为 12 字体颜色为 梅红 6 5 2对工作表的文字进行修饰 知识拓展 1 设置单元格的文字格式用户可以用菜单命令 工具栏或快捷键定义文字的字体 字形 大小 颜色 底线和特殊效果 用菜单命令设置 选定单元格区域 在菜单栏上选择 格式 单元格 选项 或在快捷菜单上选择 单元格 选项 在 单元格格式 对话框的 字体 选项卡中设置 字体 字形 字号 下划线 颜色 特殊效果 删除线 上标和下标 等 完成设置后 可在 预览 框预览当前选定的字体及其格式式样 用工具栏设置 可用 格式 工具栏的字体 字号等按钮对选定的单元格区域设置字体 6 5 2对工作表的文字进行修饰 2 设置单元格的颜色和图案选定单元格区域 在菜单栏上选择 格式 单元格 选项 在 单元格格式 对话框的 图案 选项卡中设置底纹颜色和图案 在 示例 显示框中可预览设置的效果 6 5 3设置单元格的数字格式 任务描述掌握设置单元格数字格式的方法 案例4 10某电器商店根据商品销售记录制作一张电子表格工作表中的数据全部采用默认格式 且其中有多种数字 如日期 数量 货币等 对工作表进行格式设置后 效果如下图所示 未格式化的商品销售记录表 格式化后的商品销售记录表 6 5 3设置单元格的数字格式 相关知识与技能 工作表内部 数字 日期 时间 货币等都以纯数字存储 单元格内显示时 按单元格的格式显示 如果单元格没有重新设置格式 采用通用格式 将数值以最大的精确度显示 数值很大时 用科学记数表示 如2 3456E 05 如果单元格的宽度无法以设定的格式将数字显示出来 单元格用 号填满 此时 只要将单元格加宽 即可将数字显示出来 将图4 29中工作表设置为图4 30所示格式 除将标题行设置为 合并及居中 将第2行与第E列设置为居中外 还要将单元格区域B3 B10设置为日期格式 将单元格区域D3 D10 F3 F10设置为货币格式 6 5 3设置单元格的数字格式 方法与步骤 选中单元格区域A1 F1 设置标题格式为 合并及居中 选中单元格区域A2 F2 在 格式 工具栏上单击 居中 选项 选中单元格区域E3 E10 在 格式 工具栏上单击 居中 选项 选中单元格区域B3 B10 在菜单栏上选择 格式 单元格 选项 弹出 单元格格式 对话框 在 数字 选项卡的 分类 列表框中选择 日期 选项 在 类型 列表框中选择 2001年3月14日 选项 如图4 31所示 在单击 确定 按钮 分别选中单元格区域D3 D10和F3 F10 用同样的方法设置数字格式为 货币 单元格格式 对话框中的 数字 选项卡 6 5 3设置单元格的数字格式 知识拓展 1 数字格式概述不同的应用场合 需要使用不同的数字格式 因此 要根据需要设置单元格中的数字格式 默认的数字格式是 常规 格式 输入时 系统根据单元格中输入的数值进行适当的格式化 例如 输入 1000时 自动格式化为1 000 输入1 3时 自动显示为1月3日 输入25 时 系统认为是0 25并显示25 6 5 3设置单元格的数字格式 2 设置单元格数字格式 用菜单设置选定单元格区域 在菜单栏上选择 格式 单元格 选项 或在快捷菜单中选择 单元格 选项 弹出 单元格格式 对话框 见图4 28 在 数字 选项卡的分类列表中选择数字类型及数据格式 单击 确定 按钮或按回车键 用工具栏设置 格式 工具栏有五个用于设置单元格数字格式的工具按钮 分别是货币样式 百分比样式 千分分隔样式 增加小数位数 减少小数位数 可以用于设置单元格的数字格式 6 5 3设置单元格的数字格式 3 创建自定义格式若Excel提供的数字格式不够用 可以创建自定义数字格式 如专门的会计或科学数值表示 电话号码 区号或其他必须以特定格式显示的数据等 创建自定义格式的方法 选定单元格区域 在 单元格格式 对话框 图4 31 数字 选项卡的分类列表中选择 自定义 选项 在 类型 列表中选择一个最接近需要的自定义格式并进行修改 单击 确定 按钮或按回车键 保存新的数字格式 6 5 3设置单元格的数字格式 自定义格式符号含义 数字预留位置 若小数点左侧位数多于 个数 将显示超出数 若小数点右侧位数多于 个数 将超出部分四舍五入0 数字预留位置 实际位数少于预设位数 用0填充 数字预留位置 用空格代替0填充 使小数点能对齐 小数点 当数介于0 1之间 小数点前不显0 千分位分隔符 百分号 数字预留位置 输入文字将被置于 处 6 5 4设置工作表的背景和边框 任务描述如果能给单元格或整个表格添加颜色或图案 将会使表格变得漂亮 增加可读性 学习设置工作表背景和边框的操作 案例4 11给图4 30所示商品销售记录表添加边框线 效果如图4 32所示 添加边框线后的商品销售记录表 6 5 4设置工作表的背景和边框 相关知识与技能 一般情况下 工作表需要加上边框线 有些比较特殊的单元格还需要突出显示 本案例为表格加上粗边