为了方便用户使用,Excel提供了9大类函数,包括数学与三角函数、统计函数、数据库函数、财务函数、日期与时间函数、逻辑处理函数和文本处理函数等。用户使用时只需按规定格式写出函数及所需的参数(number)即可。函数的一般格式是:
函数名(参数1,参数2,…)
例如,要求出A1、A2、A3、A4四个单元格内数值之和,可写上函数“=SUM(A1:A4)”,其中SUM为求和函数名,A1:A4为参数。
又如“=SUM(A1:A30,D1:D30)”,其中逗号表示要求总和的有两个区域。与输入公式一样,输入函数时必须以等号(=)开头。
函数的输入有两种方法:一是手工输入;二是利用函数向导输入,通过单击“公式”选项卡下“插入函数fx”按钮,或单击“编辑”工具栏上的“插入函数”按钮fx,打开“插入函数”对话框,然后在函数向导的指引下输入函数。
常用函数及其功能将在4.6.2节介绍,用户也可以借助于Excel帮助系统查阅函数的使用说明。
下面先重点介绍几个常用函数及其使用方法。
1.求和函数SUM
格式:SUM(number1,number2,…)
功能:计算参数中数值的总和。
说明:每个参数可以是数值、单元格引用坐标或函数。
例4-5 如图4-3所示的“工资表”中,要求计算基本工资总数,并将结果存放在单元格D8中。操作步骤如下:
①单击单元格D8,使之成为活动单元格。
②键入公式“=SUM(D2:D7)”,并按回车键。
执行结果如图4-9所示。
图4-9 例4-5的执行结果
为了便于使用,Excel在“公式”选项卡下的“函数库”组中设置了“自动求和”按钮∑,并扩充其使用功能。利用此按钮的下拉列表,可以对一至多列数据求和、求最大数、计算平均值等。
例4-6 在上述工资表中,要求计算基本工资总数和扣款总数,并将结果分别存放在单元格D8和E8中。操作步骤如下:
①选定D2:E8区域。
②单击“公式”选项卡下“函数库”组中的“自动求和”按钮∑,即可对这两列分别求和,和数分别存放在两列所选区域的最后一个单元格上。
2.求平均值函数AVERAGE
格式:AVERAGE(numberl,number2,…)
功能:计算参数中数值的平均值。
例4-7 在例4-6的基础上,要求计算基本工资的平均值,并将结果存放在D9中。以下利用“插入函数”来输入公式,操作步骤如下:
①单击单元格D9,使之成为活动单元格。
②单击“公式”选项卡下“插入函数fx”按钮,或单击“编辑”工具栏上的“插入函数”按钮fx,系统弹出“插入函数”对话框。
③从“或选择类别”框中选择“常用函数”,从“选择函数”框中选择“AVERAGE”。
④单击“确定”按钮,系统弹出“函数参数”对话框。
⑤在Numberl(参数1)文本框中键入“D2:D7”(也可通过鼠标拖放来选定单元格区域),如图4-10所示。
图4-10 “函数参数”对话框
⑥单击“确定”按钮。
执行结果如图4-11所示。
图4-11 例4-7的执行结果
3.求最大值函数MAX
格式:MAX(Numberl,Number2,…)
功能:求参数中数值的最大值。
例4-8 在例4-7的基础上,利用函数MAX求出基本工资数的最大值,并将结果存放在单元格D10中。操作步骤如下:
①单击单元格D10,使之成为活动单元格。
②键入公式“=MAX(D2:D7)”,并按回车键。
4.求数字个数函数COUNT
格式:COUNT(Numberl,Number2,…)
功能:求参数中数值数据的个数。
例如,假设单元格A1、A2、A3、A4的值分别为1、2、空,“ABC”,则COUNT(A1:A4)的值为2。
5.条件计数函数COUNTIF
格式:COUNTIF(Range,Criteria)
功能:返回区域Range中符合条件Criteria的单元格个数。
说明:Range为单元格区域,在此区域中进行条件测试,Criteria为双引号括起来的比较条件式,也可以是一个数值常量或单元格地址。
例如,在上述“工资表”中,若要求出基本工资大于等于3000元的职工人数,可以采用以下函数:
=COUNTIF(D2:D7,">=3000")
例如,在图4-14所示的工作表(A1:D9)中,要求出班号为21的班级学生人数,可以采用以下函数:
=COUNTIF(D2:D9,"21")
或
=COUNTIF(D2:D9,D3)
6.逻辑函数IF
格式:IF(条件,值1,值2)
其中:“条件”(也称Logical_test参数)为比较条件式,可使用比较运算符,如=、<>、>、<、>=、<=等;“值1”(Value_if_true)为条件成立时取的值;“值2”(Value_if_false)为条件不成立时取的值。
功能:本函数对“条件”进行测试,如果条件成立,则取第一个值(即值1),否则就取第二个值(即值2)。
例如,已知单元格C2中存放考试分数,现要根据该学分数判断学生是否及格,可采用如下函数:
=IF(C2<60,"不及格","及格")
一个IF函数可以实现“二者选一”的运算,若要在更多的情况中选择一种,则需要IF函数嵌套来完成。
例4-9 成绩等级与分数有如下关系:成绩>=80——优良,60<=成绩<80——中,成绩<60——不及格,假设成绩存放在单元格C2中,则可以采用如下函数来得到等级信息:
=IF(C2>=80,"优良",IF(C2>=60,"中","不及格")
例4-10 在工资表中,按级别计算“补贴”,教授补贴1500元,副教授补贴1200元,讲师1000元,助教及其他工作人员补贴800元,然后计算每个人的实发数。
操作步骤如下:
①计算第一条记录的“补贴”。
在F2单元格中输入函数:
=IF(C2="教授",1500,IF(C2="副教授",1200,IF(C2="讲师",1000,800)))
按回车键后,该单元格显示1500,如图4-12所示。
图4-12 输入函数及显示结果
注意 上面函数不能写成=IF(C2="教授","1500"……即不能用双撇号把数字(数值型数据)括起来,只是用到文本数据时才要用双撇号(英文双引号)括起来,如“教授”。(www.xing528.com)
②计算第一条记录的“实发数”。
在G2单元格中输入公式:
=D2+F2-E2
按回车键后,该单元格显示5600。
③用复制公式的方法,把单元格F2和G2中的公式复制到对应的其他单元格中,以计算出其他老师的“补贴”和“实发数”。
7.排名次函数RANK
格式:RANK(Number,Ref,Order)
功能:返回一个数字在数字列表中的排名。
其中Number为需要找到排名次的数字,Ref为数字列表数组或对数字列表的引用(Ref中的非数值型参数将被忽略),Order为一个数字,指明排名次的方式。
返回一个数字在数字列表中的排名数。
例如,要给如图4-13所示的成绩单的每一名学员的成绩排名。输入公式的具体操作步骤是:单击H3单元格,输入:=RANK(G3,$G$3:$G$15,0),按〈Enter〉键。
图4-13 RANK函数应用举例
此函数的公式使用与前面的函数使用不同,采用了绝对引用,这是因为当用户完成H3单元格操作后,其他的单元格需要使用填充柄,如果不使用绝对引用,Ref的范围将变为“G4:G16”,而排序的Ref应固定为“G3:G15”,所以必须采用绝对引用。
自Office 2010开始,RANK函数分成两种:一种叫RANK.EQ,其用法和原来版本的RANK函数一致;另一种叫RANK.AVG函数。两者的不同之处在于:RANK.AVG函数对于数值相等的情况,返回该数值的平均排名;而RANK.EQ函数对于相等的数值返回其最高排名。
8.频率分布统计函数FREQUENCY
频率分布统计函数用于统计一组数据在各个数值区间的分布情况,这是对数据进行分析的常用方法之一。
格式:FREQUENCY(Data_array,Bins_array)
其中,Data_array为要统计的数据(数组),Bins_array为统计的间距数据(数组)。
设Bins_array为指定的参数为A1,A2,A3,…,An,则其统计的区间为X<=A1,A1<X<=A2,A2<X<=A3,…,An-1<X<=An,X>An,共n+1个区间。
功能:计算一组数(Data_array)分布在指定各区间(由bins_array来确定)的个数。
例4-11 在图4-13的成绩表(A1:D9)中,统计出成绩<60,60<=成绩<70,70<=成绩<80,80<=成绩<90,成绩>=90的学生各有多少。操作步骤如下:
①在一个空区域(如F4:F7)中建立统计的间距数组(59,69,79,89)。
②选定作为统计结果的数组输出区域G4:G8。
③键入函数“=FREQUENCY(C2:C9,F4:F7)”(或使用“插入函数”对话框来设置)。
④按下〈Ctrl〉+〈Shift〉+〈Enter〉组合键。
执行结果如图4-14所示。
图4-14 例4-11的执行结果
说明 Excel中,输入一般公式或函数后按〈Enter〉键(或单击编辑栏上的“√”按钮),但对于含有数组参数的公式或函数(如上述的FREQUENCY函数),则必须按〈Ctrl〉+〈Shift〉+〈Enter〉组合键。
常用函数及功能一览表
(1)数学函数(见表4-4)
表4-4 数学函数
续表
(2)统计函数(见表4-5)
表4-5 统计函数
(3)文本函数(也称字符串函数,见表4-6)
表4-6 文本函数
(4)日期和时间函数(见表4-7)
表4-7 日期和时间函数
(5)数据库函数表(见表4-8)
表4-8 数据库函数表
(6)逻辑函数
①逻辑“与”函数(AND)。
格式:AND(Logical1,Logical2,…)
功能:当所有参数的逻辑值都是TRUE(真)时,返回TRUE;否则返回FALSE(假)。
说明:Logical1,Logical2,…是1~30个结果为逻辑值的表达式(一般为比较运算表达式)。
举例:=AND(3>=1,1+6=7) 结果为TRUE
=AND(TRUE,6<=7) 结果为TRUE
=AND(3>=1,"AB">"A",FALSE) 结果为FALSE
②逻辑“或”函数(OR)。
格式:OR(Logical1,Logical2,…)
功能:当所有参数的逻辑值都是FALSE(假)时,返回FALSE(假);否则返回TRUE(真)。
说明:Logical1,Logical2,…是1~30个结果为逻辑值的表达式(一般为比较运算表达式)。
举例:=OR(3>=1,1+6=7) 结果为TRUE
=OR(FALSE,6>=7) 结果为FALSE
=OR(2>=5,"AB">"A",FALSE) 结果为TRUE
③逻辑“非”函数(NOT)。
格式:NOT(Logical)
功能:当Logical的值为FALSE时,返回TRUE;当Logical的值为TRUE时,返回FALSE。
举例:=NOT(3>=1) 结果为FALSE
=NOT(FALSE) 结果为TRUE
④条件选择函数IF。
这是一个逻辑函数。它能根据不同情况选择不同表达式进行处理。
在前面已经介绍IF函数的格式及功能,下面结合逻辑函数AND再举一例。
假设在工作表的C8单元格中存放学生身高(cm),现要挑选170~175cm的人选,凡符合条件的显示“符合条件”,不符合条件的显示“不符合条件”,采用的公式为:
=IF(AND(C8>=170,C8<=175),"符合条件","不符合条件")
免责声明:以上内容源自网络,版权归原作者所有,如有侵犯您的原创版权请告知,我们将尽快删除相关内容。