Conteúdo
Esta lição explica como lidar rapidamente com uma situação em que uma função VPR (VLOOKUP) não quer funcionar no Excel 2013, 2010, 2007 e 2003, e como identificar e corrigir erros comuns e superar limitações VPR.
Em vários artigos anteriores, exploramos as várias facetas da função VPR em Excel. Se você os leu com atenção, agora deve ser um especialista neste campo. No entanto, não é sem razão que muitos especialistas em Excel acreditam VPR uma das características mais complexas. Ele tem um monte de limitações e recursos que se tornam a fonte de muitos problemas e erros.
Neste artigo você encontrará explicações simples de erros #NO (#N / D), # Nome? (#Nome e #VALOR! (#VALUE!) que aparecem ao trabalhar com a função VPR, bem como técnicas e métodos de lidar com eles. Começaremos com os casos mais comuns e as razões mais óbvias. VPR não funciona, então é melhor estudar os exemplos na ordem em que são dados no artigo.
Corrigindo o erro #N/A na função PROCV no Excel
Em fórmulas com VPR mensagem de erro #NO (#N/A) significa não disponível (sem dados) – aparece quando o Excel não consegue encontrar o valor que você está procurando. Isso pode acontecer por vários motivos.
1. O valor desejado está incorreto
Boa idéia para verificar este item primeiro! Erros de digitação geralmente ocorrem quando você trabalha com grandes quantidades de dados, consistindo em milhares de linhas, ou quando o valor que você está procurando está escrito em uma fórmula.
2. Erro #N/A ao procurar uma correspondência aproximada com PROCV
Se você usar uma fórmula com uma condição de pesquisa de correspondência aproximada, ou seja, argumento Pesquisa de alcance (range_lookup) for TRUE ou não especificado, sua fórmula pode relatar um erro #N / D em dois casos:
- O valor a ser pesquisado é menor que o menor valor na matriz que está sendo pesquisada.
- A coluna de pesquisa não é classificada em ordem crescente.
3. Erro #N/A ao procurar uma correspondência exata com PROCV
Se você estiver procurando por uma correspondência exata, ou seja, argumento Pesquisa de alcance (range_lookup) for FALSE e o valor exato não foi encontrado, a fórmula também reportará um erro #N / D. Saiba mais sobre como pesquisar correspondências exatas e aproximadas com uma função VPR.
4. A coluna de pesquisa não está mais à esquerda
Como você provavelmente sabe, uma das limitações mais significativas VPR é que ele não pode ficar virado para a esquerda, portanto, a coluna de pesquisa em sua tabela deve estar mais à esquerda. Na prática, muitas vezes nos esquecemos disso, o que leva a uma fórmula que não funciona e a um erro. #N / D.
Decisão: Se não for possível alterar a estrutura de dados para que a coluna de pesquisa fique mais à esquerda, você pode usar uma combinação de funções ÍNDICE (ÍNDICE) e MAIS EXPOSTOS (MATCH) como uma alternativa mais flexível para VPR.
5. Os números são formatados como texto
Outra fonte de erro #N / D em fórmulas com VPR são números em formato de texto na tabela principal ou na tabela de pesquisa.
Isso geralmente acontece quando você importa informações de bancos de dados externos ou quando digita um apóstrofo antes de um número para manter o zero à esquerda.
Os sinais mais óbvios de um número em formato de texto são mostrados na figura abaixo:
Além disso, os números podem ser armazenados no formato Geral (Em geral). Nesse caso, há apenas um recurso perceptível – os números são alinhados à borda esquerda da célula, enquanto por padrão são alinhados à borda direita.
Decisão: Se for um valor único, basta clicar no ícone de erro e selecionar Converter para número (Converter em Número) no menu de contexto.
Se esta for a situação com muitos números, selecione-os e clique com o botão direito do mouse na área selecionada. No menu de contexto que aparece, selecione formatar células (Formatar células) > guia Sessão (Número) > formato Sessão (numérico) e pressione OK.
6. Há um espaço no início ou no final
Esta é a razão menos óbvia para o erro. #N / D em função VPR, pois é visualmente difícil ver esses espaços extras, especialmente ao trabalhar com tabelas grandes, quando a maioria dos dados está fora da tela.
Solução 1: Espaços extras na tabela principal (onde está a função PROCV)
Se aparecerem espaços extras na tabela principal, você pode garantir que as fórmulas funcionem corretamente colocando o argumento lookup_value (lookup_value) em uma função TRIM (APARAR):
=VLOOKUP(TRIM($F2),$A$2:$C$10,3,FALSE)
=ВПР(СЖПРОБЕЛЫ($F2);$A$2:$C$10;3;ЛОЖЬ)
Solução 2: Espaços extras na tabela de pesquisa (na coluna de pesquisa)
Se houver espaços extras na coluna de pesquisa – maneiras simples #N / D na fórmula com VPR não pode ser evitado. Ao invés de VPR Você pode usar uma fórmula de matriz com uma combinação de funções ÍNDICE (ÍNDICE), MAIS EXPOSTOS (MATCH) e TRIM (APARAR):
=INDEX($C$2:$C$10,MATCH(TRUE,TRIM($A$2:$A$10)=TRIM($F$2),0))
=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(ИСТИНА;СЖПРОБЕЛЫ($A$2:$A$10)=СЖПРОБЕЛЫ($F$2);0))
Como esta é uma fórmula de matriz, não se esqueça de pressionar Ctrl + Shift + Enter em vez do habitual Entrarpara inserir a fórmula corretamente.
Erro #VALUE! em fórmulas com PROCV
Na maioria dos casos, o Microsoft Excel relata um erro #VALOR! (#VALUE!) quando o valor usado na fórmula não corresponde ao tipo de dados. Relativo VPR, geralmente há dois motivos para o erro #VALOR!.
1. O valor que você está procurando tem mais de 255 caracteres
Atenção: função VPR não pode pesquisar valores que contenham mais de 255 caracteres. Se o valor que você está procurando exceder esse limite, você receberá uma mensagem de erro. #VALOR!.
Decisão: Use um monte de recursos ÍNDICE + CORRESP (ÍNDICE + CORRESP). Abaixo está uma fórmula que funcionará bem para esta tarefa:
=INDEX(C2:C7,MATCH(TRUE,INDEX(B2:B7=F$2,0),0))
=ИНДЕКС(C2:C7;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B2:B7=F$2;0);0))
2. O caminho completo para a pasta de trabalho de pesquisa não foi especificado
Se você estiver recuperando dados de outra pasta de trabalho, deverá especificar o caminho completo para esse arquivo. Mais especificamente, você deve incluir o nome da pasta de trabalho (incluindo a extensão) entre colchetes [ ], seguido pelo nome da planilha, seguido por um ponto de exclamação. Toda esta construção deve ser colocada entre apóstrofos, caso o nome do livro ou folha contenha espaços.
Aqui está a estrutura completa da função VPR para pesquisar em outro livro:
=VLOOKUP(lookup_value,'[workbook name]sheet name'!table_array, col_index_num,FALSE)
=ВПР(искомое_значение;'[имя_книги]имя_листа'!таблица;номер_столбца;ЛОЖЬ)
A fórmula real pode ser assim:
=VLOOKUP($A$2,'[New Prices.xls]Sheet1'!$B:$D,3,FALSE)
=ВПР($A$2;'[New Prices.xls]Sheet1'!$B:$D;3;ЛОЖЬ)
Esta fórmula irá procurar o valor da célula A2 em uma coluna B na folha Sheet1 na pasta de trabalho Novos Preços e extraia o valor correspondente da coluna D.
Se qualquer parte do caminho da tabela for omitida, sua função VPR não funcionará e reportará um erro #VALOR! (mesmo que a pasta de trabalho com a tabela de pesquisa esteja aberta no momento).
Para mais informações sobre a função VPRreferenciando outro arquivo do Excel, consulte a lição: Pesquisando outra pasta de trabalho usando PROCV.
3. O argumento Column_num é menor que 1
É difícil imaginar uma situação em que alguém insira um valor menor que 1para indicar a coluna da qual extrair o valor. Embora seja possível se o valor deste argumento for calculado por outra função do Excel aninhada dentro VPR.
Então, se acontecer que o argumento col_index_num (column_number) menor que 1função VPR também reportará um erro #VALOR!.
Se o argumento col_index_num (column_number) é maior que o número de colunas na matriz fornecida, VPR reportará um erro # Ref! (#SSIL!).
Erro #NAME? em PROCV
O caso mais simples é um erro # Nome? (#NAME?) – aparecerá se você acidentalmente escrever um nome de função com um erro.
A solução é óbvia – verifique sua ortografia!
PROCV não funciona (limitações, advertências e decisões)
Além da sintaxe bastante complicada, VPR tem mais limitações do que qualquer outra função do Excel. Devido a essas limitações, fórmulas aparentemente simples com VPR muitas vezes levam a resultados inesperados. Abaixo, você encontrará soluções para vários cenários comuns em que VPR está errado.
1. PROCV não diferencia maiúsculas de minúsculas
função VPR não faz distinção entre maiúsculas e minúsculas e aceita caracteres minúsculos e maiúsculos como a mesma coisa. Portanto, se houver vários elementos na tabela que diferem apenas em maiúsculas e minúsculas, a função PROCV retornará o primeiro elemento encontrado, independentemente de maiúsculas e minúsculas.
Decisão: Use outra função do Excel que possa realizar uma pesquisa vertical (LOOKUP, SUMPRODUCT, INDEX e MATCH) em combinação com EXATOUm caso que distingue. Para obter mais detalhes, você pode aprender com a lição – 4 maneiras de diferenciar maiúsculas e minúsculas de PROCV no Excel.
2. PROCV retorna o primeiro valor encontrado
Como você já sabe, VPR retorna o valor da coluna fornecida correspondente à primeira correspondência encontrada. No entanto, você pode fazer com que ele extraia a 2ª, 3ª, 4ª ou qualquer outra repetição do valor desejado. Se você precisar extrair todos os valores duplicados, precisará de uma combinação de funções ÍNDICE (ÍNDICE), MENOS (PEQUENO) e LINHA (FILEIRA).
3. Uma coluna foi adicionada ou removida da tabela
Infelizmente, as fórmulas VPR pare de funcionar sempre que uma nova coluna for adicionada ou removida da tabela de pesquisa. Isso acontece porque a sintaxe VPR requer que você especifique o intervalo completo da pesquisa e o número de coluna específico para extração de dados. Naturalmente, tanto o intervalo fornecido quanto o número da coluna mudam quando você exclui uma coluna ou insere uma nova.
Decisão: E novamente as funções estão com pressa para ajudar ÍNDICE (ÍNDICE) e MAIS EXPOSTOS (COMBINE). Na fórmula ÍNDICE + CORRESP Você define separadamente as colunas de pesquisa e recuperação e, como resultado, pode excluir ou inserir quantas colunas desejar sem se preocupar em atualizar todas as fórmulas de pesquisa relacionadas.
4. As referências de célula são distorcidas ao copiar uma fórmula
Este título explica exaustivamente a essência do problema, certo?
Decisão: Sempre use referências de célula absolutas (com o símbolo $) nos registros do intervalo, por exemplo $ A $ 2: $ C $ 100 or $ A: $ C. Na barra de fórmulas, você pode alternar rapidamente o tipo de link clicando F4.
PROCV – trabalhando com as funções SEERRO e ISERROR
Se você não quer assustar os usuários com mensagens de erro #N / D, #VALOR! or # Nome?, você pode mostrar uma célula vazia ou sua própria mensagem. Você pode fazer isso colocando VPR em uma função SE ERRO (SEERRO) no Excel 2013, 2010 e 2007 ou use várias funções SE+ERRO (IF+ISERROR) em versões anteriores.
PROCV: trabalhando com a função SEERRO
Sintaxe da função SE ERRO (SEERRO) é simples e fala por si:
IFERROR(value,value_if_error)
ЕСЛИОШИБКА(значение;значение_если_ошибка)
Ou seja, para o primeiro argumento você insere o valor a ser verificado quanto a um erro e para o segundo argumento você especifica o que retornar se um erro for encontrado.
Por exemplo, esta fórmula retorna uma célula vazia se o valor que você está procurando não for encontrado:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"")
Se você deseja exibir sua própria mensagem em vez da mensagem de erro padrão da função VPR, coloque-o entre aspas, assim:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"Ничего не найдено. Попробуйте еще раз!")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"Ничего не найдено. Попробуйте еще раз!")
PROCV: trabalhando com a função ISERROR
Desde a função SE ERRO apareceu no Excel 2007, ao trabalhar em versões anteriores, você terá que usar a combinação IF (SE) e EOSHIBKA (ISERRO) assim:
=IF(ISERROR(VLOOKUP формула),"Ваше сообщение при ошибке",VLOOKUP формула)
=ЕСЛИ(ЕОШИБКА(ВПР формула);"Ваше сообщение при ошибке";ВПР формула)
Por exemplo, a fórmula SE+ERRO+PROCV, semelhante à fórmula SEERRO+PROCVMostrado acima:
=IF(ISERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE)),"",VLOOKUP($F$2,$B$2:$C$10,2,FALSE))
=ЕСЛИ(ЕОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ));"";ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ))
Isso é tudo por hoje. Espero que este pequeno tutorial o ajude a lidar com todos os erros possíveis. VPR e fazer suas fórmulas funcionarem corretamente.