Você conseguiu encontrar o tipo de gráfico de colunas empilhadas em cluster do Excel?
Bem, não está lá, infelizmente. Mas o Excel é uma ferramenta poderosa e podemos usar outras opções para criar o gráfico que estamos procurando com esta dica simples para fazer seu próprio gráfico de colunas empilhadas em cluster do Excel. Este tutorial é focado no Microsoft Excel 2013, Excel 2016 e Excel 2019. Mas provavelmente funcionará para as versões posteriores a 2019. Se você quiser ver as etapas necessárias para o Excel 20010 e Excel 2007, confira este tutorial: como fazer facilmente-criar-um-gráfico-de-colunas-agrupadas-empilhado-no-excel
O Breakdown
Aqui estão as etapas básicas que iremos percorrer neste tutorial. O arquivo de download de amostra e a demonstração em vídeo estão no final do artigo.
1) Dados de configuração
2) Criar gráfico de colunas empilhadas
3) Trocar linhas / colunas do gráfico (se necessário)
4) Diminuir a largura da lacuna da série (conforme desejado)
Etapas opcionais para os recursos estendidos do gráfico:
5) Adicionar série de preenchimento para separação
5a) Inserir linhas conforme necessário para o gráfico criado anteriormente
6) Adicionar Série de etiquetas de 2º eixo vertical
6a) Adicionar série
6b) Repita as etapas 2-3
6c) Mova a nova série de etiquetas de 2º eixo vertical para o 2º eixo
6d) Alterar o novo preenchimento vertical da série de rótulos do segundo eixo para não preencher
6e) Diminuir a lacuna da série Largura (conforme desejado)
6f) Excluir a entrada da legenda do rótulo do segundo eixo
Passo a passo
1) Dados de configuração
Para criar um tipo de Gráfico de colunas empilhadas em cluster simulado, precisamos apenas realizar 4 etapas. Agora, esta dica usa a opção de gráfico “Rótulos de categoria multinível” do Excel para gráficos de colunas. Para usar essa opção, precisamos configurar nossos dados de uma determinada maneira. Esse é nosso primeiro passo.
Nosso cenário é que estamos construindo um gráfico para a equipe de marketing sobre o valor gasto e o valor orçado para 2 produtos em 4 tipos de mídia (impressa, Internet, TV, rádio). Os dados que recebemos estavam neste formato.
| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Orçamento de vendas vs. real | ||||||||
| 2 | Rádio | Rádio | Imprimir | Imprimir | TV | TV | Internet | Internet | |
| 3 | Orçamento | Real | Orçamento | Real | Orçamento | Real | Orçamento | Real | |
| 4 | Produto 1 | 123 | 166 | 128 | 91 | 163 | 181 | 171 | |
| 5 | Produto 2 | 63 | 149 | 157 | 101 | 53 | 140 | 198 | 153 |
Dados originais
Para utilizar a opção ”Rótulos de categoria multinível” em um gráfico de colunas, precisamos configurar nossos dados com categorias em camadas. Isso significa que temos uma categoria principal em uma coluna e muitas categorias na coluna à direita. Portanto, precisamos alterar nossos dados para o seguinte formato.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Orçamento de vendas vs. real | |||||
| 2 | Rádio | Impressão | TV | Internet | ||
| 3 | Produto 1 | Orçamento | 123 | 155 | 91 | 181 |
| 4 | Real | 166 | 128 | 163 | 171 | |
| 5 | Produto 2 | Orçamento | 63 | 157 | 53 | 198 |
| 6 | Real | 149 | 101 | 140 | 153 |
Configuração básica de gráficos empilhados agrupados
Assim, você pode ver que nossos dados foi reorganizado para que O produto 1 possui 2 linhas associadas a ele. Um para os valores do orçamento e outro para os valores reais de todos os 4 tipos de publicidade na mídia. Agora estamos prontos para criar o gráfico de colunas empilhadas em cluster do Excel.
2) Criar gráfico de colunas empilhadas
Para criar nosso gráfico, destaque o intervalo de dados no conjunto de dados acima de A2: F6. Em seguida, vá para Inserir Faixa de Opções e clique no Gráfico de Colunas Empilhadas no botão do gráfico, como você vê aqui.
Seu gráfico agora deve ter a seguinte aparência:
3 ) Alterne linhas / colunas do gráfico (se necessário)
Nosso gráfico não parece muito certo. Na maioria dos casos, você precisa alternar linhas / colunas em seu gráfico. Por que preciso fazer isso? Confira este vídeo aqui
Para corrigir seu gráfico, basta clicar no gráfico e ir para a Faixa de Design e clicar no botão Alternar Linhas / Colunas.
Seu gráfico agora está completo e deve ter a seguinte aparência:
Se você quiser mover as colunas para mais perto, siga o próximo passo, mas é opcional.
4) Diminua a largura da lacuna da série (conforme desejado)
Se desejar, e é meu visual preferido, você pode fechar a largura da lacuna das colunas. Para fazer isso, selecione o gráfico e, em seguida, selecione qualquer série de dados e pressione CTRL + F1 (ou clique com o botão direito e selecione Formatar série de dados). Em seguida, na caixa de diálogo Formatar série de dados, altere a largura da lacuna para 15% ou o que achar melhor.
Seu gráfico final agora deve se parecer com o que você vê acima. Mas não terminamos se você quiser adicionar algumas alterações adicionais. Abaixo é totalmente opcional e conforme você deseja, mas acho que adiciona alguma clareza e detalhes adicionais que ajudarão o leitor.
Etapas opcionais do gráfico de colunas empilhadas em cluster
5) Adicionar série de preenchimento para separação
Se você deseja separar os agrupamentos, siga esta etapa.
5a) Insira as linhas conforme necessário para o gráfico criado anteriormente
Faça isso como achar necessário , simplesmente insira uma linha em seus dados e ela separará os grupos.
Depois de inserir sua linha, você deve seguir as etapas 1-3 acima para criar seu gráfico desde o início.Ao concluir as etapas de 1 a 3, você verá que seu gráfico não parece muito certo, pois seu grupo de produto 1 inclui este novo espaço, como você pode ver aqui:
Para corrigir isso, você precisa enganar o Excel criando outro agrupamento de nível um que não aparece no gráfico. Isso é muito simples, bastando colocar um espaço no conjunto de dados na célula logo acima da entrada do Produto 2.
Seu gráfico final com a lacuna adicionada deve tem esta aparência com o agrupamento corrigido:
6) Adicionar série de etiqueta vertical do 2º eixo
A etapa 5 opcional acima parece boa para mim, mas os dados do produto 2 estão mais distantes dos números, então às vezes gosto de adicionar um séries de gráficos adicionais no eixo secundário para que os números do eixo vertical sejam exibidos mais próximos dessa série. Esta é uma etapa opcional, portanto, use-a apenas se também gostar da aparência.
6a) Adicionar série
Para fazer com que o valor do limite máximo do eixo secundário corresponda ao limite máximo do eixo primário, precisamos criar uma fórmula para nossa série de eixo secundário adicional. Para fazer isso, crie uma fórmula na primeira linha de dados com uma fórmula que calcule a coluna máxima empilhada de todas as colunas empilhadas que estarão no gráfico. Portanto, na célula G3, criamos uma fórmula MAX () com a soma de cada linha. Consulte a fórmula Máx. E Soma abaixo para obter mais detalhes.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Orçamento de vendas vs. real | ||||||
| 2 | Rádio | Imprimir | TV | Internet | Rótulos do 2º eixo | ||
| 3 | Produto 1 | Orçamento | 123 | 155 | 91 | 181 | 628 |
| 4 | Real | 166 | 128 | 163 | 171 | ||
| 5 | |||||||
| 6 | Produto 2 | Orçamento | 63 | 157 | 53 | 198 | |
| 7 | Real | 149 | 101 | 140 | 153 |
Gráfico opcional do 2º eixo
Fórmulas de planilha
|
6b) Repita as etapas 2-3
Agora que criou seu conjunto de dados, você simplesmente seguirá todas as etapas básicas originais 1 a 3 acima para criar seu Gráfico de colunas empilhadas agrupadas mas mude o intervalo do gráfico para A2: G6 ou A2: G7 se você adicionou uma linha extra para uma lacuna entre os agrupamentos. No meu caso, adicionei a linha de lacuna, então o intervalo do meu gráfico é A2: G7.Vá em frente e crie o gráfico inicial como você vê aqui:
A inicial do seu gráfico agora deve ter esta aparência se você tiver a linha de lacuna na série de dados após a etapa 2.
Depois de alternar linhas / colunas, seu gráfico ficará assim após a etapa 3:
6c) Mova a nova série de rótulos do segundo eixo vertical para o segundo eixo
Nosso gráfico é muito semelhante ao gráfico básico, mas há uma série extra que usaremos em nosso eixo secundário. Portanto, selecione o gráfico e, em seguida, selecione a série “Rótulos do 2º eixo” e pressione CTRL + F1 ou clique com o botão direito sobre ele e selecione a opção Formatar série de dados … Isso abrirá a opção de movê-lo para o eixo secundário. Faça isso agora .
6d) Alterar novo preenchimento de série de rótulo vertical do segundo eixo para não preenchimento
Quando movemos a série para o eixo secundário, ela cobre uma de nossas colunas empilhadas. Portanto, para contornar isso, precisamos mudar o preenchimento da série para Sem preenchimento. Se você ainda estiver na caixa de diálogo Formatar série de dados, clique em Opções de preenchimento (parece uma lata de tinta derramando) e altere o preenchimento para Sem preenchimento.
6d) Diminua a largura da lacuna da série (como Desejado)
Este é o mesmo que a etapa opcional 4 acima. Mudei a largura da lacuna das colunas empilhadas no eixo primário para 15%, como você pode ver aqui:
6f) Excluir a entrada da legenda do rótulo do segundo eixo
A última coisa que precisamos fazer é excluir a entrada da legenda” Etiquetas do segundo eixo “na legenda. Para fazer isso, selecione o gráfico e, em seguida, selecione a legenda e, em seguida, selecione a legenda e, em seguida, selecione a entrada de legenda “Rótulos do 2º eixo” e pressione a tecla delete.
Seu gráfico final ficará assim:
Esta é uma ótima técnica para fazer um gráfico de combinação que é frequentemente necessário para conjuntos de dados complexos. Funcionará até mesmo para mais conjuntos de dados de produtos. Aqui está um exemplo de como seria uma outra série.
Demonstração em vídeo
Confira este tutorial em vídeo sobre as técnicas apresentado acima.
Download do arquivo de amostra
Clique aqui para fazer o download do arquivo de modelo do Excel grátis:
Clustered-Stacked -Column-Charts
