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

《Excel在数值计算中的应用(公式与函数)》由会员分享，可在线阅读，更多相关《Excel在数值计算中的应用(公式与函数)（53页珍藏版）》请在金锄头文库上搜索。

1、第六章 Excel在数值计算中的应 用（公式与函数）Excel环境 菜单栏编辑栏工具栏工具栏状态栏名称框 编辑框活动单元格填充柄数据的手动输入几个常用功能键Shift: 选取相邻单元格Ctrl：选取多个单元格Tab：水平移动Enter：垂直移动Ctrl+Enter：同时输入数据输入Excel 数据3要素地址， 数据， 运算符号Excel 的内置函数数据填充柄点住黑点按住左键上下左右 拖曳数据自动填充举例填充工具引用功能相对引用：B1；绝对引用$B$1；混合：$B1引用地址的自动填充编辑与复制注意引用方式复制全部复制数据数据输入举例求不同PH值时H2C2O4溶液中各物种的 平衡浓度由物料平衡可计

2、算平衡时各物种的浓度输入步骤=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提供的工具栏中“ ”按钮快速完成计算 公式是工作表中对数据

3、进行分析和运算的等式，它是工作表的数据计 算中不可缺少的部分。 函数是Excel提供的一些特殊的内置公式，可以进行数学运算或逻辑 运算等。与直接使用公式进行计算相比较，使用函数进行计算的速度 更快，并可减少错误的发生本讲作为Excel的核心部分，对Excel的数据计算与分析， 具有重要作用，特别是公式与函数。公式简介：公式可以用来执行Excel各种计算，使用公式可以方便而准确地分析工作表中的数据。如用户向工作表中输入计算的数值时，就可以用公式。公式是工作表中的数值进行计算的等式，由运算符、单元格引用位置、数值、工作表函数等组成。通过公式，用户可以对工作表中的数据进行较为复杂的运算数据统计计算

4、公式运算符=（B4 + 25）/ SUM（D5:F5）公式符区域范围引用常量函数单元格地址公式结构数据统计计算 公式算术运算符*/+-%说 明乘方乘除加减百分号比较运算符=说 明大于大于等于小于小于等于等于不等于引用运算符说 明：（冒号） 对两个引用之间，包括两个引用在内的所有单元格（区域 ）进行引用，如B2:B8、 A2:E10 ，（逗号） 将多个引用合并成一个引用，如SUM（B1:B10,A1:A10,C11 ）文本运算符 说 明 连字符 &将一个或多个数据以文本形式连接起来形成一个新文本。 如公式“=”计算机“ & ”基础“”，结果为“计算机基础” 。Excel公式中的运算符数据统计计算

5、 公式注意（很重要）：空格-交叉运算符，如：SUM(B5:B15 A7:D7)两区域交叉单 元格之和，即B7 在Excel中,每个运算符都有一定的优 先级.优先级从高到低分别为:冒号、单个空 格、逗号、负号、百分号、插入符号、星 号和正斜杠、加号和减号、和号、比较运 算符。Excel中的运算符单元格或区域的表示方法 单元格区域的表示法：独立地址：各地址用（，）隔开。例如，=SUM（A1，A8），表示A1和A8两单元格的和。连续地址：首尾地址用（：）连接。例如，=SUM（A1：A8），冒号表示计算范围从A1到A8单元格之间区域。混合地址：例如，=SUM（A1：A6，A8），表示计算A1到A6再加

6、A8的和。 单元格区域的表示法：1）直接输入 2 ）鼠标选中（1）公式： 以等号（=）开始，由连续的一组数字和运算 符组成的序列表达式。即：公式形式：=操作数和运算符公式是对数据进行分析与计算的等式，它可以对工作表中的数值进 行从简单的四则混合运算直到复杂的财务计算、统计分析。 选定单元格在编辑栏（或单元格）中输入等号输入公式回车（2）输入公式并计算公式输入准则：124.5 + 118.4 + 115.2 + 128.7B3 + C3 + D3 + E3SUM( B3, C3, D3, E3 )SUM( B3 : E3 )SUM( B3 : E3 )数据统计计算 公式* 若更改求值顺序，请将公

7、式中要先计算的部分用括号括起来。单元格、数字、字符、区域名、区域、函数图 输入公式例题公式的操作数据统计计算 公式公式中显示和修改默认情况下，编辑栏显示 公式，单元格显示公式计算的 结果。故一般情况下，在编辑 栏中修改公式。公式的复制用填充法复制公式：先单击 已有公式的单元格，然后拖填 充柄至最后一个单元格 用“复制/粘贴”法复制公式， 如果公式不连续，此法较好。公式插入时的出错提示错误值的涵义 #！输入或计算结果的数值太长，单元格容纳不下 。 # VALUE！使用了错误的参数，或运算对象的类型不对。 # DIV / 0！公式中除数为 0，或引用了空单元格，或引用 了包含 0 值的单元格。 #

8、 NAME?公式中使用了不能识别的单元格名称。 # N /A 公式或函数中没有可用的数值。 # REF！单元格引用无效。 # NUM！公式或函数中某一数字有问题。 # NULL！对两个不相交的单元格区域引用使用了交叉引 用运算符（空格）。函数函数是Excel已定义好的具有特定功能的内置公 式，执行计算、分析等处理数据任务数据统计计算 函数例:求平均=（B2+B3+B4+B5+B6+B7+B8+B9+B10）/ 9= Average（B2:B10） 为了适应各种计算要求，在Excel中函数共分为十类：常用 、财务、日期与时间、数学与三角函数、统计、查找与引用、 数据库、文本、逻辑和信息函数或按“

9、shift+F3”直接打开321函数的查看财务函数：可数值进行一般的财务计算，如确定贷款的支付额、投资的未来值或 净现值等。日期和时间：在公式中分析和处理日期值，如TODAY（）返回系统当前日期。查询和引用：查询特定数值，或者查找某一单元格的引用。数据库：分析数据库中的数和时间值，如数字取整、求和、按给定条件求和等。数学和三角：处理简单或复杂的数学计算，如直线的斜率和y轴截距等。.统计：对数据区域进行统计，如计算所有数值大于100且小于250的记录总数。文本：在公式中处理文字串，如提取文字串中的字符等。逻辑：进行真假值判断，或复合检验。如IF（）函数可确定条件的真假并返回相 应的数值。信息：可

10、以测试/确认存储在单元格中的数据类型。函数的分类工程函数：对数值进行各种工程上的运算和分析外部函数：使用加载项提供自定义命令或自定义功能的补充程序Excel的任何函数都有一样的标准结构，即：都是由函数名和 参数两部分组成。将函数作为另一个函数的参数也称为嵌套函数。不同类型的函数要求给定 不同类型的参数函数准则函数准则：函数名函数名（参数参数1 1，参数参数2 2 ， ）代表函数功能函数输入准则 参数：数字、数组、文本、常量、单元格、区 域、区域名、公式或其他函数 函数和公式不同，公式是在工作表中队数据进行分析的等式，它可以引用一个工作表的多个单元格、同一工作薄不同工作表的单元格以及其他工作薄中

11、的工作表；而函数是预定义的内置公式，它使用被称为参数的特定数值，并按特定顺序计算 函数是一种特殊的公式，因此所有的函数都要以“=”开 使，其次是函数名称，然后是括号，括号表示其中括住的内 容是此函数必需的参数。如果函数对应的参数不只一个时， 就必须按照规定的顺序依次排列各参数，并且用逗号把它们 隔离开来 手工输入：选定单元格 “=” 输入函数 确定 粘贴函数（“函数向导”） ：首先选定要输入函数的单元格1）单击工具栏上的“自动求和”按钮中的“其他函数” 选择类别 选择函数输入参数 确定2） “插入/函数” 选择类别 选择函数输入参数 确定3）单击 “插入函数”钮 选择类别 选择函数 输入参数

12、确定输入函数方法： 直接输入法、向导法常用函数 数学函数：（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，A

13、2:A10)参数说明：第一个参数为用于条件判断的单元格区域；条件确定哪些单元格将被 相加求和，其形式可以为数字、表达式或文本。例如，条件可以表示为 32、“男”、“32” ；后面的参数是需要求和的数值所在的单元格区域，如果省略了后面参数，则对第一个参 数区域中的单元格求和。 应用举例：输入公式：=SUMIF(A2:A14,“计算机“,G2:G14)，表示在A2：A14区域中找出值 为“计算机”的单元格区域，将它们的G列数据求和。（3）AVERAGE（）：平均值函数 格式： =AVERAGE （参数1, 参数2, ）功能：求个参数的平均值如 =AVERAGE(E12:I12) 如果引用区域中包含

14、“0”值单元格，则计算在内；如果引用区域中包含空白或字符单元格，则不计算在内 （4）ROUND（） ：四舍五入函数 格式：ROUND（数值，位数）功能：对数值项进行四舍五入。 若位数0，表示保留位数指定的小数；位数=0，表示保留整数；位数G3,G6G3,G6H6)常用函数 费用： =IF(H350,“催“,IF(H35=0,“清“)或 =IF(H350,“催“,“清“)E全勤奖： =IF(OR(L90,M90),0,100) 等级： =IF(J6=400,“优秀“,IF(AND(J6=350,J6=400,“优秀“,IF(AND(J6=350,J6400),“中等 “,if(J3350,“差“

15、)常用函数使用：时间函数计算机当前内部的日期和时间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) a.PV 返回投资的现值(本金)b.RATE 各期利率c.NPER 为总投资期d.FV 未来值,或在最后一次付款后希望