EXCEL函数精编.docx
- 文档编号:25585300
- 上传时间:2023-06-10
- 格式:DOCX
- 页数:93
- 大小:855.66KB
EXCEL函数精编.docx
《EXCEL函数精编.docx》由会员分享,可在线阅读,更多相关《EXCEL函数精编.docx(93页珍藏版)》请在冰豆网上搜索。
EXCEL函数精编
EXCEL
函
数
精
编
赠:
我的果果
责任编辑:
李春雨
时间:
二零一二年十一月二十九日
目录
目录2
(一)文本函数7
1.1.LEFT函数7
1.2.RIGHT函数8
1.3.MID函数8
1.4.LEN函数9
1.5.TEXT函数9
1.6.VALUE函数9
1.7.ASC函数9
1.8.WIDECHAR函数10
1.9.CHAR函数10
1.10.CLEAN函数10
1.11.CODE函数11
1.12.DOLLAR函数11
1.13.EXACT函数11
1.14.FIND函数12
1.15.SEARCH函数12
1.16.FIXED函数12
1.17.LOWER函数13
1.18.UPPER函数13
1.19.Offset函数13
1.20.PROPER函数14
1.21.REPLACE函数14
1.22.SUBSTITUTE函数15
1.23.REPT函数15
1.24.TRIM函数15
1.25.CHOOSE函数16
1.26.CONCATENATE函数16
(二)逻辑函数16
2.1.AND函数16
2.2.OR函数17
2.3.IF函数17
(三)信息级排序函数17
3.1.ISERROR函数17
3.2.ISODD函数17
3.3.SMALL18
3.4.LARGE18
(四)查找与引用函数18
4.1COLUMN函数18
4.2ROW函数19
4.3RAND函数19
4.4RANDBETWEEN19
4.5ROUND函数19
4.6ROUNDDOWN函数20
4.7ROUNDUP函数20
4.8MROUND20
4.9COUNT函数20
4.10COUNTA函数21
4.11COUNTIF函数21
4.12DCOUNT函数22
4.13FREQUENCY函数22
4.14MATCH函数23
4.15RANK函数24
4.16INDEX函数25
4.17LOOKUP25
4.18VLOOKUP函数26
4.19HLOOKUP26
(五)数学函数27
5.1ABS函数27
5.2MIN函数27
5.3MOD函数27
5.4INT函数27
5.5TRUNC28
5.6AVERAGE函数28
5.7GCD函数28
5.8LCM28
5.9EXP29
5.10LN29
5.11LOG29
5.12LOG1029
5.13ODD29
5.14EVEN30
5.15PI30
5.16POWER30
5.17PRODUCT函数30
5.18SIGN30
5.19ROMAN31
5.20QUOTIENT函数31
5.21SERIESSUM31
5.22FACT31
5.23SUMSQ32
5.24FACTDOUBLE32
5.25SUMPRODUCT32
5.26SUMX2MY232
5.27SUMX2PY232
5.28SUMXMY233
5.29BINOMDIST33
5.30CHIDIST33
5.31CHIINV33
(六)日期函数34
6.1NOW函数34
6.2TODAY函数34
6.3DATE函数34
6.4函数名称:
DATEDIF34
6.5DAY函数35
6.6MONTH函数35
6.7WEEKDAY函数35
(七)统计函数35
7.1AVERAGE35
7.2AVERAGEA36
7.3COUNT36
7.4COUNTA36
7.5COUNTBLANK36
7.6COUNTIF36
7.7SUBTOTAL36
7.8SUMIF函数37
7.9AVEDEV37
7.10MAX37
7.11MAXA37
7.12MIN38
7.13MINA38
7.14MODE38
7.15MEDIAN38
7.16PERCENTRANK38
7.17PERMUT38
7.18FLOOR39
7.19VAR39
7.20VARA39
7.21VARP39
7.22VARPA39
7.23BETADIST40
7.24BETAINV40
7.25CHITEST40
7.26CONFIDENCE40
7.27CORREL40
7.28COVAR41
7.29CRITBINOM41
7.30DEVSQ41
7.31EXPONDIST41
7.32FDIST41
7.33FINV42
7.34FISHER42
7.35FISHERINV42
7.36FORECAST42
7.37FREQUENCY42
7.38FTEST42
7.39GAMMADIST43
7.40GAMMAINV43
7.41GAMMALN43
7.42GEOMEAN43
7.43GROWTH43
7.44HARMEAN44
7.45HYPGEOMDIST44
7.46INTERCEPT44
7.47KURT44
7.48LINEST44
7.49LOGEST45
7.50LOGINV45
7.51NEGBINOMDIST45
7.52NORMDIST46
7.53NORMSINV46
7.54NORMSDIST46
7.55NORMSINV46
7.56PEARSON46
7.57PERCENTILE46
7.58POISSON47
7.59PROB47
7.60QUARTILE47
7.61RSQ47
7.62SKEW47
7.63SLOPE48
7.64STANDARDIZE48
7.65STDEV48
7.66STDEVA48
7.67STDEVP48
7.68STDEVPA49
7.69STEYX49
7.70TDIST49
7.71TINV49
7.72TREND49
7.73TRIMMEAN50
7.74TTEST50
7.75WEIBULL50
7.76ZTEST50
(八)三角函数51
8.1TAN51
8.2TANH51
8.3SIN51
8.4SINH51
8.5DEGREES51
8.6COS51
8.7TAN52
8.8TANH52
8.9COSH52
8.10ACOS52
8.11ACOSH52
8.12ASIN52
8.13ASINH52
8.14ATAN53
8.15ATAN253
8.16ATANH53
(九)财务函数53
9.1DB53
9.2DDB53
9.3FV54
9.4IPMT54
9.5IRR54
9.6ISPMT54
9.7MIRR54
9.8NPER54
9.9NPV55
9.10PMT55
9.11PPMT55
9.12PV55
9.13RATE55
9.14SLN55
9.15SYD55
9.16VDB56
(十)数据库和清单管理函数56
10.1DAVERAGE56
10.2DCOUNT56
10.3DCOUNTA56
10.4DGET56
10.5DMAX56
10.6DMIN57
10.7DPRODUCT57
10.8DSTDEV57
10.9DSTDEVP57
10.10DSUM57
10.11DVAR57
10.12DVARP57
10.13GETPIVOTDATA57
(十一)部分函数应用举例58
11.1身份证信息提取58
11.1.1从身份证号码中提取出性别58
11.1.2从身份证号码中进行年龄判断58
11.2年龄和工龄计算58
11.3根据出生年月推算生肖58
11.4如何求出一个人到某指定日期的周岁?
58
11.5计算距离退休年龄的公式58
11.6计算工龄58
11.7年龄及工龄计算59
11.8时间和日期应用59
11.8.1自动显示当前日期公式59
11.8.2如何在单元格中自动填入当前日期59
11.8.3如何判断某日是否星期天59
11.8.4某个日期是星期几59
11.8.5什么函数可以显示当前星期59
11.8.6求本月天数59
11.8.7关于取日期59
11.8.8如何对日期进行上、中、下旬区分59
11.8.9日期格式转换59
11.9排名及排序筛选60
11.9.1自动排序60
11.9.2重复数据得到唯一的排位序列60
11.9.3按奇偶数排序60
11.9.4自动生成序号60
11.9.5如何自动标示A栏中的数字大小排序?
60
11.9.6如何设置自动排序60
11.9.7重复数据得到唯一的排位序列60
11.9.8按字符数量排序60
11.9.9排序字母与数字的混合内容60
11.9.10随机排序61
11.9.11排序的问题61
11.9.12怎样才能让数列自动加数61
11.9.13数字的自动排序,插入后不变?
61
11.9.14根据规律的重复的姓名列产生自动序号62
(一)文本函数
1.1.LEFT函数
函数名称:
LEFT
主要功能:
从一个文本字符串的第一个字符开始,截取指定数目的字符。
使用格式:
LEFT(text,num_chars)
参数说明:
text代表要截字符的字符串;num_chars代表给定的截取数目。
应用举例:
假定E2单元格中保存了“我喜欢老婆手里的电饭锅”的字符串,我们在E2单元格中输入公式:
=LEFT(D2,5),确认后即显示出“我喜欢老婆”的字符。
如下图:
特别提醒:
此函数名的英文意思为“左”,即从左边截取,Excel很多函数都取其英文的意思。
1.2.RIGHT函数
函数名称:
right
主要功能:
从一个文本字符串的右面第一个字符开始,截取指定数目的字符。
使用格式:
RIGHT(text,num_chars)
参数说明:
text代表要截字符的字符串;num_chars代表给定的截取数目。
应用举例:
假定D2单元格中保存了“我喜欢老婆手里的电饭锅”的字符串,我们在E2单元格中输入公式:
=Right(D2,6),确认后即显示出“手里的电饭锅”的字符。
如下图:
特别提醒:
此函数名的英文意思为“右”,即从右边截取。
1.3.MID函数
函数名称:
MID
主要功能:
从一个文本字符串的指定位置开始,截取指定数目的字符。
使用格式:
MID(text,start_num,num_chars)
参数说明:
text代表一个文本字符串;start_num表示指定的起始位置num_chars表示要截取的数目。
应用举例:
假定D2单元格中保存了“我喜欢老婆手里的电饭锅”的字符串,我们在E2单元格中输入公式:
=MID(D2,4,4),确认后即显示出“老婆手里”的字符。
如下图:
特别提醒:
公式中各参数间,要用英文状态下的逗号“,”隔开。
1.4.LEN函数
函数名称:
LEN
主要功能:
统计文本字符串中字符数目。
使用格式:
LEN(text)
参数说明:
text表示要统计的文本字符串。
应用举例:
假定D2单元格中保存了“我喜欢老婆手里的电饭锅”的字符串,我们在E2单元格中输入公式:
=LEN(D2),确认后即显示出统计结果“11”。
如下图:
特别提醒:
LEN要统计时,无论中全角字符,还是半角字符,每个字符均计为“1”;与之相对应的一个函数——LENB,在统计时半角字符计为“1”,全角字符计为“2”。
1.5.TEXT函数
函数名称:
TEXT
主要功能:
根据指定的数值格式将相应的数字转换为文本形式。
使用格式:
TEXT(value,format_text)
参数说明:
value代表需要转换的数值或引用的单元格;format_text为指定文字形式的数字格式。
应用举例:
如果B68单元格中保存有数值1280.45,我们在C68单元格中输入公式:
=TEXT(B68,"$0.00"),确认后显示为“$1280.45”。
特别提醒:
format_text参数可以根据“单元格格式”对话框“数字”标签中的类型进行确定。
1.6.VALUE函数
函数名称:
VALUE
主要功能:
将一个代表数值的文本型字符串转换为数值型。
使用格式:
VALUE(text)
参数说明:
text代表需要转换文本型字符串数值。
应用举例:
如果B74单元格中是通过LEFT等函数截取的文本型字符串,我们在C74单元格中输入公式:
=VALUE(B74),确认后,即可将其转换为数值型。
特别提醒:
如果文本型数值不经过上述转换,在用函数处理这些数值时,常常返回错误。
1.7.ASC函数
主要功能:
将字符串中的全角(双字节)英文字母更改为半角(单字节)字符。
使用格式:
ASC(text)
应用举例:
如下图
1.8.WIDECHAR函数
主要功能:
将单字节字符转换为双字节字符。
使用格式:
WIDECHAR(text)
应用举例:
1.9.CHAR函数
主要功能:
返回对应于数字代码的字符,函数CHAR可将其他类型计算机文件中的代码转换为字符。
使用格式:
CHAR(number)
应用举例:
如下图
1.10.CLEAN函数
主要功能:
删除文本中不能打印的字符。
对从其他应用程序中输入的字符串使用CLEAN函数,将删除其中含有的当前操作系统无法打印的字符。
例如,可以删除通常出现在数据文件头部或尾部、无法打印的低级计算机代码。
使用格式:
CLEAN(text)
应用举例:
如下图
1.11.CODE函数
主要功能:
返回文字串中第一个字符的数字代码。
返回的代码对应于计算机当前使用的字符集。
使用格式:
CODE(text)
应用举例:
如下图
1.12.DOLLAR函数
主要功能:
依照货币格式将小数四舍五入到指定的位数并转换成文字。
使用格式:
DOLLAR或RMB(number,decimals)
应用举例:
如下图
A24代表数字,3表示小数点后几位(会自动四舍五入)
1.13.EXACT函数
主要功能:
该函数测试两个字符串是否完全相同。
如果它们完全相同,则返回TRUE;否则,返回FALSE。
函数EXACT能区分大小写,但忽略格式上的差异。
利用函数EXACT可以测试输入文档内的文字。
使用格式:
EXACT(text1,text2)
应用举例:
如下图
其中“L”一个大写一个小写,所以返回错误(False)
1.14.FIND函数
主要功能:
FIND用于查找其他文本串(within_text)内的文本串(find_text),并从within_text的首字符开始返回find_text的起始位置编号
使用格式:
FIND(find_text,within_text,start_num)
应用举例:
如下图
注:
“1”如果输入过大,超过六将会跳过第六位的的“我”进行查找,返回值将是“#value!
”
1.15.SEARCH函数
主要功能:
SEARCH返回从start_num开始首次找到特定字符或文本串的位置上特定字符的编号。
使用SEARCH可确定字符或文本串在其他文本串中的位置,这样就可使用MID或REPLACE函数更改文本。
SEARCHB也可在其他文本串(within_text)中查找文本串(find_text),并返回find_text的起始位置编号。
此结果是基于每个字符所使用的字节数,并从start_num开始的。
此函数用于双字节字符。
此外,也可使用FINDB在其他文本串中查找文本串。
使用格式:
SEARCH(find_text,within_text,start_num)
SEARCHB(find_text,within_text,start_num)
应用举例:
如下图
附:
SEARCH和FIND函数的区别主要有两点:
1.FIND函数区分大小写,而SEARCH函数则不区分
2.SEARCH函数支持通配符,而FIND函数不支持(通配符指如“?
”)
1.16.FIXED函数
主要功能:
按指定的小数位数进行四舍五入,利用句点和逗号,以小数格式对该数设置格式,并以文字串形式返回结果。
使用格式:
FIXED(number,decimals,no_commas)
应用举例:
如下图
1.17.LOWER函数
主要功能:
将一个文字串中的所有大写字母转换为小写字母。
使用格式:
LOWER(text)
应用举例:
如下图
1.18.UPPER函数
主要功能:
将文本转换成大写形式。
使用格式:
UPPER(text)
应用举例:
1.19.Offset函数
主要功能:
将单元格转移
使用格式:
offset(一个位置或区域,向下转移的行数,向右转移的列数,所要取的行的数量,所要取列的数量)
应用举例:
如下图
将锁定B30单元格向下移动2个,之后向右移动3个,只取1个行高和1个列宽之后的单元格,之后A29返回这个单元格的内容
1.20.PROPER函数
主要功能:
将文字串的首字母及任何非字母字符之后的首字母转换成大写。
将其余的字母转换成小写。
使用格式:
PROPER(text)
应用举例:
如下图
1.21.REPLACE函数
主要功能:
REPLACE使用其他文本串并根据所指定的字符数替换某文本串中的部分文本。
REPLACEB使用其他文本串并根据所指定的字符数替换某文本串中的部分文本。
此函数专为双字节字符使用。
使用格式:
REPLACE(想被修改的字符串,开始修改的字符,要修改的字符数量,用这样的字符替换)
REPLACEB(old_text,start_num,num_bytes,new_text)
应用举例:
如下图
1.22.SUBSTITUTE函数
主要功能:
在文字串中用new_text替代old_text。
如果需要在某一文字串中替换指定的文本,请使用函数SUBSTITUTE;如果需要在某一文字串中替换指定位置处的任意文本,请使用函数REPLACE。
使用格式:
SUBSTITUTE(text,old_text,new_text,instance_num)
应用举例:
如下图
instance_num:
表示要替换部分出现的位置,不输入表示全部替换,上图输入“1”时结果为“2012-11*11”。
1.23.REPT函数
主要功能:
按照给定的次数重复显示文本。
可以通过函数REPT来不断地重复显示某一文字串,对单元格进行填充。
使用格式:
REPT(要填充文本,重复出现的次数)
应用举例:
如下图
1.24.TRIM函数
主要功能:
除了单词之间的单个空格外,清除文本中首尾的空格及文本间连续多个空格变成一个空格。
在从其他应用程序中获取带有不规则空格的文本时,可以使用函数TRIM。
使用格式:
TRIM(text)
应用举例:
如下图
1.25.CHOOSE函数
主要功能:
显示你想出现的文本
使用格式:
(数值,"文本","文本","文本"……)
应用举例:
如下图
因为A227输入的数值是3,所以显示第三个字符串,也就是“星期三”
1.26.CONCATENATE函数
函数名称:
CONCATENATE
主要功能:
将多个字符文本或单元格中的数据连接在一起,显示在一个单元格中。
使用格式:
CONCATENATE(Text1,Text……)
参数说明:
Text1、Text2……为需要连接的字符文本或引用的单元格。
应用举例:
如下图
如果输入=concatenate(”我”,”是”,”中国人”)效果一样
(二)逻辑函数
8.1.AND函数
函数名称:
AND
主要功能:
返回逻辑值:
如果所有参数值均为逻辑“真(TRUE)”,则返回逻辑“真(TRUE)”,反之返回逻辑“假(FALSE)”。
使用格式:
AND(logical1,logical2,...)
参数说明:
Logical1,Logical2,Logical3……:
表示待测试的条件值或表达式,最多这30个。
应用举例:
在C5单元格输入公式:
=AND(A5>=60,B5>=60),确认。
如果C5中返回TRUE,说明A5和B5中的数值均大于等于60,如果返回FALSE,说明A5和B5中的数值至少有一个小于60。
特别提醒:
如果指定的逻辑条件参数中包含非逻辑值时,则函数返回错误值“#VALUE!
”或“#NAME”。
8.2.OR函数
函数名称:
使用格式:
OR
主要功能:
返回逻辑值,仅当所有参数值均为逻辑“假(FALSE)”时返回函数结果逻辑“假(FALSE)”,
否则都返回逻辑“真(TRUE)”。
使用格式:
OR(logical1,logical2,...)
参数说明:
Logical1,Logical2,Logical3……:
表示待测试的条件值或表达式,最多这30个。
应用举例:
在C62单元格输入公式:
=OR(A62>=60,B62>=60),确认。
如果C62中返回TRUE,说明A62和B62中的数值至少有一个大于或等于60,如果返回FALSE,说明A62和B62中的数值都小于60。
特别提醒:
如果指定的逻辑条件参数中包含非逻辑值时,则函数返回错误值“#VALUE!
”或“#NAME”。
8.3.IF函数
函数名称:
IF
主要功能:
根据对指定条件的逻辑判断的真假结果,返回相对应的内容。
使用格式:
=IF(逻辑表达式,条件为真时返回内容,条件为假时返回的内容)
应用举例:
如下图
(三)信息级排序函数
8.1.ISERROR函数
函数名称:
ISERROR
主要功能:
用于测试函数式返回的数值是否有错。
如果有错,该函数返回TRUE,反之返回FALSE。
使用格式:
ISERROR(value)
参数说明:
Value表示需要测试的值或表达式。
应用举例:
输入公式:
=ISERROR(A35/B35),确认以后,如果B35单元格为空或“0”,则A35/B35出现错误,此时前述函数返回TRUE结果,反之返回FALSE。
特别提醒:
此函数通常与IF函
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- EXCEL 函数 精编