☰

数组公式有什么变化吗？

tag-icon • tag-icon microsoft-excel
数组公式有什么变化吗？

所以这一直困扰着我，因为我找不到以下公式不再起作用的原因：

=IFERROR(IF(AN:AN="",1-(AQ:AQ/(AE:AE/1.1)),1-(AQ:AQ/(((AE:AE+AN:AN)/1.1)-AP:AP))),"")

这之前为我们提供了利润率%，对于较旧的表格或较旧版本的 Excel 用户仍然如此。

然而，使用 Office 365 的用户现在遇到了#SPILL错误。

找不到办法，只好放弃并使用以下方法重新实现它INDIRECT：

=IFERROR(IF(INDIRECT(ADDRESS(ROW(),COLUMN($AN$1),3,1))="",
1-(INDIRECT(ADDRESS(ROW(),COLUMN($AQ$1),3,1))/(INDIRECT(ADDRESS(ROW(),COLUMN($AE$1),3,1))/1.1)),
1-(INDIRECT(ADDRESS(ROW(),COLUMN($AQ$1),3,1))/(((INDIRECT(ADDRESS(ROW(),COLUMN($AE$1),3,1))+INDIRECT(ADDRESS(ROW(),COLUMN($AN$1),3,1)))/1.1)-INDIRECT(ADDRESS(ROW(),COLUMN($AP$1),3,1))))),"")

但考虑到它的规模如此之大，它并不理想，也不容易证明。

如果我能找出原始配方停止起作用的原因，我将不胜感激。

干杯，

编辑：

示例文件：https://drive.google.com/open?id=1hIm2e3D2WcFr27JrQX8UE4iqM05AQbZr

AF 和 AG 是相关列。

答案1

这是因为 Excel 正在实现我们一直拥有的隐式交集版本（30 多年），以保持其实用性并维持兼容性，同时仍然将其消除，以进一步在程序中采用新的方式执行数组（称为动态数组）。

顺便说一句，这就是您收到该spill消息的原因。请注意，就目前情况而言，我敢打赌它确实存在错误，因为您几乎肯定在列中还有其他内容，或者公式可能不在其列的第 1 行。由于它的编写范围很广，涵盖了整个列（您实际上没有超过一百万行数据，对吗？），因此它需要一整列来保存结果。

可以这么说，如果你有多余的专栏，它将是一条关于你所看到的新奇迹的消息。

然而，尽管如此，还是有一个解决方案。将公式编辑为：

=IFERROR(IF(@AN:AN="",1-(@AQ:AQ/(@AE:AE/1.1)),1-(@AQ:AQ/(((@AE:AE+@AN:AN)/1.1)-@AP:AP))),"")

看到里面的“@”符号了吗？这就是我们现在要用来强制使用传统隐式交集样式的东西。如果您使用上面的公式，spilling则不会发生这种情况，因此当前电子表格中不会出现任何问题。（此外，至少在这里不需要将其变成公式CSE。只是一个常规公式，添加了“@”符号。）

这样就可以实现您所要求的功能了。

但是，考虑到这种情况不会消失（30 多年后才会消失？），您可能想从这里开始下定决心，考虑将公式调整到更小的单元格范围。如果您可以合理地拥有 200,000 行数据，那么将范围设为 225,000 或 250,000 个单元格。如果这样做，您可以使用该spill功能在数据区域的顶部单元格中写入单个公式，然后将结果spill放入 25 万个空单元格中。然后其他相关函数可以对该动态数组进行操作。也许那会是一件很棒的事情。

由于您要考虑的系统不会获得这些好处，因此也许，请在适当的单元格中输入此公式，或者选择适当的单元格，输入公式，然后使其成为 CSE 公式。其中之一（似乎第一个）会让您完全回到这个“奇妙的事情”之前的状态。

但无论如何，也许不通过选择完整的列范围来避免某些事情可能是上述公式向前迈出的一步。不是说教，只是说说而已。

相关内容