Criar gráfico com linha de média / meta no Excel

No Excel, pode ser uma tarefa comum inserir uma linha horizontal ou vertical em um gráfico; a linha horizontal ou vertical pode se referir a algum valor de meta ou média. Esse tipo de linha facilita a comparação entre os valores reais e o valor de meta ou média, e com a ajuda dessa linha, você pode facilmente ver onde os valores estão acima ou abaixo desse valor de referência, como mostrado nas capturas de tela abaixo. Neste artigo, vou falar sobre como criar um gráfico de colunas ou barras com uma linha de média ou meta no Excel.

Gráfico de colunas com linha horizontal	Gráfico de barras com linha vertical

• Criar gráfico de colunas com linha de média ou meta horizontalmente
• Adicionar uma linha de meta com valores diferentes em um gráfico de colunas
• Criar gráfico de colunas com linha de média dinamicamente usando botões de opção
• Criar gráfico de barras com linha de média ou meta verticalmente
• Adicionar uma linha de meta com valores diferentes em um gráfico de barras
• Adicionar uma linha de meta / média a um gráfico de colunas ou barras com um recurso prático
• Baixar arquivo de exemplo de Gráfico com Linha de Média / Meta

Criar gráfico de colunas com linha de média ou meta horizontalmente

Para criar um gráfico de colunas com uma linha de média ou meta horizontalmente, siga as etapas abaixo:

1. Se você deseja criar um gráfico de colunas com uma linha de média, primeiro deve calcular o valor médio da lista de dados. Insira a fórmula abaixo em uma célula em branco e, em seguida, arraste a alça de preenchimento automático para copiar a fórmula para outras células, veja a captura de tela:

2. Em seguida, selecione o intervalo de dados, incluindo os valores médios, e clique em Inserir > Inserir Gráfico de Colunas ou Barras > Colunas Agrupadas, veja a captura de tela:

3. Um gráfico de colunas será inserido na planilha. Você pode excluir elementos desnecessários, como título do gráfico, linhas de grade, conforme necessário, veja as capturas de tela:

4. Depois, clique com o botão direito na série de dados médios (barras laranjas) e escolha Alterar Tipo de Gráfico da Série no menu de contexto, veja a captura de tela:

5. Na caixa de diálogo Alterar Tipo de Gráfico, na lista Escolha o tipo de gráfico e o eixo para sua série de dados, selecione Gráfico de Linhas no menu suspenso Tipo de Gráfico para a série de dados Média, veja a captura de tela:

6. Em seguida, clique no botão OK e você obterá o gráfico como mostrado na captura de tela abaixo:

7. A seguir, você pode adicionar o rótulo de dados para esta linha média. Clique duas vezes para selecionar o ponto de dados mais à direita da linha, veja a captura de tela:

8. Depois, clique em Elementos do Gráfico, marque a caixa Rótulos de Dados e o rótulo de dados será inserido para a linha média, veja a captura de tela:

Adicionar uma linha de meta com valores diferentes em um gráfico de colunas

Às vezes, você pode querer comparar os valores reais com os valores de meta que são diferentes para cada linha. Nesse caso, para visualizar os valores de meta mais claramente, o tipo de gráfico abaixo pode ajudá-lo.

Para criar esse tipo de gráfico, faça o seguinte:

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

2. Depois, clique com o botão direito na série de dados de meta (barras laranjas) e escolha Alterar Tipo de Gráfico da Série no menu de contexto. Na caixa de diálogo Alterar Tipo de Gráfico, escolha Gráfico de Linhas para a série de dados de meta no menu suspenso Tipo de Gráfico, veja a captura de tela:

3. Em seguida, clique no botão OK e depois clique duas vezes na linha de meta e escolha Formatar Série de Dados, veja a captura de tela:

4. No painel Formatar Série de Dados aberto, na guia Preenchimento e Linha, selecione Sem linha na seção Linha, veja a captura de tela:

5. Ainda na guia Preenchimento e Linha, mude para a seção Marcador, selecione Incorporado na seção Opções de Marcador e, em seguida, selecione a barra horizontal no menu suspenso Tipo. Por fim, defina um tamanho correspondente à largura das suas barras na caixa Tamanho, veja a captura de tela:

6. Depois, defina uma cor para as linhas de meta como desejar na seção Preenchimento, veja a captura de tela:

7. Por fim, você pode excluir os elementos indesejados, como título do gráfico, linhas de grade, conforme necessário.

Criar gráfico de colunas com linha de média dinamicamente usando botões de opção

Se você tem uma grande tabela com as vendas de produtos em vários anos e agora deseja mostrar a linha de média para cada ano em um gráfico, pode usar um gráfico com linha de média clicando no botão de opção, como mostrado na demonstração abaixo. Quando você seleciona o ano correspondente, o gráfico com a linha de média será exibido dinamicamente.

Para criar este gráfico dinâmico, primeiro você deve inserir vários botões de opção e, em seguida, criar uma tabela auxiliar de dados na qual o gráfico será baseado. Por fim, insira um gráfico vinculado ao botão de opção. Siga as etapas abaixo:

Primeiro, insira vários botões de opção

1. Clique em Desenvolvedor > Inserir > Botão de Opção (Controle de Formulário) e, em seguida, desenhe quatro botões de opção e edite o texto conforme necessário, veja as capturas de tela:

2. Depois, clique com o botão direito no primeiro botão de opção e escolha Formatar Controle no menu de contexto, veja a captura de tela:

3. Na caixa de diálogo Formatar Objeto aberta, na guia Controle, selecione uma célula para vincular o botão de opção na caixa de texto Vincular célula e clique em OK para fechar, veja a captura de tela:

Segundo, insira uma tabela auxiliar de dados para criar o gráfico baseado nela

4. Copie os nomes dos produtos da tabela original para um novo intervalo e, em seguida, aplique a fórmula abaixo em uma célula em branco ao lado dos seus novos dados copiados e arraste a alça de preenchimento para obter o resultado, veja a captura de tela:

5. Agora, quando você clica em um botão de opção, o ano correspondente dos dados será exibido automaticamente, como mostrado na demonstração abaixo:

6. E então, você pode calcular os valores médios usando a fórmula abaixo, veja a captura de tela:

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

Depois, selecione os novos dados criados e aplique as etapas 2 a 8 do método 1 para criar um gráfico de colunas com uma linha de média, e você obterá um gráfico com uma linha de média dinamicamente. Veja a demonstração abaixo:

Criar gráfico de barras com linha de média ou meta verticalmente

Esta seção introduzirá como criar um gráfico de barras com média ou meta verticalmente, faça o seguinte:

1. Primeiro, crie uma tabela auxiliar de dados, insira 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 tela:

2. Depois, insira os números 0 e 1 nas células E2 e E3 para os valores Y, veja a captura de tela:

3. Depois, selecione o intervalo de dados original e clique em Inserir > Inserir Gráfico de Colunas ou Barras > Barras Agrupadas, veja a captura de tela:

4. Um gráfico de barras será inserido na planilha, depois, clique com o botão direito na barra e escolha Selecionar Dados no menu de contexto, veja a captura de tela:

5. Na caixa de diálogo Selecionar Fonte de Dados que apareceu, clique no botão Adicionar, veja a captura de tela:

6. Depois, na caixa de diálogo Editar Série, insira um nome de série na caixa de texto Nome da série e, em seguida, selecione as células com os valores X (D2:D3 neste exemplo) na caixa de texto Valores da série, veja a captura de tela:

7. Clique em OK > OK para fechar as caixas de diálogo e a nova série de dados agora foi adicionada ao seu gráfico de barras (duas barras laranjas), e depois clique com o botão direito na barra laranja e escolha Alterar Tipo de Gráfico da Série, veja a captura de tela:

8. Na caixa de diálogo Alterar Tipo de Gráfico que apareceu, selecione Dispersão com Linhas Suaves no menu suspenso Tipo de Gráfico para a série de dados Média, veja a captura de tela:

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

10. Na caixa de diálogo Selecionar Fonte de Dados, na caixa de listagem Entradas da Legenda (Séries), clique na opção Média e depois clique no botão Editar, veja a captura de tela:

11. Depois, uma caixa de prompt Editar Série aparecerá. Na caixa Valores X da série, selecione as duas células X (D2:D3) e, na caixa Valores Y da série, selecione duas células Y (E2:E3), veja a captura de tela:

12. Clique em OK > OK para fechar as caixas de diálogo e você verá que uma linha média vertical foi adicionada ao gráfico, veja a captura de tela:

13. Depois, 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 tela:

14. No painel Formatar Eixo aberto, na guia Opções do Eixo, na seção Opções do Eixo, digite 1.0 na caixa Limite Máximo e você obterá o gráfico como mostrado na captura de tela abaixo:

15. Por fim, você pode excluir o eixo vertical secundário e as linhas de grade indesejadas conforme necessário. Agora, um gráfico de barras com uma linha média vertical está concluído. Veja a captura de tela:

Adicionar uma linha de meta com valores diferentes em um gráfico de barras

Para adicionar a linha de meta com valores diferentes em um gráfico de barras, como mostrado nas capturas de tela abaixo, siga as etapas abaixo passo a passo:

1. Selecione o intervalo de dados e clique em Inserir > Inserir Gráfico de Colunas ou Barras > Barras Agrupadas para inserir um gráfico de barras, veja a captura de tela:

2. Depois, clique com o botão direito na série de dados de meta (barras laranjas) e escolha Formatar Série de Dados no menu de contexto, veja a captura de tela:

3. No painel Formatar Série de Dados aberto, na guia Opções da Série, altere a sobreposição da série para 100%, veja a captura de tela:

4. Ainda no painel Formatar Série de Dados, mude para a guia Preenchimento e Linha, selecione Sem preenchimento na seção Preenchimento, veja a captura de tela:

5. Em seguida, vá para a guia Efeitos, na seção Sombra, defina as seguintes operações:

• Especifique uma cor que você precisa no menu suspenso Cor;
• Defina o desfoque para 0 pt;
• Depois, ajuste o ângulo para 0°;
• Por fim, altere a distância para 2pt conforme necessário.

6. Agora, volte para a guia Opções da Série novamente, selecione Eixo Secundário na seção Opções da Série e altere a largura do intervalo para 90% conforme necessário. Veja a captura de tela:

7. Finalmente, exclua os elementos indesejados, como título do gráfico, eixo X superior, linhas de grade conforme necessário, e você obterá o gráfico de barras com diferentes linhas de meta, como mostrado na captura de tela abaixo:

Adicionar uma linha de meta / média a um gráfico de colunas ou barras com um recurso prático

Baixar arquivo de exemplo de Gráfico com Linha de Média / Meta