Excel公式常用函数及地址引用省公共课一等奖全国赛课获奖课件.pptx

第2章l公式、惯用函数与地址引用公式、惯用函数与地址引用 第1页2主要内容o简单计算o表示式与公式o惯用函数及其应用o地址引用o数组与数组运算o应用举例o错误值第2页3o4课时o本章重点：n n运算符、公式与惯用函数运算符、公式与惯用函数n n相对地址、绝对地址和混合地址相对地址、绝对地址和混合地址n n公式复制对公式中地址引用影响公式复制对公式中地址引用影响o难点：n n相对地址、绝对地址和混合地址正确使用相对地址、绝对地址和混合地址正确使用公式、惯用函数与地址引用第3页42.1 简单计算1.自动求和n选定一组数，右击“状态栏”看这组数算数累加和、平均值、最大值、最小值等2.简单计算n选定一组数，在工具栏“”中选择算数累加和、平均值、最大值、最小值等。o练习：练习：第第2 2章章.xls.xls计算实发工资计算实发工资!第4页52.2 表示式与公式o公式：=o表示式包含：n常量(见第1章，数据类型)n运算符n地址引用n函数o例n=100+C4*AVERAGE(B2:B10)第5页62.2 表示式与公式运算符运算符n n算术运算符算术运算符+、-、*、/、(乘方乘方)、%优先级别优先级别:由高到低由高到低()()函数函数 *,/*,/+,-+,-公式转换为数值操作：选定公式所在区域选定公式所在区域 复制复制 选择性粘贴选择性粘贴数值数值算术表示式算术表示式 例例 2323 等价等价2 23 3,输入：输入：=23=23第6页7n n关系运算符关系运算符:=、=、=、(不等)n n文本连接运算符文本连接运算符 o练习：练习：第第2 2章章.xls.xls公式公式!o练习：练习：第第2 2章章.xls.xls字符串连接字符串连接!关系表示式关系表示式 例例 2=5 2=5（FALSEFALSE）输入：输入：=2=52=5 “ABCABC”=“AbCAbC”(TRUE)(TRUE)输入：输入：=“ABC”=“AbCABC”=“AbC”文本表示式文本表示式文本表示式文本表示式 例例例例 “ABC”&“DE”等于“ABCDE”输入：=“ABC”&“DE”例A1=“中国”,B1=“北京”，C1=A1&B1,则C1=“中国北京”2.2 表示式与公式第7页82.3 惯用函数及其应用o函数格式o 函数名(参数表)o函数输入方法o直接在单元格输入ofx向导输入o函数嵌套o例oROUND(AVERAGE(A10:C10),0)o最多嵌套7层（）o最多嵌套64层（版）第8页92.3 惯用函数及其应用1.1.求和求和 SUMSUM(参数参数1,1,参数参数2,2,)2.2.求平均值求平均值 AVERAGEAVERAGE(参数参数1,1,参数参数2,2,)3.3.求最大值求最大值 MAXMAX(参数参数1,1,参数参数2,2,)4.4.求最小值求最小值 MINMIN(参数参数1,1,参数参数2,2,)其中参数个数其中参数个数3030o练习：练习：第第2 2章章.xls.xls分理处汇总表分理处汇总表!例例例例=SUM(A1:D1)=SUM(A1:D1)或或或或=SUM(A1,B1,C1,D1)SUM(A1,B1,C1,D1)或或或或=A1+B1+C1+D1=A1+B1+C1+D1例例例例 =AVERAGE(A1:D1)=AVERAGE(A1:D1)或或或或 =AVERAGE(A1,B1,C1,D1)=AVERAGE(A1,B1,C1,D1)或或或或 =(A1+B1+C1+D1)/4=(A1+B1+C1+D1)/4第9页102.3 惯用函数及其应用5.5.统计个数统计个数（1 1）统计数值型数据个数）统计数值型数据个数 COUNTCOUNT（参数（参数1 1，参数，参数2 2，）（2 2）统计非空单元格个数）统计非空单元格个数 COUNTACOUNTA（参数（参数1 1，参数，参数2 2，）（3 3）统计空单元格个数）统计空单元格个数 COUNTBLANKCOUNTBLANK（参数（参数1 1，参数，参数2 2，）oo练习：练习：第第2 2章章.xls.xls简单函数！简单函数！oo假假如如复复制制公公式式后后，复复制制后后公公式式不不一一样样，不不过过显显示示一一样样结结果果，是是因因为为系系统统为为“手手动动计计算算”，改改为为“自自动动重重算算”即即可。可。n n：OFFICEOFFICE按钮，选项，公式，选中按钮，选项，公式，选中“自动重算自动重算”n n：工具，选项，重新计算，选中工具，选项，重新计算，选中“自动重算自动重算”第10页o默认情况下，单元格输入公式，显示公式值。默认情况下，单元格输入公式，显示公式值。假如假如改变显示改变显示为公式，操作：（反之亦然为公式，操作：（反之亦然）n n步骤步骤1 1：l l：officeoffice按钮，选项，高级，此工作表选项中按钮，选项，高级，此工作表选项中l l：工具菜单：工具菜单 ，选项，视图，窗口选项，选项，视图，窗口选项n n步骤步骤2 2：选中：选中/放弃放弃”公式公式”选项选项o公式转为数值操作（反之不然）公式转为数值操作（反之不然）n n选定公式所在单元格区域，单击右键选选定公式所在单元格区域，单击右键选“复制复制”，单击右键选单击右键选“选择性粘贴选择性粘贴”，选择，选择“数值数值”。11第11页126.四舍五入函数 ROUNDROUND（参数，（参数，n n）功效：功效：取四舍五入到第取四舍五入到第n n位近似值位近似值。=ROUND=ROUND（825.746825.746，2 2）=825.75=825.75=ROUND=ROUND（825.746825.746，1 1）=825.7=825.7=ROUND=ROUND（825.746825.746，0 0）=826 =826 =ROUND=ROUND（825.746825.746，1 1）=830=830=ROUND=ROUND（825.746825.746，2 2）=800=800 o练习：练习：第第2 2章章.xls.xls简单函数！简单函数！2.3 惯用函数及其应用看出规律第12页137.7.条件函数条件函数IFIF（逻辑表示式，表示式（逻辑表示式，表示式1 1，表示式，表示式2 2）功效：功效：逻辑表示式为逻辑表示式为“真值真值”，结果为，结果为“表示式表示式1 1”值，不然为表值，不然为表示式示式2 2值。值。例例 在在C2C2单元格输入：单元格输入：=IF(=IF(A2B2,100,200)A2B2,100,200)=IF(=IF(A2=60,A2=60,truetrue,false),false)=IF(IF(A2=60,A2=60,及格及格,不及格不及格)=IF(A260,=IF(A260,=IF(A260,=IF(A260,不及格不及格不及格不及格,IF(A270,IF(A270,IF(A270,IF(A270,及格及格及格及格,良好良好良好良好/优异优异优异优异)=IF(A260,=IF(A260,=IF(A260,=IF(A260,不及格不及格不及格不及格,IF(A270,IF(A270,IF(A270,IF(A270,及格及格及格及格,IF(A2=84,IF(A2=84,IF(A2=84,IF(A2=60”)o9.条件求和oSUMIF(条件区域，条件，求和区域)o功效:求与“条件区域”中满足“条件”单元格相对应“求和区域”单元格数据累加和。o 假如省略“求和区域”，对“条件区域”求和。o10.条件求平均值(有该函数)o AVERAGEIF(条件区域，条件，求和区域)o 功效:求与“条件区域”中满足“条件”单元格相对应“求和区域”单元格数据平均值o 假如省略“求和区域”，对“条件区域”求平均值。2.3 惯用函数及其应用第14页15应用举例 例5 例6第15页16SUMIF 函数举例p 练习：第2章.xlssumif,countif!第16页172.4 地址引用相对地址、绝对地址和混合地址n n“A1”A1”访问方式访问方式l l相对地址相对地址 ：B1B1（复制时，相对位置不变）复制时，相对位置不变）l l绝对地址绝对地址 ：$B$1$B$1（复制时，一直为固定地址）复制时，一直为固定地址）l l混合地址混合地址 ：B$1,$B1B$1,$B1（上述相对（上述相对/绝对混合绝对混合）技巧：按技巧：按 F4F4键键 自动转换：自动转换：B1$B$1 B$1$B1B1$B$1 B$1$B1o绝对地址应用练习：绝对地址应用练习：n n 第第2 2章章.xls.xls商品价目表商品价目表!o混合地址应用练习：混合地址应用练习：n n 第第2 2章章.xls.xls九九乘法表九九乘法表!第17页18oo“R1C1”“R1C1”访问方式（列访问方式（列1-2561-256，行，行1-655361-65536）n n相对地址：相对地址：RR数字数字CC数字数字 l lR R表示行，表示行，C C表示列表示列 例：例：R-1C0 R-1C0 或或 R-1C R-1C 引用相对当前行上引用相对当前行上1 1行，当前列单元格行，当前列单元格 例：例：R0C-2 R0C-2 或或 RC-2 RC-2 引用当前行，相对当前列上引用当前行，相对当前列上2 2列单元格列单元格n n绝对地址引用：绝对地址引用：R R数字数字C C数字数字 例：例：R1C2 R1C2 引用第引用第1 1行第行第2 2列单元格列单元格n n混合地址引用：混合地址引用：RR数字数字CC数字数字 或或R R数字数字CC数字数字 例：例：R1C R1C 引用第引用第1 1行当前列单元格行当前列单元格 例：例：R2C5 R2C5 引用当前行，引用相对当前行下引用当前行，引用相对当前行下2 2行第行第5 5列单元格列单元格oo观察观察 第第2 2章章.xls.xls商品价目表商品价目表!“R1C1”“R1C1”访问方式访问方式（见下页）（见下页）2.4 地址引用“A1”与“R1C1”转换：步骤1：:“工具”菜单选项常规卡 版:Office按钮excel选项公式步骤2：选中“RICI引用样式”第18页19DD4=4=B B4*4*C C4 4 等价等价R4CR4C4 4=RC=RC-2-2*RC*RC-1-1 B B8=SUM(B4:B7)8=SUM(B4:B7)等价等价R8CR8C2 2=SUM(R-4C:R-=SUM(R-4C:R-1C)1C)E4=B4*E4=B4*$B$B$1$1 等价等价R4C5=RC-3*R1CR4C5=RC-3*R1C2 22.4 地址引用第19页20o复制移动公式n n复制公式复制公式l l复制后，公式中引用复制后，公式中引用相相对地址变，不过相对位置对地址变，不过相对位置不变；不变；l l复制后，公式中引用复制后，公式中引用绝对地址不会变。绝对地址不会变。n n移动公式移动公式：移动后，移动后，公式引用地址均不变。公式引用地址均不变。n n比如：比如：C1C1$A$3$A$3D5D5，将将C1 C1 复制到复制到E3E3，则，则E3E3？移动到移动到E3E3，则，则E3E3？2.4 地址引用E3E3$A$3$A$3F7 F7 E3E3$A$3$A$3D5D5第20页21o移动公式中引用单元格移动公式中引用单元格观察：观察：假如假如A1=1A1=1，B1=2B1=2，例：当例：当C1C1$A$1$A$1B1B1，将将A1A1、B1B1数据数据 移动移动 到到A2A2、B2B2 ，则则C1C1？C1C1$A$A$2 2B B2 2 C1C1值不变值不变例：当例：当C1=SUM(C1=SUM(A1,B1A1,B1)，将将A1A1、B1B1数据数据 移动移动 到到A2A2、B2B2 ，则则C1C1？C1=SUM(C1=SUM(A2,B2A2,B2)C1)C1值不变值不变o练习：练习：第第2 2章章.xls.xls移动公式、数据移动公式、数据!2.4 地址引用第21页22o插入删除单元格对公式影响n n插入删除引用区域一部分插入删除引用区域一部分l l“引用引用”自动调整自动调整（比如工资表内插入一个人统（比如工资表内插入一个人统计）计）n n插入删除公式引用整个区域：插入删除公式引用整个区域：l lREF!REF!比如比如 A5A5SUM(A1:A4)SUM(A1:A4)假如在第假如在第1 1行第行第4 4行之间插入行之间插入/删除行，删除行，则则 A5A5？比如比如 F1F1E1/4E1/4，假如删除，假如删除E1E1单元格，则单元格，则 F1=F1=？o练习：第2章.xls插入删除!2.4 地址引用第22页23oo一个区域输入一组相同数：一个区域输入一组相同数：n n操作：选定区域，输入一个数，按操作：选定区域，输入一个数，按Ctrl+Enter Ctrl+Enter 键键oo数组数组:用于对一组数进行计算，得到一组结果。用于对一组数进行计算，得到一组结果。o输入输入/定义数组操定义数组操作：作：（1 1）选择一个存放计算结果单元格区域）选择一个存放计算结果单元格区域（2 2）输入公式（可能引用输入公式（可能引用单元格区域单元格区域）后，不要按回车健）后，不要按回车健（3 3）按）按Ctrl+Shift+Enter Ctrl+Shift+Enter 键键o练习：练习：第第2 2章章.xls.xls数组数组!2.5 数组与数组公式第23页2.5 数组与数组公式o数组公式与普通公式比较，数组特点：n计算对象是一个数或一组数，结果是一个数计算对象是一个数或一组数，结果是一个数或一组数。或一组数。n不能修改其中任何一个结果（起到保护结果不能修改其中任何一个结果（起到保护结果作用）作用）n修改修改/删除数组公式：必须修改删除数组公式：必须修改/删除删除整个数整个数组组o数组运算n本身算数运算、二个数组算数运算（矩阵加本身算数运算、二个数组算数运算（矩阵加/减法）减法）第24页252.6 应用举例o例1 银行存款利息计算o例2 实发工资与工新税计算o例3 财务数据计算o例4 收益预测表第25页262.7 错误值错误值错误值o错误值出现原因举举例例例例说说明明明明#DIV/0!#DIV/0!除数除数为为0 0=3/0=3/0N/AN/Ao引用了无法使用数值o比如VLOOKUP第1个参数对应单元格为空#NAME?#NAME?o不能识别名字=SUN(A1:A4)=SUN(A1:A4)#NULL!#NULL!交集交集为为空空=SUM(A1:A3 B1:B3)=SUM(A1:A3 B1:B3)#NUM!#NUM!数据数据类类型不正确型不正确=SQRT(-4)=SQRT(-4)#REF!#REF!引用无效引用无效单单元格元格o比如引用单元格被删除#VALUE!#VALUE!o不正确参数或运算符=1+=1+”a a”#宽宽度不度不够够o加宽列宽度第26页作业lP41 习题习题lP159 第第10章章 试验试验2l预习第预习第3章章第27页