电子表格应用基础五.docx

1、电子表格应用基础五32使用函数函数是Excel定义好的具有特定功能的内置公式。在公式中可以直接调用这些函数,在调用的时候,一般要提供给它一些数据即参数,函数执行之后一般给出一个结果,这个结果称为函数的返回值。Excel中提供了大量的可用于不同场合的各类函数,分为财务、日期与时间、数学与三角函数、统计、查找与引用、数据库、文本、逻辑和信息9大类。这些函数极大地扩展了公式的功能,使数据的计算、处理更为容易,更为方便,特别适用于执行繁长或复杂计算的公式。函数一般是在公式中调用的。函数以函数名称开始,后面紧跟着圆括号,圆括号中是以逗号隔开的参数。函数的参数可以是数值、文本、单元格或单元格区域的引用地址

2、、名称、标志和函数。有的函数不需要参数,有的则需要输入多个参数,其中有些参数是可选的。用户必须按照正确的次序和格式输入函数参数,参数无大小写之分。3.2.1自动求和在工作表中经常会遇到对数据进行求和的问题,为此Excel在常用工具栏上提供了自动求和工具按钮,利用该按钮可以快捷地调用求和函数以及平均值、最大值、最小值等函数。例1:求工作表中第一个学生的总成绩。操作步骤如下:(1)单击要存放求和结果的单元格E2。(2)单击常用工具栏中的自动求和按钮。(3)Excel自动在单元格中插入SUM函数,并给出求和范围,生成相应的求和公式,可用鼠标拖曳选取新的求和区域。(4)按回车键或单击编辑栏中的输入按钮

3、以上操作也可以选取需要求和的单元格矛和它们右边存放结果的单元格,如B2:E2,然后单击自动求和按钮即可直接完成求和。例2:求工作表中每个学生的平均分及每门课成绩的平均分。操作步骤如下:(l)选取单元格区域BLE9或Al:E9或A2:E9或Bl:E9。(2)单击自动求和按钮右侧的下拉按钮,弹出下拉菜单，其中包括：求和，求平均值、计数、最大值和最小值。(3)在下拉菜单中选择平均值命令,3.2.2自动计算Excel提供了自动计算功能,也叫快速计算。利用它可以自动计算选定单元格区域的均值、最小值、最大值、计数、计数值及求和,其默认计算为求和。在状态栏单击鼠标右键,可显示自动计算快捷菜单。选择设置某自动

4、计算功能后,当选定了要计算的单元格区域时,其计算结果将在状态栏显示出来。为状态栏中显示选定区域数值的总和。也可以先选定要计算的区域,再右键单击状态栏,在快捷菜单中单击需要的计算,状态栏上会立即显示出选定区域此种计算的结果。注意:自动计算和自动求和是不同的,自动计算的结果显示在状态栏上,自动求和的结果显示在工作表中。3.2.3函数的输入和在公式中套用函数在公式中输入函数有两种方法,一种是直接输入讼,另一种是使用插入函数对话框。(l)直接输入如果用户对函数名称和参数意义都非常清楚,可以直接在单元格或编辑栏中将函数及其参数输入到公式中,如=SUM(ALAIO)/10,按回车键或单击编辑栏中的输入按钮

5、确认,得到结果。(2)通过插入函数对话框由于Excel有两百多个函数,记住所有函数及其参数是非常困难的。因此创建含有函数的公式时,使用插入函数对话框将有助于函数的输入。例如,在单元格的中,计算B2到B8单元格区域中数据的平均值,操作步骤如下:1)选取要输入函数的单元格B9。2)选取插入菜单的函数命令,或单击编辑栏的插入函数按钮髓,或按Shift+F3组合键,打开插入函数对话框。3)在对话框的选择类别下拉列表中选择一种函数类别,如统计。4)在选择函数列表框中选择所需要的函数名,如AVERAGE,此时列表框的下方会出现关于该函数功能的简单提示(也可以在搜索函数文本框中输入对需要解决的问题的简单说明

6、,然后单击转到按钮,就会在选择函数列表框中给出推荐的函数,从中进行选择)。5)单击确定按钮,出现函数参数对话框。6)在参数框中可以输入常量、单元格引用或区域。对单元格引用或区域无把握时,可单击参数框右端的折叠对话框按钮,以暂时折叠起对话框,显露出工作表。此时,可用鼠标选取单元格区域,如B2到B8单元格区域。然后单击折叠后的参数输入框右端的按钮,回到函数参数对话框。7)完成函数所需要的所有参数输入后,单击确定按钮。在单元格中显示计算结果,编辑栏中显示公式=AVERAGE(B2:B8)。插入函数还可以使用公式选项板。当在单元格或编辑栏中输入=后,位于编辑栏左侧的单元格名称框变为公式选项板。单击其右

7、侧的下拉按钮打开函数列表,单击其中的所需函数,出现函数参数对话框,以后的操作与前述方法相同。如果所需函数没有出现在列表中,可选择其他函数。3.2.4函数的嵌套当以函数作为参数的时候,称为函数的嵌套。在公式中最多可以包含七级嵌套函数。当函数B用作函数A的参数时,函数B称为二级函数。如果函数B中还有函数C作为参数,则函数C称为三级函数。例如:工作表的E2单元格中输入公式=IF(AVERAGE(BLD2)60,SUM(B2:D2),不通过),公式中使用了嵌套的AVERAGE函数和SUM函数。在此AVERAGE函数和SUM函数为二级函数。该公式的含义是,用AVERAGE函数求出B2:D2单元格区域数据

8、的平均值,并将它与数值60比较,比较后返回值为TRUE或FALSE。当返回值为TRUE时,求解IF函数的第二个参数,即用SUM函数求该区域数据的和并作为IF函数的结果显示在E2单元格,否则求解IF函数的第三个参数,在E2单元格显示不通过。使用插入函数对话框输入该公式的操作步骤如下:(1)选取要输入函数的单元格E2。(2)单击编辑栏的插入函数按 ,打开插入函数对话框。(3)在对话框的选择类别下拉列表中选择函数类型逻辑。(4)在选择函数列表框中选择函数IF。(5)单击确定按钮,出现函数参数对话框。(6)单击Logical-test文本框,单击公式选项板右侧的下拉按钮打开函数列表(7)单击AVERA

9、GE函数,出现该函数的参数输入对话框。(8)在对话框中输入参数B232,单击确定按钮,弹出公式出错警告信息框,单击信息框中的确定按钮,回到公式编辑栏。(9)在编辑栏中继续输入60,然后再次单击插入函数按钮,回到IF函数参数输入对话框。(lO)单击Value-if-true文本框,在公式选项板的函数列表中选取SUM函数。在SUM函数参数对话框中输入参数B232,单击确定按钮,回到公式编辑栏。(11)在编辑栏中继续输入IF函数的第三个参数不通过,回车确认;或再次单击插入函数按钮,回到IF函数参数输入对话框,在Value-iffalse文本框中输入不通过,单击确定按钮结束操作。3.3常用函数3.3.

10、1求和函数SUM& 功能:返回参数中所有参数的和,SUM属于数学与三角函数类。& 格式:SUM(number1,number2,.)& 参数:Number1,number2,.为130个需要求和的参数。数值、逻辑值、文本数字、单元格和区域的引用地址、名称和标志都可以作为参数。直接键入到参数表中的数字、逻辑值及数字的文本表达式将被计算。如SUM(3,2)等于民SUM(3,2,TRUE)等于6,因为文本数字3在执行时将被自动转换为数值3,逻辑值TRUE被转换为数字l(FALSE为0)。如果参数是单元格或区域的引用,只有其中的数字将被计算,而引用中的空白单元格、逻辑值、文本或错误值等将被忽略。如果参

11、数直接为非数字的文本或错误值时,执行时将显示错误提示#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)的返回值为5OgSUM(BLE2,15)的返回值为150。使用插入函数对话框输入该公式的操作步骤如

12、下:选取单元格El后,单击编辑栏的插入函数按钮,打开插入函数对话框。在对话框中选择数学与三角函数函数类型中的函数SUM,输入参数A1:D1,单击确定按钮。3.3.2求平均值函数AVERAGE& 功能:返回参数包含的数据集的算术平均值,AVERAGE属于统计函数。& 格式:AVERAGE(numberl,number2,.)& 参数:Number1,number2,.要计算平均值的130个参数。数字、逻辑值、文本 数字、单元格和区域的引用地址、名称和标志都可以作为参数。直接键入到参数表中的数字、逻辑值及数字的文本表达式将被计算,逻辑值中的TRUE被转换为数字1,FALSE被转换为数字0,文本数字

13、在执行时将被自动转换为数值。如果参数是单元格或区域的引用,只有其中的数字将被计算,而引用中的空白单元格、逻 辑值、文本或错误值等将被忽略。但是, 如果单元格包含零值则计算在内。例:求工作表中每个学生三门课程的平均分。操作步骤:(1)单击单元格E2。(2)选取插入菜单的函数命令,打开插入函数对话框。(3)在对话框中选择统计函数 类型中的函数AVERAGE。(4)单击确定按钮,出现AVERAGE函数的函数参数对话框。(5)在Number1参数文本框中输入参数B2:D2。6)单击确定按钮。(7)鼠标对准E2单元格右下角的填充柄拖曳至E8单元格,完成公式的复制填充。注意:对单元格中的数值求平均时,应牢

14、记空单元格与含零值单元格的区别,尤其在选项对话框中的视图选项卡中已经清除了零佳复选框的条件下,空单元格不计算在内,但计算零值。若要查看选Z页对话框,单击工具菜单的选工页命令即可。3.3.3条件检测函数IF& 功能:IF函数具有判断的能力,通过对作为第一参数的条件进行判断,根据判断结果的真假执行不同的计算,返回不同的结果。IF函数属于逻辑函数。& 格式:IF(logical_test,value_if_true,value_if_false)& 参数:三个参数,各参数含义如下:参数1:LogicalJtest,数值或表达式,计算结果为逻辑值,表示被判断的条件。参数2:Value-if-true,

15、当检测到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可以省略。如果

16、省略,应该返回参数2时,则返回0。 参数3也可以省略。当参数3前面的逗号一起省略时,应该返回参数3,则返回TRUE;当参数3前面的逗号没有省略时,则返回0。操作示例:在图3-26所示的工作表中,将总分大于等于260的学生的总评显示为优秀,否则什么都不显示。操作步骤:(1)单击要存放公式的单元格F2。(2)选取插入菜单的函数命令,打开插入函数对话框。(3)在对话框中选择逻辑函数类型中的函数IF。(4)单击确定按钮,出现IF函数的函数参数对话框。(5)在Logical-test参数文本框中输入第一个参数:E2=260。也可以单击参数文本框的折叠按钮以暂时折叠起对话框(或将对话框移开),显露出工作表

17、。单击E2单元格,将其引用地址E2粘贴到文本框,接着键入=260后,再单击折叠按钮,还原对话框。(6)在Value-if-true参数文本框中输入第二个参数:优秀。(7)在Value-iffalse参数文本框中输入第三个参数:。因为当E232或apples。简单地说,COUNTIF函数的功能是在第一个参数指定的范围中,统计满足第二个参数给 定的条件的单元格个数。例: 如果B2:B5中的数据分别为:32,54,75,86,则计算该区域中值大于55的单元格个数用=COUNTIF(B2:时,55),等于2:计算该区域中值为86的单元格个数用=COUNTIF(B2:B5,86),或=COUNTIF(B

18、LB5,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:需要进行四舍五入处理的数字。第二参数I

19、111III-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

20、，需要计算其绝对值的实数或引用的单元格。 示例: ABS(2)返回2。 ABS(-2)返回2。 如果Al单元格中的内容是一个数值，无论正负，公式=ABS(Al) 的返回值都是一个 正数。 如果参数是文本字符，则函数ABS返回错误值#VALUE! 。 3.3.11日期函数TODAY 功能:返回当前日期的序列数。如果在输入函数前，单元格的格式为常规，则结果 以日期格式显示。TODAY函数属于日期与时间函数。 格式: TODAY（），没有参数。 每当编辑任意单元格或重新打开包含该公式的工作簿时，公式都会重新计算，并返回当 前的日期。 3.4在公式中套用函数和函数的嵌套3.4.1在公式中套用函数公式可

21、以由单一函数构成，但更多的情况下，公式是由运算符、常量、单元格引用以及函数共同组成的。操作示例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称为三级函数。