☰

如何從公式建立（真正的）空白單元格，以便在 Ctrl+箭頭 中跳過它們？

tag-icon • tag-icon microsoft-excel worksheet-function
如何從公式建立（真正的）空白單元格，以便在 Ctrl+箭頭 中跳過它們？

這個問題詢問如何使用公式建立真正的空白儲存格這樣它們就不會出現在圖表中。解決方案是使用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，其中包含 A1表示A 列中的偶數值，對於A 列中的奇數值將為空。的對應值然後，您可以隱藏 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不會跳到具有另一個值的下一個單元格，""因為只是""透過公式得出某種值。

我嘗試使用 VBA 和事件模組來克服這個問題Workbook.Open。如下：

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 程式碼的位置取決於您想要如何觸發它。我把我的放在需要訪問的最低層區域；本例中為 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. 對列執行文本，固定寬度，不選擇任何中斷（再次確保您不更改您的選擇）

優點：無需維護程式碼。好技能值得擁有。

缺點：每次都必須手動完成。貼到您的公式上，讓您在每次需要時重新輸入。

相關內容