实践操作excel电子表格ppt课件.ppt

实践操作环节excel2010电子表格 1 Excel大作业要求 以小组为单位 自选题材完成 管理 项目 模仿实践指导书的操作案例 实现一个 学生成绩管理 或 职工工资管理 项目 项目只提出必须实现的功能和效果 但具体实现的方法和过程可以自由选择和发挥 2 Excel大作业要求 学生成绩管理 项目建立包含学生相关信息的数据表格信息 输入符合统计汇总要求的相应基础数据 学生人数不得少于20人 课程不得少于4门 专业不得少于5个 确保信息输入的有效性和界面友好型 专业由下拉菜单选择输入 学生的各门课程的成绩来源 平时成绩 期中成绩和期末成绩 总评成绩 平时 20 期中 30 期末 50 将学生各门课程的最后得分汇总到一个工作表 要求用vlookup函数实现 并计算各门课程的单项平均分 每个学生的多项平均分以及排名情况 利用函数统计学生的各门课程平均分超过85分的人数 使用条件格式功能标示出 单项分值均超过单项平均分 的学生 挑选部分学生的成绩进行图表分析比较 并将图表存放在单独的工作表中 生成排名前二十的各个专业的人数分布情况饼图 并将图表存放在单独的工作表中 输出排名在前十名的学生成绩信息和不及格学生的名单 要求使用自动套用格式功能 3 Excel大作业要求 职工工资管理 项目 建立包含职工相关信息的数据表格信息 输入符合统计汇总要求的相应基础数据 职工人数不得少于20人 部门不得少于5个 确保信息输入的有效性和界面友好型 部门由下拉菜单选择输入 职工的工资来源 基本工资 职务津贴 绩效津贴 工龄津贴和纳税扣除等 纳税扣除 基本工资 职务津贴 绩效津贴 工龄津贴 20 计算职工的实发工资 并统计各项的平均值 按实发工资升序排列 并将实发工资最低的前十人信息的字体用蓝色表示 输出低工资 2000元以下的 职工名单 要求使用自动套用格式功能 统计按部门分类的工资汇总情况 生成各个部分的工资分布情况饼图 并将图表存放在单独的工作表中 4 概述数据输入单元格编辑格式设置创建名称公式概述 单元格的引用函数排序筛选汇总图表分析 操作准备 5 通过自定义填充序列和数据有效性下拉列表 可以提高数据输入的速度和准确性 自定义下拉列表输入建立方法数据 数据有效性 数据有效性 允许 序列 普通方法设置 直接键入列表选项使用名称设置 使用名称建立列表选项 操作准备 数据输入 6 自定义序列与填充柄自定义序列 一组数据 可按重复方式填充列 创建自定义序列的方法文件 选项 高级 常规 编辑自定义列表利用工作表中现有的数据项采用临时输入的方式创建好自定义序列之后 采用填充柄进行填充能达到快速 有效的结果 操作准备 数据输入 7 自定义序列与填充柄 操作准备 数据输入 8 概述数据输入单元格编辑格式设置创建名称公式概述 单元格的引用函数排序筛选汇总图表分析 操作准备 9 条件格式作用 通过设置数据条件格式 可以让单元格中的数据满足指定条件时就以特殊的标记 如 以红色 数据条 图标等 显示出来 设置方法 开始 样式 条件格式 新建 管理 规则套用样式可以套用系统内置样式 快速地装饰选中区域的单元格的边框 底纹和字体等格式 操作准备 格式设置 10 条件格式 操作准备 格式设置 11 概述数据输入单元格编辑格式设置创建名称公式概述 单元格的引用函数排序筛选汇总图表分析 操作准备 12 创建名称及其使用在Excel中 可以通过一个名称来代表工作表 单元格 常量 图表或公式等 创建方法 公式 定义名称 定义名称定义了一个名称之后 就可以在公式中直接使用它 操作准备 创建名称 13 创建名称及其使用定义了一个名称之后 就可以在公式中直接使用它 操作准备 创建名称 14 概述数据输入单元格编辑格式设置创建名称公式概述 单元格的引用函数排序筛选汇总图表分析 操作准备 15 公式的定义 对工作表中的数值进行计算的式子 公式的组成 由操作符和运算符组成 操作符可以是常量 名称 数组 单元格引用和函数等 运算符用于连接公式中的操作符 是处理数据的指令 算术运算符 逻辑运算符 文本运算符 引用运算符每个运算符都有一个优先级 按照从高到低的优先级顺序进行计算 对于同一优先级的运算 按照从左到右的书讯进行计算 操作准备 公式概述 16 算术运算符 加 减 乘 除 百分号 乘方 比较运算符 不等于 文字运算符 连接字符 引用运算符 冒号 连续区域引用 逗号 离散区域引用 空格 交叉区域引用 感叹号 三维区域引用 操作准备 公式概述 17 概述数据输入单元格编辑格式设置创建名称公式概述 单元格的引用函数排序筛选汇总图表分析 操作准备 18 单元格的引用相对引用绝对引用混合引用三维引用循环引用 操作准备 单元格引用 19 相对引用总是以当前单元格位置为基准 在复制公式时 当前单元格改变了 单元格引用的地址也随之发生变化 相对引用的表示方法 直接写列字母和行号 如A1 D8等 绝对引用在复制公式时 不想改变公式中的某些数据 即所引用的单元格地址在工作表中的位置固定不变 它的位置与包含公式的单元格无关 绝对引用的表示方法 在相应的单元格地址的列字母和行号前加 符号 如 A 1 操作准备 单元格引用 20 混合引用公式中参数的行采用相对引用 列采用绝对引用 或者列采用相对引用 行采用绝对引用 如 A1 A 1 三维引用引用工作簿中其它工作表的单元格 三维引用的一般格式为 工作表标签 单元格引用 如 进货清单 B 2 B 操作准备 单元格引用 21 概述数据输入单元格编辑格式设置创建名称公式概述 单元格的引用函数排序筛选汇总图表分析 操作准备 22 如果公式中要使用函数 可以直接输入函数名和参数 也可以通过以下方式编辑函数 公式 选项卡 函数库 组 单击组中的某类函数选项 在其下拉列表中选择所需的函数 通过 函数参数 对话框 编辑参数 公式 选项卡 函数库 组 单击 自动求和 选项 可以选择求和 平均值 计数 最大值 最小值以及其他函数的计算 公式 选项卡 函数库 组 单击 插入函数 选项 可以打开 插入函数 对话框 同样可以实现插入和编辑函数 操作准备 函数 23 操作准备 函数 24 操作准备 函数 25 操作准备 函数 26 操作准备 函数 27 统计函数SUM函数 SUMIF函数AVERAGE函数 AVERAGEIF函数COUNT函数 COUNTIF函数RANK EQ函数 RANK AVG函数 操作准备 函数 28 SUM函数功能 该函数是返回指定参数所对应的数值之和 格式 SUM number1 number2 参数 number1 number2等这些是指定所要进行求和的参数 参数类型可以是数字 逻辑值和数字的文字表示等形式 操作准备 函数 29 SUMIF函数功能 该函数是用于计算符合指定条件的单元格区域内的数值进行求和 格式 SUMIF range criteria sum range 参数 range表示的是条件判断的单元格区域 criteria表示的是指定条件表达式 sum range表示的是需要计算的数值所在的单元格区域 如果省略sum range参数 Excel会对在范围参数中指定的单元格 即应用条件的单元格 求和 操作准备 函数 30 AVERAGE函数功能 该函数是返回指定参数所对应数值的算术平均数 格式 AVERAGE number1 number2 参数 number1 number2等这些是指定所要进行求平均值的参数 该函数只对参数的数值求平均数 如区域引用中包含了非数值的数据 则AVERAGE不把它包含在内 操作准备 函数 31 AVERAGEIF函数功能 该函数返回某个区域内满足给定条件的所有单元格的平均值 算术平均值 操作准备 函数 32 COUNT函数功能 该函数用于返回数值参数的个数 即统计数组或单元格区域中含有数值类型的单元格个数 COUNTIF函数功能 该函数是用于计算区域内符合给定条件的单元格的数量 操作准备 函数 33 RANK EQ函数功能 返回一个数值在一组数值中的排位 如果多个值具有相同的排位 则返回该组数值的最高排位 格式 RANK EQ number ref order 参数 Number需要排名的数据Ref排名范围 列表数组或引用 Order排名升降序方式 取0或缺省 为降序 取1为升序 操作准备 函数 34 RANK AVG函数功能 也是返回一个数字在列表中的排位 如果多个值具有相同的排位 则将返回平均排位 在前面的例子中 RANK AVG A4 A1 A5 1 等于3 5 操作准备 函数 35 逻辑判断函数IF函数AND函数OR函数NOT函数 操作准备 函数 36 IF函数功能 是一个条件函数 条件满足则输出value if true 不满足则输出value if false 格式 IF logical test value if true value if false 参数 Logical test 函数的逻辑条件式Value if true 当值为 真 时的返回值Value if false 当值为 假 时的返回值三个参数中可以省略后两者之一 但不能同时省略 在IF函数中使用嵌套函数 最多可用嵌套7层 操作准备 函数 37 AND函数的应用And logical test logical test logical test Logical test 函数的逻辑条件式所有条件式的值为真 结果为真OR函数的应用or logical test logical test logical test Logical test 函数的逻辑条件式只要有一个条件式的值为真 结果为真NOT函数的应用Not logical test Logical test 函数的逻辑条件式条件式值为真 结果为假 条件式值为假 结果为真 操作准备 函数 38 文本处理类函数REPLACE函数MID函数LEFT函数RIGHT函数 操作准备 函数 39 REPLACE函数功能 将某几位的文字以新的字符串替换 其替换功能与SUBSTITUTE函数大致类似 格式 REPLACE old text start num num chars new text 参数 old text 原始的文本数据start num 设置要从old text的第几个字符位置开始替换num chars 设置共有多少字符要被替换new text 要用来替换的新字符串 操作准备 函数 例 REPLACE 你好 同学好 老师好 4 6 大家 值为 你好 大家好 40 MID函数功能 用来返回在指定字符串中从指定位置开始截取指定数目的字符串 格式 MID text start num num chars 参数 text 文本字符串start num 从text的第几个字符开始截取num chars 截取字符串的个数 操作准备 函数 例 MID 你好 同学好 老师好 4 4 结果为 同学好 41 LEFT函数功能 用来返回在指定字符串中从左边位置开始截取指定数目的字符串 格式 LEFT text num chars 参数 text 文本字符串num chars 截取字符串的个数 操作准备 函数 例 LEFT 你好 同学好 老师好 3 结果为 你好 42 RIGHT函数功能 用来返回在指定字符串中从右边位置开始截取指定数目的字符串 格式 RIGHT text num chars 参数 text 文本字符串num chars 截取字符串的个数 操作准备 函数 例 RIGHT 你好 同学好 老师好 4 结果为 老师好 43 日期时间类函数NOW TODAY函数DATE函数DAY MONTH YEAR函数WORKDAY WEEKNUM函数TIME函数HOUR MINUTE SECOND函数 操作准备 函数 44 NOW函数功能 返回当前系统的日期和时间 格式 NOW 无参数 TODAY函数功能 返回当前系统的日期 格式 TODAY 操作准备 函数 45 DATE函数功能 计算某一特定日期的系列编号 格式 DATE year month day 参数 year 指定年份 介于0 1899之间的值 系统会自动加上1990 month 每年中月份的数字 如果值大于12 则将从指定年份一月份开始往上累加 day 在该月份中第几天的数字 如果值大于该月份的最大天数 则将从指定月数的第一天开始往上累加 操作准备 函数 46 DAY函数功能 返回指定日期所对应的当月中的第几天的数值 介于1 31之间格式 DAY serial number 参数 serial number指定的日期或数值参数serial number使用的是日期输入参数serial number使用的是数值的输入在Excel中 系统将1900年1月1日对应于序列号1 后面的日期都是相对于这个时间进行对序列号的进行累加 操作准备 函数 47 MONTH函数功能 返回指定日期所对应的当月数值 介于1 12之间 格式 MONTH serial number 参数 serial number 指定的日期或数值YEAR函数功能 返回指定日期所对应的年的数值 介于1900 9999之间 格式 YEAR serial number 参数 serial number 指定的日期或数值 操作准备 函数 48 TIME函数功能 返回某一特定时间的小数值 它返回的小数值从0 0 99999999之间 代表0 00 00 12 00 00A M 23 59 59 11 59 59P M 之间的时间格式 TIME hour minute second 参数 hour 0 23之间的数 代表小时minute 0 59之间的数 代表分second 0 59之间的数 代表秒 操作准备 函数 49 HOUR MINUTE SECOND函数功能 返回指定时间所对应的小时 分钟和秒数值格式 HOUR serial number 格式 MINUTE serial number 格式 SECOND serial number hour 0 23之间的数 代表时minute 0 59之间的数 代表分second 0 59之间的数 代表秒 操作准备 函数 50 查找与引用函数在一个工作表中 可以利用查找与引用函数功能按指定的条件对数据进行快速查询 选择和引用 查找与引用函数用于查找 查看 列表或表格中的值 操作准备 函数 51 VLOOKUP函数功能 从一个数组或表格的最左列中查找含有特定值的字段 再返回同一列中某一指定单元格中的值 格式 VLOOKUP lookup value table array col index num range lookup 参数 look value 要搜索的数据 它可以是数值 引用地址或文字字符串 table array 要搜索的数据表格 数组或数据库 col index num 是一个数字 代表要返回的值位于table array中的第几列 操作准备 函数 52 VLOOKUP函数格式 VLOOKUP lookup value table array col index num range lookup 参数 rang lookup是个逻辑值 如果其值为 TRUE 或被省略 则返回精确匹配值或近似匹配值 如果找不到精确匹配值 则返回小于lookup value的最大值 如果该值为 FALSE 时 则只查找精确匹配值 如果table array的第一列中有两个或更多值与lookup value匹配 则使用第一个找到的值 如果找不到精确匹配值 则返回错误值 N A 操作准备 函数 如果range lookup为 TRUE 则table array第一列的值必须以递增次序排列 这样才能找到正确的值 如果rang lookup是 FALSE 则table array不需要先排序 53 操作准备 函数 54 操作准备 函数 55 数据库函数DCOUNT函数DGET函数DAVERAGE函数DMAX函数DPRODUCT函数DSUM函数 操作准备 函数 56 数据库函数数据库函数是用于对存储在数据清单或数据库中的数据进行分析 判断其是否符合特定的条件 根据各自函数所具有的功能不同 可分为两大类 数据库信息函数 直接获取数据库中的信息数据库分析函数 分析数据库的数据信息 操作准备 函数 数据库函数格式为 函数名称 database field criteria 数据库函数的参数含义database 构成数据清单或数据库的单元格区域field 指定函数所使用的数据列criteria 一组包含给定条件的单元格区域 57 数据库函数DCOUNT函数 返回列表或数据库中满足指定条件的记录字段 列 中包含数值的单元格的个数 DGET函数 从列表或数据库的列中提取符合指定条件的单个值 DCOUNTA函数 返回列表或数据库中满足指定条件的记录字段 列 中非空单元格的个数 操作准备 函数 58 操作准备 函数 59 数据库函数DAVERAGE函数 计算列表或数据库的列中满足指定条件的数值的平均值 DMAX函数 返回列表或数据库的列中满足条件的最大值 DPRODUCT函数 返回列表或数据库中满足指定条件的记录字段 列 中数值的乘积 DSUM函数 返回列表或数据库中满足指定条件的记录字段 列 中的数字之和 操作准备 函数 60 操作准备 函数 61 概述数据输入单元格编辑格式设置创建名称公式概述 单元格的引用函数排序筛选汇总图表分析 操作准备 62 普通排序数据 排序和筛选 排序主要关键字 选择排序字段 按升序或降序排序 次要关键字 主要关键字 列中出现了重复项 就按次要关键字来排序重复的部分 次要关键字 主要关键字 与 次要关键字 列中都出现了重复项 就按此次要关键字来排序重复的部分 以此类推 有标题行 排序时包含列表第一行 无标题行 排序时不包含列表第一行 操作准备 数据排序 63 概述数据输入单元格编辑格式设置创建名称公式概述 单元格的引用函数排序筛选汇总图表分析 操作准备 64 数据筛选是一种用于查找数据的快速方法 筛选将数据列表中所有不满足条件的记录暂时隐藏起来 只显示满足条件的数据行 以供用户浏览和分析 Excel提供了自动和高级两种筛选数据的方式 操作准备 数据筛选 65 自动筛选自动筛选为用户提供了在具有大量记录的数据列表中快速查找符合某些条件的记录的功能 筛选后只显示出包含符合条件的数据行 而隐藏其它行 选择 数据 排序和筛选 筛选 数据列表中第一行的各列中将分别显示出一个下拉按钮 自动筛选就将通过它们进行 单击需要进行筛选的列标的下拉列表 Excel会显示出该列中所有不同的数据值 这些值可用于筛选条件 操作准备 数据筛选 66 图5 12自动筛选前10个 图5 13自动筛选前10个后的结果 操作准备 数据筛选 67 高级筛选对于筛选条件较多的情况 可以使用高级筛选功能来处理 必须先建立一个条件区域 用来指定筛选条件 条件区域的第一行是所有作为筛选条件的字段名 这些字段名与数据列表中的字段名必须一致 条件区域的其它行则输入筛选条件 需要注意的是 条件区域和数据列表不能连接 必须用以空行或空列将其隔开 条件区域的构造规则是 同一列中的条件是 或 同一行中的条件是 与 操作准备 数据筛选 68 图5 15高级筛选 操作准备 数据筛选 69 概述数据输入单元格编辑格式设置创建名称公式概述 单元格的引用函数排序筛选汇总图表分析 操作准备 70 分类汇总是对数据列表指定的行或列中的数据进行汇总统计 统计的内容可以由用户指定 通过折叠或展开行 列数据和汇总结果 从汇总和明细两种角度显示数据 可以快捷的创建各种汇总报告 在这一节中 我们将使用分类汇总来完成任务3 操作准备 分类汇总 71 Excel可自动计算数据列表中的分类汇总和总计值 当插入自动分类汇总时 Excel将分级显示数据列表 以便为每个分类汇总显示或隐藏明细数据行 Excel分类汇总的数据折叠层次最多可达8层 分类汇总可以完成以下工作 显示一组数据的分类汇总及总和 显示多组数据的分类汇总及总和 在分组数据上完成不同的计算 如求和 统计个数 求平均值 或最大值 最小值 求总体方差等 操作准备 分类汇总 72 取消删除分类汇总如果由于某种原因 需要取消分类汇总的显示结果 恢复到数据列表的初始状态 其操作步骤如下 步骤1 单击分类汇总数据列表中任一单元格 步骤2 选择 数据 分级显示 分类汇总 命令 打开 分类汇总 对话框 步骤3 单击对话框中的 全部删除 按钮即可 经过以上步骤之后 数据列表中的分类汇总就被删除了 恢复成汇总前的数据 第3步中的 全部删除 只会删除分类汇总 不会删除原始数据 操作准备 分类汇总 73 概述数据输入单元格编辑格式设置创建名称公式概述 单元格的引用函数排序筛选汇总图表分析 操作准备 74 使用图表 可以将抽象的数据以图形化的方式形象直观地展示出来 Excel提供了多种类型的分析数据的图表 普通图表 数据透视图表 迷你图和切片器 操作准备 图表分析 75 普通图表首先需要选中数据区域 然后通过 插入 选项卡的 图表 组 可以选择各种类型的图表 根据 图表工具 的 设计 布局 和 格式 选项卡 可以选择图表类型 图表源数据 设置图表选项 指定图表位置以及图标样式等 图表创建以后 点击图表可以方便地在图表上直接选择要修改的部分进行修改 操作准备 普通图表 76 操作准备 普通图表 77 操作准备 普通图表 78 操作准备 普通图表 79 操作准备 普通图表 80 操作准备 普通图表 81 操作准备 普通图表 82 数据透视表是一种对大量数据快速汇总和建立交叉列表的交互式表格 不仅能够改变行和列以查看源数据的不同汇总结果 也可以显示不同页面以筛选数据 还可以根据需要显示区域中的明细数据 数据透视图则是一个动态的图表 它可以将创建的数据透视表以图表的形式显示出来 在这一节中我们将使用数据透视表和数据透视图来完成任务4 数据准备 数据透视表 83 数据准备 数据透视表 84 图5 23数据透视表示例 数据准备 数据透视表 85 数据透视表的创建可以通过 插入 表格 数据透视表或数据透视图 数据准备 数据透视表 86 数据准备 数据透视表 图5 24向导步骤1 87 图5 28生成的数据透视表 数据准备 数据透视表 88 创建好数据透视表之后 根据需要有可能要对它的布局 数据项 数据汇总方式与显示方式 格式等进行修改 修改数据透视表的布局修改数据透视表的数据项隐藏或显示行 列中的数据项调整数据项显示的位置修改数据透视表的数据汇总方式和显示方式修改数据透视表的格式 数据准备 数据透视表 89 数据准备 数据透视表 图5 29修改数据透视表的布局 90 在默认情况下 数据透视表采用SUM函数对其中的数值项进行汇总 用COUNT函数对文本类型字段项进行计数 但有时SUM和COUNT函数并不能满足透视需要