Excel常用函数.docx
《Excel常用函数.docx》由会员分享，可在线阅读，更多相关《Excel常用函数.docx（13页珍藏版）》请在冰点文库上搜索。
Excel常用函数
Excel2010常用函数
D
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）多条件求和+求个数
Ａ、使用SUMPRODUCT进行多条件计数 ，计数就是统计满足条件的个数。
语法：
＝SUMPRODUCT（（条件1）*（条件2）*（条件3）*…（条件n））
作用：
统计同时满足条件1、条件2到条件n的记录的个数。
实例：
=SUMPRODUCT（（A2:
A10="男"）*（B2:
B10="中级职称"）），
公式解释：
统计性别为男且职称为中级职称的职工的人数
Ｂ、使用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
以千为单位
升级会员 