Conteúdo
- A função PROCV diferencia maiúsculas de minúsculas
- Função LOOKUP para pesquisa com distinção entre maiúsculas e minúsculas
- SUMPRODUCT – encontra valores de texto, diferencia maiúsculas de minúsculas, mas retorna apenas números
- INDEX + MATCH – pesquisa sensível a maiúsculas e minúsculas para qualquer tipo de dados
Este pequeno tutorial explica como fazer uma função VPR (VLOOKUP) diferencia maiúsculas de minúsculas, mostra várias outras fórmulas que o Excel pode pesquisar com distinção entre maiúsculas e minúsculas e aponta os pontos fortes e fracos de cada função.
Acho que todo usuário do Excel sabe qual função executa a pesquisa vertical. Isso mesmo, é uma função VPR. No entanto, poucas pessoas sabem que VPR não diferencia maiúsculas de minúsculas, ou seja, os caracteres minúsculos e maiúsculos são idênticos para ele.
Aqui está um exemplo rápido demonstrando a incapacidade VPR reconhecer registro. Suponha que em uma célula A1 contém o valor “bill” e a célula A2 – “Bill”, fórmula:
=VLOOKUP("Bill",A1:A10,2)
=ВПР("Bill";A1:A10;2)
… irá parar sua busca por “bill” já que esse valor vem primeiro na lista, e extrairá o valor da célula B1.
Mais adiante neste artigo, mostrarei como fazer VPR maiúsculas e minúsculas. Além disso, aprenderemos mais algumas funções que podem realizar pesquisas que diferenciam maiúsculas de minúsculas no Excel.
Vamos começar com o mais simples – VEJA (PESQUISA) e SUMPRODUCT (SUMPRODUCT), que, infelizmente, tem várias limitações significativas. A seguir, examinaremos mais de perto a fórmula um pouco mais complexa ÍNDICE + CORRESP (INDEX+MATCH), que funciona perfeitamente em qualquer situação e com qualquer conjunto de dados.
A função PROCV diferencia maiúsculas de minúsculas
Como você já sabe, a função usual VPR não diferencia maiúsculas de minúsculas. No entanto, existe uma maneira de torná-lo sensível a maiúsculas. Para fazer isso, você precisa adicionar uma coluna auxiliar à tabela, conforme mostrado no exemplo a seguir.
Suponha em uma coluna B existem identificadores de produto (Item) e você deseja extrair o preço do produto e o comentário correspondente das colunas C и D. O problema é que os identificadores contêm caracteres minúsculos e maiúsculos. Por exemplo, valores de células B4 (001Tvci3u) e B5 (001Tvci3U) diferem apenas no caso do último caractere, u и U respectivamente.
Como você pode imaginar, a fórmula de pesquisa usual
=VLOOKUP("001Tvci3U",$A$2:$C$7,2,FALSE)
=ВПР("001Tvci3U";$A$2:$C$7;2;ЛОЖЬ)
retornará $ 90, uma vez que o valor 001Tvci3u está no intervalo de pesquisa antes de 001Tvci3U. Mas não é disso que precisamos, não é?
Para pesquisar com uma função VPR no Excel diferenciando maiúsculas de minúsculas, você terá que adicionar uma coluna auxiliar e preencher suas células com a seguinte fórmula (onde B é a coluna de pesquisa):
=CODE(MID(B2,1,1)) & CODE(MID(B2,2,1)) & CODE(MID(B2,3,1)) & CODE(MID(B2,4,1)) & CODE(MID(B2,5,1)) & CODE(MID(B2,6,1)) & CODE(MID(B2,7,1)) & CODE(MID(B2,8,1)) & IFERROR(CODE(MID(B2,9,1)),"")
=КОДСИМВ(ПСТР(B2;1;1)) & КОДСИМВ(ПСТР(B2;2;1)) & КОДСИМВ(ПСТР(B2;3;1)) & КОДСИМВ(ПСТР(B2;4;1)) & КОДСИМВ(ПСТР(B2;5;1)) & КОДСИМВ(ПСТР(B2;6;1)) & КОДСИМВ(ПСТР(B2;7;1)) & КОДСИМВ(ПСТР(B2;8;1)) & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;9;1));"")
Esta fórmula divide o valor desejado em caracteres separados, substitui cada caractere por seu código (por exemplo, em vez de A aos 65, em vez a código 97) e, em seguida, combina esses códigos em uma sequência única de números.
Depois disso, usamos uma função simples VPR para pesquisa com distinção entre maiúsculas e minúsculas:
=VLOOKUP($G$3,$A$2:$C$8,3,FALSE)
=ВПР($G$3;$A$2:$C$8;3;ЛОЖЬ)
Funcionamento correto da função VPR A diferenciação entre maiúsculas e minúsculas depende de dois fatores:
- A coluna auxiliar deve ser a coluna mais à esquerda no intervalo visível.
- O valor que você está procurando deve conter um código de caractere em vez do valor real.
Como usar a função CODE corretamente
A fórmula inserida nas células da coluna auxiliar pressupõe que todos os seus valores de pesquisa tenham o mesmo número de caracteres. Se não, então você precisa conhecer os números menores e maiores e adicionar tantos recursos SE ERRO (SEERRO) quantos caracteres é a diferença entre o valor pesquisado mais curto e mais longo.
Por exemplo, se o valor de pesquisa mais curto tiver 3 caracteres e o mais longo tiver 5 caracteres, use esta fórmula:
=CODE(MID(B2,1,1)) & CODE(MID(B2,2,1)) & CODE(MID(B2,3,1)) & IFERROR(CODE(MID(B2,3,1)),"") & IFERROR(CODE(MID(B2,4,1)),"")
=КОДСИМВ(ПСТР(B2;1;1)) & КОДСИМВ(ПСТР(B2;2;1)) & КОДСИМВ(ПСТР(B2;3;1)) & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;3;1));"") & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;4;1));"")
Para função PSTR (MID) Você fornece os seguintes argumentos:
- 1º argumento – texto (texto) é o texto ou referência de célula que contém os caracteres a serem extraídos (no nosso caso é B2)
- 2º argumento – número_inicial (start_position) é a posição do primeiro desses caracteres a serem extraídos. entre 1 na primeira função PSTR, 2 – na segunda função PSTR etc.
- 3º argumento – num_chars (number_of_characters) – especifica o número de caracteres a serem extraídos do texto. Como só precisamos de 1 caractere o tempo todo, em todas as funções escrevemos 1.
LIMITAÇÕES: função VPR não é a melhor solução para pesquisas que diferenciam maiúsculas de minúsculas no Excel. Primeiro, é necessária a adição de uma coluna auxiliar. Em segundo lugar, a fórmula faz um bom trabalho apenas se os dados forem homogêneos ou se o número exato de caracteres nos valores pesquisados for conhecido. Se este não for o seu caso, é melhor usar uma das soluções que mostramos abaixo.
Função LOOKUP para pesquisa com distinção entre maiúsculas e minúsculas
função VEJA (LOOKUP) relacionado VPR, porém sua sintaxe permite pesquisas que diferenciam maiúsculas de minúsculas sem adicionar uma coluna auxiliar. Para fazer isso, use VEJA combinado com a função EXATO (EXATO).
Se pegarmos os dados do exemplo anterior (sem uma coluna auxiliar), a seguinte fórmula lidará com a tarefa:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
Pesquisas de fórmulas no intervalo A2: A7 correspondência exata com o valor da célula F2 diferencia maiúsculas de minúsculas e retorna o valor da coluna B da mesma linha.
Como VPRfunção VEJA funciona igualmente com valores de texto e numéricos, como você pode ver na captura de tela abaixo:
Importante! Para a função VEJA funcionou corretamente, os valores na coluna de pesquisa devem ser ordenados em ordem crescente, ou seja, do menor para o maior.
Deixe-me explicar brevemente como a função funciona EXATO na fórmula mostrada acima, pois este é o ponto chave.
função EXATO compara os dois valores de texto no 1º e 2º argumentos e retorna TRUE se forem exatamente iguais, ou FALSE se não forem. É importante para nós que a função EXATO maiúsculas e Minúsculas.
Vamos ver como nossa fórmula funciona VER + EXATO:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
- função EXATO compara o valor da célula F2 com todos os elementos em uma coluna A (A2:A7). Retorna TRUE se uma correspondência exata for encontrada, caso contrário, FALSE.
- Como você fornece o primeiro argumento da função VEJA valor TRUE, ele extrai o valor correspondente da coluna especificada (no nosso caso, coluna B) somente se uma correspondência exata for encontrada, diferenciando maiúsculas de minúsculas.
Espero que esta explicação tenha sido clara e agora você entenda a ideia principal. Nesse caso, você não terá dificuldades com outras funções que analisaremos mais a fundo, porque. todos eles trabalham no mesmo princípio.
LIMITAÇÕES: Os dados na coluna de pesquisa devem ser classificados em ordem crescente.
SUMPRODUCT – encontra valores de texto, diferencia maiúsculas de minúsculas, mas retorna apenas números
Como você já entendeu do título, SUMPRODUCT (SUMPRODUCT) é outra função do Excel que o ajudará a fazer uma pesquisa com distinção entre maiúsculas e minúsculas, mas retornará apenas valores numéricos. Se esta opção não combina com você, você pode prosseguir imediatamente para o pacote ÍNDICE + CORRESP, que fornece uma solução para qualquer caso e para qualquer tipo de dados.
Primeiro, deixe-me explicar brevemente a sintaxe dessa função, isso ajudará você a entender melhor a fórmula que diferencia maiúsculas de minúsculas a seguir.
função SUMPRODUCT multiplica os elementos das matrizes fornecidas e retorna a soma dos resultados. A sintaxe fica assim:
SUMPRODUCT(array1,[array2],[array3],...)
СУММПРОИЗВ(массив1;[массив2];[массив3];…)
Como precisamos de uma pesquisa que diferencia maiúsculas de minúsculas, usamos a função EXATO (EXACTO) do exemplo anterior como um dos multiplicadores:
=SUMPRODUCT((EXACT($A$2:$A$7,$F$2)*($B$2:$B$7)))
=СУММПРОИЗВ((СОВПАД($A$2:$A$7;$F$2)*($B$2:$B$7)))
Como você se lembra, EXATO compara o valor da célula F2 com todos os elementos em uma coluna A. Retorna TRUE se uma correspondência exata for encontrada, caso contrário, FALSE. Em operações matemáticas, o Excel assume TRUE como 1, e FALSO para 0Mais SUMPRODUCT multiplica esses números e soma os resultados.
Zeros não são contados porque quando multiplicados sempre dão 0. Vamos dar uma olhada no que acontece quando uma correspondência exata em uma coluna A encontrado e devolvido 1… Função SUMPRODUCT multiplica o número na coluna B on 1 e retorna o resultado – exatamente o mesmo número! Isso ocorre porque os resultados dos outros produtos são zero e não afetam a soma resultante.
Infelizmente a função SUMPRODUCT não pode trabalhar com valores de texto e datas, pois não podem ser multiplicados. Neste caso, você receberá uma mensagem de erro #VALOR! (#VALOR!) como em uma célula F4 na imagem abaixo:
LIMITAÇÕES: Retorna apenas valores numéricos.
INDEX + MATCH – pesquisa sensível a maiúsculas e minúsculas para qualquer tipo de dados
Por fim, estamos perto de uma fórmula de pesquisa ilimitada e sensível a maiúsculas e minúsculas que funciona com qualquer conjunto de dados.
Este exemplo vem por último, não porque o melhor é deixado para a sobremesa, mas porque o conhecimento adquirido com os exemplos anteriores o ajudará a entender melhor e mais rápido a fórmula que diferencia maiúsculas de minúsculas. ÍNDICE + CORRESP (ÍNDICE + CORRESP).
Como você provavelmente adivinhou, a combinação de funções MAIS EXPOSTOS и ÍNDICE usado no Excel como uma alternativa mais flexível e poderosa para VPR. O artigo Usando INDEX e MATCH em vez de VLOOKUP explicará perfeitamente como essas funções funcionam juntas.
Vou apenas recapitular os pontos principais:
- função MAIS EXPOSTOS (MATCH) procura um valor em um determinado intervalo e retorna sua posição relativa, ou seja, o número da linha e/ou coluna;
- A seguir, a função ÍNDICE (INDEX) retorna um valor de uma coluna e/ou linha especificada.
Para formular ÍNDICE + CORRESP pode pesquisar com distinção entre maiúsculas e minúsculas, você só precisa adicionar uma função a ele. Não é difícil adivinhar o que é novamente EXATO (EXATO):
=INDEX($B$2:$B$7,MATCH(TRUE,EXACT($A$2:$A$7,$F$2),0))
=ИНДЕКС($B$2:$B$7;ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);0))
Nesta fórmula EXATO funciona da mesma forma que em conjunto com a função VEJA, e dá o mesmo resultado:
Observe que a fórmula ÍNDICE + CORRESP entre chaves é uma fórmula de matriz e você deve completá-la pressionando Ctrl + Shift + Enter.
Por que o INDEX+MATCH é a melhor solução para pesquisa com distinção entre maiúsculas e minúsculas?
As principais vantagens do pacote ÍNDICE и MAIS EXPOSTOS:
- Não requer a adição de uma coluna auxiliar, ao contrário VPR.
- Não requer que a coluna de pesquisa seja classificada, ao contrário VEJA.
- Funciona com todos os tipos de dados – números, texto e datas.
Esta fórmula parece perfeita, não é? Na verdade, não é. E é por isso.
Suponha que a célula na coluna de valor de retorno associada ao valor de pesquisa esteja vazia. Que resultado a fórmula retornará? Não? Vamos ver o que a fórmula realmente retorna:
Ops, a fórmula retorna zero! Isso pode não ser um grande problema se você estiver trabalhando com valores de texto puro. No entanto, se a tabela contiver números, incluindo zeros “reais”, isso se tornará um problema.
Na verdade, todas as outras fórmulas de pesquisa (VLOOKUP, LOOKUP e SUMPRODUCT) que discutimos anteriormente se comportam da mesma maneira. Mas você quer a fórmula perfeita, certo?
Para tornar uma fórmula sensível a maiúsculas e minúsculas ÍNDICE + CORRESP perfeito, coloque em uma função IF (IF) que testará uma célula com um valor de retorno e retornará um resultado vazio se estiver vazio:
=IF(INDIRECT("B"&(1+MATCH(TRUE,EXACT($A$2:$A$7,$G$2),0)))<>"",INDEX($B$2:$B$7, MATCH(TRUE,EXACT($A$2:$A$7,$G$2),0)),"")
=ЕСЛИ(ДВССЫЛ("B"&(1+ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$G$2);0)))<>"";ИНДЕКС($B$2:$B$7; ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$G$2);0));"")
Nesta fórmula:
- B é uma coluna com valores de retorno
- 1+ é um número que transforma a posição relativa da célula retornada pela função MAIS EXPOSTOS, para o endereço real da célula. Por exemplo, em nossa função MAIS EXPOSTOS matriz de pesquisa fornecida A2: A7, ou seja, a posição relativa da célula A2 precisarão 1, porque é o primeiro na matriz. Mas a posição real da célula A2 na coluna é 2, então adicionamos 1fazer a diferença e ter a função INDIRETO (INDIRETO) recuperou o valor da célula desejada.
As imagens abaixo mostram a fórmula sensível a maiúsculas e minúsculas corrigida ÍNDICE + CORRESP Em ação. Ele retorna um resultado vazio se a célula retornada estiver vazia.
Eu reescrevi a fórmula em colunas B:Dpara ajustar a barra de fórmulas na captura de tela.
Fórmula retorna 0se a célula retornada contiver zero.
Se você quiser o link ÍNDICE и MAIS EXPOSTOS exibido alguma mensagem quando o valor de retorno está vazio, você pode escrevê-lo nas últimas aspas (“”) da fórmula, por exemplo, assim:
=IF(INDIRECT("D"&(1+MATCH(TRUE,EXACT($B$2:$B$7,$G$2),0)))<>"",INDEX($D$2:$D$7, MATCH(TRUE,EXACT($B$2:$B$7,$G$2),0)),"There is nothing to return, sorry.")
=ЕСЛИ(ДВССЫЛ("D"&(1+ПОИСКПОЗ(ИСТИНА;СОВПАД($B$2:$B$7;$G$2);0)))<>"";ИНДЕКС($D$2:$D$7; ПОИСКПОЗ(ИСТИНА;СОВПАД($B$2:$B$7;$G$2);0));"There is nothing to return, sorry.")
