Excel常用函数.docx
《Excel常用函数.docx》由会员分享，可在线阅读，更多相关《Excel常用函数.docx（13页珍藏版）》请在冰豆网上搜索。
Excel常用函数
Excel工作表函数与公式
一、数学函数2
1.SUM函数2
2.常用数值舍入函数2
3.SUMIF函数2
4.MOD函数2
5.SUMPRODUCT函数3
6.SUBTOTAL函数3
7.RAND函数3
二、查询和引用函数4
1.LOOKUP函数4
2.INDEX函数5
3.MATCH函数5
4.ROW、COLUMN函数5
5.OFFSET函数6
三、文本函数6
1.LEFT函数6
2.FIND函数6
3.LEN函数6
4.RIGHT函数7
5.MID函数7
6.TEXT函数7
四、统计函数7
1.COUNTIF函数7
2.MAX函数7
3.RANK函数8
五、逻辑函数8
1.IF函数8
2.AND函数8
六、财务函数8
1.SLN函数8
2.DB函数8
3.VDB函数8
七、日期和时间函数8
1.DATEDIF函数8
2.NOW函数9
3.DATE函数9
4.EOMONTH函数9
八、信息函数9
1.IS类函数9
一、数学函数
1.SUM函数
SUM（数值1，数值2,数值n）
求和，返回某一单元格区域所有数字之和。
（Alt++回车或求和快捷键）
2.常用数值舍入函数
1）按指定小数位数计算
ROUND函数用于返回指定小数位数的四舍五入运算。
ROUNDUP函数远离零值，向上舍入数字
ROUNDWN函数远离零值，向下（绝对值减小的方向）舍入数字
ROUND（数值，小数位）
例：
=ROUND（21.5，-1）四舍五入到小数点左侧一位结果20
=ROUND（-1.475,2）将-1.475四舍五入到两位小数结果-1.48
2）按指定基数倍数舍入
CEILING函数向上舍入
FLOOR函数向下舍去
FLOOR（数字，舍入基数）括号内参数符号要相同
3）数值取整
INT函数INT（数字）将数字向下舍入到最接近的整数。
TRUNC函数与ROUNDWN类似
3.SUMIF函数
SUMIF（区域，条件，求和区域）
按给定条件对指定单元格求和。
1）条件中可以使用通配符、问号（？
）、星号（*）。
问号匹配任意
单个字符，星号匹配任意一串字符。
如果要查找实际的问号或星号，
请在该字符前键入波形符（~）。
）当省略了SUMIF，则结果为区域中符合条件的值之和。
4.MOD函数
返回两数相除的余数。
结果的正负号与除数相同。
MOD（被除数，除数）
示例
公式
说明
结果
1
=MOD（3,2）
3/2的余数
1
2
=MOD（-3,2）
-3/2的余数，符号与除数相同
1
3
=MOD（3,-2）
3/-2的余数，符号与除数相同
-1
4
=MOD（-3,-2）
-3/-2的余数，符号与除数相同
-1
5.SUMPRODUCT函数
在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。
SUMPRODUCT（数组1，数组2，……）
1）乘积求和
2）条件计数二班数学出现次数
=SUMPRODUCT（（A2:
A10=”二班”）*（B2:
B10=”数学”））
3）条件求和二班数学均分
=SUMPRODUCT（（A2:
A10="二班"）*（B2:
B10="数学"）*（C2:
C10））
数学均分之和=SUMPRODUCT（（B2:
B10="数学"）*（C2:
C10））
6.SUBTOTAL函数
返回列表或数据库中的分类汇总。
SUBTOTAL（函数序号，引用1，…）A124A23A36
例：
=SUBTOTAL（9，A1:
A3）对A列使用SUM函数计算出的分类汇总结果：
33
=SUBTOTAL（1，A1:
A3）对A列使用AVERAGE函数计算出的分类汇总结果：
11
7.RAND函数
返回大于等于0及小于1的均匀分布随机实数，每次计算工作表时
都将返回一个新的随机实数。
RAND（）
若要生成a与b之间的随机实数，使用：
RAND（）*（b-a）+a
示例
公式
说明
结果
1
=RAND（）
介于0和1之间的一个随机数
变量
2
=RAND（）*100
大于等于0但小于100的一个随机数
变量
二、查询和引用函数
1.LOOKUP函数
从单行或单列区域或者从一个数组返回值。
具有两种语法形式：
向量形式和数组形式。
LOOKUP（查找值，查找向量，返回向量）
（1）求A列最后一个文本
=LOOKUP（“座”，A:
A）
（2）求A列最后一个数字，可输入以下公式：
=LOOKUP（9E+307,A:
A）
9E+307是接近于EXCEL中能处理的最大数，可以认为是EXCEL中最大正数。
LOOKUP在搜索不到搜索值时，会搜索区域中最后一个小于搜索值的数据。
（3）求A列最后一个不为空单元格的内容，可以输入以下公式：
=LOOKUP（2,1/（A:
A<>””）,A:
A）
（4）逆向查询
=LOOKUP（1,0/（（条件1）*（条件2）*（条件N））,目标区域或数组）
注意事项：
1）Lookup函数的使用要求查询条件按照升序排列，所
以使用该函数前需要对表格进行排序处理，2）查询的条件可以高于
查询条件列的最大值，但不能低于查询条件列的最小值。
VLOOKUP函数
在表格数组的首列查找指定的值，并由此返回表格数组当前行
中指定列处的数值。
VLOOKUP（查找值，数据表，序列数，匹配条件）
匹配条件为TRUE
（1）或省略，则返回精确匹配值或近似匹配值，
为FALSE（0）则只寻找精确匹配值。
HLOOKUP函数
2.INDEX函数
返回表格或数组中的元素值，此元素由行序号和列序号共同决定。
数组形式INDEX（数组，行序数，列序数）
行列序数不为0时，返回交叉处的单元格的值。
行（列）序数为0，返回整行（列）的数组数值；行（列）以水平（垂直）单
元格区域的形式输入；按键输入数组公式。
公式
说明
结果
=INDEX（{1,2；3,4}，0,2）
返回数组常量中第一行、第二列的值
并且返回数组常量中第二行、第二列的值
2
4
引用形式INDEX（参考区域，行序数，列序数，区域序数）
3.MATCH函数
返回在指定方式下与指定数值匹配的数组中元素的相应位置。
MATCH（查找值，查找区域，匹配类型）
匹配类型为1或省略，查找小于或等于查找值的最大数值，查找区
域按升序排列：
-2，-1,0,1,2，A-Z、FALSE、TRUE；为0时，查找
等于查找值的第一个数值，按任何顺序排列；为-1时，查找大于或
等于查找值查找值的最小数值，按降序排列。
示例
公式
说明
结果
1
=MATCH（35,B2:
B5,1）
此处无正确的匹配，返回B2:
B5中最接近下一个值的位置
1
2
=MATCH（98,B2:
B5,0）
数据区域B2：
B5中98的位置
3
3
=MATCH（40,B2:
B5,-1）
由于数据区域B2:
B5不是按降序排列，所以返回错误值
#N/A
4.ROW、COLUMN函数
1）ROW函数ROW（参照区域）返回引用的行号。
COLUMN函数COLUMN（参照区域）返回给定引用的列标。
ROWS函数ROWS（数组）返回引用或数组的行数。
2）所组成的数字序列
=INT（（ROW（）-1）/2+1）
返回的结果是1、1、2、2、3、3……修改公式中的2为3，
序列会变为1、1、1、2、2、2、3、3、3……
=MOD（ROW（）,3）返回值1、2、0……
=MOD（ROW（）-1,3）返回值0、1、2……
=MOD（ROW（）-2,3）返回值2、0、1……
=MOD（ROW（）-3,3）返回值1、2、0……第一二行时不满足？
？
=MOD（ROW（）,3）-1返回值0、1、-1……
=MOD（ROW（）,3）-2返回值-1、0、-2……
=MOD（ROW（）,3）-3返回值-2、-1、-3……
=MOD（ROW（）-1,3）+1返回值1、2、3……
=MOD（ROW（）-1,3）+2返回值2、3、4……
=MOD（ROW（）-1,3）+3返回值3、4、5……
5.OFFSET函数
以指定的引用为参照系，通过给定偏移量得到新的引用。
返回的引
用可以为一个单元格或单元格区域，并可以指定返回的行数或列数。
OFFSET（参照区域，行数，列数，高度，宽度）
三、文本函数
1.LEFT函数
根据所指定的字符数，返回文本字符串中第一个字符或前几个字符。
LEFT（字符串，字符个数）
2.FIND函数
用于在第二个文本串中定位第一个文本串，并返回第一个文本串的
起始位置的值，该值从第二个文本串的第一个字符算起。
FIND（要查找的字符串，被查找的字符串，开始位置）
例：
A2MichaelJordan
示例
公式
说明
结果
1
=FIND（“M”,A2）
A2字符串中第一个“M”的位置
1
2
=FIND（“a”,A2）
A2字符串中第一个“a”的位置
5
3
=FIND（“a”,A2,6）
A2字符串中从第6个字符开始查找第一个“a”位置
13
FINDB函数
3.LEN函数
返回文本字符串中的字符个数。
LEN（字符串）
例：
A2Influentialmovie=LEN（A2）结果：
17
4.RIGHT函数
根据所指定的字符数返回文本字符串中最后一个或多个字符。
RIGHTB（字符串，字节个数）
5.MID函数
返回文本字符串中从指定位置开始的特定数目的字符。
MID（字符串，开始位置，返回字符的个数）
示例
公式
说明
结果
1
=MID（A2,1,5）
A2单元格字符串的5个字符，从第一个字符开始
Beaut
2
=MID（A2,7,20）
A2单元格字符串的20个字符，从第7个字符开始
fulflowers
3
=MID（A2,20,5）
因为要提取的第一个字符的位置大于A2单元格字符串的长度返回空文本
6.TEXT函数
将数值转换为按指定数字格式表示的文本。
TEXT（值，数值格式）
示例
公式
说明
结果
1
=A1&”占总GDP比重”&TEXT（B1,”0%”）
将A1和B1单元格里的内容以及引号间的内容，合并为一句
工业占总GDP比重45%
2
=A2&”的GDP为”&TEXT（B2,””）
将A2和B2单元格里的内容以及引号间的内容，合并为一句
服务业的GDP为1000亿
四、统计函数
1.COUNTIF函数
计算区域中满足给定条件的单元格的个数。
COUNTIF（区域，条件）
COUNTA函数：
返回参数列表中非空值的单元格个数。
COUNT函数：
返回包含数字的单元格的个数。
2.MAX函数
返回一组值中的最大值MAX（数字2,数字2，…）
MIN函数：
最小值
3.RANK函数
返回一个数字在数字列表中的排位。
RANK（数值，引用，排位方式）
排位方式为0，降序排列；不为0，升序排列。
五、逻辑函数
1.I
升级会员 