Excel常用函数素材表格推荐下载.xlsx
《Excel常用函数素材表格推荐下载.xlsx》由会员分享,可在线阅读,更多相关《Excel常用函数素材表格推荐下载.xlsx(24页珍藏版)》请在冰点文库上搜索。
![Excel常用函数素材表格推荐下载.xlsx](https://file1.bingdoc.com/fileroot1/2023-4/30/152b2f26-b1bb-4169-a236-790b907932ea/152b2f26-b1bb-4169-a236-790b907932ea1.gif)
(44)四四舍舍五五入入函函数数ROUND(NumberROUND(Number,Num_digits)Num_digits)主要功能:
按指定的位数Num_digits对参数Number进行四舍五入。
参数Number表示要四舍五入的数字;
参数Num_digits表示保留的小数位数。
=ROUND(227.568,2)返回结果为227.57。
(55)取取整整函函数数TRUNCTRUNC(NumberNumber,Num_digitsNum_digits)主要功能:
将参数Number的小数部分截去,返回整数;
参数Num_digits为取精度数,默认为0。
=TRUNC(227.568)返回结果为227。
=TRUNC(-227.568)返回结果为-227。
(66)向向下下取取整整函函数数INTINT(NumberNumber)主要功能:
将参数Number向下舍入到最接近的整数,Number为必需的参数。
Number表示需要取整的数值或引用的单元格。
=INT(227.568)返回结果为227。
=INT(-227.568)返回结果为-228。
练练习习栏栏求求和和函函数数SUMSUM(Number1,Number2Number1,Number2)78.3主要功能:
计算所有参数的和。
80.2参数说明:
至少包含一个参数Number1,每个参数可以是具体的数值、引用的单元格(区域)、数组、公式或另一个函数的结果。
82.5应用举例:
=SUM(A2:
A10)是将单元格A2-A10中的所有数值相加;
=SUM(A2,A10,A20)是将单元格A2、A10和A20中的数字相加。
75.6说明:
如果参数为数组或引用,只有其中的数字可以被计算,空白单元格、逻辑值、文本或错误值将被忽略。
82.270.571785.2248.876.885.784.385.683.487.676.983.175.786.981.484.882.877.277.291.482.452.2英语体育计算机50.0078.389.690.580.289.176.382.580.286.375.690.178.682.250.591.270.585.568.685.286.576.576.893.681.485.749.565.8计算机成绩总分715.2=SUM(C2:
C10)体育大于60学生的总成绩614.6=SUMIF(B2:
B10,60)体育大于60学生的计算机成绩之和558.2=SUMIF(B2:
B10,60,C2:
C10)体育大于60且计算机小于80学生的英语总分311.5=SUMIFS(A2:
A10,B2:
C10,60)表示对B2:
B10区域中大于60的数值进行相加;
=SUMIF(B2:
B10,60,C2:
C10),表示在区域B2:
B10中,查找大于60的单元格,并在C2:
C10区域中找到对应的单元格进行求和多多条条件件求求和和函函数数SUMIFSSUMIFS(Sum_Range,Criteria_Range1,Criteria1,Sum_Range,Criteria_Range1,Criteria1,Criteria_Range2,Criteria_Range2,Criteria2Criteria2)主要功能:
对指定单元格区域中符合多组条件的单元格求和。
Sum_Range必需的参数。
参加求和的实际单元格区域;
Criteria_Range1必需的参数。
第1组条件中指定的区域;
riteria1必需的参数。
第1组条件中指定的条件;
Criteria_Range2,Criteria2可选参数。
第2组条件,还可以有其他多组条件。
=SUMIFS(A2:
C10,80)表示对A2:
A10区域中符合以下条件的单元格的数值求和:
B2:
B10中的相应数值大于60且C2:
C20中的相应数值小于80。
品名正价折扣会员折扣营业额公式冰箱1029080.00%90.00%7409=B2*C2*D2电视1260075.00%90.00%8505=B3*C3*D3洗衣机724570.00%90.00%4564=B4*C4*D4营业额合计20478.15=SUMPRODUCT(B2:
B4,C2:
C4,D2:
D4)营业额合计(数组计算)20478.15=SUMPRODUCT(10290;
12600;
7245,0.8;
0.75;
0.7,0.9;
0.9;
0.9)积积和和函函数数SUMPRODUCTSUMPRODUCT(array1,array2,array3,array1,array2,array3,.)主要功能:
先计算出各个数组或区域内位置相同的元素之间的乘积,然后再计算出它们的和。
可以是数值、逻辑值或作为文本输入的数字的数组常量,或者包含这些值的单元格区域,空白单元格被视为0。
区域计算要求:
计算A、B、C三列对应数据乘积的和。
公式:
=SUMPRODUCT(B2:
D4);
计算方式:
=B2*C2*D2+B3*C3*D3+B4*C4*D4即三个区域B2:
B4,C2:
C4,D2:
D4同行数据积的和。
数组计算要求:
把区域B2:
D4数据按一个区域作为一个数组,即B2:
B4表示为数组B2;
B3;
C4表示为数组C2;
C3;
D4表示为数组D2;
D3;
D4。
=SUMPRODUCT(B2;
B4,C2;
C4,D2;
D4),其中单元格名称在计算时候要换成具体的数据。
数组参数必须具有相同的维数公式=SUMPRODUCT(B2:
D4)=SUMPRODUCT(10290;
0.9)参数说明:
英语体育计算机78.389.690.580.289.176.382.580.286.375.690.178.682.250.591.270.585.568.685.286.576.576.893.681.485.749.565.8英语平均成绩79.66667=AVERAGE(A2:
A10)英语总分及C10的平均值78.28=AVERAGE(A2:
A10,C10)平平均均值值函函数数AVERAGE(Number1,AVERAGE(Number1,Number2,)Number2,)主要功能:
求出所有参数的算术平均值。
至少包含一个参数,最多可包含255个。
=AVERAGE(A2:
A10)表示对单元格区域A2到A10中的数值求平均值;
A10,C10)表示对单元格区域A2到A10中数值与C10中的数值求平均值。
如果引用区域中包含“0”值单元格,则计算在内;
如果引用区域中包含空白或字符单元格,则不计算在内。
英语体育计算机78.389.690.580.289.176.382.580.286.375.690.178.682.250.591.270.585.568.685.286.576.576.893.681.485.749.565.8体育小于80分学生的平均成绩50体育小于80分学生的计算机平均成绩78.5体育大于60且计算机小于80学生的英语平均成绩77.875=AVERAGEIF(B2:
B10,80)=AVERAGEIF(B2:
C10,80)条条件件平平均均值值函函数数AVERAGEIF(RangeAVERAGEIF(Range,CriteriaCriteria,Average_range)Average_range)主要功能:
对指定单元格区域中符合一组条件的单元格求平均值。
Range必需的参数。
进行条件对比的单元格区域。
Criteria必需的参数。
求平均值的条件,其形式可以为数字、表达式、单元格引用、文本或函数。
Average_range可选的参数。
要求平均值的实际单元格区域。
如果Sum_Range参数被省略,Excel会对在Range参数中指定的单元格求平均值。
=AVERAGEIF(B2:
B10,80)表示对B2:
B10区域中小于80的数值求平均值;
C10,60)英语成绩大于60且体育成绩小于80的学生数目2=COUNTIFS(A2:
A10,60,B2:
B10,60)表示统计单元格区域B2-B10中值大于60的单元格的个数。
允许引用的单元格区域中有空白单元格出现。
(44)多多条条件件计计数数函函数数COUNTIFSCOUNTIFS(Criteria_range1Criteria_range1,Criteria1Criteria1,Criteria_range2Criteria_range2,Criteria2Criteria2)主要功能:
统计指定单元格区域中符合多组条件的单元格的个数。
Criteria_range1必需的参数。
第1组条件中指定的区域。
Criteria1必需的参数。
第1组条件中指定的条件,条件的形式可以为数字、表达式、单元格地址或文本。
=COUNTIFS(A2:
B10,=60,及格,不及格),表示如果C2单元格中的数值大于或等于60,则显示“及格”字样,反之显示“不及格”字样=IF(C2=90,优秀,IF(C2=80,良好,IF(C2=60,及格,不及格)表示以下对应关系:
=IF(C2=60,及格,不及格),表示如果C2单元格中的数值大于或等于60,则显示“及格”字样,反之显示“不及格”字样英语体育计算机78.389.690.58089.176.382.580.286.375.690.178.682.250.591.270.585.568.685.286.576.576.893.681.485.749.565.8查找英语成绩为80的学生的体育成绩89.1=VLOOKUP(80,A2:
C10,2,1)垂垂直直查查询询函函数数VLOOKUP(Lookup_value,Table_array,Col_index_num,Range_lookup)VLOOKUP(Lookup_value,Table_array,Col_index_num,Range_lookup)主要功能:
搜索指定单元格区域的第1列,然后返回该区域相同一行上任何指定单元格中的值。
Lookup_value必需的参数。
查找目标,即要在表格或区域的第1列中搜索到的值;
Table_array必需的参数。
查找范围,即要查找的数据所在的单元格区域;
Col_index_num必需的参数。
返回值的列数,即最终返回数据所在的列号;
Range_lookup可选参数。
为一逻辑值,决定查找精确匹配值还是近似匹配值。
如果为1(TRUE)或被省略,则返回近似匹配值;
也就是说,如果找不到精确匹配值,则返回小于Lookup_value的最大数值。
如果为0(FALSE),则只查找精确匹配值;
如果找不到精确匹配值,则返回错误值#N/A。
=VLOOKUP(80,A2:
C10,2)要查找的区域为A2:
C10,因此A列为第1列,B列为第2列,C列为第3列。
表示使用近似匹配搜索A列(第1列)中的值80,如果在A列中没有80,则近似找到A列中与80最接近的值,然后返回同一行中B列(第2列)的值。
=VLOOKUP(80,A2:
C10,2,1)英语体育计算机78.389.690.58089.176.382.580.286.375.690.178.682.250.591.270.585.568.685.286.576.576.893.681.485.749.565.86=RANK(A2,$A$1:
$A$10,0)4=RANK(A2,$A$1:
$A$10,0)排排位位函函数数RANK.EQRANK.EQ(Number,ref,Number,ref,orderorder)和和RANK.AVG(Number,ref,order)RANK.AVG(Number,ref,order)主要功能:
返回一个数值在指定数值列表中的排位;
如果多个值具有相同的排位,使用函数RANK.AVG将返回平均排位;
使用函数RANK.EQ则返回实际排位。
Number必需的参数。
表示需要排位的数值;
ref必需的参数。
表示要查找的数值列表所在的单元格区域;
order可选的参数。
指定数值列表的排序方式(如果为order为0或者忽略,则按降序排名,即数值越大,排名结果数值越小;
如果为非0值,则按升序排名,即数值越大,排名结果数值越大)。
=RANK(A2,$A$1:
$A$10,0),表示求取A2单元格中的数值在单元格区域A1:
A10中的降序排位。
$A$10,1),表示求取A2单元格中的数值在单元格区域A1:
A10中的升序排位说明:
在上述公式中,我们让Number参数采取了相对引用形式,而让ref参数采取了绝对引用形式(增加了一个“$”符号),这样设置后,选中公式所在单元格,将鼠标移至该单元格右下角,按住左键向下拖拉填充柄,即可将上述公式快速复制到该列下面的单元格中,完成其他数值的排位统计。
$A$10,0)=RANK(A2,$A$1:
$A$10,0)(11)当当前前日日期期和和时时间间函函数数NOW(NOW()主要功能:
返回当前系统日期和时间。
该函数不需要参数。
输入公式=NOW(),确认后即可显示出当前系统日期和时间。
如果系统日期和时间发生了改变,只要按一下F9功能键,即可让其随之改变。
(22)当当前前日日期期函函数数TODAYTODAY()主要功能:
返回当前系统日期。
输入公式=TODAY(),确认后即可显示出当前系统日期。
如果系统日期发生了改变,只要按一下F9功能键,即可让其随之改变。
(33)年年份份函函数数YEARYEAR(Serial_numberSerial_number)主要功能:
返回指定日期或引用单元格中对应的年份。
返回值为1900到9999之间的整数。
Serial_number必需参数。
是一个日期值,其中包含要查找的年份。
直接在单元格中输入公式=YEAR(2015/12/25),返回年份2015。
当在A2单元格中输入日期“2015/12/25”时,公式=YEAR(A2)返回年份2015。
公式所在的单元格不能是日期格式。
(44)月月份份函函数数MONTH(Serial_number)MONTH(Serial_number)主要功能:
返回指定日期或引用单元格中对应的月份。
返回值为1到12之间的整数。
是一个日期值,其中包含要查找的月份。
直接在单元格中输入公式=MONTH(2003-12-18),返回月份12。
资料计算机计算机Excel资料Excel(11)文文本本合合并并函函数数CONCATENATE(Text1CONCATENATE(Text1,Text2)Text2)主要功能:
将几个文本项合并为一个文本项。
至少有一个参数,最多有255个。
参数可以是文本、数字、单元格地址等。
如在单元格A2和A10中分别输入文本“计算机”和“Excel”,在B2单元格中输入公式=CONCATENATE(A2,A10,资料),则结果为“计算机Excel资料”。
文本连接符&
与函数CONCATENATE的功能基本相同,例如公式=CONCATENATE(A2,A10,资料)可以改为:
=CONCATENATE(A2&
A10&
资料)也能达到相同的目的。
(22)截截取取字字符符串串函函数数MID(Text,Start_num,Num_chars)MID(Text,Start_num,Num_chars)主要功能:
从文本字符串的指定位置开始,截取指定数目的字符。
Text必需参数。
代表要截取字符的文本字符串;
Start_num必需参数。
表示指定的起始位置;
Num_chars必需参数。
表示要截取的字符个数。
例如在B2单元格中有文本“计算机Excel资料”,在B1单元格中输入公式=MID(B2,9,2)=MID(B2,9,2),表示从单元格A2中的文本字符串中的第9格字符开始截取2个字符,结果为“资资料料”。
截取字符串函数MID与文本合并函数CONCATENATE经常联合使用,表示先使用MID截取字符,再使用CONCATENAT将所截取字符与原有文本连接起来。
(33)左左侧侧截截取取字字符符串串函函数数LEFTLEFT(Text,Num_chars)(Text,Num_chars)主要功能:
从文本字符串的最左边(即第一个字符)开始,截取指定数目的字符。
Num_chars可选参数。
必须大于或等于0,如果省略,默认值为1。
B2单元格中有文本“计算机Excel资料”,在B1单元格中输入公式=LEFT(B2,3)表示从单元格B2中的文本字符串中截取前四个字符,结果为“计算机”。
(44)右右侧侧截截取取字字符符串串函函数数RIGHT(Text,Num_chars)RIGHT(Text,Num_chars)主要功能:
从一个文本字符串的最右边(即最后一个字符)开始,截取指定数目的字符。
B2单元格中有文本“计算机Excel资料”,在B1单元格中输入公式=RIGHT(B2,2)表示从单元格B2中的文本字符串中截取后四个字符,结果为“资料”。
(55)删删除除空空格格函函数数TRIMTRIM(TextText)主要功能:
删除指定文本或区域中的空格参数说明:
Text表示需要删除空格的文本或区域。
TRIM函数是除了单词之间的单个空格外,清除文本中所有的空格,包括文本开头的空格和结尾的空格。
=TRIM(计算机)表示删除中文文本的前导空格、尾部空格以及字间空格(6)字符个数函数LEN(Text)主要功能:
统计并返回指定文本字符串中的字符个数参数说明:
Text为必需的参数,代表要统计其长度的文本,空格也将作为字符进行计数。
如果在A2单元格中有文本“计算机”,则在C2单元格中输入公式“=LEN(A2)”,表示统计A2单元格中的字符串长度,结果为“3”。