Excel 2016应用大全 – 第37页

Excel 计算两个日期之间的天数：DAYS360函数

DAYS360函数用于按照一年365天进行计算，用于返回两个日期之间相差的天数，其语法是DAYS360（start_date，ent_date，method）。其中，参数start_date为计算日期的起始时间；参数ent_date为计算日期的终止时间；参数method为用于计算方法的逻辑值。FALSE或忽略表示使用美国方法，TRUE则是使用欧洲方法。

步骤1：某公司财务人员需要计算每种固定资产使用的具体天数，由于数据庞大，使用人工计算比较复杂，下面将利用DAYS360函数，分别使用美国与欧洲方法计算每种固定资产使用天数，输入本例的原始数据，如图10-18所示。

步骤2：选中E2单元格，在编辑栏中输入“=DAYS360（C2，D2）”，然后按Enter键，返回美国方法计算的两日期之间的天数，如图10-19所示。

步骤3：选中E2单元格，移动鼠标指针至单元格右下角，当鼠标指针变成黑色“+”时，按住鼠标左键拖动光标至单元格D7处，返回所有美国方法计算的两日期之间的天数，如图10-20所示。

步骤4：选中F2单元格，在编辑栏中输入“=DAYS360（C2，D2，TRUE）”，然后按Enter键，返回欧洲方法计算的两日期之间的天数，如图10-21所示。

图10-18　含有固定资产列表的工作表

图10-19　返回美国方法计算两日期之间的天数

图10-20　返回所有美国方法计算两日期之间的天数

图10-21　返回欧洲方法计算的两日期之间的天数

步骤5：选中F2单元格，移动鼠标指针至单元格右下角，当鼠标指针变成黑色“+”时，按住鼠标左键拖动光标至单元格D7处，返回所有欧洲方法计算的两日期之间的天数，如图10-22所示。

图10-22　返回欧洲方法计算的所有两日期之间的天数

注意：此函数一般适用于财务领域。

Excel 将非日期数据转换为标准日期：DATE函数

为了实现快速输入，在输入日期数据的时候，都采用了类似20160522、20160220、20160325、20160528的形式，在完成数据输入后，需要将其转换为标准的日期格式，利用前面介绍的DATE函数，可以方便地实现该功能。

步骤1：输入原始数据，如图10-15所示。

步骤2：选中B2单元格，在公式编辑栏中输入公式“=DATE（MID（A2，1，4），MID（A2，5，2），MID（A2，7，2））”。按Enter键即可将非日期数据转换为标准的日期，如图10-16所示。

步骤3：选中B2单元格，然后利用自动填充功能对单元格进行自动填充，结果如图10-17所示。

图10-15　原始数据

图10-16　转换日期

图10-17　最终结果

Excel 将数值转换为日期格式：DATE函数

DATE函数是用于返回代表特定日期的序列号，其语法如下：DATE（year，month，day），其中参数year是1~4位数字，Excel会根据当前所使用的日期系统来解释year参数。参数month代表一年中从1月到12月各月的正整数或负整数。参数day代表一月中从1日到31日各天的正整数或负整数，利用该函数，可以将数值转换为日期格式，下面通过实例来具体讲解该函数的操作技巧。

步骤1：首先输入本例的原始数据，如图10-12所示。

步骤2：选中D2单元格，在公式编辑栏中输入公式：“=DATE（A2，B2，C2）”。按Enter键即可将指定单元格中的数据转换为日期格式，如图10-13所示。

图10-12　原始数据

图10-13　返回对应日期

步骤3：选中D2单元格，然后利用自动填充功能，对其他单元格进行自动填充，其最终结果如图10-14所示。

图10-14　返回对应结果

Excel 将文本格式的日期转换为序列号：DATEVALUE函数

DATEVALUE函数用于将以文本格式表示的日期转换成序列号，其语法是DATEVALUE（date text）。其中，参数date text为以文本格式表示的日期，介于1900-1-1~1999-12-31或1904-1-1~1999-12-31，下面通过实例来具体讲解该函数的操作技巧。

步骤1：某读者想计算几个不同年份距离2016-12-31的确定天数。如果利用计算器或人工计算，比较费劲，下面利用DATEVALUE函数计算几个不同年份距离2016-12-31的天数，首先在工作表中分别输入年份，如图10-6所示。

步骤2：选中C2单元格，在编辑栏中输入“=DATEVALUE（”2016-12-31″）-DATEVALUE（”1900-1-1″）”，然后按Enter键，返回1900-1-1~2016-12-31之间天数，如图10-7所示。

图10-6　含有年份的工作表

图10-7　返回单元格B2对应结果

步骤3：选中C3单元格，在编辑栏中输入“=DATEVALUE（”2016-12-31″）-DATEVALUE（”1921-3-5″）”，然后按Enter键，返回1921-3-5~2016-12-31之间天数，如图10-8所示。

步骤4：选中C4单元格，在编辑栏中输入“=DATEVALUE（”2016-12-31″）-DATEVALUE（”1949-10-1″）”，然后按Enter键，返回1949-10-1~2016-12-31之间天数，如图10-9所示。

图10-8　返回单元格B3对应结果

图10-9　返回单元格B4对应结果

步骤5：选中C5单元格，在编辑栏中输入“=DATEVALUE（”2016-12-31″）-DATEVALUE（”1956-12-31″）”，然后按Enter键，返回1956-12-31~2016-12-31之间天数，如图10-10所示。

步骤6：选中C6单元格，在编辑栏中输入“=DATEVALUE（”2016-12-31″）-DATEVALUE（”2006-12-31″）”，然后按Enter键，返回2006-12-31~2016-12-31之间天数，如图10-11所示。

图10-10　返回单元格B5对应结果

图10-11　返回单元格B6对应结果

注意：此函数适用于将文本格式的日期转换成序列号，便于管理与统计。

Excel 显示任意日期：DAY函数

DAY函数用于返回指定任意日期的天数，介于1~31之间，其语法是DAY（serial number）。其中，参数serial number为要进行查找的日期，下面通过实例来具体讲解该函数的操作技巧。

步骤1：已知某公司员工的姓名及出生日期，利用函数DAY，返回员工生日的具体天数，首先输入本例的原始数据，如图10-3所示。

图10-3　含有出生日期的工作表

步骤2：选中D2单元格，在编辑栏中输入“=DAY（C2）”，然后按Enter键，返回具体天数，如图10-4所示。

步骤3：利用Excel的自动填充功能，计算其他单元格的结果，如图10-5所示。

图10-4　返回单元格C2对应的具体天数

图10-5　返回其他单元格对应的具体天数

注意：此函数常用于配合其他日期函数使用。

Excel 显示当前系统日期：TODAY函数

TODAY函数用于返回系统当前日期，其语法是TODAY（），该函数没有参数，下面通过实例来具体讲解该函数的操作技巧。

步骤1：某公司财务人员在制作年终报表的时候，需要记录当前修改日期，下面利用TODAY函数，记录当天修改的日期，首先输入本例的原始数据，如图10-1所示。

步骤2：选中单元格F2，在编辑栏中输入“=TODAY（）”，然后按Enter键，返回当前修改日期，如图10-2所示。

图10-1　员工资料表

图10-2　返回当前修改日期

注意：此函数广泛适用于人事及财务领域。但此函数所返回的当前日期是指当前计算机中的日期。

有时，当用户打开一个工作簿但不做任何更改就关闭时，Excel却提醒是否保存。这是因为Excel文件用到了一些具有volatile特性的函数，即易失性函数。这种函数一个典型的特点是：使用这些函数以后，会引发工作表的重新计算。因此没激活一个单元格，或者在一个单元格输入数据，甚至只是打开工作簿，具有易失性的函数都会自动重新计算。常见的易失性函数有NOW（）、TODAY（）、RAND（）、CELL（）、OFFSET（）、INDIRECT（）、INFO（）、RANDBETWEEN（）等。

虽然易失性函数在实际应用中非常有用，但是如果大量使用易失性函数，则会因为重新计算工作量太大而影响表格的运行速度。

Excel 巧妙处理函数参数

在函数的实际使用过程中，并非总是需要将一个函数的所有参数都写完整才可以计算，可以根据需要对参数进行省略和简化，以达到缩短公式长度或者减少计算步骤的目的。本节将具体讲解如何省略、简写以及简化函数参数。

函数的帮助文件会将其各个参数表达的意思和要求罗列出来，仔细看看就会发现，有很多参数的描述包括“忽略”、“省略”、“默认”等词，而且会注明，如果省略该参数，则表示默认该参数代表某个值。对于参数的省略，是指该参数连同该参数存在所需的逗号间隔都不出现在函数中。

例：判断B2是否与A2的值相等，如果是则返回TRUE，否则返回FALSE。

=IF(B2=A2,TRUE,FALSE)

可以省略为：

=IF(B2=A2,TRUE)

部分函数中的参数为TRUE或者FALSE，比如VLOOKUP函数的参数range_lookup。当指定其为FALSE的时候，可以用“0”来替代。甚至连“0”也不写，而只是用逗号占据参数位置。下面3个公式是等价的。

= VLOOKUP(A1,B1:C10,2,FALSE)
= VLOOKUP(A1,B1:C10,2,0)
= VLOOKUP(A1,B1:C10,2,)

此外，有些针对数值的逻辑判断，可利用“0=FALSE”和“非0数值=TRUE”的规则来简化，比如已知A1单元格的数据只可能是数值的前提下，可以将公式=IF（A1<>0，B1/A1，””），简化为=IF（A1，B1/A1，””）。

Excel 浅谈函数公式的限制和突破

Excel在公式计算方面有其自身的标准与规范，这些规范对公式的编写有一定的限制，主要包括以下几个方面。

公式内容的长度不能超过1024个字符。
公式中函数的嵌套不能超过7层。
公式中函数的参数不能超过30个。

正是因为上面的这些限制条件，使得用户在操作Excel的过程中存在诸多问题。下面以具体实例来详细讲解如何突破函数的7层嵌套以及30个函数参数的限制。

当函数B在函数A中用作参数的时候，函数B为第2级函数。如公式“=IF（A1>0，SUM（B1，G1），””）”，其中SUM函数是第2级函数，因为它是IF函数的参数。如果在SUM函数中继续嵌套函数则为第3级函数，以此类推，Excel函数公式可以包含多达7级的嵌套函数。函数的7层嵌套限制了使用多个函数，要解决这个问题，可以通过定义名称的方法来实现。

例如，要将A1中字符串“我113爱322学43习75E56x5353c256382e85626l54”的数字去掉，利用SUBSTITUTE函数来解决，在B1中输入公式“=SUBSTITUTE（SUBSTITUTE（SUBSTITUTE（SUBSTITUTE（SUBSTITUTE（SUBSTITUTE（SUBSTITUTE（SUBSTITUTE（A1，0，），1，），2，），3，），4，），5，），6，），7，）”，之后就不能直接再套用函数了，因为从第2个SUBSTITUTE开始，每一个都是前一个的参数，已经达到了7级嵌套。此时，切换到“公式”选项卡，然后单击“定义名称”按钮，打开“新建名称”对话框，将上面的名称定义为X，然后在B1中输入“=SUBSTITUTE（SUBSTITUTE（X，8，），9，）”，这样就可以得到去掉数字的字符串“我爱学习Excel了！”。同样，利用名称定义的方法可解决用IF判断，以及其他函数因为嵌套层数超过7层而导致公式无法输入的问题。

上面介绍了如何突破函数的7层嵌套限制，在实际应用过程中，用户还会受到Excel中30个函数参数的限制，下面简单介绍如何突破30个函数参数的限制。

Excel规定的函数参数最多为30个，如SUM函数、COUNT函数、COUNTA函数、AVERAGE函数、CHOOSE函数等。例如需要计算某些特定单元格中数值的平均值，有以下公式“=AVERAGE（A1，A2，B1，B3，B6，C6，D8，…）”，其中，括号里面的参数多于30个。这个时候，可以在函数参数的两边加上一对括号，形成联合区域来作为参数，相当于只有1个参数。这样，公式就只受到字符个数的限制了。

Excel 熟练掌握运算符号

对公式中的元素进行特定类型的运算，就需要用到特定类型的运算符号。在Excel 2016中，包括4种类型的运算符：算术运算符、比较运算符、文本运算符和引用运算符。在使用运算符的过程中，Excel将根据公式中的特定顺序进行计算。如果公式中存在多个运算符，Excel将根据表9-2所示的顺序进行计算。

表9-2　Excel运算符

上面简单介绍了Excel运算符的有关属性，接下来以通配符为例，具体讲解一下Excel在使用过程中的有关技巧。在Excel 2016中，*（星号）和？（问号）都可以作为通配符来使用，用于查找、统计等运算的比较条件中。下面分别加以介绍。

·*（星号）表示任何字符

例：计算A1：A8中以A开头的记录个数，其公式如下所示

=COUNTIF(A1:A8,"A*")

·？（问号）表示任何单个字符

例：计算A1：A8中第2个字母是A的记录个数公式：

=COUNTIF(A1:A8,"?A*")

分类：Excel 2016应用大全