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

《Excel在数值计算中的应用(公式与函数).ppt》由会员分享，可在线阅读，更多相关《Excel在数值计算中的应用(公式与函数).ppt（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）文本运算符文本运算符 说 明连字符&将一个或多个数据以文本形式连接起来形成一个新文

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

6、址：首尾地址用（：）连接。例如，=SUM（A1：A8），冒号表示计算范围从A1到A8单元格之间区域。混合地址：混合地址：例如，=SUM（A1：A6，A8），表示计算A1到A6再加A8的和。单元格区域的表示法：单元格区域的表示法：1）直接输入）直接输入 2）鼠标选中）鼠标选中 （1）公公式式：以以等等号号（=）开开始始，由由连连续续的的一一组组数数字字和和运运算算符组成的序列表达式。即：符组成的序列表达式。即：公式形式：=操作数和运算符操作数和运算符 公公式式是是对对数数据据进进行行分分析析与与计计算算的的等等式式，它它可可以以对对工工作作表表中中的的数数值值进进行从简单的四则混合运算直到复杂的

7、财务计算、统计分析。行从简单的四则混合运算直到复杂的财务计算、统计分析。选定单元格选定单元格在编辑栏（或单元格）中在编辑栏（或单元格）中输入等号输入等号输入公式输入公式回车回车 （2）输入公式并计算）输入公式并计算公式公式输入准则：输入准则：124.5+118.4+115.2+128.7B3+C3+D3+E3SUM(B3,C3,D3,E3)SUM(B3:E3)SUM(B3:E3)数据统计计算数据统计计算 公式公式*若更改求值顺序，请将公式中要先计算的部分用括号括起来。若更改求值顺序，请将公式中要先计算的部分用括号括起来。单元格、数字、字符、区域名、区域、函数单元格、数字、字符、区域名、区域、函

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

9、数中没有可用的数值。#REF！单元格引用无效。#NUM！公式或函数中某一数字有问题。#NULL！对两个不相交的单元格区域引用使用了交叉引用运算符（空格）。函数函数 函数是函数是ExcelExcel已定义好的具有特定功能的内置公已定义好的具有特定功能的内置公式，式，执行计算、分析等处理数据任务执行计算、分析等处理数据任务数据统计计算数据统计计算 函数函数例例:求平均求平均=（B2+B3+B4+B5+B6+B7+B8+B9+B10B2+B3+B4+B5+B6+B7+B8+B9+B10）/9/9=Average=Average（B2:B10B2:B10）为了适应各种计算要求，为了适应各种计算要求，在

10、在Excel中函数共分为十类：中函数共分为十类：常用、常用、财务、日期与时间、数学与三角函数、统计、查找与引用、数财务、日期与时间、数学与三角函数、统计、查找与引用、数据库、文本、逻辑和信息函数据库、文本、逻辑和信息函数或按按“shift+F3”直接打直接打开开 函数的查看财务函数：可数值进行一般的财务计算，如确定贷款的支付额、投资的未来值或净现值等。日期和时间：在公式中分析和处理日期值，如TODAY（）返回系统当前日期。查询和引用：查询特定数值，或者查找某一单元格的引用。数据库：分析数据库中的数和时间值，如数字取整、求和、按给定条件求和等。数学和三角：处理简单或复杂的数学计算，如直线的斜率和

11、y轴截距等。.统计：对数据区域进行统计，如计算所有数值大于100且小于250的记录总数。文本：在公式中处理文字串，如提取文字串中的字符等。逻辑：进行真假值判断，或复合检验。如IF（）函数可确定条件的真假并返回相应的数值。信息：可以测试/确认存储在单元格中的数据类型。函数的分类工程函数：对数值进行各种工程上的运算和分析 外部函数：使用加载项提供自定义命令或自定义功能的补充程序 Excel的任何函数都有一样的标准结构，即：都是由函数名和参数两部分组成。将函数作为另一个函数的参数也称为嵌套函数。不同类型的函数要求给定不同类型的函数要求给定不同类型的参数不同类型的参数函数准则函数准则函数准则函数准则：

12、函数名函数名（参数参数1，参数参数2，）代表函数功能代表函数功能函数输入准则函数输入准则 函数和公式不同，公式是在工作表中队数据进行分析的等式，函数和公式不同，公式是在工作表中队数据进行分析的等式，它可以引用一个工作表的多个单元格、同一工作薄不同工作表的单它可以引用一个工作表的多个单元格、同一工作薄不同工作表的单元格以及其他工作薄中的工作表；而函数是预定义的内置公式，它元格以及其他工作薄中的工作表；而函数是预定义的内置公式，它使用被称为参数的特定数值，并按特定顺序计算使用被称为参数的特定数值，并按特定顺序计算 函数是一种特殊的公式，因此所有的函数都要以函数是一种特殊的公式，因此所有的函数都要以

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

14、击单击“插入函数插入函数”钮钮 选择类别选择类别 选择函数选择函数 输入参数输入参数 确定确定输入输入函数方法：函数方法：直接输入法、向导法直接输入法、向导法常用函数常用函数 数学函数：数学函数：（1）SUM：求和函数求和函数 格式：格式：=SUM（参数（参数1,参数参数2,）功能：功能：求各参数的和，最多求各参数的和，最多30个参数个参数 如：注意：如果参数中有空白单元格、逻辑值、文本，则忽略。（2）SUMIF：计算对满足单元格条件区域数值求和计算对满足单元格条件区域数值求和 格式：格式：=SUMIF（参数（参数 条件 参数）=SUMIF（参数（参数 条件 如 =SUMIF(E3:E27,=

15、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

16、,计算机,G2:G14)，表示在A2：A14区域中找出值为“计算机”的单元格区域，将它们的G列数据求和。（3）AVERAGE（）：平均值（）：平均值函数函数 格式：格式：=AVERAGE（参数（参数1,参数参数2,）功能：求个参数的平均值功能：求个参数的平均值 如如 =AVERAGE(E12:I12)如果引用区域中包含“0”值单元格，则计算在内；如果引用区域中包含空白或字符单元格，则不计算在内（4）ROUND（）（）：四舍五入函数：四舍五入函数 格式：格式：ROUND（数值，位数）（数值，位数）功能：对数值项进行四舍五入功能：对数值项进行四舍五入。若位数0，表示保留位数指定的小数；位数=0，表

17、示保留整数；位数0，表示从个位向左对第几位进行舍入 如：如：ROUND（123.528，2）结果结果123.52 ROUND（123.528，0）124 ROUND(123.528，-2）100（5）INT（）：取整函数（）：取整函数 格式：格式：INT（A1）功能：取不大于参数功能：取不大于参数A1的最大整数的最大整数 如：如：INT（12.45）=12 INT（-12.45）=-13 常用函数常用函数 数学函数：数学函数：（1）求最大值/最小值函数 MAX/MIN（）格式：格式：=MAX/MIN（参数（参数1,参数参数2,）功能：求各参数中的最大功能：求各参数中的最大/小的值小的值 注意：

18、如果参数中有文本或逻辑值，则忽略。（2）计数函数：COUNT/COUNTA（）格式：格式：=COUNT/COUNTA（参数（参数1,参数参数2,）功能：功能：COUNT求各参数中数值型参数和包含数值的单元格个数求各参数中数值型参数和包含数值的单元格个数 COUNTA求各参数中包含所有字符的单元格个数求各参数中包含所有字符的单元格个数 如：=COUNT（12，D1:D3，CHINA）若D1:D3中存放的全是数值，则函数的结果是4，若D1:D3中只有一个存放的是数值，则结果为2（3）COUNTIF（）：（）：条件计数函数条件计数函数 格式：格式：=COUNTIF（取值范围，条件式）（取值范围，条件

19、式）功能：计算某区域内满足条件的单元格个数功能：计算某区域内满足条件的单元格个数 如：=COUNTIF(B1:B13,=COUNTIF(B1:B13,“60G3,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,差)常用函数使用：常用函数使用：时间函数时间函数计算机当前内部的日期和时间now()now()-括号无

20、参数=(NOW()-E46)/365 =（当前日期-进厂日期）/365工龄计算：“C T R L+：”显 示 系 统 日 期“CTRL+SHIFT+：”显示系统时间 常用函数常用函数（1）RANK:排名次函数排名次函数 格式：格式：RANK（参数，（参数，数据区域，升降序）数据区域，升降序）功能：计算某数值在数据区域内相对其它数值的大小排位功能：计算某数值在数据区域内相对其它数值的大小排位（2）VLOOKUP：搜索函数：搜索函数（搜索表区域首列满足（搜索表区域首列满足条件的元素）条件的元素）格式：格式：VLOOKUP（比较值（比较值,搜索区域，取值的列号，是否精确搜索区域，取值的列号，是否精确

21、判断）判断）功能：搜索表区域内首列满足条件的元素，找到时返回所在行功能：搜索表区域内首列满足条件的元素，找到时返回所在行指定列的值指定列的值（3）LOOKUP：从单行或单列或数组中查找一个值；从单行或单列或数组中查找一个值；查找与引用函数 常用函数常用函数=函数名（要查找的值函数名（要查找的值,选择数组选择数组,选择数组选择数组)格式：格式：如：=LOOKUP(沈克,B4:B19,G4:G19)财务函数 常用函数常用函数 PMT ：计算在固定利率下，贷款的等额分期偿还额 格式：=PMT(rate,nper,pv,FV,type)a.PV 返回投资的现值返回投资的现值(本金本金)b.RATE 各

22、期利率各期利率c.NPER 为总投资期为总投资期d.FV 未来值未来值,或在最后一次付款后希望得到的现金金额或在最后一次付款后希望得到的现金金额,省略为省略为”0”e.TYPE 指定各期付款时间指定各期付款时间”0”期末期末”1”期期=PMT（年率/12，年数*12，-货款总额）单元格引用单元格引用 即以单元格为计算对象；即以单元格为计算对象；引用的作用是标识工作表上单元格或单元格区域，并指明公式中所使用的数据的位置。通过引用在Excel中，可以在公式中使用工作表不同单元格中的数据，或同一工作簿的不同工作表的单元格。对单元格的引用有三种方法：相对引用、绝对引用和混合引用对单元格的引用有三种方法

23、：相对引用、绝对引用和混合引用 单元格或单元格区域引用一般式为：工作表名！单元格引用 或 工作簿名工作表名！单元格引用 在引用同一工作簿单元格时，工作簿可以省略，在引用同一工作表时，工作表可以省略。例：=E12+5 引用了同一工作表的E12单元格；=Sheet2!A2+Sheet3!A2 引用了工作表Sheet2的A2单元格和工作表Sheet3的A2单元格。（）（）（）（）相对地址引用：相对地址引用：相对地址引用：相对地址引用：是指用列号和行号直接表示的地址 包括单元格的相对引用和单元格区域的相对引用。相对引用的特点相对引用的特点当含公式单元格移动、复制到一个新位当含公式单元格移动、复制到一个

24、新位置时，公式中的相对地址会随之发生变化置时，公式中的相对地址会随之发生变化。1）单元格的相对引用。直接用单元格所在的列号和行号作为其引用，例如A3是引用了第A列与第3行交叉处的单元格；B4、C5 2）单元格区域的相对引用是由单元格区域的左上角单元格相对引用和右下角单元格相对引用组成的，中间用冒号隔开，如“D2:J6”表示以D2单元格为左上角，J6单元格为右下角的矩形单元格区域。单元格引用单元格引用公式的复制例题公式的复制例题 相对地址一些注意如果把某个单元格中的公式移到另一个单元格时，在原单元格内公式中的地址在新单元格中保持不变。如果复制单元格中的公式，在复制的新单元格中，公式中的地址将作相

25、应的变化。如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 绝对引用的特点绝对引用的特点当公

26、式移动、复制到一个新位置时，公当公式移动、复制到一个新位置时，公式中的式中的绝对地址不会发生变化绝对地址不会发生变化。（）（）（）（）混合地址引用：混合地址引用：混合地址引用：混合地址引用：在单元格中既有相对地址引用，也有绝对地址引用。混合地址是指单元格地址的行号或混合地址是指单元格地址的行号或列号前不同时加列号前不同时加“$”符号符号 例如：B2的混合地址为$B2或B$2 “B2”表示行将会随之变化，而列不变。相反“B2”则表示行不变，而列将会随之变化。单元格引用单元格引用混合引用例题混合引用例题相对相对相对相对地址：地址：A2绝对绝对绝对绝对地址地址:$A$2混合混合混合混合地址：地址：$A2 A$2E4=B1+A2+C2按按F4F4，可在以上三种地址引用之间进行切换，可在以上三种地址引用之间进行切换 单元格地址在公式中的作用单元格引用单元格引用例如：Sheetn!B2 （4 4）三维地址引用）三维地址引用）三维地址引用）三维地址引用三维地址引用的特点三维地址引用的特点引用不同工作表中的单元格引用不同工作表中的单元格 三维引用主要用于两个或多个工作表中的单元格或单元格区域中的数据，也可用于同一个工作簿中不同工作表中的单元格区域。三维引用的一般格式为“工作表名！:单元格地址”。工作表名后跟的符号“！”是系统自带的。三维地址引用三维地址引用三维地址引用三维地址引用