电子表格应用基础五.docx

电子表格应用基础五.docx

《电子表格应用基础五.docx》由会员分享，可在线阅读，更多相关《电子表格应用基础五.docx（10页珍藏版）》请在冰豆网上搜索。

电子表格应用基础五

3．2使用函数

函数是Excel定义好的具有特定功能的内置公式。

在公式中可以直接调用这些函数,在调用的时候,一般要提供给它一些数据即参数,函数执行之后一般给出一个结果,这个结果称为函数的返回值。

Excel中提供了大量的可用于不同场合的各类函数,分为财务、日期与时间、数学与三角函数、统计、查找与引用、数据库、文本、逻辑和信息9大类。

这些函数极大地扩展了公式的功能,使数据的计算、处理更为容易,更为方便,特别适用于执行繁长或复杂计算的公式。

函数一般是在公式中调用的。

函数以函数名称开始,后面紧跟着圆括号,圆括号中是以逗号隔开的参数。

函数的参数可以是数值、文本、单元格或单元格区域的引用地址、名称、标志和函数。

有的函数不需要参数,有的则需要输入多个参数,其中有些参数是可选的。

用户必须按照正确的次序和格式输入函数参数,参数无大小写之分。

3.2.1自动求和

在工作表中经常会遇到对数据进行求和的问题,为此Excel在"常用"工具栏上提供了"自动求和"工具按钮,利用该按钮可以快捷地调用求和函数以及平均值、最大值、最小值等函数。

例1:

求工作表中第一个学生的总成绩。

操作步骤如下:

（1）单击要存放求和结果的单元格E2。

（2）单击"常用"工具栏中的"自动求和"按钮。

（3）Excel自动在单元格中插入SUM函数,并给出求和范围,生成相应的求和公式,可用鼠标拖曳选取新的求和区域。

（4）按回车键或单击编辑栏中的输入按钮

以上操作也可以选取需要求和的单元格矛和它们右边存放结果的单元格,如B2:

E2,然后单击"自动求和"按钮即可直接完成求和。

例2:

求工作表中每个学生的平均分及每门课成绩的平均分。

操作步骤如下:

（l）选取单元格区域BLE9或Al:

E9或A2:

E9或Bl:

E9。

（2）单击"自动求和"按钮右侧的下拉按钮,弹出下拉菜单，其中包括：

求和，求平均值、计数、最大值和最小值。

（3）在下拉菜单中选择"平均值"命令,

3.2.2自动计算

Excel提供了自动计算功能,也叫快速计算。

利用它可以自动计算选定单元格区域的均值、最小值、最大值、计数、计数值及求和,其默认计算为求和。

在状态栏单击鼠标右键,可显示"自动计算"快捷菜单。

选择设置某自动计算功能后,当选定了要计算的单元格区域时,其计算结果将在状态栏显示出来。

为状态栏中显示选定区域数值的总和。

也可以先选定要计算的区域,再右键单击状态栏,在快捷菜单中单击需要的计算,状态栏上会立即显示出选定区域此种计算的结果。

注意:

自动计算和自动求和是不同的,自动计算的结果显示在状态栏上,自动求和的结果显示在工作表中。

3.2.3函数的输入和在公式中套用函数

在公式中输入函数有两种方法,一种是直接输入讼,另一种是使用"插入函数"对话框。

（l）直接输入

如果用户对函数名称和参数意义都非常清楚,可以直接在单元格或编辑栏中将函数及其参数输入到公式中,如"=SUM（ALAIO）/10",按回车键或单击编辑栏中的输入按钮"√"确认,得到结果。

（2）通过"插入函数"对话框

由于Excel有两百多个函数,记住所有函数及其参数是非常困难的。

因此创建含有函数的公式时,使用"插入函数"对话框将有助于函数的输入。

例如,在单元格的中,计算B2到B8单元格区域中数据的平均值,操作步骤如下:

1）选取要输入函数的单元格B9。

2）选取"插入"菜单的"函数"命令,或单击编辑栏的"插入函数"按钮髓,或按Shift+F3组合键,打开"插入函数"对话框。

3）在对话框的"选择类别"下拉列表中选择一种函数类别,如"统计"。

4）在"选择函数"列表框中选择所需要的函数名,如"AVERAGE",此时列表框的下方会出现关于该函数功能的简单提示（也可以在"搜索函数"文本框中输入对需要解决的问题的简单说明,然后单击"转到"按钮,就会在"选择函数"列表框中给出推荐的函数,从中进行选择）。

5）单击"确定"按钮,出现"函数参数"对话框。

6）在参数框中可以输入常量、单元格引用或区域。

对单元格引用或区域无把握时,可单击参数框右端的"折叠对话框"按钮,以暂时折叠起对话框,显露出工作表。

此时,可用鼠标选取单元格区域,如B2到B8单元格区域。

然后单击折叠后的参数输入框右端的按钮,回到"函数参数"对话框。

7）完成函数所需要的所有参数输入后,单击"确定"按钮。

在单元格中显示计算结果,编辑栏中显示公式"=AVERAGE（B2:

B8）"。

插入函数还可以使用"公式选项板"。

当在单元格或"编辑"栏中输入"="后,位于"编辑"栏左侧的单元格名称框变为公式选项板。

单击其右侧的下拉按钮打开"函数"列表,单击其中的所需函数,出现"函数参数"对话框,以后的操作与前述方法相同。

如果所需函数没有出现在列表中,可选择"其他函数"。

3.2.4函数的嵌套

当以函数作为参数的时候,称为函数的嵌套。

在公式中最多可以包含七级嵌套函数。

当函数B用作函数A的参数时,函数B称为二级函数。

如果函数B中还有函数C作为参数,则函数C称为三级函数。

例如:

工作表的E2单元格中输入公式"=IF（AVERAGE（BLD2）>60,SUM（B2:

D2）,"不通过"）",公式中使用了嵌套的AVERAGE函数和SUM函数。

在此AVERAGE函数和SUM函数为二级函数。

该公式的含义是,用AVERAGE函数求出B2:

D2单元格区域数据的平均值,并将它与数值60比较,比较后返回值为TRUE或FALSE。

当返回值为TRUE时,求解IF函数的第二个参数,即用SUM函数求该区域数据的和并作为IF函数的结果显示在E2单元格,否则求解IF函数的第三个参数,在E2单元格显示"不通过"。

使用"插入函数"对话框输入该公式的操作步骤如下:

（1）选取要输入函数的单元格E2。

（2）单击编辑栏的"插入函数"按,打开"插入函数"对话框。

（3）在对话框的"选择类别"下拉列表中选择函数类型"逻辑"。

（4）在"选择函数"列表框中选择函数"IF"。

（5）单击"确定"按钮,出现"函数参数"对话框。

（6）单击"Logical-test"文本框,单击公式选项板右侧的下拉按钮打开"函数"列表

（7）单击"AVERAGE"函数,出现该函数的"参数"输入对话框。

（8）在对话框中输入参数B232,单击"确定"按钮,弹出公式出错"警告"信息框,单击信息框中的"确定"按钮,回到公式编辑栏。

（9）在编辑栏中继续输入">60",然后再次单击"插入函数"按钮,回到"IF函数参数"输入对话框。

（lO）单击"Value-if-true"文本框,在公式选项板的"函数"列表中选取SUM函数。

在"SUM函数参数"对话框中输入参数B232,单击"确定"按钮,回到公式编辑栏。

（11）在编辑栏中继续输入IF函数的第三个参数"‘不通过'",回车确认;或再次单击"插入函数"按钮,回到"IF函数参数"输入对话框,在"Value-iffalse"文本框中输入"‘不通过'",单击"确定"按钮结束操作。

3.3常用函数

3.3.1求和函数SUM

&功能:

返回参数中所有参数的和,SUM属于数学与三角函数类。

&格式:

SUM（number1,number2,...）

&参数:

Number1,number2,...为1~30个需要求和的参数。

数值、逻辑值、文本数字、单元格和区域的引用地址、名称和标志都可以作为参数。

直接键入到参数表中的数字、逻辑值及数字的文本表达式将被计算。

如SUM（3,2）等于民SUM（"3",2,TRUE）等于6,因为文本数字"3"在执行时将被自动转换为数值3,逻辑值TRUE被转换为数字l（FALSE为0）。

如果参数是单元格或区域的引用,只有其中的数字将被计算,而引用中的空白单元格、逻辑值、文本或错误值等将被忽略。

如果参数直接为非数字的文本或错误值时,执行时将显示错误提示"#NAME?

"。

示例:

当Al的值为"25"（文本数字）,Bl的值为TRUE肘,SUM（ALB1,2）返回2,因为对非数值型的值的引用不能被转换成数值,都被忽略。

当Al的值为"asd",Bl的值为"xyz"时,SUM（ALB1,2）返回2,因为当引用的值是文本时被忽略。

SUM（"asd",飞yz",2）的返回值是"#VALUE!

",如果asd,xyz不带引号则返回"#NAME?

",两者都是错误提示。

如果ALE2中的数值分别为:

5,15,30,40和50,则:

SUM（ALC2）的返回值为5Og

SUM（BLE2,15）的返回值为150。

使用"插入函数"对话框输入该公式的操作步骤如下:

选取单元格El后,单击编辑栏的"插入函数"按钮,打开"插入函数"对话框。

在对话框中选择"数学与三角函数"函数类型中的函数"SUM",输入参数A1:

D1,单击"确定"按钮。

3.3.2求平均值函数AVERAGE

&功能:

返回参数包含的数据集的算术平均值,AVERAGE属于统计函数。

&格式:

AVERAGE（numberl,number2,...）

&参数:

Number1,number2,...要计算平均值的1~30个参数。

数字、逻辑值、文本数字、单元格和区域的引用地址、名称和标志都可以作为参数。

直接键入到参数表中的数字、逻辑值及数字的文本表达式将被计算,逻辑值中的TRUE被转换为数字1,FALSE被转换为数字0,文本数字在执行时将被自动转换为数值。

如果参数是单元格或区域的引用,只有其中的数字将被计算,而引用中的空白单元格、逻辑值、文本或错误值等将被忽略。

但是,如果单元格包含零值则计算在内。

例:

求工作表中每个学生三门课程的平均分。

操作步骤:

（1）单击单元格E2。

（2）选取"插入"菜单的"函数"命令,打开"插入函数"对话框。

（3）在对话框中选择"统计"函数类型中的函数"AVERAGE"。

（4）单击"确定"按钮,出现AVERAGE函数的"函数参数"对话框。

（5）在"Number1"参数文本框中输入参数B2:

D2。

〈6）单击"确定"按钮。

（7）鼠标对准E2单元格右下角的填充柄拖曳至E8单元格,完成公式的复制填充。

注意:

对单元格中的数值求平均时,应牢记空单元格与含零值单元格的区别,尤其在"选项"对话框中的"视图"选项卡中已经清除了"零佳"复选框的条件下,空单元格不计算在内,但计算零值。

若要查看"选Z页"对话框,单击"工具"菜单的"选工页"命令即可。

3.3.3条件检测函数IF

&功能:

IF函数具有判断的能力,通过对作为第一参数的条件进行判断,根据判断结果的真假执行不同的计算,返回不同的结果。

IF函数属于逻辑函数。

&格式:

IF（logical_test,value_if_true,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是判断的条件,条件成立,返回参数与条件不成立,返回参数3。

参数2、3也可以是公式或函数,但前面不要等号"="。

当参数2、3是函数的时候,就是函数的嵌套,函数最多允许嵌套7层。

参数2、3也可以是带西文双引号的文本,当该文本就是IF函数的返回结果时,则直接显示该文本。

参数2可以省略。

如果省略,应该返回参数2时,则返回0。

参数3也可以省略。

当参数3前面的逗号一起省略时,应该返回参数3,则返回TRUE;当参数3前面的逗号没有省略时,则返回0。

操作示例:

在图3-26所示的工作表中,将总分大于等于260的学生的总评显示为"优秀",否则什么都不显示。

操作步骤:

（1）单击要存放公式的单元格F2。

（2）选取"插入"菜单的"函数"命令,打开"插入函数"对话框。

（3）在对话框中选择"逻辑"函数类型中的函数"IF"。

（4）单击"确定"按钮,出现IF函数的"函数参数"对话框。

（5）在"Logical-test"参数文本框中输入第一个参数:

E2>=260。

也可以单击参数文本

框的"折叠"按钮以暂时折叠起对话框（或将对话框移开）,显露出工作表。

单击E2单元格,将其引用地址E2粘贴到文本框,接着键入">=260"后,再单击"折叠"按钮,还原对话框。

（6）在"Value-if-true"参数文本框中输入第二个参数:

"优秀"。

（7）在"Value-iffalse"参数文本框中输入第三个参数:

。

因为当E2<260时,在F2中什么都不显示,所以输入1111两个紧跟着的西文双引号。

（8）单击"确定"按钮。

（9）鼠标对准F2单元格右下角的填充柄拖曳至F8单元格,完成公式的复制填充。

3.3.4计数函数COUNT

&功能:

返回参数中数字项的个数。

COUNT属于统计函数。

&格式:

COUNT（value1,value2,...）

&参数:

value1,value2,...为包含或引用各种类型数据的参数,COUNT函数最多可以有30个参数。

数字、逻辑值、文本数字、日期值、空单元格、单元格和区域的引用地址、名称和标志都可以作为参数。

如果参数直接是数字、空白、逻辑值、日期或文本数字,执行时都可以被计算。

如果参数直接是非数字的文本或错误值都将被忽略。

如果参数是单元格或区域的引用,则只统计引用中的数字、日期值;引用的空白单元格、逻辑值、文字或错误值都将被忽略。

例:

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函数统计工作表中数字项的个数。

操作步骤:

（1）单击要存放公式的单元格El。

（2）选取"插入"菜单的"函数"命令,打开"插入函数"对话框。

（3）在对话框中选择"统计"函数类型中的函数"COUNT"。

（4）单击"确定"按钮,出现COUNT函数的"函数参数"对话框。

（5）在参数文本框中输入Al:

D6。

（6）单击"确定"按钮,结果如图3-29所示。

3.3.5最大值函数MAX、最小值函数MIN

&功能:

MAX返回参数包含的数据集中的最大数值,MIN返回参数包含的数据集中的最小数值。

函数MAX和MIN属于统计函数。

&格式:

MAX/MIN（numberl,number2,...）

&参数：

numberl,number2,...为需要求最大值或最小值或包含需要求最大值或最小值数据的参数,最多可以有30个参数。

数字、逻辑值、文本数字、空单元格、单元格和区域的引用地址、名称和标志都可以作为参数。

如果参数直接是数字、空单元格、逻辑值或文本数字,计算时都将被包括在内,空单元格被视为0,TRUE的值为1,FALSE的值为0,文本数字自动转换为数值数字。

如果参数直接是非数字文本或错误值,将显示错误提示。

如果参数是单元格或区域的引用,只有引用内容是数字的才被计算,其他值都将被忽略。

3.3.6LOOKUP（不含数组）

&功能:

在指定的单行区域中查找指定的数值,然后返回在另一个指定的单行区域中与该查找到的数值位于相同列位置的数值。

或在指定的单列区域中查找指定的数值,然后返回另一个指定的单列区域中与该查找到的数值位于相同行位置的数值。

LOOKUP函数属于查找与引用函数。

该函数有两种语法形式:

向量形式和数组形式,向量形式的LOOKUP函数使用三个参数,数组形式的LOOKUP函数使用两个参数,这里只介绍向量形式的LOOKUP函数。

&格式:

LOOKup（lookup-value,lookup-vector,result-vector）

&参数:

三个参数

第一参数lookup-value:

要在第一个指定的单行区域或单列区域中查找的值,可以为数字、文本、逻辑值或包含数值的名称或引用。

第二参数lookup-vector:

要查找的区域（行或列）,即指定的第一个单行区域或单列区域。

在该区域中查找lookup-value。

第三参数result-vector:

要返回的值所在的区域（行或列）,即指定的另一个单行区域或单列区域,其大小必须与lookup-vector所确定的区域大小相同,位置对应。

第二参数确定的区域中的数值必须按升序排...,-2,-l,0,1,2,...,A-Z,FALSE,TRu否则,函数LOOKUP不能返回正确的结果。

文本不区分大小写。

如果函数LOOKUP在第二参数确定的区域中找不到与第一参数完全相同的值,则查找该区域中最接近第一参数且小于它的值。

如果第一参数小于第二参数确定的区域中的最小值,函数LOOKUP返回错误值#N/A。

例:

工作表中,查找成绩为85分的学生的姓名,并将查找结果显示在Gl单元格中。

操作步骤如下:

（l）单击要存放公式的单元格Gl。

（2）选取"插入"菜单的"函数"命令,打开"插入函数"对话框。

（3）在对话框中选择"查找与引用"函数类型中的函数"LOOKUP"。

（4）单击"确定"按钮,出现LOOKUP函数的"选定参数"对话框。

（5）选择默认的参数组合方式,单击"确定",出现LOOKUP函数的"函数参数"对话框。

（6）在"lookup-value"参数文本框中输入85,A2:

A8，在"result-vector"参数文本框中输入b2:

b

（7）回车，产生结果

3.3.7条件计数函数COUNTIF

&功能:

返回区域中满足给定条件的单元格的个数。

COUNTIF函数属于统计函数。

·格式:

COUNTIF（range,criteria）

&参数:

两个参数。

第一参数range:

需要计算其中满足条件的单元格数目的单元格区域。

第二参数criteria:

为确定哪些单元格将被计算在内的条件,其形式可以为数字、表达式或文本。

当第二参数为表达式或文本时应加双引号;为数字时双引号可加可不加。

例如:

条件可以表示为32,"32",">32"或"apples"。

简单地说,COUNTIF函数的功能是在第一个参数指定的范围中,统计满足第二个参数给定的条件的单元格个数。

例:

如果B2:

B5中的数据分别为:

32,54,75,86,则计算该区域中值大于55的单元格个数用"=COUNTIF（B2:

时,">55"）",等于2:

计算该区域中值为86的单元格个数用"=COUNTIF（B2:

B5,86）",或"=COUNTIF（BLB5,"86"）",或"二COUNTIF（B2:

B5,」86"）",等于1。

3.3.8取整函数INT

&功能:

返回参数取整处理后的整数值。

无论参数是正数还是负数,INT函数总是将要处理的数取整为小于等于它的整数。

INT函数属于数学与三角函数。

&格式:

INT（number）

&参数:

只有一个参数rlumber,需要进行取整处理的实数。

INT（8.9）等于8。

3.3.9舍入函数ROUND

&功能:

返回某个数字按指定位数四舍五入后的数字。

ROUND函数属于数学与三角函数。

&格式:

ROUND（number,num_digits）。

&参数:

两个参数。

第一参数number:

需要进行四舍五入处理的数字。

第二参数I111III-digits:

指定的舍入位数。

例:

ROUND（2.15,1）返回2.2,将2.15四舍五入到一个小数位。

ROUND（2.149,l）返回2.1,将2.149四合五入到一个小数位。

ROUND（-1.475,2）返回-1.48,将一1.475四舍五入到两个小数位。

ROUND（21.5,一1）返回20,将21.5四舍五入到小数点左侧一位。

ROIJNB（473.67,-2）返回500,将473.67四舍五入到小数点左侧二位。

3.3.10绝对值函数ABS

•功能:

返回数字的绝对值，绝对值没有符号。

ABS函数属于数学与三角函数。

格式:

ABS（number）。

•参数:

只有一个参数number，需要计算其绝对值的实数或引用的单元格。

示例:

ABS

（2）返回2。

ABS（-2）返回2。

如果Al单元格中的内容是一个数值，无论正负，公式"=ABS（Al）"的返回值都是一个正数。

如果参数是文本字符，则函数ABS返回错误值"#VALUE!

"。

3.3.11日期函数TODAY

•功能:

返回当前日期的序列数。

如果在输入函数前，单元格的格式为"常规"，则结果以日期格式显示。

TODAY函数属于日期与时间函数。

•格式:

TODAY（），没有参数。

每当编辑任意单元格或重新打开包含该公式的工作簿时，公式都会重新计算，并返回当前的日期。

3.4在公式中套用函数和函数的嵌套

3.4.1在公式中套用函数

公式可以由单一函数构成，但更多的情况下，公式是由运算符、常量、单元格引用以及函数共同组成的。

操作示例1：

在学生成绩工作标注红，求每一个学生的数学成绩与该课程平均成绩之间的差，填写在C2：

c8区域中。

操作步骤：

单击C2单元格，输入公式"=B2-AVERAGE（B$2:

B$8）"，确认后将公式复制到C3:

C8区域即可。

此处单元格区域的引用是"B$2:

B$8"，列相对引用、行绝对引用，也可以是"$B$2:

$B$8"，即所求平均分的区域固定。

使用"插入函数"对话框输入该公式的操作步骤如下:

（1）单击要存放公式的单元格C2。

（2）单击编辑栏，输入"="，或直接在单元格中输入"-"。

（3）单击单元格B2，再输入"一"。

（4）用公式选项板或"插入函数"对话框打开AVERAGE函数的"函数参数"对话框。

（5）在第一个参数文本框中输入"B$2:

B$8"或"$B$2:

$B$8"。

（6）单击"确定"按钮。

（7）鼠标对准B2单元格右下角的填充柄按左键拖曳至B8单元格，完成公式的复制填充。

3.4.2函数的嵌套

当以函数作为参数的时候，称为函数的嵌套。

在公式中最多可以包含七级嵌套函数。

当函数B用作函数A的参数时，函数B称为二级函数。

如果函数B中还有函数C作为参数，则函数C称为三级函数。