microsoft officeExcel电子表格 教学

1 Excel电子表格 目标 熟悉Excel的操作环境 掌握工作簿和工作表的基本操作 添加 重命名 移动 复制 删除工作表 掌握工作表的各种编辑操作 数据的输入方法 单元格的移动和复制 工作表的格式化 掌握工作表中公式和函数的使用 理解单元格的相对引用 绝对引用和混合引用 熟悉常用函数的使用方法 掌握用工作表中的数据创建各种图表的方法 掌握对数据清单的管理 排序 筛选 分类汇总 以及数据透视表的基本操作 2 实现对数据的编辑 修改 管理 保存和使用 制作各种各样的数据表格 利用原始数据或通过分析得到的数据 生成各种图表 进行数据统计 分析预测 可添加图形 图像效果 增强表现力 电子表格软件的基本功能 3 4 工作簿 是用来运算和存储数据的文件 每个工作簿都可以包含多个工作表 当前工作的工作表只有一个 称为活动工作表 通常启动Excel后会建立一个空工作簿 名称为Book1 工作表 工作表是工作簿的一部分 俗称电子表格 工作表的名称显示在工作簿窗口底部的工作表标签上 默认Sheet1 Sheet2等 工作表由若干行和列组成 一个工作簿默认有3个工作表 用户可以根据需要添加工作表 最多可有255个 单元格 是Excel工作簿不可分割的最小组成单位 确定单元格的三个参数 哪一个工作表 哪一行 哪一列 列标与行号 单元格的最左端和顶端有一些框栏 上面标有数字或字母 一个工作表最多有256列 用字母A Z AA AZ BA BZ IA IV表示 最多有65536行 用数字1 65536表示 一张表可以有256 65536个单元格 相关概念 5 新建工作簿保存工作簿 第一季度销售统计 添加工作表 单击窗口底部的工作表标签 选定某个工作表 然后右击 弹出快捷菜单 单击 插入 命令 重命名工作表 双击要重命名的工作表标签 如将Sheet1 Sheet2 Sheet3分别命名为 1月份销售表 2月份销售表 3月份销售表 添加工作表标签颜色 右击要添加颜色的工作表标签 在弹出的快捷菜单中选择 工作表标签颜色 命令 6 删除工作表移动和复制工作表工作簿内移动和复制 单击待移动的工作表标签 按住鼠标左键向左或向右拖动 按住Ctrl键拖动是复制 工作簿之间移动和复制打开用于接收工作表的工作簿 切换到包含要移动或复制工作表的工作簿中 并选定要移动或复制的工作表 选择 编辑 菜单中的 移动或复制工作表 命令 或者右击 在弹出快捷菜单中选择 移动或复制工作表 命令 打开 移动或复制工作表 对话框 在 工作簿 列表框中选择用于接收工作表的工作簿名 如果选择 新工作簿 则可以将选定的工作表移动或复制到新的工作簿中 在 下列选定工作表之前 列表框中选择工作表将要插入的位置 如果要复制工作表 选中 建立副本 复选框 不选中只是移动工作表 7 Excel将汉字 字母 数字或者其他特殊字符的任意组合作为文本 一个单元格中输入多达32000个字符 有些数字是无须计算的代码 如电话号码 邮政编码 人员编号等 往往要把这些数字作为文本处理 为了与数值区别 先输入单撇号 然后输入数字或者先输入等号 和双撇号 再输入数字 例如 输入数字字符串 7600或 7600 字符 7600 将在单元格中左对齐并作为文本 其中 所有符号都是英文符号 输入文本 8 输入数值可以用自然计数法和科学计数法 采用自然计数法 当位数太多时 会自动改成科学计数法表示 在默认情况下 输入的数值数字在单元格中右对齐 如果要输入一个负数 可以在数字前加上一个负号 或者将数字括在圆括号内 自然计数法 正数1234512 345 负数 12 345 12 45 科学计数法 1 2345E 04 分数输入法 如输入分数5 6的方法是 先输入0和空格 再输入5 6 以区别日期的输入 输入数值 9 2001 6 19表示2001年6月19日6 10表示当前年度6月10日December1表示当前年度12月1日2001 6 1013 55表示2001年6月10日13点55分15 24表示24小时制时间3 24 35PM表示12小时制时间同时按下Ctrl键和 键 在活动单元格中输入系统当前日期 同时按下Ctrl Shift键和 键 在活动单元格中输入系统当前时间 输入日期 10 填充柄和填充序列对话框在Excel工作表中填写数据时 经常会遇到一些在结构上有规律的数据 例如1997 1998 1999 星期一 星期二 星期三等 对这些数据可以采用填充技术 让它们自动出现在一系列连续单元格中 填充技术是通过 填充柄 或 填充序列对话框 来实现的 用鼠标单击一个单元格或拖动鼠标选定一个连续的单元格区域时 框线的右下角会出现一个黑点 这个黑点就是填充柄 打开填充序列对话框的方法是 选择 编辑 填充 序列 命令 填充 11 数字序列填充 等差 等比序列 日期填充 日期序列提供了4种填充方式 日 工作日 月 年 文本填充文本中没有数字 填充操作只是复制初始单元格的内容文本中全为数字 当在文本单元格格式中 数字作为文本处理的情况下 填充时将按等差序列进行 文本中含有数字 无论用何种方法填充 字符部分不变 数字按等差序列 当文本中有两个或两个以上数字时 只有最后面的数字才能按等差序列变化 其余数字不发生变化 填充 12 单个单元格的选定 单击该单元格即可 如C3 单元格区域的选定 单击选定该区域的第一个单元格 然后拖动鼠标直至选定最后一个单元格 或者单击该区域第一个单元格 按住Shift键 再单击该区域最后一个单元格 不连续单元格区域的选定 先选定第一个单元格区域 按住Ctrl键 再选定其他的单元格区域 行列选定 单击行号可以选中一行 单击列标可以选中整列 单击工作表表格左上角的第一个格 全选按钮 可以选中整个工作表 一般是先选定单元格区域 再进行相关操作 区域选取 13 Excel中 选定的单元格区域地址表示为 起始单元格 终止单元格 如A1 E10 在使用工作表进行工作的时候 如果不愿意使用这种不直观 难以理解记忆的单元格区域地址 可以为其定义一个名称 名称是创建的一个易于记忆的标识符 单元格区域的命名 14 名称的第一个字母必须是字母或下划线 名称中的字符可以是字母 数字 下划线和句号 名称不能与单元格引用相同 如R1C1 E 20等 名称中不能有空格 名称最多可以包含255个字符 名称中不区分大小写字符 例如 已经在工作簿中创建了名称 No sale 接着又在同一工作簿中创建 NO SALE 则第二个名称替换第一个名称 一旦定义名称之后 名称的使用范围是工作簿级 可以在同一个工作簿中的任何地方使用 单元格区域的命名 15 例如 将 1月份销售 工作表中的单元格区域E1 E24命名为 原价 F1 F24命名为 平价售价 G1 G24命名为 销售数量 选定单元格区域 如E1 E24 选择 插入 名称 定义 菜单命令 给单元格区域定义的名称会随工作簿一起保存 名称定义后将在 名称框 列出 使用了区域名称的地址是绝对地址 命名 16 在需要插入单元格处选定相应的单元格区域 选择 插入 单元格 菜单命令 如果需要插入一行 则单击需要插入的新行之下相邻行中的任意单元格 如果要插入多行 则选定需要插入的新行之下相邻的若干行 选择 插入 行 命令 即可插入一行或多行 插入单元格 行 列的数量和选中的一致 在 1月份销售 工作表的 统计日期 字段前插入两列 销售额 和 折扣率 在顶部插入两行输入表格的题目 数码产品专卖店第一季度销售统计 插入单元格 行 列 17 移动和复制单元格 复制 将 1月份销售 工作表的A1 J3复制到 2月份销售 工作表中移动 选定单元格或区域 将鼠标指针指向选定区域的选择框上 当鼠标指针变成十字型箭头时 按住鼠标左键拖动到新位置 选择性粘贴 除了复制整个单元格外 Excel还可以选择单元格中的特定内容进行复制 18 删除单元格 行或列 是指将选定的单元格从工作表中移走 并自动调整周围的单元格填补删除后的空格 选择 编辑 菜单下的 删除 命令 清除单元格 行或列 是指将选定的单元格中的内容 格式或批注等从工作表中删除 单元格仍保留在工作表中 用 编辑 菜单的 清除 命令 查找和替换 其它操作 19 工作表背景 格式 工作表 背景 边框与底纹 每个单元格都由围绕单元格的灰色网格线标识的 网格线是为输入 编辑方便而预设置的 是个虚框 在打印时 网格线是不可能出现的 若希望在打印时能清楚的区分每个单元格 或为了强调工作表的某一部分 可以给单元格或单元格区域加上边框 选定单元格区域后 单击 格式 单元格 命令 打开 单元格格式 对话框 调整行高和列宽 鼠标拖动设置行高 列宽用菜单精确设置行高 列宽 单击 格式 列 列宽 或 格式 行 行高 输入精确值 20 设置单元格的字符格式用工具栏将 1月份销售 工作表的标题设置成 隶书 20磅 加粗 红色 用单元格格式对话框设置 设置列标题 A3 J3 字体楷体 字号12 加粗 深红 会计用双下划线 设置单元格格式 21 格式化数字 可以使用数字格式更改数字 包括日期和时间 的外观 而不更改数字本身 所应用的数字格式并不会影响单元格中的实际数值 而Excel是使用该实际值进行计算的 例如将 原价 平价售价 以及 销售额 列的数字设置为货币样式 货币符号为 并且精确到1位小数 打开 单元格格式 对话框 单击 数字 选项卡 设置数字格式为 文本 将使输入的数字作为数字字符串 设置单元格格式 22 设置对齐方式 系统在默认的情况下 输入单元格的数据对齐方式为文字左对齐数字右对齐日期右对齐逻辑值居中用工具栏按钮设置对齐方式利用 单元格格式 对话框设置对齐方式 水平对齐 的格式 常规 系统默认的对齐方式 左 缩进 居中 靠右 填充 两端对齐 跨列居中 分散对齐 垂直对齐 的格式 靠上 居中 靠下 两端对齐 分散对齐 在 方向 列表框中 拖动红色小方块 或在旋转角度微调器中 输入数值 可以改变单元格内容的显示方向 自动换行 复选框 当单元格中的内容宽度大于列宽时 会自动换行 设置单元格格式 23 条件格式 在Excel工作表中 可以根据一定的条件突出显示某些单元格 例如 在 1月份销售 工作表中 要突出显示 销售数量 高于40和低于15的单元格 大于40的单元格 设置字符格式为 红色 加粗倾斜 小于15的单元格 设置字符格式为 蓝色 加粗 橙色底纹 24 有时要在电子表格中添加公司的标志 或者是图形和说明性的文字 来解释表格中的内容 增强视觉效果 如在工作表左上角添加一个数码产品的图形 Excel本身提供在单元格中添加一些注解来解释数据含义的 批注 功能 给单元格添加批注 单击要添加批注的单元格 执行 插入 批注 菜单命令 在输入框中输入批注内容 单元格的右上角出现红色小三角标记 修改批注 选中相应单元格 执行 插入 编辑批注 菜单命令 删除批注 在相应单元格右击 在快捷菜单中选择 删除批注 25 算术运算符 算术运算符用来完成基本的数学运算 如加法 减法和乘法 算术运算符有十 加 一 减 乘 除 百分比 乘方 比较运算符 比较运算符用来对两个数值进行比较 产生的结果为逻辑值TRUE 真 或FALSE 假 比较运算符有 等于 大于 小于 大于等于 小于等于 不等于 运算符 26 文本运算符 文本运算符 用来将一个或多个文本连接成为一个组合文本 例如 Micro soft 的结果为 Microsoft 引用运算符 引用运算符用来将单元格区域合并运算 冒号运算符可以用来表示一个区域 例如 A1 B3它代表以A1为左上角 B3为右下角所围成的矩形单元格区域 公式 SUM A1 B3 完成了对该区域的每个单元格的求和运算 运算符 27 空格运算符用来表示几个单元格区域共有 重叠 的那些单元格 例如 A1 D3B1 E3 它代表以A1为左上角D3为右下角围成的矩形区域和以B1为左上角E3为右下角所围成的矩形相重叠的单元格区域B1 D3 公式 SUM A1 D3B1 E3 即求 SUM B1 D3 逗号运算符将两个单元格引用名联合起来 这种用法在使用函数处理不连续的一系列单元格时是很方便的 例如 在单元格A1中输入公式 SUM B4 B9 D4 D9 表示将B4到B9以及D4到D9单元格的数字相加 公式 SUM A1 B3 C5 即求A1 B3 C5 运算符 28 选定需要输入公式的单元格 在所选的单元格中输入等号 输入公式内容 如果计算中用到单元格中的数据 可用鼠标单击所需引用的单元格 公式输入完后 按Enter键 Excel自动计算并将计算结果显示在单元格中 公式本身显示在编辑栏中 1月份销售 表中计算销售额和折扣率 1 单击单元格H4 输入 F4 G4 然后按Enter键确认 结果将显示在H4单元格中 2 单击单元格I4 输入公式 F4 E4 创建公式 29 B4 25 SUM D5 F5 30 公式中的变量是单元格或单元格区域地址时 称为引用 引用使公式的值依赖于单元格或单元格区域的值的变化而变化 计算出了第一个产品的销售额和折扣率 而其它产品的销售额和折扣率算法与第一个相同 如果一个一个地重复输入公式 将是一件非常麻烦的重复劳动 而且容易出错 利用Excel的公式复制功能 可以快速建立有规律的公式 移动和复制公式 31 单元格的引用分3种 相对引用 绝对引用和混合引用 当移动公式时 公式中的单元格引用并不会改变 当复制公式时 单元格中的绝对引用不会改变而相对引用要发生改变 具体的操作和对单元格中的数据的移动复制是类似的 移动和复制公式 32 公式中单元格的相对引用 如G4 是基于创建公式的单元格和引用单元格的相对位置的 亦即 引用后系统记住建立公式的单元格和被引用单元格的相对位置 公式被复制到其他单元格时 其单元格相对引用也会改变 在H4单元格中输入公式 F4 G4 后 复制H4单元格 粘贴到H5 将得到 F5 G5 的值 相对引用 33 绝对引用表示某一单元格在工作表中的绝对位置 绝对引用要在行号和列标前加一个 符号 例如 在F2单元格中输入 D2 B 7 E2 B 8 复制F2单元格 粘贴到F6单元格时将得到 D6 B 7 E6 B 8 绝对引用 34 行采用相对引用 列采用绝对引用即列标前带 符号而行号前不带 符号 行采用绝对引用 列采用相对引用即行号前带 符号而列标前不带 符号 如E 4或 B3混合引用是相对地址与绝对地址的混合使用 E 4表示E是相对引用 4是绝对引用 当公式所在的单元格位置改变时 相对引用改变而绝对引用不变 如在A2中输入 A 1 复制A2单元格粘贴到B3单元格中得到 B 1 的值 混合引用 35 当某个单元格输入公式后 如果相邻的单元格中需要进行同类型的计算 可以利用填充功能来复制公式 现在计算 销售额 列的其它值就非常简单 单击H4单元格 按住鼠标左键拖动填充柄至单元格H26时放开即可 折扣率 列的计算方法一样 之后将 折扣率 列的数据精确到两位小数 填充公式 36 函数是预定义的内置公式 使用被称为参数的特定数值 按照语法所列的特定顺序进行计算 Excel提供了大量的函数 可以实现数值统计 逻辑判断 财务计算 工程分析 数字计算等功能 函数由函数名 后跟用括号括起来的参数组成 函数名要用大写字母 Excel会自动把输入的小写字母名变成大写字母名 如果输入的函数没有自动变成大写字母 可能是拼写错误或根本就没有这个函数 常用的有SUM AVERAGE MAX MIN COUNT SQRT函数等 函数 37 函数 例如 计算 1月份销售 表中 销售额 的总和 把结果置于H27中 计算 原价 最大值与 平价售价 最大值的差额 将结果置于E27中 引用单元格区域计算总额单击需要输入函数的单元格H27 选择菜单 插入 函数 命令 打开 插入函数 对话框 在 选择类别 列表框中选择合适的函数类型 如果不知道函数所属类型 则选择 全部 根据提示输入每个参数值 引用名称计算差额 MAX 原价 MAX 平价售价 38 公式和函数的综合应用 在工作簿中插入一个新的工作表 命名为 1月份统计表 计算各种产品的销售额占总销售额的百分比 B3中的公式为 SUM 1月份销售 H4 H12 1月份销售 H27 100 更改公式自动求和 39 柱形图 用来显示一段时间内数据的变化或者描述各项数据之间的差异 条形图 用来显示特定时间内各项数据的变化情况 或者比较各项数据之间的差别 分类项垂直组织 数据值水平组织 突出数值的比较 而淡化随时间的变化 饼图 体现的是一个整体中每一部分所占的比例时 折线图 通常用来显示等间隔时间的数据变化趋势 常见的如股票价格的折线图等 散点图 主要用在科学计算中 不仅可以用来比较多个数据系列的数值 而且可以将两组数值显示为XY坐标系中的一个系列 圆环图 类似饼图 也显示部分与整体的关系 但圆环图可以包含多个数据系列 图表类型 40 新创建的图表分为两类 一种是嵌入式图表 即把图表作为工作表的一部分 另一种是图表工作表 即生成独立于工作表的图表存在 两类图表都链接到它的工作表数据 所以在改变数据时 图表将自动更新 建立图表 41 使用图表向导创建图表的步骤选择 插入 图表 菜单命令 选择图表类型 选择数据源设置图表选项选择图表放置的位置 建立图表 42 图表组成 43 图表的基本组成如下 图表区 整个图表及其包含的元素 绘图区 在二维图表中 以坐标轴为界并包含全部数据系列的区域 在三维图表中 绘图区以坐标轴为界并包含数据系列 分类名称 刻度线和坐标轴标题 图表标题 一般情况下 一个图表应该有一个文本标题 它可以自动与坐标轴对齐或在图表顶端居中 数据系列 图表上的一组相关数据点 取自工作表的一行或一列 图表中的每个数据系列以不同的颜色和图案加以区别 在同一图表上可以绘制一个以上的数据系列 数据标记 图表中的条形 面积 圆点 扇形或其它类似符号 来自于工作表单元格的单一数据点或数值 图表中所有相关的数据标记构成了数据系列 源自工作表中的行或列 图表组成 44 图表组成 图表的基本组成如下 数据标志 根据不同的图表类型 数据标志可以表示数值 数据系列名称 百分比等 坐标轴 为图表提供计量和比较的参考线 一般包括X轴 Y轴 刻度线 坐标轴上的短度量线 用于区分图表上的数据分类数值或数据系列 网格线 图表中从坐标轴刻度线延伸开来并贯穿整个绘图区的可选线条系列 图例 是图例项和图例项标示的方框 用于标示图表中的数据系列 图例项标示 图例中用于标示图表上相应数据系列的图案和颜色的方框 数据表 在图表下面的网格中显示每个数据系列的值 45 编辑图表 图表的缩放 移动和删除图表类型的更改 如将 1月份统计表 中建立的柱形图改为饼图 右击图表区 从快捷菜单中选择 图表类型 命令 修饰图表 包括设置图表区 绘图区 图例 图表标题 网格线等的格式 设置图表选项 右击图表区 选择快捷菜单中的 图表选项 命令 46 数据清单 EXCEL具有一定的数据管理与分析功能 是通过数据清单来实现的 数据清单由若干列组成 每列有一个列标题 相当于数据库中表的字段名 列相当于字段 数据清单中的行相当于数据库中表的记录 不要把其它数据放入数据清单的同一个工作表中 如果要在一个工作表中存放多个数据清单 就用空行或空列分隔每一个数据清单 在一个数据清单中不应有空白行或空白列出现 数据清单中的第一行通常为列标题 数据清单中不能有合并单元格 设计数据清单时 应该使同一列中的各行有近似的数据项目 向单元格输入数据或文本时 不要在开始位置输入空格 列标题与数据行之间不能有空行 47 使用记录单 记录单是一个对话框 利用它可以很方便地在数据清单中输入 编辑或显示一行完整的信息或记录 也可以利用数据记录单删除记录 添加记录修改 删除记录查找记录 查找 销售数量 大于30 的记录 48 排序规则 系统内部有一个默认的排序顺序 它是根据单元格中的数值大小而不是其格式来安排的 在按升序排序时 其默认顺序如下 数值从最小的负数到最大的正数排序 文本及包含数字的文本 按0 9 A Z的顺序排序 逻辑假 FALSE 排在逻辑真 TRUE 之前 所有错误值的优先级相等 即排序不分先后 空格始终排在最后 49 排序 单列排序 在 1月份销售 表中 可以按 品牌 重排数据清单 多列排序 例如 按 产品类型 排序 在 产品类型 相同的情况下 对 品牌 排序 在 品牌 相同的情况下 按 销售额 从高到低排序 选择数据清单A3 J26 单击 数据 排序 菜单命令 50 数据筛选将数据清单中满足条件的显示 不满足条件的数据暂时隐藏起来 但没有被删除 当筛选条件被删除 隐藏的数据又恢复显示 系统提供了两种筛选功能 一个是自动筛选 另一个就是高级筛选 51 选定要筛选的数据区域A3 J26 选择 数据 筛选 自动筛选 菜单命令 在数据清单中每一个列标题旁边会有下拉箭头 筛选按钮 显示出来 单击包含想显示的数据列旁的箭头 可以看到一个下拉列表 选择所需的内容 只希望显示品牌是 三星 的数据记录 则单击 品牌 旁的筛选按钮 从下拉列表框中选择 三星 自动筛选 52 自动筛选 在自动筛选的条件中 单击 全部 将无条件显示全部内容单击 前10个 出现如下所示对话框 可以显示出最大或最小的前n项 或n 单击 自定义 出现 自定义自动筛选方式 对话框 选择所需要的条件 输入具体值 则筛选出与具体值相符的项 如筛选掉 折扣率 高于0 9 低于0 7的记录 则单击 折扣率 字段的筛选按钮 选择 自定义 选项 取消筛选 选择 数据 筛选 自动筛选 命令 清除该命令前面的对勾标记 53 分类汇总 分类汇总就是对数据清单按某字段进行分类 将字段值相同的连续记录作为一类 进行求和 平均 计数等汇总运算 针对同一个分类字段 可进行多种汇总 在分类汇总前 首先 必须对要分类的字段进行排序 否则分类无意义 其次 在分类汇总时要区分清楚 对哪个字段分类 对哪些字段汇总 汇总的方式 这在分类汇总对话框要逐一设置 进行数据筛选后 对数据清单进行分类汇总时 隐藏的记录将不参与分类汇总 54 分类汇总 分类汇总步骤 选择数据清单中的数据区域A3 J26 单击 数据 排序 命令 按分类字段 产品类别 排序 单击 数据 分类汇总 菜单命令 打开 分类汇总 对话框 选择分类字段 汇总方式 汇总项 删除分类汇总 1 在分类汇总数据清单中选择一个单元格 2 单击 数据 分类汇总 命令 打开 分类汇总 对话框 3 单击 全部删除 按钮即可 55 高级函数 CORREL函数 根据给定的两组数据 计算它们之间的相关系数 使用相关系数可以确定两种属性之间的关系 语法 CORREL Array1 Array2 Array1 第一组数值单元格区域 Array2 第二组数值单元格区域 说明 如果数组或引用参数包含文本 逻辑值或空白单元格 则这些值将被忽略 但包含零值的单元格将计算在内 如果Array1和Array2的数据点的个数不同 函数CORREL返回错误值 N A 如果Array1或Array2为空 或者其数值的s 标准偏差 等于零 函数CORREL返回错误值 DIV 0 56 高级函数 FORECAST函数根据已有的数值计算或预测未来值 此预测值为基于给定的x值推导出的y值 已知的数值为已有的x值和y值 再利用线性回归对新值进行预测 可以使用该函数对未来销售额 库存需求或消费趋势进行预测 函数FORECAST的计算公式为a bx 语法 FORECAST X Known y s Known x s X 为需要进行预测的数据点 Known y s 为因变量数组或数据区域 Kno