excel中的公式计算

1、.公式的灵活性是通过单元格的引用来实现的。引用的作用在于标识工作表上的单元格或单元格区域,并指明公式中所使用的数据的位置。如上例,单元格Bl中的公式"=A1*A2/2"中出现了其他单元格的引用地址Al和A2,这就是在该公式中引用了其他单元格或区域中的数据。这种方式使得在Excel的公式中不仅可以使用常数、运算符,还可以使用其他单元格或区域中的数据。并且当引用的源单元格中的数据发生变化时,公式中的引用数据也随之变化。被引用的单元格中的数据中不能含有"$"、"￥"或"，"可以引用同一工作表中的其他单元格或区域,还可以引

2、用其他工作表甚至其他工作簿的工作表中的单元格或区域。根据单元格引用被复制到其他单元格时是否会发生改变,单元格的引用分为相对引用、绝对引用和混合引用。(1)相对引用相对引用是指当把一个含有单元格引用的公式复制或填充到一个新的位置时,公式中的单元格引用会随着目标单元格位置的改变而相对改变。Excel中默认的单元格引用为相对引用。单元格相对引用:由单元格的列标行号表示。如A1,引用了第A列与第1行交叉处的单元格。单元格区域相对引用:由单元格区域的左上角单元格相对引用和右下角单元格相对引用组成,中间用冒号分隔。如A1:D5,引用了以单元格Al为左上角、以单元格D5为右下角的单元格区域。示例:在工作表&

3、quot;学生成绩单"的D3单元格中,利用单元格相对引用计算第一个学生的总成绩。除了在单元格D3中直接输入公式=B3+C3"引用B3和C3以外,还可以用鼠标选取要引用的单元格。操作步骤如下:1)选中单元格D3,输入"="号。2)单击单元格肘,B3被虚线框包围。单元格D3中显示"=B3"。3)输入"+"号,单元格D3中显示"=B奸"。4)单击单元格口,C3被虚线框包围。单元格D3中显示"二B3十。"。5)按回车键或单击编辑栏中的输入按钮""这种用鼠标点取要引

4、用的单元格来获得其地址并粘贴到公式中的方法,使公式的输入和单元格的引用更方便快捷。用这种方沾不仅可以在同一工作表中粘贴其他单元格或区域的引用地址,还可以粘贴其他工作表甚至其他工作簿的工作表中的单元格或区域的引用地址。在上面的例子中,如果要在D4单元格中计算第二个学生的总成绩,只需把D3单元格中的公式复制到D4单元格中即可,D4单元格中的公式为:"二B4+C4",所引用的单元格的地址发生了变化,但所引用的单元格相对于存放公式的目标单元格的相对位置没有改变。D3单元格中引用的B叉口,相对于D3分别位于同一行左边2列、D4单元格中引用的B4、C4,相对于D4仍然是分别位于同一行左

5、边2列、1列。公式从D3复制剧D4,列不变,行数增加1,所以公式中引用的单元格也列不变,行数增加1。如果要计算每个学生的总成绩,可用鼠标对准D3单元格右下角的填充柄,向下拖动至D10单元格,D3单元格中的公式即被复制到了D4:D10单元格区域中。也可用鼠标右键拖动填充柄,释放鼠标后在快捷菜单中选取"复制单元格"命令。 (2)绝对引用绝对引用是指当把一个含有单元格引用的公式复制或填充到一个新的位置时,公式中的单元格引用不会改变。无论将公式复制到哪里,都将引用同一个单元格。单元格绝对引用的方法是在列标和行号前分别加上"$"符号。如$A$1,表示单元格Al的绝

6、对引用;$A$1:$D$5,表示单元格区域Al:D5的绝对引用。例如:把图3-3中D3的公式改为"=$B$3十$C$3",然后将公式复制到单元格D4中,D4中的值与D3相同,引用地址没有改变。(3)混合引用混合引用是指在一个单元格地址中,既有绝对地址引用又有相对地址引用。例如,单元格地址、A1"表明保持列不发生变化,而行随着新的复制位置发生变化;单元格地址"A$1"表明保持行不发生变化,而列随着新的复制位置发生变化。三种引用输入时可以互相转换。在公式中选定引用单元格的部分,反复按F4键可进行引用间的转换,转换规律:Al$A$1A$1$AlAl。

7、3.1.3引用其他王作表数据公式中也可以引用其他工作表中的单元格或单元格区域,甚至是其他工作簿的工作表中的单元格或单元格区域。(l)引用同一工作簿的其他工作表中的单元格如果用在公式中键入引用地址的方法,对同一工作簿中其他工作表的单元格或单元格区域进行引用,引用格式是:工作表名!单元格(或区域)的引用地址必须用感叹号"!"将工作表名称和单元格引用分开。如:要引用工作表Sheet2的B3单元格,应输入公式:"=Sheet2!BY。如果引用的工作表名称中含有空格,必须用单引号将工作表名称括住,如:"=MySheet!B3"。如果用鼠标引用工作簿中其他

8、工作表的单元格或单元格区域,可在公式中要输入引用地址的地方,单击需要引用的单元格所在的工作表标签,选中需要引用的单元格或单元格区域,则该引用将显示在公式中。(2)引用其他工作簿中的单元格在要引用的工作簿已经打开的情况下,如果用在公式中键入引用地址的方法对其单元格进行引用,引用格式是:工作簿名称工作表名!单元格(或区域)的引用地址用中括号将工作簿名称括住。如:要引用工作簿Book2中的工作表Sheet2的B3单元格,应输入公式:"=Book2lsheet2!BY。如果工作簿名称中含有空格,也必须用单引号将工作簿名称连同工作表名称一起括住,例如,"=Book2Sheet2

9、9;!B3"。如果用鼠标引用其他工作簿中的单元格或单元格区域,可在公式中要输入引用地址的地方,首先选择需要引用的工作簿为当前工作簿,然后单击需要引用的单元格所在的工作表标签,再选中需要引用的单元格或单元格区域,贝。该引用将显示在公式中。如果公式中需要引用的工作簿事先没有打开,则必须在公式中的工作簿名称前加入该工作簿的路径,并在路径前和工作表名后加上单引号,即路径、文件名和工作表名要用单引号括起来,例如:"二D:Book2Sheet2'!B3"。3.1.4日期和时间的运算在很多情况下会遇到日期和时间的运算,Excel提供了有关日期和时间运算的若干函数。此处只

10、讨论用算术运算求两个日期或时间之间的差值的方法。下面以例解的形式说明如何进行日期和时间的运算。(1)日期运算在当前单元格Al中利用四则运算求出2005年1月1日到2006年1月1日期间的天数。操作步骤如下:l)单击单元格Al。2)在单元格Al中输入:="2006一l一1"一"2005-1-1",日期字符串必须用双引号括起来。3)按回车键或单击编辑栏中的输入按钮""确认。 (2)时间运算在当前单元格Al中求同一天上午5:09:00到11:09:00期间的时间差。操作步骤如下:1)单击单元格Al。2)在单元格Al中输入:="11

11、:09:00"一"5:09:00",时间字符串必须用双引号括起来。3)按回车键或单击编辑栏中的输入按钮"/"确认,计算结果为0.25天。3.2.4函数的嵌套当以函数作为参数的时候,称为函数的嵌套。在公式中最多可以包含七级嵌套函数。当函数B用作函数A的参数时,函数B称为二级函数。如果函数B中还有函数C作为参数,则函数C称为三级函数。例如:工作表的E2单元格中输入公式"=IF(AVERAGE(BLD2)>60,SUM(B2:D2),"不通过")",公式中使用了嵌套的AVERAGE函数和SUM函数。在此A

12、VERAGE函数和SUM函数为二级函数。该公式的含义是,用AVERAGE函数求出B2:D2单元格区域数据的平均值,并将它与数值60比较,比较后返回值为TRUE或FALSE。当返回值为TRUE时,求解IF函数的第二个参数,即用SUM函数求该区域数据的和并作为IF函数的结果显示在E2单元格,否则求解IF函数的第三个参数,在E2单元格显示"不通过"。使用"插入函数"对话框输入该公式的操作步骤如下:(1)选取要输入函数的单元格E2。(2)单击编辑栏的"插入函数"按,打开"插入函数"对话框。(3)在对话框的"选择类

13、别"下拉列表中选择函数类型"逻辑"。(4)在"选择函数"列表框中选择函数"IF"。(5)单击"确定"按钮,出现"函数参数"对话框。(6)单击"Logical-test"文本框,单击公式选项板右侧的下拉按钮打开"函数"列表(7)单击"AVERAGE"函数,出现该函数的"参数"输入对话框。(8)在对话框中输入参数B232,单击"确定"按钮,弹出公式出错"警告"信息框,单击

14、信息框中的"确定"按钮,回到公式编辑栏。(9)在编辑栏中继续输入">60",然后再次单击"插入函数"按钮,回到"IF函数参数"输入对话框。(lO)单击"Value-if-true"文本框,在公式选项板的"函数"列表中选取SUM函数。在"SUM函数参数"对话框中输入参数B232,单击"确定"按钮,回到公式编辑栏。(11)在编辑栏中继续输入IF函数的第三个参数"不通过'",回车确认;或再次单击"插

15、入函数"按钮,回到"IF函数参数"输入对话框,在"Value-iLfalse"文本框中输入"不通过'",单击"确定"按钮结束操作。3.3.3条件检测函数IF&      功能:IF函数具有判断的能力,通过对作为第一参数的条件进行判断,根据判断结果的真假执行不同的计算,返回不同的结果。IF函数属于逻辑函数。&      格式:IF(logical-test,value-if-true,

16、value-if-false)&      参数:三个参数,各参数含义如下:参数1:LogicalJtest,数值或表达式,计算结果为逻辑值,表示被判断的条件。参数2:Value-if-true,当检测到Logical-test的值为TRUE时函数的返回值,或者说参数1所代表的条件成立时,IF函数将返回参数2表达式的值。参数3:Value-if-false,当检测到Logical-test的值为FALSE时函数的返回值,或者说参数l所代表的条件不成立时,IF函数将返回参数3表达式的值。简单地说,参数1是判断的条件,条件成立,返回参数与条

17、件不成立,返回参数3。参数2、3也可以是公式或函数,但前面不要等号"="。当参数2、3是函数的时候,就是函数的嵌套,函数最多允许嵌套7层。参数2、3也可以是带西文双引号的文本,当该文本就是IF函数的返回结果时,则直接显示该文本。参数2可以省略。如果省略,应该返回参数2时,则返回0。参数3也可以省略。当参数3前面的逗号一起省略时,应该返回参数3,则返回TRUE;当参数3前面的逗号没有省略时,则返回0。操作示例:在图3-26所示的工作表中,将总分大于等于260的学生的总评显示为"优秀",否则什么都不显示。操作步骤:(1)单击要存放公式的单元格F2。(2)选取

18、"插入"菜单的"函数"命令,打开"插入函数"对话框。(3)在对话框中选择"逻辑"函数类型中的函数"IF"。(4)单击"确定"按钮,出现IF函数的"函数参数"对话框。(5)在"Logical-test"参数文本框中输入第一个参数:E2>=260。也可以单击参数文本框的"折叠"按钮以暂时折叠起对话框(或将对话框移开),显露出工作表。单击E2单元格,将其引用地址E2粘贴到文本框,接着键入">=260&

19、quot;后,再单击"折叠"按钮,还原对话框。(6)在"Value-if-true"参数文本框中输入第二个参数:"优秀"。(7)在"Value-iLfalse"参数文本框中输入第三个参数:111f。因为当E2<260时,在F2中什么都不显示,所以输入1111两个紧跟着的西文双引号。(8)单击"确定"按钮。(9)鼠标对准F2单元格右下角的填充柄拖曳至F8单元格,完成公式的复制填充。3.3.4计数函数COUNT&      功能:返回参

20、数中数字项的个数。COUNT属于统计函数。&      格式:COUNT(value1,value2,.)&      参数:value1,value2,.为包含或引用各种类型数据的参数,COUNT函数最多可以有30个参数。数字、逻辑值、文本数字、日期值、空单元格、单元格和区域的引用地址、名称和标志都可以作为参数。如果参数直接是数字、空白、逻辑值、日期或文本数字,执行时都可以被计算。如果参数直接是非数字的文本或错误值都将被忽略。如果参数是单元格或区域的引用,则只统计引用中的数字、

21、日期值;引用的空白单元格、逻辑值、文字或错误值都将被忽略。例: Al:Fl中的数据如图3-28所示,统计该区域中数字项的个数。在Gl单元格中输入公式:"=COUNT(A1：Fl)",结果、为2。因为参数是引用,所以文本类型的练习、空单元格、逻辑值TRUE、错误值#DIV/0!都将被忽略。只有2005-10-1、10被计算,所以返回值是2。COUNT(练习,2005-10-1,10,TRUE,#DIV/0!)的返回值是4,因为是直接输入,所以空单元格、2005-10-1,10,TRUE被计算,文本和错误值被忽略。例:在El单元格中用COUNT函数统计工作表中数字项的个数。操作

22、步骤:(1)单击要存放公式的单元格El。(2)选取"插入"菜单的"函数"命令,打开"插入函数"对话框。(3)在对话框中选择"统计"函数类型中的函数"COUNT"。(4)单击"确定"按钮,出现COUNT函数的"函数参数"对话框。(5)在参数文本框中输入Al:D6。(6)单击"确定"按钮,结果如图3-29所示。3.3.5最大值函数MAX、最小值函数MIN&      功能:MAX返回

23、参数包含的数据集中的最大数值,MIN返回参数包含的数据集中的最小数值。函数MAX和MIN属于统计函数。&      格式:MAX/MIN(numberl,number2,.)&      参数：numberl,number2,.为需要求最大值或最小值或包含需要求最大值或最小值数据的参数,最多可以有30个参数。数字、逻辑值、文本数字、空单元格、单元格和区域的引用地址、名称和标志都可以作为参数。如果参数直接是数字、空单元格、逻辑值或文本数字,计算时都将被包括在内,空单元格被视为0,T

24、RUE的值为1,FALSE的值为0,文本数字自动转换为数值数字。如果参数直接是非数字文本或错误值,将显示错误提示。如果参数是单元格或区域的引用,只有引用内容是数字的才被计算,其他值都将被忽略。3.3.6LOOKUP(不含数组)&      功能:在指定的单行区域中查找指定的数值,然后返回在另一个指定的单行区域中与该查找到的数值位于相同列位置的数值。或在指定的单列区域中查找指定的数值,然后返回另一个指定的单列区域中与该查找到的数值位于相同行位置的数值。LOOKUP函数属于查找与引用函数。该函数有两种语法形式:向量形式和数组形式,向量形式的

25、LOOKUP函数使用三个参数,数组形式的LOOKUP函数使用两个参数,这里只介绍向量形式的LOOKUP函数。&      格式:LOOKIJP(lookup-value,lookup-vector,result-vector)&      参数:三个参数第一参数lookup-value:要在第一个指定的单行区域或单列区域中查找的值,可以为数字、文本、逻辑值或包含数值的名称或引用。第二参数looklip-vector:要查找的区域(行或列),即指定的第一个单行区域或单列区域。在该区

26、域中查找lookup-value。第三参数result-vector:要返回的值所在的区域(行或列),即指定的另一个单行区域或单列区域,其大小必须与lookup-vector所确定的区域大小相同,位置对应。第二参数确定的区域中的数值必须按升序排.,-2,-l,0,1,2,.,A-Z,FALSE,TRUEg否则,函数LOOKUP不能返回正确的结果。文本不区分大小写。如果函数LOOKUP在第二参数确定的区域中找不到与第一参数完全相同的值,则查找该区域中最接近第一参数且小于它的值。如果第一参数小于第二参数确定的区域中的最小值,函数LOOKUP返回错误值#N/A。例:工作表中,查找成绩为85分的学生的

27、姓名,并将查找结果显示在Gl单元格中。操作步骤如下:(l)单击要存放公式的单元格Gl。(2)选取"插入"菜单的"函数"命令,打开"插入函数"对话框。(3)在对话框中选择"查找与引用"函数类型中的函数"LOOKUP"。(4)单击"确定"按钮,出现LOOKUP函数的"选定参数"对话框。(5)选择默认的参数组合方式,单击"确定",出现LOOKUP函数的"函数参数"对话框。(6)在"lookup-value&quo

28、t;参数文本框中输入85, A2:A8，在"result-vector"参数文本框中输入b2:b(7)回车，产生结果3.3.7条件计数函数COUNTIF&      功能:返回区域中满足给定条件的单元格的个数。COUNTIF函数属于统计函数。·格式:COUNTIF(range,criteria)&      参数:两个参数。第一参数range:需要计算其中满足条件的单元格数目的单元格区域。第二参数criteria:为确定哪些单元格将被计算在内的条件,其形式可以为数字、表达式或文本。当第二参数为表达式或文本时应加双引号;为数字时双引号可加可不加。例如:条件可以表示为32,"32",">32"或"apples"。简单地说,COUNTIF函数的功能是在第一个参数指定的范围中,统计满