excel表格常用函数详解

excel工作表常用函数详解

(注:查看函数运算过程，点击表格中工具—公式审核—公式求值)

数学和三角函数

ABS(number) 返回数字的绝对值 单元格A1输入:=ABS(-2) 显示结果:2

INT(number) 将数字向下舍入(取小)到最接近的整数 单元格A1输入:=INT(8.9) 显示结果:8 单元格A1输入:=INT(-8.9) 显示结果:-9 MOD(number,divisor)返回被除后的余数 单元格A1输入:=MOD(3, 2) 显示结果:1 单元格A1输入:=MOD(3, -2) 显示结果:-1 RAND 返回 0 和 1 之间的一个随机数

单元格A1输入:=RAND() 显示结果: 大于等于0及小于1之间的一个随机数 RANDBETWEEN 返回指定数字间的随机数

单元格A1输入:=RANDBETWEEN(1,36) 显示结果: 大于等于1及小于36之间的一个随机数 ROUND(number,num_digits)将数字按指定位数舍入 单元格A1输入:=ROUND(2.15, 1) 显示结果:2.2

单元格A1输入:=ROUND(-1.475, 2) 显示结果: -1.48

ROUNDDOWN(number,num_digits)向绝对值减小的方向舍入数字 单元格A1输入:=ROUNDDOWN(2.15, 1) 显示结果:2.1 单元格A1输入:=ROUNDDOWN(-1.475,2) 显示结果: -1.47 ROUNDUP(number,num_digits)向绝对值增大的方向舍入数字 单元格A1输入:=ROUNDUP(2.13, 1) 显示结果:2.2 单元格A1输入:=ROUNDUP(-1.473,2) 显示结果: -1.48 SIGN(number)返回数字的正负数符号 单元格A1输入:=SIGN(10) 显示结果:1 单元格A1输入:=SIGN(-5) 显示结果: -1

SUBTOTAL(function_num, ref1, ref2, ...)返回列表或数据库中的分类汇总(自动筛选用) Function_num为1到11,对忽略隐藏行列数据分类汇总 Function_num为1到11,对包含隐藏行列数据分类汇总

SUM(number1,number2, ...) 返回某一单元格区域中所有数字之和(快捷键:alt+=) 单元格输入:=SUM(3,2) 显示结果:3+2=5

单元格A1输入:=SUM(A2:A4) 显示结果: A2+A3+A4的和

SUMIF(range,criteria,sum_range) 根据指定条件对若干单元格求和 SUMIF(条件区域,条件,求和区域) 常量条件加\号，单元格条件直接引用，条件首尾可以加*号通配符，单元格的条件加&来连接\。求和区域可以用起始单元格代替

单元格输入:=SUMIF(A2:A4,\张*\显示结果:对A2,A3,A4三个单元格满足以\张\开头对应的B2,B3,B4求和

SUMPRODUCT(array1,array2,array3, ...) 将数组间对应的元素相乘，并返回乘积之和 单元格输入:=SUMPRODUCT({1,2,3}*{1,2,3})显示结果:1*1+2*2+3*3=14

SUMPRODUCT((条件区域1=条件1)* (条件区域2=条件2)*求和区域)条件不能用通配符

TRUNC(number,num_digits)将数字截尾取整 单元格输入:=TRUNC(8.9) 显示结果:8 单元格输入:=TRUNC(-8.9) 显示结果: -8 统计函数

AVERAGE(number1,number2,...) 返回参数的平均值（算术平均值） 单元格输入:=AVERAGE(3,4,5),结果显示： (3+4+5)/3=4

单元格输入:=AVERAGE(A2:A4) 显示结果:(A2+A3+A4)/3的平均数

COUNT(value1,value2,...)返回包含数字以及包含参数列表中的数字的单元格的个数 单元格输入:=COUNT(3,4,\张三\结果显示：2,其中”张三”为文本类型数据不计入 单元格输入:=COUNT(A2:A4) 显示结果:A2,A3,A4三个单元格数字类型数据的个数 COUNTA(value1,value2,...)返回参数列表中非空值的单元格个数 单元格输入:=COUNTA(3,4,\张三\结果显示：3

单元格输入:=COUNTA(A2:A4) 显示结果:A2,A3,A4三个单元格非空值数据的个数 COUNTBLANK(range)计算指定单元格区域中空白单元格的个数

单元格输入:=COUNTBLANK(A2:A4)显示结果:A2,A3,A4三个单元格中空白单元格的个数 COUNTIF(range,criteria)计算区域中满足给定条件的单元格的个数

单元格输入:=COUNTIF(A2:A4,\显示结果:A2,A3,A4三个单元格中数值不小于60的个数

LARGE(array,k) 返回数据集中第 k 个最大值

单元格输入:=LARGE({4,5,6},2)显示结果:5，是4,5,6中第二大的数值

单元格输入:=LARGE(A2:A4,3)显示结果: A2,A3,A4三个单元格中第三大的数值 MAX(number1,number2,...)返回一组值中的最大值

单元格输入:=max({4,5,6})显示结果:6，是4,5,6中最大的数值

单元格输入:=max(A2:A4,3)显示结果: A2,A3,A4三个单元格中最大的数值 MIN(number1,number2,...)返回一组值中的最小值

单元格输入:=min({4,5,6})显示结果:4，是4,5,6中最小的数值

单元格输入:=min(A2:A4,3)显示结果: A2,A3,A4三个单元格中最小的数值 RANK(number,ref,order)返回一个数字在数字列表中的排位

单元格输入:=RANK(A3,A2:A4,)显示结果:A3在A2,A3,A4三个单元格按从大到小顺序排列的名次

单元格输入:=RANK(A3,A2:A4,1)显示结果:A3在A2,A3,A4三个单元格按从小到大顺序排列的名次

SMALL(array,k)返回数据集中第 k 个最小值

单元格输入:=SMALL({4,5,6},2) 显示结果:5，是4,5,6中第二小的数值

单元格输入:=SMALL(A2:A4,3) 显示结果: A2,A3,A4三个单元格中第三小的数值 逻辑运算符

AND(logical1,logical2, ...)待检测的 1 到 30 个条件值的逻辑值为真时，返回 TRUE；只要一个参数的逻辑值为假，即返回 FALSE。

(单元格A2输入75)单元格A1输入:=and(A2>=70, A2<=80)显示结果:true

IF(logical_test,value_if_true,value_if_false)执行真假值判断，根据逻辑计算的真假值，返回不同结果，最多可以嵌套七层。

(单元格A2输入75)单元格A1输入:=IF(A2<60,\不及格\及格\良好\优秀\显示结果:良好(三个if，最后三个反括号) NOT(logical)对其参数的逻辑求反

(单元格A2输入75)单元格A1输入:=not(and(A2>=70,A2<=80))显示结果:false

OR(logical1,logical2,...)任何一个参数逻辑值为TRUE，即返回TRUE；当所有参数的逻辑值为 FALSE，即返回FALSE。

(单元格A2输入75)单元格A1输入:=OR(A2>=70,A2<=60)显示结果:true 查找和引用函数

ADDRESS(row_num,column_num,abs_num,a1,sheet_text)按照给定的行号和列标，建立文本类型的单元格地址，将数值化的行列号按引用类型显示出来 单元格A1输入:=ADDRESS(1,1,4) 显示结果:A1

CHOOSE(index_num,value1,value2,...)Index_num 必须为 1 到 29 之间的数字,根据 Index_num取得对应位置的值

(单元格A2输入2013-7-1)单元格A1输入:=CHOOSE(MOD(A2,7)+1,\六\日\一\二\三\四\五\显示结果:一

COLUMN(reference)返回给定引用的列号(数值) 单元格B1输入:=column() 显示结果:2

HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)在表格或数值数组的首行查找指定的数值，并由此返回表格或数组当前列中指定行处的数值 A1:C1=1,2,3 A2:C2=a,b,c A3:C3=d,e,f

单元格A4输入:=HLOOKUP(3,{1,2,3;\在数组常量的第一行中查找 3，并返回同列中第2行的值。(c)

单元格A4输入:=HLOOKUP(3,A1:C3,2,)与VLOOKUP列查找对应 HYPERLINK(link_location, friendly_name)创建一个快捷方式或跳转，用以打开存储在网络服务器、Intranet 或 Internet 中的文件

单元格A1输入:=HYPERLINK(\显示结果: Sheet3!A1的值 INDEX(array,row_num,column_num)从引用或数组选择指定行号列号交点的值 单元格A1输入:=INDEX(Sheet3!A:B,2,1) 显示结果: Sheet3!A2的值 INDIRECT(ref_text,a1)返回由文本值指定的引用,激活文本变为链接 单元格A1输入:=INDIRECT(\显示结果: Sheet3!A2的值

LOOKUP(lookup_value,lookup_vector,result_vector)在二维数组中根据对应值查找所需值

单元格A1输入: =LOOKUP(\显示结果:2

MATCH(lookup_value,lookup_array,match_type)返回在指定方式下与指定数值匹配的数组中元素的相应位置

单元格A1输入: =MATCH(\显示结果:2

OFFSET(reference,rows,cols,height,width)以指定的单元格或者区域为参照系，通过给定偏移量得到新的引用

单元格A1输入: =sum(OFFSET( Sheet3!A1,1,1,2,2)) 显示结果: Sheet3!B2:C3的和 ROW(reference)返回引用的行号 单元格B1输入:=ROW() 显示结果:1

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值 A1:C1=1,2,3 A2:C2=a,b,c A3:C3=d,e,f

单元格A4输入:=VLOOKUP(1,{1,2,3;\在数组常量的第一列中查找1，并返回同行中第2列的值。(2)

单元格A4输入:=VLOOKUP(1,A1:C3,2,)与HLOOKUP行查找对应

文本和数据函数

ASC(text)将字符串中的全角（双字节）英文字母或片假名更改为半角（单字节）字符 单元格A4输入: =ASC(\（）\显示结果:()将中文刮号转换成英文状态 CHAR(number)返回计算机代码数字所对应的字符 单元格A4输入: = =CHAR(65) 显示结果:A

FIND(find_text,within_text,start_num) 在一个文本值中查找另一个文本值（区分大小写）

单元格A4输入: =FIND(\人\中华人民共和国\显示结果:3

FIXED(number,decimals,no_commas)将数字设置为具有固定小数位数，以文本形式返回结果。 单元格A1输入: =FIXED(1234.5678) 显示结果: 1,234.57会计格式,保留两位

单元格A1输入: =FIXED(1234.5678,-1,ture) 显示结果: 1230常规格式,小数点左移一位 LEFT(text,num_chars)返回文本字符串中左起的指定个数字符(right,右起) 单元格A1输入: = LEFT (\中华人民共和国\显示结果: 中华 LEN(text)返回文本字符串中的字符个数

单元格A1输入: = LEN (\中华人民共和国\显示结果: 7

MID(text,start_num,num_chars)从文本字符串中指定的起始位置开始返回指定数量的字符 单元格A1输入: =MID(\中华人民共和国\显示结果: 人民

REPLACE(old_text,start_num,num_chars,new_text)从文本字符串中指定的起始位置开始替换指定数量的字符为新字符

单元格A1输入:=REPLACE(\中华人民共和国\显示结果: 中*国 REPT(text,number_times)按照给定的次数重复显示文本

单元格A1输入:=REPLACE(\中华人民共和国\显示结果: 中*****国 RIGHT(text,num_chars)返回文本字符串中右起的指定个数字符

单元格A1输入: =RIGHT (\中华人民共和国\显示结果: 共和国

SUBSTITUTE(text,old_text,new_text,instance_num)在文本字符串中用新文本替换旧文本 单元格A1输入:=SUBSTITUTE(\中华人民共和国\华人民共和\显示结果: 中国

TEXT(value,format_text)设置数字格式将其转换为文本(设置单元格格式只是显示的改变) 单元格A1输入:=(-100,\显示结果: 65536 TRIM(text)将文本头尾空格删除,不删除文本中间的空格

单元格A1输入:=TRIM(\中华人民共和国 \显示结果: 中华人民共和国 VALUE(text)将文本参数转换为数字

单元格A1输入:=VALUE(\显示结果:3,相当字符*1或+0或-0 日期与时间函数

DATE(year,month,day)返回特定日期的序列号

单元格A1输入:=DATE(2013,7,20)显示结果: 2013-7-20

DAY(serial_number)返回以序列号表示的某日期的天数，用整数 1 到 31 表示 单元格A1输入:=DAY(DATE(2013,7,20))显示结果: 20

HOUR(serial_number)返回时间值的小时数。即一个介于 0到 23之间的整数 单元格A1输入:=HOUR(now())显示结果为当前时间的小时数

MINUTE(serial_number)返回时间值中的分钟，为一个介于 0 到 59 之间的整数 单元格A1输入:=MINUTE(now())显示结果为当前时间的分钟数

MONTH(serial_number)返回以序列号表示的日期中的月份。月份是介于 1到 12之间的整数 单元格A1输入:=MONTH(now())显示结果为当前时间的月份 NOW( )返回当前日期和时间的序列号或显示值

单元格A1输入:=now()显示结果为当前日期和时间

SECOND(serial_number)返回时间值的秒数。返回的秒数为 0 到 59 之间的整数 单元格A1输入:=SECOND(now())显示结果为当前时间的秒数

TODAY()返回今天日期的序列号(单元格的格式为“常规”,则结果将显示为日期格式) 单元格A1输入:= TODAY (now())显示结果为今天的日期

WEEKDAY(serial_number,return_type)返回日期为星期几(美式,星期天为1) 单元格A1输入:=WEEKDAY(now())显示结果为今天的星期 YEAR(serial_number)返回某日期对应的年份

单元格A1输入:=YEAR(now())显示结果为当前时间的年份

DATEDIF(start_date,end_date,unit)返回两个日期之间的年\\月\\日间隔数 单元格A1输入:= DATEDIF(\显示结果:364 信息函数

CELL(info_type,reference)返回单元格的格式、位置或内容等信息 单元格A1输入:=CELL(\显示结果为当前工作表路径

IS 类函数，可以检验数值的类型并根据参数取值返回TRUE或FALSE,与函数IF结合在一起使用时，可以提供一种方法用来在公式中判断错误值

ISBLANK(value)如果值为空，则返回TRUE,否则返回FALSE

ISERR(value)如果值为除 #N/A 以外的任何错误值，则返回 TRUE,否则返回FALSE

ISERROR(value)如果值为任何错误值(#N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME? 或 #NULL!),则返回TRUE,否则返回FALSE

ISLOGICAL(value)如果值为逻辑值，则返回TRUE,否则返回FALSE ISNA(value)如果值为 #N/A 错误值，则返回TRUE,否则返回FALSE ISNONTEXT(value)如果值不为文本，则返回TRUE,否则返回FALSE ISNUMBER(value)如果值为数字，则返回TRUE,否则返回FALSE

单元格A1输入: =IF(ISNUMBER(FIND(\人\中华人民共和国\人\中华人民共和国\显示结果: 3

ISREF(value)如果值为引用值，则返回TRUE,否则返回FALSE ISTEXT(value)如果值为文本，则返回TRUE,否则返回FALSE 引用

绝对引用：$A$1,行列号固定

相对引用：$A1(行号不固定)或者A$1(列号不固定)或者A1(行列号都不固定) 在对公式进行复制粘贴或者下拉过程中，引用的单元格可能会随着当前单元格的位置而变化，所以要根据区域条件对单元格进行引用处理(通过在A1单元格输入=A2或者其他函数时按F4键调整引用方式)