O Excel VLOOKUP é poderoso, mas não funcionará quando você tiver situações específicas. Hoje, uma olhada em como solucionar problemas de VLOOKUP.
VLOOKUP é minha função favorita no Excel. Se você pode fazer VLOOKUP, é capaz de resolver muitos problemas no Excel. Mas há coisas que podem causar uma PROCV. Este tópico fala sobre alguns deles.
Mas, primeiro, o básico de VLOOKUP em inglês simples.
Os dados em A: C vieram do departamento de TI. Você solicitou vendas por item e data. Eles deram a você o número do item. Você precisa da descrição do item. Em vez de esperar que o departamento de TI execute novamente os dados, você encontra a tabela mostrada na coluna F: G.
Você deseja que PROCV encontre o item em A2 enquanto pesquisa a primeira coluna da tabela em $ F $ 3: $ G $ 30. Quando VLOOKUP encontra a correspondência em F7, você deseja que VLOOKUP retorne a descrição encontrada na segunda coluna da tabela. Cada VLOOKUP que procura uma correspondência exata deve terminar em False (ou zero, que é equivalente a False). A fórmula abaixo está configurada corretamente.
Observe que você usa F4 para adicionar quatro cifrões ao endereço da tabela de pesquisa. Ao copiar a fórmula para baixo na coluna D, você precisa que o endereço da tabela de pesquisa permaneça constante. Existem duas alternativas comuns: Você pode especificar as colunas F: G inteiras como a tabela de pesquisa. Ou você pode nomear F3: G30 com um nome como ItemTable. Se você usar =VLOOKUP(A2,ItemTable,2,False), o intervalo nomeado atua como uma referência absoluta.
Sempre que você fizer vários VLOOKUPs, precisará classificar a coluna de VLOOKUPs. Classifique ZA e quaisquer erros # N / A virão para o topo. Neste caso, existe um. O item BG33-9 está faltando na tabela de pesquisa. Talvez seja um erro de digitação. Talvez seja um item novo. Se for novo, insira uma nova linha em qualquer lugar no meio de sua tabela de pesquisa e adicione o novo item.
É bastante normal ter alguns erros # N / A. Mas na figura abaixo, exatamente a mesma fórmula está retornando nada além de # N / A. Quando isso acontece, vejo se consigo resolver o primeiro VLOOKUP. Você está procurando o BG33-8 encontrado em A2. Comece navegando pela primeira coluna da tabela de pesquisa. Como você pode ver, o valor correspondente está claramente em F10. Por que você pode ver isso, mas o Excel não pode ver?
Vá para cada célula e pressione a tecla F2. Aqui está F10. Observe que o cursor de inserção aparece logo após o 8.
Aqui está a célula A2 no modo Editar. O cursor de inserção está a alguns espaços de distância do 8. Isso é um sinal de que em algum ponto esses dados foram armazenados em um antigo conjunto de dados COBOL. De volta ao COBOL, se o campo Item fosse definido como 10 caracteres e você digitasse apenas 6 caracteres, o COBOL o preencheria com 4 espaços extras.
A solução? Em vez de procurar A2, procure o TRIM(A2).
A função TRIM () remove espaços à esquerda e à direita. Se você tiver vários espaços entre as palavras, TRIM irá convertê-los em um único espaço. Na figura abaixo, há espaços antes e depois de ambos os nomes em A1. =TRIM(A1)remove todos, exceto um espaço em A3.
A propósito, e se o problema fosse espaços à direita na coluna F em vez da coluna A? Adicione uma coluna de funções TRIM () a E, apontando para a coluna F. Copie esses e cole como valores em F para fazer as pesquisas começarem a funcionar novamente.
O outro motivo muito comum pelo qual PROCV não funciona é mostrado aqui. A coluna F possui números reais. A coluna A contém texto que se parece com números.
Selecione toda a coluna A. Pressione alt = "" + D, E, F. Isso cria um Texto para Colunas padrão e converterá todos os números de texto em números reais. A pesquisa começa a funcionar novamente.
Assistir vídeo
- VLOOKUP resolve muitos problemas
- Problemas comuns de VLOOKUP:
- Se VLOOKUP começar a funcionar, mas # N / A se tornar mais proeminente: esqueci $ na tabela de pesquisa
- Alguns # N / A: itens faltando na mesa
- Nenhuma das VLOOKUP funciona: verifique se há espaços à direita
- Remova os espaços finais com TRIM
- Números e números armazenados como texto
- Selecione as duas colunas e use alt = "" + DEF
- O episódio inclui uma piada que tanto contadores quanto pessoal de TI acham engraçada, mas por razões diferentes
Transcrição de vídeo
Aprenda Excel com o podcast, episódio 2027 - Solução de problemas VLOOKUP!
Tudo bem, podcasting este livro inteiro, clique no “i” no canto superior direito para ir para a lista de reprodução!
VLOOKUP É minha função favorita em todo o Excel, e sempre conto essa piada em um dos meus seminários, e isso dá risada, quer você seja uma pessoa de TI ou não. Eu sempre digo “Bem, veja, temos esse conjunto de dados aqui à esquerda, e posso olhar para esses dados e dizer que vieram do departamento de TI porque é exatamente o que pedi, mas não exatamente o que eu precisava. Pedi a data e a quantidade do item e eles me deram a data e a quantidade do número do item, mas não se preocuparam em me dar a descrição, certo? ” E os contadores sempre riem disso, porque isso acontece com eles o tempo todo, e o pessoal de TI fica tipo "Bem, eu dei a você o que você pediu, não é minha culpa!" Ambos acham engraçado por diferentes razões, então, você sabe, e o cara de TI está ocupado, ele não pode voltar a reescrever a consulta,então temos que fazer algo para resgatar isso nós mesmos.
E então esta pequena tabela que copiei de algum outro lugar no meu computador, em inglês simples, o que VLOOKUP faz é dizer “Ei, temos um número de item W25-6.” Temos uma tabela aqui. Quero navegar pela primeira coluna da tabela até encontrar o número do item e, em seguida, retornar algo dessa linha. Tudo bem, neste caso, o que eu quero é a 2ª coluna dessa linha. E então, no final de cada VLOOKUP, temos que colocar FALSE ou 0. Agora bem aqui, depois de escolher o intervalo, vou pressionar a tecla F4 e, em seguida, quero a 2ª coluna e depois FALSE para um valor exato partida. Nunca escolha uma correspondência aproximada, nunca, nunca! Não é uma correspondência aproximada, é uma coisa muito especial, não funciona o tempo todo. Se você quiser reafirmar seus números à Comissão de Valores Mobiliários, sinta-se à vontade para usar,TRUE ou simplesmente deixe FALSE desativado. Mas cada VLOOKUP que você criar deve terminar em FALSE ou 0, 0 é mais curto que FALSE, você deve colocar um FALSE aqui, mas um 0 é a mesma coisa que FALSE.
Tudo bem agora, solução de problemas, a primeira coisa, quando você faz um monte de VLOOKUPs, é muito normal ter alguns # N / As, certo? E eu sempre encontro os # N / A's indo para Data, ZA, que traz o # N / As para o topo, bem ali, BG33-9, ou isso é um erro de digitação ou é um item totalmente novo, certo, e nós temos entender. Então aqui à direita eu tenho os novos dados, vou recortá-los, e então Alt + IED, inserir essas células no meio, não precisa ser alfabética, essa tabela não precisa ser classificada. Quando você está usando a versão FALSA, a tabela não é - você pode simplesmente colocá-la onde quiser, não coloquei no final porque não tive que reescrever a fórmula, só quero que a fórmula trabalhos. Tudo bem, alguns # N / A's, extremamente, extremamente normais, mas verifique isso.
Quando você começa com respostas que funcionam, mas então os # N / A's começam a aparecer com certa frequência e, eventualmente, aparecem totalmente para baixo, isso é um sinal claro de que você não bloqueou a referência da tabela. Tudo bem, então aqui a tabela está se movendo enquanto copio a fórmula, certo, e estou tendo sorte em acertar alguns que estavam no final da lista. Mas, eventualmente, chego ao ponto em que parece aqui em células completamente em branco e, claro, nada foi encontrado. Tudo bem, então essa é a primeira coisa, você consegue alguns que funcionam, alguns # N / A's, mais alguns que funcionam, e então todos os # N / A's o resto do caminho - sinal claro de que você não colocou o $ in.
Basta voltar, F2 para o modo de edição, selecionar os dois pontos, pressionar F4, que coloca todo o $, clique duas vezes para derrubar isso, e as coisas começam a funcionar de novo, certo. O próximo, exatamente a mesma fórmula, a fórmula é perfeita, BG33-8 veja, não estamos entendendo nada disso direito. Tudo bem, então eu vou olhar, BG33-8, ei, aí está, está bem ali, está em vermelho, eu deveria ter visto! Chego a este do lado direito, pressiono F2 e observo o ponto de inserção piscando e vejo, é logo após o 8, assim, e então venho aqui e pressiono F2, há alguns espaços à direita lá. Isso é muito, muito comum na época do COBOL, eles diziam "Sabe, vamos dar a você 10 espaços para o número do item e se você não digitar todos os 10 espaços, eles preencherão os espaços . ” E isso é muito comum,e a grande solução aqui é se livrar desses espaços à esquerda e à direita com a função TRIM. Em vez de A2 use o TRIM (A2), clique duas vezes para derrubá-lo, certo, o BG33-9 ainda está de volta na outra mesa.
Tudo bem, para que você entenda como o TRIM funciona, digitei vários espaços, John, vários espaços, Durran e vários espaços, e concatenei um * antes e depois para que você possa ver como fica . Quando eu peço o TRIM (P4), nos livramos de todos os espaços à esquerda, de todos os espaços à direita e, então, os vários espaços internos são reduzidos a um espaço. Tudo bem, então você pode ver, meio que visualizar aí, que eles estão se livrando dos espaços à esquerda e à direita, quaisquer espaços dobrados no meio se tornam um único espaço assim. Então TRIM, ótima, ótima ferramenta em seu arsenal, certo, aqui está outra muito comum.
Se não forem os espaços finais, então a coisa mais comum que vi é onde aqui temos números verdadeiros e aqui temos números armazenados como texto. E PROCV não verá isso como uma correspondência, embora 4399, este é um número, este é um texto, não funcione. A maneira mais rápida de converter uma coluna de texto em números, selecione a coluna, 3 letras em sequência, alt = "" DEF, e de repente, nossos VLOOKUPs começam a funcionar novamente, ótima dica ótima de cerca de 1500 podcasts atrás. Tudo bem, esses são os problemas de VLOOKUP mais comuns, ou você esqueceu o $, ou você tem espaços à direita, ou tem números e números armazenados como texto. Todas essas dicas estão neste livro “MrExcel XL”, clique no “i” no canto superior direito, você pode comprar o livro.
Tudo bem, recapitulação rápida: VLOOKUP resolve muitos problemas, se uma VLOOKUP começar a funcionar, mas # N / A! torna-se mais proeminente, você se esqueceu de colocar o $ na tabela de pesquisa. Se houver alguns # N / A, são apenas itens faltando na tabela. Se nenhum dos VLOOKUPs funcionar e for texto, verifique se há espaços à direita, você pode usar a função TRIM. Se você tiver números e números armazenados como texto, selecione uma das colunas, aquela que contém o texto, e faça alt = "" DEF para que todos eles voltem aos números.
Tudo bem, bem, quero agradecer a sua visita, nos vemos na próxima vez para outro netcast do!
⇬ Fazer download do arquivo
Baixe o arquivo de amostra aqui: Podcast2027.xlsx
