您当前的位置:首页 > 电脑百科 > 软件技术 > office

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

时间:2020-08-24 10:21:21  来源:  作者:

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

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

Excel中的这些烧脑问题,你遇到过几种?

 

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

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

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

Excel中的这些烧脑问题,你遇到过几种?

 

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

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

 

2)高精度浮点

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

Excel中的这些烧脑问题,你遇到过几种?

 


Excel中的这些烧脑问题,你遇到过几种?

 

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

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

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

Excel中的这些烧脑问题,你遇到过几种?

 

 

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

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

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

Excel中的这些烧脑问题,你遇到过几种?

 

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

Excel中的这些烧脑问题,你遇到过几种?

 

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

Excel中的这些烧脑问题,你遇到过几种?

 


Excel中的这些烧脑问题,你遇到过几种?

 

即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天。

Excel中的这些烧脑问题,你遇到过几种?

 

测试表明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函数:

Excel中的这些烧脑问题,你遇到过几种?

 

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

Excel中的这些烧脑问题,你遇到过几种?

 

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

 

7)数组和引用

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

Excel中的这些烧脑问题,你遇到过几种?

 

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

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

Excel中的这些烧脑问题,你遇到过几种?

 

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

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

Excel中的这些烧脑问题,你遇到过几种?

 

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

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

 

8)你不知道的性质

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

Excel中的这些烧脑问题,你遇到过几种?

 

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

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

Excel中的这些烧脑问题,你遇到过几种?

 

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

Excel中的这些烧脑问题,你遇到过几种?

 

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

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

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



Tags:EXCEL   点击:()  评论:()
声明:本站部分内容及图片来自互联网,转载是出于传递更多信息之目的,内容观点仅代表作者本人,如有任何标注错误或版权侵犯请与我们联系(Email:2595517585@qq.com),我们将及时更正、删除,谢谢。
▌相关推荐
Excel常用电子表格公式大全   一、Excel基本公式   1、查找重复内容公式:=IF(COUNTIF(A:A,A2)>1,"重复","")。   2、用出生年月来计算年龄公式:=TRUNC((DAYS360(H6,"200...【详细内容】
2021-12-21  Tags: EXCEL  点击:(11)  评论:(0)  加入收藏
前言越来越多开发者表示,自从用了Python/Pandas,Excel都没有打开过了,用Python来处理与可视化表格就是四个字——非常快速!下面我来举几个明显的例子1.删除重复行和空...【详细内容】
2021-12-16  Tags: EXCEL  点击:(20)  评论:(0)  加入收藏
哈喽大家好! 前几天一个朋友向我疯狂吐槽。 快到年底了,公司要统计年度数据。 需要把一月到十二月份,十二张表格的数据,全部汇总到一个表格内。 这样的数据汇报每年都会有,每到年...【详细内容】
2021-12-14  Tags: EXCEL  点击:(23)  评论:(0)  加入收藏
我发现最近不少小叶子的留言都和 Excel 相关,我寻思是时候出一期解决 Excel 疑难杂症的小合集了。于是毛毛在众多问题中,挑了三个被问次数最多的有关 Excel 的问题,今天就来给...【详细内容】
2021-12-14  Tags: EXCEL  点击:(28)  评论:(0)  加入收藏
当你需要每天对 Excel 做大量重复的操作,如果只靠人工来做既浪费时间,又十分枯燥,好在 Python 为我们提供了许多操作 Excel 的模块,能够让我们从繁琐的工作中腾出双手。今天就和...【详细内容】
2021-12-07  Tags: EXCEL  点击:(16)  评论:(0)  加入收藏
试想一下,用 Excel 管理项目的时候,会有很严格的日期安排,而且项目中的各细目经常是并行作业的,这就意味着日期不一定是排序的 。 那么事项太多如何更好管理,而不至于遗忘关键节...【详细内容】
2021-11-16  Tags: EXCEL  点击:(22)  评论:(0)  加入收藏
大家好,我是Python进阶者。前几天给大家分享了一些乱码问题的文章,阅读量还不错,感兴趣的小伙伴可以前往:盘点3种Python网络爬虫过程中的中文乱码的处理方法,UnicodeEncodeError:...【详细内容】
2021-11-01  Tags: EXCEL  点击:(34)  评论:(0)  加入收藏
经常用 Excel 的表哥表姐们,想必都知道「下拉菜单」这个神器,鼠标点点点,就能轻轻录入数据:▲ 一级下拉菜单 它的制作方法也很简单,用【数据验证】功能可以直接实现! 有小伙伴表示...【详细内容】
2021-10-27  Tags: EXCEL  点击:(41)  评论:(0)  加入收藏
在工作中, 我们需要对业务人员的业绩进行跟进,会有如下格式的跟进表 每天要进行更新表格的时候,表头都要重新手动的进行输入,比较麻烦,今天教大家的技巧是使用公式进行自动更新首...【详细内容】
2021-10-26  Tags: EXCEL  点击:(41)  评论:(0)  加入收藏
如下所示,有一份模拟的txt数据,想放到excel表格里面去分析 如果我们直接CTRL+A全选数据,CTRL+C复制,然后在Excel里面CTRL+V粘贴,数据就会变成如下所示情形一: 整行数据会放在挤在...【详细内容】
2021-10-26  Tags: EXCEL  点击:(41)  评论:(0)  加入收藏
▌简易百科推荐
Excel常用电子表格公式大全   一、Excel基本公式   1、查找重复内容公式:=IF(COUNTIF(A:A,A2)>1,"重复","")。   2、用出生年月来计算年龄公式:=TRUNC((DAYS360(H6,"200...【详细内容】
2021-12-21  楠方儿i    Tags:Excel   点击:(11)  评论:(0)  加入收藏
最近这段时间,我们三易生活收到了一些来自读者朋友的求助,在他们选购电脑时发现,有的设备出厂预装了“Microsoft 365”,而另一些则预装的是“Office 2021”。对于这两款同为微软...【详细内容】
2021-12-14  三易生活    Tags:Office   点击:(26)  评论:(0)  加入收藏
我们在做问卷调差或者填写一些资料表的时候,会遇到一些word文档中有小方框【□】,需要在里面打钩【√】,那么是如何操作呢,今天和大家分享一下。方法一1,打开我们需要操作...【详细内容】
2021-11-26  小七哆来咪发唆    Tags:word   点击:(36)  评论:(0)  加入收藏
试想一下,用 Excel 管理项目的时候,会有很严格的日期安排,而且项目中的各细目经常是并行作业的,这就意味着日期不一定是排序的 。 那么事项太多如何更好管理,而不至于遗忘关键节...【详细内容】
2021-11-16  Excel学习世界    Tags:Excel   点击:(22)  评论:(0)  加入收藏
与大家分享一下有关制作身份证电子版时四周圆角处理的具体方法。方法/步骤首先,我们利用WPS打开相应的身份证扫描件,选中图片,点击“裁剪图片”按钮。 此时将显示“裁剪方式”...【详细内容】
2021-11-09  数字传媒微课堂    Tags:身份证   点击:(196)  评论:(0)  加入收藏
WPS是我们的常用办公软件之一,很多人在使用WPS打印功能的时候,经常会遇到多种多样的打印小问题,今天就为大家简单讲解下打印面板的各个小功能的作用。打印文档方法: 在左上角“W...【详细内容】
2021-11-02    21世纪教育网  Tags:WPS   点击:(31)  评论:(0)  加入收藏
前言:说起办公,就会想到三大办公软件,Word、Excel、PPT。这些软件的使用是有技巧的,学会使用技巧工作效率就会提高,加班自然就能避免。 今天就来分享其中之一:Word办公的7个小技巧...【详细内容】
2021-11-02  小杰好厉害呀    Tags:Word   点击:(52)  评论:(0)  加入收藏
我们使用 Word 的时候,页面上通常会有一些默认的符号,平时大家司空见惯了,可能没有多加留意,更不知道如何去除这些符号。 今天教大家两个 Word 技巧,将 Word 中默认的一些标记符...【详细内容】
2021-11-01  Excel学习世界    Tags:Word   点击:(48)  评论:(0)  加入收藏
经常用 Excel 的表哥表姐们,想必都知道「下拉菜单」这个神器,鼠标点点点,就能轻轻录入数据:▲ 一级下拉菜单 它的制作方法也很简单,用【数据验证】功能可以直接实现! 有小伙伴表示...【详细内容】
2021-10-27  秋叶Excel    Tags:Excel   点击:(41)  评论:(0)  加入收藏
在工作中, 我们需要对业务人员的业绩进行跟进,会有如下格式的跟进表 每天要进行更新表格的时候,表头都要重新手动的进行输入,比较麻烦,今天教大家的技巧是使用公式进行自动更新首...【详细内容】
2021-10-26  Excel自学成才    Tags:   点击:(41)  评论:(0)  加入收藏
最新更新
栏目热门
栏目头条