Excel常用函数详解.docx

Excel常用函数详解.docx

《Excel常用函数详解.docx》由会员分享，可在线阅读，更多相关《Excel常用函数详解.docx（35页珍藏版）》请在冰豆网上搜索。

Excel常用函数详解

Excel常用函数详解

为更好发挥计算机的作用，减少不必要的手工计算，提高工作效益，针对我们实际工作中经常使用的Excel电子表格软件，特整理一套Excel常用函数详解及举例说明，希望能给大家一点帮助：

本文主要通过Excel函数的四个方面进行讲解：

1、逻辑函数

2、文本、日期／时间函数

3、查找与引用函数

4、数据库函数

具体内容如下：

1、逻辑函数

用来判断真假值，或进行复合检验的Excel函数，称为逻辑函数。

Excel提供了六种逻辑函数。

即AND、OR、NOT、FALSE、IF、TRUE函数。

  一、AND、OR、NOT函数

     这三个函数都用来返回参数逻辑值。

详细介绍见下：

（一）AND函数

     所有参数的逻辑值为真时返回TRUE；只要一个参数的逻辑值为假即返回FALSE。

简言之，就是当AND的参数全部满足某一条件时，返回结果为TRUE，否则为FALSE。

  语法为AND（logical1,logical2,...），其中Logical1,logical2,...表示待检测的1到30个条件值，各条件值可能为TRUE，可能为FALSE。

参数必须是逻辑值，或者包含逻辑值的数组或引用。

举例说明：

  1、在C2单元格中输入数字50，在D2中写公式=AND（B2>30,B2<60）。

由于B2等于50的确大于30、小于60。

所以两个条件值（logical）均为真，则返回结果为TRUE。

  图1

   2、如果B1-B3单元格中的值为TRUE、FALSE、TRUE，显然三个参数并不都为真，所以在B4单元格中的公式=AND（B1:

B3）等于FALSE

  图2

（二）OR函数

  OR函数指在其参数组中，任何一个参数逻辑值为TRUE，即返回TRUE。

它与AND函数的区别在于，AND函数要求所有函数逻辑值均为真，结果方为真。

而OR函数仅需其中任何一个为真即可为真。

比如，上面的示例2，如果在B4单元格中的公式写为=OR（B1:

B3）则结果等于TRUE

  图3

   （三）NOT函数

  NOT函数用于对参数值求反。

当要确保一个值不等于某一特定值时，可以使用NOT函数。

简言之，就是当参数值为TRUE时，NOT函数返回的结果恰与之相反，结果为FALSE.

  比如NOT（2+2=4），由于2+2的结果的确为4，该参数结果为TRUE，由于是NOT函数，因此返回函数结果与之相反，为FALSE。

  二、TRUE、FALSE函数

  TRUE、FALSE函数用来返回参数的逻辑值，由于可以直接在单元格或公式中键入值TRUE或者FALSE。

因此这两个函数通常可以不使用。

  三、IF函数

（一）IF函数说明

  IF函数用于执行真假值判断后，根据判断结果的真假值返回不同的结果，因此If函数也称之为条件函数。

它的应用很广泛，可以使用函数IF对数值和公式进行条件检测。

  它的语法为IF（logical_test,value_if_true,value_if_false）。

其中Logical_test表示计算结果为TRUE或FALSE的任意值或表达式。

本参数可使用任何比较运算符。

  Value_if_true显示在logical_test为TRUE时返回的值，Value_if_true也可以是其他公式。

Value_if_falselogical_test为FALSE时返回的值。

Value_if_false也可以是其他公式。

  简言之，如果第一个参数logical_test返回的结果为真的话，则执行第二个参数Value_if_true的结果，否则执行第三个参数Value_if_false的结果。

IF函数可以嵌套七层，用value_if_false及value_if_true参数可以构造复杂的检测条件。

  Excel还提供了可根据某一条件来分析数据的其他函数。

例如，如果要计算单元格区域中某个文本串或数字出现的次数，则可使用COUNTIF工作表函数。

如果要根据单元格区域中的某一文本串或数字求和，则可使用SUMIF工作表函数。

（二）IF函数应用

  1、输出带有公式的空白表单

  图5

   以图中所示的人事状况分析表为例，由于各部门关于人员的组成情况的数据尚未填写，在总计栏（以单元格G5为例）公式为：

=SUM（C5:

F5）

  我们看到计算为0的结果。

如果这样的表格打印出来就页面的美观来看显示是不令人满意的。

是否有办法去掉总计栏中的0呢？

你可能会说，不写公式不就行了。

当然这是一个办法，但是，如果我们利用了IF函数的话，也可以在写公式的情况下，同样不显示这些0。

如何实现呢？

只需将总计栏中的公式（仅以单元格G5为例）改写成：

  =IF（SUM（C5:

F5）,SUM（C5:

F5）,""）

  通俗的解释就是：

如果SUM（C5:

F5）不等于零，则在单元格中显示SUM（C5:

F5）的结果，否则显示字符串。

  几点说明：

（1）SUM（C5:

F5）不等于零的正规写法是SUM（C5:

F5）<>0，在EXCEL中可以省略<>0；

（2）""表示字符串的内容为空，因此执行的结果是在单元格中不显示任何字符。

  图4

   2、不同的条件返回不同的结果

  如果对上述例子有了很好的理解后，我们就很容易将IF函数应用到更广泛的领域。

比如，在成绩表中根据不同的成绩区分合格与不合格。

现在我们就以某班级的英语成绩为例具体说明用法。

  图6

  某班级的成绩如图6所示，为了做出最终的综合评定，我们设定按照平均分判断该学生成绩是否合格的规则。

如果各科平均分超过60分则认为是合格的，否则记作不合格。

  根据这一规则，我们在综合评定中写公式（以单元格B12为例）：

  =IF（B11>60,"合格","不合格"）

  语法解释为，如果单元格B11的值大于60，则执行第二个参数即在单元格B12中显示合格字样，否则执行第三个参数即在单元格B12中显示不合格字样。

  在综合评定栏中可以看到由于C列的同学各科平均分为54分，综合评定为不合格。

其余均为合格。

  3、多层嵌套函数的应用

  在上述的例子中，我们只是将成绩简单区分为合格与不合格，在实际应用中，成绩通常是有多个等级的，比如优、良、中、及格、不及格等。

有办法一次性区分吗？

可以使用多层嵌套的办法来实现。

仍以上例为例，我们设定综合评定的规则为当各科平均分超过90时，评定为优秀。

如图7所示。

  图7

   说明：

为了解释起来比较方便，我们在这里仅做两重嵌套的示例，您可以按照实际情况进行更多重的嵌套，但请注意Excel的IF函数最多允许七重嵌套。

  根据这一规则，我们在综合评定中写公式（以单元格F12为例）：

  =IF（F11>60,IF（AND（F11>90）,"优秀","合格"）,"不合格"）

  语法解释为，如果单元格F11的值大于60，则执行第二个参数，在这里为嵌套函数，继续判断单元格F11的值是否大于90（为了让大家体会一下AND函数的应用，写成AND（F11>90），实际上可以仅写F11>90），如果满足在单元格F12中显示优秀字样，不满足显示合格字样，如果F11的值以上条件都不满足，则执行第三个参数即在单元格F12中显示不合格字样。

  在综合评定栏中可以看到由于F列的同学各科平均分为92分，综合评定为优秀。

  （三）根据条件计算值

  在了解了IF函数的使用方法后，我们再来看看与之类似的Excel提供的可根据某一条件来分析数据的其他函数。

例如，如果要计算单元格区域中某个文本串或数字出现的次数，则可使用COUNTIF工作表函数。

如果要根据单元格区域中的某一文本串或数字求和，则可使用SUMIF工作表函数。

关于SUMIF函数在数学与三角函数中以做了较为详细的介绍。

这里重点介绍COUNTIF的应用。

  COUNTIF可以用来计算给定区域内满足特定条件的单元格的数目。

比如在成绩表中计算每位学生取得优秀成绩的课程数。

在工资表中求出所有基本工资在2000元以上的员工数。

  语法形式为COUNTIF（range,criteria）。

其中Range为需要计算其中满足条件的单元格数目的单元格区域。

Criteria确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本。

例如，条件可以表示为32、"32"、">32"、"apples"。

  1、成绩表

  这里仍以上述成绩表的例子说明一些应用方法。

我们需要计算的是：

每位学生取得优秀成绩的课程数。

规则为成绩大于90分记做优秀。

如图8所示   

根据这一规则，我们在优秀门数中写公式（以单元格B13为例）：

  =COUNTIF（B4:

B10,">90"）

  语法解释为，计算B4到B10这个范围，即jarry的各科成绩中有多少个数值大于90的单元格。

  在优秀门数栏中可以看到jarry的优秀门数为两门。

其他人也可以依次看到。

  图8

  2、销售业绩表

  销售业绩表可能是综合运用IF、SUMIF、COUNTIF非常典型的示例。

比如，可能希望计算销售人员的订单数，然后汇总每个销售人员的销售额，并且根据总发货量决定每次销售应获得的奖金。

  原始数据表如图9所示（原始数据是以流水单形式列出的，即按订单号排列）

  图9

   按销售人员汇总表如图10所示

  图10

   如图10所示的表完全是利用函数计算的方法自动汇总的数据。

首先建立一个按照销售人员汇总的表单样式，如图所示。

然后分别计算订单数、订单总额、销售奖金。

（1）订单数--用COUNTIF计算销售人员的订单数。

  以销售人员ANNIE的订单数公式为例。

公式：

=COUNTIF（$C$2:

$C$13,A17）

  语法解释为计算单元格A17（即销售人员ANNIE）在"销售人员"清单$C$2:

$C$13的范围内（即图9所示的原始数据表）出现的次数。

  这个出现的次数即可认为是该销售人员ANNIE的订单数。

（2）订单总额--用SUMIF汇总每个销售人员的销售额。

  以销售人员ANNIE的订单总额公式为例。

公式：

  =SUMIF（$C$2:

$C$13,A17,$B$2:

$B$13）

  此公式在"销售人员"清单$C$2:

$C$13中检查单元格A17中的文本（即销售人员ANNIE），然后计算"订单金额"列（$B$2:

$B$13）中相应量的和。

  这个相应量的和就是销售人员ANNIE的订单总额。

  （3）销售奖金--用IF根据订单总额决定每次销售应获得的奖金。

  假定公司的销售奖金规则为当订单总额超过5万元时，奖励幅度为百分之十五，否则为百分之十。

根据这一规则仍以销售人员ANNIE为例说明。

公式为：

  =IF（C17<50000,10%,15%）*C17

  如果订单总额小于50000则奖金为10%；如果订单总额大于等于50000，则奖金为15%。

  至此，我们已完全了解了EXCEL函数的逻辑函数，相信大家在实际工作中会想出更多更有用的运用。

2、文本、日期/时间函数

文本函数，就是在公式中处理文字串的函数。

如，改变大小写或确定文字串的长度；可以替换某些字符或者去除某些字符等。

日期和时间函数则可在公式中分析和处理日期值和时间值。

具体介绍如下：

  一、文本函数

（一）大小写转换

  LOWER--将一个文字串中的所有大写字母转换为小写字母。

  UPPER--将文本转换成大写形式。

  PROPER--将文字串的首字母及任何非字母字符之后的首字母转换成大写。

将其余的字母转换成小写。

  这三种函数的基本语法形式均为“函数名（text）”。

示例说明：

  已有字符串为：

pLeaseComEHere!

这句话大小写乱用了。

  通过以上三个函数可以将文本转换显示样式，使得文本变得规范。

参见图1

  Lower（pLeaseComEHere!

）=pleasecomehere!

  upper（pLeaseComEHere!

）=PLEASECOMEHERE!

  proper（pLeaseComEHere!

）=PleaseComeHere!

  图1

（二）取出字符串中的部分字符

  您可以使用Mid、Left、Right等函数从长字符串内获取一部分字符。

具体语法格式为

  LEFT函数：

LEFT（text,num_chars）其中Text是包含要提取字符的文本串。

Num_chars指定要由LEFT所提取的字符数。

  MID函数：

MID（text,start_num,num_chars）其中Text是包含要提取字符的文本串。

Start_num是文本中要提取的第一个字符的位置。

  RIGHT函数：

RIGHT（text,num_chars）其中Text是包含要提取字符的文本串。

Num_chars指定希望RIGHT提取的字符数。

  如，从字符串"Thisisanapple."分别取出字符"This"、"apple"、"is"的具体函数写法为。

LEFT（"Thisisanapple",4）=This

  RIGHT（"Thisisanapple",5）=apple

  MID（"Thisisanapple",6,2）=is

  图2

（三）去除字符串的空白

  在字符串形态中，空白也是一个有效的字符，但是如果字符串中出现空白字符时，容易在判断或对比数据是发生错误，在Excel中您可以使用Trim函数清除字符串中的空白。

  语法形式为：

TRIM（text）其中Text为需要清除其中空格的文本。

  需要注意的是，Trim函数不会清除单词之间的单个空格，如果连这部分空格都需清除的话，建议使用替换功能。

比如，从字符串"MynameisMary"中清除空格的函数写法为：

TRIM（"MynameisMary"）=MynameisMary参见图3

  图3

（四）字符串的比较

  在数据表中经常会对比不同的字符串，此时可以使用EXACT函数来比较两个字符串是否相同。

该函数测试两个字符串是否完全相同。

如果它们完全相同，则返回TRUE；否则返回FALSE。

函数EXACT能区分大小写，但忽略格式上的差异。

语法形式为：

EXACT（text1,text2）其中Text1为比较的第一个字符串。

Text2为比较的第二个字符串。

举例说明：

参见图4

  EXACT（"China","china"）=False

  图4

二、日期与时间函数

  在数据表的处理过程中，日期与时间的函数是相当重要的处理依据。

Excel在这方面提供了丰富的函数供大家使用。

（一）取出当前系统时间/日期信息

  用于取出当前系统时间/日期信息的函数主要有NOW、TODAY。

  语法形式均为函数名（）。

（二）取得日期/时间的部分字段值

  如果需要单独的年份、月份、日数或小时的数据时，可以使用HOUR、DAY、MONTH、YEAR函数直接从日期/时间中取出需要的数据。

具体示例参看图5。

  比如，需返回2001-5-3012:

30PM的年份、月份、日数及小时数，可以分别采用相应函数实现。

  YEAR（E5）=2001

  MONTH（E5）=5

  DAY（E5）=30

  HOUR（E5）=12

  图5

 三、示例：

辅助生成的人事资料分析表

  1、示例说明

  在如图6所示的某公司人事资料表中，除了编号、员工姓名、身份证号码以及参加工作时间为手工添入外，其余各项均为用函数计算所得。

  图6

在此例中将详细说明如何通过函数求出：

（1）自动从身份证号码中提取出生年月、性别信息。

（2）自动从参加工作时间中提取工龄信息。

  2、身份证号码相关知识

  在了解如何实现自动从身份证号码中提取出生年月、性别信息之前，首先需要了解身份证号码所代表的含义。

我们知道，当今的身份证号码有15/18位之分。

早期签发的身份证号码是15位的，现在签发的身份证由于年份的扩展（由两位变为四位）和末尾加了效验码，就成了18位。

这两种身份证号码将在相当长的一段时期内共存。

两种身份证号码的含义如下：

（1）15位的身份证号码：

1~6位为地区代码，7~8位为出生年份（2位），9~10位为出生月份，11~12位为出生日期，第13~15位为顺序号，并能够判断性别，奇数为男，偶数为女。

（2）18位的身份证号码：

1~6位为地区代码，7~10位为出生年份（4位），11~12位为出生月份，13~14位为出生日期，第15~17位为顺序号，并能够判断性别，奇数为男，偶数为女。

18位为效验位。

  3、应用函数

  为了实现数据的自动提取，应用以下几个Excel函数。

（1）IF函数：

根据逻辑表达式测试的结果，返回相应的值。

IF函数允许嵌套。

（2）CONCATENATE：

将若干个文字项合并至一个文字项中。

  （3）MID：

从文本字符串中指定的起始位置起，返回指定长度的字符。

  （4）TODAY：

返回计算机系统内部的当前日期。

  （5）DATEDIF：

计算两个日期之间的天数、月数或年数。

  （6）VALUE：

将代表数字的文字串转换成数字。

  （7）RIGHT：

根据所指定的字符数返回文本串中最后一个或多个字符。

  （8）INT：

返回实数舍入后的整数值。

语法形式为：

INT（number）

  4、公式写法及解释（以员工Andy为例说明）

  说明：

为避免公式中过多的嵌套，这里的身份证号码限定为15位的。

如果您看懂了公式的话，可以进行简单的修改即可适用于18位的身份证号码，甚至可适用于15、18两者并存的情况。

（1）根据身份证号码求性别

  =IF（VALUE（RIGHT（E4,3））/2=INT（VALUE（RIGHT（E4,3））/2）,"女","男"）

  公式解释：

a.RIGHT（E4,3）用于求出身份证号码中代表性别的数字，实际求得的为代表数字的字符串

  b.VALUE（RIGHT（E4,3）用于将上一步所得的代表数字的字符串转换为数字

  c.VALUE（RIGHT（E4,3））/2=INT（VALUE（RIGHT（E4,3））/2用于判断这个身份证号码是奇数还是偶数，当然你也可以用Mod函数来做出判断。

  d.=IF（VALUE（RIGHT（E4,3））/2=INT（VALUE（RIGHT（E4,3））/2）,"女","男"）及如果上述公式判断出这个号码是偶数时，显示"女"，否则，这个号码是奇数的话，则返回"男"。

（2）根据身份证号码求出生日期

  =CONCATENATE（"19",MID（E4,7,2）,"/",MID（E4,9,2）,"/",MID（E4,11,2））

  公式解释：

a.MID（E4,7,2）为在身份证号码中获取表示年份的数字的字符串

  b.MID（E4,9,2）为在身份证号码中获取表示月份的数字的字符串

  c.MID（E4,11,2）为在身份证号码中获取表示日期的数字的字符串

  d.CONCATENATE（"19",MID（E4,7,2）,"/",MID（E4,9,2）,"/",MID（E4,11,2））目的就是将多个字符串合并在一起显示。

  （3）根据参加工作时间求年资（即工龄）

  =CONCATENATE（DATEDIF（F4,TODAY（）,"y"）,"年",DATEDIF（F4,TODAY（）,"ym"）,"个月"）

  公式解释：

  a.TODAY（）用于求出系统当前的时间

  b.DATEDIF（F4,TODAY（）,"y"）用于计算当前系统时间与参加工作时间相差的年份

  c.DATEDIF（F4,TODAY（）,"ym"）用于计算当前系统时间与参加工作时间相差的月份，忽略日期中的日和年。

  d.=CONCATENATE（DATEDIF（F4,TODAY（）,"y"）,"年",DATEDIF（F4,TODAY（）,"ym"）,"个月"）目的就是将多个字符串合并在一起显示。

3、查找与引用函数

有关引用的知识：

  1、引用的作用

在Excel中引用的作用在于标识工作表上的单元格或单元格区域，并指明公式中所使用的数据的位置。

通过引用，可以在公式中使用工作表不同部分的数据，或者在多个公式中使用同一单元格的数值。

还可以引用同一工作簿不同工作表的单元格、不同工作簿的单元格、甚至其它应用程序中的数据。

  2、引用的含义

关于引用需要了解如下几种情况的含义：

外部引用--不同工作簿中的单元格的引用称为外部引用。

  远程引用--引用其它程序中的数据称为远程引用。

  相对引用--在创建公式时，单元格或单元格区域的引用通常是相对于包含公式的单元格的相对位置。

  绝对引用--如果在复制公式时不希望Excel调整引用，那么请使用绝对引用。

即加入美元符号，如$C$1。

  3、引用的表示方法

关于引用有两种表示的方法，即A1和R1C1引用样式。

（1）引用样式一（默认）--A1

A1的引用样式是Excel的默认引用类型。

这种类型引用字母标志列（从A到IV，共256列）和数字标志行（从1到65536）。

这些字母和数字被称为行和列标题。

如果要引用单元格，请顺序输入列字母和行数字。

例如，C25引用了列C和行25交叉处的单元格。

如果要引用单元格区域，请输入区域左上角单元格的引用、冒号（：

）和区域右下角单元格的引用，如A20:

C35。

（2）引用样式二--R1C1

在R1C1引用样式中，Excel使用"R"加行数字和"C"加列数字来指示单元格的位置。

例如，单元格绝对引用R1C1与A1引用样式中的绝对引用$A$1等价。

如果活动单元格是A1，则单元格相对引用R[1]C[1]将引用下面一行和右边一列的单元格，或是B2。

  在了解了引用的概念后，我们来看看Excel提供的查询与引用函数。

查询与引用函数可以用来在数据清单或表格中查找特定数值，或者需要查找某一单元格的引用。

Excel中一共提供了ADDRESS、AREAS、CHOOSE、COLUMN、COLUMNS、HLOOKUP、HYPERLINK、INDEX、INDIRECT、LOOKUP、MATCH、OFFSET、R