Excel公式、常用函数及地址引用.ppt

《Excel公式、常用函数及地址引用.ppt》由会员分享，可在线阅读，更多相关《Excel公式、常用函数及地址引用.ppt（27页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、第2章l公式、常用函数与地址引用公式、常用函数与地址引用 2主要内容o简单计算o表达式与公式o常用函数及其应用o地址引用o数组与数组运算o应用举例o错误值3o4课时o本章重点：n n运算符、公式与常用函数运算符、公式与常用函数n n相对地址、绝对地址和混合地址相对地址、绝对地址和混合地址n n公式的复制对公式中地址引用的影响公式的复制对公式中地址引用的影响o难点：n n相对地址、绝对地址和混合地址的正确使用相对地址、绝对地址和混合地址的正确使用公式、常用函数与地址引用42.1 简单计算1.自动求和n选定一组数，右击“状态栏”看这组数的算数累加和、平均值、最大值、最小值等2.简单计算n选定一组数

2、，在工具栏“”中选择算数累加和、平均值、最大值、最小值等。o练习：练习：第第2 2章章.xls.xls计算实发工资计算实发工资!52.2 表达式与公式o公式：=o表达式包含：n常量(见第1章，数据类型)n运算符n地址引用n函数o例n=100+C4*AVERAGE(B2:B10)62.2 表达式与公式运算符运算符n n算术运算符算术运算符+、-、*、/、(乘方乘方)、%优先级别优先级别:由高到低由高到低()()函数函数 *,/*,/+,-+,-公式转换为数值操作：选定公式所在区域选定公式所在区域 复制复制 选择性粘贴选择性粘贴数值数值算术表达式算术表达式 例例 2323 等价等价2 23 3,输

3、入：输入：=23=237n 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&B

4、1,则C1=“中国北京”2.2 表达式与公式82.3 常用函数及其应用o函数的格式 函数名函数名(参数表参数表)o函数输入方法n n直接在单元格输入直接在单元格输入n nfxfx向导输入向导输入o函数嵌套n n例例ROUND(AVERAGE(A10:C10),0)ROUND(AVERAGE(A10:C10),0)n n最多嵌套最多嵌套7 7层（层（20032003版）版）n n最多嵌套最多嵌套6464层（层（20072007版）版）92.3 常用函数及其应用1.1.求和求和 SUMSUM(参数参数1,1,参数参数2,2,)2.2.求平均值求平均值 AVERAGEAVERAGE(参数参数1,1,

5、参数参数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,

6、B1,C1,D1)或或或或 =(A1+B1+C1+D1)/4=(A1+B1+C1+D1)/4102.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如如果果复复制制公公式式

7、后后，复复制制后后公公式式不不同同，但但是是显显示示同同样样的的结结果果，是是因因为为系系统统为为“手手动动计计算算”，改改为为“自自动动重重算算”即即可。可。n n2007200720072007：OFFICEOFFICE按钮，选项，公式，选中按钮，选项，公式，选中“自动重算自动重算”n n2003200320032003：工具，选项，重新计算，选中工具，选项，重新计算，选中“自动重算自动重算”o默认情况下，单元格输入公式，显示公式的值。默认情况下，单元格输入公式，显示公式的值。如果如果改变显示改变显示为公式，操作：（反之亦然为公式，操作：（反之亦然）n n步骤步骤1 1：l l200720

8、07：officeoffice按钮，选项，高级，此工作表的选项中按钮，选项，高级，此工作表的选项中l l20032003：工具菜单：工具菜单 ，选项，视图，窗口选项，选项，视图，窗口选项n n步骤步骤2 2：选中：选中/放弃放弃”公式公式”选项选项o公式转为数值的操作（反之不然）公式转为数值的操作（反之不然）n n选定公式所在的单元格区域，单击右键选选定公式所在的单元格区域，单击右键选“复制复制”，单击右键选，单击右键选“选择性粘贴选择性粘贴”，选择，选择“数值数值”。11126.四舍五入函数 ROUNDROUND（参数，（参数，n n）功能：功能：取四舍五入到第取四舍五入到第n n位的近似值

9、位的近似值。=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 常用函数及其应用看出规律137.7.条件函数条件函数IFIF（逻辑表达式，表达式（逻辑表达

10、式，表达式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,及

11、格及格及格及格,良好良好良好良好/优秀优秀优秀优秀)=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”)COUNTIF(b2:d2,”=60”)9.9.条件求和条件求和SUMIF(SUMIF(条件区域，条件，求和区域条件区域，条件，求和区域)功能功能:求与求与“条件区域条件区域”中满足中满足“条件条件”的单元格相对应的的单元格相对应的“求和区域求和区域”单元格数据的累加和。单元格数据的累加和。如

12、果省略如果省略如果省略如果省略“求和区域求和区域求和区域求和区域”，对，对，对，对“条件区域条件区域条件区域条件区域”求和。求和。求和。求和。10.10.条件求平均值条件求平均值条件求平均值条件求平均值(2007(2007版有该函数版有该函数)AVERAGEIF(AVERAGEIF(条件区域，条件，求和区域条件区域，条件，求和区域)功能功能:求与求与“条件区域条件区域”中满足中满足“条件条件”的单元格相对应的的单元格相对应的“求和区求和区域域”单元格数据的单元格数据的平均值平均值平均值平均值 如果省略如果省略如果省略如果省略“求和区域求和区域求和区域求和区域”，对，对，对，对“条件区域条件区域

13、条件区域条件区域”求平均值。求平均值。求平均值。求平均值。2.3 常用函数及其应用15应用举例 例5 例616SUMIF 函数举例p 练习：第2章.xlssumif,countif!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

14、$1$B1B1$B$1 B$1$B1o绝对地址应用练习：绝对地址应用练习：n n 第第2 2章章.xls.xls商品价目表商品价目表!o混合地址应用练习：混合地址应用练习：n n 第第2 2章章.xls.xls九九乘法表九九乘法表!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

15、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商品价目表商品价

16、目表!的的“R1C1”“R1C1”访问方式访问方式（见下页）（见下页）2.4 地址引用“A1”与“R1C1”转换：步骤1：2003版:“工具”菜单选项常规卡 2007版:Office按 钮excel选项公式步骤2：选中“RICI引用样式”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

17、.4 地址引用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$3D5D521o移动公式中引用的单元格移动公式中引用的单元格

18、观察：观察：如果如果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 地址引用22o插入删除单元格对公式的影响n

19、 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 地址引用23oo一个区域输入一组相同的数：一个区域输入一组相同的数：n n操作：

20、选定区域，输入一个数，按操作：选定区域，输入一个数，按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 数组与数组公式2.5 数组与数组公式o数组公

21、式与一般公式比较，数组特点：n计算的对象是一个数或一组数，结果是一个计算的对象是一个数或一组数，结果是一个数或一组数。数或一组数。n不能修改其中任何一个结果（起到保护结果不能修改其中任何一个结果（起到保护结果的作用）的作用）n修改修改/删除数组公式：必须修改删除数组公式：必须修改/删除删除整个数整个数组组o数组的运算n自身的算数运算、二个数组的算数运算（矩自身的算数运算、二个数组的算数运算（矩阵加阵加/减法）减法）252.6 应用举例o例1 银行存款利息的计算o例2 实发工资与工新税的计算o例3 财务数据计算o例4 收益预测表262.7 错误值错误值错误值错误值错误值出出出出现现的原因的原因的

22、原因的原因举举例例例例说说明明明明#DIV/0!#DIV/0!除数除数为为0 0=3/0=3/0N/AN/A引用了无法使用的数引用了无法使用的数值值例如例如VLOOKUPVLOOKUP第第1 1个参个参数对应的单元格为空数对应的单元格为空#NAME?#NAME?不能不能识别识别的名字的名字=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!引用无效引用无效单单元格元格例如引用的例如引用的单单元格被元格被删删除除#VALUE!#VALUE!不正确的参数或运算符不正确的参数或运算符=1+=1+”a a”#宽宽度不度不够够加加宽宽列的列的宽宽度度作业lP41 习题习题lP159 第第10章章 实验实验2l预习第预习第3章章