Gráfico de Controle – Impressionando o Chefe

Nessa aula nós vamos te mostrar como criar um gráfico de controle no Excel para que você possa impressionar o seu chefe na reunião da empresa!

Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo ou acesse o nosso canal do YouTube!

O que você aprende neste vídeo

Resposta rápida: A aula monta do zero um gráfico de controle de estoque no Excel, com linha suavizada e limites mínimo e máximo guardados em duas células. Duas colunas auxiliares, feitas com SE, marcam com bolinhas vermelhas os meses abaixo do mínimo e com verdes os acima do máximo, e mudar os limites muda o gráfico.

Neste vídeo (28 min):

  • 1:00 - O gráfico pronto: estoque de janeiro a dezembro de 2020 entre os limites de 4 mil e 8 mil
  • 1:36 - Mudança do limite mínimo e dos pontos vermelhos que acompanham
  • 3:17 - Os limites mínimo e máximo digitados em duas células (3 mil e 7 mil)
  • 5:08 - Colunas de mínimo e máximo, com a referência do limite travada com F4
  • 6:36 - Duas colunas auxiliares, uma para o limite mínimo e outra para o máximo
  • 8:35 - Fórmula com SE para o mínimo: o próprio estoque se ficar abaixo do limite, zero se não
  • 11:17 - A mesma lógica para o máximo, com a comparação invertida
  • 15:22 - Gráfico de linha com a opção de linha suavizada em Formatar Série de Dados
  • 16:45 - Ampliação da seleção do gráfico para incluir as colunas de mínimo e de máximo
  • 21:04 - Marcadores em bolinha, tamanho 12, na série auxiliar do mínimo
  • 23:07 - O zero da fórmula troca por #N/D para o ponto não aparecer
  • 25:38 - Gráfico pronto, com bolinhas vermelhas e verdes

Trechos do vídeo:

  • Um estoque abaixo do limite mínimo pode não bastar para atender os clientes, e acima do máximo pode acabar perdendo a validade. Por isso a faixa de controle fica entre os dois limites.
  • Ao arrastar uma fórmula para baixo, a referência da célula do limite precisa estar travada com $ (a tecla F4 coloca os dois). Sem a trava, a fórmula passa a pegar a linha seguinte e dá erro.
  • Mudar o valor de uma célula de limite muda a coluna de limite, que muda a coluna auxiliar, que muda o gráfico: tudo está ligado à célula de origem.
  • Na aula, o erro #N/D no lugar do zero na coluna auxiliar é o que faz o ponto não aparecer no gráfico.

Para receber a planilha que usamos na aula no seu e-mail, preencha:

Como Fazer um Gráfico de Controle no Excel

Você já deve ter passado por uma situação em que precisava criar um gráfico de controle para analisar o estoque dos seus produtos.

Hoje nós vamos te mostrar como criar um gráfico de controle para você impressionar seu chefe, colegas de trabalho ou quem for.

Vamos fazer um gráfico com os limites de estoque e vamos deixar ele com uma formatação automática para impressionar!

Bora para o próximo tópico porque a aula de hoje está excelente!

Ícone ExcelExcel Impressionador

Tudo que você precisa saber, do Básico ao Avançado, pra dominar a ferramenta mais importante do Mercado de Trabalho. Aprenda as principais ferramentas, funções e recursos do Excel para deixar qualquer planilha mais chamativa e eficiente.

Black Friday · Lote Especial · Lote 1 · Último Lote

De R$ 1.497 56% OFF54% OFF53% OFF

por 12x R$ 6467,5069

Preço do Lote Especial por tempo limitadoPreço do Lote Especial só até amanhã, 13/11, às 23h59Preço do Lote Especial até 13/11, às 23h59Último dia do Lote Especial: o preço vale até hoje, às 23h59Preço do Lote 1 por tempo limitadoPreço do Lote 1 só até amanhã, 20/11, às 23h59Preço do Lote 1 até 20/11, às 23h59Último dia do Lote 1: o preço vale até hoje, às 23h59Preço do Último Lote por tempo limitadoPreço do Último Lote só até amanhã, 27/11, às 23h59Preço do Último Lote até 27/11, às 23h59Último dia do Último Lote: o preço vale até hoje, às 23h59

Começar agoraQuero a oferta de Black FridaySeta para a direita
Fundo ExcelTelas Excel
Luz Excel

Como Fazer um Gráfico de Estoque no Excel

O nosso objetivo é construir o gráfico abaixo a partir de algumas informações.

Gráfico de Controle
Gráfico de Controle

Esse gráfico de controle vai permitir com que o usuário consiga inserir o limite inferior e superior para a quantidade em estoque, assim vai poder analisar essas quantias fora desses limites.

Então inicialmente teríamos apenas os dados das colunas A e B, mas para criar esse gráfico vamos precisar de mais 4 colunas auxiliares para que possamos fazer alguns cálculos para ter esse resultado.

Gráfico de Controle
Tabela de dados

Vamos utilizar as seguintes fórmulas para as colunas:

  1. =$B$15
  2. =$B$16
  3. =SE(B2>C2;NÃO.DISP();B2)
  4. =SE(B2>D2;B2;NÃO.DISP())

As duas primeiras colunas auxiliares são para que o nosso gráfico fique com uma linha constante no gráfico para mostrar o limite mínimo e limite máximo.

As outras duas fórmulas são para comparar os valores de estoque atual com o valor mínimo e máximo para que no gráfico somente o que está abaixo do mínimo e acima do máximo sejam evidenciados.

Assim teremos uma formatação automática desses dados e deixará o gráfico mais visual e mais fácil de analisar.

Outro ponto importante nessas duas fórmulas é a função NÃO.DISP(), essa função permite com que retorne um “erro” na célula para evitar que essa informação seja representada no gráfico.

Isso é importante para evitar com que o gráfico tenha os marcadores no 0 por exemplo. E com a fórmula SE no Excel é que vamos verificar se temos o resultado ou esse erro.

Com essas colunas criadas, nós já podemos criar o nosso gráfico de linha no Excel para poder fazer nossas análises e alterações.

Após a criação do gráfico vamos fazer algumas formatações básicas para deixar o gráfico mais limpo.

Gráfico de linha
Gráfico de linha

Agora é o momento em que vamos formatar as linhas do gráfico, onde basta selecionar a linha desejada e clicar nela com o botão direito do mouse e selecionar a opção Formatar Série de Dados.

Opção para formatar os dados
Opção para formatar os dados

Com isso temos uma aba sendo aberta ao lado direito do programa para que possamos fazer algumas alterações.

Formatando a linha de estoque
Formatando a linha de estoque

Na parte de linha de preenchimento (balde) vamos alterar a cor da linha, a largura e vamos marcar a opção linha suavizada.

Depois podemos selecionar a linha de mínimo e máximo e formatá-las também.

Formatando o mínimo e máximo no gráfico
Formatando o mínimo e máximo no gráfico

Esse é o gráfico que temos até o momento. Agora na parte superior dessa aba, na seta logo ao lado do nome Opções de Série, vamos selecionar a série do valor mínimo do nosso estoque.

Trocando a seleção para continuar a formatação
Trocando a seleção para continuar a formatação

Logo no início vamos marcar a opção Sem Linha para não mostrar linha e vamos até a opção de Marcador.

Formatando os marcadores
Formatando os marcadores

Aqui vamos poder fazer as formatações dessa coluna, mas aqui vamos inserir um tipo de marcador e vamos editar para melhorar a visualização.

Gráfico de controle
Gráfico com os marcadores formatados

Nosso gráfico já está quase pronto, agora só falta colocarmos o rótulo de dados. Para isso basta selecionar a nossa linha de estoque (preta) e ir até a opção de + ao lado do gráfico e marcar a opção de rótulo de dados.

Gráfico de controle completo
Gráfico de controle completo

Aqui já fizemos a mudança dos limites inferior e superior para que você observe que a nossa marcação é automática, então somente o que está fora desse limite, para baixo ou para cima que será marcado para evidenciar.

Agora você tem um gráfico automático para fazer sua análise de estoque no Excel. É um gráfico que vai impressionar, pois além de mostrar o estoque, você tem os limites que são marcados à medida que um produto está abaixo ou acima dos limites.

Conclusão do Gráfico de Controle

Nessa aula conseguimos aprender a como construir um gráfico de controle para análise de estoque.

Esse gráfico não é simplesmente um gráfico de linha, mas um gráfico completo que mostra tanto um limite inferior quanto um limite superior de estoque.

E caso o estoque do produto esteja fora desses limites ele ficará realçado para visualizar de forma mais fácil que há um problema.

Agora é só praticar o que aprendeu e impressionar o seu chefe com esse super gráfico de controle!

Hashtag Treinamentos

Para acessar outras publicações de Excel Intermediário, clique aqui!


Quer aprender tudo de Excel para se tornar o destaque de qualquer empresa?