Existe um tipo de gráfico que é sempre solicitado, no entanto, o Excel não oferece esse tipo de gráfico. O que poderia ser?

O Excel não oferece um gráfico de Colunas Empilhadas em Cluster, nem um gráfico de Barras Empilhadas em Cluster. O Excel oferece um tipo de gráfico Clustered Column e um tipo de gráfico Stacked Column, mas não oferece a combinação desses dois gráficos. O mesmo vale para um gráfico de barras empilhadas agrupadas.

O que mostra o Gráfico de colunas empilhadas agrupadas?

Bem, um gráfico de colunas empilhadas agrupadas permitiria a você agrupar seus dados (ou agrupar os pontos de dados ), mas use-o em conjunto com um tipo de gráfico de colunas empilhadas.

Esta é a aparência de um Gráfico de colunas empilhadas agrupadas:

Esta é a aparência de um gráfico de barras empilhadas agrupadas:

Por que o Microsoft Excel não oferece esses tipos de gráficos? Não sei, mas são comumente solicitados pelos usuários. Neste tutorial, mostrarei como fazer um Gráfico de colunas empilhadas em cluster, mas a técnica é a mesma ao criar um Gráfico de barras empilhadas em cluster. A única diferença é que você escolherá um gráfico de barras empilhadas em vez de um gráfico de colunas empilhadas. Fora isso, todo o resto é exatamente o mesmo.

A divisão

1) Crie um intervalo de dados do gráfico

2) Crie um gráfico de colunas empilhadas ou barra empilhada Gráfico

. (Observação: este tutorial mostrará apenas o tipo de gráfico de colunas empilhadas, mas as técnicas são as mesmas)

3) Alternar linha / coluna

4) Alterar largura da lacuna da coluna

5) Altere o eixo vertical

6) Altere a série de preenchimento branco para uma cor branca

7) Remova o rótulo de preenchimento branco da legenda

Passo a passo

1) Crie um intervalo de dados do gráfico

Esta é a etapa crítica em fazer um gráfico de colunas agrupadas EASY Stacked. Existem outras maneiras de criar esse tipo de gráfico do Excel, mas elas não são fáceis e geralmente confundem as pessoas.

Minha solução envolve o uso de rótulos de categoria multinível como formato de eixo horizontal.

As categorias de vários níveis são a chave. Você pode verificar outros exemplos de gráficos que criei com rótulos de categoria multinível aqui:

Solução de estudo de caso – mamãe precisa de ajuda em gráficos / gráficos da feira de ciências

Este gráfico é vamos comparar os gastos com publicidade em dois produtos diferentes em quatro categorias, tanto para números orçados quanto para números reais.

Então, aqui está como queremos configurar nossos dados:

Como você pode ver, tenho 2 produtos. Depois, tenho uma linha separada para os dólares orçados para publicidade e para os gastos reais com publicidade. Existem 4 categorias de orçamento e despesas relacionadas com Rádio, Imprensa, Televisão e Internet. Você também verá uma série de dados do gráfico à direita, chamada White Fill. Isso é usado para criar um espaço em branco entre os clusters. Você pode verificar uma postagem com um truque semelhante aqui:

Como fazer um gráfico de painel horizontal do Wall Street Journal no Excel

Vou passar a maior parte desta postagem aqui, então vamos examinar a forma como configurei os dados.

A primeira linha do os dados contêm os itens que queremos que apareçam na legenda como as categorias de nossas colunas empilhadas.

As próximas linhas representam nossa primeira seção de nosso primeiro grupo de dados Stacked Clustered. Será para o Produto 1 e temos 2 linhas, 1 que representa os dólares de publicidade orçados e outra que representa os dólares de publicidade reais para todas as 4 categorias (rádio, mídia impressa, TV e internet.

A coisa mais importante a lembrar aqui é que criamos uma categoria multinível. Fizemos isso na coluna A. Observe que A3 tem o nome do produto, mas não há nada em A4. isso faz é criar um agrupamento ou categoria multinível para o gráfico do primeiro produto. Aqui está como ficará no gráfico final:

Observe como ele cria um agrupamento para o Produto 1 e tem subcategorias para o orçamento versus os números reais.

A próxima linha parece ser uma linha em branco. Está quase toda em branco, exceto em 2 células. Na célula G5, I coloquei o valor da linha de grade horizontal mais alta que desejo exibir no gráfico.

Além disso, na célula A5, coloquei um Espaço. Apenas um espaço simples com o seu teclado. Eu coloquei um espaço na célula A5 para que as categorias de vários níveis no Excel coloque uma linha do gráfico na parte inferior dos rótulos dos eixos horizontais.

Aqui está com um espaço e sem um espaço

Veja como o agrupamento para o Produto 1 é restrito em torno do orçamento e reais vs. à direita quando se estende através do espaço vazio? O espaço é importante para informar ao Excel onde termina a primeira categoria multinível.

Finalmente, a última área do intervalo de dados do gráfico é o nosso próximo agrupamento de despesas com publicidade para o Produto 2.

2) Criar gráfico de colunas empilhadas ou gráfico de barras empilhadas

Agora que você configurou seus dados, pode criar seu gráfico. Destaque o intervalo de A2: G7 e, em seguida, escolha o gráfico de Colunas Empilhadas no botão Coluna na faixa de opções Inserir.

Seu gráfico agora deve ter a seguinte aparência:

3) Alternar linha / coluna

Agora, nosso gráfico não está no formato certo que desejamos. Queríamos os rótulos de Produto e Orçamento x Real no eixo horizontal, não os grupos de publicidade. Podemos corrigir isso selecionando o gráfico e, em seguida, escolhendo o botão Alternar linha / coluna na faixa de opções Design.

Se você não sabe por que o Excel está fazendo isso, verifique esta postagem:

Por que o Excel alterna linhas / colunas em meu gráfico?

Depois de fazer isso, seu gráfico deve ter a seguinte aparência:

4) Alterar a largura da lacuna da coluna

Isso está muito próximo do que queremos. Agora vamos fazer os agrupamentos parecerem mais intimamente relacionados. Podemos fazer isso alterando a largura da lacuna na caixa de diálogo Format Series. Você pode acessar essas opções clicando com o botão direito em qualquer série de dados no gráfico e selecionando “Formatar série de dados” no menu pop-up.

Então altere a largura da lacuna em Opções de série para o tamanho desejado. Neste caso, vou mudar a largura da lacuna para 25%. Isso fará com que as colunas empilhadas agrupadas se movam para mais perto juntos para que os dados possam ser comparados mais facilmente. Esta é a aparência do seu gráfico agora:

5) Alterar o eixo vertical

Não gosto do eixo vertical padrão máximo que o Excel define para nós. Gostaria de alterá-lo para algo mais próximo dos meus dados. Clique com o botão direito do mouse no eixo vertical e escolha “Formatar eixo” no menu pop-up menu.

Em seguida, na caixa de diálogo Formatar eixo nas Opções de eixo, altere o mínimo para 0 e o máximo para 701 como você vê aqui:

Seu gráfico resultante deve agora tem esta aparência:

6) Mude a série de preenchimento de branco para uma cor de branco

Estamos quase terminando. Agora, adicionamos uma série adicional chamada White Fill que criará ainda mais separação entre os 2 gráficos de colunas empilhadas agrupadas diferentes. Então, para fazer isso, precisamos clicar com o botão direito na coluna Preenchimento branco e escolher “Formatar série de dados …” no menu pop-up.

Em seguida, na caixa de diálogo Formatar série de dados, vá para as opções de Preenchimento e altere a opção Preenchimento para Preenchimento Sólido e mude a Cor de Preenchimento para Branco.

Seu gráfico agora deve ter a seguinte aparência:

7) Remova o rótulo de preenchimento branco da legenda

Parece ótimo. Estamos quase terminando. Só uma última coisa a fazer. Precisamos remover a entrada da legenda “Preenchimento branco”. Podemos fazer isso de maneira muito rápida e fácil selecionando primeiro o gráfico, depois selecione a legenda e, por fim, selecionando a entrada de legenda “Preenchimento branco”. Agora deve ser semelhante a:

Depois de selecionar a entrada de legenda “Preenchimento de branco”, basta pressionar a tecla delete. Seu gráfico final ficará assim:

É assim que você pode criar um Gráfico de Colunas Empilhadas em Cluster muito simples e fácil. Verifique o link abaixo para ver este tutorial em ação em uma demonstração em vídeo.