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，表示保留整数；位数H6),常用函数,费用：=IF(H350,催,IF(H35=0,清)或=IF(H350,催,清)全勤奖：=IF(OR(L90,M90),0,100)等级：=IF(J6=400,优秀,IF(AND(J6=350,J6=400,优秀,IF(AND(J6=350,J6400),中等,if(J3350,差),常用函数使用：,时间函数,计算机当前内部的日期和时间,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、C52）单元格区域的相对引用是由单元格区域的左上角单元格相对引用和右下角单元格相对引用组成的，中间用冒号隔开，如“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”表示行将会随之变化，而列不变。相反“B2”则表示行不变，而列将会随之变化。,单元格引用,乘法口诀表,混合引用例题,相对地址：A2绝对地址:$A$2混合地址：$A2A$2,E4=B1+A2+C2,按F4，可在以上三种地址引用之间进行切换,单元格地址在公式中的作用,单元格引用,例如：Sheetn!B2,（4）三维地址引用,三维地址引用的特点引用不同工作表中的单元格,三维引用主要用于两个或多个工作表中的单元格或单元格区域中的数据，也可用于同一个工作簿中不同工作表中的单元格区域。三维引用的一般格式为“工作表名！:单元格地址”。工作表名后跟的符号“！”是系统自带的。,三维地址引用,谢谢,