Criar gráfico com linha média/linha de referência no Excel

No Excel, inserir uma linha horizontal ou vertical num gráfico é uma tarefa comum — essa linha pode representar um valor de referência ou uma média. Esse tipo de linha facilita a comparação entre os valores atuais e o valor de referência ou média, permitindo identificar facilmente onde os dados estão acima ou abaixo desse valor, tal como ilustrado nas imagens seguintes. Neste artigo, explico como criar um gráfico de colunas ou de barras com uma linha média ou de referência no Excel.

Gráfico de Colunas com linha horizontal	Gráfico de Barras com Linha Vertical

• Criar Gráfico de Colunas com linha média ou de referência horizontalmente
• Adicionar uma linha de referência com Valor diferente num Gráfico de Colunas
• Criar Gráfico de Colunas com linha média dinâmica utilizando Botão de Rádio
• Criar Gráfico de Barras com linha média ou de referência verticalmente
• Adicionar uma linha de referência com Valor diferente num Gráfico de Barras
• Adicionar uma linha de referência/média a um gráfico de colunas ou Gráfico de Barras com uma funcionalidade prática
• Transferir ficheiro de exemplo do gráfico com linha média/linha de referência

Criar Gráfico de Colunas com linha média ou de referência horizontalmente

Para criar um Gráfico de Colunas com linha média ou de referência horizontalmente, siga os passos seguintes:

1. Para criar um gráfico de colunas com uma linha média, comece por calcular o valor médio da lista de dados. Introduza a fórmula seguinte numa célula vazia e, em seguida, arraste a alça de preenchimento automático para copiar a fórmula para as restantes células, conforme ilustrado na imagem:

2. Em seguida, selecione o Intervalo de Dados, incluindo os valores médios, e clique em Inserir > Inserir Coluna ou Gráfico de Barras > Colunas Agrupadas, conforme ilustrado na imagem:

3. Será inserido um gráfico de colunas na folha de cálculo. Pode remover os elementos desnecessários, como o título do gráfico e as linhas de grelha, conforme necessário, tal como ilustrado nas imagens:

4. Em seguida, clique com o botão direito do rato na série de dados da média (barras laranja) e escolha Alterar Tipo de Gráfico no menu de contexto, conforme ilustrado na imagem:

5. Na caixa de diálogo Alterar Tipo de Gráfico, sob a lista Escolher o Tipo de Gráfico e o eixo para a sua série de dados, selecione Gráfico de Linhas na lista pendente de Tipo de Gráfico para a série de dados Média, conforme ilustrado na imagem:

6. Em seguida, clique no botão OK para obter o gráfico apresentado na imagem seguinte:

7. Em seguida, pode adicionar a etiqueta de dados a esta linha média. Faça duplo clique para selecionar o ponto de dados mais à direita da linha, conforme ilustrado na imagem:

8. Em seguida, clique em Elementos do Gráfico, marque a caixa Rótulos de Dados e os rótulos serão inseridos na linha média, conforme ilustrado na imagem:

Adicionar uma linha de referência com Valor diferente num Gráfico de Colunas

Por vezes, poderá pretender comparar os Valor Atual com valores de referência diferentes para cada linha. Neste caso, para visualizar os valores de referência de forma mais clara, o tipo de gráfico seguinte poderá ser útil.

Para criar este tipo de gráfico, proceda da seguinte forma:

1. Primeiro, selecione o intervalo de dados e clique em Inserir > Inserir Coluna ou Gráfico de Barras > Colunas Agrupadas para inserir o gráfico.

2. Em seguida, clique com o botão direito do rato na série de dados de referência (barras laranja) e escolha Alterar Tipo de Gráfico no menu de contexto. Na caixa de diálogo Alterar Tipo de Gráfico, selecione Gráfico de Linhas para a série de dados de referência na lista pendente de Tipo de Gráfico, conforme ilustrado na imagem:

3. Em seguida, clique no botão OK e, de seguida, faça duplo clique na linha de referência e escolha Formatar Série de Dados, conforme ilustrado na imagem:

4. No painel Formatar Série de Dados aberto, no separador Preenchimento e Linha, selecione Sem linha na secção Linha, conforme ilustrado na imagem:

5. Ainda no separador Preenchimento e Linha, mude para a secção Marcador, selecione Integrado na secção Opções de Marcador, escolha depois a barra horizontal na lista pendente de Tipo e, por fim, defina um tamanho correspondente à largura das suas barras na caixa Tamanho, conforme ilustrado na imagem:

6. Em seguida, defina a cor das linhas de referência à sua escolha na secção Preenchimento, conforme ilustrado na imagem:

7. Por fim, pode remover os elementos indesejados, como o título do gráfico e as linhas de grelha, conforme necessário.

Criar Gráfico de Colunas com linha média dinâmica utilizando Botão de Rádio

Se tiver uma tabela extensa com as vendas do produto em vários anos e pretender mostrar a linha média de cada ano num gráfico, pode utilizar um gráfico com linha média clicando em Botão de Rádio, tal como demonstrado abaixo. Ao selecionar o ano correspondente, o gráfico com a linha média será apresentado dinamicamente.

Para criar este gráfico dinâmico, primeiro deverá inserir vários Botão de Rádio, criar depois uma tabela auxiliar na qual o gráfico se baseará e, por fim, inserir um gráfico ligado aos Botão de Rádio. Proceda da seguinte forma:

Primeiro, insira vários Botão de Rádio

1. Clique em Programador > Inserir > Botão de Opção (Controlo de Formulário) e desenhe quatro botões de rádio, editando o texto conforme necessário, tal como ilustrado nas imagens:

2. Em seguida, clique com o botão direito do rato no primeiro Botão de Rádio e escolha Formatar Controlo no menu de contexto, conforme ilustrado na imagem:

3. Na caixa de diálogo Formatar Objeto aberta, no separador Controlo, selecione uma célula para associar o Botão de Rádio na caixa de texto Célula e clique em OK para a fechar. Veja a captura de ecrã:

Em segundo lugar, insira uma tabela auxiliar de dados para criar o gráfico com base em

4. Copie os nomes dos produtos da tabela original para um novo intervalo e, de seguida, aplique a fórmula abaixo numa célula vazia junto aos dados copiados. Depois, arraste a alça de preenchimento para obter o resultado. Veja a captura de ecrã:

5. Agora, ao clicar num botão de rádio, o ano correspondente dos dados será apresentado automaticamente, tal como demonstrado abaixo:

6. Em seguida, calcule os valores médios utilizando a fórmula abaixo. Veja a captura de ecrã:

Terceiro, crie o gráfico com base na nova tabela auxiliar de dados

Depois, selecione os novos dados criados e aplique os passos 2 a 8 do método 1 para criar um gráfico de colunas com uma linha média — e obtenha um gráfico com uma linha média dinâmica! Veja a demonstração abaixo:

Criar Gráfico de Barras com linha média ou de objetivo verticalmente

Esta secção irá explicar como criar um Gráfico de Barras com média ou objetivo verticalmente. Siga estes passos:

1. Primeiro, crie uma tabela auxiliar de dados: introduza X e Y em duas células e, em seguida, calcule os valores médios nas células D2 e D3. Veja a captura de ecrã:

2. Em seguida, introduza os números 0 e 1 nas células E2 e E3 para o Valor Y. Veja a captura de ecrã:

3. Depois, selecione o intervalo de dados original e clique em Inserir > Inserir Coluna ou Gráfico de Barras > Barra Agrupada. Veja a captura de ecrã:

4. Foi inserido um gráfico de barras na folha. Clique com o botão direito na barra e escolha Selecionar Dados no menu de contexto. Veja a captura de ecrã:

5. Na caixa de diálogo Origem dos Dados Selecionar que surge, clique no botão Adicionar. Veja a captura de ecrã:

6. Em seguida, na caixa de diálogo Editar Série, introduza um nome para a série na caixa de texto Nome da Série e selecione as células com os valores X (D2:D3 neste exemplo) na caixa de texto Valores da Série. Veja a captura de ecrã:

7. Clique em OK > OK para fechar as caixas de diálogo. A nova série de dados foi agora adicionada ao seu Gráfico de Barras (duas barras laranja). Clique com o botão direito na barra laranja e escolha Alterar Tipo de Gráfico. Veja a captura de ecrã:

8. Na caixa de diálogo Alterar Tipo de Gráfico que surge, selecione Dispersão com Linhas Suaves na lista pendente Tipo de Gráfico para a série de dados Média. Veja a captura de ecrã:

9. Depois, clique em OK para fechar a caixa de diálogo. A nova série de dados foi convertida num ponto de dados ao longo do eixo Y primário. Clique com o botão direito na barra e escolha Selecionar Dados no menu de contexto. Veja a captura de ecrã:

10. Na caixa de diálogo Origem dos Selecionar Dados, na lista Intervalo de nomes da série, clique na opção Média e, em seguida, clique no botão Editar. Veja a captura de ecrã:

11. Surge então a caixa de diálogo Editar Série. Na caixa Valor da Série no Eixo X, selecione as duas células X (D2:D3) e, na caixa Valor da Série no Eixo Y, selecione as duas células Y (E2:E3). Veja a captura de ecrã:

12. Clique em OK > OK para fechar as caixas de diálogo. Agora já pode ver que foi adicionada uma linha média vertical ao gráfico. Veja a captura de ecrã:

13. Em seguida, clique com o botão direito no eixo vertical secundário (o eixo à direita) e escolha Formatar Eixo no menu de contexto. Veja a captura de ecrã:

14. No painel Formatar Eixo aberto, no separador Opções do Eixo, na secção Opções do Eixo, introduza 1,0 na caixa de limite Máximo. Obterá assim o gráfico apresentado na captura de ecrã seguinte:

15. Por fim, pode remover o eixo vertical secundário e as linhas de grelha desnecessárias, conforme necessário. Está agora concluído um gráfico de barras com uma linha média vertical. Veja a captura de ecrã:

Adicionar uma linha de objetivo com Valor diferente num Gráfico de Barras

Para adicionar a linha de objetivo com Valor diferente num Gráfico de Barras, tal como demonstrado nas capturas de ecrã seguintes, siga os passos abaixo:

1. Selecione o intervalo de dados e clique em Inserir > Inserir Coluna ou Gráfico de Barras > Barra Agrupada para inserir um gráfico de barras. Veja a captura de ecrã:

2. Em seguida, clique com o botão direito na série de dados do objetivo (barras laranja) e escolha Formatar Série de Dados no menu de contexto. Veja a captura de ecrã:

3. No painel Formatar Série de Dados aberto, no separador Opções da Série, altere a sobreposição da série para 100 %. Veja a captura de ecrã:

4. Ainda no painel Formatar Série de Dados, mude para o separador Preenchimento e Linha e selecione Sem preenchimento na secção Preenchimento. Veja a captura de ecrã:

5. Em seguida, aceda ao separador Efeitos, na secção Sombra, e efetue as seguintes operações:

• Especifique uma cor que necessite no menu Corpendente;
• Defina o desfocagem para 0 pt;
• Depois, ajuste o ângulo para 0°;
• Por fim, ajuste a distância para 2pt conforme necessário.

6. Agora, volte novamente ao separador Opções da Série e selecione Eixo Secundário na secção Opções da Série. Ajuste também a largura do espaçamento para 90 %, conforme necessário. Veja a captura de ecrã:

7. Por fim, remova os elementos indesejados — como o título do gráfico, o eixo X superior e as linhas de grelha — conforme necessário, e obterá um gráfico de barras com diferentes linhas-alvo, tal como mostrado na imagem abaixo:

Adicione uma linha de meta/média a um gráfico de colunas ou Gráfico de Barras com uma funcionalidade prática

Transferir ficheiro de exemplo do gráfico com linha média/linha de referência