Excel常用函数.docx
《Excel常用函数.docx》由会员分享,可在线阅读,更多相关《Excel常用函数.docx(12页珍藏版)》请在冰豆网上搜索。
Excel常用函数
Excel2010 常用函数
SUM(number1,[number2],...])
例如,SUM(A1:
A5) 将单元格 A1 至 A5 中的所有数字相加,再如,SUM(A1, A3, A5) 将单元格 A1、A3 和 A5 中的数字相加。
SUMIF(range, criteria, [sum_range])
例如,假设在含有数字的某一列中,需要让大于 5 的数值相加,请使用以下公式:
=SUMIF(B2:
B25,">5")
SUMIF 函数语法具有以下参数:
range 必需。
用于条件计算的单元格区域。
每个区域中的单元格都必须是数字或名称、数组或包含数字的引用。
空值和文本值将被忽略。
criteria 必需。
用于确定对哪些单元格求和的条件,其形式可以为数字、表达式、单元格引用、文本或函数。
例如,条件可以表示为 32、">32"、B5、32、"32"、"苹果" 或 TODAY()。
要点 任何文本条件或任何含有逻辑或数学符号的条件都必须使用双引号 (") 括起来。
如果条件为数字,那么无需使用双引号。
sum_range 可选。
要求和的实际单元格〔如果要对未在 range 参数中指定的单元格求和〕。
如果 sum_range 参数被省略,Excel 会对在 range 参数中指定的单元格〔即应用条件的单元格〕求和。
SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...) 对区域中满足多个条件的单元格求和。
例如,如果需要对区域 A1:
A20 中符合以下条件的单元格的数值求和:
B1:
B20 中的相应数值大于零 (0) 且 C1:
C20 中的相应数值小于 10,那么可以使用以下公式:
=SUMIFS(A1:
A20, B1:
B20, ">0", C1:
C20, "<10")
SUMIFS 函数语法具有以下参数 〔参数:
为操作、事件、方法、属性、函数或过程提供信息的值。
〕:
sum_range 必需。
对一个或多个单元格求和,包括数字或包含数字的名称、区域或单元格引用 〔单元格引用:
用于表示单元格在工作表上所处位置的坐标集。
例如,显示在第 B 列和第 3 行穿插处的单元格,其引用形式为“B3〞。
〕。
忽略空白和文本值。
criteria_range1 必需。
在其中计算关联条件的第一个区域。
criteria1 必需。
条件的形式为数字、表达式、单元格引用或文本,可用来定义将对 criteria_range1 参数中的哪些单元格求和。
例如,条件可以表示为 32、">32"、B4、"苹果" 或 "32"。
criteria_range2, criteria2, … 可选。
附加的区域与其关联条件。
最多允许 127 个区域/条件对。
SUMPRODUCT(array1, [array2], [array3], ...)
〔1〕计算工作表多列中对应值相乘之后的和,即乘积之和。
其中,Array1,array2,array3,…为2到30个数组,其相应元素需要进展相乘并求和。
数组参数必须具有一样的维数,否那么,函数SUMPRODUCT将返回错误值#VALUE!
。
该函数将非数值型的数组元素作为0处理。
〔2〕SUMPRODUCT函数乘积之和举例
A
B
C
D
1
1
2
10
公式:
=SUMPRODUCT(A1:
B3,C1:
D3),说明:
两个数组的所有元素对应相乘,然后把乘积相加,即1*10+2*20+3*30+4*40+5*50+6*60(结果为910)
20
2
3
4
30
40
3
5
6
50
60
〔3〕多条件求和+求个数
A、使用SUMPRODUCT进展多条件计数 ,计数就是统计满足条件的个数。
语法:
=SUMPRODUCT((条件1)*(条件2)*(条件3)*…(条件n))
作用:
统计同时满足条件1、条件2到条件n的记录的个数。
实例:
=SUMPRODUCT((A2:
A10="男")*(B2:
B10="中级职称")),
公式解释:
统计性别为男且职称为中级职称的职工的人数
B、使用SUMPRODUCT进展多条件求和
语法:
=SUMPRODUCT((条件1)*(条件2)*(条件3)*…(条件n)*某区域)
作用:
汇总同时满足条件1、条件2到条件n的记录指定区域的汇总金额。
实例:
=SUMPRODUCT((A2:
A10="男")*(B2:
B10="中级职称")*C2:
C10)
公式解释:
统计性别为男性且职称为中级职称的职工的工资总和〔假设C列为工资〕
SUMPRODUCT(条件1*条件2*条件3...条件N)利用此函数进展多条件计数时,*:
满足所有条件;
SUMPRODUCT(条件1+条件2+条件3...+条件N)+:
满足任一条件
例子一:
=SUMPRODUCT((G1:
G3="男")*(E1:
E3<=60))
这个公式的意思就是要这样来统计,G1:
G3满足条件是男的,同时E1:
E3的数值小于等于60,满足这两个条件的总人数。
首先这是一个数组公式,要按Ctrl+Shift+Enter完毕。
例子二:
多条件求和
求男性与格人数:
=SUMPRODUCT((C2:
C13>=60)*1,(B2:
B13="男")*1)
例子三:
多条件求和
汇总三班籍贯为浙男性人数,=SUMPRODUCT((B2:
B13="男")*1,(D2:
D13="三班")*1,(E2:
E13="浙")*1)
PRODUCT(number1, [number2], ...)
PRODUCT 函数使所有以参数形式给出的数字相乘并返回乘积。
例如,如果单元格A1和A2中包含数字,那么可以使用公式 =PRODUCT(A1,A2) 将这两个数字相乘。
您也可以通过使用乘(*)数学运算符〔例如 =A1*A2〕执行一样的操作。
当需要使很多单元格相乘时,PRODUCT 函数很有用。
例如,
公式 =PRODUCT(A1:
A3,C1:
C3) 等价于 =A1*A2*A3*C1*C2*C3
number1 必需。
要相乘的第一个数字或围。
number2,... 可选。
要相乘的其他数字或单元格区域,最多可以使用255个参数。
注意 如果参数是一个数组或引用,那么只使用其中的数字相乘。
数组或引用中的空白单元格、逻辑值和文本将被忽略。
CHOOSE(index_num,value1,[value2],...)
使用index_num返回数值参数列表中的数值。
使用CHOOSE可以根据索引号从最多254个数值中选择一个。
例如,如果value1到value7表示一周的7天,那么将1到7之间的数字用作index_num时,CHOOSE将返回其中的某一天。
index_num 必需。
用于指定所选定的数值参数。
index_num必须是介于1到254之间的数字,或是包含1到254之间的数字的公式或单元格引用。
value1,value2,... Value1是必需的,后续值是可选的。
1到254个数值参数,CHOOSE将根据index_num从中选择一个数值或一项要执行的操作。
参数可以是数字、单元格引用、定义的名称、公式、函数或文本。
如:
=CHOOSE(2,A2,A3,A4,A5)是第二个列表参数的值〔单元格A3中的值〕
AVERAGE(number1, [number2], ...)
公式 =AVERAGE(A1:
A20) 将返回这些数字的平均值。
再例如:
AVERAGE(A1,A5,B4)将单元格A1、A5、B4之和的平均数返回给应用的单元格。
AVERAGEIF(range,criteria,[average_range])
range必需。
要计算平均值的一个或多个单元格,其中包括数字或包含数字的名称、数组或引用。
criteria必需。
数字、表达式、单元格引用或文本形式的条件,用于定义要对哪些单元格计算平均值。
例如,条件可以表示为32、"32"、">32"、"苹果"或B4。
average_range可选。
要计算平均值的实际单元格集。
如果忽略,那么使用range。
IF(logical_test, [value_if_true], [value_if_false])
Logical_test表示计算结果为TRUE或FALSE的任意值或表达式。
Value_if_truelogical_test为TRUE时返回的值。
Value_if_falselogical_test为FALSE时返回的值。
如果指定条件的计算结果为 TRUE,IF 函数将返回某个值;如果该条件的计算结果为 FALSE,那么返回另一个值。
例如,如果 A1 大于 10,公式 =IF(A1>10,"大于 10","不大于 10") 将返回“大于 10〞,如果 A1 小于等于 10,那么返回“不大于 10〞。
LEFT(text,num_chars)
Text 是包含要提取字符的文本字符串,可以直接输入含有目标文字的单元格名称。
Num_chars 指定要由 LEFT 所提取的字符数。
Num_chars 必须大于或等于 0。
如果 num_chars 大于文本长度,那么 LEFT 返回所有文本。
如果省略 num_chars,那么假定其为 1。
RIGHT(text,num_chars)
RIGHTB(text,num_bytes)
Text 是包含要提取字符的文本字符串,可以直接输入含有目标文字的单元格名称。
Num_chars 指定希望 RIGHT 提取的字符数。
注意:
Num_chars 必须大于或等于 0。
如果 num_chars 大于文本长度,那么 RIGHT 返回所有文本。
如果忽略 num_chars,那么假定其为 1
INT(number)
Number需要进展向下舍入取整的实数。
MID(text, start_num, num_chars)
Text是包含要提取字符的文本字符串,可以直接输入含有目标文字的单元格名称。
Start_num 是文本中要提取的第一个字符的位置。
Num_chars 指定希望 MID 从文本中返回字符的个数。
如果 start_num 大于文本长度,那么 MID 返回空文本 ()。
如果 start_num 小于文本长度,但 start_num 加上 num_chars 超过了文本的长度,那么 MID 只返回至多直到文本末尾的字符。
如果start_num小于1,那么MID返回错误值#VALUE!
。
如果 num_chars 是负数,那么 MID 返回错误值 #VALUE!
MIDB(text, start_num, num_bytes)
MID 和 MIDB 函数语法具有以下参数 〔参数:
为操作、事件、方法、属性、函数或过程提供信息的值。
〕:
Text 必需。
包含要提取字符的文本字符串。
Start_num 必需。
文本中要提取的第一个字符的位置。
文本中第一个字符的 start_num 为 1,依此类推。
Num_chars 必需。
指定希望 MID 从文本中返回字符的个数。
Num_bytes 必需。
指定希望 MIDB 从文本中返回字符的个数〔字节数〕。
INDEX(array, row_num, [column_num])
返回表格或数组中的元素值,此元素由行号和列号的索引值给定。
MATCH(lookup_value, lookup_array, [match_type])
返回行号。
可在单元格区域 〔区域:
工作表上的两个或多个单元格。
区域中的单元格可以相邻或不相邻〕中搜索指定项,然后返回该项在单元格区域中的相对位置。
例如,如果单元格区域 A1:
A3 包含值 5、25 和 38,那么以下公式:
=MATCH(25,A1:
A3,0)会返回数字 2,因为值 25 是单元格区域中的第二项。
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
是用于在一组数组里查找某一个数据。
然后返回所对应需要返回的参数。
1、Lookup_value是需要查找的参数.
2、table_array是需要查找参数的查找区域。
3、col_index_num是lookup_value在table_arry查找到过后返回的对应的参数列。
4、 range_lookup是模糊匹配TRUE〔或不填或填1那么模糊匹配〕/FALSE或填0那么准确匹配。
VLOOKUP函数的第三个参数是查找返回值所在的列数,如果我们需要查找返回多列时,这个列数值需要一个个的更改,比方返回第2列的,参数设置为2,如果需要返回第3列的,就需要把值改为3。
。
。
如果有十几列会很麻烦的。
那么能不能让第3个参数自动变呢?
向后复制时自动变为2,3,4,5。
。
。
在EXCEL中有一个函数COLUMN,它可以返回指定单元格的列数,比方
=COLUMNS〔A1〕返回值1
=COLUMNS〔B1〕返回值2
而单元格引用复制时会自动发生变化,即A1随公式向右复制时会变成B1,C1,D1。
。
这样我们用COLUMN函数就可以转换成数字1,2,3,4。
。
。
HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)
LOOKUP函数是Excel等电子表格中的横向查找函数,它与LOOKUP函数和VLOOKUP函数属于一类函数,
HLOOKUP是按行查找的,VLOOKUP是按列查找的。
POWER(number, power)
返回数字乘幂的结果。
POWER 函数语法具有以下参数:
Number 必需。
底数,可以为任意实数。
Power 必需。
指数,底数按该指数次幂乘方。
可以使用“^〞代替POWER,以表示基数乘幂运算的幂,例如5^2。
RANK(number,ref,[order])
是返回一列数字的数字排位。
数字的排位是其相对于列表中其他值的大小。
〔如果要对列表进展排序,那么数字排位可作为其位置。
RANK 函数语法具有以下参数 〔参数:
为操作、事件、方法、属性、函数或过程提供信息的值。
〕:
Number 必需。
需要找到排位的数字。
Ref 必需。
数字列表数组或对数字列表的引用。
Ref 中的非数值型值将被忽略。
Order 可选。
一数字,指明数字排位的方式。
如果 order 为 0〔零〕或省略,Microsoft Excel 对数字的排位是基于 ref 为按照降序排列的列表。
如果 order 不为零,Microsoft Excel 对数字的排位是基于 ref 为按照升序排列的列表。
RANK.EQ(number,ref,[order])
是回一列数字的数字排位。
其大小与列表中其他值相关;如果多个值具有一样的排位,那么返回该组值的最高排位。
如果要对列表进展排序,那么数字排位可作为其位置。
Number 必需。
要找到其排位的数字。
Ref 必需。
数字列表的数组,对数字列表的引用。
Ref中的非数字值会被忽略。
如果Order为0〔零〕或省略,Excel对数字的排位是基于Ref为按降序排列的列表。
如果Order不为零,Excel对数字的排位是基于Ref为按照升序排列的列表。
RANK.EQ赋予重复数一样的排位。
但重复数的存在将影响后续数值的排位。
例如,在按升序排序的整数列表中,如果数字10出现两次,且其排位为5,那么11的排位为7〔没有排位为6的数值〕。
RANK.AVG(number,ref,[order])
是返回一列数字的数字排位:
数字的排位是其大小与列表中其他值的比值;如果多个值具有一样的排位,那么将返回平均排位。
Number 必需。
要找到其排位的数字。
Ref 必需。
数字列表的数组,对数字列表的引用。
Ref中的非数字值会被忽略。
Order 可选。
一个指定数字排位方式的数字。
SQRT(number) number
指需要计算平方根的数
MOD(number, divisor)
MOD 函数语法具有以下参数:
Number 必需。
被除数。
Divisor 必需。
除数。
MAX(number1, [number2], ...)
max函数语法具有以下:
Number1, number2, ... Number1 是必需的,后续数值是可选的。
这些是要从中找出最大值的返回给引用的单元格。
MIN(number1, [number2], ...)
MIN 函数语法具有以下:
Number1, number2, ... Number1 是必需的,后续数值是可选的。
这些是要从中查找最小值的值的返回给引用的单元格
COUNT(A1:
A20)
COUNT 函数计算包含数字的单元格以与参数列表中数字的个数。
使用函数 COUNT 可以获取区域或数字数组中数字字段的输入项的个数。
例如,输入以下公式可以计算区域 A1:
A20 中数字的个数:
COUNTA(value1, [value2], ...) COUNTA 函数计算表中不为空的单元格的个数。
COUNTIF(range, criteria)
例如:
=COUNTIF(B2:
B25,"Nancy")
COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)
COUNTIFS 函数语法具有以下参数:
criteria_range1 必需。
在其中计算关联条件的第一个区域。
criteria1 必需。
条件的形式为数字、表达式、单元格引用或文本,可用来定义将对哪些单元格进展计数。
例如,条件可以表示为 32、">32"、B4、"苹果" 或 "32"。
criteria_range2, criteria2, ... 可选。
附加的区域与其关联条件。
最多允许 127 个区域/条件对。
要点 每一个附加的区域都必须与参数 criteria_range1 具有一样的行数和列数。
这些区域无需彼此相邻。
OFFSET(reference,rows,cols,height,width)
Offset函数主要应用在单元格区域的定位和统计方面,一般做数据透视表定义名称都需要用到Offset函数。
Offset函数属于查找与引用类的函数。
OFFSET函数以指定的引用为参照系,通过给定偏移量得到新的引用。
返回的引用可以为一个单元格或单元格区域,并可以指定返回的行数或列数。
OFFSET函数的语法是:
OFFSET(reference,rows,cols,height,width),按照中文的说法即是:
OFFSET(引用区域,行数,列数,[高度],[宽度])
Reference作为偏移量参照系的引用区域。
Reference必须为对单元格或相连单元格区域的引用;否那么,函数OFFSET返回错误值#VALUE!
。
Rows相对于偏移量参照系的左上角单元格,上〔下〕偏移的行数。
如果使用5作为参数Rows,那么说明目标引用区域的左上角单元格比reference低5行。
行数可为正数〔代表在起始引用的下方〕或负数〔代表在起始引用的上方〕。
Cols相对于偏移量参照系的左上角单元格,左〔右〕偏移的列数。
如果使用5作为参数 Cols,那么说明目标引用区域的左上角的单元格比reference靠右5列。
列数可为正数〔代表在起始引用的右边〕或负数〔代表在起始引用的左边〕。
Height高度,即所要返回的引用区域的行数。
Height必须为正数。
Width宽度,即所要返回的引用区域的列数。
Width必须为正数。
COLUMN(reference)
reference为需要得到其列标的单元格或单元格区域。
含义是返回一引用的列号。
ROW(reference)
reference为需要得到其行号的单元格或单元格区域。
含义是返回一个引用的行号。
TEXT(value,format_text)
使用函数TEXT可以将数值转换为带格式的文本,而其结果将不再作为数字参与计算。
Value为数值、计算结果为数字值的公式,或对包含数字值的单元格的引用。
Format_text为"单元格格式"对话框中"数字"选项卡上"分类"框中的文本形式的数字格式。
Format_text不能包含星号(*)。
通过"格式"菜单调用"单元格"命令,然后在"数字"选项卡上设置单元格的格式,只会更改单元格的格式而不会影响其中的数值。
Format_text参数代码(常用)
格式代码
数字
显示的值TEXT(A,B)
说明
G/通用格式
10
10
常规格式
“000.0〞
10.25
“010.3〞
小数点前面不够三位以0补齐,保存1位小数,缺乏一位以0补齐
####
10.00
10
没用的0一律不显示
00.##
1.253
01.25
小数点前缺乏两位以0补齐,保存两位,缺乏两位不补位
正数;负数;零
1
正数
大于0,显示为“正数〞
正数;负数;零
0
零
等于0,显示为“零〞
正数;负数;零
-1
负数
小于0,显示为“负数〞
0000-00-00
19820506
1982-05-06
按所示形式表示日期
0000年00月00日
19820506
1982年05月06日
dddd
2007-12-31
Monday
显示为英文星期几全称
[>=90]优秀;[>=60]与格;不与格
90
优秀
大于等于90,显示为“优秀〞
[>=90]优秀;[>=60]与格;不与格
60
与格
大于等于60,小于90,显示为“与格〞
[>=90]优秀;[>=60]与格;不与格
59
不与格
小于60,显示为“不与格〞
[DBNum1][$-804]G/通用格式
125
一百二十五
中文小写数字
[DBNum2][$-804]G/通用格式
元整125
壹佰贰拾伍元整
中文大写数字,并参加“元整〞字尾
[DBNum3][$-804]G/通用格式
125
1百2十5
中文小写数字
[DBNum1][$-804]G/通用格式
19
一十九
中文小写数字,11-19无设置
[>20][DBNum1];[DBNum1]d
19
十九
中文小写数字,11-显示为十一而不是一十一
0.00,K
12536
12.54K
以千为单位
#!
.0000万元
12536
1.2536万元
以万元为单位,保存4位小数
以万元为单位,保存4位小数
12536
1.3万元
以万元为单位,保存1位小数
TIME(hour, minute, second)
此函数的参数和英文意思一致
WEEKDAY(serial_number,retu