《Excel数据处理与可视化》教案9-公式综述

《Excel数据处理与可视化》课程教案授课题目公式综述授课类型理实一体教学目标必须知道的公式基础知识公式中常出现的错误代码及修正方法使用“追踪错误”对公式进行检查将公式保护起来数组公式重点1、数组公式2、公式基础知识难点1、数组公式教学过程一必须知道的公式基础知识1.运算符Excel运算符有数学运算符、逻辑运算符、引用运算符、文本运算符。2.运算符优先级公式中同时用到了多个运算符，Excel将按下面的顺序进行运算。（1）如果公式中包含了相同优先级的运算符，Excel将从左到右进行计算。（2）如果公式中有不同优先级的运算符，则运算符的计算顺序从高到低依次为：引用运算符（：）、负号（-）、百分比（%）、乘方（^）、乘和除（*和/）、加和减（+和-）、连接符（&）、比较运算符。如果要修改计算顺序，则应把公式中要先计算的部分括在圆括号内。3.相对引用与绝对引用（1）概念。●A1：相对引用。●$A$1：绝对引用。●$A1：列绝对引用，行相对引用。●A$1：行绝对引用，列相对引用。（2）用途。●行前添加$：在复制（填充）公式时，行数不发生变化。●列前添加$：在复制（填充）公式时，列数不发生变化。（3）F4键（用于引用方式之间切换的快捷键）。●按1次：绝对引用。●按2次：对行绝对引用、对列相对引用。●按3次：对行相对引用、对列绝对引用。●按4次：相对引用。4.公式排查错误在公式返回值出现错误时，可以单击“公式”→“公式求值”来查找错误5.公式填充将公式由一个单元格拖到另一个单元格称为公式填充。6.公式批量填充选取要输入公式的单元格区域，在编辑栏中输入公式7.只保留公式单元格的数值为了防止数据随源数据改变，或者复制数据后出现错误，可以通过“选择性粘贴”选项只保留公式单元格的数值8.公式显示如果想要看到详细的公式，可以单击“公式”→“显示公式”9.显示另一个单元格公式如果要在别的单元格显示某单元格的公式，可使用FORMULATEXT函数，如图41310.隐藏公式如果不想让别人看到你的公式，可以将公式隐藏。11.保护公式选中非公式单元格区域，取消单元格锁定，不必操作公式单元格区域，再设置保护工作表，这样操作以后，公式单元格就不会被选中了。12.显示公式部分计算结果选取公式中要显示的部分表达式，按F9键，即可显示公式部分计算结果。13.中断公式编辑有时在公式编辑状态下，不小心单击到了其他单元格，出现取消不掉公式编辑的情况。此时，应该选择公式编辑栏左侧的红色“×”，强行停止公式编辑。二、公式中常出现的错误代码及修正方法错误值含义解决办法＃＃＃＃1.单元格数据太长2.公式单元格所产生的结果太大，在单元格中无法完整显示3.日期和时间格式的单元格相减，出现了负值1.增加列宽2.如果是由日期或时间相减产生了负值引起的，可以改变单元格的格式为文本，但结果只能是负时间量＃DIV/0!1.除数为02.公式中除数使用了空单元格或包含零值单元格的引用1.修改单元格引用2.在做除数的单元格中输入不为零的值＃VALUE!1.数值运算时引用文本型的数据2.使用了不正确的参数或运算符3.当执行自动更正公式功能时，不能更正公式1.更正相关的数据类型或参数类型2.提供正确的参数＃REF!删除了被公式引用的单元格范围恢复被引用的单元格范围，或者重新设定引用范围＃N/A查找或引用函数中找不到匹配的值检查被查找的值，使其存在于查找的数据区域＃NAME?在公式中使用了Excel所不能识别的文本，例如：1.输错了函数名称2.使用了已删除的单元格区域或名称3.引用的文本没有加英文双引号1.改正函数名称2.修改引用的单元格区域或名称3.引用的文本加英文双引号＃NUM!1.函数参数无效2.公式的结果太大或太小，无法在工作表中表示1.确认函数中使用的参数类型正确2.修改公式，找到错误原因并更正＃NULL！1.使用了不正确的区域运算符2.引用的单元格区域的交集为空1.改正区域运算符2.更改引用的单元格区域，使之相交三、使用“追踪错误”对公式进行检查（1）将光标放在公式单元格中，单击“公式”→“错误检查”→“追踪错误”之后，可以发现，Excel自动用箭头标出了错误来源。（2）将光标放在箭头的“源头”，左侧会出现错误提示按钮（），单击错误提示下拉箭头，可看到错误原因与改正方法。四、将公式保护起来（1）所有单元格去除“锁定”。（2）保护和隐藏公式单元格。（3）设置保护工作表。五、数组公式1.数组概念什么是数组？数组就是具有某种联系的多个单元格数据的组合2.数组的维度“维度”是数组的一个重要概念。数组有一维数组、二维数组、三维数组、四维数组……在公式里，应用更多的是一维数组和二维数组。一维数组可以简单地被看成一行或一列单元格数据集合，如图4-30中B2:B8单元格区域的一组数据、E2:K2单元格区域的一组数据都是一维数组。二维数组可以看成一个多行多列的单元格数据集合，也可以看成多个一维数组的组合，但在公式中，数组的描述如下。一维数组：单独一行的数组元素之间用半角逗号隔开；单独一列的数组元素之间用半角分号隔开。二维数组：同行的数组元素间用半角逗号分隔，不同行的数组元素间用半角分号分隔。如果要看一个数组中的数组元素，可以将该数组元素选中，然后按F9键，即可在地址栏中显示该数组元素3.数组公式数组公式与普通公式有以下几点不同。（1）执行计算方式不同：普通公式按Enter键结束，数组公式按Ctrl+Shift+Enter组合键执行计算。（2）公式显示方式不同：整个数组公式外围有一对{}。（3）结果显示位置不同：普通公式的结果显示在一个单元格里；数组公式的结果可以显示在一个单元格里，也可以显示在多个单元格里。（4）公式修改方式不同：选中普通公式所在单元格即可修改普通公式；数组公式，尤其是结果显示在多个单元格里的数组公式，必须选中其结果单元格区域进行修改，并按Ctrl+Shift+Enter组合键执行计算才能被修改，否则会出现“无法更改部分数组。”的提示框。六、数组公式——应用初步1.行列数相同的数组运算1）行数相同的数组相乘运算2）列数相同的数组相乘运算切记：●输入公式之前要选中所有填结果的单元格区域。●只输入执行计算的公式部分。2.数组与单一数据运算3.单列数组与单行数组运算单列数组与单行数组的计算规律：●计算结果返回一个多行多列的二维数组。●返回二维数组的行数同单列数组的行数相同、列数同单行数组的列数相同。●二维数组各单元格结果分别是单行数组与单列数组每个数组元素互相运算得出的。单列数组与单行数组的计算规律如图444所示。4.单行或单列数组与多行多列数组运算单行或单列数组与多行多列数组运算规律：●结果返回一个多行多列的二维数组。●返回数组的行、列数与多行多列数组的行、列数相同。●单列数组与多行多列数组运算时，返回单列数组与二维数组对应行所有列数据的运算结果。●单行数组与多行多列数组运算时，返回单行数组与二维数组对应列所有行数据的运算结果。5.行、列数相等的二维数组运算七、数组公