内容
什么是动态数组
2018 年 7 月，Microsoft 发布了一个更新，为 Microsoft Excel 添加了一个全新的工具：动态数组和 XNUMX 个用于处理它们的新函数。 毫不夸张地说，这些东西从根本上改变了使用公式和函数的所有常用技术，并且真正关心每个用户。
考虑一个简单的例子来解释本质。
假设我们有一个包含城市月份数据的简单表格。 如果我们选择工作表右侧的任何空单元格并在其中输入一个不链接到一个单元格而是立即链接到一个范围的公式会发生什么？
在所有以前版本的 Excel 中，点击后 输入 我们只会得到一个第一个单元格 B2 的内容。 能怎样？
好吧，或者可以将这个范围包装在某种聚合函数中，比如 =SUM(B2:C4) 并得到一个总计。
如果我们需要比原始求和更复杂的运算，例如提取唯一值或 Top 3，那么我们将不得不使用键盘快捷键将我们的公式作为数组公式输入 按Ctrl+转移+输入.
现在一切都不同了。
现在输入这样的公式后，我们可以简单地点击 输入 – 结果立即得到我们提到的所有值 uXNUMXbuXNUMXbto：
这不是魔术，而是 Microsoft Excel 现在拥有的新动态数组。 欢迎来到新世界🙂
使用动态数组的特点
从技术上讲，我们的整个动态数组存储在第一个单元格 G4 中，用它的数据填充所需数量的单元格。 如果您选择数组中的任何其他单元格，则编辑栏中的链接将处于非活动状态，表明我们位于“子”单元格之一中：
尝试删除一个或多个“子”单元格不会导致任何结果——Excel 将立即重新计算并填充它们。
同时，我们可以安全地在其他公式中引用这些“子”单元格：
如果复制数组的第一个单元格（例如，从 G4 到 F8），则整个数组（其引用）将沿与常规公式相同的方向移动：
如果我们需要移动数组，那么移动就足够了（使用鼠标或结合 按Ctrl+X, 按Ctrl+V)，同样，只有第一个主单元格 G4——在它之后，它将被转移到一个新的地方，我们的整个阵列将再次被扩展。
如果您需要在工作表上的其他位置引用创建的动态数组，则可以在其前导单元格的地址后使用特殊字符 #（“磅”）：
例如，现在您可以轻松地在引用创建的动态数组的单元格中创建一个下拉列表：
动态数组错误
但是，如果没有足够的空间来扩展数组，或者路径中的其他数据已经占用了单元格，会发生什么？ 在 Excel 中遇到一种全新的错误类型—— ＃转移！ （＃洒！）:
和往常一样，如果我们点击带有黄色菱形和感叹号的图标，我们将得到更详细的问题根源解释，我们可以快速找到干扰单元：
如果数组离开工作表或碰到合并的单元格，也会发生类似的错误。 如果您移除障碍物，那么一切都会立即得到纠正。
动态数组和智能表
如果动态数组指向由键盘快捷键创建的“智能”表 按Ctrl+T 或 主页 – 格式为表格 （首页——表格格式），那么它也将继承它的主要品质——自动调整大小。
在底部或右侧添加新数据时，智能表和动态范围也会自动拉伸：
但是，有一个限制：我们不能在智能表内的论坛中使用动态范围参考：
动态数组和其他 Excel 功能
好吧，你说。 这一切都很有趣也很有趣。 不需要像以前一样手动拉伸公式，并参考原始范围的第一个单元格向下和向右等等。 就这样？
不完全是。
动态数组不仅仅是 Excel 中的另一个工具。 现在它们已嵌入 Microsoft Excel 的核心（或大脑）——它的计算引擎。 这意味着我们熟悉的其他 Excel 公式和函数现在也支持使用动态数组。 让我们看几个例子，让您了解已经发生的变化的深度。
颠倒
转置范围（交换行和列）Microsoft Excel 一直有一个内置函数 运输 （移调）. 但是，要使用它，您必须首先正确选择结果的范围（例如，如果输入的范围是 5×3，那么您必须选择 3×5），然后进入函数并按组合 按Ctrl+转移+输入，因为它只能在数组公式模式下工作。
现在您可以只选择一个单元格，在其中输入相同的公式，然后单击正常 输入 – 动态数组会自己做所有事情：
乘法表
这是我被要求在 Excel 中可视化数组公式的好处时给出的示例。 现在，要计算整个毕达哥拉斯表，站在第一个单元格 B2 中就足够了，在那里输入一个将两个数组相乘的公式（垂直和水平的数字集 1..10），然后单击 输入:
胶合和外壳转换
数组不仅可以相乘，还可以与标准运算符 &（& 符号）粘合在一起。 假设我们需要从两列中提取名字和姓氏，并纠正原始数据中的跳跃大小写。 我们用一个构成整个数组的简短公式来做到这一点，然后我们将函数应用于它 普罗纳赫 （恰当的）整理注册表：
结论 Top 3
假设我们有一堆数字，我们想从中得出前三个结果，并按降序排列。 现在这是通过一个公式完成的，同样，没有任何 按Ctrl+转移+输入 就像之前一样：
如果您希望结果不是放在一列中，而是放在一行中，那么将这个公式中的冒号（行分隔符）替换为分号（一行内的元素分隔符）就足够了。 在英文版的 Excel 中，这些分隔符分别是分号和逗号。
VLOOKUP 一次提取多列
主要工作内容 VPR （VLOOKUP） 现在您可以一次从多个列中提取值，而不是从一个列中提取值——只需在函数的第三个参数中将它们的数字（以任何所需的顺序）指定为数组：
OFFSET 函数返回一个动态数组
用于数据分析的最有趣和最有用的（在 VLOOKUP 之后）函数之一是函数 处置 （抵消），我曾经在我的书中用了一整章和一篇文章在这里。 理解和掌握这个函数的难点一直是它作为结果返回了一个数组（范围）的数据，但是我们看不到它，因为 Excel 仍然不知道如何开箱即用地处理数组。
现在这个问题已经过去了。 看看现在如何使用一个公式和一个由 OFFSET 返回的动态数组，您可以从任何排序表中提取给定产品的所有行：
让我们看看她的论点：
- A1 – 起始单元格（参考点）
- ПОИСКПОЗ(F2;A2:A30;0) – 计算从起始单元到第一个发现的卷心菜的移动。
- 0 – 相对于起始单元格向右移动“窗口”
- СЧЁТЕСЛИ(A2:A30;F2) – 计算返回的“窗口”的高度 – 有卷心菜的行数。
- 4 — 水平“窗口”的大小，即输出 4 列
动态数组的新函数
除了在旧函数中支持动态数组机制外，Microsoft Excel 中还添加了几个全新的函数，专门针对动态数组进行了改进。 特别是，这些是：
- GRADE （种类） – 对输入范围进行排序并在输出上生成一个动态数组
- 分类器 （排序方式） – 可以按另一个范围的值对一个范围进行排序
- FILTER （筛选） – 从源范围中检索满足指定条件的行
- K （独特的） – 从范围中提取唯一值或删除重复项
- 大规模 （兰德雷） – 生成给定大小的随机数数组
- 胞衣 （序列） — 以给定的步长从一系列数字形成一个数组
更多关于他们的信息——稍后。 他们值得一篇单独的文章（而不是一篇）进行深思熟虑的研究🙂
结论
如果您已经阅读了上面写的所有内容，那么我认为您已经意识到已经发生的变化的规模。 Excel 中的许多事情现在可以更轻松、更轻松、更合乎逻辑地完成。 我必须承认，我对现在有多少文章必须在这里、在这个网站和我的书中进行更正感到有点震惊，但我已经准备好轻松地做这件事了。
总结结果， плюсы 动态数组，您可以编写以下内容：
- 你可以忘记组合 按Ctrl+转移+输入. Excel 现在认为“常规公式”和“数组公式”之间没有区别，并以相同的方式对待它们。
- 关于功能 SUMPRODUCT （总和），以前用于输入数组公式 按Ctrl+转移+输入 你也可以忘记 - 现在很容易 SUM и 输入.
- 智能表和熟悉的函数（SUM、IF、VLOOKUP、SUMIFS 等）现在也完全或部分支持动态数组。
- 存在向后兼容性：如果您在旧版本的 Excel 中打开带有动态数组的工作簿，它们将变成数组公式（在花括号中）并继续以“旧样式”工作。
找到了一些号码 缺点:
- 您不能从动态数组中删除单个行、列或单元格，即它作为单个实体存在。
- 您不能以通常的方式对动态数组进行排序 数据 - 排序 （数据——排序）. 现在有一个特殊的功能。 GRADE （种类）.
- 动态范围不能变成智能表（但您可以基于智能表制作动态范围）。
当然，这还没有结束，我相信微软未来会继续完善这个机制。
我在哪里可以下载？
最后，主要问题🙂
微软于 2018 年 XNUMX 月在一次会议上首次宣布并展示了 Excel 中动态数组的预览 点燃. 在接下来的几个月里，对新功能进行了彻底的测试和试运行，首先是 猫 微软本身的员工，然后是 Office Insiders 圈子的志愿者测试人员。 今年，添加动态数组的更新开始逐步向普通 Office 365 订阅者推出。 例如，我仅在 365 月通过我的 Office XNUMX Pro Plus（每月定向）订阅收到了它。
如果您的 Excel 还没有动态数组，但您确实想使用它们，那么有以下选项：
- 如果您有 Office 365 订阅，您只需等到此更新到达您的手中。 这种情况发生的速度取决于向您的 Office 交付更新的频率（一年一次、每六个月一次、每月一次）。 如果您有一台公司 PC，您可以要求您的管理员将更新设置为更频繁地下载。
- 您可以加入那些 Office Insiders 测试志愿者的行列——然后您将是第一个获得所有新特性和功能的人（当然，Excel 中的错误可能会增加）。
- 如果您没有订阅，而是有盒装独立版 Excel，那么您至少要等到 2022 年发布下一版 Office 和 Excel。 此类版本的用户只收到安全更新和错误修复，所有新的“好东西”现在只提供给 Office 365 订阅者。 悲伤但真实🙂
无论如何，当动态数组出现在您的 Excel 中时——阅读本文后，您将做好准备🙂
- 什么是数组公式以及如何在 Excel 中使用它们
- 使用 OFFSET 函数的窗口（范围）求和
- 在 Excel 中转置表格的 3 种方法