Excel函数应用大全整理篇.docx
《Excel函数应用大全整理篇.docx》由会员分享，可在线阅读，更多相关《Excel函数应用大全整理篇.docx（25页珍藏版）》请在冰豆网上搜索。
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
二十八：
ISERRORandfind函数
二十九、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。
Num_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分解成15yearsold三部分分别存放于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"）
370784************
370784xxxx04185234
八、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是所要选用的文本型数字格式，即“单元格格式”对话框“数字”选项卡的“分类”列表框中显示的格式，它不能包含星号“*”。
注意：
使用“单元格格式”对话框的“数字”选项卡设置单元格格式，只会改变单元格的格式而不会影响其中的数值。
使用函数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”&A1
十一、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，...）
说明:
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）
参数：
Rang为需要计算其中满足条件的单元格数目的单元格区域。
Criteria为确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本。
统计某个数据的个数如统计一级教师的个数
=countif（a2：
t123，“一级教师”）
统计某些单元格数据区域符合指定条件的数据的个数：
=countif（$A$1:
$A$24,”一年级”）。
即求：
在A1：
A24数据区域中，是一年级的数据的个数。
再如统计>98的学生人数
=COUNTIF（C2:
C36,">98"）
注意:
条件除纯数字外必须用“”引起来。
两个条件求个数
（1） >10并且<=15 =SUM（COUNTIF（data,">"&{10,15}）*{1,-1}）
（2） >=10并且<15 =SUM（COUNTIF（data,">="&{10,15}）*{1,-1}）
（3） >=10并且<=15 =SUM（COUNTIF（data,{">=10",">15"}）*{1,-1}）
（4） >10并且<15 =SUM（COUNTIF（data,{">10",">=15"}）*{1,-1}）
举例：
防止输入重复身份证号码
在单位制作员工花名册等表格时，员工的身份证号码应该是唯一的，我们可以通过“数据有效性”来防止重复输入:
选中需要输入身份证号码的单元格区域（如D2至D101），执行“数据→有效性”命令，打开“数据有效性”对话框，在“设置”标签下，按“允许”右侧的下拉按钮，在随后弹出的快捷菜单中，选择“自定义”选项，然后在下面“公式”方框中输入公式:
=COUNTIF（D:
D,D2）=1，确定返回。
以后在上述单元格中输入了重复的身份证号码时，系统会弹出提示对话框（如图1），并拒绝接受输入的号码。
再如：
在excel表中，比较B列和A列的数据，把A列中在B列包含的数据在C列中表示出来
=IF（COUNTIF（B:
B,A1&"*"）,A1,""）
如果是让B列中在A列包含的数据在C列中显示出来只要修改一下公式即可:
=IF（COUNTIF（A:
A,B1&"*"）,B1,""）
二十、DAVERAGE
参数:
返回数据库或数据清单中满足指定条件的列中数值的平均值。
语法:
DAVERAGE（database，field，criteria）
参数:
Database构成列表或数据库的单元格区域。
Field指定函数所使用的数据列。
Criteria为一组包含给定条件的单元格区域。
二十一、DCOUNT
参数:
返回数据库或数据清单的指定字段中，满足给定条件并且包含数字的单元格数目。
语法:
DCOUNT（database，field，criteria）
参数:
Database构成列表或数据库的单元格区域。
Field指定函数所使用的数据列。
Criteria为一组包含给定条件的单元格区域。
二十二、DCOUNTA
参数:
返回数据库或数据清单指定字段中满足给定条件的非空单元格数目。
语法:
DCOUNTA（database，field，criteria）
参数:
Database构成列表或数据库的单元格区域。
Field指定函数所使用的数据列。
Criteria为一组包含给定条件的单元格区域。
二十三、DMAX
参数:
返回数据清单或数据库的指定列中，满足给定条件单元格中的最大数值。
语法:
DMAX（database，field，criteria）
参数:
Database构成列表或数据库的单元格区域。
Field指定函数所使用的数据列。
Criteria为一组包含给定条件的单元格区域。
二十四、DMIN
参数:
返回数据清单或数据库的指定列中满足给定条件的单元格中的最小数字。
语法:
DMIN（database，field，criteria）
参数:
Database构成列表或数据库的单元格区域。
Field指定函数所使用的数据列。
Criteria为一组包含给定条件的单元格区域。
二十五、DSUM
参数:
返回数据清单或数据库的指定列中，满足给定条件单元格中的数字之和。
语法:
DSUM（database，field，criteria）
二十六、mod函数
MOD
返回两数相除的余数。
结果的正负号与除数相同。
语法
MOD（number,divisor）
MOD函数语法具有下列参数：
Number 必需。
被除数。
Divisor 必需。
除数。
说明
如果divisor为零，函数MOD返回错误值#DIV/0!
。
函数MOD可以借用函数INT来表示:
MOD（n,d）=n-d*INT（n/d）
举例：
=IF（MOD（MID（A2,17,1）,2）=1,"男","女"）假设数据单元格在A2，存放性别在C2。
=（MID（A2,17,1）,2）=1,"男","女"说明：
提取A2单元格从第17位开始的1位数字；
=MOD（MID（A2,17,1）,2）=1，说明：
17位数字除以2，如果余1。
整个公式就是如果检测A2单元格的17位开始的后一位数字被2除余1则在C2中显示“男”，否则显示“女”。
二十七、SUMIF
sumif函数的用法是根据指定条件对若干单元格、区域或引用求和。
sumif函数语法是：
SUMIF（range，criteria，sum_range）
sumif函数的参数如下：
第一个参数：
Range为条件区域，用于条件判断的单元格区域。
第二个参数：
Criteria是求和条件，由数字、逻辑表达式等组成的判定条件。
第三个参数：
Sum_range为实际求和区域，需要求和的单元格、区域或引用。
当省略第三个参数时，则条件区域就是实际求和区域。
criteria参数中使用通配符（包括问号（?
）和星号（*））。
问号匹配任意单个字符；星号匹配任意一串字符。
如果要查找实际的问号或星号，请在该字符前键入波形符（~）。
举例：
求某些单元格中数值的和：
=sumif（A1:
A24,100）。
即求A1：
A24单元格中分数是100分的所有人数的总和。
注意：
此处的100，因为是数字，可以不用“”，如果是非数字必须用“”引起来。
求报表中各栏目的总流量
如下图：
选中F2单元格，输入公式：
=SUMIF（B2:
B19,E2,C2:
C19），输入公式完成后，按键盘上ctrl+shift+enter组合键（必须按此ctrl+shift+enter组合键数组公式才能得到正确结果），即可统计出办公软件栏目的总流量。
以此类推，选中F3单元格，输入公式：
=SUMIF（B2:
B19,E3,C2:
C19），输入公式完成后，按键盘上ct
升级会员 