Excel在数值计算中的应用(公式与函数).ppt

第六章Excel在数值计算中的应用 公式与函数 Excel环境 菜单栏 编辑栏 工具栏 工具栏 状态栏 名称框 编辑框 活动单元格 填充柄 数据的手动输入 几个常用功能键Shift 选取相邻单元格Ctrl 选取多个单元格Tab 水平移动Enter 垂直移动Ctrl Enter 同时输入 数据输入 Excel数据3要素地址 数据 运算符号 Excel的内置函数 数据填充柄 点住黑点按住左键上下左右拖曳 数据自动填充举例 填充工具 引用功能 相对引用 B1 绝对引用 B 1 混合 B1 引用地址的自动填充 编辑与复制 注意引用方式 复制全部 复制数据 数据输入举例 求不同PH值时H2C2O4溶液中各物种的平衡浓度 由物料平衡可计算 平衡时各物种的浓度 输入步骤 1 1 10 A6 B 3 10 A6 2 B 3 C 3 B6 10 A6 B 3 C6 10 A6 C 3 1 1 10 A8 B 3 10 A8 2 B 3 C 3 C8 10 A8 C 3 数据统计计算 数据统计在工作表中输入数据后 往往需对数据进行计算处理 Excel则提供强大的数据计算功能即数据统计计算 可以对Excel工作表中数据进行精确 高速的计算 分析和处理 从而极大地提高工作效率 它包括了简单运算 公式与函数 简单运算是利用Excel提供的工具栏中 按钮快速完成计算 公式是工作表中对数据进行分析和运算的等式 它是工作表的数据计算中不可缺少的部分 函数是Excel提供的一些特殊的内置公式 可以进行数学运算或逻辑运算等 与直接使用公式进行计算相比较 使用函数进行计算的速度更快 并可减少错误的发生 本讲作为Excel的核心部分 对Excel的数据计算与分析 具有重要作用 特别是公式与函数 公式简介 公式可以用来执行Excel各种计算 使用公式可以方便而准确地分析工作表中的数据 如用户向工作表中输入计算的数值时 就可以用公式 公式是工作表中的数值进行计算的等式 由运算符 单元格引用位置 数值 工作表函数等组成 通过公式 用户可以对工作表中的数据进行较为复杂的运算 数据统计计算公式 公式结构 数据统计计算公式 Excel公式中的运算符 数据统计计算公式 注意 很重要 空格 交叉运算符 如 SUM B5 B15A7 D7 两区域交叉单元格之和 即B7 在Excel中 每个运算符都有一定的优先级 优先级从高到低分别为 冒号 单个空格 逗号 负号 百分号 插入符号 星号和正斜杠 加号和减号 和号 比较运算符 Excel中的运算符 单元格或区域的表示方法 单元格区域的表示法 独立地址 各地址用 隔开 例如 SUM A1 A8 表示A1和A8两单元格的和 连续地址 首尾地址用 连接 例如 SUM A1 A8 冒号表示计算范围从A1到A8单元格之间区域 混合地址 例如 SUM A1 A6 A8 表示计算A1到A6再加A8的和 单元格区域的表示法 1 直接输入2 鼠标选中 1 公式 以等号 开始 由连续的一组数字和运算符组成的序列表达式 即 公式形式 操作数和运算符公式是对数据进行分析与计算的等式 它可以对工作表中的数值进行从简单的四则混合运算直到复杂的财务计算 统计分析 选定单元格 在编辑栏 或单元格 中 输入等号 输入公式 回车 2 输入公式并计算 公式输入准则 SUM B3 E3 数据统计计算公式 若更改求值顺序 请将公式中要先计算的部分用括号括起来 单元格 数字 字符 区域名 区域 函数 图输入公式例题 公式的操作 数据统计计算公式 公式中显示和修改默认情况下 编辑栏显示公式 单元格显示公式计算的结果 故一般情况下 在编辑栏中修改公式 公式的复制 用填充法复制公式 先单击已有公式的单元格 然后拖填充柄至最后一个单元格 用 复制 粘贴 法复制公式 如果公式不连续 此法较好 公式插入时的出错提示 错误值的涵义 输入或计算结果的数值太长 单元格容纳不下 VALUE 使用了错误的参数 或运算对象的类型不对 DIV 0 公式中除数为0 或引用了空单元格 或引用了包含0值的单元格 NAME 公式中使用了不能识别的单元格名称 N A公式或函数中没有可用的数值 REF 单元格引用无效 NUM 公式或函数中某一数字有问题 NULL 对两个不相交的单元格区域引用使用了交叉引用运算符 空格 函数函数是Excel已定义好的具有特定功能的内置公式 执行计算 分析等处理数据任务 数据统计计算函数 例 求平均 B2 B3 B4 B5 B6 B7 B8 B9 B10 9 Average B2 B10 为了适应各种计算要求 在Excel中函数共分为十类 常用 财务 日期与时间 数学与三角函数 统计 查找与引用 数据库 文本 逻辑和信息函数 或按 shift F3 直接打开 3 2 1 函数的查看 财务函数 可数值进行一般的财务计算 如确定贷款的支付额 投资的未来值或净现值等 日期和时间 在公式中分析和处理日期值 如TODAY 返回系统当前日期 查询和引用 查询特定数值 或者查找某一单元格的引用 数据库 分析数据库中的数和时间值 如数字取整 求和 按给定条件求和等 数学和三角 处理简单或复杂的数学计算 如直线的斜率和y轴截距等 统计 对数据区域进行统计 如计算所有数值大于100且小于250的记录总数 文本 在公式中处理文字串 如提取文字串中的字符等 逻辑 进行真假值判断 或复合检验 如IF 函数可确定条件的真假并返回相应的数值 信息 可以测试 确认存储在单元格中的数据类型 函数的分类 工程函数 对数值进行各种工程上的运算和分析 外部函数 使用加载项提供自定义命令或自定义功能的补充程序 Excel的任何函数都有一样的标准结构 即 都是由函数名和参数两部分组成 将函数作为另一个函数的参数也称为嵌套函数 不同类型的函数要求给定不同类型的参数 函数准则 函数名 参数1 参数2 代表函数功能 函数输入准则 参数 数字 数组 文本 常量 单元格 区域 区域名 公式或其他函数 函数和公式不同 公式是在工作表中队数据进行分析的等式 它可以引用一个工作表的多个单元格 同一工作薄不同工作表的单元格以及其他工作薄中的工作表 而函数是预定义的内置公式 它使用被称为参数的特定数值 并按特定顺序计算 函数是一种特殊的公式 因此所有的函数都要以 开使 其次是函数名称 然后是括号 括号表示其中括住的内容是此函数必需的参数 如果函数对应的参数不只一个时 就必须按照规定的顺序依次排列各参数 并且用逗号把它们隔离开来 手工输入 选定单元格 输入函数 确定 粘贴函数 函数向导 首先选定要输入函数的单元格1 单击工具栏上的 自动求和 按钮中的 其他函数 选择类别 选择函数 输入参数 确定2 插入 函数 选择类别 选择函数 输入参数 确定3 单击 插入函数 钮 选择类别 选择函数 输入参数 确定 输入函数方法 直接输入法 向导法 常用函数数学函数 1 SUM 求和函数 格式 SUM 参数1 参数2 功能 求各参数的和 最多30个参数如 注意 如果参数中有空白单元格 逻辑值 文本 则忽略 2 SUMIF 计算对满足单元格条件区域数值求和 格式 SUMIF 参数 条件 参数 SUMIF 参数 条件 如 SUMIF E3 E27 90 A求 语文成绩大于等于70的和方法 SUMIF E4 E8 70 B求 语文成绩大于等于70的求总分的和方法 SUMIF D20 D27 70 E20 E27 SUM E4 E8 H8 H10 A2 A10 参数说明 第一个参数为用于条件判断的单元格区域 条件确定哪些单元格将被相加求和 其形式可以为数字 表达式或文本 例如 条件可以表示为32 男 32 后面的参数是需要求和的数值所在的单元格区域 如果省略了后面参数 则对第一个参数区域中的单元格求和 应用举例 输入公式 SUMIF A2 A14 计算机 G2 G14 表示在A2 A14区域中找出值为 计算机 的单元格区域 将它们的G列数据求和 3 AVERAGE 平均值函数 格式 AVERAGE 参数1 参数2 功能 求个参数的平均值如 AVERAGE E12 I12 如果引用区域中包含 0 值单元格 则计算在内 如果引用区域中包含空白或字符单元格 则不计算在内 4 ROUND 四舍五入函数 格式 ROUND 数值 位数 功能 对数值项进行四舍五入 若位数 0 表示保留位数指定的小数 位数 0 表示保留整数 位数 0 表示从个位向左对第几位进行舍入如 ROUND 123 528 2 结果123 52ROUND 123 528 0 124ROUND 123 528 2 100 5 INT 取整函数 格式 INT A1 功能 取不大于参数A1的最大整数如 INT 12 45 12INT 12 45 13 常用函数数学函数 1 求最大值 最小值函数MAX MIN 格式 MAX MIN 参数1 参数2 功能 求各参数中的最大 小的值 注意 如果参数中有文本或逻辑值 则忽略 2 计数函数 COUNT COUNTA 格式 COUNT COUNTA 参数1 参数2 功能 COUNT求各参数中数值型参数和包含数值的单元格个数COUNTA求各参数中包含所有字符的单元格个数如 COUNT 12 D1 D3 CHINA 若D1 D3中存放的全是数值 则函数的结果是4 若D1 D3中只有一个存放的是数值 则结果为2 3 COUNTIF 条件计数函数 格式 COUNTIF 取值范围 条件式 功能 计算某区域内满足条件的单元格个数如 COUNTIF B1 B13 60 确认后 即可统计出B1至B13单元格区域中 数值小于60的单元格数目 COUNTIF D3 B27 男 注意 允许引用的单元格区域中有空白单元格出现 常用函数统计函数 1 条件判断函数 IF 格式 IF 条件式 值1 值2 功能 若满足条件 则结果取值1 否则 取值2 IF 条件式 值1 值2 条件式 条件为假时 结果值 条件为真时 判断条件是否满足 满足则返回值1 不满足则返回值2 常用函数逻辑函数 如 if A2 100 good bad 逻辑函数 And同时满足两个条件才能成为真值 否则为假值 AND F3 G3 G6G3 G6 H6 常用函数 费用 IF H35 0 催 IF H35 0 清 或 IF H35 0 催 清 全勤奖 IF OR L9 0 M9 0 0 100 等级 IF J6 400 优秀 IF AND J6 350 J6 400 优秀 IF AND J6 350 J6 400 中等 if J3 350 差 常用函数使用 时间函数 计算机当前内部的日期和时间 now 括号无参数 NOW E46 365 当前日期 进厂日期 365 工龄计算 CTRL 显示系统日期 CTRL SHIFT 显示系统时间 常用函数 1 RANK 排名次函数 格式 RANK 参数 数据区域 升降序 功能 计算某数值在数据区域内相对其它数值的大小排位 2 VLOOKUP 搜索函数 搜索表区域首列满足条件的元素 格式 VLOOKUP 比较值 搜索区域 取值的列号 是否精确判断 功能 搜索表区域内首列满足条件的元素 找到时返回所在行指定列的值 3 LOOKUP 从单行或单列或数组中查找一个值 查找与引用函数 常用函数 函数名 要查找的值 选择数组 选择数组 格式 如 LOOKUP 沈克 B4 B19 G4 G19 财务函数 常用函数 PMT 计算在固定利率下 贷款的等额分期偿还额格式 PMT rate nper pv FV type PV返回投资的现值 本金 RATE各期利率NPER为总投资期FV未来值 或在最后一次付款后希望得到的现金金额 省略为 0 TYPE指定各期付款时间 0 期末 1 期 PMT 年率 12 年数 12 货款总额 单元格引用 即以单元格为计算对象 引用的作用是标识工作表上单元格或单元格区域 并指明公式中所使用的数据的位置 通过引用在Excel中 可以在公式中使用工作表不同单元格中的数据 或同一工作簿的不同工作表的单元格 对单元格的引用有三种方法 相对引用 绝对引用和混合引用 单元格或单元格区域引用一般式为 工作表名 单元格引用或 工作簿名 工作表名 单元格引用在引用同一工作簿单元格时 工作簿可以省略 在引用同一工作表时 工作表可以省略 例 E12 5 引用了同一工作表的E12单元格 Sheet2 A2 Sheet3 A2 引用了工作表Sheet2的A2单元格和工作表Sheet3的A2单元格 相对地址引用 是指用列号和行号直接表示的地址包括单元格的相对引用和单元格区域的相对引用 相对引用的特点 当含公式单元格移动 复制到一个新位置时 公式中的相对地址会随之发生变化 1 单元格的相对引用 直接用单元格所在的列号和行号作为其引用 例如A3是引用了第A列与第3行交叉处的单元格 B4 C5 2 单元格区域的相对引用是由单元格区域的左上角单元格相对引用和右下角单元格相对引用组成的 中间用冒号隔开 如 D2 J6 表示以D2单元格为左上角 J6单元格为右下角的矩形单元格区域 单元格引用 公式的复制例题 相对地址一些注意 如果把某个单元格中的公式移到另一个单元格时 在原单元格内公式中的地址在新单元格中保持不变 如果复制单元格中的公式 在复制的新单元格中 公式中的地址将作相应的变化 如C1单元格的公式是 A1 B2 如果把C1单元格公式移到C2 C2单元格公式仍是 A1 B2 若把C1单元格公式复制到C2 C2单元格中的公式会变成 A2 B3 这是由于把C1单元格公式复制到C2单元格的过程中 行号加了1 由此引起公式中的行号也自动加1 若将C1单元格公式复制到E5单元格 E5单元格中的公式就是 C5 D6 列号与行号分别在原来的基础上加了C1至E5的差值 绝对地址引用 公式中单元格的确切位置保持不变 在单元格的行号和列号前加上 符号来表示例如 B2的绝对地址为 B 2绝对引用的特点 当公式移动 复制到一个新位置时 公式中的绝对地址不会发生变化 混合地址引用 在单元格中既有相对地址引用 也有绝对地址引用 混合地址是指单元格地址的行号或列号前不同时加 符号例如 B2的混合地址为 B2或B 2 B2 表示行将会随之变化 而列不变 相反 B 2 则表示行不变 而列将会随之变化 单元格引用 乘法口诀表 混合引用例题 相对地址 A2绝对地址 A 2混合地址 A2A 2 E4 B1 A2 C2 按F4 可在以上三种地址引用之间进行切换 单元格地址在公式中的作用 单元格引用 例如 Sheetn B2 4 三维地址引用 三维地址引用的特点 引用不同工作表中的单元格 三维引用主要用于两个或多个工作表中的单元格或单元格区域中的数据 也可用于同一个工作簿中不同工作表中的单元格区域 三维引用的一般格式为 工作表名 单元格地址 工作表名后跟的符号 是系统自带的 三维地址引用 谢谢