Como criar células (verdadeiramente) em branco a partir da fórmula para que sejam ignoradas em Ctrl + Seta?

Question

Essa questãopergunta sobre como usar uma fórmula para criar células verdadeiramente em brancopara que eles não apareçam em um gráfico. A solução é usar a fórmula NA()que faz a célula assumir o valor #N/A.

Como caso de uso comum, tenho uma coluna que é basicamente um sinalizador, contendo uma fórmula IF(*flag_condition*, 1, ""). Eu então tenho um SUM()no topo que me diz quantas bandeiras estão na coluna. Eu então selecionaria uma célula aparentemente vazia (sinalizador = falso) perto do topo dessa coluna e usaria Ctrl+ Downtentando pular para a próxima célula não vazia dessa coluna, para inspecionar os valores nessa linha, especialmente quando os sinalizadores são esparsos e os dados são longos.

Porém, Ctrl+ Downapenas vai para a próxima célula, que parece vazia, mas contém a fórmula.

Usar NA()em vez de ""(a) faz com que a célula assuma visivelmente o valor #N/A, (b) faz com que a soma assuma o valor #N/Ae (c) não permite que CTRL + seta pule essa célula.

Portanto, faço isso como uma pergunta separada, que não é uma duplicata dela.

Existe alguma solução que supere pelo menos os problemas (c) e possivelmente (b) acima?

---

Por solicitação de @JvdV, aqui está um exemplo:

A fórmula nesta célula e nas abaixo é =IF(MOD(A3,2)=0,1,"").

A saída pretendida é pressionar Ctrl+ Downe pular para B6, em vez de B4.

Neste caso, a bandeira não é particularmente esparsa, mas noutros é.

Answer 1

Eu encontrei uma maneira de fazer isso, usando VBA e Worksheet_Change. Preenchi uma terceira coluna, C, que conterá a 1para valores pares na coluna A e estará vazia para valores ímpares na coluna A. Sempre que um valor na coluna A for alterado, este código será acionado, atualizando o valor correspondente em C . Você pode então ocultar a coluna B e usar a coluna C para fazer sua navegação 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

Este é um código rápido e sujo, então você provavelmente pode simplificá-lo um pouco. Por exemplo, você poderia colocar a If … Mod …lógica no código VBA em vez de em uma fórmula na coluna B, e então não precisará da coluna extra.

Answer 2

Receio que, como os valores em B3, B4etc, não sejam células verdadeiramente vazias, o Excel Ctrl+ Downnão pule para a próxima célula que tenha outro valor, a não ser ""porque simplesmente ""é algum tipo de valor por meio de fórmula.

Tentei superar isso usando VBA com um módulo no Workbook.Openevento. Como abaixo:

Private Sub Workbook_Open()

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

End Sub

Isso informa ao Excel ao abrir e ao pressionar Ctrl+ Downque ele precisa chamar um módulo chamado ChangeKey. Esse módulo específico poderia ser parecido com:

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

Você ainda deve poder usar SUMo intervalo agora sem dar #N/Aerro.

---

Se mudar de ideia e precisar #N/Adas células para fins de gráfico, você pode:

• Use SUMIFpara pular os #N/Avalores:

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

• Altere o ChangeKeymódulo para pular #N/Avalores também

  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

Na verdade, existe uma solução muito mais fácil, sem a necessidade de escrever nenhum código em VBA. Basta inserir o filtro e selecionar apenas as células 'em branco'. Em seguida, vá até o ícone ‘limpar’ e selecione ‘limpar conteúdo’. Você deve estar pronto ao remover os filtros.

Answer 4

A combinação Ctrl+ Arrowno Excel responde exclusivamente a quebras de dados. Como essas células têm uma fórmula, nunca haverá quebra de dados. A única maneira de contornar isso é substituir os “dados” nessas células após a avaliação da fórmula.

Existem 2 maneiras básicas de fazer isso: VBA ou não VBA. Qualquer método pode ter inúmeras variações. Aqui estão os mais simples que encontrei para ambos.

VBA:

Isso também avaliaria a própria fórmula. Ele precisaria ser acionado manualmente, por meio de um botão ou executando uma macro, cada vez que você desejasse avaliar a fórmula. O local onde você colocaria o código VBA dependeria de como você deseja acioná-lo. Coloquei o meu na área de nível mais baixo necessária para acesso; Folha1 neste caso.

Sub test()

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

End Sub

A1:A6 seria onde está localizada a fonte que está sendo avaliada.

B1:B6 seria para onde os resultados irão.

Pró: Extremamente rápido em comparação com outros métodos de cálculo.

Contra: Usar Evaluate para funções complexas ou de array pode ser complicado. Existem métodos para tornar os intervalos no código flexíveis, mas não vou entrar nisso aqui.

Nota: Usar o Worksheet_Change()evento funcionará. No entanto, tendo a evitar essa solução, pois o código será executado sempre que a planilha for alterada de alguma forma. Isso pode tornar as coisas mais lentas ou interferir em outras funções da planilha.

Não VBA:

1. Use a fórmula necessária normalmente.

2. Selecione os resultados (começando de cima e pressionando Ctrl+ Shift+ Downé o método mais fácil).

3. cópia de

4. Colar apenas valores (NÃO altere sua seleção!)

5. Faça um texto para colunas, largura fixa, sem quebras selecionadas (novamente, certificando-se de NÃO alterar sua seleção)

Pró: Nenhum código para manter. Boa habilidade para ter.

Contra: Tem que ser feito manualmente sempre. Cola sobre sua fórmula, fazendo com que você a redigite sempre que precisar.