¿Cómo crear celdas (verdaderamente) en blanco a partir de una fórmula para que se omitan con Ctrl+Flecha?

Question

Esta preguntapregunta sobre cómo usar una fórmula para crear celdas verdaderamente en blancopara que no aparezcan en un gráfico. La solución es utilizar la fórmula NA()que hace que la celda tome el valor #N/A.

Como caso de uso común, tengo una columna que es básicamente una bandera y contiene una fórmula IF(*flag_condition*, 1, ""). Luego tengo un SUM()en la parte superior que me dice cuántas banderas hay en la columna. Luego seleccionaría una celda aparentemente vacía (bandera = falso) cerca de la parte superior de esa columna y usaría Ctrl+ Downintentando saltar a la siguiente celda no vacía de esa columna, para inspeccionar los valores en esa fila, especialmente cuando las banderas son escasas y Los datos son largos.

Sin embargo, Ctrl+ Downsimplemente va a la siguiente celda, que parece vacía pero tiene la fórmula.

Usar NA()en lugar de ""(a) hace que la celda tome visiblemente el valor #N/A, (b) hace que la suma tome el valor #N/Ay (c) no permite que CTRL+flecha omita esa celda.

Por lo tanto, hago esto como una pregunta separada, que no es un duplicado de la anterior.

¿Existe alguna solución que supere al menos los problemas (c) y posiblemente (b) de lo anterior?

---

Por solicitud de @JvdV, aquí hay un ejemplo:

La fórmula en esta celda y las siguientes es =IF(MOD(A3,2)=0,1,"").

El resultado previsto es presionar Ctrl+ Downy saltar a B6, en lugar de B4.

En este caso la bandera no es especialmente escasa, pero en otros sí lo es.

Answer 1

Encontré una manera de hacer esto, usando VBA y Worksheet_Change. Llené una tercera columna, C, que contendrá un 1valor par para la columna A y estará vacía para los valores impares de la columna A. Siempre que se cambie un valor en la columna A, este código se activará y actualizará el valor correspondiente en C. Luego puede ocultar la columna B y usar la columna C para hacer su Ctrlnavegación 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

Este es un código rápido y sucio, por lo que probablemente puedas simplificarlo un poco. Por ejemplo, podrías poner la If … Mod …lógica en el código VBA en lugar de en una fórmula en la columna B, y entonces no necesitarás la columna adicional.

Answer 2

Me temo que debido a que los valores en B3, B4etc. no son celdas realmente vacías, Excel Ctrl+ Downno pasará a la siguiente celda que tiene otro valor que ""simplemente ""es algún tipo de valor a través de una fórmula.

Intenté superar eso usando VBA con un módulo en el Workbook.Openevento. Como a continuación:

Private Sub Workbook_Open()

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

End Sub

Esto le indica a Excel que al abrir y al presionar Ctrl+ Downdebe llamar a un módulo llamado ChangeKey. Ese módulo en particular podría verse así:

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

Aún debería poder usarlo SUMen el rango ahora sin que se produzca un #N/Aerror.

---

Si cambia de opinión y necesita el contenido #N/Aen las celdas para fines del gráfico, puede hacer ambas cosas:

• Utilice SUMIFpara omitir los #N/Avalores:

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

• Cambie el ChangeKeymódulo para omitir #N/Avalores también

  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
  

  

Answer 3

En realidad, existe una solución mucho más sencilla sin tener que escribir ningún código en VBA. Simplemente inserte el filtro y seleccione solo las celdas "en blanco". A continuación, vaya al icono "borrar" y seleccione "borrar contenidos". Debería estar todo listo cuando retire los filtros.

Answer 4

El combo Ctrl+ Arrowen Excel responde exclusivamente a rupturas en los datos. Dado que esas celdas tienen una fórmula, nunca habrá una interrupción en los datos. La única forma de evitar esto es reemplazar los "datos" en esas celdas después de evaluar la fórmula.

Hay 2 formas básicas de hacer esto: VBA o sin VBA. Cualquiera de los métodos puede tener innumerables variaciones. Aquí están los más simples que he encontrado para ambos.

VBA:

Esto también evaluaría la fórmula misma. Deberá activarse manualmente, ya sea mediante un botón o ejecutando una macro, cada vez que desee evaluar la fórmula. El lugar donde colocaría el código VBA dependería de cómo quisiera activarlo. Puse el mío en el área del nivel más bajo necesaria para el acceso; Hoja1 en este caso.

Sub test()

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

End Sub

A1:A6 sería donde se ubica la fuente que se está evaluando.

B1:B6 sería hacia donde irán los resultados.

Ventaja: Extremadamente rápido en comparación con otros métodos de cálculo.

Desventaja: Usar Evaluar para funciones complejas o de matriz puede ser complicado. Existen métodos para hacer que los rangos en el código sean flexibles, pero no entraré en eso aquí.

Nota: Usar el Worksheet_Change()evento funcionará. Sin embargo, tiendo a evitar esta solución, ya que el código se ejecutará cada vez que se cambie la hoja de alguna manera. Puede ralentizar las cosas o interferir con otras funciones de la hoja.

Sin VBA:

1. Utilice la fórmula que necesita normalmente.

2. Seleccione los resultados (comenzar desde arriba y presionar Ctrl+ Shift+ Downes el método más sencillo).

3. Copiar

4. Pegue solo valores (¡NO cambie su selección!)

5. Haga un texto en columnas, ancho fijo, sin saltos seleccionados (Nuevamente, asegúrese de NO cambiar su selección)

Ventaja: No hay código que mantener. Buena habilidad para tener.

Desventaja: Debe hacerse manualmente cada vez. Pega tu fórmula, haciéndote volver a escribirla cada vez que la necesites.