OFFICE 365的这些功能，颠覆了我对Excel的认知①

2021-06-10 本文已影响0人龙逸凡

最近推送的五篇文章：

你真的理解了相对引用？这三点95%的人都不会，你呢？

SUBTOTAL函数：统计筛选出的数据，就用它

【一本不正经系列】我把Excel玩坏了！

一本不正经地用Excel画了个。。那个，还是自己看吧

保护工作表时能否在指定单元格输入数据？能否使用切片器？

Excel 2019 新增了哪些实用功能？（附正式版下载地址）

《料理鼠王》中美食家柯博对厨神餐厅的评价：昨晚，我有了一次全新的体验，品尝了一道异乎寻常的美味，出自某个想都不敢想的厨师之手。这道美味以及它的创造者改变了我对厨艺的肤浅看法，他们彻底颠覆了我的内心。

前言 “久病成医”的龙表哥，使用Excel多年，在掉进自己挖的坑里多次后，为了不再掉进坑底，总结了一些肤浅经验，这就是后来写到《“偷懒”的技术》第一章与大家分享的那些经验，比如一致性原则、规范性原则、整体性原则、可扩展性原则等等。真正掌握了这些原则，可以让我们少走弯路，少掉坑。但是，随着Excel的发展，微软开发出了一些功能，让这些原则都有逐步失效的“危险”，比如整体性原则是这样的：

同一事项数据放在同一工作表、同一类型的工作表放在同一个工作簿、同一类工作簿放置在同一文件夹。如第三章第三节所举的例子，各公司的报表都放在以月份命名的文件夹下，这样在报表翻新时才能批量修改公式。清单型表格的数据能在同一工作表记录就不要拆分为年或月分表保存。

但是，随着Power Query的应用，合并同一工作簿中的表格，甚至合并同一文件夹下不同工作簿的数据，都轻而易举，因而，整体性原则其"政治正确性”、必要性就没那么强了。今天，安装了Office 365体验版，体验了一下动态数组、新增的一些函数后，这些新功能所形成的冲击，给我的感受，完全可以套用本文开篇所引用的美食家柯博的评论：我有了一次全新的体验，品尝了一道异乎寻常的美味。。。。这道美味以及它的创造者改变了我对厨艺的肤浅看法，他们彻底颠覆了我的内心。本公众号“Excel偷懒的技术”将抽时间陆续给大家介绍Office 365里新增的这些颠覆性的功能。

动态数组之初体验

新手们在看《“偷懒”的技术：打造财务Excel达人》第一章熊孩子被罚抄500遍“我再也不给美女递纸条”那个公式时，很多人都问：为什么结果都在一个单元格显示，如下图：

而不能显示在一列的500行里？

以前我都是这样回复的：

一个单元格的公式，其结果只会在本单元格，不会跑到其他单元格去。

现在好了，微软啪啪啪地打我的脸，已经帮大家实现这个“侵占邻国领土的梦想”了：一个单元格的公式也可以跑到其他单元格去。这就是OFFICE 365新增的动态数组自动溢出功能。

上图的公式就是使用了动态数组的函数。尽管公式只在A1单元格输入，由于其结果有500条，它就自动扩展到500个单元格中。这在之前的Excel版本中，是不可想象的！

什么是动态数组什么是动态数组，微软官方是这样描述的：

从2018年9月开始, 返回多个值的所有 Excel 公式 (也称为数组) 将结果返回到相邻单元格。此行为称为超过（龙逸凡按：此处英文版为 This behavior is called spilling，因而应该翻译为“溢出”才对）。

有可能返回多个结果的任何公式都可以称为动态数组公式。当前返回多个结果并成功超过的公式可能被称为 "溢出的数组公式"。

我们可以这样理解，Excel会根据公式结果的个数，自动动态调整在相应的单元格显示，动态数组公式能屈能伸，舒之弥四海，卷之不盈怀。比如在以前的版本中我们在一个单元格中输入公式：=ROW（1:5）它可以生成1到5的五个整数，并不会扩展填充到其他单元格。但在OFFICE 365最新体验版中却会自动扩展到5个单元格。