282
Eis uma situação muito comum. Temos abaixo uma tabela no Excel simples de número de itens vendidos x vendedor, à esquerda e, à direita, temos uma tabela a qual preencheremos com os nomes dos vendedores com as maiores e menores vendas em cada mês. Não há, até então, fórmulas na planilha.
Vejam nosso modelo abaixo:
Para a resolução desse exercício, utilizaremos quatro funções distintas:
- Função MÍNIMO
- Função MÁXIMO
- Função CORRESP
- Função ÍNDICE
EFETUAR TODOS OS PROCEDIMENTOS PARA UM ÚNICO MÊS
Começaremos nosso exercício identificando a menor venda do mês de janeiro. Para isso utilizaremos a função mínimo. A função mínimo retorna o MENOR valor de um intervalo fornecido. Portanto, olhando nosso modelo podemos ver que a menor venda foi a de PEDRO, com 43 unidades e a maior foi de JOSÉ, 677 unidades.
IDENTIFICANDO AS MAIORES E MENORES VENDAS
Façamos agora as duas fórmulas que irão retornar esses dois valores no Excel.
MENOR VENDA DE JANEIRO – Digitem a fórmula “=MÍNIMO(B3:F3)” na célula I3 e teclem ENTER
MAIOR VENDA DE JANEIRO – Digitem a fórmula “=MÁXIMO(B3:F3)” na célula I3 e teclem ENTER
Agora precisamos copiar as duas fórmulas referentes ao mês de janeiro para os demais meses. Cada um pode fazer essa cópia como souber, porém, eu gostaria de dar um “macetinho”, uma dica: Conforme citei em meu artigo “ALGUNS ATALHOS VALIOSÍSSIMOS EM EXCEL!!!“, a tecla de atalho para copiar “PARA BAIXO” é CTRL+D (DOWM).
Vejam como funciona:
- Selecionem o intervalo I3:J8 (fórmulas de janeiro + células que irão receber as cópias)
- Agora pressiome CTRL + D
Notem que as fórmulas foram devidamente copiadas para os meses seguintes e seus comportamentos estão relativos aos meses efetivos, e não ao mês onde foram criadas inicialmente.
IDENTIFICANDO OS NOMES DOS MAIORES E MENORES VENDEDORES
Para a identificação dos nomes dos vendedores utilizaremos as funções CORRESP, e ÍNDICE .
A função CORRESP retorna a posição correspondente de um item em uma lista. A função ÍNDICE retorna o valor de uma célula em uma matriz baseado em uma linha e uma coluna. Então, desenvolvendo a lógica da nossa fórmula, ela será assim:
– Primeiramente precisarei saber em qual coluna (da matriz de vendas, não da planilha) está a pior venda nbo mês. Para isso utilizaremos a CORRESP para nos dizer qual a posição correspondente, na tabela de vendas, ao valor identificado como o menor na célula I3. Olhando para a nossa tabela vemos claramente que oa menor venda de janeiro é 43 e ela aparece na coluna de número 5 de nossa tabela de vendas, o que significa que, em nosso modelo, no mês de janeiro, a coluna 5 é a que tem o nome de quem vendeu menos.
Escrevendo a primeira parte da fórmula, digitem em H3 (célula que irá, ao final, ter o nome do vendedor que menos produziu em janeiro), a seguinte fórmula: =CORRESP(H3;B3:F3;0).
– Agora que sabemos em qual coluna está nosso pior vendedor em janeiro, vamos alterar a fórmula para que busque o NOME desse vendedor.
Para isso siga os seguintes passos:
- Selecione os nomes dos vendedores e dê um nome a esse conjunto de células:
- Marque o intervalo B3:F3
- Clique na caixa de nomes (em amarelo na ilustração a seguir)
- Digite o nome VENDEDORES e dê ENTER
Agora, sabemos que a nossa fórmula atual retorna o número da coluna na matriz que contém o nosso melhor vendedor de jnaneiro. Utilizaremos essa informação como parte da função INDICE, que nos trará o nome.
Alterem a fórmula de H3 para =ÍNDICE(VENDEDORES;CORRESP(I3;B3:F3;0))
Notem que a fórmula anterior É PARTE da nova fórmula.
Agora, basta usar o mesmo procedimento para identificar os nomes do maior vendedor de janeiro e, em seguida copiar para os demais meses. O resultado será o demonstrado abaixo:
MARCANDO NA TABELA EXCEL OS PIORES DESEMPENHOS
Para a realização dessa operação, uitilizaremos o recurso de FORMATAÇÃO CONDICIONAL, que aplica formatos às células baseado em testes de valores. Faremos para o mês de janeiro e depois copiaremos para os demais.
- Selecionem o intervalo B3:F3 (vendas de janeiro)
- Agora acessem o menu INICIO -> FORMATAÇÃO CONDICIONAL -> REGRAS DE PRIMEIROS / ÚLTIMOS -> 10 ÚLTIMOS ÍTENS
A tela abaixo aparecerá exibindo alguns padrões definidos para a formatação e apresenta também, na última posição, a possibilidade de criar o nosso próprio modelo. Selecionem a primeira opção para este exercício.
Alterem o número de elementos de 10 para 1, uma vez que desejamos marcar O PIOR desempenho.
Notem que a primeira linha está pronta. Agora, para exercitar, faça para as demais linhas, ou, se preferir, copie a formataçao.
Abaixo temos o exercício concluído.
Se alguém se interessar, por favor, comente aqui mesmo no artigo. Se quiser uma cópia da planilha, clique aqui.
Estou sempre à disposição.
6 comentários
Gilberto bom dia, ótimo material, parabéns!!
Mas tenho uma duvida.. no caso desses exemplos foram selecionados os dados de “linha por linha”. Tem como por exemplo, saber o pior e melhor resultado do ano? com o respectivo mês e vendedor?
Tenho uma planilha que demonstra o faturamento dos últimos 10 anos da empresa. Pra localizar o maior e o menor valor é tranquilo, mas referencia-los com o mês e ano que não consigo.
grande abraço!
Bom dia Maycon e grato pelo feedback. Sim, tem jeito, mas usando uma técnica diferente. Você poderia, e se quiser, mandar para meu email (Gilberto.gomes@Gmail.com) uma amostra da sua planilha. Assim, poderei ver melhor seu case e propor uma direção.
Gilberto
EXCELENTE POST.
Boa tarde Elias e obrigado pelo Feedback,
à disposição para quaisquer informações e/ou sugestões.
Gilberto
TENHO UMA TABELA DE VARIAS MERCADORIAS E VARIOS FORNECEDORES…COM ISSO TEM VARIOS PRECOS…COMO FACO PARA SABER O MENOR PRECO DE CADA MERCADORIA…OU MELHOR,A SEQUENCIA DE PRECOS DO MENOR PARA O MAIOR.
EX: MERCADORIA FORNECEDOR PRECO
A 1 10,00
A 2 8,50
A 3 12,00
B 1 3,20
B 2 4,10
B 3 1,90
B 4 2,45
C 1 20,00
C 2 16,50
C 3 23,90
C 4 18,40
C 5 21,35
AGUARDO ORIENTACAO
OBRIGADA
Boa tarde!
Gostaria que o menor preço mudasse de cor automaticamente e não igual ou menor que entende?