Compartilhando soluções – 0001

Um dos nossos colegas do blog, o Diêgo Cordeiro me procurou com a seguinte dúvida:
“Como posso determinar o melhor valor de uma cotação e retornar para outra célula qual o fornecedor correspondente?”
Abaixo temos o modelo de planilha que ele estava utilizando.
0001_Compartilhando_01
A primeira parte, determinar o melhor valor, no caso, o menor valor é aplicação direta da função MÍNIMO. Desta forma colocamos em C7 a fórmula: =MÍNIMO(F7:M7).
0001_Compartilhando_02
Comentário: A função MÍNIMO admite intervalos contínuos, desta forma ao invés de =MÍNIMO(F7;G7;H7;I7;J7;L7;M7) podemos utilizar a versão citada acima, mais simples e menos suscetível a erros.
Colocaremos agora os valores da linha 7 para verificarmos a consistência do resultado:
0001_Compartilhando_03
Comentário: É sempre importante testar a coerência dos valores retornados. Como se trata de um intervalo pequeno, isso pode ser feito visualmente. Quando suas fórmulas tiverem que abranger intervalos muito grandes, primeiro faça testes de pequena escala antes, e à medida que obtenha valores coerentes vá expandindo o escopo das fórmulas.
Estabelecido o valor MÍNIMO, como podemos agora determinar o fornecedor que fez a oferta?
A linha de raciocínio é a seguinte:
1. Sabendo qual o valor mínimo, teremos que determinar em que coluna ele ocorre.
2. Se soubermos a coluna, e sabendo que os nomes dos fornecedores estão na linha 5, temos que agora retornar o valor que está na 5º linha e na coluna determinada na etapa 1.
Existem várias formas de fazê-lo, mas utilizaremos duas funções muito utilizadas em conjunto: CORRESP e ÍNDICE.
Primeiro utilizaremos CORRESP para determinar em que coluna do intervalo F7:M7 está localizado o menor valor, que está calculado em C7
Desta forma inserimos em O7, a fórmula: =CORRESP(H7;F7:M7;0)
0001_Compartilhando_04
Observem que a fórmula retorna o valor 3. Isso significa que o valor mínimo é o terceiro valor (da esquerda para a direita) dos valores localizados no intervalo F7:M7.
Utilizaremos agora a função ÍNDICE para retornar o terceiro valor do intervalo F5:M5 no qual estão localizados os nomes dos fornecedores.
Para isso colocaremos em P7 a fórmula: =ÍNDICE($F$5:$M$5;O7).
0001_Compartilhando_05
Vejamos se o resultado retornado é coerente:
usuariosdoexcel.wordpress.com
Conforme esperado, o fornecedor exibido corresponde ao valor mínimo das cotações.
Vamos realizar mais alguns passos:
1. Como o valor em P7 faz referência ao valor em O7, iremos combinar as duas fórmulas numa só, colocando em O7: =ÍNDICE($F$5:$M$5;CORRESP(H7;F7:M7;0))
2. Já ficou melhor, mas temos um problema: se os valores não estiverem preenchidos a fórmula acima retorna um valor de erro #N/D. Para evitarmos que isso polua a planilha, vamos adaptar a fórmula inserindo a função SEERRO. Ficamos então com: =SEERRO(ÍNDICE($F$5:$M$5;CORRESP($C7;$F7:$M7;0));””).
3. É uma boa prática diferenciarmos as células de acordo com o seu propósito. Para isso vamos destacar as células nas quais inserimos fórmulas (Colunas C e O), com um fundo e fonte azuis. Isso alerta os usuários da planilha de que esse conteúdo não deve ser alterado e facilita auditoria de problemas, pois fica visualmente mais fácil de entender de onde estão localizados os cálculos.
E temos aqui a versão final:
0001_Compartilhando_07
Agradeço ao Diêgo pela sugestão de compartilharmos essas informações no Blog.
O arquivo está disponível para download em: Link

Anúncios

In Excel We Trust

Marcado com: , , ,
Publicado em Excel
12 comentários em “Compartilhando soluções – 0001
  1. ilidio disse:

    Boas, Achei muito interessante esta tabela, mas com esta formula ela vai-me dar sempre o F.3 como melhor fornecedor, o que eu queria era por exemplo na celula O8 ele me fosse dar automaticamente o melhor fornecedor, mas com esta formula da-me sempre o F.3 como o melhor fornecedor, será que existem alguma forma de dar volta a esta situação? se houver agradecia que me ajudassem.

    Obrigado

  2. jpliveira@uol.com.br disse:

    Estou enviando uns cálculos para o recebimentos de encargos financeiros, quando uma firma recebe um determinado valor de um cliente com vários dias de atraso com certo limite de tolerância. Caso acharem que devem publicar isso, podem faze-lo com toda a liberdade. Se tiverem algo semelhante que já foi publicado, esqueçam. Os valores em questão podem serem alterados de acordo com as necessidades dos usuários. Abraços   JP  

  3. Clari disse:

    Muito boa essa tabela!
    Agora, uma dúvida: e no caso de termos dois fornecedores com os menores preços idênticos?
    Por exemplo, F.3 e F.6 estivessem cobrando o mesmo valor? A função retomaria apenas o fornecedor 3, certo? Existe alguma maneira de apresentarmos todos os fornecedores com o menor preço?
    Obrigada

    • Boa tarde, Clari.
      Uma das dificuldades das funções do Excel é colocar múltiplos retornos numa mesma célula. A primeira dificuldade é determinar quantos seriam os retornos, em seguida como deveriam ser listados e finalmente preparar a listagem. Para evitar um aumento na complexidade e consequente interpretação das fórmulas, em situações como essa eu sugiro lançarmos mão de uma formatação condicional que permita destacar na linha qual(is) o(s) valor(es) mínimo(s). E caso haja mais de um, o usuário pode manualmente editar o valor colocando, por exemplo, um centavo de diferença de forma a eliminar uma das possibilidades.

  4. andrericarte disse:

    Muito boa a explicação! Obrigado

  5. Bruno disse:

    Olá, só uma duvida exemplo minha planilha tem 5 fornecedores mas somente três cotaram, se eu não colocar valores nas células a formula me da o menor valor do campo em branco, como posso corrigir isso?

  6. Cássia disse:

    Tenho a mesma dúvida do Bruno, se um dos meus forncederos estiver com valor em branco retonará ela, como posso excluir os que tiverem feito cotação?

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s

Visitas dos usuários
  • 2,074,858

Clique para assinar este blog e receber notificações de novos artigos por email.

Junte-se a 1.536 outros seguidores

Aumente sua interação

Siga-nos do twitter (@usuariosdoexcel).

Acesse o nosso fórum e compartilhe as suas dúvidas.

Curta nossa página no Facebook.

Junte-se ao grupo do LinkedIn.

Visite-nos utilizando o seu smartphone ou tablet.

Recomende este Blog
Visualizar notícias
Site monitorado:
Real Time Web Analytics

Clicky

%d blogueiros gostam disto: