EXCEL,EDATE和EOMONTH到底能不能返回数组

今天和大家来分享高级篇，高级篇嘛，当然是以烧脑为主，介绍一些在论坛和QQ群困扰了很多人的公式错误原因。

1，EDATE和EOMONTH到底能不能返回数组？

有些时候需要引用数据源返回相关的数组结果，但很多小盆友引用了区域后发现EOMONTH这些函数结果是报错的。

因此得出一个结论，这批函数在引用区域后是不能返回数组的。

但是，这只是因为这批函数性质特殊而已，稍微加点料就可以返回数组了。

数据源区域只要*1，- -等把直接引用转化为内存数组，就可以使这批函数在引用区域后成功返回数组结果。

这是个很偏门的性质，具有这种性质的函数有EDATE，EOMONTH，WEEKNUM，QUOTIENT/DELTA等等。共同点是，这批函数在2003版需要加载工具库才能使用，这或许就是这批函数具有这个奇葩性质的原因。

2)高精度浮点

这个性质也是坑过无数老司机的：

这个性质在于，这种浮点数在目前函数阶段，F9，公式求值，格式小数位数，等号比较等等多种方法都无法检测出来，但就是会造成MATCH，VLOOKUP，RANK，MODE，FREQUENCY等函数的结果异常。

因为这种浮点超过了Excel的15位精度，所以无法显示，但部分函数还是会识别这种差异，重点是影响上面几个函数的结果(这种精度差异可以使用DELTA函数检查)。

解决方法是使用ROUND等修正这类数值的实际精度，或者换成不识别这种精度的函数或判断式(等号比较不识别这种差异)。

3)真伪内存数组和365动态数组性质差异

365前的版本VLOOKUP和INDEX等函数有一类性质。如果VLOOKUP的第1参数和INDEX的第2/3参数，直接使用数组，并不能使整个VLOOKUP或INDEX返回内存数组。

虽然可以以区域数组方式录入多个单元格返回对应的多个结果，但并不能作为真内存数组用于后续计算，因此这个性质被称为伪内存数组。

如果要把INDEX的结果变成真内存数组，必须加N/T+IF结构来进行转化。

而自从出现了365版本，真伪内存数组性质发生了变化：

即365下部分公式使用三键和不使用三键，结果是会有区别的，

即通常在不使用三键环境下，365版本会把早期版本的伪内存结构直接形成真内存数组，但如果使用三键，还是早期版本的伪内存数组性质，这个性质主要在早期版本的伪内存数组和多维引用两种公式结构下存在。

4) 1和{1}的区别

这类性质，通常出现在涉及INDIRECT或OFFSET的动态引用问题内，INDIRECT或OFFSET函数配合ROW或COLUMN函数时容易出现。

很多人会被这个错误困扰不知道如何解决，其实这个公式之所以错误，根源在于ROW函数，因为ROW(A1)的结果为{1}，注意这是1个单元素的数组。

而INDIRECT和OFFSET都是容易构造多维引用的，所以目前的观点上面公式错误和这两个函数的多维引用下的降维理论有关。

解决方法也很简单，1是在ROW外面套个聚合函数，SUM/MAX/MIN等等均可，目的是把{1}变成常量1，就不会出现类似错误了。

5)时间精度

Excel里的时间精度到千分之一毫秒，即YYYY/MM/DDHH:MM:SS.000

到了时间阶段，Excel里的日期函数会出现两大阵营。部分日期函数对日期的识别仅到日期部分，不考虑时间范围。

但还有部分日期函数，对日期的识别精度达到了这个毫秒级，时间不大于23:59:59.499的日期识别为当天，但23:59:59.500-23:59:59.999的范围会被识别为第2天。

测试表明DAY、WEEKDAY、YEAR、MONTH、HOUR、MINUTE、SECOND、DAYS360与TEXT的日期时间格式处理，对日期时间的识别不完全按照日期部分，

这几个函数对日期的识别范围至少到毫秒的精度，23:59:59.499(含)前识别为当日日期，23:59:59.500开始识别为第2天。

而DAYS、EDATE、EOMONTH、WEEKNUM、ISOWEEKNUM、WORKDAY(.INTL)、NETWORKDAYS(.INTL)、YEARFRAC这10个函数对日期时间的识别仅识别日期部分，严格取整，与时间范畴无关。

因此如果你们的软件导出数据时间精度达到毫秒级，请注意这个精度差异对结果的影响。

6)参数上限

有些函数具有内置上限，这个上限会限制这些函数的使用，其中大部分函数在超过上限后是会报错的，例如MOD函数：

但是，少数函数在超过上限后不会报错而是返回特殊结果，这类性质比较隐蔽，会影响函数运算结果，例如DATE函数：

这个函数的优势是例如第3参数大于本月天数时，会自动把函数结果转化为对应的有效日期。但这个函数有内置参数，第3参数有效的最大值只有32767，超过32767的数值都会缩减到32767进行日期换算，故需要注意这个性质避免转化的日期结果异常。

7)数组和引用

曾经写过一篇文章介绍AGGREGATE函数，这个函数堪称函数小霸王，因为这个函数是19个函数的集合体，然而有些新手在没看完攻略的前提下想用这个函数对某个内存数组忽略错误值求和。

9是对应求和SUM，6是忽略错误值，然而发现结果还是错的，

AGGREGATE是个神奇的函数，有两种形式：向量型和数组型。

Excel里具有2种形式的函数不多，除了AGGREGATE外常见的还有INDEX和LOOKUP，这其中AGGREGATE由于某些性质是最特殊的。

注意AGGREGATE的参数，数组型里的第4参数为K值，而AGGREGATE的第1参数对应的19个函数里，只有14-19的6个函数是具有K值的。

所以关于AGGREGATE的最大局限就是，只有在替代LARGE，SMALL，PERCENTILE，QUARTILE这几个函数时，第3参数才支持数组，否则只支持引用，这是这个函数的最大局限。

因此上面用AGGREGATE针对数据源存在错误值时的求和，3参数只能使用引用，不支持数组。

8)你不知道的性质

Excel有大量简写模式和特殊识别的字符串规则：

0-12的整数+空格+A / P 为整点时间的缩写表达式，可以被Excel直接识别为时间的(12 A 为12:00 AM实际为0:00故结果为0)。

这些特殊规则很多，都可能影响你的计算结果：

(小编测试过很多最特殊的，大概是下面这个从2013版本开始出现的以空格+冒号开头的特殊表达式)

很多时候，你自认为公式是正确的，但结果异常，很多时候就是因为你并没有了解Excel的这些特殊规则和机制造成的。

因此，学习函数不是一朝一夕的事情，只有多练习，多看别人的文章和公式，才能随心所欲，否则就可能出现各式各样的问题。

好了，今天的内容就是这些吧，我是流浪铁匠，点个赞，咱们一起走天涯。