Excel常用函数Word下载.docx
- 文档编号:16585552
- 上传时间:2022-11-24
- 格式:DOCX
- 页数:12
- 大小:53.55KB
Excel常用函数Word下载.docx
《Excel常用函数Word下载.docx》由会员分享,可在线阅读,更多相关《Excel常用函数Word下载.docx(12页珍藏版)》请在冰豆网上搜索。
(1)计算工作表内多列中对应值相乘之后的和,即乘积之和。
其中,Array1,array2,array3,…为2到30个数组,其相应元素需要进行相乘并求和。
数组参数必须具有相同的维数,否则,函数SUMPRODUCT将返回错误值#VALUE!
。
该函数将非数值型的数组元素作为0处理。
(2)SUMPRODUCT函数乘积之和举例
A
B
C
D
1
2
10
公式:
=SUMPRODUCT(A1:
B3,C1:
D3),说明:
两个数组的所有元素对应相乘,然后把乘积相加,即1*10+2*20+3*30+4*40+5*50+6*60(结果为910)
20
3
4
30
40
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:
)*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:
)*1,(D2:
D13="
三班"
)*1,(E2:
E13="
浙"
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,
=AVERAGE(A1:
A20)
将返回这些数字的平均值。
再例如:
AVERAGE(A1,A5,B4)将单元格A1、A5、B4之和的平均数返回给应用的单元格。
AVERAGEIF(range,criteria,[average_range])
range必需。
要计算平均值的一个或多个单元格,其中包括数字或包含数字的名称、数组或引用。
criteria必需。
数字、表达式、单元格引用或文本形式的条件,用于定义要对哪些单元格计算平均值。
例如,条件可以表示为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,则返回另一个值。
例如,如果
大于
10,公式
=IF(A1>
10,"
10"
"
不大于
将返回“大于
10”,如果
小于等于
10,则返回“不大于
10”。
LEFT(text,num_chars)
Text
是包含要提取字符的文本字符串,可以直接输入含有目标文字的单元格名称。
Num_chars
指定要由
LEFT
所提取的字符数。
必须大于或等于
0。
如果
num_chars
大于文本长度,则
返回所有文本。
如果省略
num_chars,则假定其为
1。
RIGHT(text,num_chars)
RIGHTB(text,num_bytes)
指定希望
RIGHT
提取的字符数。
注意:
如果忽略
INT(number)
Number需要进行向下舍入取整的实数。
MID(text,
start_num,
num_chars)
Text是包含要提取字符的文本字符串,可以直接输入含有目标文字的单元格名称。
Start_num
是文本中要提取的第一个字符的位置。
MID
从文本中返回字符的个数。
start_num
返回空文本
()。
小于文本长度,但
加上
超过了文本的长度,则
只返回至多直到文本末尾的字符。
如果start_num小于1,则MID返回错误值#VALUE!
是负数,则
返回错误值
#VALUE!
MIDB(text,
num_bytes)
MIDB
函数语法具有下列参数
(参数:
为操作、事件、方法、属性、函数或过程提供信息的值。
):
包含要提取字符的文本字符串。
文本中要提取的第一个字符的位置。
文本中第一个字符的
为
1,依此类推。
Num_bytes
从文本中返回字符的个数(字节数)。
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
需要找到排位的数字。
Ref
数字列表数组或对数字列表的引用。
中的非数值型值将被忽略。
Order
一数字,指明数字排位的方式。
order
0(零)或省略,Microsoft
Excel
对数字的排位是基于
ref
为按照降序排列的列表。
不为零,Microsoft
为按照升序排列的列表。
RANK.EQ(number,ref,[order])
是回一列数字的数字排位。
其大小与列表中其他值相关;
如果多个值具有相同的排位,则返回该组值的最高排位。
如果要对列表进行排序,则数字排位可作为其位置。
要找到其排位的数字。
数字列表的数组,对数字列表的引用。
Ref中的非数字值会被忽略。
如果Order为0(零)或省略,Excel对数字的排位是基于Ref为按降序排列的列表。
如果Order不为零,Excel对数字的排位是基于Ref为按照升序排列的列表。
RANK.EQ赋予重复数相同的排位。
但重复数的存在将影响后续数值的排位。
例如,在按升序排序的整数列表中,如果数字10出现两次,且其排位为5,则11的排位为7(没有排位为6的数值)。
RANK.AVG(number,ref,[order])
是返回一列数字的数字排位:
数字的排位是其大小与列表中其他值的比值;
如果多个值具有相同的排位,则将返回平均排位。
一个指定数字排位方式的数字。
SQRT(number)
number
指需要计算平方根的数
MOD(number,
divisor)
MOD
被除数。
Divisor
除数。
MAX(number1,
max函数语法具有下列:
Number1,
number2,
...
Number1
是必需的,后续数值是可选的。
这些是要从中找出最大值的返回给引用的单元格。
MIN(number1,
MIN
函数语法具有下列:
这些是要从中查找最小值的值的返回给引用的单元格
COUNT(A1:
A20)
COUNT
函数计算包含数字的单元格以及参数列表中数字的个数。
使用函数
可以获取区域或数字数组中数字字段的输入项的个数。
例如,输入以下公式可以计算区域
A20
中数字的个数:
COUNTA(value1,
[value2],
COUNTA
函数计算表中不为空的单元格的个数。
COUNTIF(range,
criteria)
例如:
=COUNTIF(B2:
Nancy"
COUNTIFS(criteria_range1,
criteria1,
[criteria_range2,
criteria2]…)
COUNTIFS
criteria_range1
在其中计算关联条件的第一个区域。
criteria1
条件的形式为数字、表达式、单元格引用或文本,可用来定义将对哪些单元格进行计数。
、B4、"
"
criteria_range2,
criteria2,
附加的区域及其关联条件。
最多允许
127
个区域/条件对。
每一个附加的区域都必须与参数
具有相同的行数和列数。
这些区域无需彼此相邻。
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/通用格式
常规格式
“000.0”
10.25
“010.3”
小数点前面不够三位以0补齐,保留1位小数,不足一位以0补齐
####
10.00
没用的0一律不显示
00.##
1.253
01.25
小数点前不足两位以0补齐,保留两位,不足两位不补位
正数;
负数;
零
正数
大于0,显示为“正数”
等于0,显示为“零”
-1
负数
小于0,显示为“负数”
0000-00-00
19820506
1982-05-06
按所示形式表示日期
0000年00月00日
1982年05月06日
dddd
2007-12-31
Monday
显示为英文星期几全称
[>
=90]优秀;
=60]及格;
不及格
90
优秀
大于等于90,显示为“优秀”
及格
大于等于60,小于90,显示为“及格”
59
小于60,显示为“不及格”
[DBNum1][$-804]G/通用格式
125
一百二十五
中文小写数字
[DBNum2][$-804]G/通用格式
元整125
壹佰贰拾伍元整
中文大写数字,并加入“元整”字尾
[DBNum3][$-804]G/通用格式
1百2十5
19
一十九
中文小写数字,11-19无设置
20][DBNum1];
[DBNum1]d
十九
中文小写数字,11-显示为十一而不是一十一
0.00,K
12536
12.54K
以千为单位
#!
.0000万元
1.2536万元
以万元为单位,保留4位小数
1.3万元
以万元为单位,保留1位小数
TIME(hour,
minute,
second)
此函数的参数和英文意思一致
WEEKDAY(serial_number,return_type)
WEEKDAY用途:
返回某日期的星期数。
serial_number是要返回日期数的日期,它有多种输入方式:
带引号的文本串(如2001/02/26)、序列号(如35825表示1998年1
月30日)或其他公式或函数的结果(如DATEVALUE(2000/1/30))。
return_type为确定返回值类型的数字,数字1或省略则1至7代表星期天到星期六,数字2则1至7代表星期一到星期天,数字3则0至6代表星期一到星期天。
在默认情况下,它的值为1(星期天)到7(星期六)之间的一个整数。
结合例子:
我们在A1单元格中输入一个日期:
2011年10月15日。
通过之前的WEEKDAY函数的方法就是=WEEKDAY(A1,2)就可以得出结果为6,表示今天是星期六。
不过文字方面不能满足我们的需求,我们需要显示出“星期六”。
可以写成如下:
=TEXT(WEEKDAY(A1),“aaaa”)
如果想将中文直接转换成英文,可以将第二个参数改为dddd,如下:
=TEXT(WEEKDAY(A1),“dddd”),
aaaa和dddd需要加英文引号“”。
=CHOOSE(WEEKDAY(2011-1-15,2),星期一,星期二,星期三,星期四,星期五,星期六,星期日)
返回值为星期六。
WEEKNUM(serial_num,return_type)
Seria_num代表要确定它位于一年中的几周的特定日期
Return_type为一数字,它确定星期计算从哪一天开始,其默认值为1。
TODAY()
返回当前年月日
,TODAY
函数语法没有参数。
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Excel 常用 函数