实训- 公式与函数应用

实训七公式与函数应用一、什么是函数与公式１、公式Excel的公式由运算符、数值、字符串、变量和函数组成。公式必须以等号“=”开头，即在Excel的单元格中，凡是以等号开头的输入数据都被认为是公式。在等号的后面可以跟数值、运算符、变量或函数，在公式中还可以使用括号。例如：=10+4*6/2+(2+1)*50就是一个公式，可以在任何单元格中输入此公式．Excel会把公式的计算结果显示在相应的单元格中．2、运算符在公式中可以使用运算符。Excel中的运算符运算符运算功能优先级()括号1-负号2%百分号3^乘方4*与/乘、除法5+与-加、减法6&文本连接7=、<、>、<=、>=、<>等于、小于、大于、小于等于、大于等于、不等于8说明：括号的运算级别最高，在Excel的公式中只能使用小括号，无中括号和大括号。小括号可以嵌套使用，当有多重小括号时，最内层的表达式优先运算。同等级别的运算符从左到右依次进行。“&”为字符连接运算，其作用是把前后的两个字符串连接为一串。例如，"ABC"&"DEF"的结果为“ABCDEF”，"财务"&"经济"的结果为“财务经济”。1、引用的概念在公式中用到了其他单元格在表格中的位置。引用的作用在于标识工作表中的单元格或单元格区域，并指明公式中所使用的数据的单元格位置。引用不同工作簿中的单元格称为链接。2、引用的类型A1引用RC引用二、引用3、相对引用相对引用也称为相对地址引用，是指在一个公式中直接用单元格的列标与行号来取用某个单元格中的内容。相对引用的例子某超市3月份的进货单如下图所示，计算“金额”。在G3计算公式=C3*D3*F3然后向下复制该公式！4、绝对引用绝对引用总是在指定位置引用单元格。如果公式所在单元格的位置改变，绝对引用保持不变。

绝对引用的形式是在引用单元格的列号与行号前面加“$”符号。比如，$A$1就是对A1单元格的绝对引用。绝对引用的案例假设LG空调在重庆各商场的销售价格相同，重庆各商场的销售数据如下图所示。计算各商场的销售总额。在E8输入公式：=$c$5*C8然后向下复制此公式！5、混合引用混合引用具有绝对列和相对行，或是绝对行和相对列。比如，$A1、$B1

案例引用6、内部引用与外部引用(提示，可在＝后直接点击引用的内容)1）引用相同工作表中的单元格，例：

=G3+G5+G10*102）引用同一工作簿的不同工作表中的单元格，例 =Sheet1!G3+Sheet1!G5+Sheet1!E273）引用不同工作簿中的单元格，例：

=[Book1]Sheet1!$IL$4+[Book1]Sheet2!$E$74）同一公式中存在几中不同的引用，例： =[Book1]Sheet1!$A$4+Sheet1!G7+F9P86页利用公式混合引用在B2单元格输入公式＝$a2*B$1后拖拉引用7、公式的使用中的错误信息错误值错误原因########单元格所含的数字、日期或时间比单元格宽，或者单元格的日期、时间公式产生了一个负值，就会产生#####错误。#VALUE!1.在需要数字或逻辑值时输入了文本，Excel不能将文本转换为正确的数据类型。2.输入或编辑数组公式时，按了Enter键。3.把单元格引用、公式或函数作为数组常量输入。#DIV/O!1.输入的公式中包含明显的除数为零（0）#NAME?1.在公式中输入文本时没有使用双引号。但这些文本又不是名字。2.函数名的拼写错误。3.删除了公式中使用的名称。4.名字拼写有错#N/A1.在调用函数时参数。2.在数组的参数的行数或列数与包含数组公式的区域的行数或列数不一致。3.在没有排序的数据表中使用了VLOOKUP、HLOOKUP或MATCH工作表函数查找数值#REF!删除了公式中所引用的单元或单元格区域#NUM!1.由公式产生的数字太大或太小，Excel不能表示。2.在需要数字参数的函数中使用了非数字参数#NULL!在公式的两个区域中加入了空格从而求交叉区域，但实际上这两个区域无重迭区域条件格式的意义对于不同的数据，可按不同的条件设置其显示格式。比如在一个学生成绩表中，把所有不及格的成绩显示为红色，就能够很容易找到有不及格科目的学生。下面是条件格式运用的一个例子：当单元格的数值大于200时，用黑色显示；当小于100时用红色显示；100~200之间的数据用蓝色显示。将该单元格的格式设置为如下的自定义数字格式：[黑色][>=200]#,###;[红色][<=100]#,###;[蓝色]#,###

三、条件格式2、条件设置案例某公司的工资表，为了查看电话费的补贴情况，用条件格式将电话费在2000元以上的单元格设置成了红字和浅绿色的背景；将1000元~2000元之间的单元格设置成了黑字和浅蓝色的背景；将1000元以下的单元格设置成了深蓝色的字体和浅青色的背景。3、案例结果：E列数据1.对于身份证长度通过条件格式来提醒（在条件格式，公式中设定＝OR(LEN(A2)=15,LEN(A2)=18）,设为其他颜色，这样当输入的长充不是15位或18位时，显示的格式颜色就不同（注：07版的条件格式有所不同）2.均值以上的单元格数据红色显示（直接设定格式条件，根据条件显示颜色）3.最大值红色、最小值蓝色（选定区域后，设定条件＝C35=max($C$35:$F$47)）4.间隔行条纹（选定后公式输入＝mod(row(),2）=0,设定条纹格式07版可以对多规则管理四、数组公式的应用

1、数组公式的含义数组公式能够对两组或两组以上的数据（两个或两个以上的单元格区域）同时进行计算。在数组公式中使用的数据称为数组参数，数组参数可以是一个数据区域，也可以是数组常量。2、数组公式的建立方法（1）选中需要保存数组公式结果的单元格或单元格区域。（2）输入公式的内容。（3）按Ctrl+Shift+Enter键。数组公式及其应用用数组公式计算两个数据区域的乘积【例4-1】某超市3月份进购的产品图4-5所示，已知各种产品的数量、单价和折扣，要计算各种产品的应付金额。数组公式及其应用数组扩展在公式中用数组作为参数时，所有的数组必须是同维的。如果数组参数或数组区域的维数不匹配，Excel会自动扩展该参数。比如，某商场29寸电视机售价为3128元，有一个10天的销售记录，如下图的B、C两列所示。现要计算每天的销售额。数组公式及其应用1、选中F8:F14；2、输入公式：=C8:C14*C53、按Ctrl+Shift+Enter操作题用数组计算总股本可以用sumproduct函数来实现数组计算功能数组公式及其应用二维数组涉及许多行(或列)数据处理的数组公式，就是所谓的二维数组。合理地运用二维数组的运算功能，会提高数据处理的能力，有时在不同工作表之间进行数据汇总时会特别有效。数组公式及其应用案例某食品商店将每个月的食品销售资料统计在一个工作表中，1～6月的销售数据分别记录在Book1工作簿的Sheet1～Sheet6中，现在要统计每种商品前6个月的销售总量和销售金额。见下图：（注意是同一区域选择）四、名字应用1、名字概述

可以给一个单元格或单元格区域取一个名字，这个名字可以出现在公式中，用来指代引用的单元格或单元格区域，这会使公式的意义更加明确。名字其实是一个标识符，由字母或下划线开头的一个或多个字符组成。在Excel中，还可以用汉字字符作为名字。名字不区分字母的大小写，比如ABC、abc、Abc都是同一个名字。可以为同一单元格或单元格区域定义多个不同的名字。名字一旦定义就可以在同一工作薄的不同工作表之间共用。名字及其应用2、名字的意义在Excel中，同一个工作簿中的名字是共享的，在一个工作表中定义的名字，可以被另一个工作表的公式引用，这使公式具有更强大的功能，它能够利用单元格的名字在不同的工作表中查找到正确的单元格或单元格区域。名字给单元格的引用带来了许多方便，因为它使人们不必记住单元格在工作表中的引用位置，用名字就能找到它。名字及其应用名字应用案例在下图中，B、C列是某商场所有产品的当日单价，E、F列是产品当天的销售记录。销售的产品多达几百种，而每天则可能只卖掉其中的几十种产品，现在要计算当日产品的销售金额。若不用名字，单价要从C列的几百个数据中去查找！

名字及其应用3、名字的定义在Excel中，可以为一个独立的单元格、连续的单元格区域或许多不连续的单元格构成的单元格组合定义一个名字（或多个名字）定义名字有多种方法，如定义、粘贴、指定或标志等名字及其应用4、定义单元格或单元格区域的名字D3单元格的名字定义方法如下：名字及其应用（1）选择“插入”|“名称”|“定义”菜单命令.（2）在弹出的“定义名字”对话框的“引用位置”输入要定义名字的单元格引用位置。（3）在“在当前工作簿中的名称”中输入名字名字及其应用5、将工作表的首行（或首列）定义为名字“指定”的方式一次可定义多个名字：可以将表格的首行或首列指定为相应的列或行的名字。方法（1）选中要指定名字的单元格区域。（2）选择“插入”|“名称”|“指定”菜单项。（3）在“指定名称”对话框中，选中“首行”、“最左列”的复选框。这样就把该表的首行、首列指定成了相应的列、行的名字名字及其应用指定首行、首列为名字的案例（注意选择区域需要包括行与列的范围，不可间隔）名字及其应用6、名字的应用1）通过名字在不同的工作表之间传递数据

通过名字查找蔬菜单价指定A2:B12最左边为名字操作题将“薪资表中的调底薪记录”区域定义名称，并将其应用于实际计算平均值名字及其应用2）名字与Indirect函数结合应用Indirect函数与名字相结合，可使数据查找更方便。Indirect函数的用法是：Indirect(x)其中的X可以是单元格引用或名字。若X为单元格引用，它必须用“”引起来，如：=INDIRECT(“A1”)，将返回A1单元格中的内容（注意加””是对单元格里内容进行引用，不加引号是对其单元格内名字）若X为单元格名字，它将返回该名字所对应的单元格的内容。名字及其应用某蔬菜商在一个工作表中保存蔬菜的单价，在另一工作表中保存销售记录，现要查找销售记录表中各蔬菜的单价。指定A2:B11区域的“最左列”为名字在D2中输入公式：=INDIRECT(B2)向下复制该公式！名字及其应用3．应用行列交叉点查找数据所谓行列交叉点指的是由[行的名字列的名字]确定的单元格。把一个工作表的首行、首列指定为名字之后，可以使用名字引用行、列交叉点对应单元格中的数据。案例某电信公司的电话用户档案，其中有成千上万个电话用户的电话号码。如果知道某个用户的名字，那么要在这样的表中查找该用户的电话号码、电话类型，或申请日期等数据就显得很困难，因为表中的资料太多了。通过行、列交叉点的名字引用能很轻松地找到所需的数据。名字及其应用1指定A2:E17的“首行，最左列”为名字。2、在任空白单元格中输入“客户名字库第二行任一单元格标题”，就能找到该客户的对应数据。（注意，其首行首列定义后是可用于交叉查找，但不能用于名字引用）Trimmean（Array,percent）功能：将数据区域头尾除去一定百分比的数据点，然后再求平均值。Array为需要筛选并求平均值的数组或数据区域；Percent为计