Ir para o conteúdo principal

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

Autor: Xiao Yang Última modificação: 2024-11-06

No Excel, pode ser uma tarefa comum inserir uma linha horizontal ou vertical em um gráfico, a linha horizontal ou vertical pode fazer referência a algum alvo ou valor médio. Este tipo de linha torna mais fácil comparar os valores reais versus o valor alvo ou médio, e com a ajuda desta linha, você pode ver facilmente onde os valores estão acima e abaixo deste valor de referência, conforme as imagens abaixo mostradas. Neste artigo, vou falar sobre como criar a coluna ou gráfico de barras com média ou linha de destino no Excel.

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

Crie gráfico de colunas com média ou linha de destino horizontalmente

Para criar um gráfico de colunas com média ou linha de destino horizontalmente, siga estas etapas:

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

=AVERAGE($B$2:$B$7)
Tips: Se você deseja criar o gráfico com uma linha de destino, você só precisa inserir o valor de destino nas células.

etapas para criar um gráfico de colunas com média ou linha de destino horizontalmente

2. Em seguida, selecione o intervalo de dados incluindo os valores médios e clique em inserção > Inserir coluna ou gráfico de barras > Coluna Agrupada, veja a captura de tela:

etapas para criar um gráfico de colunas com média ou linha de destino horizontalmente

3. E um gráfico de colunas é inserido na planilha, você pode excluir os elementos desnecessários, como título do gráfico, linhas de grade conforme necessário, consulte as capturas de tela:

etapas para criar um gráfico de colunas com média ou linha de destino horizontalmente etapas para criar um gráfico de colunas com média ou linha de destino horizontalmente etapas para criar um gráfico de colunas com média ou linha de destino horizontalmente

4. Em seguida, clique com o botão direito na série de dados média (barras laranja) e escolha Alterar o tipo de gráfico de série no menu de contexto, veja a captura de tela:

etapas para criar um gráfico de colunas com média ou linha de destino horizontalmente

5. No Alterar o tipo de gráfico caixa de diálogo, sob o Escolha o tipo de gráfico e eixo para sua série de dados caixa de lista, selecione Gráfico de linha do Tipo de Gráfico lista suspensa para o Média série de dados, veja a captura de tela:

etapas para criar um gráfico de colunas com média ou linha de destino horizontalmente

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

etapas para criar um gráfico de colunas com média ou linha de destino horizontalmente

7. Em seguida, 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:

etapas para criar um gráfico de colunas com média ou linha de destino horizontalmente

8. E então, clique Elementos do gráfico, Verifica Etiquetas de dados caixa, e o rótulo de dados foi inserido para a linha média, consulte a captura de tela:

etapas para criar um gráfico de colunas com média ou linha de destino horizontalmente

Tips: Se você deseja alterar a aparência da linha horizontal, clique com o botão direito na linha e escolha o tipo de traço desejado, peso ou cor do Esboço desça conforme você precisar.

Adicione uma linha de destino com valores diferentes em um gráfico de colunas

Às vezes, você pode querer comparar os valores reais com os valores alvo que são diferentes para cada linha; neste caso, para visualizar os valores alvo de forma mais clara, o tipo de gráfico abaixo pode lhe fazer um favor.

etapas para adicionar uma linha de destino com valores diferentes em um gráfico de colunas etapas para criar um gráfico de colunas com média ou linha de destino horizontalmente etapas para adicionar uma linha de destino com valores diferentes em um gráfico de colunas

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

1. Primeiro, selecione o intervalo de dados e clique em inserção > Inserir coluna ou gráfico de barras > Coluna Agrupada para inserir o gráfico.

etapas para adicionar uma linha de destino com valores diferentes em um gráfico de colunas

2. Em seguida, clique com o botão direito na série de dados de destino (barras laranja) e escolha Alterar o tipo de gráfico de série a partir do menu de contexto, no Alterar o tipo de gráfico caixa de diálogo, escolha Gráfico de linha para a série de dados de destino do Tipo de Gráfico lista suspensa, veja a captura de tela:

etapas para adicionar uma linha de destino com valores diferentes em um gráfico de colunas

3. Então clique OK botão e, em seguida, clique duas vezes na linha de destino e escolha Formato de série de dados, veja a captura de tela:

etapas para adicionar uma linha de destino com valores diferentes em um gráfico de colunas

4. No aberto Formato de série de dados painel, sob o Preenchimento e linha guia, selecione Nenhuma linha do Line seção, veja a captura de tela:

etapas para adicionar uma linha de destino com valores diferentes em um gráfico de colunas

5. Ainda no Preenchimento e linha guia, mude para o Marcador seção, selecione Autenticador do Opções de Marcador seção e, em seguida, selecione a barra horizontal na Formato drop down, por fim, defina um tamanho correspondente à largura de suas barras no Tamanho caixa, veja a captura de tela:

etapas para adicionar uma linha de destino com valores diferentes em um gráfico de colunas

6. E então, defina uma cor para as linhas de destino como você gosta do Preencher seção, veja a captura de tela:

etapas para adicionar uma linha de destino com valores diferentes em um gráfico de colunas

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

etapas para adicionar uma linha de destino com valores diferentes em um gráfico de colunas


Crie um gráfico de colunas com linha média de forma dinâmica usando botões de opção

Se você tem uma grande tabela com as vendas do produto em vários anos, agora deseja mostrar a linha média de cada ano em um gráfico. Para lidar com esta tarefa, você pode usar um gráfico com linha média clicando no botão de rádio conforme a demonstração abaixo. Ao selecionar o ano correspondente, o gráfico com linha média será exibido de forma dinâmica.

Para criar este gráfico dinâmico, primeiro, você deve inserir vários botões de rádio e, em seguida, criar uma tabela de dados auxiliar na qual o gráfico se baseia, por último, insira um link de gráfico para o botão de rádio. Execute as seguintes etapas:

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

1. Clique Developer > inserção > Botão de opção (controle de formulário)e desenhe quatro botões de opção e edite o texto de acordo com sua necessidade, veja as capturas de tela:

etapas para criar um gráfico de colunas com linha média dinamicamente usando botões de opção etapas para criar um gráfico de colunas com linha média dinamicamente usando botões de opção etapas para criar um gráfico de colunas com linha média dinamicamente usando botões de opção

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

etapas para criar um gráfico de colunas com linha média dinamicamente usando botões de opção

3. No aberto Objeto de formato caixa de diálogo, sob o Controle guia, selecione uma célula para vincular o botão de opção do Célula caixa de texto do link e clique em OK para fechá-lo, veja a imagem:

etapas para criar um gráfico de colunas com linha média dinamicamente usando botões de opção

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

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 novos dados copiados e arraste a alça de preenchimento para obter o resultado, consulte a captura de tela:

=VLOOKUP(G2,$A$2:$E$8,$O$1+1,0)
Note: Na fórmula acima, G2 é a célula da lista de produtos que você colou, A2: E8 é o intervalo de dados da tabela de dados original, O1 é a célula vinculada do botão de opção. Esta fórmula é usada para retornar a coluna de dados com base no botão de opção de seleção.

etapas para criar um gráfico de colunas com linha média dinamicamente usando botões de opção

5. Agora, ao clicar em um botão de opção, o ano correspondente dos dados será exibido automaticamente conforme a demonstração abaixo:

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

=AVERAGE($H$3:$H$8)

etapas para criar um gráfico de colunas com linha média dinamicamente usando botões de opção

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

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


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

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

1. Primeiro, crie uma tabela de dados auxiliar, digite X e Y em duas células e, em seguida, calcule os valores médios nas células D2 e ​​D3, consulte a captura de tela:

=AVERAGE($B$2:$B$7)
Tips: Se você deseja criar o gráfico com uma linha de destino, você só precisa inserir o valor de destino nas células.

etapas para criar um gráfico de barras com média ou linha de destino verticalmente

2. Em seguida, digite o número 0 e 1 nas células E2 e E3 para os valores de Y, veja a captura de tela:

etapas para criar um gráfico de barras com média ou linha de destino verticalmente

3. Em seguida, selecione o intervalo de dados original e clique em inserção > Inserir coluna ou gráfico de barras > Barra Agrupada, veja a captura de tela:

etapas para criar um gráfico de barras com média ou linha de destino verticalmente

4. E um gráfico de barras foi inserido na planilha, então, clique com o botão direito na barra e escolha Selecione Dados no menu de contexto, veja a captura de tela:

etapas para criar um gráfico de barras com média ou linha de destino verticalmente

5. No estalou para fora Selecione a fonte de dados diálogo, clique no Adicionar botão, veja a captura de tela:

etapas para criar um gráfico de barras com média ou linha de destino verticalmente

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

etapas para criar um gráfico de barras com média ou linha de destino verticalmente

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

etapas para criar um gráfico de barras com média ou linha de destino verticalmente

8. No estalou para fora Alterar o tipo de gráfico caixa de diálogo, selecione Espalhe com linhas suaves do Tipo de Gráfico cair para baixo para o Média série de dados, veja a captura de tela:

etapas para criar um gráfico de barras com média ou linha de destino verticalmente

9. Então clique 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, a seguir, clique com o botão direito na barra e escolha Selecione Dados no menu de contexto, veja a captura de tela:

etapas para criar um gráfico de barras com média ou linha de destino verticalmente

10. No Selecione a fonte de dados caixa de diálogo, na caixa de listagem Legend Entries (Series), clique no Média opção e, em seguida, clique em Editar botão, veja a captura de tela:

etapas para criar um gráfico de barras com média ou linha de destino verticalmente

11. Então, um Editar Série caixa de prompt é exibida, no Valores da série X caixa, selecione as duas células X (D2: D3), e na Valores da série Y caixa, selecione duas células Y (E2: E3), veja a imagem:

etapas para criar um gráfico de barras com média ou linha de destino verticalmente

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

etapas para criar um gráfico de barras com média ou linha de destino verticalmente

13. Em seguida, clique com o botão direito do mouse no eixo vertical secundário (o eixo direito) e escolha Formato de eixo no menu de contexto, veja a captura de tela:

etapas para criar um gráfico de barras com média ou linha de destino verticalmente

14. No aberto Formato de eixo painel, sob o Opções de eixo guia, no Opções de eixo seção, tipo 1.0 no Máximo caixa encadernada e você obterá o gráfico conforme a captura de tela abaixo:

etapas para criar um gráfico de barras com média ou linha de destino verticalmente

15. Por fim, você pode excluir o eixo vertical secundário e as linhas de grade indesejadas conforme sua necessidade, agora um gráfico de barras com uma linha média vertical está finalizado. Veja a imagem:

etapas para criar um gráfico de barras com média ou linha de destino verticalmente

Tips: Se você deseja alterar a aparência da linha vertical, basta clicar com o botão direito na linha e, em seguida, escolher o tipo de traço, peso ou cor desejada no Esboço caia conforme você precisa

Adicione uma linha de destino com valores diferentes em um gráfico de barras

Para adicionar a linha de destino com diferentes valores em um gráfico de barras, conforme as capturas de tela mostradas abaixo, siga os seguintes passos:

etapas para adicionar uma linha de destino com valores diferentes em um gráfico de barras etapas para adicionar uma linha de destino com valores diferentes em um gráfico de barras etapas para adicionar uma linha de destino com valores diferentes em um gráfico de barras

1. Selecione o intervalo de dados e clique em inserção > Inserir coluna ou gráfico de barras > Barra Agrupada para inserir um gráfico de barras, consulte a captura de tela:

etapas para adicionar uma linha de destino com valores diferentes em um gráfico de barras

2. Em seguida, clique com o botão direito na série de dados de destino (barras laranja) e escolha Formato de série de dados no menu de contexto, veja a captura de tela:

etapas para adicionar uma linha de destino com valores diferentes em um gráfico de barras

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

etapas para adicionar uma linha de destino com valores diferentes em um gráfico de barras

4. Ainda no Formato de série de dados painel, mude para o Preenchimento e linha guia, selecione Sem preenchimento do Preencher seção, veja a captura de tela:

etapas para adicionar uma linha de destino com valores diferentes em um gráfico de barras

5. Em seguida, vá para o Efeito guia, sob o Shadow seção, defina as seguintes operações:

  • Especifique uma cor que você precisa do Cor suspenso;
  • Defina o desfoque para 0 pt;
  • Em seguida, ajuste o ângulo para;
  • Por fim, altere a distância para 2pt como você precisa.

etapas para adicionar uma linha de destino com valores diferentes em um gráfico de barras

6. Agora, mude para o Opções de série guia novamente, selecione Eixo Secundário do Opções de série seção e altere a largura da lacuna para 90% como você precisa. Veja a imagem:

etapas para adicionar uma linha de destino com valores diferentes em um gráfico de barras

7. Finalmente, exclua os elementos indesejados, como bloco do gráfico, eixo X superior, linhas de grade conforme necessário, você obterá o gráfico de barras com diferentes linhas de destino, como mostrado abaixo:

etapas para adicionar uma linha de destino com valores diferentes em um gráfico de barras


Adicione uma meta / linha média a uma coluna ou gráfico de barras com um recurso útil

Kutools for Excel fornece mais de 50 tipos especiais de gráficos que o Excel não possui, como Gráfico de marcadores, Alvo e gráfico real, Gráfico de setas de diferença e assim por diante. Com sua ferramenta útil de gráficos Adicionar linha ao gráfico, você pode adicionar uma meta ou linha média a uma coluna ou gráfico de barras com apenas um clique no Excel. Clique para baixar o Kutools for Excel para teste gratuito!


Baixar arquivo de amostra de gráfico com linha média / alvo

exemplo de criação de gráfico de colunas


As melhores ferramentas de produtividade para escritório

Kutools para Excel - ajuda você a se destacar na multidão

🤖 Assistente de IA do Kutools: Revolucionar a análise de dados com base em: Execução Inteligente   |  Gerar Código  |  Crie fórmulas personalizadas  |  Analise dados e gere gráficos  |  Invocar funções do Kutools...
Recursos mais comuns: Encontre, destaque ou identifique duplicatas  |  Excluir linhas em branco  |  Combine colunas ou células sem perder dados  |  Rodada sem Fórmula ...
Super VLookup: Vários critérios  |  Valor múltiplo  |  Em várias folhas  |  Pesquisa Difusa...
Av. Lista suspensa: Lista suspensa fácil  |  Lista suspensa de dependentes  |  Lista suspensa de seleção múltipla...
Gerenciador de colunas: Adicione um número específico de colunas  |  Mover colunas  |  Alternar status de visibilidade de colunas ocultas  Comparar colunas com Selecionar células iguais e diferentes ...
Recursos em destaque: Foco da Grade  |  Vista de Design  |  Grande Barra de Fórmula  |  Gerenciador de pastas de trabalho e planilhas | Biblioteca (Auto texto)  |  Data Picker  |  Combinar planilhas  |  Criptografar/Descriptografar Células  |  Enviar e-mails por lista  |  Super Filtro  |  Filtro Especial (filtro negrito/itálico/tachado...) ...
15 principais conjuntos de ferramentas12 Texto Ferramentas (Adicionar texto, Remover Personagens ...)  |  50+ de cores Tipos (Gráfico de Gantt ...)  |  Mais de 40 práticos Fórmulas (Calcule a idade com base no aniversário ...)  |  19 Inclusão Ferramentas (Insira o código QR, Inserir imagem do caminho ...)  |  12 Conversão Ferramentas (Números para Palavras, Conversão de moedas ...)  |  7 Unir e dividir Ferramentas (Combinar linhas avançadas, Dividir células do Excel ...)  |  ... e mais

Kutools para Excel possui mais de 300 recursos, Garantindo que o que você precisa está a apenas um clique de distância...


Guia Office - Habilite leitura e edição com guias no Microsoft Office (inclui 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 (inclui Excel), assim como Chrome, Edge e Firefox.