Excel在数值计算中的应用公式与函数省公共课一等奖全国赛课获奖课件.pptx

第六章Excel在数值计算中应用（公式与函数）第1页Excel环境菜单栏编辑栏工具栏工具栏状态栏名称框 编辑框活动单元格填充柄第2页数据手动输入几个惯用功效键Shift:选取相邻单元格Ctrl：选取多个单元格Tab：水平移动Enter：垂直移动Ctrl+Enter：同时输入第3页数据输入Excel 数据3要素 地址，数据，运算符号第4页Excel 内置函数第5页数据填充柄点住黑点按住左键上下左右拖曳第6页数据自动填充举例第7页填充工具第8页引用功效相对引用：B1；绝对引用$B$1；混合：$B1第9页引用地址自动填充第10页编辑与复制第11页注意引用方式第12页复制全部复制数据第13页数据输入举例求不一样PH值时H2C2O4溶液中各物种平衡浓度第14页由物料平衡可计算平衡时各物种浓度第15页输入步骤=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第16页第17页第18页数据统计计算数据统计计算数据统计 在工作表中输入数据后，往往需对数据进行计算处理，Excel 则提供强大数据计算功效即 数据统计计算，能够对Excel工作表中数据进行准确、高速计算、分析和处理，从而极大地提升工作效率。它包含了简单运算、公式与函数 简单运算是利用Excel提供工具栏中“”按钮快速完成计算 公式是工作表中对数据进行分析和运算等式，它是工作表数据计算中不可缺乏部分。函数是Excel提供一些特殊内置公式，能够进行数学运算或逻辑运算等。与直接使用公式进行计算相比较，使用函数进行计算速度更加快，并可降低错误发生 本讲作为Excel关键部分，对Excel数据计算与分析，含有主要作用，尤其是公式与函数尤其是公式与函数。第19页 公式介绍：公式介绍：公式能够用来执行Excel各种计算，使用公式能够方便而准确地分析工作表中数据。如用户向工作表中输入计算数值时，就能够用公式。公式是工作表中数值进行计算等式，由运算符、单元格引用位置、数值、工作表函数等组成。经过公式，用户能够对工作表中数据进行较为复杂运算数据统计计算数据统计计算 公式公式第20页运算符运算符=（B4+25）/SUM（D5:F5）公式符公式符区域范围引用区域范围引用常量常量函数函数单元格地址单元格地址公式结构公式结构数据统计计算数据统计计算 公式公式第21页算术运算符算术运算符*/+-%说 明乘方乘除加减百分号比较运算符比较运算符=说 明大于大于等于小于小于等于等于不等于引用运算符引用运算符说 明：（冒号）对两个引用之间，包含两个引用在内全部单元格（区域）进行引用，如B2:B8、A2:E10，（逗号）将多个引用合并成一个引用，如SUM（B1:B10,A1:A10,C11）文本运算符文本运算符 说 明连字符&将一个或多个数据以文本形式连接起来形成一个新文本。如公式“=”计算机“&”基础“”，结果为“计算机基础”。Excel公式中运算符公式中运算符数据统计计算数据统计计算 公式公式第22页 注意（很主要）：空格-交叉运算符，如：SUM(B5:B15 A7:D7)两区域交叉单元格之和，即B7 在Excel中,每个运算符都有一定优先级.优先级从高到低分别为:冒号、单个空格、逗号、负号、百分号、插入符号、星号和正斜杠、加号和减号、和号、比较运算符。Excel中运算符中运算符第23页单元格或区域表示方法 单元格区域表示法：单元格区域表示法：独立地址：独立地址：各地址用（，）隔开。比如，=SUM（A1，A8），表示A1和A8两单元格和。连续地址：连续地址：首尾地址用（：）连接。比如，=SUM（A1：A8），冒号表示计算范围从A1到A8单元格之间区域。混合地址：混合地址：比如，=SUM（A1：A6，A8），表示计算A1到A6再加A8和。单元格区域表示法：单元格区域表示法：1）直接输入）直接输入 2）鼠标选中）鼠标选中第24页 （1）公公式式：以以等等号号（=）开开始始，由由连连续续一一组组数数字字和和运运算算符符组成序列表示式。即：组成序列表示式。即：公式形式：=操作数和运算符操作数和运算符 公公式式是是对对数数据据进进行行分分析析与与计计算算等等式式，它它能能够够对对工工作作表表中中数数值值进进行行从从简单四则混合运算直到复杂财务计算、统计分析。简单四则混合运算直到复杂财务计算、统计分析。选定单元格选定单元格在编辑栏（或单元格）中在编辑栏（或单元格）中输入等号输入等号输入公式输入公式回车回车 （2）输入公式并计算）输入公式并计算公式公式输入准则：输入准则：124.5+118.4+115.2+128.7B3+C3+D3+E3SUM(B3,C3,D3,E3)SUM(B3:E3)SUM(B3:E3)数据统计计算数据统计计算 公式公式*若更改求值次序，请将公式中要先计算部分用括号括起来。若更改求值次序，请将公式中要先计算部分用括号括起来。单元格、数字、字符、区域名、区域、函数单元格、数字、字符、区域名、区域、函数第25页图图 输入公式例题输入公式例题第26页 公式操作数据统计计算数据统计计算 公式公式 公式中显示和修改 默认情况下，编辑栏显示公式，单元格显示公式计算结果。故普通情况下，在编辑栏中修改公式。公式复制 用填充法复制公式：先单击已经有公式单元格，然后拖填充柄至最终一个单元格 用“复制/粘贴”法复制公式，假如公式不连续，此法很好。第27页公式插入时犯错提醒错误值涵义#！输入或计算结果数值太长，单元格容纳不下。#VALUE！使用了错误参数，或运算对象类型不对。#DIV/0！公式中除数为 0，或引用了空单元格，或引用了包含 0 值单元格。#NAME?公式中使用了不能识别单元格名称。#N/A 公式或函数中没有可用数值。#REF！单元格引用无效。#NUM！公式或函数中某一数字有问题。#NULL！对两个不相交单元格区域引用使用了交叉引用运算符（空格）。第28页 函数函数 函数是函数是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）为了适应各种计算要求，为了适应各种计算要求，在在Excel中函数共分为十类：中函数共分为十类：惯用、惯用、财务、日期与时间、数学与三角函数、统计、查找与引用、数财务、日期与时间、数学与三角函数、统计、查找与引用、数据库、文本、逻辑和信息函数据库、文本、逻辑和信息函数第29页或按按“shift+F3”直接打直接打开开321 函数查看第30页财务函数：可数值进行普通财务计算，如确定贷款支付额、投资未来值或净现值等。日期和时间：在公式中分析和处理日期值，如TODAY（）返回系统当前日期。查询和引用：查询特定数值，或者查找某一单元格引用。数据库：分析数据库中数和时间值，如数字取整、求和、按给定条件求和等。数学和三角：处理简单或复杂数学计算，如直线斜率和y轴截距等。.统计：对数据区域进行统计，如计算全部数值大于100且小于250统计总数。文本：在公式中处理文字串，如提取文字串中字符等。逻辑：进行真假值判断，或复合检验。如IF（）函数可确定条件真假并返回对应数值。信息：能够测试/确认存放在单元格中数据类型。函数分类工程函数：对数值进行各种工程上运算和分析 外部函数：使用加载项提供自定义命令或自定义功效补充程序第31页 Excel任何函数都有一样标准结构，即：都是由函数名和参数两部分组成。将函数作为另一个函数参数也称为嵌套函数。不一样类型函数要求给定不一样类型函数要求给定不一样类型参数不一样类型参数函数准则函数准则函数准则函数准则：函数名函数名（参数参数1，参数参数2，）代表函数功效代表函数功效函数输入准则函数输入准则 第32页 函数和公式不一样，公式是在工作表中队数据进行分析等式，函数和公式不一样，公式是在工作表中队数据进行分析等式，它能够引用一个工作表多个单元格、同一工作薄不一样工作表单元它能够引用一个工作表多个单元格、同一工作薄不一样工作表单元格以及其它工作薄中工作表；而函数是预定义内置公式，它使用被格以及其它工作薄中工作表；而函数是预定义内置公式，它使用被称为参数特定数值，并按特定次序计算称为参数特定数值，并按特定次序计算 函数是一个特殊公式，所以全部函数都要以函数是一个特殊公式，所以全部函数都要以“=”开使，开使，其次是函数名称，然后是括号，括号表示其中括住内容是此函数必需参数。假如函数对应参数不只一个时，就必须按照要求次序依次排列各参数，而且用逗号把它们隔离开来第33页 手工输入：手工输入：选定单元格选定单元格 “=”输入函数输入函数 确定确定 粘贴函数（粘贴函数（“函数向导函数向导”）：首先首先选定要输入函数单元格选定要输入函数单元格 1）单击工具栏上）单击工具栏上“自动求和自动求和”按钮中按钮中“其它函数其它函数”选择类别选择类别 选择函数选择函数输入参数输入参数 确定确定 2）“插入插入/函数函数”选择类别选择类别 选择函数选择函数输入参数输入参数 确定确定 3）单击单击“插入函数插入函数”钮钮 选择类别选择类别 选择函数选择函数 输入参数输入参数 确定确定输入输入函数方法：函数方法：直接输入法、向导法直接输入法、向导法第34页惯用函数惯用函数 数学函数：数学函数：（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列数据求和。第35页（3）AVERAGE（）：平均值（）：平均值函数函数 格式：格式：=AVERAGE（参数（参数1,参数参数2,）功效：求个参数平均值功效：求个参数平均值 如如 =AVERAGE(E12:I12)假如引用区域中包含“0”值单元格，则计算在内；假如引用区域中包含空白或字符单元格，则不计算在内（4）ROUND（）（）：四舍五入函数：四舍五入函数 格式：格式：ROUND（数值，位数）（数值，位数）功效：对数值项进行四舍五入功效：对数值项进行四舍五入。若位数0，表示保留位数指定小数；位数=0，表示保留整数；位数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 惯用函数惯用函数 数学函数：数学函数：第36页（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,=COUNTIF(B1:B13,“60G3,G6G3,G6H6)惯用函数惯用函数第39页 费用：费用：=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,差)惯用函数使用：惯用函数使用：第40页 时间函数时间函数计算机当前内部日期和时间now()now()-括号无参数=(NOW()-E46)/365 =（当前日期-进厂日期）/365工龄计算：“C T R L+：”显 示 系 统 日 期“CTRL+SHIFT+：”显示系统时间 惯用函数惯用函数第41页（1）RANK:排名次函数排名次函数 格式：格式：RANK（参数，（参数，数据区域，升降序）数据区域，升降序）功效：计算某数值在数据区域内相对其它数值大小排位功效：计算某数值在数据区域内相对其它数值大小排位（2）VLOOKUP：搜索函数：搜索函数（搜索表区域首列满足（搜索表区域首列满足条件元素）条件元素）格式：格式：VLOOKUP（比较值（比较值,搜索区域，取值列号，是否准确判搜索区域，取值列号，是否准确判断）断）功效：搜索表区域内首列满足条件元素，找到时返回所在行指功效：搜索表区域内首列满足条件元素，找到时返回所在行指定列值定列值（3）LOOKUP：从单行或单列或数组中查找一个值；从单行或单列或数组中查找一个值；查找与引用函数 惯用函数惯用函数=函数名（要查找值函数名（要查找值,选择数组选择数组,选择数组选择数组)格式：格式：如：=LOOKUP(沈克,B4:B19,G4:G19)第42页财务函数 惯用函数惯用函数 PMT ：计算在固定利率下，贷款等额分期偿还额 格式：=PMT(rate,nper,pv,FV,type)a.PV 返回投资现值返回投资现值(本金本金)b.RATE 各期利率各期利率c.NPER 为总投资期为总投资期d.FV 未来值未来值,或在最终一次付款后希望得到现金金额或在最终一次付款后希望得到现金金额,省略为省略为”0”e.TYPE 指定各期付款时间指定各期付款时间”0”期末期末”1”期期=PMT（年率/12，年数*12，-货款总额）第43页单元格引用单元格引用 即以单元格为计算对象；即以单元格为计算对象；引用作用是标识工作表上单元格或单元格区域，并指明公式中所使用数据位置。经过引用在Excel中，能够在公式中使用工作表不一样单元格中数据，或同一工作簿不一样工作表单元格。对单元格引用有三种方法：相对引用、绝对引用和混合引用对单元格引用有三种方法：相对引用、绝对引用和混合引用 单元格或单元格区域引用普通式为：工作表名！单元格引用 或 工作簿名工作表名！单元格引用 在引用同一工作簿单元格时，工作簿能够省略，在引用同一工作表时，工作表能够省略。例：=E12+5 引用了同一工作表E12单元格；=Sheet2!A2+Sheet3!A2 引用了工作表Sheet2A2单元格和工作表Sheet3A2单元格。第44页（）（）（）（）相对地址引用：相对地址引用：相对地址引用：相对地址引用：是指用列号和行号直接表示地址 包含单元格相对引用和单元格区域相对引用。相对引用特点相对引用特点当含公式单元格移动、复制到一个新位置当含公式单元格移动、复制到一个新位置时，公式中相对地址会随之发生改变时，公式中相对地址会随之发生改变。1）单元格相对引用。直接用单元格所在列号和行号作为其引用，比如A3是引用了第A列与第3行交叉处单元格；B4、C5 2）单元格区域相对引用是由单元格区域左上角单元格相对引用和右下角单元格相对引用组成，中间用冒号隔开，如“D2:J6”表示以D2单元格为左上角，J6单元格为右下角矩形单元格区域。单元格引用单元格引用第45页公式复制例题公式复制例题 第46页相对地址一些注意假如把某个单元格中公式移到另一个单元格时，在原单元格内公式中地址在新单元格中保持不变。假如复制单元格中公式，在复制新单元格中，公式中地址将作对应改变。如C1单元格公式是=A1+B2，假如把C1单元格公式移到C2，C2单元格公式仍是=A1+B2。若把C1单元格公式复制到C2，C2单元格中公式会变成=A2+B3。这是因为把C1单元格公式复制到C2单元格过程中，行号加了1，由此引发公式中行号也自动加1。若将C1单元格公式复制到E5单元格，E5单元格中公式就是=C5+D6，列号与行号分别在原来基础上加了C1至E5差值。第47页（）（）（）（）绝对地址引用：绝对地址引用：绝对地址引用：绝对地址引用：公式中单元格确实切位置保持不变，在单元格行号和列号前加上“”符号来表示 比如：B2绝对地址为$B$2 绝对引用特点绝对引用特点当公式移动、复制到一个新位置时，公式当公式移动、复制到一个新位置时，公式中中绝对地址不会发生改变绝对地址不会发生改变。（）（）（）（）混合地址引用：混合地址引用：混合地址引用：混合地址引用：在单元格中现有相对地址引用，也有绝对地址引用。混合地址是指单元格地址行号或列混合地址是指单元格地址行号或列号前不一样时加号前不一样时加“$”符号符号 比如：B2混合地址为$B2或B$2 “B2”表示行将会随之改变，而列不变。相反“B2”则表示行不变，而列将会随之改变。单元格引用单元格引用第48页混合引用例题混合引用例题第49页相对相对相对相对地址：地址：A2绝对绝对绝对绝对地址地址:$A$2混合混合混合混合地址：地址：$A2 A$2E4=B1+A2+C2按按F4F4，可在以上三种地址引用之间进行切换，可在以上三种地址引用之间进行切换 单元格地址在公式中作用第50页单元格引用单元格引用比如：Sheetn!B2 （4 4）三维地址引用）三维地址引用）三维地址引用）三维地址引用三维地址引用特点三维地址引用特点引用不一样工作表中单元格引用不一样工作表中单元格 三维引用主要用于两个或多个工作表中单元格或单元格区域中数据，也可用于同一个工作簿中不一样工作表中单元格区域。三维引用普通格式为“工作表名！:单元格地址”。工作表名后跟符号“！”是系统自带。第51页三维地址引用三维地址引用三维地址引用三维地址引用第52页第53页