禁用#SPILL！错误（Microsoft Excel）

Jan的公司最近升级到Office 365 ProPlus。随之而来的是#SPILL！使用VLOOKUP时出错，他使用了很多。现在，他无法将VLOOKUP与过滤器一起使用。 Jan知道排序和/或删除上一个VLOOKUP中的＃N / A很容易，但是此错误使他付出了很多额外的时间和精力。他想知道是否有办法关闭此“功能”。

在不久的将来，Microsoft更改了计算工作表的方式。

这是一个巨大的变化，您可能已经在其他地方阅读过。这是一篇提供有关更改的重要信息的文章：

这篇文章很长；您需要预留一些时间来消化其中包含的信息。但是，如果您在Excel中创建大量公式，则需要这样做-对程序的更改意味着您最终必须了解它。

本质上，Microsoft取消了数组函数的概念（尽管它们仍然可以使用），而是允许几乎所有函数（包括VLOOKUP）返回值数组。如果返回值的数组无法容纳在可用空间中，则会得到新的#SPILL！错误。

老实说，答案不是禁用#SPILL！错误；确实没有办法。答案是了解Excel在计算时正在做什么，然后相应地修改公式。

让我们来看一个例子。假设您有一个工作表，该工作表在一个简单的两列数据集中列出了商品及其价格。然后，在其右侧，输入一些项目并使用VLOOKUP函数返回与每个项目相关的价格。在较旧版本的Excel（2019或更早版本）中打开此工作簿时，您会获得很好的结果。 （请参见图1。）

图1. Excel 2010中的一个简单的VLOOKUP公式。

此屏幕快照是使用Excel 2010系统拍摄的，但如果在Excel 2016甚至Excel 2019中查看，则其效果相同。请注意，在此示例中，单元格F2中的VLOOKUP公式（在公式栏中显示，因为单元格F2被选中）复制到F2：F8范围。由于VLOOKUP函数从表中返回单个值，因此可以得到理想的结果。

现在，让我们看看如果在Office 365附带的Excel版本中使用相同的公式创建相同的工作簿，会发生什么情况。在这种情况下，您将看到错误。 （请参见图2。）

图2.最新版本的Excel中相同的简单VLOOKUP公式。

注意#SPILL！错误。因为VLOOKUP公式现在可以返回多个值，所以会发生此错误。实际上，当在VLOOKUP的第一个参数中使用单元格区域时，它现在将为该范围内的每个单元格返回一个值。因此，对第一个参数使用范围E2：E8意味着VLOOKUP返回7个值。换句话说，它会自动返回一个值数组。如果不能全部显示这些值，则显示#SPILL！错误。这就是为什么您看到#SPILL！ F2：F7单元格中的错误；它们下面有东西阻止显示这些公式中返回的所有值。您不会在单元格F8中看到该错误，因为该单元格下面没有任何内容可以停止显示。

那么，您如何解决呢？实际上，有三种方法可以修复它。

在此特定示例中，最简单的方法是简单地删除单元格F3：F8中的所有内容。这使单元格F2中的公式“溢出”

正确地放在它下面的其他单元格上。

第二种方法是更改​​单元格F2中的公式，因此如下所示：

注意在第一个参数之前使用@符号。这告诉Excel您希望VLOOKUP公式仅返回一个值。调整公式的另一种方法是在单元格F2中使其看起来像这样：

现在，您可以将任一公式从单元格F2复制到F2：F8的整个范围内，这样就不会有问题。为什么？因为（再次）在这些实例中VLOOKUP仅返回单个值，而不返回值数组。

结果是，更改公式的最佳方法是使用（1）

确保没有任何阻塞您要求VLOOKUP返回的值的完整数组的显示，或（2）修改第一个参数，使其仅引用单个单元格。

_ExcelTips_是您进行经济高效的Microsoft Excel培训的来源。

本技巧（13750）适用于Office 365中的Microsoft Excel Excel。