Dicas do Microsoft Excel é ao Domingo…no Pplware – 7

19 Fev 2012

22 Comentários

Por João Pinto para o PPLWARE.COM

Objectivo: Aprender a trabalhar com a função VLOOKUP (PROCV em Português)

Ora vivam caros leitores. Mais um Domingo e mais uma artigo da rubrica “Dicas do Microsoft Excel é ao Domingo….no Pplware”. Lembramos que esta é uma rubrica “aberta” a todos os leitores que pretendam contribuir. Para tal devem enviar-nos um e-mail com a dica que pretendem ver publicada. Caso tenham também alguma dúvida ou problema a resolver no Excel, deixem essa informação nos comentários para que a comunidade Pplware ajude a resolver.

Tal como já referimos aqui, a função VLOOKUP (PROCV em Português) é uma função muito interessante e muito utilizada pois permite procurar por um valor numa tabela/matriz e devolver o valor que está na coluna ou na coluna à direita, indicando-se para isso o índice.

Imaginemos que temos a seguinte tabela de dados:

Se, numa célula, colocarmos um ID, queremos ir buscar o nome correspondente a esse ID. Para isso, o Excel tem uma função chamada PROCV (VLOOKUP na versão inglesa).

A sintaxe para esta função é a seguinte:

PROCV(valor_proc,matriz_tabela,núm_índice_coluna,[procurar_intervalo])

No exemplo em baixo, temos o ID a procurar na célula B14 e queremos colocar, na célula B15, o nome correspondente da nossa tabela.

Assim, colocamos nessa célula, a seguinte fórmula:

PROCV(B14,A2:B12,2,FALSO)

De volta à nossa sintaxe, temos então os seguintes parâmetros para a nossa função:

valor_proc: este é o valor a procurar. Neste caso, é o ID que está na célula B14;
matriz_tabela: aqui temos a tabela aonde vamos procurar o nosso valor, assim temos as células A2:B12;
núm_índice_coluna: este é o número da coluna aonde está a informação que queremos receber da função, caso encontre o valor a procurar. Neste exemplo queremos retornar o nome, o que corresponde à coluna 2;
[procurar_intervalo]: este é um argumento opcional. Caso optemos por o usar, este pode ser apenas VERDADEIRO ou FALSO. Colocar VERDADEIRO se quisermos uma correspondência aproximada e FALSO se quisermos uma correspondência exacta. Neste exemplo queremos uma correspondência exacta pelo que colocamos FALSO.

Assim, a nossa fórmula encontra o ID 1002 na linha 4 da nossa tabela e retorna o nome correspondente (“Ted Dawson”).

Se o ID a procurar não existisse na nossa tabela, a fórmula iria devolver um erro de “#N/D” (não disponível).

Por hoje é tudo…esperamos que as dicas sejam úteis e que vos ajudem a dominar o Microsoft Excel. Como referi anteriormente, esta dica está aberta a todos os que pretendam contribuir. Enviem-nos as vossas dicas para ppinto @ pplware .com para publicarmos em próximos artigos.

Download: Ficheiro Excel utilizado (aqui)

PUB

Autor: Pedro Pinto

Partilhar:

Tags:

PUB.

Questão Semanal

Loading …

Arquivo de Questões

PUB.

Velocímetro Pplware

Teste a velocidade da sua Internet

Arquivo

Artigo anterior

iResize – O tamanho das imagens não são problema no Mac OS X

Próximo artigo

Pplware Classics…

PUB

Comentários

Deixe um comentário Cancelar resposta

Valquiria

19 de Fevereiro de 2012

Agora a parte difícil: a célula não devolver o erro “N/D”, como é que faço?

Responder
1. rom_c
  
  19 de Fevereiro de 2012
  
  Boas!
  Não sei se a questão é um desafio ou se não sabe mesmo como fazer…
  Primeiro testei se havia nomes associados ao id que queremos. Caso não haja nomes associados retorna a seguinte mensagem “Não existe nenhum nome associado a esta entrada”.
  No caso de haver um nome associado, utilizei a função SE.ERRO (função apenas disponível a partir do Excel 2007 e que julgo que em inglês se designe por IFERROR) para determinar que caso seja devolvido o “N/D” colocar uma mensagem diferente “Ainda não existe essa número de entrada”
  
  =SE(SE.ERRO(PROCV(B14;A2:B12;2;FALSO);“Ainda não existe essa número de entrada”)=0;”Não existe nenhum nome associado a esta entrada”;SE.ERRO(PROCV(B14;A2:B12;2;FALSO);“Ainda não existe essa número de entrada”))
  
  Espero ter ajudado ou respondido ao seu desafio 😉
  
  Responder
  1. Paulo Correia
    
    19 de Fevereiro de 2012
    
    Para esta fórmula utiliza antes o ISNA(), porque o iserror() é mais abranjente e pode esconder outro tipo de erros.
    
    Responder
    1. rom_c
      
      19 de Fevereiro de 2012
      
      Bem, optei por utilizar o IFERROR e depois o ISERR pois pareceu-me que os erros que poderiam surgir eram os que eu contornei, ou seja, o haver ou não haver id e o facto de haver ou não associação de um nome ao id.
      
      De qualquer forma, obrigado pelo feedback. Desconhecia o ISNA
      
      Responder
      1. Paulo Correia
        
        19 de Fevereiro de 2012
        
        Não dá o mesmo resultado porque o isna apenas é activado se não encontrar o que procura, enquanto o iserr() é activado quando encontre qualquer tipo de erro.
        
        Se ouver erros na coluna de pesquisa, ambos têm o mesmo comportamento, mas se houver erros nas coluna a retorna (a coluna no 3º argumento), as coisas mudam de figura
        
        Imagina que tens uma base onde numa das colunas tens fórmulas e algumas dão erro, tipo #div/0. Se mandares retornar os valores dessa coluna, tens comportamentos diferentes
        
        Se usares o iserror() ele detecta um erro (porque o valor a retornar é um erro) e tem como resultado o teu “Ainda não existe essa número de entrada”
        
        Se usares o isna() retorna o DIV/0 e sabes que há problemas na base.
        
        Tens aqui um pequeno exemplo das diferenças de comportamento: http://bit.ly/zegRcy
        
        Outra diferença é se pedires uma coluna fora do bloco, isto é, defines 5 colunas e pedes a 10ª
  2. rom_c
    
    19 de Fevereiro de 2012
    
    Aquela fórmula ainda me retornava erros.
    Esta agora já me parece bem.
    
    =SE(É.ERRO(PROCV(B14;A2:B12;2;FALSO));”Não existe esse id”;SE(PROCV(B14;A2:B12;2;FALSO)=0;”Não existe nenhum nome associado a esse id”;PROCV(B14;A2:B12;2;FALSO)))
    
    Responder
  3. Valquiria
    
    19 de Fevereiro de 2012
    
    Não era desafio, tenho vários livros de excel em que uso o procv e precisava que não aparecesse o N/D. A função se.erro segundo certos sites de referência para formulas de excel, não é abrangente, indicando a função isna, que eu até hoje não consegui traduzir, mas finalmente descobri a tradução para as formulas em PT-PT, ficando qualquer coisa assim:
    {=SE(É.NÃO.DISP(PROCV(B12;A5:C8;2;FALSO));””;(PROCV(B12;A5:C8;2;FALSO))} . Entre as aspas pode-se também personalizar a mensagem de alerta. De qualquer maneira obrigada pela dica.
    
    Responder
    1. rom_c
      
      19 de Fevereiro de 2012
      
      A “tradução” da função ISNA é É.NÃO.DISP.
      Podes encontrar todas ou quase todas as traduções das funções aqui:
      http://fazaconta.com/excel-ingles-portugues.htm
      
      Responder
Tactos

19 de Fevereiro de 2012

Existe 2 ted na lista, neste caso da para colocar as varias ida que ele tem?

Responder
1. rom_c
  
  19 de Fevereiro de 2012
  
  Utilizando VBA era fácil, mas utilizando funções, não me ocorre nenhuma ideia de como percorrer uma coluna de alto a baixo procurando os nomes. É fácil determinar quantos nomes se repetem de acordo com o nome que for introduzido numa célula.
  Ah, aqui o processo deveria ser completamente diferente, pois temos de procurar os nomes, não os id.
  
  Responder
2. João Pinto
  
  19 de Fevereiro de 2012
  
  Existem 2 Ted na lista mas estamos a procurar o ID único 1004, não pelo nome.
  
  Responder
fiendmost

19 de Fevereiro de 2012

OFF:

recebi agora minha conta no PINTEREST e notei que o botão de PIN do PPLWARE não está funcionando, tenho que copiar e colar diretamente a URL para que consiga dar um Pin nos tópicos.

Responder
Paulo Correia

19 de Fevereiro de 2012

Quando se utiliza a fórmula com o argumento “Verdadeiro” ou sem o 4ª argumento o que vai dar ao mesmo, a tabelatêm de estar ordenada, porque a fórmula retorna o valor mais aproximado mas anterior.

Se a tabela tiver

100 – Primeiro
200 – Terceiro
150 – Segundo

e pesquisarem por 160, ela retorna “Primero” se estiver como o exemplo e “Segundo” se estiver ordenada.

Esta fórmula é muito poderosa mas muito perigosa, porque funciona apenas com os 3 primeiros argumentos e nesse caso a pesquisa é aproximada e os resultado podem ser muito diferentes. Deve ser sempre utilizado o 4º argumento.

Responder
Pedro A.

19 de Fevereiro de 2012

Já agora podem por 0 no último argumento em vez de Falso que é a mesma coisa mas mais rápido de escrever.

Responder
1. João Pinto
  
  19 de Fevereiro de 2012
  
  É correcto, pode usar-se 0 ou 1, em vez de verdadeiro ou falso.
  
  Responder
R@lf

19 de Fevereiro de 2012

Já utilizo esta fórmula há bastante tempo, pois é muito eficaz para uma procura rápida num indíce que não se repita.
O curioso é que a fórmula só funciona correctamente se os dados estiverem ordenados por ordem crescente.
Se, por exemplo, os valores da coluna “ID”, estiverem por ordem decrescente, ou desordenados, a fórmula já não é eficaz.
Ou será só comigo que isso acontece?

Responder
1. João Pinto
  
  19 de Fevereiro de 2012
  
  Esta função não precisa que a lista esteja ordenada!
  
  Responder
2. José Augusto
  
  20 de Fevereiro de 2012
  
  A este proposito, transcrevo, por cópia, o conteúdo da ajuda on-line do Excel 2010 (versão portuguesa)
  PROCV localize uma correspondência exacta ou uma correspondência aproximada:
  Se procurar_intervalo for VERDADEIRO ou omitido, é devolvida uma correspondência exacta ou aproximada. Se não for encontrada uma correspondência exacta, é devolvido o valor maior seguinte que seja menor que valor_proc.
  Importante Se procurar_intervalo for VERDADEIRO ou omitido, os valores da primeira coluna da matriz_tabela têm de ser colocados por ordem ascendente; caso contrário, PROCV poderá não devolver o valor correcto.
  
  Para obter mais informações, consulte Ordenar dados num intervalo ou numa tabela.
  
  Se procurar_intervalo for FALSO, os valores na primeira coluna da matriz tabela não precisam de estar ordenados.
  
  Responder
paulo g.

24 de Fevereiro de 2012

Aqui vai o vídeo feito em LibreOffice Calc 3.5, com as mesmas funções do M$Excel. Acrescentei mais uma coluna, para ser mais perceptível a função ProcV, uma função extra (AleatórioEntre) e uma grande dica dos comentários. Veja aqui:
http://youtu.be/qnm1Z6MZvCU

Responder
Daniel Santos

11 de Março de 2012

Boas!
Há forma de procurar pelo “valor mais recente”?
Por exemplo, tenho uma coluna de A1 e por aí abaixo, na qual vou introduzindo valores novos, mas preciso que numa determinada célula me apreça somente o valor mais recente que tenha sido introduzido nessa coluna de A1 …

Bom Domingo!

Responder
1. ITWare2008
  
  15 de Janeiro de 2013
  
  Amigo Daniel Santos,
  
  De certeza que esta dica já vai tarde mas para quem, como eu, só agora encontrou esta rubrica aqui fica uma dica para obter o resultado que pretende.
  Assumindo que vai inserir os valores a partir da célula A1 para baixo, e que na célula B1 quer que apareça o valor mais recente, na célula B1 escreva a seguinte fórmula:
  
  =INDIRECTO(ENDEREÇO(CONTAR.VAL($A:$A);1))
  
  Explicação:
  
  A função contar.val() conta as células não vazias no intervalo especificado;
  A função endereço(linha;coluna) devolve, em forma de texto, o endereço da célula na linha e coluna especificada. Neste caso o 1 significa coluna A, o número da linha é devolvido pela função contar.val()
  A função indirecto() devolve o conteúdo da célula cujo endereço foi especificado. Neste caso o endereço é especificado pela função endereço().
  
  Apesar de tardiamente espero ter ajudado.
  
  ITWare2008
  
  Responder
Luciana

30 de Agosto de 2012

Bom dia,

Tenho uma coluna com diversos valores, todos do tipo “letra espaço algarismo algarismo”, como exemplo: F 72, P 59, M 91, H 59…).
Preciso de uma fórmula que me permita, numa única célula, procurar nessa coluna pelas ocorrências de “59” e que me dê como resultado “P, H”.
Estou a usar fórmula com as funções indice com corresp, mas quando há valores repetidos – como o 59 do exemplo – a resposta é apenas o 1º encontrado – neste caso a letra P.
Preciso de outras funções, de novos argumentos, … ?

Grata,
Luciana

Responder

Aviso:

Todo e qualquer texto publicado na internet através deste sistema não reflete, necessariamente, a opinião deste site ou do(s) seu(s) autor(es). Os comentários publicados através deste sistema são de exclusiva e integral responsabilidade e autoria dos leitores que dele fizerem uso. A administração deste site reserva-se, desde já, no direito de excluir comentários e textos que julgar ofensivos, difamatórios, caluniosos, preconceituosos ou de alguma forma prejudiciais a terceiros. Textos de caráter promocional ou inseridos no sistema sem a devida identificação do seu autor (nome completo e endereço válido de email) também poderão ser excluídos.