Excel常用函数.docx
- 文档编号:7791824
- 上传时间:2023-01-26
- 格式:DOCX
- 页数:9
- 大小:20.89KB
Excel常用函数.docx
《Excel常用函数.docx》由会员分享,可在线阅读,更多相关《Excel常用函数.docx(9页珍藏版)》请在冰豆网上搜索。
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为需要得到其行号的单元格或单元格区域。
含义是返回一个引用的行号。
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Excel 常用 函数
![提示](https://static.bdocx.com/images/bang_tan.gif)