sexta-feira, 24 de agosto de 2012

Aplicando formatação condicional em gráficos


Sem sombra de dúvidas, a utilização de formatação condicional oferece inúmeras alternativas aos usuários que desejam melhorar o aspecto visual de seus Dashboards e painéis de indicadores. Daí que uma dúvida que a maioria das pessoas sempre procuram esclarecer é: há como aplicar formatação condicional a gráficos?

Há duas respostas para essa pergunta: Não e Sim. Não porque o Excel não disponibiliza nenhuma funcionalidade que permita aos usuários formatarem seus gráficos condicionalmente. E Sim porque, com pequenos ajustes, é possível personalizar um gráfico condicionalmente. Então, mãos à obra.

Digamos que sua empresa efetue o acompanhamento mensal das vendas em uma planilha, na qual são lançados os valores acumulados mês-a-mês. Foi definido o seguinte cenário para analisar as vendas:

  • Vendas maiores do que R$ 80.0000 – Acima da meta (semáforo verde)
  • Vendas entre R$ 50.000 e R$ 80.000 – Resultado aceitável (semáforo amarelo)
  • Vendas menores que R$ 50.000 – Resultado ruim (semáforo vermelho)

Vou simular a construção desse gráfico com formatação condicional usando o Excel 2013. Mas o procedimento para as versões 2007 e 2010 é o mesmo J

Em primeiro lugar, é necessário obter a planilha com os valores de vendas mensais:

Como não é possível formatar condicionalmente o gráfico de maneira automática, devemos criar uma tabela auxiliar a direita, copiando os meses de Janeiro a Dezembro e criando os cenários Ruim, Médio e Bom. Veja o exemplo:


O preenchimento das colunas Ruim, Médio e Bom se dará de acordo com o cenário discutido acima. Assim, se o valor de venda for inferior a R$ 50.000, por exemplo, ele será repetido na coluna Ruim, ficando as colunas Médio e Bom vazias, e assim por diante.

Para exemplificar na prática, devemos ir à coluna Ruim. Nela, a seguinte fórmula será construída:

=SE(C3<50000;C3;0)

Queremos dizer o seguinte: se o valor de vendas for inferior a R$ 50.000, então ele deverá ser copiado na coluna Ruim. Caso contrário, a coluna receberá o valor 0 (zero).

Para as demais colunas, as fórmulas serão:

Coluna Médio: =SE(E(C3>=50000;C3<80000);C3;0)
Coluna Bom: =SE(C3>=80000;C3;0)

O resultado da tabela auxiliar será o seguinte:

E é com base na tabela auxiliar que o gráfico será gerado. É importante notar que, de acordo com os critérios estabelecidos nas fórmulas, apenas uma das três colunas recebe informação.

Selecione a tabela auxiliar e insira um gráfico de colunas. O resultado será:


É possível perceber que o gráfico aplica automaticamente uma cor diferente para cada item, de acordo com seu rótulo (Ruim, Médio e Bom). Entretanto, o espaçamento entre as colunas do gráfico não é o ideal, pois como são três as colunas que alimentam o gráfico, ele reserva espaço para seu resultado. Para melhorar o aspecto visual, clique bom o botão direito do mouse em uma das colunas e escolha a opção Formatar Série de Dados. Na opção Sobreposição, defina o valor como 100%:


Para finalizar, você pode formatar a cor das colunas de acordo com sua preferência (a sugestão é usar o esquema de semáforo). Como a única informação visível para o usuário final será o gráfico, é interessante posicioná-lo sobre a tabela auxiliar:


Se quiser tentar na prática, você pode baixar a planilha aqui. Já este post, em formato digital, está disponível aqui.

Até a próxima!!



10 comentários:

  1. Boa tarde. E se você quer colocar os valores nas barras, como faz?

    ResponderExcluir
  2. Olá,

    Caso seja necessário adicionar os valores nas barras, basta clicar no gráfico e clicar em Adicionar Elemento Gráfico (Guia Design). Em seguida, escolha rótulo de dados.

    O problema é que o Excel vai adicionar o rótulo inclusive das colunas que não contém valores, obrigando que você remova esses valores manualmente.

    Espero ter ajudado.

    ResponderExcluir
    Respostas
    1. Isso também pode ser contornado criando uma terceira série de dados e aplicando rótulos somente à ela. Para manter o visual de semáforo proposto pelo Raphael nesse tutorial, siga estes passos:

      - Crie a série "Vendas" e acrescente seus dados normalmente.
      - Depois, aplique os rótulos de dados para esta nova série.
      - Por último, reordene a disposição das séries de dados no painel de Selecionar Dados, fazendo com que a série "Vendas" seja a primeira, de cima para baixo, pois assim ela ficará abaixo das séries coloridas.

      Caso seja uma série mensal dividida por dias do mês e ainda não tenha todos os dados, selecione, em todas as séries de dados, apenas o intervalo onde os mesmos existam.

      Espero ter ajudado vocês!

      Thayan Oliveira
      thayanoliveira@outlook.com

      Excluir
  3. Eu quero saber o código fonte para esta operação. Alguém tem?

    ResponderExcluir
  4. Olá, Raphael!
    Muito explicativo o seu post! Eu consegui fazer no gráfico de barras, mas não consegui no de Rosca. Existe a possibilidade de fazer essa formatação condicional em um gráfico de rosca sem as séries ficarem sobrepostas?
    Se houver, vc poderia me explicar, por favor?
    Obrigada!
    Vanessa Antunes.

    ResponderExcluir
    Respostas
    1. Olá Vanesa

      Tentei diferentes alternativas para fazer isso no gráfico de pizza, mas por sua dinâmica não incluir escalas para dois eixos, também não consegui nada satisfatório =-(

      Excluir
  5. Cara muito boa sua dica, tudo muito bem explicado, me ajudou muito.

    ResponderExcluir