Excel函数应⽤⼤全（整理篇）
Excel函数应⽤⼤全
⼀、concatenate
⼆、EXACT
三、LEFT或LEFTB 和right⽤法⼀样
四、LEN或LENB
五、LOWER
六、MID或MIDB
七、REPLACE或REPLACEB
⼋、SUBSTITUTE
九、T ⽬录
⼗、TEXT
⼗⼀、TRIM
⼗⼆、UPPER
⼗三、value
⼗四、AVERAGE
⼗五、AVERAGEA
⼗六、COUNT
⼗七、COUNTA
⼗⼋、COUNTBLANK
⼗九、COUNTIF
⼆⼗、DAVERAGE
⼆⼗⼀、DCOUNT
⼆⼗⼆、DCOUNTA
⼆⼗三、DMAX
⼆⼗四、DMIN
⼆⼗五、DSUM
⼆⼗六、mod函数
⼆⼗七、SUMIF
⼆⼗⼋：ISERROR
and find函数
⼆⼗九、rank排名
函数
三⼗、rand随机函
数
⼀、CONCATENATE
⽤途：将若⼲⽂字串合并到⼀个⽂字串中，其功能与"&"运算符相同。
语法：CONCATENATE(text1，text2，...)
参数：Text1，text2，...为1到30个将要合并成单个⽂本的⽂本项，这些⽂本项可以是⽂字串、数字或对单个单元格的引⽤。
实例：如果A1=98、A2=千⽶，则公式“=CONCATENATE(A1，A2)”返回“98千⽶”，与公式“=A1&A2”等价。即将两个单元格数据合并在⼀起。
举例：调资
将⼀级教师加100元，⼆级教师加80元。
=if(b1=”⼀级教师”，concatenate(c1+100),concatenate(c1+80))
说明：B列是职称，C列是⼯资。如果再有三级教师，⾼级教师在进⾏if 语句叠加。
=if（b1=”⼀级教师”，concatenate(c1+100),if(b1=”⾼级教师”，concatenate(c1+120)，if（b1=”⼆级教
师”，concatenate(c1+80)，concatenate(c1+60)），可以叠加三层。本公式意思是：如果B1单元格中是⼀级教师则C1+100元，如果是⾼级教师则C1+120元，如果B1单元格是⼆级教师，则C1+80元，其他+60元。
加称呼：=if（A1=“男”，concatenate（B1，“先⽣”），concatenate （B1，“⼥⼠”））
例：提取⾝份证信息
=CONCATENATE(MID(A2,7,4),"-",MID(A2,11,2),"-",MID(A2,13,2))
⼆、EXACT
⽤途：测试两个字符串是否完全相同。如果它们完全相同，则返回TRUE；否则返回FALSE。EXACT函数能区分⼤⼩写，但忽略格式上的差异。
语法：EXACT(text1，text2)。
参数：Text1是待⽐较的第⼀个字符串，Text2是待⽐较的第⼆个字符串。
实例：如果A1=物理、A2=化学A3=物理，则公式“=EXACT(A1，A2)”返回FALSE，=EXACT(A1，A3)返回FALSE，=EXACT("word"，"word")返回TRUE。
举例：如测试两列数据是否完全⼀致，可⽤此公式：=exact（A1：B1），如果⼀样则显⽰true，否则显⽰FALSE。
举例: 检查两个字符是否相同：=Exact(text1,text2)
三、LEFT或LEFTB和right⽤法⼀样。
⽤途：根据指定的字符数返回⽂本串中的第⼀个或前⼏个字符。此函数⽤于双字节字符。
语法：LEFT(text，num_chars)或LEFTB(text，num_bytes)。
参数：Text是包含要提取字符的⽂本串;Num_chars指定函数要提取的字符数，它必须⼤于或等于0。N
um_bytes按字节数指定由LEFTB提取的字符数。
实例：如果A1=电脑爱好者，则LEFT(A1，2)返回“电脑”，LEFTB(A1，2)返回“电”。
举例：提取姓=if（len（C1）=4，left（C1,2），left（C1,1））即：检查c1单元格如果是四个字，则提取左边两个字，否则提取左边⼀个字。
提取名=if（len（C1）=2，right（C1,1）,right（C1,2））即：检查C1单元格如果是两个字则，提取右边第⼀个字，否则提取右边第⼀和第⼆个字。
例如：分解单元格为多个部分
A1单元格数据 15yearsold 分解成 15 years old三部分分别存放于B1、C1、D1单元格中 B1=left（A1，2） C1=mid（A1,3,2）
D1=right（A1，8）中⽂姓名若是3个字
B1=left（A1,3） C1=mid（A1,4,2） D1=right（A1，8）
四、LEN或LENB
⽤途：LEN返回⽂本串的字符数。LENB返回⽂本串中所有字符的字节数。
语法：LEN(text)或LENB(text)。
参数：Text待要查其长度的⽂本。
注意：此函数⽤于双字节字符，且空格也将作为字符进⾏统计。
实例：如果A1=电脑爱好者，则公式“=LEN(A1)”返回5，=LENB(A1)返回10。说明：检测的字符数含空格。
五、LOWER
⽤途：将⼀个⽂字串中的所有⼤写字母转换为⼩写字母。
语法：LOWER(text)。
语法：Text是包含待转换字母的⽂字串。
注意：LOWER函数不改变⽂字串中⾮字母的字符。LOWER与PROPER和UPPER函数⾮常相似。
实例：如果A1=Excel，则公式“=LOWER(A1)”返回excel
六、MID或MIDB
⽤途：MID返回⽂本串中从指定位臵开始的特定数⽬的字符，该数⽬由⽤户指定。MIDB返回⽂本串中从指定位臵开始的特定数⽬的字符，该数⽬由⽤户指定。MIDB 函数可以⽤于双字节字符。
语法：MID(text，start_num，num_chars)或MIDB(text，start_num，num_bytes)。参数：Text是包含要提取字符的⽂本串。Start_num是⽂本中要提取的第⼀个字符的位臵，⽂本中第⼀个字符的start_num为1，以此类推;Num_chars指定希望MID从⽂本中返回字符的个数;Num_bytes指定希望MIDB从⽂本中按字节返回字符的个数。
实例：如果a1=电⼦计算机，则公式“=MID(A1，3，2)”返回“计算”，=MIDB(A1，3，2)返回“⼦”。
例1、提取⾝份证中的出⽣⽇期和性别信息：
=TEXT(MID(A16,7,8),"00-00-00") 即：提取⾝份证信息中A16单元格中的从第七位开始的六位数中间⽤-分隔开。仅限于18位的⾝份证。如果是15为的改为=TEXT(MID(A16,7,6),"00-00-00")，如71-01-07如果想让71前⾯加上19，可以这样：19&A17.即将19合并到某个单元格中。
=TEXT(IF(LEN(A1)=15,"19",)&MID(A1,7,IF(LEN(A1)=18,8,6)),"####-##-##")
假如⾝份证号数据在A1单元格，在B1单元格中编辑公式
=IF(LEN(A1)=15,"19"&MID(A1,7,2)&MID(A1,9,2)&MID(A1,11,2),MID(A1,7 ,4)&MID(A1,11,2)&MID(A1,13,2))
这样输出格式就都是19821010这种格式了。
例2、提取性别信息：
假定⾝份证号在A1单元格，则在B1单元格中编辑公式
=IF(AND(LEN(A1)=15,MOD(MID(A1,15,1)*1,2)=1),"",IF(AND(LEN(A1)=18, MOD(MID(A1,17,1)*1,2)=1),"男","⼥"))
=IF(MOD(IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1)),2)=1,"男","⼥")LEN(C2)=15：检查⾝份证号码的长度是否是15位。
MID(C2,15,1)：如果⾝份证号码的长度是15位，那么提取第15位的数字。
MID(C2,17,1)：如果⾝份证号码的长度不是15位，即18位⾝份证号码，那么应该提取第17位的数字。
MOD(IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1)),2)：⽤于得到给出数字除以指定数字后的余数，本例表⽰对提出来的数值除以2以后所得到的余数。
IF(MOD(IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1)),2)=1,"男","⼥")：如果除以2以后的余数是1，那么B2单元格显⽰为“男”，否则显⽰为“⼥。
简单公式提取出⽣年⽉：
=mid（A1,7，if（len（A1）=15,6，if（len（A1）=18,8,0）））
七、REPLACE或REPLACEB
⽤途：REPLACE使⽤其他⽂本串并根据所指定的字符数替换另⼀⽂本串中的部分⽂本。REPLACEB的⽤途与REPLACE相同，它是根据所指定的字节数替换另⼀⽂本串中的部分⽂本。
语法：REPLACE(old_text，start_num，num_chars，new_text)，REPLACEB(old_text，start_num，num_bytes，new_text)。
参数：Old_text是要替换其部分字符的⽂本;Start_num是要⽤new_text替换的old_text中字符的位臵;Num_chars是希望REPLACE使⽤new_text替换old_text 中字符的个数;Num_bytes是希望REPLACE使⽤new_text替换old_text的字节数;New_text是要⽤于替换old_text中字符的⽂本。
注意：以上两函数均适⽤于双字节的汉字。
实例：如果A1=学习的⾰命、A2=电脑，则公式“=REPLACE(A1，3，3，A2)”返回“学习电脑”，=REPLACEB(A1，2，3，A2)返回“电脑的⾰命”。即：⽤A2单元格即“电脑”代替了“的⾰命”完成了⽂本的替换。
再如：=REPLACE(A1,7,4,"xxxx")
⼋、SUBSTITUTE
⽤途：在⽂字串中⽤new_text 替代old_text 。如果需要在⼀个⽂字串中替换指定的⽂本，可以使⽤函数SUBSTITUTE；如果需要在某⼀⽂字串中替换指定位臵处的任意⽂本，就应当使⽤函数REPLACE 。
语法：SUBSTITUTE(text ，old_text ，new_text ，instance_num)。
参数：Text 是需要替换其中字符的⽂本，或是含有⽂本的单元格引⽤;Old_text 是需要替换的旧⽂本;New_text ⽤于替换old_text 的⽂
本;Instance_num 为⼀数值，⽤来指定以new_text 替换第⼏次出现的old_text；如果指定了instance_num ，则只有满⾜要求的old_text 被替换；否则将⽤new_text 替换Text 中出现的所有old_text 。
实例：如果A1=学习的⾰命、A2=电脑，则公式“=SUBSTITUTE(A1，"的⾰命"，A2，1)九、T
⽤途：将数值转换成⽂本。
语法：T(value)。
参数：value 是需要进⾏测试的数据。如果value 本⾝就是⽂本，或是对⽂本单元格的引⽤，T 函数将
返回value；如果没有引⽤⽂本，则返回""(空⽂本)。实例：如果A1中含有⽂本“电脑”，则公式“=T(A1)⼗、TEXT
⽤途：将数值转换为按指定数字格式表⽰的⽂本。
语法：TEXT(value ，format_text)。
参数：value 是数值、计算结果是数值的公式、或对数值单元格的引⽤;format_text 是所要选⽤的⽂本型数字格式，即“单元格格式”对话框“数字”选项卡的“分类”列表框中显⽰的格式，它不能包含星号“*”。
370784************ 370784xxxx04185234
注意：使⽤“单元格格式”对话框的“数字”选项卡设臵单元格格式，只会改变单元格的格式⽽不会影响其中的数值。使⽤函数TEXT可以将数值转换为带格式的⽂本，⽽其结果将不再作为数字参与计算。
例：将20060606格式转换成2006-06-06的样式：可以这样操作
假设数据在A2单元格，在B2单元格中输⼊公式 =text（A1，”-??-??”）回车即可。
例：简单公式15位的=TEXT(MID(A16,7,6),"00-00-00")；如果是18位的则=TEXT(MID(A16,7,8),"00-00-00")。或在15位的基础上再⽤公式：=”19”
⼗⼀、TRIM
⽤途：除了单词之间的单个空格外，清除⽂本中的所有的空格。如果从其他应⽤程序中获得了带有不规则空格的⽂本，可以使⽤TRIM函数清除这些空格。
语法：TRIM(text)。
参数：Text是需要清除其中空格的⽂本。
实例：如果A1=FirstQuarterEarnings，则公式“=TRIM(A1)”返回
“FirstQuarterEarnings
⼗⼆、UPPER
⽤途：将⽂本转换成⼤写形式。
语法：UPPER(text)。
参数：Text为需要转换成⼤写形式的⽂本，它可以是引⽤或⽂字串。
实例：公式“=UPPER("apple")”返回APPLE
⼗三、value
⽤途：将表⽰数字的⽂字串转换成数字。
语法：value(text)。
参数：Text为带引号的⽂本，或对需要进⾏⽂本转换的单元格的引⽤。它可以是Excel可以识别的任意常数、⽇期或时间格式。如果Text不属于上述格式，则value函数返回错误值#value!。
注意：通常不需要在公式中使⽤value函数，Excel可以在需要时⾃动进⾏转换。value函数主要⽤于与其他电⼦表格程序兼容。
实例：公式“=value("￥1，000")”返回1000;=value("16:48:00")-value("12:00:00")返回0.2，该序列数等于4⼩时
48分钟。
⼗四、AVERAGE
⽤途：计算所有参数的算术平均值。
语法：AVERAGE(number1，number2，...)。
参数：Number1、number2、...是要计算平均值的1～30个参数。
实例：如果A1:A5区域命名为分数，其中的数值分别为100、70、92、47和82，
则公式“=AVERAGE(分数)”返回78.2。
⼗五、AVERAGEA
⽤途：计算参数清单中数值的平均值。它与AVERAGE函数的区别在于不仅数字，⽽且⽂本和逻辑值(如TRUE和FALSE)也参与计算。
语法：AVERAGEA(value1，value2，...)
参数：value1、value2、...为需要计算平均值的1⾄30个单元格、单元格区域或数值。
实例：如果A1=76、A2=85、A3=TRUE，则公式“=AVERAGEA(A1:A3)”返回54(即
76+85+1/3=54)
⼗六、COUNT
⽤途：返回数字参数的个数。它可以统计数组或单元格区域中含有数字的单元格个数。
语法：COUNT(value1，value2，...)。
参数：value1，value2，...是包含或引⽤各种类型数据的参数(1～30个)，其中只有数字类型的数据才能被统计。
实例：如果A1=90、A2=⼈数、A3=〞〞、A4=54、A5=36，则公式“=COUNT(A1:A5)”
返回3。
⼗七、COUNTA
⽤途：返回参数组中⾮空值的数⽬。利⽤函数COUNTA可以计算数组或单元格区域中数据项的个数。
语法：COUNTA(value1，value2，...)mid函数提取年月日
说明:value1，value2，...所要计数的值，参数个数为1～30个。在这种情况下的参数可以是任何类型，它们包括空格但不包括空⽩单元格。如果参数是数组或单元格引⽤，则数组或引⽤中的空⽩单元格将被忽略。如果不需要统计逻辑值、⽂字或错误值，则应该使⽤COUNT函数。
实例：如果A1=6.28、A2=3.74，其余单元格为空，则公式“=COUNTA(A1:A7)”
的计算结果等于2。
查看单元格是否空值时⽤=counta（），若返回1，有数据，0则⽆数据。
⼗⼋、COUNTBLANK
⽤途：计算某个单元格区域中空⽩单元格的数⽬。
语法：COUNTBLANK(range)
参数：Range为需要计算其中空⽩单元格数⽬的区域。
实例：如果A1=88、A2=55、A3=""、A4=72、A5=""，则公式“=COUNTBLANK(A1:A5)”
返回2
⼗九、COUNTIF
⽤途：计算区域中满⾜给定条件的单元格的个数。
语法：COUNTIF(range，criteria)

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。