Note: The other languages of the website are Google-translated. Back to English

Crie um gráfico de colunas com variação percentual no Excel

No Excel, você pode criar um gráfico de coluna simples para visualizar as tendências de dados normalmente. Para fazer com que os dados pareçam mais intuitivos para exibir as variações entre os anos, você pode criar um gráfico de colunas com variação percentual entre cada coluna, conforme a imagem mostrada abaixo. Nesse tipo de gráfico, as setas para cima indicam a porcentagem aumentada no ano posterior ao ano anterior, enquanto as setas para baixo indicam a porcentagem diminuída.

Neste artigo, apresentaremos como criar um gráfico de colunas que exibe a variação percentual entre as colunas no Excel.


Crie um gráfico de colunas com variação percentual usando barras de erro

Usando as barras de erro para criar um gráfico de colunas com alteração percentual, você deve inserir algumas colunas auxiliares conforme os dados mostrados abaixo e, em seguida, criar o gráfico com base nos dados auxiliares. Faça o seguinte:

Primeiro, crie os dados das colunas auxiliares

1. Na célula C2 que ao lado dos dados originais, digite a seguinte fórmula e arraste a fórmula para a célula C10, veja a captura de tela:

=A2&REPT(" ",10)
Tips: Esta fórmula é usada para tornar o rótulo mais longo e move o texto para a esquerda. Caso contrário, o rótulo estará localizado sob o centro das barras de coluna visíveis e invisíveis.

2. Vá em frente, insira a fórmula abaixo na célula D2 e ​​arraste e copie a fórmula para a célula D10, veja a captura de tela:

=B2
Tips: Esta fórmula faz referência aos números do pedido na coluna B.

3. Em seguida, na célula E2, insira a seguinte fórmula e arraste a alça de preenchimento para a célula E9, veja a captura de tela:

=B3
Tips: Esta fórmula é usada para fazer referência aos pedidos do próximo ano. Ele será usado para as barras invisíveis, que são o topo ou a base das barras de erro.

4. Em seguida, insira a fórmula abaixo na célula F2 e arraste a alça de preenchimento para a célula F9, veja a captura de tela:

=B3-B2
Tips: Esta fórmula irá calcular a variação ou diferença entre o ano atual e o ano seguinte.

5. Continue inserindo a seguinte fórmula na célula G2 e arraste-a para a célula G9, veja a captura de tela:

=IF(F2>0,-F2,"")
Tips: Esta fórmula é usada para retornar o valor da variância se for positivo, e alguns números negativos serão exibidos, os quais são usados ​​para as barras de erro começarem no topo da barra invisível e descerem.

6. Em seguida, continue digitando a seguinte fórmula na célula H2 e copie esta fórmula para a célula H9, veja a captura de tela:

=IF(F2<0,F2,"")
Tips: Esta fórmula é usada para retornar o valor da variância se for negativo, e alguns números negativos serão exibidos, os quais são usados ​​para as barras de erro começarem na parte inferior da barra de erro e subirem.

7. Agora, insira a última coluna auxiliar, aplique a fórmula abaixo na célula I2, arraste-a para a célula I9 e formate os resultados decimais para Estilo percentual. veja a imagem:

=F2/B2
Tips: Esta fórmula calcula a porcentagem da diferença entre o ano anterior e o ano seguinte. Ele será usado como rótulos na coluna invisível sob as barras de erro.

Em segundo lugar, crie o gráfico com base nos dados das colunas auxiliares

8. Depois de criar os dados auxiliares, selecione os dados na coluna C, coluna D e coluna E e, a seguir, clique em inserção > Inserir coluna ou gráfico de barras > Coluna Agrupada, veja a captura de tela:

9. E então, um gráfico de colunas foi inserido, você pode excluir os elementos desnecessários do gráfico, como título do gráfico, legenda ou linhas de grade, consulte as capturas de tela:

10. Em seguida, clique na barra da coluna que exibe os dados invisíveis e, em seguida, clique no Elementos do gráfico botão para expandir o Elementos do gráfico caixa de listagem e escolha Barras de erro > Mais opções, veja a captura de tela:

11. No aberto Barras de erro de formato painel, sob o Opções da barra de erro aba:

  • Selecionar Ambos de Direção seção;
  • Escolha Capa de Estilo Final;
  • Selecionar Personalizadas de Quantidade de ErroE clique em Especifique o valor, na sequência Barras de erro personalizadas caixa de diálogo, selecione os dados da célula G2: G10 no Valor de erro positivo e selecione células H2: H10 na caixa Valor de erro negativo caixa.

12. Então clique OK botão, e você obterá o gráfico conforme a captura de tela abaixo:

13. E agora, clique com o botão direito na coluna da barra que exibe os dados do pedido 1 e escolha Formato de série de dados no menu de contexto, veja a captura de tela:

14. No aberto Formato de série de dados painel, sob o Opções de série guia, altere os valores em Sobreposição de série e Largura da lacuna seções para 0%, veja a captura de tela:

15. Agora, você deve ocultar a barra de dados invisível, clique com o botão direito em qualquer uma delas, no menu de contexto aberto, escolha Sem preenchimento de Preencher seção, veja a captura de tela:

16. Com a barra de dados invisível ainda selecionada, clique Elementos do gráfico botão, escolha Etiquetas de dados > Mais opções, veja a captura de tela:

17. No Formatar rótulos de dados painel, sob o Opções de etiqueta guia, cheque Valor das células, e no estalou Faixa de rótulo de dados prompt, selecione o intervalo de dados de variância I2: I9, consulte as capturas de tela:

18. Então clique OK, ainda no Formatar rótulos de dados painel:

  • Desmarque o Valor e Mostrar linhas líderes opções sob o Opções de etiqueta;
  • Em seguida, especifique a posição do rótulo como Extremidade Externa de Posição da etiqueta.

19. Agora, você pode ver que os rótulos de dados foram adicionados ao gráfico, você pode definir os rótulos de porcentagem negativa para Fim Internoe formate os rótulos de dados de acordo com sua necessidade, consulte a captura de tela:


Crie um gráfico de colunas com variação percentual usando as setas para cima e para baixo

Às vezes, você pode querer usar algumas setas para substituir as barras de erro, se os dados aumentam no próximo ano, uma seta para cima é exibida, se os dados diminuem no próximo ano, uma seta para baixo é exibida. Ao mesmo tempo, os rótulos de dados e as setas serão alterados dinamicamente à medida que os dados mudam, conforme mostrado na demonstração abaixo.

Para criar este tipo de gráfico, você deve inserir dados auxiliares de duas partes, conforme a captura de tela mostrada abaixo. A primeira parte calcula a variação e a variação percentual como a parte azul exibida, a segunda parte é usada para barras de erro personalizadas para aumentar e diminuir como a parte vermelha exibida.

Primeiro, crie os dados das colunas auxiliares

1. Para inserir os dados auxiliares da primeira parte, aplique as fórmulas abaixo:

C2: =REPT(A2,1)        (drag the formula to cell C10)
D2
: = B2 (arraste a fórmula para a célula D10)
E2: = B3-B2 (arraste a fórmula para a célula E9)
F2: = E2 / B2 (arraste a fórmula para a célula F9)

2. Em seguida, aplique as seguintes fórmulas para criar os dados auxiliares da segunda parte:

G2: Enter 1.5, 2.5, 3.5, 4.5…plus one to the previous cell.          (fill the data to from G2 to G9)
H2
: = SE (B3> = B2, B3, NA ()) (arraste a fórmula para a célula H9)
I2: = SE (B3

Em segundo lugar, crie o gráfico com base nos dados das colunas auxiliares

3. Selecione os dados na coluna C e na coluna D e clique em inserção > Inserir coluna ou gráfico de barras > Coluna Agrupada para inserir um gráfico de colunas conforme a captura de tela abaixo:

4. Então aperte Ctrl + C para copiar os dados na coluna G, coluna H, coluna I e, em seguida, clique para selecionar o gráfico, veja a captura de tela:

5. Após selecionar o gráfico, por favor clique Início > macarrão > colar especial, Na colar especial caixa de diálogo, selecione Nova série, Opções de coluna, e então verifique Nome da série na primeira linha e Categorias (rótulos X) na primeira coluna opções, veja as capturas de tela:

6. E então, você obterá um gráfico conforme a captura de tela abaixo:

7. Clique com o botão direito em qualquer barra de coluna do gráfico e escolha Alterar tipo de gráfico de sériee no menu de contexto, veja a captura de tela:

8. No Alterar o tipo de gráfico caixa de diálogo, mude ambos Crescimento e Baixa para Gráfico de dispersão, então desmarque o Eixo Secundário caixa para cada um de Escolha o tipo de gráfico e eixo para sua série caixa de listagem. Veja a imagem:

9. E então, clique OK botão, você obterá um gráfico de combinação em que os marcadores estão posicionados entre as respectivas colunas. Veja a imagem:

10. Em seguida, clique para selecionar a série de aumento (o ponto laranja) e clique em Elementos do gráfico botão, verifique o Barras de erro na caixa de listagem e barras de erro foram adicionadas ao gráfico, consulte a captura de tela:

11. Selecione as barras de erro horizontais e pressione Apagar chave para excluí-los, veja a imagem:

12. E então, selecione as barras de erro verticais, clique com o botão direito e escolha Barras de erro de formato, Na Barras de erro de formato painel, sob o Opções da barra de erro guia, faça as seguintes operações:

  • Selecionar Ambos opção do Direção;
  • Selecionar Sem tampa de Estilo Final;
  • De Quantidade de Erro seção, selecione PersonalizadasE clique em Especifique o valor botão, no popped out Barras de erro personalizadas caixa de diálogo, no Valor de erro positivo caixa, entre = {0}, E no Valor de erro negativo caixa, selecione o valor da variância E2: E9.
  • Então clique OK botão.

13. Agora, ainda no Barras de erro de formato painel, clique em Preenchimento e linha guia, faça as seguintes operações:

  • Selecionar Linha sólida no Line seção e escolha a cor que você precisa, em seguida, especifique a largura da linha como você precisa;
  • De Tipo de seta inicial lista suspensa, escolha uma seta tipo.

14. Nesta etapa, você deve ocultar os marcadores (os pontos laranja), selecionar os pontos laranja e clicar com o botão direito, escolher Formato de série de dados a partir do menu de contexto, no aberto Formato de série de dados painel, sob o Preenchimento e linha guia, clique em Marcador seção e selecione nenhum de Opções de Marcador, veja a captura de tela:

15. Repita a etapa 10-14 acima para inserir a seta para baixo para diminuir a série de dados e ocultar os marcadores cinza, e você obterá o gráfico conforme a captura de tela abaixo:

16. Depois de inserir as setas, agora, você deve adicionar os rótulos de dados, por favor clique para selecionar a série de aumento oculta e clique em Elementos do gráfico > Etiquetas de dados > Acima, veja a captura de tela:

17. Em seguida, clique com o botão direito em qualquer rótulo de dados e escolha Formatar rótulos de dados a partir do menu de contexto, no Formatar rótulos de dados painel, sob o Opções de etiqueta guia, cheque Valor das células opção, então, no pop out Faixa de rótulo de dados caixa de diálogo, selecione as células de porcentagem de variância (F2: F9), veja a captura de tela:

18. Clique OK para fechar a caixa de diálogo, ainda no Formatar rótulos de dados painel, desmarque o Valor Y e Mostrar linhas líderes opções, veja a captura de tela:

19. Então, você só precisa repetir a etapa 16-18 acima para adicionar os rótulos de porcentagem negativa (desta vez, você deve adicionar rótulos abaixo dos pontos de diminuição de dados, escolha Abaixo no submenu de Etiquetas de dados in Elementos de gráficos), e o gráfico de colunas com alteração percentual foi criado com sucesso, consulte a captura de tela:


Crie um gráfico de colunas com variação percentual usando um recurso poderoso

Para a maioria de nós, os métodos acima são muito difíceis de usar, mas, se você tiver Kutools for Excel, ele fornece vários tipos especiais de gráficos que o Excel não possui, como Gráfico de marcadores, Alvo e gráfico real, Gráfico de Desvio e assim por diante. Com sua ferramenta fácil Gráfico de colunas com porcentagem alterada, você pode criar um gráfico de colunas com alteração percentual usando as setas para cima e para baixo de forma rápida e fácil no Excel. Clique para baixar Kutools para Excel para avaliação gratuita!


Baixar arquivo de amostra de gráfico de coluna com alteração percentual


As melhores ferramentas de produtividade para escritório

Kutools for Excel - ajuda você a se destacar da multidão

Você gostaria de completar seu trabalho diário de forma rápida e perfeita? O Kutools for Excel traz 300 recursos avançados poderosos (combinar pastas de trabalho, somar por cor, dividir o conteúdo da célula, converter data e assim por diante ...) e economizar 80% do tempo para você.

  • Projetado para 1500 cenários de trabalho, ajuda você a resolver 80% dos problemas do Excel.
  • Reduza milhares de cliques de teclado e mouse todos os dias, alivie seus olhos e mãos cansados.
  • Torne-se um especialista em Excel em 3 minutos. Não é mais necessário se lembrar de fórmulas dolorosas e códigos VBA.
  • Avaliação gratuita ilimitada de 30 dias. Garantia de devolução do dinheiro em 60 dias. Atualização e suporte gratuitos por 2 anos.
Faixa de opções do Excel (com Kutools para Excel instalado)

Guia Office - Habilitar leitura e edição com guias no Microsoft Office (incluindo Excel)

  • Um segundo para alternar entre dezenas de documentos abertos!
  • Reduza centenas de cliques do mouse para você todos os dias, diga adeus à mão do mouse.
  • Aumenta sua produtividade em 50% ao visualizar e editar vários documentos.
  • Traz guias eficientes para o Office (incluindo Excel), assim como o Chrome, Firefox e o novo Internet Explorer.
Captura de tela do Excel (com guia Office instalado)
Comentários (1)
Ainda não há classificações. Seja o primeiro a avaliar!
Este comentário foi feito pelo moderador no site
Adoro este tutorial - gostaria de saber se é possível criar um carrinho semelhante, mas agrupado por meses ao longo do ano, de modo que 3 barras próximas umas das outras para abril, maio, junho etc. Não consigo resolver isso!
Não há comentários postados aqui ainda
Deixe o seu comentário
Postando como convidado
×
Avalie esta postagem:
0   Personagens
Locais sugeridos

Siga-nos

Copyright © 2009 - www.extendoffice.com. | Todos os direitos reservados. Distribuído por ExtendOffice. | | | Mapa do site
Microsoft e o logotipo do Office são marcas comerciais ou marcas registradas da Microsoft Corporation nos Estados Unidos e / ou em outros países.
Protegido por Sectigo SSL