☰

突出显示给定范围内未被公式引用的所有单元格（在 Excel 中）

tag-icon • tag-icon microsoft-excel
突出显示给定范围内未被公式引用的所有单元格（在 Excel 中）

我有一张 Excel 表，用作费用日志

其结构如下：

A1 至 A4 -> 一些数值

A5-> =SUM(A1:A4)

A6 到 A10 ->一些数值

A11 -> 总计（A5:A10）

因此，我基本上每隔几行就保留一个检查点

现在，我有 200 多行，想检查在应用公式时是否遗漏了任何行

有没有一种简单的方法，只需选择整个范围并告诉 Excel 突出显示公式未引用的单元格？

（跟踪先例是一种方法，但它并不易于阅读，并且一些单元格依赖于此范围之外的数字，因此它也会从那里分支出来）

答案1

我不确定我是否完全理解了你的问题。
但这个宏会突出显示所有使用公式的单元格

  • 打开包含所有数据的 Excel 表副本
  • 按ALT+F11打开 VBA 编辑器

  • 从左侧窗格打开 Sheet1，然后插入以下代码

    Sub highlight()
  For Each mycell In UsedRange
    If mycell.HasFormula Then mycell.Interior.ColorIndex = 6
  Next mycell
End Sub

  • 返回到你的工作表并按ALT+F8并执行你的宏

答案2

如今（2020 年）可行的解决方案是：

=IF(SUM(A1:A200)-2*SUM(IF(ISERROR(FORMULATEXT(A1:A200)),0,A1:A200))<>0,"Uh oh…","All good")

（我从来都不喜欢 TYPE() 函数，因为它本身就是个恶魔，不会告诉我公式是否在单元格中。）但是，有人可能会想使用上述逻辑和 TYPE() 来查找没有公式的单元格并执行相同类型的计算。但是，TYPE(A1:A200) 只是返回“64”，告诉你它的耳朵之间的范围（“()”）是……一个范围（一个数组，同样的东西）。所以它没有起到作用，因此采用了上述方法。

当问题发布时，人们可以使用 Excel 4 宏函数 GET() 返回公式，并从该返回值进行测试。但是我们现在有了 FORMULATEXT()，它可以巧妙地区分而无需进行任何操作，因为它会返回单元格中没有公式的错误。

综上所述，基本上，它会对包括小计在内的整列数据进行求和，因此该部分的值是不含小计的数据值的两倍。如果您找到包含公式的单元格（小计）并对其进行 SUM()，并且如果您在进行每个小计时没有遗漏任何数据单元格，则该总和将是整列总和的一半。因此，我们将其乘以 2，然后从整列的简单总和中减去它。

如果结果为 0，那么一切可能都很好（“可能”是因为可能会发生奇怪的巧合（如果你愿意的话，可以称之为“完美风暴”），从而误导你）。如果涉及 200 个或更多单元格，那么这种情况肯定不会发生，因此这似乎是一个不错的算法。但你应该意识到，可能会发生一些奇怪的事情。如果结果不是 0，那么，肯定是小计中遗漏了某些内容，或者一个单元格包含在两个小计中，并且考虑到构造，整个小计很可能是两次添加的小计之一。

相关内容