Excel公式：检查单元格内容

Question

我正在尝试在运行时创建一些条件格式（好极了）的插件（双倍欢呼），并发现，显然，有些函数不能像在普通工作表中那样使用。（当我尝试创建引用VBA函数的CF时，即使它在插件而不是工作簿中，我只会收到无效的过程调用错误5；我可以使用内置函数轻松创建CF。）我找到的最清晰的确认信息在这里，但它并没有真正解释问题所在；那是玄学的部分，希望能听到更多关于这个问题的信息。

关键问题是：我是否可以完全避免使用VBA，并使用一系列Excel专用的内置函数来验证给定单元格是否包含常量（即用户输入的值）、公式（即某种计算、逻辑操作等——几乎以=开头）或链接（即对另一个工作表或另一个工作簿中的单元格的引用）？我知道Excel可以轻松确定这一点；看看GoTo/Special的用途和速度。但我怎么才能得到它呢？

提前感谢您的帮助。

- downwitch

---

如果常量是字符串，CELL() 函数可能会很有用，点击此处查看详细信息：http://www.informit.com/articles/article.aspx?p=440961（使用 CELL("type", A1) 作为您的单元格，其中 A1 是您的单元格。）我不确定在您的问题中“公式”和“链接”的区别是什么。 - Gintautas Miliauskas

是的，我知道CELL函数，谢谢——我一开始就用它了，但是它（a）在大范围内速度较慢，而且（b）不够具体。在我的命名法中，所有链接都是公式，但并非所有公式都是链接。一个非常简单的例子： 这是一个公式：=SUM(A1:A5) 这是一个链接：=SUM(A1:A5,Sheet1!B6) - downwitch

你对间接引用有什么期望，其中实际使用的单元格直到运行时才知道（即...有时可能是你所谓的“链接”）？ - Dr. belisarius

对于我的目的，使用INDIRECT()公式构成了一个公式，而不是链接。我并不试图构建一个跟踪例程。Excel很清楚一个单元格的“公式”表示某种计算还是常量值。这些信息未以公式的形式向用户公开，这一点非常令人沮丧和愚蠢。只需要在公式中加入感叹号就可以确定“链接”状态。再次强调，这并不难……一旦你能够实际阅读公式字符串。 - downwitch

Answer 1

已更新至Excel 2013：

对于 Office 版本 2013 及以上，可使用 ISFORMULA¹ 函数。将其与 NOT 函数、AND 函数和 COUNTBLANK、ISBLANK 或 LEN 函数相结合，可以产生一个公式来确定单元格是否包含常量。

E2:F2 中的标准公式为，

=ISFORMULA(D2)
=AND(NOT(ISFORMULA(D2)), LEN(D2))

      

如果需要进一步了解单元格值的性质，可以使用TYPE函数来确定单元格内容是数字、文本、布尔值、错误还是数组。

当与本地工作表函数一起使用时，这里讨论的原生工作表函数可以复制从VBA的Range.SpecialCells方法和其xlCellTypeConstants或xlCellTypeFormulasxlCellType枚举中可用的结果。

---

¹ _{ISFORMULA 函数是在 Excel 2013 中引入的，早期版本不可用。}

excel-2013

- user4039065

---

终于有一个赢家了 - 我想微软公司的某个人和我一样感到沮丧。我已经使用2013版本有一段时间了，但不知道这个功能，谢谢！ - downwitch

Answer 2

我认为你无法完全避免使用VBA，但是你可以创建一个简单的UDF并在Excel中使用它。

例如：

Function IsFormula(Check_Cell As Range)
  IsFormula = Check_Cell.HasFormula
End Function

并且

Function IsLink(Check_Cell As Range)

  If InStr(1, Check_Cell.Formula, "!", vbTextCompare) Then
   IsLink = Check_Cell.HasFormula
  End If
End Function

=IsFormula(A1)会在A1中存在公式时返回TRUE，否则返回FALSE =IsLink(A1)会在A1中存在包含“！”的公式时返回TRUE，否则返回FALSE

您可以将它们结合起来创建一个字符串输出“Formula”、“Link”、“Value”

- user688334

---

谢谢，但正如原帖所述，你不能从插件中调用VBA函数（我也写了）来进行条件格式化。 - downwitch

你不必调用函数。强制进行Excel重新计算，UDF应该会自动更新。 - user688334

那有什么帮助呢？我试图设置条件格式，而不是将UDF放入单元格中。如果UDF位于Excel加载项中，则无法在条件格式公式调用中嵌入UDF。我敦促您尝试一下；它会产生这个错误：“您不能使用对其他工作表或工作簿的引用作为条件格式标准。” - downwitch

有趣，我不知道关于条件格式的那个。看起来你唯一的选择是构建一个“扫描和格式化”函数，它在工作簿的每个工作表中运行所有使用的单元格。不是你想要的，但我不认为你可以仅使用Excel内置函数来实现你的要求。另外，请不要太激进，仅因为一些回答你问题的人显然无法阅读问题，这并不是攻击他们的理由。 - Anonymous Type

Answer 3

不确定这是否是您想要的，但它似乎可以做到您所要求的一些功能。

http://www.ozgrid.com/VBA/special-cells.htm

这是 range.specialcells 方法。

它返回一个仅包含常量或公式等的范围。

下面展示了如何使用此代码的示例：

Sub CheckForConstants()
    Dim x As Range
    Set x = Selection.SpecialCells(xlCellTypeConstants, xlNumbers)
    MsgBox "address of cells that contain numbers only is " & x.Address
    Set x = Selection.SpecialCells(xlCellTypeConstants)
    MsgBox "address of cells that contain constant of any type is " & x.Address
End Sub

你选择一个范围，然后执行这个宏，它会返回那些符合要求的单元格的地址。

第一个 x 查找只包含数字的单元格。 第二个 x 查找包含任何常量的单元格

在这种情况下，范围是选择的，但你可以设置为你想要的，例如 range("a1:b5") 等。

我回到工作表并使用了“转到特殊”方法。

显然它也使用了 range.special 方法。

我使用了记录宏选项，这就是我得到的结果。

Selection.SpecialCells(xlCellTypeConstants, 23).Select
    Range("M7").Select
    Selection.SpecialCells(xlCellTypeFormulas, 23).Select
    Range("I6:J16").Select
    Selection.SpecialCells(xlCellTypeConstants, 1).Select
    Range("L9").Select
    ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
    Sheets("CP").Select
    Application.CutCopyMode = False
    Range("I21").Select
    ActiveSheet.DrawingObjects.Select
    Application.Goto Reference:="GoToSpecialRoutine"

工作表上的goto特殊功能使用了一些特殊单元格方法。

它还使用其他一些方法。在最后5行代码中，我更改了工作表并要求它转到对象。

它实际上并没有去到它们。它只是选择它们。

工作表CP包含对象，并且它使用最后3行中的代码来选择工作表上的所有对象。

查看goto特殊功能背后的代码最好的方法是记录一个宏，然后在工作表中使用goto / special功能。

完成后，停止录制并查看您记录的宏。

我不知道任何其他按单元格类型选择的功能，但我只是一个新手，所以很容易有这个功能而我不知道。

- THEO

---

谢谢，但是问题上方加粗的部分指出，解决方案必须仅限于Excel内置功能，不能使用VBA。我有VBA函数可以很好地确定这些单元格内容类型（常量、公式、带链接的公式）。原帖已经解释了原因。 - downwitch