计算机二级常用函数公式及解析.docx
《计算机二级常用函数公式及解析.docx》由会员分享,可在线阅读,更多相关《计算机二级常用函数公式及解析.docx(7页珍藏版)》请在冰豆网上搜索。
计算机二级常用函数公式及解析
--本页仅作为文档封面,使用时请直接删除即可----内页可以根据需求调整合适字体及大小--
计算机二级常用函数公式及解析(总5页)
1、求和函数SUM
功能:
将参数中的所有数字相加求和;其中每个参数既可以是一个单元格或单元格区域的引用或名称,也可以是一个常量、公式或另一函数的运算结果等。
使用格式:
SUM(Numberl,Number2,…)
说明:
第一个参数Number1是必须要给出的,Number2及以后参数是可有可无的。
若通过Number1已经指定完毕所有要相加的数据(例如通过Number1指定了一个单元格区域,区域中已包含了所有数据),就不必再给出Number2及以后的参数;当需要更多要相加的内容时再通过Number2、Number3…等给出(一般最多不要给出超过30个参数)。
例如:
“SUM(A1:
A5)”表示对A1~A5这5个单元格中的内容求和;“SUM(A1,A3,A5)”表示对单元格A1、A3和A5的数值求和(本书函数实例均不含“=”,因为函数可以作为公式的一部分,而“=”是用于输入整个公式的;由一个函数组成的公式只是公式的一种特例)。
2、条件求和函数SUMIF
功能:
也是相加求和,但会从参数指定的单元格区域中进行挑选,仅对挑选出的符合指定条件的那些单元格求和。
使用格式:
SUMIF(Range,Criteria,Sum_range)
说明:
Range参数是要进行条件计算的单元格区域。
Criteria为求和的条件,其形式可以是数字、文本、表达式、单元格引用或函数等,例如可以是32、"32"、"苹果"、">32"、B5、SQRT(4)等。
Sum_range是要求和的实际单元格区域,如果Sum_range参数省略,将对Range中的单元格求和;否则Range仅表示条件,将对Sum_range中符合条件的对应单元格求和。
例如:
“SUMIF(B2:
B25,">5")”表示对B2:
B5区域中大于5的数值进行相加求和;“SUMIF(B2:
B5,"John",C2:
C5)”表示先找到B2:
B5中等于"John"的单元格,再通过这些单元格找到C2:
C5中的对应单元格,对C2:
C5中的这些对应单元格的数值求和。
3、多条件求和函数SUMIFS
功能:
也是相加求和,但是对指定区域中满足多个条件的单元格求和。
使用格式:
SUMIFS(Sum_range,Criteria_range1,Criteria1,Criteria_range2,Criteria2,…)
说明:
Sum_range为实际求和的单元格区域。
Criteria_range1为在其中计算关联条件的第1个区域;Criteria1为第1个求和的条件,例如条件可以是32、"32"、"苹果"、">32"、B5、SQRT(4)等。
Criteria1与Criteria_range1配对使用。
Criteria_range2,Criteria2,…及以后的参数可省略,表示附加的区域和条件。
Criteria2与Criteria_Range2配对使用,Criteria3与Criteria_range3配对使用……但每个Criteria_RangeN的参数区域所包含的行数和列数都必须与Sum_range的相同。
例如:
“SUMIFS(A1:
A20,B1:
B20,">0",C1:
C20,"<10")”表示对区域A1:
A20中符合以下条件的单元格求和:
B1:
B20中的相应数值大于0、且C1:
C20中的相应数值小于10。
4、平均值函数AVERAGE
功能:
求各参数的算术平均值。
使用格式:
AVERAGE(Numberl,Number2,…)
说明:
与SUM函数类似,第一个参数Number1是必须要给出的,Number2及以后参数是可有可无的。
若Number1中已经指定完毕所有要求平均值的数据(例如通过Number1指定了一个单元格区域,区域中包含了所有数据),就不必再给出Number2及以后的参数;若需要更多数据求平均值再通过Number2及以后的参数指定这些数据(一般最多不超过30个参数)。
例如:
“AVERAGE(A2:
A6)”表示对A2~A6这5个单元格的数据求平均值;“AVERAGE(A2:
A6,C6)”表示对A2~A6这5个单元格及C6单元格中的数据(共6个数据)求平均值。
如图(从略),我们已为法律一班“潘志阳”同学计算出了期末总分,现需为他计算平均分。
选中要保存计算结果的单元格J3,使其成为活动单元格。
在【公式】选项卡【函数库】工具组中单击“插入函数”按钮,弹出“插入函数”对话框。
在类别中选择“统计”,再从函数列表中选择AVERAGE,单击【确定】。
在打开的“函数参数”对话框中单击参数的“”按钮折叠对话框,然后选择区域D3:
H3,如图(从略)。
再次单击“”恢复对话框,单击【确定】。
再向下拖动J3的填充柄到J9单元格,完成所有同学的平均分计算。
5、条件平均值函数AVERAGEIF
功能:
对指定区域中满足给定条件的单元格求算术平均值。
使用格式:
AVERAGEIF(Range,Criteria,Average_range)
说明:
Range参数是要进行条件计算的单元格区域。
Criteria为求平均值的条件,其形式可以是数字、文本、表达式、单元格引用或函数等,例如32、"32"、"苹果"、">32"、B5、SQRT(4)等。
Average_range是可选的参数,表示要求平均值的实际单元格区域,如果Average_range参数省略,将对Range中的单元格求平均;否则Range仅表示条件,将对Average_range中符合条件的对应单元格求平均。
例如:
“AVERAGEIF(A2:
A5,"<5000")”表示求单元格区域A2:
A5中小于5000的数值的平均值;“AVERAGEIF(A2:
A5,">5000",B2:
B5)”表示对B2:
B5中的一部分单元格求平均,这部分的单元格是:
与A2:
A5区域中大于5000的单元格所对应的那部分单元格。
6、求最大值函数MAX和求最小值函数MIN
功能:
计算一批数值中的最大值(MAX函数)和最小值(MIN函数)。
使用格式:
MAX(Numberl,Number2,…)和MIN(Numberl,Number2,…)
说明:
同样第一个参数Number1是必须要给出的,Number2及以后参数是可有可无的。
若Number1中已经指定完毕所有要求最大或最小值的数据(例如通过Number1指定了一个单元格区域,区域中包含了所有数据),就不必再给出Number2及以后参数;若需要更多数据求最大或最小值就通过Number2及以后参数指定这些数据(一般最多不超过30个参数)。
例如:
“MAX(D2:
D6)”表示求D2到D6单元格中的最大值;“MIN(E55:
J55,10)”表示求E55:
J55区域中所有单元格中的数值、以及10这些数值中的最小值。
7、计数函数COUNT和COUNTA
功能:
统计某单元格区域中的单元格的个数,COUNT是只统计内容为数字的单元格个数,COUNTA是统计内容不为空的单元格的个数。
使用格式:
COUNT(Value1,Value2,…)和COUNTA(Value1,Value2,…)
说明:
通过Value1,Value2,……指定要计数的单元格区域,同样其中第一个参数Value1是必须要给出的,Value2及以后参数是可有可无的。
例如:
“COUNT(A2:
A8)”表示统计单元格区域A2:
A8中包含数值的单元格的个数;“COUNTA(A2:
A8)”表示统计单元格区域A2:
A8中的非空单元格的个数。
8、条件计数函数COUNTIF
功能:
统计某单元格区域中符合给定条件的单元格个数。
使用格式:
COUNTIF(Range,Criteria)
说明:
Range表示要统计的单元格区域,Criteria表示指定的条件,当条件为一个值时可直接写出,当条件为一个表达式(">=80")时,应将条件用双引号引起来。
例如:
“COUNTIF(B2:
B5,">=80")”表示统计出B2~B5中值大于等于80的单元格个数。
如果单元格D3:
D17中保存有全部学生的分数,其中90分以上为优秀,计算优秀率(90分以上的人数占全部人数的比率)的公式是“=COUNTIF(D3:
D17,">=90")/COUNT(D3:
D17)”。
9、多条件计数函数COUNTIFS
功能:
统计某个单元格区域中符合多个指定条件的单元格个数。
使用格式:
COUNTIFS(Criteria_rangel,Criterial,Criteria_range2,Criteria2,…)
说明:
Criteria_range1为在其中计算关联条件的第1个区域;Criteria1为第1个计数的条件,例如条件可以是32、"32"、"苹果"、">32"、B5、SQRT(4)等。
Criteria_range2,Criteria2,…及以后的参数可省略,表示附加的区域和条件。
Criteria2与Criteria_Range2配对使用,Criteria3与Criteria_range3配对使用。
每一个附加的区域都必须与参数Criteria_range1具有相同的行数和列数,这些区域无需彼此相邻。
例如:
“COUNTIFS(A2:
A7,">80",B2:
B7,"<100")”表示统计单元格区域A2:
A7中大于80的单元格、并且对应在B2:
B7中的单元格小于100的“行数”。
10、排位函数RANK
功能:
返回某一数值在一列数值中相对于其他数值的大小排名。
使用格式:
RANK(Number,Ref,Order)
说明:
Number是需要比较的一个数值。
Ref是一组数,可以是一个单元格区域的引用。
函数的计算结果就是这个数值在这组数中的排名。
Order指定排名方式,如果Order为0或不输入的话,是降序排列;如果Order为非0值,则升序排列。
例如:
“RANK(95,A2:
A6,1)”求数值95在单元格区域A2:
A6中升序排列的第几位。
注意:
在应用本函数时,条件区域也就是括号中的第二个数值,应该要选定单元格后摁下F4!
单元格中出现$A$2:
$A$6才能应用正确!
常用的条件函数和逻辑函数
1、条件判断函数IF
功能:
根据一个条件判断条件的真假,根据真假分别返回两种不同的结果值。
使用格式:
IF(Logical,Value_if_true,Value_if_false)
说明:
Logical是一个条件;Value_if_true表示当条件为真时,IF函数的结果值;Value_if_false表示当条件为假时,IF函数的结果值。
因此,IF函数的使用格式可被理解为“如果(条件,如果真…,如果假…)”
例如:
“IF(A2>=60,"及格","不及格")”表示如果单元格A2中的值大于等于60,则返回“及格”字样,否则返回“不及格”字样。
如在单元格A3中输入“=IF(A2>=60,"及格","不及格")”,所表示的意思是根据A2中的分数判断“及格”、“不及格”,将结果存入A3。
2、逻辑函数AND和OR
功能:
当需要多个条件具有“并且”、“或者”的关系时,要用AND、OR函数表示。
使用格式:
AND(Logical1,Logical2,Logical3,…)和OR(Logical1,Logical2,Logical3,…)
说明:
这两个函数所有参数都必须是逻辑判断式(可得到TRUE或FALSE结果的判断式)或包含逻辑值的数组、单元格(区域)等。
AND函数在当所有参数都成立时,返回TRUE,否则返回FALSE。
OR函数在其中有一个参数成立时就返回TRUE,否则返回FALSE。
例如:
若D2为数学成绩,E2为语文成绩,“IF(AND(D2>=60,E2>=60),"合格","不合格")”表示只有数学、语文成绩都及格的才是“合格”,有一门科目不及格的就是“不合格”。
3、Excel的垂直查询函数VLOOKUP
功能:
搜索指定单元格区域中的第1列,然后返回该区域相同行上任意单元格中的数值。
使用格式:
VLOOKUP(Lookup_value,Table_array,Col_index_num,Range_lookup)
说明:
Lookup_value表示要在区域的第1列中搜索的数值。
Table_array为要搜索的单元格区域。
Col_index_num表示最终要返回的数据是该区域第几列的值。
例如Col_index_num为1时返回Table_array中第1列的值,Col_index_num为2时返回Table_array中第2列的值。
如果Col_index_num小于1,函数返回错误值#VALUE!
;如果Col_index_num大于区域Table_array中的总列数,函数返回错误值#REF!
。
Range_lookup参数为TRUE或FALSE,表示查找精确匹配值(TRUE,找不到精确匹配值时返回小于Lookup_value的最大值)、还是近似匹配值(FALSE,当第1列有多个匹配时仅找第一个值),Range_lookup可以省略(省略时视为TRUE)。
注意:
若Range_lookup为TRUE或被省略,则必须按升序排列Table_array第1列的值,否则函数返回值可能不正确。
如果Range_lookup为FALSE,则不需要排序。
因此在使用VLOOKUP函数时,虽然Range_lookup可以省略,但一般不要省略,而要给FALSE或0值。
例如:
“VLOOKUP,B2:
D10,3,FALSE)”表示在B2:
D10区域中查找,在该区域的第1列即B列中找,找到后返回同行第3列即D列的值。
如没找到返回错误#N/A。
常用的日期函数
1、提取日期各部分的函数YEAR、MONTH、DAY、WEEKDAY
功能:
YEAR函数提取某日期的年份部分,返回值为1900~9999之间的一个整数。
MONTH函数提取某日期的月份部分,返回值为1~12。
DAY函数提取某日期的日部分,返回值为1~31。
WEEKDAY函数提取某日期的星期几的星期数,返回值为1~7。
使用格式:
YEAR(Serial_number)、MONTH(Serial_number)、DAY(Serial_number)和WEEKDAY(Serial_number,Return_type)
说明:
Serial_number为要被提取某部分的一个日期,或含有日期的单元格引用。
WEEKDAY的Return_type参数说明星期的表示方式:
该参数为1时,星期日为1、星期六为7;该参数为2时,星期一为1、星期日为7(符合中国习惯);该参数为3时,星期一为0、星期日为6。
例如:
“YEAR("2014/12/27")”将得到2014。
当在A2单元格中输入日期“2014/12/27”时,“WEEKDAY(A2,2)”将得到6(“2014/12/27”为星期六)。
2、获得当前日期时间的函数TODAY和NOW
功能:
TODAY函数获得系统当下的日期;NOW函数获得系统当下的日期和时间。
使用格式:
TODAY()和NOW()
说明:
这两个函数都不需参数,直接写“函数名”+“()”即可。
例如:
“TODAY()”即获得当下日期;“NOW()”即获得当下日期和时间。
注意:
一定要分清楚TODAY和NOW,TODAY可以和YEAR、MONTH、DAY混合使用,例如如果需要提取出今天的年,就可以这样使用=YEAR(TODAY())
3、构造日期函数DATE
功能:
通过给出年、月、日的数值,构造出对应的一个日期数据。
使用格式:
DATE(year,month,day)
说明:
year为年份数值(小于9999);month为月份数值(可大于12);day为天数。
例如:
“DATE(2015,2,4)”将得到“2015/2/4”的日期数据。
4、计算两日期差值的函数DATEDIF
功能:
计算两个日期之间的差值。
使用格式:
DATEDIF(Date1,Date2,"y")或DATEDIF(Date1,Date2,"m")或DATEDIF(Date1,Date2,"d")
说明:
Date1表示较前的一个日期,Date2表示较后的一个日期,第3个参数为"y"、"m"或"d"时,函数分别返回这两个日期之间相差的年数、月数、天数。
例如:
“DATEDIF(A3,TODAY(),"y")”表示计算今天与A3单元格中的日期所差的整年数。
常用的文本函数
1、文本合并。
这个可以说不算函数,但是又是需要用到,比如说在排名中要写第几名,就要这样写:
="第"&RANK(95,A2:
A6,1)&"名",需要注意的是其中的符号必须均为英文。
2、中间截取字符串函数MID。
功能:
从一个文本字符串的中间截取若干个字符组成一个新的文本字符串。
使用格式:
MID(Text,Start_num,Num_chars)
说明:
Text为要被截取的文本字符串。
Start_num为要截取的第1个字符在Text中的字符位置,Text的第1个字符位置为1。
Num_chars为要从Start_num位置开始截取几个字符,Num_chars必须≥0,如果省略Num_chars参数,则默认其值为1。
例如:
若A3单元格中的内容为“abcdef”,则“MID(A3,2,3)”将得到内容“bcd”。