7Microsoft Office Excel 2010A formatação condicional para as classes contra alvos

Assim, na minha outra vida, eu sou realmente um professor de ciências, e como ele geralmente fica rodada que eu sou “bom com os computadores” Eu geralmente se pediu para dar uma olhada em algumas coisas. Duas vezes nos últimos meses eu fui perguntado se eu configurar a formatação condicional no Excel para colorir os resultados dos testes de estudante código em comparação com suas notas alvo. Embora não exatamente relacionada com o conteúdo central deste site, ainda é DIY ajuda técnica, então decidi publicar um conjunto completo de instruções aqui. Aproveitar…

Microsoft Excel Conditional Formatting: Lookup TablesPrimeiro você precisa de uma folha de pesquisa

  • Criar uma nova folha no livro.
  • Preencha 2 - colunas à esquerda, com graus (1c, 1b, 1a, 2c etc) e da direita, com um valor "score" (pode ser apenas 1, 2, 3, 4, 5 etc)
  • Preencha 2 colunas adicionais com uma “pesquisa inversa”, que é a mesma que antes, mas com a 2 colunas revertida
  • Destaque a mesa mão esquerda completo que você criou, clique direito sobre ele, e selecione "definir o nome". Dê-lhe um nome, e.g. “Pesquisar”
  • Realce a segunda tabela que você criou, clique direito e definir outro nome. Dê-lhe um nome, e.g. "Reverse_lookup"

Microsoft Excel Conditional Formatting: Define NameCriando as planilhas

  • Agora crie uma planilha para cada classe, grupo ou ano (como ternos).  Incluir uma coluna para as classes alvo e várias colunas para resultados de testes ou de avaliação (e.g. como a seguir)
  • Nota neste exemplo que os dados alvo é em C3 - C5. Os resultados estão em D3 - F5

Microsoft Excel Conditional Formatting: Example Markbook

Construindo a formatação condicional básica

  • Seleccione a célula D3 - parte superior esquerda das células de dados de resultados. Verifique se você está na guia “casa” e clique em “formatação condicional” e selecione “nova regra”
  • Selecione "usar uma fórmula para determinar quais células para formatar"
  • O que nós queremos fazer é comparar a célula atual para o destino, mas não é tão simples como isso, por causa da maneira como o Excel compara valores - por isso precisamos usar as tabelas de pesquisa.
  • Precisamos também 5 regras - 2 subníveis ou mais abaixo, 1 abaixo, no alvo, 1 acima e 2 ou mais acima. Você pode criar mais regras se você quiser mais detalhes ou menos, se você quer apenas abaixo, em e acima do alvo.
  • A eventual fórmula para 2 subníveis ou mais abaixo será =VLOOKUP($C3,Lookup,2,FALSE)-1>VLOOKUP(D3,Lookup,2,FALSE)

    Microsoft Excel Conditional Formatting: New Rule Formula

  • O C3 na primeira parte refere-se à célula-alvo topo, e o D3 refere-se à célula de resultado real superior. O "Lookup" refere-se ao intervalo da tabela de pesquisa que nomeamos anteriormente.
  • Decompô-lo - nós estamos olhando-se valores numéricos para tanto o alvo quanto a pontuação real na célula atual. Porque queremos 2 subníveis ou mais abaixo tomamos 1 fora do alvo e dizem que ainda deve ser maior do que a pontuação. o $ antes de o C de C3 assegura que, quando se usar a mesma fórmula de outras colunas que ainda vai ser comparada com a coluna alvos.
  • Agora clique no formato e aplicar a formatação desejada. Eu uso uma cor de vermelho luz de fundo
  • Agora nós criamos 1 de 5 regras para uma única célula.

Microsoft Excel Conditional Formatting: Format CellsExpandindo a formatação condicional para várias regras

  • Próximo repetir o processo para criar 4 mais regras.
  • Quando tudo terminar minha 5 exemplos ficaria como abaixo. Somente a comparação e possível adição ou mudança subtração entre eles. Além disso, observe a ordem (que chegamos a próxima)
= VLOOKUP($C3, pesquisa,2,FALSO)-1>VLOOKUP(D3, pesquisa,2,FALSO)
= VLOOKUP($C3, pesquisa,2,FALSO)>VLOOKUP(D3, pesquisa,2,FALSO)
= VLOOKUP($C3, pesquisa,2,FALSO)= VLOOKUP(D3, pesquisa,2,FALSO)
= VLOOKUP($C3, pesquisa,2,FALSO)+1<VLOOKUP(D3, pesquisa,2,FALSO)
= VLOOKUP($C3, pesquisa,2,FALSO)<VLOOKUP(D3, pesquisa,2,FALSO)
  • Eu uso vermelha média, luz vermelha, amarelo, verde médio e verde claro (naquela ordem) para cores de fundo

Classificando a ordem das regras

  • Finalmente, temos de garantir que as regras estão na ordem certa, e aplicá-los a todas as células resultados, não apenas o resultado única célula D3.
  • Clique no botão formatação condicional novamente e escolha “gerenciar regras ...”
  • Certifique-se de que as regras são na ordem certa - como acima (vermelho médio, luz vermelha, amarelo, verde médio, luz verde)Microsoft Excel Conditional Formatting: Rules Manager
  • Clique em “Aplicar a” caixa e digite a gama de células que contêm resultados. No meu exemplo este é =$D$3:$F$5.  Colocar isso em tudo 5 "Aplica-se a" caixas
  • Esta folha agora deve funcionar totalmente com formatação condicional

Expandindo facilmente folhas adicionais

  • Para se candidatar a outras folhas simples copiar e colar uma única célula que já tem formatação condicional aplicada a ele, para a nova folha.
  • Em seguida, clique no botão de formatação condicional, selecione gerenciar regras, e alterar o 5 “Aplica-se a” caixas para apontar para as células da nova página que precisam de formatação condicional

Leave a Reply

7 Comentários

CMChloé Moore

Oi John,

Você tem um exemplo de planilha que você fez? Strug­gling fol­low­ing the steps here!

Resposta
NNikki

Eu adoraria voltar a postar este artigo em meu próprio site, isso seria aprovado?

Resposta
GZGary Zawadzki

Oi,
Grande tutorial - graças. Eu tenho 3 cores diferentes funcionando perfeitamente, mas o 2 acima e 2 a seguir não são formatação a suas cores corretas. Eles estão mudando para vermelho e verde, mas não as máscaras que eles precisam para mudar para. Todas as sugestões por favor?

Obrigado,

Gary

Resposta
JSJon Scaife

Desculpe pelo longo atraso na resposta Gary - Estive ocupado migrar o site para o nosso novo VPS. Será que você consegue resolver isso? Se você não fez, em seguida, por todos os meios zap-me sobre o seu arquivo do Excel para ter um olhar para. Vou verificar novamente este guia no dia seguinte ou 2 para se certificar de que não existem quaisquer de erro de digitação ou partes enganosa, tem sido conhecida a acontecer 😉

Resposta
EEmma

Tenho seguido todas as instruções acima, mas nenhuma das minhas células têm ido colorido?? Todas as sugestões para onde estou indo errado??

Resposta
JSJon Scaife

Eu suponho que você tenha preenchido com os dados, ea coluna de destino também é preenchido com dados?
Por todos os meios zap-me o arquivo mais e eu vou dar uma olhada rápida - Eu tenho você e-mail

Resposta