7 de setembro de 2013 às 17:08
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 venderores com as maiores e menores vendas em cada mês. Não há, anté então, fórmulas na planilha.
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).
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.
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 G3 (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). Vejam abaixo como ela teve o comportamento exatamente como previmos:
- 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 B2:F2
- 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 G3 para =ÍNDICE(VENDEDORES;1;CORRESP(H3;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:
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, basta efetuar o download em www.excelline.net/files/artigo_0008.xlsx.
Estou sempre à disposição.
GILBERTO GOMES
ExcelLine - Office World
Setembro/2013
Setembro/2013
