Sim, você pode ocultar linhas no Excel usando uma fórmula ou declaração condicional, mas não * diretamente * dentro de uma fórmula celular. Em vez disso, você precisa usar a formatação condicional
com uma fórmula personalizada em conjunto com uma macro VBA . Veja como fazer:
1. Formatação condicional: *
Objetivo: Para sinalizar linhas que atendem aos seus critérios (a condição). Em vez de se esconder diretamente, alteraremos a fonte da linha e preencheremos a cor para ser invisível.
*
Etapas: 1.
Selecione o intervalo de dados: Destaque toda a gama de linhas e colunas às quais você deseja aplicar a condição de esconderijo (por exemplo, `A1:Z100` ou por grande parte que seus dados são). Fundamentalmente, a primeira linha * * da sua seleção será usada como referência para sua fórmula.
2.
vá para formatação condicional: Na guia "Home", clique em "Formatação condicional"> "nova regra ..."
3.
Escolha "Use uma fórmula para determinar quais células formatar": Selecione este tipo de regra.
4.
Digite sua fórmula: Na caixa de fórmula, insira uma fórmula que retorna `true` quando você deseja ocultar a linha e` falso`. * IMPORTANTE:* Use referências relativas (`$ columnNameRownBumber`) ou referências mistas (` $ colunMineRownBumber`) apropriadamente, dependendo do que você deseja testar.
*
Exemplo 1 (Ocultar linhas onde o valor na coluna A é "hide"): `` `Excel
=$ A1 ="ocultar"
`` `
Esta fórmula verifica se o valor na coluna A da * primeira linha * do seu intervalo selecionado é igual a "ocultar". Em seguida, aplicará a formatação a essa linha. Como você selecionou todo o intervalo, a fórmula se ajustará automaticamente para cada linha.
*
Exemplo 2 (Ocultar linhas onde o valor na coluna B é menor que 0): `` `Excel
=$ B1 <0
`` `
Isso esconde linhas onde o valor na coluna B é negativo.
*
Exemplo 3 (Ocultar linhas onde o valor na coluna C está em branco): `` `Excel
=Isblank ($ C1)
`` `
5.
Defina o formato (para "ocultar"): Clique no botão "Formato ...".
*
Fonte: Altere a cor da fonte para combinar com a cor de fundo de suas células. Normalmente, isso é branco (ou sem preenchimento).
*
preencher: Altere a cor de preenchimento para combinar com a cor de fundo de suas células. Novamente, normalmente branco (ou sem preenchimento).
* Clique em "OK" para fechar a caixa de diálogo Formato e depois "OK" novamente para criar a regra de formatação condicional.
2. Macro VBA (para ajustar a altura da linha): *
Objetivo: Para definir a altura da linha para 0 para as linhas que correspondem à formatação. Esta é a etapa final para ocultar visualmente as linhas.
*
Etapas: 1.
Abra o editor VBA: Pressione `alt + f11` para abrir o editor do Visual Basic.
2.
Insira um módulo: No editor VBA, vá para "Inserir"> "Módulo".
3.
Cole o seguinte código: Cole este código VBA no módulo:
`` `VBA
Sub HiderwsBasedonformat ()
Dim RNG como alcance, célula como alcance
Dim WS como planilha
Dim CondFormat como formatCondition
'Alterar Sheet1 "para o nome da sua folha
Set ws =thisworkbook.sheets ("Sheet1")
'Altere A1:Z100 para o intervalo real em que a formatação condicional é aplicada
Definir rng =ws.Range ("A1:Z100")
'Faça um loop através de cada célula no intervalo
Para cada célula em RNG.Rows
'Suponha que inicialmente não se esconda
Cell.hidden =false
'Faça uma folga em todos os formatos condicionais
Para cada condformat em células.FormatConditions
Se predformat.type =XLEXPension então
'Avalie a fórmula, se verdade
Se ws.Value (condformat.formula1) então
Cell.hidden =true
Saída para
Final se
Final se
Próximo condformat
Próxima célula
Final sub
Sub UntideallRows ()
Dim WS como planilha
'Alterar Sheet1 "para o nome da sua folha
Set ws =thisworkbook.sheets ("Sheet1")
ws.rows.hidden =false
Final sub
`` `
4.
Personalize o código: *
`ws =thisworkbook.sheets (" Sheet1 ")` :Alterar `" Sheet1 "` Para o nome real da planilha onde está seus dados.
*
`set rng =ws.Range (" A1:Z100 ")` :Alterar `" A1:Z100 "` Para o mesmo intervalo de dados que você usou ao criar a regra de formatação condicional.
5.
Execute a macro: Volte para o editor VBA (se você o fechou) e pressione `f5` para executar a macro` hiderowsbasedonformat`. Como alternativa, no Excel, vá para a guia "Desenvolvedor" (se você não o vir, precisará ativá -lo nas opções do Excel)> "macros", selecione `HiderwsBasedonformat` e clique em" Run ".
6.
(opcional) Adicione um botão: Você pode inserir um botão na sua planilha (inserir -> Shapes -> Botão) e atribuir a macro `hiderwsbasedonformat`. Isso fornece uma maneira amigável de recuperar as linhas sempre que os dados mudarem. Você também pode adicionar um botão para `untideallRows`.
Explicação: *
Formatação condicional: Isso marca as linhas que queremos esconder.
*
VBA Macro: Esse código itera através das linhas, verificando se a formatação condicional se aplicar a cada linha. Se isso acontecer, ele define o `RowHeight` para 0, tornando a linha efetivamente invisível. Usamos a fórmula de formatação condicional como parte da macro, para que ela não seja apenas baseada no formato, pois isso pode mudar.
Considerações importantes: *
desempenho: Se você tiver um conjunto de dados muito grande (dezenas de milhares de linhas), essa abordagem * pode * ser lenta porque itera em cada linha. Pode haver soluções VBA mais complexas, mas mais rápidas, se o desempenho for crítico.
*
Recalculação: Quando seus dados alteram (e as regras de formatação condicional recalculadas), você precisará executar novamente a macro para recuperar as linhas se as condições tiveram alterado. Você pode automatizar isso usando um manipulador de eventos no VBA (por exemplo, `worksheet_change` para acionar a macro sempre que uma célula na folha for modificada). No entanto, tenha cuidado, pois executar uma macro em todas as mudanças também pode diminuir as coisas.
Exemplo com `worksheet_change` Evento (ocultação automática): Para tornar o esconderijo automático sempre que os dados mudarem, você pode modificar o código VBA da seguinte maneira. Em vez de colocar o código em um módulo, coloque -o na Janela de código * da planilha *:
1.
Clique com o botão direito do mouse na guia da folha (por exemplo, "Sheet1") no Excel e selecione "Exibir código". Isso abrirá o editor VBA diretamente para o módulo de código da planilha.
2. Cole o seguinte código na janela de código da planilha:
`` `VBA
Sub -planilha privada (alvo Byval como alcance)
'Ligue para a macro HiderowsBasedonformat sempre que a folha mudar.
HIDEROWSBASEDONFORMAT
Final sub
Sub HiderwsBasedonformat ()
Dim RNG como alcance, célula como alcance
Dim WS como planilha
Dim CondFormat como formatCondition
'Alterar Sheet1 "para o nome da sua folha
Set ws =thisworkbook.sheets ("Sheet1")
'Altere A1:Z100 para o intervalo real em que a formatação condicional é aplicada
Definir rng =ws.Range ("A1:Z100")
'Faça um loop através de cada célula no intervalo
Para cada célula em RNG.Rows
'Suponha que inicialmente não se esconda
Cell.hidden =false
'Faça uma folga em todos os formatos condicionais
Para cada condformat em células.FormatConditions
Se predformat.type =XLEXPension então
'Avalie a fórmula, se verdade
Se ws.Value (condformat.formula1) então
Cell.hidden =true
Saída para
Final se
Final se
Próximo condformat
Próxima célula
Final sub
Sub UntideallRows ()
Dim WS como planilha
'Alterar Sheet1 "para o nome da sua folha
Set ws =thisworkbook.sheets ("Sheet1")
ws.rows.hidden =false
Final sub
`` `
*
Importante: Substitua `" Sheet1 "` e `" A1:Z100 "` pelo seu nome de folha e intervalo de dados, como antes.
Agora, sempre que você altera um valor na planilha, a macro `hiderowsbasedonformat` será executada automaticamente, recuperando todas as linhas que atendem aos critérios de formatação condicional.
Lembre-se de salvar seu arquivo do Excel como uma pasta de trabalho com macro (.xlsm) para preservar o código VBA.
Essa abordagem fornece uma maneira flexível de "ocultar" as linhas no Excel com base em uma fórmula, embora exija o uso da formatação condicional e do VBA. Escolha o método que melhor se adapte às suas necessidades e ao nível de conforto com o VBA. A macro mais simples e ativada por botões geralmente é um bom ponto de partida.
