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

第2章,公式、常用函数与地址引用,2,主要内容,简单计算表达式与公式常用函数及其应用地址引用数组与数组运算应用举例错误值,3,4课时本章重点：运算符、公式与常用函数相对地址、绝对地址和混合地址公式的复制对公式中地址引用的影响难点：相对地址、绝对地址和混合地址的正确使用,公式、常用函数与地址引用,4,2.1简单计算,1.自动求和选定一组数，右击“状态栏”看这组数的算数累加和、平均值、最大值、最小值等2.简单计算选定一组数，在工具栏“”中选择算数累加和、平均值、最大值、最小值等。练习：第2章.xls计算实发工资!,5,2.2表达式与公式,公式：=表达式包含：常量(见第1章，数据类型)运算符地址引用函数例=100+C4*AVERAGE(B2:B10),6,2.2表达式与公式,运算符算术运算符+、-、*、/、(乘方)、%优先级别:由高到低()函数*,/+,-公式转换为数值操作：选定公式所在区域复制选择性粘贴数值,算术表达式例23等价23,输入：=23,7,关系运算符:=、=、(不等)文本连接运算符练习：第2章.xls公式!练习：第2章.xls字符串连接!,关系表达式例2=5（FALSE）输入：=2=5“ABC”=“AbC”(TRUE)输入：=“ABC”=“AbC”,文本表达式例“ABC”&“DE”等于“ABCDE”输入：=“ABC”&“DE”例A1=“中国”,B1=“北京”，C1=A1&B1,则C1=“中国北京”,2.2表达式与公式,8,2.3常用函数及其应用,函数的格式函数名(参数表)函数输入方法直接在单元格输入fx向导输入函数嵌套例ROUND(AVERAGE(A10:C10),0)最多嵌套7层（2003版）最多嵌套64层（2007版）,9,2.3常用函数及其应用,1.求和SUM(参数1,参数2,)2.求平均值AVERAGE(参数1,参数2,)3.求最大值MAX(参数1,参数2,)4.求最小值MIN(参数1,参数2,)其中参数的个数30练习：第2章.xls分理处汇总表!,例=SUM(A1:D1)或=SUM(A1,B1,C1,D1)或=A1+B1+C1+D1,例=AVERAGE(A1:D1)或=AVERAGE(A1,B1,C1,D1)或=(A1+B1+C1+D1)/4,10,2.3常用函数及其应用,5.统计个数（1）统计数值型数据的个数COUNT（参数1，参数2，）（2）统计非空单元格的个数COUNTA（参数1，参数2，）（3）统计空单元格的个数COUNTBLANK（参数1，参数2，）练习：第2章.xls简单函数！如果复制公式后，复制后公式不同，但是显示同样的结果，是因为系统为“手动计算”，改为“自动重算”即可。2007：OFFICE按钮，选项，公式，选中“自动重算”2003：工具，选项，重新计算，选中“自动重算”,默认情况下，单元格输入公式，显示公式的值。如果改变显示为公式，操作：（反之亦然）步骤1：2007：office按钮，选项，高级，此工作表的选项中2003：工具菜单，选项，视图，窗口选项步骤2：选中/放弃”公式”选项公式转为数值的操作（反之不然）选定公式所在的单元格区域，单击右键选“复制”，单击右键选“选择性粘贴”，选择“数值”。,11,12,6.四舍五入函数ROUND（参数，n）功能：取四舍五入到第n位的近似值。=ROUND（825.746，2）=825.75=ROUND（825.746，1）=825.7=ROUND（825.746，0）=826=ROUND（825.746，1）=830=ROUND（825.746，2）=800练习：第2章.xls简单函数！,2.3常用函数及其应用,看出规律,13,7.条件函数IF（逻辑表达式，表达式1，表达式2）功能：逻辑表达式为“真值”，结果为“表达式1”的值，否则为表达式2的值。例在C2单元格输入：=IF(A2B2,100,200)=IF(A2=60,true,false)=IF(A2=60,及格,不及格)=IF(A260,不及格,IF(A270,及格,良好/优秀)=IF(A260,不及格,IF(A270,及格,IF(A2=60”)9.条件求和SUMIF(条件区域，条件，求和区域)功能:求与“条件区域”中满足“条件”的单元格相对应的“求和区域”单元格数据的累加和。如果省略“求和区域”，对“条件区域”求和。10.条件求平均值(2007版有该函数)AVERAGEIF(条件区域，条件，求和区域)功能:求与“条件区域”中满足“条件”的单元格相对应的“求和区域”单元格数据的平均值如果省略“求和区域”，对“条件区域”求平均值。,2.3常用函数及其应用,15,应用举例例5例6,16,SUMIF函数举例,练习：第2章.xlssumif,countif!,17,2.4地址引用,相对地址、绝对地址和混合地址“A1”访问方式相对地址：B1（复制时，相对位置不变）绝对地址：$B$1（复制时，始终为固定地址）混合地址：B$1,$B1（上述相对/绝对混合）技巧：按F4键自动转换：B1$B$1B$1$B1绝对地址应用练习：第2章.xls商品价目表!混合地址应用练习：第2章.xls九九乘法表!,18,“R1C1”访问方式（列1-256，行1-65536）相对地址：R数字C数字R表示行，C表示列例：R-1C0或R-1C引用相对当前行的上1行，当前列单元格例：R0C-2或RC-2引用当前行，相对当前列的上2列单元格绝对地址引用：R数字C数字例：R1C2引用第1行第2列单元格混合地址引用：R数字C数字或R数字C数字例：R1C引用第1行当前列单元格例：R2C5引用当前行，引用相对当前行的下2行第5列单元格观察第2章.xls商品价目表!的“R1C1”访问方式（见下页）,2.4地址引用,“A1”与“R1C1”转换：步骤1：2003版:“工具”菜单选项常规卡2007版:Office按钮excel选项公式步骤2：选中“RICI引用样式”,19,D4=B4*C4等价R4C4=RC-2*RC-1B8=SUM(B4:B7)等价R8C2=SUM(R-4C:R-1C)E4=B4*$B$1等价R4C5=RC-3*R1C2,2.4地址引用,20,复制移动公式复制公式复制后，公式中引用的相对地址变，但是相对位置不变；复制后，公式中引用的绝对地址不会变。移动公式：移动后，公式引用的地址均不变。例如：C1$A$3D5，将C1复制到E3，则E3？移动到E3，则E3？,2.4地址引用,E3$A$3F7E3$A$3D5,21,移动公式中引用的单元格观察：如果A1=1，B1=2，例：当C1$A$1B1，将A1、B1的数据移动到A2、B2，则C1？C1$A$2B2C1的值不变例：当C1=SUM(A1,B1)，将A1、B1的数据移动到A2、B2，则C1？C1=SUM(A2,B2)C1的值不变练习：第2章.xls移动公式、数据!,2.4地址引用,22,插入删除单元格对公式的影响插入删除引用区域的一部分“引用”自动调节（例如工资表内插入一个人的记录）插入删除公式引用的整个区域：REF!例如A5SUM(A1:A4)如果在第1行第4行之间插入/删除行，则A5？例如F1E1/4，如果删除E1单元格，则F1=？练习：第2章.xls插入删除!,2.4地址引用,23,一个区域输入一组相同的数：操作：选定区域，输入一个数，按Ctrl+Enter键数组:用于对一组数进行计算，得到一组结果。输入/定义数组操作：（1）选择一个存放计算结果的单元格区域（2）输入公式（可能引用单元格区域）后，不要按回车健（3）按Ctrl+Shift+Enter键练习：第2章.xls数组!,2.5数组与数组公式,2.5数组与数组公式,数组公式与一般公式比较，数组特点：计算的对象是一个数或一组数，结果是