set 18, 2013 Ξ 6 comentários
Eis uma situação muito comum. Temos abaixo uma tabela 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:
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.
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).
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.
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[oprio 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.
EXCEL: Como destacar valores menores e maiores em uma tabela? was last modified: Março 20th, 2015 by Gilberto Gomes
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.
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.
Boa tarde Elias e obrigado pelo Feedback,
à disposição para quaisquer informações e/ou sugestões.
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.