Este tutorial demonstrará como criar gráficos do Excel com formatação condicional em todas as versões do Excel: 2007, 2010, 2013, 2016 e 2022.
A formatação condicional é a prática de atribuir formatação personalizada às células do Excel - cor, fonte, etc. com base nos critérios (condições) especificados. O recurso ajuda a analisar dados, encontrar valores estatisticamente significativos e identificar padrões em um determinado conjunto de dados.
Mas, infelizmente, o Excel não oferece nenhuma ferramenta integrada para aplicar a formatação condicional aos gráficos do Excel.
No entanto, com uma gota da mágica da planilha, nenhum problema é intransponível. Neste tutorial, você aprenderá a criar um gráfico dinâmico com formatação condicional, onde os pontos de dados plotados são formatados automaticamente com base em seus respectivos valores reais.
Como exemplo, dê uma olhada neste gráfico de colunas, detalhando o desempenho de um gerente de vendas em um período de oito meses. Observe como a cor da coluna se ajusta automaticamente, dependendo de seu valor subjacente:
Começando
Considere este conjunto de dados de amostra contendo dados de desempenho de vendas:
Antes de começar, mova seu conjunto de dados para que você tenha três linhas vazias acima da tabela com seus dados brutos. As linhas serão usadas para definir regras de formatação condicional.
Etapa # 1: Prepare os dados do gráfico.
Para fazer as coisas funcionarem, você precisa adicionar alguns dados extras do gráfico à equação. Para sua conveniência, dê uma olhada em como os dados do gráfico devem ficar ao final da fase de preparação:
Nossa primeira etapa é definir os valores de limite (condições) que devem dividir os valores reais em várias categorias, tornando possível traçá-los como séries de dados separadas.
Na captura de tela acima, por exemplo, você pode ver que os números de vendas de um determinado mês devem estar na faixa de $ 90.000 a $ 150.000 para serem considerados satisfatórios.
Para criar as regras, use as três linhas vazias acima do conjunto de dados:
- Linha 1 (etiqueta): Use essas células para rotular as regras de formatação.
- Linha 2 (valor mínimo): Os valores nesta linha definem o limite inferior para cada condição.
- Linha 3 (valor máx.): Da mesma forma, esses valores definem os limites superiores.
Com este método, você pode criar quantas regras de formatação condicional desejar - apenas certifique-se de que os intervalos de valores não se sobreponham.
Conforme você continua avançando na configuração de seu gráfico, projete itens de legenda de gráfico personalizados que fornecerão contexto para o gráfico. Insira esta fórmula em C4 e copie-o para D4 arrastando a alça de preenchimento:
| 1 | = C1 & ": de" & TEXTO (C2, "$ #, ##") & "para" & TEXTO (C3, "$ #, ##") |
A fórmula reúne um rótulo dinâmico de aparência organizada com base nas regras de formatação condicional definidas anteriormente. A função TEXT formata os valores como moeda. Mas se o seu tipo de dados for diferente, aplique esta fórmula:
| 1 | = C1 & ": de" & TEXTO (C2, "#, ##") & "para" & TEXTO (C3, "#, ##") |
Ou este quando você trabalha com porcentagens:
| 1 | = C1 & ": de" & TEXTO (C2, "#%") & "para" & TEXTO (C3, "#%") |
Finalmente, encontre os valores do gráfico. Digite a seguinte fórmula em C5, copie-o para C12 e através das células correspondentes em coluna D:
| 1 | = SE (E (C $ 2 <$ B5, $ B5 <= C $ 3), $ B5, NA ()) |
A fórmula compara um determinado valor real em coluna B contra os valores limite especificados com a ajuda das funções IF e AND. Se o valor estiver dentro do intervalo, ele será copiado para esta coluna. Caso contrário, a fórmula retornará o erro # N / A para evitar que o mesmo valor seja plotado várias vezes.
Etapa 2: Configure um gráfico de colunas.
Tendo reunido todos os dados do gráfico, configure um gráfico de colunas simples ou um gráfico de barras como alternativa:
- Destaque todos os dados do gráfico, exceto as colunas que contêm os valores reais e as regras, mantendo pressionado o Ctrl chave (A4: A12 e C4: D12).
- Vou ao Inserir aba.
- Selecione “Inserir coluna ou gráfico de barras.”
- Escolher "Coluna agrupada / Barra agrupada.”
Como resultado, você terá um gráfico com pontos de dados plotados com base nas regras de formatação condicional - exatamente como prometido:
Etapa # 3: Modifique os valores de sobreposição e largura da lacuna.
Antes de fechar este tutorial, corrija o problema das colunas mal colocadas causadas pelos valores vazios invisíveis (os erros # N / A) plotados no gráfico.
Clique com o botão direito em qualquer uma das colunas e selecione “Formatar série de dados”No menu contextual que aparece.
No painel de tarefas, altere a posição e a largura das colunas:
- Mudar para o Opções de série aba.
- Mudar "Sobreposição de série" para "100%.”
- Colocou o Largura da lacuna para "60%.”
Etapa 4: Ajuste o esquema de cores.
Por fim, adicione os toques finais. Sem fechar o Formatar série de dados painel de tarefas, modifique o esquema de cores do gráfico:
- Vou ao Preenchimento e linha aba.
- Debaixo "Preencher," escolher "Preenchimento sólido.”
- Clique no "Cor de Preenchimento”Ícone e escolha verde na paleta de cores.
Uma vez lá, recolorir as outras séries de dados, alterar o título do gráfico e seu gráfico totalmente dinâmico com formatação condicional estará pronto para uso!
