☰

是否有可能检测出细胞是否已被溢出？

tag-icon • tag-icon microsoft-excel
是否有可能检测出细胞是否已被溢出？

我喜欢在工作表上添加条件格式，它很简单=ISFORMULA(A1)并且涵盖整个工作表。

因此，基本上，如果一个单元格包含一个公式，它会得到一个灰色背景或类似的东西，这使得需要用户输入的单元格（不是灰色）和输出且不应该被弄乱的单元格（灰色）之间有明显的区分。

但是，当使用溢出数组结果时，只有实际包含公式的单元格才会被此格式标记，而溢出结果不会受到条件格式的影响。

那么，是否存在某种ISFORMULA()等效方法或其他方法来检测细胞是否已被溢出？

答案1

不确定这是否有帮助，但当您不知道有多少结果时，可以使用溢出范围运算符 # 来参考溢出结果列表（在公式内）。例如，如果 A1 处的结果溢出到 A2、A3 等，并且您使用 A1#，它将自动提取整个范围或结果。您还可以使用 counta(A1#) 来量化有多少结果，也许作为检查函数的一部分来查看是否存在任何溢出，例如 if(counta(A1#)>1,true)。

答案2

一种有效的解决方案是定义以下简单的 VBA 函数：

Function isSpill(x As Range)
    isSpill = x.HasSpill
End Function

=isSpill(Z100)然后，对于已经被其他单元格溢出的单元格，调用类似的方法返回 TRUE。

对于我的条件格式情况，这可以简单地与之相结合=OR(ISFORMULA(A1), isSpill(A1))以包含非溢出公式。

但是，这有一个明显（而且非常大）的缺点，即它需要 .xlsm 文件，并且用户必须始终启用宏。因此，如果可能的话，非 VBA 解决方案将受到欢迎。

答案3

假设我们知道A1有一个溢出的公式，我们想列出溢出所占据的单元格。尝试：

=TRANSPOSE(ADDRESS(SEQUENCE(1,COUNTA(A:A)),1))

在此处输入图片描述

（这里我们用 Spill 来描述 Spill）

并将答案放在一个单元格中：

=TEXTJOIN(", ",TRUE,TRANSPOSE(ADDRESS(SEQUENCE(1,COUNTA(A:A)),1)))

编辑＃1：

要确定单元格是通过输入还是通过溢出来填充，只需单击它并运行：

Sub DontCryOverSpiltCell()
    MsgBox (ActiveCell.Address & vbCrLf & ActiveCell.HasSpill)
End Sub

答案4

您可以使用 Excel 4 宏函数在命名范围内测试单元格是否具有公式，通过GET.CELL(41,"cell being checked")该公式可以对单元格的公式起作用SPILLED Into。它还将返回非公式单元格的内容，因此需要进行进一步的测试，AND()最简单的方法是，Does the result of whatever is in the cell equal the result of the GET.CELL() result?

如果确实如此，则内容可能是某种常数，数字或字符串。但是，公式可能会返回与公式本身的文本完全不同的值，就像……基本上总是这样。因此，如果测试失败，人们很有可能会假设单元格中有一个公式。这是成功的一半。

那么，它是一个SPILL公式吗？通常，人们不会遇到连续的单元格，无论是上下还是左右（没错，除非您可以消除左右，否则SPILL您需要在此处同时测试两者，因此希望可以消除其中一个或另一个问题）。因此，例如，A1-A2-A3-A4-A5 可能有相关的公式，但没有完全相同的公式SPILL range。测试单元格的内容，例如向上的三个或 14 个单元格（正如人们对此的猜测可能表明的那样：匹配越远，SPILL工作的可能性就越大，但发生奇怪的事情的可能性也越大）与感兴趣的单元格应该是一个不错的测试。GET.CELL()也这样做。

将两个测试（AND()再次）绑定到一个命名范围内会给您带来问题，因为您AND()在这里需要，但这两个测试将给出相反的结果（TRUE和FALSE），因此您必须选择NOT()其中一个。无论你选择哪种方式，你都会得到一个可以在简单中使用的结果IF()。

不幸的是，单元格内容FORMULATEXT()返回“” SPILLED Into。否则就只能这样了（叹气……）。

还有其他方法可以进行测试，例如，TYPE()结果是否GET.CELL()等于简单=cell's contents结果。但这种方法在“精确和无歧义”的意义上并不直接，因此需要根据情况进行测试。上面描述的方法有效，在我看来，这是一个相当不错的测试（关键部分是假设相邻单元格不应有比“相似”公式更接近的公式，而不是“相同”公式）。

编辑

无需教学，因为您将编写公式，他们将使用结果，而不是技术：

从那时起它就成为宏语言的一部分，但它实际上只是一个函数，就像SUM()或一样，但VLOOKUP()有一个例外：如果直接输入到单元格中，它将不起作用。您可以编写一个完全正常的公式，例如检查单元格 A5，就像这样：

=GET.CELL(41,$A$5)

但是您首先要创建一个命名范围，调用它Horse，然后将其写入那里（或者将其写入单元格端，它甚至不会被视为函数，但谁在乎呢，对吧，您只需要文本，然后将其复制到剪贴板，然后创建命名范围并将其粘贴进去）。所以现在您有一个名为的命名范围，Horse并且“引用”框中包含上述内容。

回到单元格中，假设您想要单元格 F23 中的结果。在该单元格中，您只需输入：

=Horse

它会使用函数为您找到值。您和用户在单元格中看到的都是结果。

根本没有宏，只是一个名字奇怪的普通公式。给它一个普通的名字，没有人会感到疑惑。

因此，在放弃之前，请尝试上述操作：创建一些命名范围，可能称为实验，输入该公式，创建一些将一些值放入单元格 A5 的溢出函数，然后选择其他单元格并输入=Experiment并查看 A5 内容产生的公式。

如果你仍然觉得奇怪，那也没关系。但也许你会发现它有多简单并且会喜欢它。只需要 2-3 分钟。

相关内容