EXCEL高级版教程.ppt

1 Excel2003 杨结成 isthe isthe isthe isthe isthe isthe 续表 isthe isthe isthe isthe isthe isthe isthe isthe isthe isthe isthe largesteconomy isthe 2rd 函数 Excel中的公式及函数 公式是Excel中数值关系的具体体现 构成了Excel的核心 正确地使用任何一个公式必须明确地把握下面三个因素 1 公式中的参数 公式中需要哪些参数 自变量来源于何外 公式如何引用其他区域或单元格2 公式的运算 公式的运算实质性机理是什么 有什么约束条件3 公式运算的结果表现 公式运算的结果表现为什么 其格式如何 Excel中公式的输入 Excel中的公式通常都是以 开头的一个运算式或函数式 可以将 引导符看成是Excel公式的标志 例如 G5单元中有如下内容 G1 G3 G4 2又如 N9单元中有如下内容 SUM A9 M9 这些都是Excel中最简单的公式示例 Excel公式中的参数 Excel中公式中的参数有下列三种情况 1 常数 公式中使用某些常数来参与运算 或由常数指明公式的运算方式2 系统变量 公式的某些变量来源于计算机系统的自身参数 如机器时间等3 地址引用 对其他工作溥 工作表 区域或单元格的内容的引用 这种引用在是根据被引用对象的位置进行的 所以Excel中又称之为地址引用 Excel公式中对地址的引用方式 在Excel中公式对其他地址的引用分为三种情况 1 相对引用 即对另一单元的地址引用是按与当前单元的特定距来确定的 这种引用在该公式被复制时会自动调整2 绝对引用 即对被引用单元采用绝对的行列号来引用 公式不会自动调整3 混合引用 行或列之一采用绝对引用如果有区域或单元格命名存在 还可以按名引用 1 使用公式 公式中的运算符有以下四类 1 算术运算符 2 比较运算符 3 文本运算符 4 引用运算符 函数 公式和函数 使用函数 Excel有丰富的内部函数 其类型包括 财务函数 日期与时间函数 数学与三角函数 统计函数 查找与引用函数 数据库函数 文本函数 逻辑函数 信息函数 1 函数的输入 加enter结束 数组函数 公式 shift ctrl enter结束 2 自动求和 3 自动计算 4 函数复制 EXCEL函数的结构 Excel函数即是预先定义 执行计算 分析等处理数据任务的特殊公式 Excel函数结构 也有一些函数是没有参数的 如ROW 左右括号成对出现 单一结构 嵌套结构 参数与参数之间使用半角逗号进行分隔 1 相对引用2 绝对引用3 三维地址引用5 Vlookup的使用 单元格引用 主要内容 EXCEL的规范化操作EXCEL函数与数据分析 EXCEL表格的规范化操作 构思和设置表结构建立和输入数据编辑和修饰表格简单使用和分析保存表格数据打印输出数据 制表的六个基本过程 管理项目与表格结构的关系 正确认识管理对象的独立性 准确拆分管理项目至最小管理单元 并建立管理项目名称与表格结构中 列 的对应关系 为数据管理提供合理的数据结构 此项工作的关健在于明确表格列与行的逻辑关系 以便有效组织一个独立的管理项目 形成可供分析的表格 案例1 规范表格示例 xls 表格设计应注意的事项 管理项目与表格结构的关系凡须独立管理的项目 必须单独设置项目名称 独占一列 避免混合设置管理项目的独立性必须与扩展性结合考虑 以方便统计分析表格结构设计必须考虑后期统计分析的应用状态 案例2 成绩表 xls 表格数据的输入与准确性控制 为什么要提出准确性哪些数据容易出错控制原始数据输入准确性的解决方法用调用法控制原始数据的准确性用计算法控制加数据的准确性使用数据有效性使用校对工具 单元格的基本操作 单元格和单元格区域的选定方法使用名称框选定和命名单元格区域单元格的编辑操作选择性粘贴的使用 演示转置和加法 原始数据的输入 在单元格中输入数值 文本 日期 时间 重点讲日期 序列填充的方法自定义序列同时在多个单元格中输入数据数据有效性的设置使用下拉列表输入数据的方法使用多级下拉列表输入数据 表格文档的保存技巧 正确使用 保存 命令何时使用 另存为 命令使用 保存工作区 命令对文件加密的保存方法 正确使用 保存 命令 新文件的保存规律和方法 新文件 是通过 新建 命令打开EXCEL窗口 并在其中制作的表格文件 通常窗口 标题栏 中按新建顺序自动编号的文件名 book1 xls book2 xls 对于新文件 无论使用 保存 还是 另存为 都显示另存为对话框旧文件保存与 保存 命令旧文件 有文件名称 每按一次保存 执行一次覆盖过程 新的替代旧的 设置备份保存 何时使用 另存为 命令 更名保存将文件存到指定位置改变文件的保存类型使用模板文件 如何使用 保存工作区 工作区 也称 工作面 是指WINDOWS桌面上正在使用的若干份电子表格文件保存工作区时 原文件按原名进行保存 工作区文件只保存这些文件的位置的存储路径 相当于快捷方式 表格数据的打印与输出 常规表格的打印输出连续页面打印打印区域设置添加到打印区域超大表格打印 常规表格页面控制 小表格的打印设置面积较小 不足一张纸 可以使用居中大表格的打印设置超出一张纸 但超出有限 可以使用缩放的方式 有效处理超大表格 设置跨页打印表头设置屏幕冻结表头强制分屏显示 拆分窗口 强制分页打印 使用分页符 设置打印顺序 页面设置 工作表 设置页眉和页脚 Excel菜单功能和必会命令 EXCEL函数与数据分析 公式和函数入门排序筛选分类汇总实用函数讲解 公式和函数 公式的使用公式是用运算符将数据 单元格地址 函数等连接在一起的式子 以等号 开头 5 10 20常数运算 A6 B1对单元格A6和B1中的值相加 SQRT 10 A2 使用Excel函数 SQRT表示求开方根1 运算符 算术运算符 文本运算符和关系运算符 公式举例 8 3 25 表示8的立方再乘以0 25 结果为128 5 8 2 3 B2 4若单元格B2的值是12 则结果为2 计算机 电脑 运算结果是 计算机电脑 A1 A2把单元格A1和A2中的文本进行连接而生成一个新的文本 B2 42比较结果为TRUE 设B2的值是12 B2 32比较结果为FALSE B2 12比较结果为FALSE A3 80假设A3为日期型数据 则A3 80表示日期A3加上80天 用于描述一个条件 例如 5 ABCD 或写成 ABCD 计算机 或写成 计算机 或写成计算机 比较条件式 当在一个单元格中输入一个公式后 Excel会自动加以运算 并将运算结果存放在该单元中 以后当公式中引用的单元格数据发生变动时 公式所在单元格的值也会随之变动例1计算出学生的总评成绩 机试 40 笔试 60 案例文件 计算机成绩 xls 1 单击总评成绩第一个单元格E2 使之成为活动单元格 2 键入计算公式 C2 0 4 D2 0 6 后按回车键 此时在单元格E2处显示出计算结果82 2 3 再次单击单元格E2使之成为活动单元格 单击 复制 按钮 4 选定区域E3 E6 单击 粘贴 按钮 2 公式的输入和复制 四种表示方式 1 相对地址 以列标和行号组成 如A1 B2 C3等公式复制时 若引用公式的单元格地址发生变动 公式中的相对地址会随之变动 2 绝对地址 以列标和行号前加上符号 构成 如 A 1 B 2等 公式复制时 公式中的绝对地址保持不变 下面式子表示引用d book2 xls的sheet1的B5单元格 d book2 xls Sheet1 B5 3 混合地址 它是上述两种地址的混合使用方式如 A1 绝对列相对行 A 1 相对列绝对行 等 公式复制时 公式中相对行和相对列部分会随引用公式的单元格地址变动而变动 而绝对行和绝对列部分保持不变 4 名称地址 事先定义好的单元格区域 5 三级地址 演示各部销售统计 xls出错信息 当公式 或函数 表达不正确时 系统将显示出错信息 单元格地址表示方式 常见错误信息 格式 举例 SUM C2 C6 AVERAGE A1 A3 C2 C6 SUM求和数AVERAGE求平方值MAX求最大值MIN求最小值COUNT求数值数据的个数COUNTIF条件求数值数据的个数IF条件选择 函数的使用 例2计算基本工资总数 并将结果存放在单元格C7中 1 单击单元格C7 使之成为活动单元格 2 键入公式 SUM C2 C6 并按回车键例3计算补贴总数和扣除总数 并将结果分别存放在单元格D7和E7中 1 选定D2 E7区域 2 单击 自动求和 按钮 即可对这两列分别求和 和数分别存放在两列的最后一个单元格上 案例文件 工资表 xls 函数应用举例 例4计算基本工资的平均值 并将结果存放在C8中例5利用函数MAX求出基本工资数的最大值 并将结果存放在单元格C9中举例假设单元格A1 A2 A3 A4的内容分别为1 2 空 ABC 则COUNT A1 A4 的值为2举例若要求出基本工资数大于等于500的职工人数 可以采用 COUNTIF C2 C6 500 举例在工作表 A1 D9 中 要求出班号为21的班学生人数 可以采用 COUNTIF D2 D9 21 或 COUNTIF D2 D9 D3 函数应用举例 假设函数If 格式 IF logical test value if true value if false 功能 本函数对比较条件式 logical test 进行测试 如果条件成立 则取第一个值 value if true 否则取第二个值 value if false 举例已知单元格E3中存放考试分数 现要根据该分数判断学生是否及格 IF E3 60 不及格 及格 例如成绩等级与分数的关系 成绩 80 优良 60 成绩 80 中 成绩 60 不及格 假设成绩存放在单元格D3中 则可以采用如下函数来实现判断和取得等级信息 IF D3 80 优良 IF D3 60 中 不及格 练习案例 if函数练习 xls COUNTIF函数函数名称 COUNTIF主要功能 统计某个单元格区域中符合指定条件的单元格数目 使用格式 COUNTIF Range Criteria 参数说明 Range代表要统计的单元格区域 Criteria表示指定的条件表达式 应用举例 在C17单元格中输入公式 COUNTIF B1 B13 80 确认后 即可统计出B1至B13单元格区域中 数值大于等于80的单元格数目 特别提醒 允许引用的单元格区域中有空白单元格出现 练习案例 if函数练习 xls DAY函数函数名称 DAY主要功能 求出指定日期或引用单元格中的日期的天数 使用格式 DAY serial number 参数说明 serial number代表指定的日期或引用的单元格 应用举例 输入公式 DAY 2003 12 18 确认后 显示出18 特别提醒 如果是给定的日期 请包含在英文双引号中 LEFT函数函数名称 LEFT主要功能 从一个文本字符串的第一个字符开始 截取指定数目的字符 使用格式 LEFT text num chars 参数说明 text代表要截字符的字符串 num chars代表给定的截取数目 应用举例 假定A38单元格中保存了 我喜欢天极网 的字符串 我们在C38单元格中输入公式 LEFT A38 3 确认后即显示出 我喜欢 的字符 特别提醒 此函数名的英文意思为 左 即从左边截取 Excel很多函数都取其英文的意思 MAX函数函数名称 MAX主要功能 求出一组数中的最大值 使用格式 MAX number1 number2 参数说明 number1 number2 代表需要求最大值的数值或引用单元格 区域 参数不超过30个 应用举例 输入公式 MAX E44 J44 7 8 9 10 确认后即可显示出E44至J44单元和区域和数值7 8 9 10中的最大值 特别提醒 如果参数中有文本或逻辑值 则忽略MAX A1 3500 5 0 6 2 4 5 6 7 9 0 21 111 201 551 1101 2701 5 MIN函数函数名称 MIN主要功能 求出一组数中的最小值 使用格式 MIN number1 number2 参数说明 number1 number2 代表需要求最小值的数值或引用单元格 区域 参数不超过30个 应用举例 输入公式 MIN E44 J44 7 8 9 10 确认后即可显示出E44至J44单元和区域和数值7 8 9 10中的最小值 特别提醒 如果参数中有文本或逻辑值 则忽略 min A1 0 21 111 201 551 1101 2701 5 5 0 6 2 4 5 6 7 9 3500 LEN函数函数名称 LEN主要功能 统计文本字符串中字符数目 使用格式 LEN text 参数说明 text表示要统计的文本字符串 应用举例 假定A41单元格中保存了 我今年28岁 的字符串 我们在C40单元格中输入公式 LEN A40 确认后即显示出统计结果 6 特别提醒 LEN要统计时 无论中全角字符 还是半角字符 每个字符均计为 1 与之相对应的一个函数 LENB 在统计时半角字符计为 1 全角字符计为 2 MID函数函数名称 MID主要功能 从一个文本字符串的指定位置开始 截取指定数目的字符 使用格式 MID text start num num chars 参数说明 text代表一个文本字符串 start num表示指定的起始位置 num chars表示要截取的数目 应用举例 假定A47单元格中保存了 我喜欢天极网 的字符串 我们在C47单元格中输入公式 MID A47 4 3 确认后即显示出 天极网 的字符 特别提醒 公式中各参数间 要用英文状态下的逗号 隔开 RIGHT函数函数名称 RIGHT主要功能 从一个文本字符串的最后一个字符开始 截取指定数目的字符 使用格式 RIGHT text num chars 参数说明 text代表要截字符的字符串 num chars代表给定的截取数目 应用举例 假定A65单元格中保存了 我喜欢天极网 的字符串 我们在C65单元格中输入公式 RIGHT A65 3 确认后即显示出 天极网 的字符 特别提醒 Num chars参数必须大于或等于0 如果忽略 则默认其为1 如果num chars参数大于文本长度 则函数返回整个文本 SUM函数函数名称 SUM主要功能 计算所有参数数值的和 使用格式 SUM Number1 Number2 参数说明 Number1 Number2 代表需要计算的值 可以是具体的数值 引用的单元格 区域 逻辑值等 应用举例 如图7所示 在D64单元格中输入公式 SUM D2 D63 确认后即可求出语文的总分 SUMIF函数函数名称 SUMIF主要功能 计算符合指定条件的单元格区域内的数值和 使用格式 SUMIF Range Criteria Sum Range 参数说明 Range代表条件判断的单元格区域 Criteria为指定条件表达式 Sum Range代表需要计算的数值所在的单元格区域 应用举例 如图7所示 在D64单元格中输入公式 SUMIF C2 C63 男 D2 D63 确认后即可求出 男 生的语文成绩和 特别提醒 如果把上述公式修改为 SUMIF C2 C63 女 D2 D63 即可求出 女 生的语文成绩和 其中 男 和 女 由于是文本型的 需要放在英文状态下的双引号 男 女 中 TEXT函数函数名称 TEXT主要功能 根据指定的数值格式将相应的数字转换为文本形式 使用格式 TEXT value format text 参数说明 value代表需要转换的数值或引用的单元格 format text为指定文字形式的数字格式 应用举例 如果B68单元格中保存有数值1280 45 我们在C68单元格中输入公式 TEXT B68 0 00 确认后显示为 1280 45 特别提醒 format text参数可以根据 单元格格式 对话框 数字 标签中的类型进行确定 VLOOKUP函数函数名称 VLOOKUP主要功能 在数据表的首列查找指定的数值 并由此返回数据表当前行中指定列处的数值 使用格式 VLOOKUP lookup value table array col index num range lookup 参数说明 Lookup value代表需要查找的数值 Table array代表需要在其中查找数据的单元格区域 Col index num为在table array区域中待返回的匹配值的列序号 当Col index num为2时 返回table array第2列中的数值 为3时 返回第3列的值 Range lookup为一逻辑值 如果为TRUE或省略 则返回近似匹配值 也就是说 如果找不到精确匹配值 则返回小于lookup value的最大数值 如果为FALSE 则返回精确匹配值 如果找不到 则返回错误值 N A 应用举例 参见图7 我们在D65单元格中输入公式 VLOOKUP B65 B2 D63 3 FALSE 确认后 只要在B65单元格中输入一个学生的姓名 如丁48 D65单元格中即刻显示出该学生的语言成绩 特别提醒 Lookup value参见必须在Table array区域的首列中 如果忽略Range lookup参数 则Table array的首列必须进行排序 在此函数的向导中 有关Range lookup参数的用法是错误的 自动筛选高级筛选使用到各种运算符西文符号比较符 逻辑符 AND 与 OR 或 通配符 代表单个字符 代表多个字符 记录的筛选 举例 成绩小于60 成绩 90成绩在60 80之间 成绩 60AND成绩 80OR班号 11B 成绩 80AND班号 11 班11及班21的学生A 班 11OR班号 21B 班 11AND班号 21 班11所有不及格的学生A 成绩 60OR班号 11B 成绩 60AND班号 11 思考题 自动筛选采用简单条件来快速筛选记录 将不满足条件的记录暂时隐藏起来 而将满足条件的记录显示在工作表上通过 数据 菜单中 筛选 命令的 自动筛选 来实现的例18在上述成绩表中 筛选出成绩大于等于80分的记录选定数据清单 选择 数据 菜单中的 筛选 命令从 筛选 级联菜单中选择 自动筛选 选项 此时在标题栏中每个字段名右边出现了一个下拉箭头 单击 成绩 字段名的下拉箭头 从下拉列表单击 自定义 选项 打开 自定义自动筛选方式 对话框 本题筛选条件 成绩 80 设置如下 自动筛选 例19在上述成绩表中 查找成绩在70 90分之间的记录本例题筛选条件是 成绩 70AND成绩 90 因此在 自定义自动筛选方式 对话框中设置如下 高级筛选采用复合条件来筛选记录 并允许把满足条件的记录复制到另外的区域 以生成一个新的数据清单 先建立条件区域 条件区域的第一行为条件标记行 第二行开始是条件行 1 同一条件行的条件互为 与 AND 的关系 表示筛选出同时满足这些条件的记录例20查找成绩大于等于80分 且班号为 11 的所有记录 筛选条件 成绩 80AND班号 11 条件区域表示 2 高级筛选 2 不同条件行的条件互为 或 OR 的关系 表示筛选出满足任何一个条件的记录例21查找英语和计算机课程中至少有一科成绩大于90分的记录英语 90OR计算机 90 条件区域表示 3 对相同的列 字段 指定一个以上的条件 或条件为一个数据范围 则应重复列标题例22查找成绩大于等于60分 并且小于等于90分的姓 林 的记录 例23在成绩表中 查找11班及21班中成绩不及格的所有记录筛选条件为 成绩 60AND班号 11 OR 成绩 60AND班号 21 表格数据排序 表格数据的排序原则数据表规则区域命名必须包括 表头 区域排序前要添加连续编号 以保护原始序列多重排序时只有主要关健字重复时才进行次要关健字的排序排序规则升序和降序符号优先 数字其次 日期由远而近为升序 逻辑值false在前 包含公式按结果排序 排序报警出现扩展提示要小心操作 排序方法单一条件排序多重条件排序序列排序恢复 简单排序 按关键值从小到大 或从小到大 的顺序进行排列排序时 最多可以同时使用三个关键字例24将成绩表按 成绩 从高到低进行排序 1 选定数据清单 2 选择 数据 菜单中的 排序 命令 打开 排序 对话框 3 设置对话框 序列排序举例 可以通过序列排序实现一些特殊的查找在排序对话框中选 选项 就可以进行序列排序 如图 使用分类汇总处理数据 分类汇总操作的基本原则分类原则 要分类的项目必须单独一列凡是需要汇总的列 数据区域中不要有空白单元格 以免分类中遗漏数据区内格式应统一操作原则 先排序 再汇总如何处理结构性分类汇总如何处理筛选式分类汇总 例库统学 xls 先要对数据清单按分类的关键字进行排序例25在成绩表中 按 班号 字段分类汇总 成绩 的平均分 即统计出各个班的平均分 结构性分类汇总 筛选式分类汇总 1 数学函数2 统计函数3 文本函