第4章_常用计算机软件的使用edit(excel_2003)

电子表格软件Excel2003 电子表格处理Excel2003 本章重点掌握工作簿 工作表的建立 数据的输入与编辑掌握数据的基本运算 公式与函数的使用数据的筛选 排序与分类汇总数据图表的创建与编辑掌握工作表的格式化与打印输出 概述 采用Windows标准的窗口菜单界面 操作直观简便 能同时编辑多张工作表 直接在工作表完成数据的输入及结果的输出 对表格的操作非常简便 真正的 所见及所得 提供了丰富的函数 对表格中的数据可以做复杂的数学分析和报表统计 具有丰富的制图功能 使图 文 表有机地结合在一起 并且操作简便 能从不同角度对数据进行分析 很容易地重新组织数据 能够与Windows环境下运行的其他软件共享数据资源 启动与退出 程序文件 Excel exe启动与退出方法参考Word2003 注意总结与Word2003的异同点 Excel窗口认识 标题栏 编辑栏 菜单栏 状态栏 任务窗格 工作表区 常用工具栏 名称框 工作表标签 活动单元格 概念 工作簿 Book 是管理和储存数据的文件 系统默认的扩展名为 xls 每一个工作簿都可以包含多张工作表 因此可在单个工作簿文件中管理各种类型的相关信息 工作表 Sheet 工作表是Excel用来组织和处理数据的最主要的文档 它包含排列的行和列的单元格 每张表由256列和65536行构成 工作表标签 工作表的管理通过工作表左下角标签进行 单击标签选择工作表 右击标签 弹出快捷菜单 更名 添加 删除 移动 复制 概念 单元格 工作表中行列交叉处的方格 活动单元格 当前被选取的单元格 粗框框住单元格地址 引用 在当前工作表中用列名行名表示 列名 A B Z AA AZ IV 行名 1 2 3 65536 如A1 G10 AP23等 在不同工作表之间 需加上工作表名和半角的感叹号 如SHEET1 C7等 概念 区域 由多个单元格组成 区域的选择 单击 拖动连续区域 Shift 单击单击行名或列名在行名或列名上拖动单击工作表左上方选择框 全选 同时选中多个分散区域 Ctrl 区域名称 用左上和右下角单元地址表示如 B1 B5 A1 F7如果我不確定A的範圍 A A 也可直接在编辑栏左边的名字框中命名 如 qqq 选中整个工作表 选中多行 创建 打开 保存工作簿文件 创建工作簿文件 启动Excel后自动处于新建状态 工作簿默认文件名为Book1 xls 文件 新建 临时文件名 Book1 xls Book2 xls 打开工作簿文件 文件 打开 双击工作簿文件保存工作簿文件 文件 保存 文件 另存为 回顾Word2003文档文件的操作 工作表基本操作 选择工作表 单击工作表标签工作表更名 右击工作表标签 单击快捷菜单 重命名 插入工作表 右击工作表标签 单击快捷菜单 插入 选择 工作表 删除工作表 右击工作表标签 单击快捷菜单 删除 移动或复制工作表 右击工作表标签 单击快捷菜单 移动或复制工作表 工作表操作 隐藏工作表菜单 格式 工作表 选择隐藏命令工作表中的某些行列隐藏单击菜单 格式 行 列 选择隐藏命令拆分和冻结工作表活动工作表分成多个窗格分别显示 菜单 窗口 拆分 以选定的单元格为分界线 冻结分界线上部内容 菜单 窗口 冻结 操作取消相应的菜单有对应的取消选项 在工作表中输入数据 常量 输入数值 日常记数法或科学记数法有效的数字 0 9 靠右对齐 宽度不够则显示为科学记数法分数输入法 0 空格 分子 分母输入字符 汉字 字母 字符 数字将数字作为字符 需加单引号 如 8075814靠左对齐 宽度不够则只显示部分 单元格内换行输入日期和时间日期默认格式 yyyy mm dd 也可用 分隔时间默认格式 hh mm ss 12小时制 空格后输入AM a 或PM p 同时输入日期和时间 中间加空格2002 11 208 15pm 提示 在活动单元格进行输入 输入数据后必须采用以下四种方法之一结束输入 单击编辑栏的 按钮 方向键 单击另一单元格 在工作表中输入数据 填充数据 填充 将数据复制到相邻的单元格 如果有相同 相似的数据或公式要输入到相邻的单元格中 除了用复制 粘贴的操作外 还可以用填充操作快速完成 鼠标操作 拖动源单元格的填充柄 FillHandle 填充目标区域 菜单操作 选择包括复制源和目的单元在内的区域 其中源单元或行或列必须在区域的边上 菜单 编辑 填充 根据复制源的位置 选择复制方向 填充操作示意图 填充柄 数据有效性 功能 可以对一个单元格输入的数据作出限制例如 可以限定输入文本的长度 时间与日期的范围 自定义输入序列等为如图所示的工作表中部门名称一列 用数据有效性规则输入职工所在部门名称要求 有效性条件为允许 序列 数据来源于 人力资源部 系统集成部 市场部 网络安全部 产品开发部 输入信息为 请输入职工所在部门 出错警告 出错信息为 请选择所在部门 步骤如下 先选中要设置的区域再选择 数据 有效性 命令进行设置 部门名称设置了数据有效性后的效果 图5 24选择职工所在部门 如果每行都需要设置有效性怎么办 宏 如果经常重复某项任务 那么可以用宏自动执行该任务宏是一系列命令和函数 存储于VisualBasic模块录制宏和运行宏在录制宏时 Excel在您执行一系列命令时存储该过程的每一步信息 然后即可运行宏来重复所录制的过程或 回放 这些命令 工作表的编辑 修改数据插入数据删除数据移动数据复制数据查找与替换 工作表的编辑 修改数据 修改单元格内容 双击击单元格 直接修改新数据 5种方法之一结束修改 单击单元格 单击编辑框 在编辑框中修改数据 注意 在修改结束后 Enter 或 注意 在修改过程中单击其他单元格或移动方向键会被Excel误会为输入单元格地址 工作表的编辑 插入数据 插入一行 插入 行 插在上面插入一列 插入 列 插在左边插入单元格 插入 单元格 对话框选择 工作表的编辑 删除数据 删除行或列 选择行或列 编辑 删除 删除单元格 选择单元格 编辑 删除 清除 选择单元格或区域 编辑 清除 全部格式内容 等同键 批注注意删除与清除的异同 工作表的编辑 移动数据 选取源 单元格或区域 剪切 单击目标区域的左上单元格 粘贴 注意 要覆盖新区域的内容 鼠标操作法 选取源后 将鼠标指向边框 用鼠标的拖拽操作直接完成移动试一试 将23两行与45两行交换位置 工作表的编辑 复制数据 选取源 单元格或区域 复制 单击目标区域的左上单元格 粘贴 注意 要覆盖新区域的内容 鼠标操作法 选取源后 将鼠标指向边框 按住 Ctrl 键 用鼠标的拖拽操作直接完成复制 工作表的编辑 查找与替换 与Word相似菜单 编辑 查找 搜索方式 按行或按列搜索范围 按值或按公式或按批注 替换 按钮进行替换操作 工作表格式化 改变行高和列宽单元格格式设置数据格式设置字体与对齐格式设置边框与图案设置字体与对齐格式保护自动套用格式条件格式 工作表格式化 改变行高和列宽 改变行高 选择行自动调整鼠标操作菜单操作 精确 格式 行 改变列宽 选择列自动调整鼠标操作菜单操作 精确 格式 列 工作表格式化 设置数据格式 选择单元格或区域 菜单 格式 单元格 数字选项卡 设置货币格式设置日期格式设置时间格式设置分数格式自定义格式 0 00E 00工具栏按钮操作 设置数字格式 两种不同的货币表示 工作表格式化 设置字体与对齐格式 选择单元格或区域 菜单 格式 单元格 字体选项卡 字体 字形 字号 选择单元格或区域 菜单 格式 单元格 对齐选项卡 水平对齐垂直对齐文本控制 合并单元格工具栏按钮操作 左 居中 右对齐与合并单元格并居中 通栏标题 合并及居中单元格 选中要单元格居中的多个单元格单击 格式 工具栏中的 合并及居中 按钮 工作表格式化 设置边框与图案 选择单元格或区域 菜单 格式 单元格 边框选项卡选择单元格或区域 菜单 格式 单元格 图案选项卡 锁定与隐藏单元格 有时工作表中的信息 是比较隐私的信息 既不允许随意改动单元格的默认格式为工作表保护时处于 锁定 状态 即不能修改单元格内容 可以改变这个设置 格式 单元格 保护 锁定 将单元格的格式设置为工作表保护时 隐藏 状态 不能显示公式 格式 单元格 保护 隐藏 以上设置均在工作表保护时生效 保护工作簿和工作表 工作簿的保护 工具 保护 工作簿 结构 不能对该工作簿中的工作表进行移动 删除 新建 隐藏 取消隐藏或重新命名 窗口 不能改变该工作簿的窗口大小和位置保护工作表 工具 保护 工作表 取消保护工作表 工具 保护 撤消工作簿 表保护 工作簿为其设置打开权限密码 工具 选项 安全性 选项卡 为工作表添加密码 有时工作表中的信息 也不能轻易被别人看到 因此 为其设置打开权限密码是必要的选择 工具 菜单下 选项 选项 在弹出的 选项 对话框中选择 安全性 选项卡 在其中的 打开权限密码 文本框中输入密码 如图所示也可以在 修改权限密码 文本框中设定修改权限密码 只有知道此密码的使用者才可以修改工资表的权限 工作表格式化 自动套用格式 格式 自动套用格式 单击 选项 可以套用某格式的一部分 工作表格式化 条件格式 可以对区域根据条件进行格式化 格式 条件格式 例1 如果成绩超过了90分 则将此单元格加上绿色的背景色 如果成绩低于60分 则将此单元格加上红色的背景色 条件格式 公式 例2 将满足以下条件的学生姓名变为相应的格式 至少有一科成绩不及格方法先设置一个单元格的格式 然后填充 打印工作表 可打印工作表的全部或部分 与word2003基本类似 页面设置打印区域 在工作表中输入公式 公式 公式是对工作表中的数据进行分析计算的等式公式可以引用同一工作表的各单元格 同一工作簿不同工作表中的单元格 甚至其他工件薄的工作表中单元格的数值 公式中由运算符和操作数组成 操作数是常量可以包含数字 单元格引用 函数 运算符包括算术 逻辑 文本 比较 引用运算符在单元格中输入公式还是函数时必须以等号 开始 公式 算术运算符 2 3 A3 A2 计算逻辑运算符 A1 A10逻辑值True False文本运算符 感叹号 例如 sheet1 A1 B4 如 公式求 B1 B2 B3函数求 Sum B1 B3 函数求 Sum B1 B2 B3 单元格地址 引用 单元格地址 在当前工作表中用列名行名表示 列名 A B Z AA AZ IV行名 1 2 3 65536如A1 G10 AP23等1 相对引用地址 会因为公式所在的位置的变化而发生对应的变化例如 A3 2 绝对引用地址 不会因为公式所在的位置的变化而发生变化 例如 A 3 3 混合引用地址 如果我们需要固定某列而变化某行 或是固定某行而变化某列的引用时 采用混合引用地址 例如 A3 或 A 3 相对地址与绝对地址 复制过程的智能性是由于单元格地址是采用相对地址 复制时公式中地址发生位移 位移按目标与源的距离测算 如果不想让地址位移 必须采用绝对地址 绝对地址的表示方法 行名或列名前加 例 A 2为绝对地址A2为相对地址A 2为相对列绝对行地址 A2为绝对列相对行地址 例 B5中公式为 A3 A1 那么将B5复制到D5时 D5的公式为 C3 C1将B5复制到B7时 B7的公式为 A5 A3 将B5复制到D7时 D7的公式为 例 B5中公式为 A 3 A 1 那么 将B5复制到D5时 D5的公式为 将B5复制到B7时 B7的公式为 将B5复制到D7时 D7的公式为 例 B5中公式为 A3 A1 那么 将B5复制到D5时 D5的公式为 将B5复制到B7时 B7的公式为 将B5复制到D7时 D7的公式为 例 B5中公式为 A 3 A 1 那么 将B5复制到D5时 D5的公式为 将B5复制到B7时 B7的公式为 将B5复制到D7时 D7的公式为 引用函数 按照各种函数的规定格式直接键入 菜单 插入 函数 弹出 粘贴函数 对话框 单击工具栏粘贴函数按钮 fx 弹出 粘贴函数 对话框 选择函数 根据提示输入各自变量 常用函数 预定义的公式 Excel函数 Excel提供包括财务函数 日期与时间函数 数量与三角函数 统计函数 查找与引用函数 数据库函数 文字函数 逻辑函数 信息函数等 INT 取得一个小于参数值的最大整数 格式 INT number 例 INT 23 6 INT 23 6 ROUND 四舍五入保留指定小数位数的参数值 格式 ROUND number num digits 例 将成绩四舍五入保留2位小数 ROUND B3 2 MAX 求一组数值中的最大值 MIN 求一组数值中的最小值 AVERAGE 求一组数值的平均值 格式 AVERAGE MAX MIn num1 num2 常用函数 逻辑 AND 判断所有逻辑表达式的值 如果全部都为True 则AND函数返回值为True 否则返回值为False 格式 AND logical1 logical2 例 AND B340 OR 判断所有逻辑表达式的值 如果全部都为False 则OR函数返回值为False 否则返回值为True 格式 OR logical1 logical2 例 OR B35 常用函数 IF 执行真假值的判断 根据逻辑测试的真假值 返回不同的结果 格式 IF logical test value if true value if false 例 成绩分类 按分数分为优 合格和不合格 IF B3 90 优 IF B3 60 合格 不合格 COUNT 求出指定区域内数值的个数 格式 COUNT num1 num2 例如 COUNT B2 B4 C5 COUNTIF 计算某个区域中满足给定条件单元格的数目格式 COUNTIF range criteria 例 统计在E2 E15的区域中有几个不合格的人 COUNTIF E2 E15 不合格 常用函数 统计 SUM 求一组数值的总和 格式 SUM num1 num2 例如 SUM A1 A3 SUM B2 B4 C5 SUM 23 45 88SUMIF 根据指定条件对若干单元格求和格式 SUMIF range criteria sum range 例如 SUMIF A1 A4 100 B1 B4 RANK 返回数字在一列数字中相对其他数值的大小排位格式 number ref order 例如 A2 A2 A10 false 日期函数Today 返回当前日期格式 Today 取日期的年月日函数YEARMonth day 日期 例如 year Today 返回给定日期的年 月 日 注意 在函数中 表达式需要使用字符时 用西文的双引号括起来 例如IF AND A2 85 B2 85 优 合格 常用函数 查找 引用 LOOKUP 用于返回向量 单行区域或单列区域 或数组中的数值LOOKUP lookup value lookup vector result vector例如 常用函数 查找 引用 在表格的首列查找值 并由此返回表格数组当前行中其他列的值VLOOKUP lookup value table array col index num range lookup Lookup value表示要在表格第一列中查找的值 Table array要查找的区域 用于查找数据的区域 上面的查找值必须位于这个区域的第一列Col index num为table array中待返回的匹配值的列序号 为相对列号 Col index num为1时 返回table array第一列的数值 以此类推 Range lookup为一逻辑值 指明函数VLOOKUP查找时是精确匹配 还是近似匹配 举例 根据工资表创建各个员工的工资条 编号A001 的工资条创建为例说明 第一步 拷贝标题栏第二步 在编号处 A21 写入A001第三步 在姓名 B21 创建公式 VLOOKUP A 21 A 3 H 12 2 FALSE 语法解释 在 A 3 H 12范围内 即工资表中 的第一列精确找出与A21单元格相符此工资条为应用Vlookup函数建立 以员工Sandy 的行 并将该行中第二列的内容计入单元格中第四步 以此类推 在随后的单元格中写入相应的公式 常用函数 查找 引用 HLOOKUP lookup value table array row index num range lookup Lookup value为需要在数据表第一行中进行查找的数值Table array为需要在其中查找数据的数据表 如果range lookup为TRUE 则table array的第一行的数值必须按升序排列 否则 函数HLOOKUP将不能给出正确的数值 如果range lookup为FALSE 则table array不必进行排序 Row index num为table array中待返回的匹配值的行序号 Row index num为1时 返回table array第一行的数值 以此类推 Range lookup为一逻辑值 指明函数HLOOKUP查找时是精确匹配 还是近似匹配 如果为TRUE或省略 则返回近似匹配值 也就是说 如果找不到精确匹配值 则返回小于lookup value的最大数值 数据管理与图表化 图表记录排序记录筛选分类汇总数据透视表 图表 图表是工作表数据的图形表示图表分为嵌入式图表 作为对象插入到工作表工作表图表 作为新工作表插入到工作簿Excel2003共提供了14种 标准类型 图表 图表示例 认识图表 分类y轴标题 分类x轴标题 图表标题 分类x轴标志 数据系列 图例 数据值 网格线 建立图表 选择数据区 含标题单击菜单 插入 图表 或 图表向导 按钮 图表向导 之一 二 三 四 直至 完成 选择图表类型选择或确认源数据图表选项的设置选择图表位置 提示 要修改图标的局部部分 双击 右击相应的局部部分 使用弹出的菜单进行相应的设置 建立图表 为图5 13中的各课程的平均分 最高分 最低分的簇状柱形图 编辑图表 移动图表单击图表 鼠标拖动图表改变图表大小单击图表 鼠标拖动尺寸柄图表数据的增加和删除单击图表 菜单 图表 数据源 选择新的数据区域 增加或删除后的 在工作表中添加数据 选择它 复制 选择图表 粘贴 图表数据的修改修改工作表单元格中数据 编辑图表 修改图表标题 图表标题 X轴标题 Y轴标题 单击图表 菜单 图表 图表选项 标题 选项卡插入数据标记单击图表 菜单 图表 图表选项 数据标记 选项卡改变图表的文字 颜色和图案双击有标题的位置如坐标轴 进行设置通过文本框进行文字添加改变图表类型单击图表 菜单 图表 图表类型 进行选择删除图表 单击图表 Delete 记录排序 简单排序 单击选择要排序的字段名 单击工具按钮 升序 A Z 或 降序 Z A 复杂排序 菜单命令 数据 排序 排序 对话框三个层次的排序 主要关键字 次要关键字 第三关键字每个层次均可选择递增或递减 有标题行 标题行不参与排序 关键字显示标题 没有标题行 显示 按列A 按列B 按列C 如果单击 选项 按钮 则打开 排序选项 对话框 可以设置自定义排序顺序例如 汉字可以按拼音或笔画排序例如 低 中 高 或者 一月 二月 三月 等 还可以指定区分大小写排序和按从上到下或从左到右排序 注意与RANK函数的区别 记录筛选 按照一定的条件 选取部分数据显示 将不符合条件的数据隐藏起来 菜单命令 数据 筛选 自动筛选 每个字段名旁显示一个小箭头 称为 筛选箭头 单击筛选箭头 下拉列表框列出该字段下的刷选项 单击下拉列表框中的 自定义 条件筛选 再次选中菜单 数据 筛选 自动筛选 取消筛选 筛选箭头消失 数据 筛选 高级筛选 条件区域的建立和选取 高级筛选 如果用户要使用高级筛选 一定要先建立一个条件区域 条件区域用来指定筛选的数据必须满足的条件 空几行 条件区域 目录 列标签 高级筛选的条件区域 单列上具有多个条件同一列或 or 关系多列同一行上具有单个条件与 and 关系某一列或另一列上具有单个条件多列上的或 or 关系两列上具有两组条件之一不同列上的与 and 关系不同行上的或 or 关系 总结 同一行之间是与 and 关系不同行之间是或 or 关系 例 布尔逻辑 类型 特制器 OR销售人员 李小明 分类汇总 必须按分类字段先排序 菜单命令 数据 分类汇总 弹出 分类汇总 对话框 汇总方式 求和 计数 平均值 最大值 最小值 选定