☰

如何通过公式创建（真正的）空白单元格，以便在 Ctrl+Arrow 中跳过它们？

tag-icon • tag-icon microsoft-excel worksheet-function
如何通过公式创建（真正的）空白单元格，以便在 Ctrl+Arrow 中跳过它们？

这个问题询问如何使用公式创建真正空白的单元格这样它们就不会出现在图表中. 解决方法是使用NA()让单元格取值的公式#N/A。

作为一个常见用例，我有一列，它基本上是一个标志，包含一个公式IF(*flag_condition*, 1, "")。然后我SUM()在顶部有一个，它告诉我该列中有多少个标志。然后，我将选择该列顶部附近一个明显为空（flag=false）的单元格，并使用Ctrl+Down尝试跳转到该列的下一个非空单元格，以检查该行中的值，尤其是当标志稀疏且数据很长时。

但是，Ctrl+Down只是转到下一个单元格，该单元格看起来是空的，但却有公式。

使用NA()而不是""（a）使单元格明显地呈现值#N/A，（b）使总和呈现值#N/A，并且（c）不允许CTRL +箭头跳过该单元格。

因此，我将此作为一个单独的问题提出，它并不是重复的问题。

是否有任何解决方案可以至少解决上述问题 (c) 和可能的 (b)？

根据@JvdV 的请求，这里有一个例子：

在此处输入图片描述

此单元格和下面的单元格中的公式是=IF(MOD(A3,2)=0,1,"")。

预期输出是按Ctrl+Down并跳转到 B6，而不是 B4。

在这种情况下，标志并不是特别稀疏，但在其他情况下，标志却很稀疏。

答案1

我找到了一种方法来做到这一点，使用 VBA 和Worksheet_Change。我填充了第三列 C，如果1A 列中有偶数值，则该列包含一个，如果 A 列中有奇数值，则该列为空。每当 A 列中的值发生变化时，此代码就会触发，更新 C 中的相应值。然后，您可以隐藏 B 列并使用 C 列进行Ctrl+Arrow导航。

Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range

' The variable KeyCells contains the cells that will
' cause an alert when they are changed.
Set KeyCells = Range("A3:A999")

'Suppress further events to avoid cascading changes.
Application.EnableEvents = False

If Not Application.Intersect(KeyCells, Range(Target.Address)) _
       Is Nothing Then

    ' Place your code here.
    If Target.Offset(0, 1).Value = 1 Then
      Target.Offset(0, 2) = 1
    Else
      Target.Offset(0, 2).Clear
    End If

    'Uncomment the next line for troubleshooting.
    'MsgBox "Cell " & Target.Address & " has changed."

End If
'Re-enable further events.
Application.EnableEvents = True
End Sub

这是快速而粗略的代码，因此您可以稍微简化一下。例如，您可以将逻辑放在If … Mod …VBA 代码中，而不是放在 B 列的公式中，这样就不需要额外的列了。

答案2

B3我担心，因为等中的值B4不是真正的空单元格，所以 Excel 的Ctrl+Down不会跳到下一个具有其他值的单元格，而""只是因为""是通过公式得到的某种值。

我尝试使用带有Workbook.Open事件模块的 VBA 来克服这个问题。如下所示：

Private Sub Workbook_Open()

Application.OnKey "^{DOWN}", "ChangeKey"

End Sub

这告诉 Excel 在打开时按下Ctrl+Down时需要调用一个名为 的模块ChangeKey。该特定模块可能如下所示：

Sub ChangeKey()

With ThisWorkbook.Sheets("Sheet1")
    Set rng = .Range(.Cells(ActiveCell.Row, ActiveCell.Column), .Cells(.Range("B" & Rows.Count).End(xlUp).Row, ActiveCell.Column))
    For Each cl In rng
        If Len(cl) <> 0 And cl.Row > ActiveCell.Row Then cl.Select: Exit For
    Next cl
End With

End Sub

您现在仍然可以使用SUM该范围而不会出现错误#N/A。

在此处输入图片描述

如果您确实改变主意，并且需要#N/A在单元格中使用图表，您可以：

  • 用于SUMIF跳过以下#N/A值：

    =SUMIF(B3:B8,"<>#N/A")

  • 更改ChangeKey模块以跳过#N/A值

    For Each cl In rng
    If Application.WorksheetFunction.IsNA(cl) = False Then
        If Len(cl) <> 0 And cl.Row > ActiveCell.Row Then cl.Select: Exit For
    End If
Next cl

    在此处输入图片描述

答案3

实际上，有一个更简单的解决方案，无需在 VBA 中编写任何代码。只需插入过滤器并仅选择“空白”单元格。接下来，转到“清除”图标并选择“清除内容”。删除过滤器后，一切就绪了。

答案4

CtrlExcel 中的+组合Arrow专门用于处理数据中断。由于这些单元格有公式，因此永远不会出现数据中断。解决此问题的唯一方法是在计算公式后替换这些单元格中的“数据”。

有两种基本方法可以实现这一点：VBA 或非 VBA。这两种方法都有无数种变化。以下是我发现的最简单的方法。

VBA：

这也会评估公式本身。每次您想要评估公式时，都需要手动触发它，无论是通过按钮还是运行宏。您将 VBA 代码放在哪里取决于您想要如何触发它。我把我的代码放在访问所需的最低级别区域；在本例中是 Sheet1。

Sub test()

ActiveSheet.Range("B1:B6").Value = Evaluate("=IF(MOD(A1:A6,2)=0,1,"""")")

End Sub

A1:A6 是被评估的源所在的位置。

B1:B6 是结果存放的位置。

优点：与其他计算方法相比，速度极快。

缺点：使用 Evaluate 处理复杂函数或数组函数可能比较棘手。有方法可以使代码中的范围灵活，但我不会在这里讨论。

注意：使用Worksheet_Change()事件会起作用。但是我倾向于避免使用这种解决方案，因为每次更改工作表时代码都会运行。这可能会减慢速度或干扰工作表中的其他功能。

非 VBA：

  1. 正常使用您需要的公式。

  2. 选择结果（从顶部开始并按Ctrl+ Shift+Down是最简单的方法）。

  3. 复制

  4. 仅粘贴值（请勿更改您的选择！）

  5. 将文本设置为列，固定宽度，不选择分隔符（再次确保您不会更改您的选择）

优点：无需维护代码。这是一项很好的技能。

缺点：每次都必须手动操作。粘贴公式会覆盖公式，每次需要时都要重新输入。

相关内容