Excel应用PPT课件

1 计算机文化基础 第4章Excel2010的应用 目录 contents 4 1电子表格的创建 学生档案管理4 2数据处理 学生成绩分析表4 3数据检索 学生成绩信息查询4 4数据综合管理 销售业绩处理 4 1电子表格的创建 学生档案管理 MicrosoftExcel2010是MicrosoftOffice2010系列办公软件中的一员 集表格创建 数据计算 图表编辑 统计分析于一体 是一款功能强大的电子表格处理软件 Excel2010提供了丰富的 新的改进功能 提供了大量的公式函数以供应用 极大地方便了各种数据的处理 统计分析和辅助决策操作 因此被广泛地应用于管理 统计 金融等众多领域 4 1 1项目描述 新生入校后 为了能及时掌握学生的各种信息 张老师正忙着利用Excel软件为学生建立电子档案 张老师是如何将学生信息输入计算机 如何利用Excel软件科学管理学生信息的呢 下面一起来学习 4 1 2项目知识准备 1 Excel窗口的组成在Windows操作系统下 选择 开始 所有程序 MicrosoftOffice MicrosoftExcel2010 菜单命令 即可启动Excel2010 Excel2010的窗口如图4 1 1所示 图4 1 1Excel2010窗口 4 1 2项目知识准备 4 1 2项目知识准备 1 功能区功能区替代了传统的菜单和工具栏 为用户提供了自定义程度更大的工作体验 在Excel2010窗口上方看起来像菜单的名称其实是功能区的名称 当单击这些名称时并不会打开菜单 而是切换到与之相对应的功能区 每个功能区根据功能的不同又分为若干个组 每个功能区所拥有的功能如下 开始 功能区 开始 功能区中包括剪贴板 字体 对齐方式 数字 样式 单元格和编辑7个组 该功能区主要用于帮助用户对Excel2010表格进行文字编辑和单元格的格式设置 是用户最常用的功能区 如图4 1 2所示 4 1 2项目知识准备 图4 1 2 开始 功能区 插入 功能区 插入 功能区包括表格 插图 图表 迷你图 筛选器 链接 文本和符号几个组 该功能区主要用于在Excel2010表格中插入各种对象 如图4 1 3所示 图4 1 3 插入 功能区 4 1 2项目知识准备 页面布局 功能区 页面布局 功能区包括主题 页面设置 调整为合适大小 工作表选项 排列几个组 该功能区用于帮助用户设置Excel2010表格的页面样式 如图4 1 4所示 图4 1 4 页面布局 功能区 4 1 2项目知识准备 公式 功能区 公式 功能区包括函数库 定义的名称 公式审核和计算几个组 该功能区用于实现在Excel2010表格中进行各种数据计算 如图4 1 5所示 图4 1 5 公式 功能区 4 1 2项目知识准备 数据 功能区 数据 功能区包括获取外部数据 连接 排序和筛选 数据工具和分级显示几个组 该功能区主要用于在Excel2010表格中进行数据处理方面的操作 如图4 1 6所示 图4 1 6 数据 功能区 4 1 2项目知识准备 审阅 功能区 审阅 功能区包括校对 中文简繁转换 语言 批注和更改5个组 该功能区主要用于对Excel2010表格进行校对和修订等操作 适用于多人协作处理Excel2010表格数据的场合 如图4 1 7所示 图4 1 7 审阅 功能区 4 1 2项目知识准备 视图 功能区 视图 功能区包括工作簿视图 显示 显示比例 窗口和宏几个组 该功能区主要用于帮助用户设置Excel2010表格窗口的视图类型 以方便操作 如图4 1 8所示 图4 1 8 视图 功能区 4 1 2项目知识准备 2 编辑栏当选择单元格或单元格区域时 相应的地址或区域名称即显示在编辑栏左端的名称框中 在单元格中编辑数据时 其内容同时出现在编辑栏右端的编辑框中 以方便用户输入或修改单元格中的数据 中间是确认区 当在右侧进行编辑时 将变成 按钮为 取消 按钮 按钮为 输入 按钮 按钮用于调用函数 当编辑完毕后可单击按钮或按 Enter 键确认 或单击按钮取消刚才编辑的内容 3 工作表工作表为Excel窗口的主体 由单元格组成 每个单元格由行号和列标来定位 其中行号位于工作表的左端 顺序为数字1 2 3 列号位于工作表的上端 顺序为字母A B C 4 1 2项目知识准备 4 工作表标签栏工作表标签显示了当前工作簿中包含的工作表 初始为Sheet1 Sheet2 Sheet3 表示工作表的名称 当前工作表以白底加黑色下画线显示 单击工作表标签名可切换到相应的工作表 2 工作簿 工作表和单元格工作簿与工作表是Excel系统中重要的两个概念 工作簿是计算和储存数据的文件 一个工作簿就是一个Excel文件 其默认扩展名为 xlsx 一个工作簿可以包含多个工作表 最多为255个 每个工作表又可以保存不同类型的数据 用户可以将若干相关的工作表组成一个工作簿 操作时不必打开多个文件 而直接在同一文件的不同工作表中方便地切换 在默认情况下 一个工作簿自动打开3个工作表 分别以Sheet1 Sheet2 Sheet3命名 其中 当前工作表为Sheet1 用户根据实际需要可以增减和选择工作表 4 1 2项目知识准备 单元格是组成工作表的最小单位 一个工作表由65536行和256列组成 行号由上至下从1到65536编号 列号由左至右从A到IV编号 每一个行列交叉处即为一个单元格 每个单元格只有一个固定的地址 即单元格地址 例如 B5是指B列与第5行交叉位置上的单元格 由于一个工作簿包含多个工作表 要区分不同工作表的单元格 必须在单元地址前加上工作表名称 并以 间隔 例如 Sheet1 B5代表Sheet1工作表的B5单元格 要输入单元格数据 首先要激活单元格 在任何时候 工作表中有且仅有一个单元格是激活的 即 活动单元格 其标志是有一个黑色边框 如图4 1 9所示 用鼠标单击单元格即可使单元格成为活动单元格 此时输入数据即出现在该单元格中 4 1 2项目知识准备 图4 1 9活动单元格 4 1 2项目知识准备 3 数据输入在工作表中输入数据是一项基本操作 主要包括数值输入 文本输入 日期 时间输入等 1 数值输入数值除了数字 0 9 还包括 E e 以及小数点 和千分位符号 等特殊字符 数值数据在单元格中默认向右对齐 Excel数值的输入与数值显示未必相同 如果输入的数据长度超出单元格宽度 则Excel自动以科学计数法表示 又如 将单元格数字格式设置为两位小数 此时输入3位小数 则末位将进行四舍五入 温馨提示 Excel计算时将以输入数值为准 而不是显示数值 输入分数时 应在分数前加个0并用空格隔开 否则系统会当作日期处理 如输入 01 5 4 1 2项目知识准备 2 文本输入Excel文本包括汉字 英文字母 数字 空格及其他键盘能输入的符号 文本在单元格中默认向左对齐 如果输入的文本长度超过单元格宽度 若右边单元格无内容 则扩展到右边列 否则 截断显示 技巧点滴 如果要输入电话号码或邮政编码等特殊文本 只需在数字前加上一个英文方式下的 单引号即可 如果要在同一单元格中显示多行文本 则单击 开始 功能区的 对齐方式 组中的按钮即可 如果要在单元格中输入 硬回车 则按 Alt Enter 组合键 4 1 2项目知识准备 3 日期 时间输入Excel内置了一些日期时间的格式 当输入的数据与这些格式相匹配时 Excel将识别它们 Excel常见的日期时间格式为 年 月 日 日 月 年 月 日 年 月 日之间用斜杠 或减号 分隔 日期和时间在单元格中默认向右对齐 技巧点滴 如果要输入当前日期 按 Ctrl 组合键 如果要输入当前时间 按 Ctrl Shift 组合键 如果要输入12小时制的时间 在时间后留一空格 并输入 AM 或 PM 表示上午或下午 4 1 2项目知识准备 4 数据的成批填充成批填充操作是指在工作表中快速生成具有一定规律的数据 1 填充简单数据将鼠标指针指向初始值所在单元格右下角的填充柄 鼠标指针变为实心十字形 按下鼠标左键拖动至需填充的最后一个单元格 松开鼠标 即可完成填充 如图4 1 10所示 图4 1 10填充简单数据 技巧点滴 若要得到2 4 6 的填充效果 则应先输入 2 4 再选中2 4所在的单元格 用鼠标指针指向4单元格右下角的填充柄 拖动即可 若要得到递增数据1 2 3 的填充效果 可先输入 1 用鼠标指针指向1单元格右下角的填充柄 并同时按住 Ctrl 键不放向下或向右拖动鼠标至要填充的最后一个单元格即可 若要得到递减数据 则可通过向上或向左拖动鼠标来实现 温馨提示 若初始值为数值型数字 自动填充时 是将原数据复制到新单元格 若为文本型数字 则自动填充时 数字将依次递增 图4 1 10中的数据就为文本型数字 若初始值为Excel预设的自动填充序列中的一员 则按预设序列填充 例如 初始值为 星期一 则自动填充为 星期二 星期三 4 1 2项目知识准备 4 1 2项目知识准备 2 填充复杂数据在 开始 功能区的 编辑 组中单击 填充 按钮 在弹出的下拉框中选择 序列 命令 弹出如图4 1 11所示的 序列 对话框 从中可实现具有一定规律的复杂数据的填充 3 用户自定义填充序列操作步骤如下 选择 文件 选项 菜单命令 在弹出的 Excel选项 对话框中选择 高级 选项 在右侧窗格的 常规 选项区域单击 编辑自定义列表 按钮 将弹出 自定义序列 对话框 如图4 1 12所示 4 1 2项目知识准备 图4 1 11 序列 对话框图4 1 12 自定义序列 对话框 4 1 2项目知识准备 在 自定义序列 列表框中选择 新序列 选项 在 输入序列 文本框中每输入一个序列成员按一次 Enter 键 如输入 第一名 第二名 输入完毕后单击 添加 按钮 序列定义成功后就可以使用它来进行自动填充了 将经常出现的有序数据定义为序列 在输入时可以减少很多工作量 5 数据的清除与删除 1 数据清除数据清除针对的对象是数据 单元格本身不受影响 方法 选取需清除数据的单元格或单元格区域 单击 开始 功能区的 编辑 组中的 清除 按钮 在出现的下拉菜单中选择相应的命令 全部清除 清除格式 清除内容 清除批注 清除超链接 即可 如图4 1 13所示 数据清除后 单元格中的相应内容被取消 而单元格本身仍留在原位置不变 4 1 2项目知识准备 图4 1 13 清除 下拉菜单 4 1 2项目知识准备 2 数据删除数据删除针对的对象是单元格 删除后 单元格连同里面的数据都从工作表中消失 方法 选取单元格或单元格区域 单击 开始 功能区的 单元格 组中的 删除 按钮 在下拉菜单中选择 删除单元格 命令 出现如图4 1 14所示的对话框 用户可选择 右侧单元格左移 或 下方单元格上移 单选按钮来填充被删掉单元格留下的空缺 选择 整行 或 整列 单选按钮将删除选取区域所在的行或列 其下方的行或右侧的列会自动填充空缺 当要删除的区域为若干整行或若干整列时 将直接删除 而不出现对话框 图4 1 14 删除 对话框 4 1 3项目实施 新生入校时 辅导员首先要查阅和建立所带班级的学生档案 以便快速地掌握学生的基本情况 提高管理学生的效率 对以表格形式存在的数据采用Excel软件来进行存储和管理 是非常合适的 首先 在Excel中无须手工绘制表格 行 列的增减非常容易 其次 对于具有规律性的数据 Excel的自动填充功能能减少重复输入数据的烦恼 再次 Excel强大的数据管理功能可方便用户对档案数据的查询和管理 最后 Excel易学易用 不太熟悉计算机的人也能很快上手操作 下面介绍如何利用Excel软件创建如图4 1 15所示的学生电子档案表 4 1 3项目实施 图4 1 15学生电子档案表 4 1 3项目实施 1 数据录入 1 输入标题和表头信息 2 输入表中数据2 工作表的格式化 1 标题的格式化 2 制表日期的格式化 3 出生日期数据的格式化 4 入学成绩数据的格式化 5 表格的格式设置 6 调整行高 7 重命名工作表 8 保存文档 4 2数据处理 学生成绩分析表 Excel的公式和函数能帮助人们实现复杂的数据运算 数据图表能直观明了地反映数据的特征信息 为数据分析工作提供依据 本项目通过创建学生成绩分析表 了解公式 函数和图表的操作方法 4 2 1项目描述 学生成绩分析是教学管理中的一项重要工作 通过进行学生成绩汇总 成绩排名 最高分数 各分数段人数统计等数据运算处理 能够在一定程度上反映教学质量的高低 为教学管理者更好地管理教学提供重要的依据 学生成绩分析表如图4 2 1所示 要求根据这张表完成如下工作 计算出学生的总分 总评成绩 总评成绩 总分 70 德育 30 和排名SUMrank RANK I6 I 5 I 13 统计出每门课程的平均成绩AVERAGE 求出计算机课程的最高分和最低分MAXMIN 统计出总分低于200分的人数 COUNTIF G5 G13 200 将计算机成绩低于60分的单元格设置为红色 创建图表 对学生的总评成绩进行分析 4 2 1项目描述 图4 2 1学生成绩分析表 4 2 2项目知识准备 1 公式与函数当需要将工作表中的数据进行总计 平均 汇总及其他更为复杂的运算时 可以在单元格中设计一个公式或函数 把计算的工作交给Excel 不但省事 而且可以避免用户手工计算的烦琐和出错 数据修改后 公式的计算结果也自动更新 1 公式Excel的公式中 最常用的是数学运算公式 此外也有可以进行一些比较 文字连接运算的公式 它的特征是 以 开头 由常量 单元格引用 函数和运算符组成 4 2 2项目知识准备 公式运算符 公式中可使用的运算符如下 数学运算符 加 减 乘 除 百分号 乘方 等 比较运算符 大于等于 不等于 文字运算符 它可以将两个文本连接起来 其操作数可以是带引号的文字 也可以是单元格地址 当多个运算符同时出现在公式中时 Excel对运算符的优先级做了严格规定 由高到低依次是 乘除号 加减号 比较运算符 如果运算优先级相同 则按从左到右的顺序计算 4 2 2项目知识准备 公式运算符 公式中可使用的运算符如下 数学运算符 加 减 乘 除 百分号 乘方 等 比较运算符 大于等于 不等于 文字运算符 它可以将两个文本连接起来 其操作数可以是带引号的文字 也可以是单元格地址 当多个运算符同时出现在公式中时 Excel对运算符的优先级做了严格规定 由高到低依次是 乘除号 加减号 比较运算符 如果运算优先级相同 则按从左到右的顺序计算 4 2 2项目知识准备 公式输入 公式一般都可以直接输入 方法为 先单击要输入公式的单元格 再输入形如 B1 B2 的公式 最后按 Enter 键或单击编辑栏中的按钮 2 函数Excel提供了很多函数 为用户对数据进行运算和分析带来了极大方便 1 函数输入函数输入有两种方法 粘贴函数法 直接输入法 由于Excel有几百个函数 记住函数的所有参数难度很大 为此 Excel提供了粘贴函数法 引导用户正确输入函数 4 2 2项目知识准备 2 自动计算Excel提供了自动计算功能 利用它可以自动计算选定单元格的总和 平均值 计数 最大值等 其默认计算平均值 计数和求和 在状态栏上单击鼠标右键 可显示自动计算快捷菜单 选择某计算功能后 选定单元格区域时 其计算结果将在状态栏显示出来 如图4 2 4所示 状态栏显示选定区域数值的最大值为92 图4 2 4自动计算功能 4 2 2项目知识准备 2 单元格引用和公式的复制公式的复制可以避免大量重复输入公式的工作 当复制公式时 涉及单元格区域的引用 引用的作用在于标识工作表上的单元格区域 并指明公式中所使用数据的位置 区域引用又分为相对引用 绝对引用和混合引用 1 相对引用Excel中默认的单元格引用为相对引用 如A1 B1等 相对引用是指当公式在复制或移动时会根据移动的位置自动调节公式中引用单元格的地址 例如本项目中的F3单元格的公式为 SUM C3 E3 当求和公式从F3单元格复制到F4单元格时 F4单元格的公式变为 SUM C4 E4 公式区域从F3移动到F4时 引用区域也相应从C3 E3变为C4 E4区域 以此类推 相对引用是实现公式复制的基础 4 2 2项目知识准备 2 绝对引用与相对引用比较 绝对引用只是在单元格区域中加了美元符号 绝对引用的单元格将不随公式位置的变化而改变 在上例中 若F3公式改为 SUM C 3 E 3 再将公式复制到F4 便会发现F4单元格的公式还是 SUM C 3 E 3 公式区域从F3移动到F4时 引用区域不会发生任何变化 3 混合引用混合引用是指单元格地址的行号或列号前加上 符号 如 B1或B 1 当公式单元格因为复制或插入而引起行列变化时 公式的相对地址部分会随位置变化 而绝对地址部分不变化 例如 F3单元格的公式 SUM C3 E 3 就是混合引用 如果将公式从F3单元格复制到F4单元格 F4单元格的公式会变为 SUM C4 E 3 若将公式从F3单元格复制到G4单元格 G4单元格的公式会变为 SUM C4 F 3 混合引用在实际工作中的应用不是很多 4 2 2项目知识准备 3 图表Excel中的图表分两种 一种是嵌入式的图表 它和创建图表的数据源放置在同一个工作表中 另一种是独立式图表 它独立于数据表 单独存在于一个工作表中 图表的默认名称为 Chart1 在Excel2010软件中 用户可以选择 插入 功能区的 图表 组中的某一图表类型 创建各种图表 1 Excel的图表类型Excel提供了面积图 条形图 柱形图 折线图 股价图 饼形图 雷达图和XY散点图等图表类型 每一种都具有多种组合和变换 在实际应用中 可以根据数据的不同和使用要求的不同 选择不同类型的图表 创建图表后 如果对图表不满意 还可以将其转换为其他的图表类型 首先选中要修改的图表 在 图表工具 设计 功能区的 类型 组中单击 更改图表类型 按钮 在打开的对话框中选择所需的图表类型和子类型即可 4 2 2项目知识准备 2 图表中数据的编辑当创建了图表后 图表和创建图表的工作表中的数据区域之间便建立了联系 如果工作表中的数据发生了变化 则图表中对应的数据也会自动更新 1 删除数据系列当要删除图表中的数据系列时 只要在图表中选定所需删除的数据系列 按 Delete 键 便可将整个数据系列从图表中删除 且不影响工作表中的数据 若删除工作表中的数据 则图表中对应的数据系列也随之删除 2 向图表中添加数据系列 单击图表 然后在 图表工具 设计 功能区的 数据 组中单击 选择数据 按钮 将弹出 选择数据源 对话框 如图4 2 5所示 4 2 2项目知识准备 图4 2 5 选择数据源 对话框 单击 添加 按钮 在出现的对话框中根据提示选择好需要添加的数据和该数据系列的名称 单击 确定 按钮即可完成添加 4 2 3项目实施 1 学生总分的计算2 总评成绩的计算3 计算排名4 求每门课程的平均成绩5 求计算机课程的最高分 最低分6 统计总分低于200分的人数7 将计算机成绩低于60分的单元格底纹设置为红色8 通过创建图表对学生的总评成绩进行分析 4 3数据检索 学生成绩信息查询 信息检索是数据管理的基本操作之一 在Excel软件中可以利用查找 排序和筛选命令来帮助人们从保存的大量数据中快速查询所需的信息 4 3 1项目描述 小刘是信息工程学院的辅导员 他想了解本院学生的成绩信息 学生成绩信息表如图4 3 1所示 查询姓名为 刘丽娟 的学生的成绩信息 查询学生总分由高到低的情况 查询各专业计算机成绩由高到低的情况 查询总分在300 325之间的学生信息 查询动漫设计专业总分高于320分或英语成绩大于90分的学生信息 4 3 1项目描述 图4 3 1学生成绩信息表 4 3 2项目知识准备 1 排序排序可以让杂乱无章的数据按一定的规律排列 从而加快数据查询的速度 在Excel2010中 排序操作可通过单击 开始 功能区的 编辑 组中的 排序和筛选 按钮 在弹出的下拉菜单中选择 升序 或 降序 命令实现单关键字的简单排序 也可通过单击 数据 功能区的 排序和筛选 组中的 排序 按钮 实现多关键字的复杂排序 下面列出了按递增方式排列时各类数据的顺序 递减排列的顺序与递增顺序相反 但空白单元格仍将排在最后 数字的顺序是从小数到大数 从负数到正数 文字和包含数字的文本排列顺序为0 9 A Z 逻辑值的排列顺序是False Ture 对于错误值 所有的都是相等的 空白 不是空格 单元格总是排在最后 4 3 2项目知识准备 2 筛选筛选操作可以帮助人们在海量数据中将不关心的数据暂时隐藏起来 只显示所需的信息 筛选操作可通过单击 开始 功能区的 编辑 组中的 排序和筛选 按钮 在下拉菜单中选择 筛选 命令 实现数据自动筛选 也可通过单击 数据 功能区的 排序和筛选 组中的 筛选 按钮实现设置多个查询条件的高级筛选 1 自动筛选筛选条件的正确设置是筛选操作能成功完成的重要保证 自动筛选可以在筛选器界面中的 搜索 文本框中输入关键字来对数据进行筛选 也可根据单元格填充颜色或文本的颜色来筛选数据 还可根据所在列的数据类型设置文本条件或数字条件进行筛选 如图4 3 2所示 4 3 2项目知识准备 图4 3 2自动筛选条件设置 4 3 2项目知识准备 2 高级筛选高级筛选一般用于条件较复杂的筛选操作 其筛选的结果可显示在原数据表格中 不符合条件的记录被隐藏起来 也可以在新的位置显示筛选结果 不符合条件的记录保留在数据表中而不会被隐藏起来 这样就更加便于进行数据的比对 高级筛选的条件设置需要用户手工输入到工作表的相应单元格中 高级筛选的条件设置区域可以在原数据的四周 但至少应与原数据保持一行或一列的间隔 通常设置在原数据区域的下方 常见的高级筛选条件区域设置形式有如下几种 单列上具有多个条件 满足其中之一 可以在各列中从上到下依次输入各个条件 例如 显示学历为硕士或博士的员工信息 如图4 3 3所示 4 3 2项目知识准备 图4 3 3单列上具有多个条件 满足其中之一 图4 3 4多列上的单个条件 同时满足 多列上的单个条件 同时满足 在条件区域的同一行中输入所有条件 例如 显示学历为硕士并且职务为主管的员工信息 如图4 3 4所示 4 3 2项目知识准备 多列上的单个条件 满足其中之一 在条件区域的不同行中输入所有条件 例如 显示学历为博士或者职务为主管的员工信息 如图4 3 5所示 图4 3 5多列上的单个条件 满足其中之一 4 3 3项目实施 1 查询姓名为 刘丽娟 的学生的成绩信息2 查询学生总分由高到低的情况3 查询各专业计算机成绩由高到低的情况4 查询总分在300 325之间的学生信息5 查询动漫设计专业总分高于320分或英语成绩大于90分的学生信息 4 4数据综合管理 销售业绩处理 分类汇总 合并运算和数据透视在数据管理中扮演着重要角色 本节以汇总公司的销售业绩为例讲述Excel中此部分知识的应用 4 4 1项目描述 小刘是一家家电企业的主管 他想了解企业产品销售的如下信息 各地区2013年的销售总额情况 各产品的平均销售数量和平均销售额 按照销售日期对各类商品在各个地区的销售情况进行深入分析 各分公司销售业绩汇总 4 4 2项目知识准备 1 分类汇总在实际应用中 分类汇总经常用到 如统计各个班级各门课程的平均分 或者统计每位教师的教学任务等 它们的共同特点是 首先要进行分类 将同类别数据放在一起 然后进行数量求平均或求和之类的汇总运算 Excel分类汇总的方式不但有求和 还有求均值 计数 求最大值 最小值 求