5.2公式和函数（课件）《信息技术》（电子工业出版社）

第五章 信息技术导论（第3版） 电子表格处理 Excel2010 1 03 公式和函数 2 公式的使用 1.公式中的运算符 公式中的运算符包括算术运算符、比较运算符、文本运算符和引用运算符，如表5.1所示。 比较运算符用于比较两个数字或字符串，产生逻辑值“TURE”或“FALSE”。比较结果为“真”时，显示“TURE”，否则显示“FALSE”。 比较运算符在对西文字符串进行比较时釆 用内部ASCII码进行比较，对中文字符进 行比较时采用汉字内码进行比较，对日期 时间型数据进行比较时釆用先后顺序（后 者为大），如2013年10月1日“大于"2012 年12月30日。 3 公式的使用 2.运算优先级 Excel对运算符的优先级做了严格的规定，由高到低各运算符的优先级为：引用运算符有冒号、逗号、空格；算术运算符有括号、负号、百分比、乘幕、乘除同级、加减同级、文本运算符、比较运算符同级。同级运算时，优先级按照从左到右的顺序计算。表5.1中的“优先级”数字越小，其运算级别越高。多个运算符同时出现在公式中时，按优先级顺序执行运算。 4 公式的使用 3.输入和编辑公式 选择要输入公式的单元格或单击公式编辑栏，输入“=”，再输入公式内容，如A2*A2+B2,然后单击编辑栏的“"”按钮或按Enter键。也可以直接在单元格中输入公式。如果没有在公式的开头输入“=”，Excel将自动按照普通字符显示在单元格中。 在使用公式时，如果输入的数值类型与所需的类型不同，Excel有时可以对这个数值进行转换。如公式"=”1"+吃"”的结果是3,公式“=1+”$2.00””的结果也是3,公式"=SQRT（"9”）”的结果也是3。这是因为在使用“+”时，Excel会认为公式中的运算项为数值型数据，自动将字符型数据"1\"2\"$2.00"和"9"转换为数值型数据。其他转换读者一试便知。 5 公式的使用 4.公式的显示与隐藏 对含有公式的单元格Excel默认的方式只显示计算结果。如果要在单元格中显示公式，单击“公式”选项卡的“显示公式”按钮，则工作表中所有含公式的单元格就会显示出公式，而不再显示计算结果。再单击一次“显示公式”按钮，则返回显示数值。也可以在“Excel选项”对话框的“高级”选项中来设置，只需勾选“在单元格中显示公式而非计算结果”复选框。另一个简单的操作方法就是使用组合键Ctrl+'（重音键'与〜在同一键上，在数字键1的左边），可以显示工作表中的所有公式。这样做的好处在于可以方便地检查单元格引用，以及公式输入是否正确。再次按组合键Ctrl+',将恢复到原来的显示状态，读者不妨通过具体操作加以了解。 6 公式的使用 5.公式的复制与移动 在Excel中会经常使用公式，如果所有公式都逐一输入是件很麻烦的工作，而且容易出错。Excel提供了公式的复制和移动功能，可以方便地实现大量公式的快速输入。公式的复制和移动与前面数据的复制和移动非常相似。 （1）复制公式 单击“开始”选项卡的“剪贴板”组的“复制”按钮（快捷键Ctrl+C）,选中要粘贴公式的单元格（区域），单击“粘贴”下拉按钮，选择“选择性粘贴”选项，在弹出的对话框中选择“公式”单选按钮来复制公式。也可单击右键，在弹出的快捷菜单中选择“选择性粘贴”。 (2)移动公式 单击“开始”选项卡的“剪贴板”组的“剪切”按钮(快捷键Ctrl+X),选中要粘贴公式的单元格(区域)，再单击“粘贴”按钮(快捷键Ctrl+V),移动公式。 7 单元格的引用 1.相对引用 所谓“相对引用”，是指在公式复制时，该地址相对于目标单元格在不断发生变化，这种类型的地址由列号和行号表示。 2.绝对引用 所谓“绝对引用”，是指在公式复制时，该地址不随目标单元格的变化而变化。 3.混合引用 所谓“混合引用”，是指在引用单元格地址时，一部分为相对引用地址，另一部分为绝对引用地址，如$A1或A$l。 8 单元格的引用 4.外部引用（链接） 同一工作表中的单元格之间的引用被称为“内部引用”。在Excel中还可以引用同一工作簿中不同工作表中的单元格，也可以引用不同工作簿中工作表的单元格，这种引用称为“外部引用”，也称为“链接”。 5.单元格（区域）命名 在引用一个单元格区域时，常用它的左上角和右下角的单元格地址来命名，如“B2:D2”。这种命名方法虽然简单，却无法体现该区域的具体含义，不易读懂。 9 公式的审核 1.公式中常见出错信息及处理 在使用公式进行计算时，经常会遇到单元格中出现类似"#NAME”"#VALUE”等信息，这些都是使用公式时出现了错误而返回的错误信息值。 表5.2列出了部分常见的错误信息、产生的原因及处理办法。使用过程中如果遇到错误信息时，可以查阅本表以查找出错原因和解决办法。 2.公式的审核 Excel提供了公式审核功能，其作用是跟踪选定区域内公式的引用或从属单元格，并能检查和跟踪错误。利用“公式”选项卡的“公式审核”组的按钮可进行公式审核。如要进行错误检查，单击“公式”选项卡的“公式审核”组中的“错误检查”按钮，弹出的对话框中列出了单元格的出错原因及解决问题的命令按钮，单击这些按钮，可以对错误进行分析及重新编辑以解决错误。单击“下一个”按钮，继续查找下一个错误。其他操作请读者自行练习。 10 公式的审核 11 函数的使用 函数是Excel内部自带的预定义公式。灵活运用函数不仅可以省去自己编写公式的麻烦，还可以解决许多仅仅通过自己编写公式尚无法实现的计算，在遵循函数语法的前提下，将大大减少公式编写错误的发生。 Excel提供的函数涵盖范围较广泛，包括财务、日期与时间、数学与三角、统计、查找与引用、数据库、文本、逻辑、信息、工程和多维数据集函数。每种类型又包括若干个函数，这里不解释每个函数的功能和作用，用户在使用具体函数时，Excel都会给出对话框和相应函数用法文字解释。 函数的语法形式为“函数名称(参数1,参数2,…)”。其中，函数的参数可以是数字常量、文本、逻辑值、数组、单元格引用、常量公式、区域、区域名称或其他函数等。如果函数是以公式的形式出现，应当在函数名称前面输入“=”。 12 函数的使用 1.输入函数 (1)使用编辑栏输入函数 与公式的输入方式一样，在编辑框或单元格中可直接输入函数和参数。 (2)使用“插入函数”方法输入函数 选择要输入函数的单元格，单击“公式”选项卡的“函数库”组的函数类型，或单击“插入函数”按钮，也可以单击编辑栏的“插入函数”按钮K，打开“插入函数”对话框，在'‘或选择类别”列表中选择函数类型，在“选择函数”列表中选择函数名称，单击“确定”按钮，又会出现“输入函数参数”对话框，输入参数并单击“确定”按钮即可。 13 函数的使用 2.自动求和与自动计算 Excel提供了一种自动求和功能，可以方便地完成SUM函数的功能。 选择要放置自动求和结果的单元格，习惯上将对行求和的结果放在行的右边，对列求和的结果放在列的下边。单击“公式”选项卡的“函数库”组或“开始”选项卡的“编辑”组中的“自动求和”按钮，Excel会按照默认状态选择一行或一列作为求和区域，如需调整，可用鼠标拖动来选择求和区域。单击编辑栏的“"”按钮或按Enter键确认。 3.函数嵌套 函数嵌套就是将某函数的运行结果作为另一函数的参数使用。例如，公式“=IF(AVERAGE(B2:E5)>200,SUM(F2:F5),0)”的含义是，如果单元格区域B2:E5的数值平均值大于200,那么公式所在单元格中就存放单元格区域F2:F5的总和，反之则存放数值0。 14 常见函数的使用 1.SUM函数 功能：SUM函数用于计算单个或多个参数之和。 语法：SUM(number1,number2,…)。 numberl,number2,…为1〜30个需要求和的参数。参数可以是数字、文本、逻辑值，也可以是单元格引用等。如果参数是单元格引用，那么引用中的空白单元格、逻辑值、文本值和错误值将被忽略，即取值为0。 15 常见函数的使用 2.SUMIF函数 功能：对符合指定条件的单元格求和。 语法:SUMIF(range,criteria,sum_range)。 range参数用于条件判断的单元格区域。criteria参数用于确定哪些单元格符合求和的条件，其形式可以是数字、表达式或文本。sumjrange为需要求和的实际单元格区域，只有range中的相应单元格满足criteria中的条件时,才对sum_range中相应的单元格求和。如果省略sum_range，则对range中满足条件的单元格求和。 16 常见函数的使用 3.AVERAGE函数 功能：对所有参数计算算术平均值。 语法：AVERAGE(numberl,number2,…)。 numberl,number2,…为1〜30个需要计算平均值的参数。参数应为数字，或者包含数字的名称、数组或单元格引用等。如果数值或单元格引用参数中有文本、逻辑值或空白单元格，则忽略不计，但单元格中包含的数字“0”将计算在内。 17 常见函数的使用 4.COUNT函数 功能：计算参数表中的数字参数和包含数字的单元格个数。 语法：COUNT(valuel,value2,…)。 valuel,value2,…为1〜30个可以包含或引用各种不同类型数据的参数，但只对数字型数据进行计算。 18 常见函数的使用 5.INT函数 功能：对实数向下取整数。 语法：INT(number)o number是需要取整的实数。例如，INT(3.9)=3,INT(-6.8)=-7。 6.MAX函数 功能：求数据集中的最大值。 语法：MAX(numberl,number2,…)。 number!,number2,…为1〜30个需要求最大值的参数。可以将参数指定为数字、空白单 元格、单元格区域、逻辑值或数字的文本表达式等。如果参数为错误值或不能转换成数字的文本，将产生错误。如果参数不包含数字，函数MAX返回0。 19 感谢观看 信息技术导论（第3版） 20 $$