Proponha uma correção, faça uma sugestão
Dicas do Microsoft Excel é ao Domingo…no Pplware – 7
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.
- [1] Operações básica: SOMA, MÉDIA, MÁXIMO, MINIMO
- [2] Actualização da formula de acordo com a linha e coluna
- [3] Referências fixas
- [4] Utilização de formulas e valores em várias folhas
- [5] Destacar facilmente valores em Excel
- [6] Dividir uma String (frase) em partes
Download: Ficheiro Excel utilizado (aqui)
Este artigo tem mais de um ano
Loading ...
Agora a parte difícil: a célula não devolver o erro “N/D”, como é que faço?
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 😉
Para esta fórmula utiliza antes o ISNA(), porque o iserror() é mais abranjente e pode esconder outro tipo de erros.
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
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ª
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)))
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.
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
Existe 2 ted na lista, neste caso da para colocar as varias ida que ele tem?
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.
Existem 2 Ted na lista mas estamos a procurar o ID único 1004, não pelo nome.
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.
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.
Já agora podem por 0 no último argumento em vez de Falso que é a mesma coisa mas mais rápido de escrever.
É correcto, pode usar-se 0 ou 1, em vez de verdadeiro ou falso.
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?
Esta função não precisa que a lista esteja ordenada!
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.
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
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!
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
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