Análise de dados do Excel - Visualização de dados

Você pode exibir seus relatórios de análise de dados de várias maneiras no Excel. No entanto, se os resultados da análise de dados podem ser visualizados como gráficos que destacam os pontos notáveis nos dados, seu público pode compreender rapidamente o que você deseja projetar nos dados. Isso também causa um bom impacto no seu estilo de apresentação.

Neste capítulo, você aprenderá a usar gráficos do Excel e recursos de formatação do Excel em gráficos que permitem apresentar os resultados da análise de dados com ênfase.

Visualizando Dados com Gráficos

No Excel, os gráficos são usados para fazer uma representação gráfica de qualquer conjunto de dados. Um gráfico é uma representação visual dos dados, em que os dados são representados por símbolos como barras em um Gráfico de Barras ou linhas em um Gráfico de Linhas. O Excel fornece muitos tipos de gráficos e você pode escolher aquele que se adapta aos seus dados ou pode usar a opção Gráficos recomendados do Excel para visualizar os gráficos personalizados para seus dados e selecionar um deles.

Consulte o Tutorial de gráficos do Excel para obter mais informações sobre os tipos de gráficos.

Neste capítulo, você compreenderá as diferentes técnicas que podem ser usadas com os gráficos do Excel para destacar os resultados da análise de dados com mais eficácia.

Criação de gráficos de combinação

Suponha que você tenha a meta e os lucros reais para o ano fiscal de 2015-2016 obtidos em diferentes regiões.

Criaremos um gráfico de colunas agrupadas para esses resultados.

Conforme você observa, é difícil visualizar a comparação rapidamente entre as metas e o real neste gráfico. Não tem um impacto real nos seus resultados.

A melhor maneira de distinguir dois tipos de dados para comparar os valores é usando gráficos de combinação. No Excel 2013 e nas versões anteriores, você pode usar gráficos de combinação para a mesma finalidade.

Use colunas verticais para os valores de destino e uma linha com marcadores para os valores reais.

Clique na guia DESIGN na guia CHART TOOLS na faixa de opções.
Clique em Alterar tipo de gráfico no grupo Tipo. A caixa de diálogo Alterar tipo de gráfico é exibida.

Clique em Combo.
Altere o tipo de gráfico da série real para linha com marcadores. A visualização aparece em Combinação personalizada.
Clique OK.

Seu gráfico de combinação personalizado será exibido.

Como você observa no gráfico, os valores de destino estão em colunas e os valores reais são marcados ao longo da linha. A visualização dos dados ficou melhor, pois também mostra a tendência de seus resultados.

No entanto, esse tipo de representação não funciona bem quando os intervalos de dados de seus dois valores de dados variam significativamente.

Criando um gráfico de combinação com o eixo secundário

Suponha que você tenha os dados sobre o número de unidades de seu produto que foram enviadas e os lucros reais para o ano fiscal de 2015-2016 obtidos em diferentes regiões.

Se você usar o mesmo gráfico de combinação de antes, obterá o seguinte -

No gráfico, os dados de No. of Units não está visível porque os intervalos de dados variam significativamente.

Nesses casos, você pode criar um gráfico de combinação com o eixo secundário, de modo que o eixo primário exiba um intervalo e o eixo secundário exiba o outro.

Clique na guia INSERIR.
Clique em Combo no grupo Charts.
Clique em Criar gráfico de combinação personalizado na lista suspensa.

A caixa de diálogo Inserir gráfico é exibida com a combinação destacada.

Para o tipo de gráfico, escolha -

Linha com marcadores para o número da série de unidades
Coluna agrupada para os lucros reais da série
Marque a caixa Eixo secundário à direita do Nº de série de unidades e clique em OK.

Uma visualização do seu gráfico aparece em Combinação personalizada.

Seu gráfico de combinação aparece com o eixo secundário.

Você pode observar os valores para Lucros reais no eixo primário e os valores para Nº de unidades no eixo secundário.

Uma observação significativa no gráfico acima é para o trimestre 3, onde o número de unidades vendidas é maior, mas os lucros reais obtidos são menores. Provavelmente, isso poderia ser atribuído aos custos de promoção incorridos para aumentar as vendas. A situação melhorou no 4º trimestre, com uma ligeira diminuição das vendas e um aumento significativo dos Lucros Reais realizados.

Discriminando séries e eixos de categorias

Suponha que você queira projetar os lucros reais realizados nos anos de 2013-2016.

Crie uma coluna agrupada para esses dados.

Como você observa, a visualização de dados não é eficaz porque os anos não são exibidos. Você pode superar isso mudando de ano para categoria.

Remova o ano do cabeçalho do intervalo de dados.

Agora, o ano é considerado uma categoria e não uma série. Seu gráfico é o seguinte -

Elementos e estilos de gráfico

Os elementos do gráfico fornecem mais descrições aos seus gráficos, ajudando assim a visualizar seus dados de forma mais significativa.

Clique no gráfico

Três botões aparecem ao lado do canto superior direito do gráfico -

Elementos do gráfico
Estilos de gráfico
Filtros de gráfico

Para obter uma explicação detalhada sobre isso, consulte o tutorial de gráficos do Excel.

Clique em Elementos do gráfico.
Clique em Rótulos de dados.

Clique em Estilos de gráfico
Selecione um estilo e uma cor adequados aos seus dados.

Você pode usar o Trendline para exibir graficamente as tendências nos dados. Você pode estender uma linha de tendência em um gráfico além dos dados reais para prever valores futuros.

Rótulos de dados

Excel 2013 e versões posteriores fornecem várias opções para exibir rótulos de dados. Você pode escolher um rótulo de dados, formatá-lo como quiser e, em seguida, usar Clonar rótulo atual para copiar a formatação para o restante dos rótulos de dados no gráfico.

Os rótulos de dados em um gráfico podem ter efeitos, variando formas e tamanhos.

Também é possível exibir o conteúdo de uma célula como parte do rótulo de dados com o campo Inserir rótulo de dados.

Layout rápido

Você pode usar o Layout rápido para alterar o layout geral do gráfico rapidamente, escolhendo uma das opções de layout predefinidas.

Clique no gráfico.
Clique na guia DESIGN em CHART TOOLS.
Clique em Layout rápido.

Diferentes layouts possíveis serão exibidos. Conforme você avança nas opções de layout, o layout do gráfico muda para essa opção específica.

Selecione o layout que você gosta. O gráfico será exibido com o layout escolhido.

Usando imagens em gráficos de colunas

Você pode dar mais ênfase à sua apresentação de dados usando uma imagem no lugar de colunas.

Clique em uma coluna no gráfico de colunas.
Em Format Data Series, clique em Fill.
Selecione a imagem.
Em Inserir imagem de, forneça o nome do arquivo ou, opcionalmente, a área de transferência se você tiver copiado uma imagem anteriormente.

A imagem que você escolheu aparecerá no lugar das colunas no gráfico.

Band Chart

Você pode ter que apresentar os resultados da pesquisa de cliente de um produto de diferentes regiões. O Band Chart é adequado para esse propósito. Um gráfico de banda é um gráfico de linha com uma área sombreada adicionada para exibir os limites superior e inferior de grupos de dados.

Suponha que os resultados da sua pesquisa com clientes das regiões leste e oeste, mês a mês sejam -

Aqui, nos dados, <50% é Baixo, 50% - 80% é Médio e> 80% é Alto.

Com o Band Chart, você pode exibir os resultados da sua pesquisa da seguinte maneira -

Crie um gráfico de linha a partir de seus dados.

Altere o tipo de gráfico para -

Séries leste e oeste para alinhar com marcadores.
Séries Baixa, Média e Alta para Coluna Empilhada.

Seu gráfico é o seguinte.

Clique em uma das colunas.
Altere a largura da lacuna para 0% em Format Data Series.

Você obterá bandas em vez de colunas.

Para tornar o gráfico mais apresentável -

Adicione o título do gráfico.
Ajuste a faixa do eixo vertical.
Mude as cores das bandas para Verde-Amarelo-Vermelho.
Adicione rótulos às bandas.

O resultado final é o Band Chart com os limites definidos e os resultados da pesquisa representados nas bandas. Pode-se perceber no gráfico de maneira rápida e clara que enquanto os resultados da pesquisa para a região Oeste são satisfatórios, os da região Leste apresentam queda no último trimestre e precisam de atenção.

Gráfico de termômetro

Quando você tem que representar um valor alvo e um valor real, você pode facilmente criar um gráfico de termômetro no Excel que mostra enfaticamente esses valores.

Com o gráfico do termômetro, você pode exibir seus dados da seguinte forma -

Organize seus dados conforme mostrado abaixo -

Selecione os dados.
Crie um gráfico de colunas agrupadas.

Como você observa, a coluna do lado direito é o alvo.

Clique em uma coluna no gráfico.
Clique em Alternar linha / coluna na faixa de opções.

Clique com o botão direito na coluna de destino.
Clique em Formatar série de dados.
Clique em Eixo secundário.

Como você observa, o eixo primário e o eixo secundário têm faixas diferentes.

Clique com o botão direito no eixo primário.
Nas opções de Formatar Eixo, em Limites, digite 0 para Mínimo e 1 para Máximo.
Repita o mesmo para o eixo secundário.

O eixo primário e o eixo secundário serão definidos para 0% - 100%. A coluna de destino oculta a coluna real.

Clique com o botão direito na coluna visível (destino)
Em Format Data Series, selecione
- Sem preenchimento para FILL
- Linha sólida para BORDER
- Azul para Cor

Em Elementos do gráfico, desmarque
- Eixo → Primário horizontal
- Eixo → Vertical Secundário
- Gridlines
- Título do gráfico
No gráfico, clique com o botão direito em Eixo vertical primário
Nas opções de Formatar Eixo, clique em MARCAS DE MARCA
Para o tipo principal, selecione Dentro

Clique com o botão direito na área do gráfico.
Nas opções de Format Chart Area, selecione
- Sem preenchimento para FILL
- Sem linha para BORDER

Redimensione a área do gráfico para obter a forma de um termômetro.

Você obteve seu gráfico de termômetro, com o valor real em relação ao valor alvo sendo mostrado. Você pode tornar este gráfico de termômetro mais impressionante com alguma formatação.

Insira uma forma de retângulo sobrepondo a parte retangular azul no gráfico.
Nas opções de Formatar Forma, selecione -
- Preenchimento gradiente para FILL
- Linear para tipo
- 180 ⁰ para ângulo
Defina as paradas de gradiente em 0%, 50% e 100%.
Para que o gradiente pare em 0% e 100%, escolha a cor preta.
Para o Gradient stop em 50%, escolha a cor branca.

Insira uma forma oval na parte inferior.
Formate a forma com as mesmas opções.

O resultado é o gráfico do termômetro com o qual começamos.

Gráfico de Gantt

Um gráfico de Gantt é um gráfico no qual uma série de linhas horizontais mostra a quantidade de trabalho realizado em determinados períodos de tempo em relação à quantidade de trabalho planejada para esses períodos.

No Excel, você pode criar um gráfico de Gantt personalizando um tipo de gráfico de barras empilhadas para que ele represente tarefas, duração da tarefa e hierarquia. Um gráfico de Gantt do Excel normalmente usa dias como a unidade de tempo ao longo do eixo horizontal.

Considere os seguintes dados onde a coluna -

A tarefa representa as tarefas no projeto
Início representa o número de dias a partir da data de início do projeto
A duração representa a duração da tarefa

Observe que o início de qualquer tarefa é o início da tarefa anterior + duração. Este é o caso quando as Tarefas estão em hierarquia.

Selecione os dados.
Crie um gráfico de barras empilhadas.

Clique com o botão direito em Iniciar série.
Nas opções de Formatar série de dados, selecione Sem preenchimento.

Clique com o botão direito no eixo de categorias.
Nas opções de Formatar eixo, selecione Categorias na ordem inversa.

Em Elementos do gráfico, desmarque
- Legend
- Gridlines
Formate o eixo horizontal para
- Ajuste o alcance
- Marcas principais em intervalos de 5 dias
- Marcas menores em intervalos de 1 dia
Formate a série de dados para torná-la impressionante
Dê um título ao gráfico

Gráfico de Cachoeira

O Waterfall Chart é uma das ferramentas de visualização mais populares usadas em pequenas e grandes empresas. Os gráficos em cascata são ideais para mostrar como você chegou a um valor líquido, como receita líquida, dividindo o efeito cumulativo de contribuições positivas e negativas.

O Excel 2016 fornece o tipo de gráfico em cascata. Se você estiver usando versões anteriores do Excel, ainda poderá criar um gráfico em cascata usando o gráfico de colunas empilhadas.

As colunas são codificadas por cores para que você possa diferenciar rapidamente os números positivos dos negativos. As colunas de valor inicial e final começam no eixo horizontal, enquanto os valores intermediários são colunas flutuantes. Por causa dessa aparência, os gráficos em cascata também são chamados de gráficos de pontes.

Considere os seguintes dados.

Prepare os dados para o Waterfall Chart
Certifique-se de que a coluna Fluxo de caixa líquido esteja à esquerda da coluna Meses (isso porque você não incluirá esta coluna ao criar o gráfico)
Adicionar 2 colunas - aumentar e diminuir para fluxos de caixa positivos e negativos, respectivamente
Adicione uma coluna Início - a primeira coluna no gráfico com o valor inicial no Fluxo de Caixa Líquido
Adicione uma coluna Final - a última coluna no gráfico com o valor final no Fluxo de Caixa Líquido
Adicione uma coluna Float - que suporta as colunas intermediárias
Calcule os valores dessas colunas da seguinte forma

Na coluna Float, insira uma linha no início e no final. Coloque n valor arbitrário 50000. Isso apenas para ter algum espaço à esquerda e à direita do gráfico

Os dados serão os seguintes.

Selecione as células C2: H18 (coluna Excluir fluxo de caixa líquido)
Criar gráfico de colunas empilhadas

Clique com o botão direito na Série Float.
Clique em Formatar série de dados.
Nas opções de Formatar série de dados, selecione Sem preenchimento.

Clique com o botão direito em Negative Series.
Selecione a cor de preenchimento como vermelho.

Clique com o botão direito em Positive Series.
Selecione a cor de preenchimento como verde.

Clique com o botão direito em Iniciar série.
Selecione a cor de preenchimento como cinza.
Clique com o botão direito em End Series.
Selecione a cor de preenchimento como cinza.
Exclua a legenda.

Clique com o botão direito em qualquer série
Nas opções de Format Data Series, selecione Gap Width como 10% em Series Options

Dê o título do gráfico. O gráfico em cascata será exibido.

Minigráficos

Minigráficos são pequenos gráficos colocados em células individuais, cada um representando uma linha de dados em sua seleção. Eles fornecem uma maneira rápida de ver tendências.

Você pode adicionar Sparklines com ferramenta de análise rápida.

Selecione os dados para os quais deseja adicionar Minigráficos.
Mantenha uma coluna vazia à direita dos dados para os Minigráficos.

O botão Análise rápida

aparece na parte inferior direita dos dados selecionados.

Clique no

botão Quick Analysis . A barra de ferramentas de análise rápida é exibida com várias opções.

Clique SPARKLINES. As opções de gráfico exibidas são baseadas nos dados e podem variar.

Clique Line. Um gráfico de linha para cada linha é exibido na coluna à direita dos dados.

PivotCharts

Os gráficos dinâmicos são usados para resumir dados graficamente e explorar dados complicados.

Um gráfico dinâmico mostra séries de dados, categorias e eixos do gráfico da mesma forma que um gráfico padrão. Além disso, ele também fornece controles de filtragem interativos diretamente no gráfico para que você possa analisar rapidamente um subconjunto de seus dados.

Os gráficos dinâmicos são úteis quando você tem dados em uma tabela dinâmica enorme ou muitos dados de planilha complexos que incluem texto e números. Um gráfico dinâmico pode ajudá-lo a entender esses dados.

Você pode criar um gráfico dinâmico a partir de

Uma Tabela Dinâmica.
Uma tabela de dados autônoma, sem tabela dinâmica.

PivotChart da PivotTable

Para criar um gráfico dinâmico, siga as etapas abaixo -

Clique na Tabela Dinâmica.
Clique em ANALISAR em FERRAMENTAS PIVOTÁVEIS na faixa de opções.
Clique em PivotChart. A caixa de diálogo Inserir gráfico é exibida.

Selecione Coluna em Cluster na opção Coluna.

Clique OK. O gráfico dinâmico é exibido.

O PivotChart possui três filtros - Região, Vendedor e Mês.

Clique na opção Region Filter Control. A caixa de pesquisa aparece com a lista de todas as regiões. As caixas de seleção aparecem ao lado de Regiões.
Selecione as opções Leste e Sul.

Os dados filtrados aparecem no gráfico dinâmico e na tabela dinâmica.

Gráfico Dinâmico sem Tabela Dinâmica

Você pode criar um gráfico dinâmico autônomo sem criar uma tabela dinâmica.

Clique na Tabela de dados.
Clique na guia Inserir.
Clique em Gráfico Dinâmico no grupo Gráficos. A janela Criar gráfico dinâmico é exibida.
Selecione a tabela / intervalo.
Selecione o local onde deseja que o gráfico dinâmico seja colocado.

Você pode escolher uma célula na própria planilha existente ou em uma nova planilha. Clique OK.

Um gráfico dinâmico vazio e uma tabela dinâmica vazia aparecem junto com a Lista de campos do gráfico dinâmico para criar o gráfico dinâmico.

Escolha os campos a serem adicionados ao gráfico dinâmico
Organize os campos arrastando-os para FILTROS, LENDA (SÉRIE), EIXO (CATEGORIAS) e VALORES
Use os controles de filtro no gráfico dinâmico para selecionar os dados a serem colocados no gráfico dinâmico

O Excel criará automaticamente uma Tabela Dinâmica acoplada.