Dica Pro: Acomodar mudanças nas condições de funcionalidade formato condicional do Excel

A formatação condicional ajusta automaticamente os formatos como alterações de dados. Aprenda a construir um pouco de flexibilidade, fazendo referência a essas condições na folha em vez de na fórmula da regra.

Recurso de formatação condicional do Excel permite a formatação on-the-fly. Por exemplo, você pode destacar registros quando a data de vencimento é a data atual – ou você pode destacar os registros para um líder de projeto específico. No entanto, se a regra muda, você deve atualizar a regra. Se isso acontece com freqüência, você pode precisar de uma solução mais flexível.

Para acompanhar este artigo, você pode trabalhar com qualquer conjunto de dados simples ou baixar o exemplo xls ou xlsx livro.

O problema

Normalmente, formatos condicionais ajustar como você entra e os valores de atualização. É fácil para permitir mudanças esperadas – que é o que a função faz! Mas o que acontece quando a condição, em vez dos dados, as mudanças? Vamos considerar um conjunto de dados simples que destaca os registros para o líder de um projeto. Se o líder muda, você tem que atualizar as regras condicionais. Atualizando uma regra, uma vez não é um grande negócio. No entanto, a atualização dessas regras com freqüência ou atualizar várias regras de uma só vez é entediante!

O conjunto na Figura A simples dados é uma versão simplificada do que você pode encontrar.A regra condicional simples, = $ A2 = “Bill”, atualiza os registros para o líder do projeto, Bill. Se você alterar o nome, a regra remove o destaque. Aqui é onde as coisas ficam um pouco complicado. Quando você transferir o registro, você quer que o destaque a desaparecer, como mostrado na Figura A. O registro já não pertence a Bill, o líder do projeto. Isso é como formatação condicional funciona. Por outro lado, se João é o novo líder do projeto, você deve atualizar a regra subjacente, porque você quer o destaque para permanecer. O destaque pertence ao líder do projeto, não exclusivamente para Bill ou John.

Figura A

Figura A
A regra condicional destaca registros de Bill; quando você alterar o nome, o destaque desaparece.

Em um mundo perfeito, você simplesmente atualizar a regra, = $ A2 = “John”, e ir em frente.Quando as mudanças ocorrem com freqüência, você deve reavaliar a configuração. A posição de líder do projeto é a condição, não o nome da pessoa que passa a ser o líder do projeto, em determinado momento. Acomodando esta condição mudança em seu conjunto de dados e, posteriormente, na sua regra condicional, é a melhor solução. Você pode fazê-lo adicionando uma coluna para a posição de cada pessoa da equipe e usar essa coluna para esta regra particular. No entanto, fazê-lo no mundo real nem sempre é uma opção.

Uma solução referenciando

Tente não ficar muito preso no e (muito) exemplo artificial simples. O conjunto de dados de exemplo não é importante – a opção que eu estou oferecendo é. Quando alterar o conjunto de dados (para incluir uma condição mais estável) não é possível, você pode fazer referência a condição de mudança na folha como mostrado na Figura B.

Figura B

Figura B
Referenciando a condição de mudança na folha.

Como você pode ver, eu entrei o nome do líder do projeto na célula A2. Agora, vamos entrar com uma regra para combinar os dados na célula:

  1. Selecione o conjunto de dados que você deseja formatar – A6: B9.
  2. Na guia Início, clique em Formatação Condicional no grupo Estilos. No Excel 2003, escolha a formatação condicional no menu Formatar, e pule para o passo 5.
  3. Escolha Nova regra a partir da lista drop-down.
  4. No painel superior, selecione a usar uma fórmula para determinar quais células para formatar opção.
  5. Digite a fórmula = $ A6 = $ A $ 2 No Excel 2003, você deve escolher Formula é a partir do drop-down antes de entrar na fórmula. Combine o absoluto e relativo referência exatamente. Você quer que o Excel para considerar todas as linhas no conjunto de dados selecionado, mas a regra deve sempre fazer referência ao texto que você está combinando na célula A2.
  6. Clique em Formatar. Na guia Fill (Padrões em versões anteriores), selecione uma cor e clique em OK. Neste ponto, você pode visualizar o seu formato (Figura C).
  7. Clique em OK para retornar à folha.

Figura C

Figura C
Neste ponto, você pode visualizar o seu formato.

Provavelmente, você vai querer apagar a regra original, escolhendo Gerenciar Regras de a opção Formatação Condicional. Em seguida, escolha esta planilha dos Mostrar regras de formatação para drop-down. Selecione a regra original e, em seguida, clique em Excluir regra.Se a regra inclui outras condições, você terá que modificá-lo, em vez de excluí-lo.

Agora, você pode transferir o registro e alterar a líder do projeto, sem ajustar a regra subjacente. Por exemplo, se você digitar John na célula A6, o destaque desaparece. Bill ainda é o líder do projeto e não há registros para ele no conjunto de dados. Se você digitar John em A2, o destaque ajusta de acordo, como mostra a Figura D. Registro de João (em vez de Bill) é agora ressaltado.

Figura D

Figura D


Permitir mudanças condicionais.

Para complicar a situação

A condição mais complicada não altera esta solução. Basta encontrar o componente que muda, digite o valor de mudar para a folha, e referência a esse componente na fórmula da regra condicional. Por exemplo, vamos supor que você quer destacar os registros onde a data da coluna B é em relação à data atual, mas as mudanças relativas de factores. Às vezes você quer destacar datas que coincidem com a data atual. Você também pode querer destacar datas que são de uma semana para o futuro (ou passado). Você pode atribuir vários destaques condicionais, ou você pode consultar o valor mudando Figura E mostra o ajuste necessário para a folha -. Componente dia referenciando na célula B2.

Figura E

Figura E
Adicionar uma célula para o componente dia mudar.

Agora, vamos adicionar a nova regra:

  1. Selecione o conjunto de dados que você deseja formatar – A6: B9.
  2. Na guia Início, clique em Formatação Condicional no grupo Estilos. No Excel 2003, escolha a formatação condicional no menu Formatar, e pule para o passo 5.
  3. Escolha Nova regra a partir da lista drop-down.
  4. No painel superior, selecione a usar uma fórmula para determinar quais células para formatar opção.
  5. Digite a fórmula = $ B6 = HOJE () + $ B $ 2 Jogo do absoluto e relativo referência exatamente. Se você estiver usando o Excel 2003, não se esqueça de escolher Formula é a partir do drop-down para que você possa inserir a fórmula.
  6. Clique em Formatar. Na guia Fill (ou guia Padrão em versões anteriores), selecione uma cor e clique em OK. Neste ponto, você pode visualizar o seu formato (Figura F).
  7. Clique em OK para retornar à folha.

Figura F

Figura F
Criar uma nova regra de formatação.

A figura L mostra o formato condicional resultante. Como você pode ver, o destaque é dependente do valor na célula B2. Cinco dias de 10 de agosto é dia 15 de agosto Da mesma forma, o dia antes de 10 de agosto é 09 de agosto, eo formato condicional atualiza em conformidade. (Se você estiver trabalhando com o arquivo de exemplo para download, lembre-se que a data atual não vai ser o mesmo que o dia que eu tiro essas figuras, de modo que o formato vai ajustar em conformidade.)

Figura G

Figura G


Um destaque baseado em um número de dias em mudança.

Eu adicionei a função HOJE () para B4 para que você possa ver a data real. A técnica não requer esta função; é uma conveniência visual.

Acomodando mudança e adição de flexibilidade

Se a sua atualização condicional é uma mudança de uma só vez, você não precisa ajustar sua folha – basta atualizar a regra. Se a condição muda frequentemente, trabalhar essas atualizações em sua folha de adicionar um pouco de flexibilidade. Condições de mudança pode ser facilmente acomodado desta forma quando usando o recurso de formatação condicional do Excel.

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s

%d blogueiros gostam disto: