Visual Dicas: Excel

IV) Regras Manuais

Crie maneiras para visualizar melhor a sua planilha com a formatação condicional (IV)

No artigo anterior da série, Valorize a sua planilha no Excel explorando mais os recursos da formatação condicional (III), ressaltamos as Barras de Dados, Escalas de Cor e Conjunto de ícones. Neste artigo mostraremos exemplos para o uso de Regras Manuais e como Localizar células com formatação condicional.

Para quem não leu o primeiro, o segundo e o terceiro artigo da série, lembramos que a formatação condicional tem como objetivo principal diferenciar células com base nos valores da planilha. A diferenciação ocorre através de cores de preenchimento, fonte e borda ou através de ícones (recursos incluídos no office 2007 e 2010), valorizando e melhorando muito a visualização e o entendimento da mesma.

Recomendamos que leia e siga os exemplos do primeiro, segundo e terceiro artigo, antes de dar sequência a este. Para isto, clique nos links abaixo:

Valorize a sua planilha no Excel explorando mais os recursos da formatação condicional (I) – Realçar Regras das Células

Valorize a sua planilha no Excel explorando mais os recursos da formatação condicional (II) – Regras de Primeiros/Últimos

Valorize a sua planilha no Excel explorando mais os recursos da formatação condicional (III) – Barras de Dados, Escala de Cor e Conjunto de ícones

Para ver se você realmente assimilou os recursos, monte uma planilha similar a que mostramos abaixo,

Configure as seguintes regras (seguir a ordem apresentada) e aplique nas células E5:J8:

Valores acima da média (vermelho)
Realçar células com valor > 20 (cor verde)
Escala de Cores Graduada (3 cores – verde, vermelho e amarelo)
Conjunto de ícones com 3 cores (verde, vermelho e amarelo)

Veja se a sua planilha ficou parecida com esta abaixo:

Localizando células com Formatação Condicional

Localize a Formatação

Encontrar rapidamente todas as células com formatação condicional pode ser uma tarefa difícil, especialmente em grandes planilhas muito coloridas.

Muitas vezes é necessário localizar estas formatações quando você precisa salvar a planilha em versões anteriores (2003) ou para o OpenOffice, que não possuem este recurso otimizado.

Para a localização acesse [Início] [Localizar e Selecionar] e clique em[Formatação Condicional]. Faça o teste na planilha que você criou anteriormente.

Observe que após acessar os comandos, toda célula formatada ficará selecionada automaticamente para alterações ou exclusão.

Regras Manuais - Gerenciando Regras

Editar Regras

Após ter localizado e selecionado as células formatadas você poderá alterá-las através do comando [Gerenciar Regras].

Para isto, acesse [Formatação Condicional] e clique em [Gerenciar Regras…]

Então, na janela do Gerenciador de Regras de Formatação Condicional, onde são mostradas todas as regras incluídas na planilha, selecione aquela que deseja editar.

No nosso exemplo vamos escolher a regra automática [Grupo de Ícones]. Selecione então esta linha no gerenciador, clicando-se sobre Conjunto de ícones.

Exemplo 1

Alterar a regra automática Conjunto de Ícones
Colocar ícone verde somente nas células com valores >20 (B1)
Colocar ícone vermelho somente nas células com valores <10 (B2)
Não mostrar os ícones amarelos

Após selecionado a regra a ser alterada, clique no botão [Editar Regra].

1) Ícone verde (quando o valor for)

Altere [Tipo] para [Fórmula] (para utilizar o parâmetro B2, cujo o valor é 20). O padrão de Tipo para o Conjunto de Ícones é [Porcentagem].

Altere [Valor] para [$B$2] (os símbolos $ antes e depois de B fixam a posição da célula).

2) Ícone amarelo (quando < )

Altere [Tipo] para [Fórmula] (para utilizar o parâmetro B1, cujo o valor é 10). O padrão de Tipo para o Conjunto de Ícones é [Porcentagem].

Altere o símbolo [>=] para [>] fazendo como que o ícone vermelho fique valendo quando <=.

Altere [Valor] para [$B$1] (os símbolos $ antes e depois de B fixam a posição da célula).

Veja como ficou a configuração:

Depois de tudo alterado clique no botão <OK> e <OK> novamente para fechar a janela do Gerenciador.

Veja então como ficou a planilha com as alterações realizadas…

Observe que os ícones verdes e os vermelhos estão corretos, porém os ícones amarelos ainda aparecem na planilha. Para que não sejam mais mostrados, como pede o nosso exemplo, devemos prosseguir conforme descrito abaixo:

1) Definir valores entre 10 (B1) e 20 (B2)

Selecione novamente as células que contém as regras para a formatação condicional, que no nosso exemplo são E5:J8 e B2. (Utilize [Localizar e Selecionar] e [Formatação Condicional]).

Em [Formatação Condicional] clique em [Gerenciar Regras].

Selecione a regra Valor da célula > 20 e clique no botão [Editar Regra]

Altere [é maior do que] para [está entre] e identifique [=$B$1] e [=$B$2] nos campos de comparação.

Clique em formatar e mude a cor do preenchimento para branco e a cor da fonte para preto. Veja como ficou a configuração:

Tudo certo, clique em <OK>.

Para darmos continuidade vamos entender um pouco da hierarquia sequencial das regras de formatação condicional. É bem simples, veja…

O Excel executa as regras na ordem mostrada no Gerenciador de Regras, ou seja:

Primeiro executa completamente a regra da primeira linha, depois da segunda linha e assim sucessivamente…

Para alterar a ordem sequencial das regras basta selecionar a regra que queremos mover e clicar nas setinhas ao lado direto do botão [Excluir Regras].

Se selecionarmos a caixa [Parar se Verdadeiro] dizemos ao Excel para que as regras sucessoras não sejam sobrepostas à regra selecionada, ou seja, todas as células que referentes a regra selecionada não poderão ser alteradas pelas regras seguintes. Bom, se você achou isto um pouco confuso, vamos entender isto na prática seguindo o nosso exemplo.

Vamos agora alterar a ordem das regras do exemplo, passando para a seguinte ordem no Gerenciador de Regras:

Escalas de Cores Graduada
O Valor da célula está entre…
Conjunto de Ícones
Acima da Média

Obs.: Utilize as setas [Mover para cima] e [Mover para baixo] para isto.

Selecione a regra [O Valor da célula esta entre] e clique na caixa de seleção [Parar se Verdadeiro].

Desta forma o Excel irá executar primeiro a regra [Escala de Cores Graduada], depois a regra [O Valor da célula está entre…] que está marcada como [Parar se Verdadeiro]. Na sequência, o Excel irá executar a regra [Conjunto de Ícones] porém, sem sobrepor a regra anterior, ou seja, não alterando as células com valores entre 10 (B1) e 20 (B2).

Clique no botão [OK] e veja então como fica a planilha do exemplo:

Pronto, agora nossa tabela está conforme pede o exemplo, sem os ícones amarelos.

Se desejar também eliminar o preenchimento das células entre os valores 10 e 20 mude a ordem da regra [Escala de Cores Graduada] abaixo da Regra com a seleção [Parar se Verdadeiro], que no caso é o [Valor da célula está…]

Veja então que a tabela ficará com um visual bem menos poluído,

Agora para finalizarmos e incluirmos os valores 10 e 20 na regra do conjunto de ícones, mude os valores na regra [O valor da célula está entre…] conforme mostrado abaixo:

Pronto, nosso exemplo está completo, veja abaixo:

Excluir Regras

Para excluir uma regra, selecione as células formatadas e acesse [Início] [Formatação Condicional] e [Gerenciar Regras…].

Dando continuidade no exemplo anterior, vamos deletar as regras: Escala de Cores Graduadas e [Acima da Média]

No Gerenciador de Regras, selecione as regra [Escala de Cores Graduadas] e clique no botão [Excluir Regra]

Faça o mesmo para a regra [Acima da Média] e clique no botão [OK]

Obs.: Como já mostrado anteriormente nos artigos da série, também é possível excluir todas as regras das células selecionadas ou da planilha toda. Para isto selecione [Início] [Formatação Condicional] e cliquem em [Limpar Regras das Células Selecionadas] ou [Limpar Regras da Planilha Inteira].

Nova Regra

Utilizando-se do Gerenciador de Regras de Formatação Condicional, basta clicar no botão [Nova Regra], ou seja:

Selecione as células através do comando [Localizar] e em [Formatação Condicional] [Gerenciar Regras…] clique em [Nova Regra].

Vamos então verificar este recurso através do exemplo abaixo:

Exemplo 2: Utilizando a planilha do exemplo 1, preencha em azul claro as células com valores exclusivos (que não se repetem)

Em [Nova Regra] selecione na janela Nova Regra de Formatação a linha [Formatar apenas valores exclusivos ou duplicados]

Clique no botão [OK] e novamente em [OK] na janela do Gerenciador…

Acrescentando Regras Manuais

Exemplo 3: Utilize a planilha do exemplo 2 e acrescente as seguintes regras:

Se a somatória das células de cada mês for >= 50 (Verde)
Se a somatória das células de cada mês for < 50 (Vermelho)
As cores do preenchimento Verde ou Vermelho devem estar posicionadas nas células que indicam os meses (E4:J4).

Selecione as célula E4. Em [Início] [Formatação Condicional] clique no botão [Nova Regra].

Selecione [Usar uma fórmula para determinar quais células devem ser formatadas]

Digite a fórmula =SOMA(E5:E8)>=50. Em [Formatar] defina o preenchimento como verde e clique em [OK].

Mantendo selecionada a célula E4, acrescente uma nova regra utilizando a fórmula =SOMA(E5:E8)<50 . Em [Formatar] defina o preenchimento como vermelho e clique em [OK].

Veja como fica a planilha:

Agora com a célula E4 selecionada, em [Início] clique sobre o ícone [Formatar Pincel] e aplique sobre as célula F4:J4.

Pronto, veja o exemplo 3 finalizado.

Dica: Teste todas as regras manuais de formatação condicional

Obs.: Em todos os exemplos, se for alterado qualquer valor das células configuradas com a formatação condicional, a planilha irá se ajustar automaticamente às regras de formatação pré-estabelecidas. Faça os testes…

Bem, assim finalizamos as dicas e informações sobre a Formatação Condicional, acrescentando para você leitor uma noção geral sobre este assunto. Treine e experimente todas as possibilidades deste excelente recurso, que bem aplicado poderá ajudar você a criar planilhas cada vez mais elaboradas…

Utilize a sua criatividade e bons trabalhos!

Todos Artigos da Série “Formatação Condicional”

Realçar Regras das Células: Valorize a sua planilha no Excel explorando mais os recursos da formatação condicional (I)

Regras do Primeiro/Últimos: Valorize a sua planilha no Excel explorando mais os recursos da formatação condicional (II)

Barra de Dados, Escalas de Cor e Conjunto de Ícones: Valorize a sua planilha no Excel explorando mais os recursos da formatação condicional (III)

até mais… [ byALF ]

Continue lendo

III) Barras de Dados, Escalas de Cor e Ícones

Crie maneiras para visualizar melhor a sua planilha com a formatação condicional (III)

No artigo anterior da série, Valorize a sua planilha no Excel explorando mais os recursos da formatação condicional (II), ressaltamos as Regras de Primeiros/Últimos. Neste artigo mostraremos exemplos para o uso de Barras de Dados, Escalas de Cor e Conjunto de ícones.

Para quem não leu o primeiro e o segundo artigo da série, lembramos que a formatação condicional tem como objetivo principal diferenciar células com base nos valores da planilha. A diferenciação poderá ser através de cores ou ícones (recursos incluídos no office 2007 e 2010), melhorando a visualização e o entendimento da mesma.

Recomendamos que leia e siga os exemplos do primeiro artigo e do segundo artigo, antes de dar sequência a este. Para isto, clique nos links abaixo:

Valorize a sua planilha no Excel explorando mais os recursos da formatação condicional (I) – Realçar Regras das Células

Valorize a sua planilha no Excel explorando mais os recursos da formatação condicional (II) – Regras de Primeiros/Últimos

Utilizando a formatação condicional automática (III)

3) Barras de dados

Selecione a célula ou células e acesse: [Início] [Formatação Condicional] e [Barras de Dados]

Barras de Dados

Exemplo 1: No nosso exemplo, vamos realçar células de acordo os valores, onde a barra mais longa representa o valor mais alto.

Selecione as células C2:C11, e em [Barras de Dados] escolha a cor para o preenchimento.

Pronto, veja como ficou a planilha deste exemplo:

Se desejar outras cores, clique em [Mais Regras]

Em [Cor da Barra] escolha a nova cor de clique no botão [OK].

4) Escalas de cores
Selecione a célula ou células e acesse: [Início] [Formatação Condicional] e [Escalas de Cores]

Exemplo 2: No nosso exemplo, vamos realçar células com escalas de cores (2 ou 3 cores), diferenciando os maiores e os menores valores da tabela.

Valores mais altos = Escala de cor Verde
Valores intermediários = Escala de cor Amarela
Valores mais baixos = Escala de cor Vermelha

Selecione as células E5:J8, e em [Escalas de Cor] escolha as cores para o preenchimento. No exemplo vamos utilizar 3 cores sendo as escalas de Verde, Amarelo e Vermelho.

Clique sobre as cores escolhidas. Veja como ficou então a planilha do exemplo:

Observe que os valores mais altos ficam com o tom mais forte. O mesmo acontece com os valores mais baixos e os intermediários. No caso dos valores mais baixos, o menor valor tem o tom mais forte.

5) Conjunto de ícones

Selecione a célula ou células e acesse: [Início] [Formatação Condicional] e [Conjunto de Ícones…]

O conjunto de ícones funciona como as escalas de cor, só que identificando os maiores e os menores valores através de ícones.

Exemplo 3: No nosso exemplo, vamos realçar células com ícones (3 a 5 ícones), diferenciando os maiores e os menores valores da tabela, conforme as regras abaixo:

Valores mais altos = Seta verde para cima
Valores intermediários = Setas em amarelo (3 setas)
Valores mais baixos = Seta vermelha para baixo

Selecione as células E5:J8, e em [Conjunto de Ícones] escolha o conjunto de 5 setas coloridas.

Clique sobre os ícones escolhidos e pronto. Veja o resultado do nosso exemplo abaixo:

Observe que os valores intermediários possuem 3 tipos de setas: valores próximos à média, valores um pouco acima da média e valores abaixo da média.

Você também poderá mesclar realces coloridos com conjuntos de ícones. Veja o exemplo abaixo (Faça os testes também…).

Neste exemplo foi utilizado escala de cores e conjuntos de ícones. No entanto, você poderá utilizar várias regras na mesma célula, desde que facilite a visualização e o entendimento da planilha. Porém, não exagere…

Conjunto de ícones Excel 2007 X Excel 2010

No Excel versão 2010 pouco foi alterado em relação à versão 2007 quando tratamos de Formatação Condicional. Apenas foram acrescentados alguns novos conjuntos de ícones. Veja o que mudou:

Excel 2007 Excel 2010

Em todos os exemplos, se for alterado qualquer valor das células configuradas com a formatação condicional, a planilha irá se ajustar automaticamente às regras de formatação pré-estabelecidas.

No próximo artigo veremos como Localizar a Formatação e como definir Regras Manualmente (Gerenciar Regras, Novas Regras ou Mais Regras). Neste artigo vimos: Barras de Dados, Escalas de Cor e Conjuntos de Ícones.