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

《Excel公式、常用函数及地址引用.ppt》由会员分享，可在线阅读，更多相关《Excel公式、常用函数及地址引用.ppt（27页珍藏版）》请在装配图网上搜索。

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

2、 = 表达式包含： 常量 (见第 1章，数据类型 ) 运算符 地址 引用 函数 例 =100+C4*AVERAGE(B2:B10) 6 2.2 表达式与公式 运算符 算术运算符 +、 -、 *、 /、 (乘方 )、 % 优先级别 :由高到低 ( ) 函数 *,/ +,- 公式转换为数值操作： 选定公式所在区域 复制 选择性粘贴 数值 算术表达式 例 23 等价 23,输入： =23 7 关系运算符 : =、 、 =、 、 =、 (不等 ) 文本连接运算符 练习： 第 2章 .xls公式 ! 练习： 第 2章 .xls字符串连接 ! 关系表达式 例 2=5 （ FALSE） 输入： = 2=5

3、“ 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, )

4、 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） 统计非空单元格的个数 COU

5、NTA（ 参数 1， 参数 2， ） （ 3） 统计空单元格的个数 COUNTBLANK（ 参数 1， 参数 2， ） 练习： 第 2章 .xls简单函数 ！ 如果复制公式后 ， 复制后公式不同 ， 但是显示同样的结 果 ， 是因为系统为 “ 手动计算 ” ， 改为 “ 自动重算 ” 即 可 。 2007： OFFICE按钮 ， 选项 ， 公式 ， 选中 “ 自动重算 ” 2003： 工具 ， 选项 ， 重新计算 ， 选中 “ 自动重算 ” 默认情况下，单元格输入公式，显示公式的值 。如果 改变显示 为公式，操作：（反之亦然 ） 步骤 1： 2007： office按钮，选项，高级，此工作表的

6、选项中 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章 .xl

7、s简单函数 ！ 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(条件区域，条件，求和区域 ) 功能 :求与 “ 条件区域 ” 中满

8、足 “ 条件 ” 的单元格相对应的 “ 求和区域 ” 单元格数据的累加和。 如果省略 “ 求和区域 ” ，对 “ 条件区域 ” 求和。 10.条件求平均值 (2007版有该函数 ) AVERAGEIF(条件区域，条件，求和区域 ) 功能 :求与 “ 条件区域 ” 中满足 “ 条件 ” 的单元格相对应的 “ 求和区域 ” 单元 格数据的 平均值 如果省略 “ 求和区域 ” ，对 “ 条件区域 ” 求平均值。 2.3 常用函数及其应用 15 应用举例 例 5 例 6 16 SUMIF 函数举例 练习： 第 2章 .xlssumif,countif! 17 2.4 地址引用 相对地址、绝对地址和混合

9、地址 “ A1”访问方式 相对地址 ： B1（ 复制时，相对位置不变） 绝对地址 ： $B$1（ 复制时，始终为固定地址） 混合地址 ： B$1, $B1（上述相对 /绝对混合 ） 技巧：按 F4键 自动转换： B1 $B$1 B$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 引用当前行 ，

10、相对当前列的上 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 等价 R

11、4C4=RC-2*RC-1 B8=SUM(B4:B7)等价 R8C2=SUM(R-4C:R-1C) E4=B4*$B$1 等价 R4C5=RC-3*R1C2 2.4 地址引用 20 复制移动公式 复制公式 复制后，公式中引用的 相 对地址变，但是相对位 置不变； 复制后，公式中引用的 绝对地址不会变。 移动公式 ： 移动后， 公式引用的地址均不变。 例如： C1 $A$3 D5， 将 C1 复制到 E3，则 E3？ 移动到 E3，则 E3？ 2.4 地址引用 E3 $A$3 F7 E3 $A$3 D5 21 移动公式中引用的单元格 观察： 如果 A1=1， B1=2， 例：当 C1 $A$1

12、B1， 将 A1、 B1的数据 移动 到 A2、 B2 ， 则 C1？ C1 $A$2 B2 C1的值不变 例：当 C1=SUM(A1,B1)， 将 A1、 B1的数据 移动 到 A2、 B2 ， 则 C1？ C1=SUM(A2,B2) C1的值不变 练习： 第 2章 .xls移动公式、数据 ! 2.4 地址引用 22 插入删除单元格对公式的影响 插入删除引用区域的一部分 “ 引用 ” 自动调节 （例如工资表内插入一个人的 记录） 插入删除公式引用的整个区域： REF! 例如 A5 SUM(A1:A4) 如果在第 1行第 4行之间插入 /删除行， 则 A5？ 例如 F1 E1/4，如果删除 E

13、1单元格，则 F1=？ 练习： 第 2章 .xls插入删除 ! 2.4 地址引用 23 一个区域输入一组相同的数： 操作：选定区域，输入一个数，按 Ctrl+Enter 键 数组 :用于对一组数进行计算，得到一组结果。 输入 /定义数组操 作： （ 1）选择一个存放计算结果的单元格区域 （ 2） 输入公式（可能引用 单元格区域 ）后，不要按回车健 （ 3）按 Ctrl+Shift+Enter 键 练习： 第 2章 .xls数组 ! 2.5 数组与数组公式 2.5 数组与数组公式 数组公式与一般公式比较，数组特点： 计算的对象是一个数或一组数，结果是一个 数或一组数。 不能修改其中任何一个结果（

14、起到保护结果 的作用） 修改 /删除数组公式：必须修改 /删除 整个数 组 数组的运算 自身的算数运算、二个数组的算数运算（矩 阵加 /减法） 25 2.6 应用举例 例 1 银行存款利息的计算 例 2 实发工资与工新税的计算 例 3 财务数据计算 例 4 收益预测表 26 2.7 错误值 错误值 错误值出现的原因 举例说明 #DIV/0! 除数为 0 =3/0 N/A 引用了无法使用的数值 例如 VLOOKUP第 1个参 数对应的单元格为空 #NAME? 不能识别的名字 =SUN(A1:A4) #NULL! 交集为空 =SUM(A1:A3 B1:B3) #NUM! 数据类型不正确 =SQRT(-4) #REF! 引用无效单元格 例如引用的单元格被删除 #VALUE! 不正确的参数或运算符 =1+”a” # 宽度不够 加宽列的宽度 作业 P41 习题 P159 第 10章 实验 2 预习第 3章