Conteúdo
Na segunda parte do nosso tutorial sobre a função VPR (PROCV) no Excel, analisaremos alguns exemplos que o ajudarão a direcionar todo o poder VPR para resolver as tarefas mais ambiciosas do Excel. Os exemplos pressupõem que você já tenha um conhecimento básico de como esse recurso funciona. Caso contrário, você pode estar interessado em começar com a primeira parte deste tutorial, que explica a sintaxe e o uso básico. VPR. Bem, vamos começar.
Pesquise no Excel por vários critérios
função VPR no Excel é uma ferramenta realmente poderosa para realizar pesquisas por um determinado valor em um banco de dados. No entanto, há uma limitação significativa – sua sintaxe permite pesquisar apenas um valor. E se você quiser pesquisar por várias condições? Você encontrará a solução abaixo.
Exemplo 1: Pesquisa por 2 critérios diferentes
Suponha que temos uma lista de pedidos e queremos encontrar Quantidade de mercadorias (Qtd.), com base em dois critérios - Nome do cliente (Cliente) e Название продукта (Produtos). A questão se complica pelo fato de cada um dos compradores ter encomendado diversos tipos de mercadorias, como pode ser visto na tabela abaixo:
função normal VPR não funcionará neste cenário porque retornará o primeiro valor encontrado que corresponda ao valor de pesquisa fornecido. Por exemplo, se você quiser saber a quantidade de um item Doces'encomendado pelo comprador Jeremy Hill, escreva a seguinte fórmula:
=VLOOKUP(B1,$A$5:$C$14,3,FALSE)
=ВПР(B1;$A$5:$C$14;3;ЛОЖЬ)
– esta fórmula retornará o resultado 15correspondente ao produto Maçãs, porque é o primeiro valor que corresponde.
Existe uma solução simples – crie uma coluna adicional para combinar todos os critérios desejados. No nosso exemplo, estas são as colunas Nome do cliente (Cliente) e Название продукта (Produtos). Não se esqueça que a coluna mesclada deve ser sempre a coluna mais à esquerda do intervalo de pesquisa, pois é na coluna da esquerda que a função VPR olha para cima quando procura um valor.
Então, você adiciona uma coluna auxiliar à tabela e copia a seguinte fórmula sobre todas as suas células: =B2&C2. Se quiser que a string fique mais legível, você pode separar os valores combinados com um espaço: =B2&» «&C2. Depois disso, você pode usar a seguinte fórmula:
=VLOOKUP("Jeremy Hill Sweets",$A$7:$D$18,4,FALSE)
=ВПР("Jeremy Hill Sweets";$A$7:$D$18;4;ЛОЖЬ)
or
=VLOOKUP(B1,$A$7:$D$18,4,FALSE)
=ВПР(B1;$A$7:$D$18;4;ЛОЖЬ)
Onde está a célula B1 contém o valor concatenado do argumento lookup_value (valor_procurado) e 4 – Argumento col_index_num (column_number), ou seja, o número da coluna que contém os dados a serem recuperados.
Exemplo 2: VLOOKUP por dois critérios com tabela sendo visualizada em outra planilha
Se precisar atualizar a tabela principal (tabela principal) adicionando dados da segunda tabela (tabela de pesquisa), que está localizada em outra planilha ou em outra pasta de trabalho do Excel, você pode coletar o valor desejado diretamente na fórmula que você insere na tabela principal.
Assim como no exemplo anterior, você precisará de uma coluna auxiliar na tabela Lookup com os valores combinados. Esta coluna deve ser a coluna mais à esquerda no intervalo de pesquisa.
Então a fórmula com VPR poderia ser assim:
=VLOOKUP(B2&" "&C2,Orders!$A&$2:$D$2,4,FALSE)
=ВПР(B2&" "&C2;Orders!$A&$2:$D$2;4;ЛОЖЬ)
Aqui, as colunas B e C contêm nomes de clientes e nomes de produtos, respectivamente, e o link Pedidos!$A&$2:$D$2 define uma tabela para consultar em outra planilha.
Para tornar a fórmula mais legível, você pode dar um nome ao intervalo de visualização e então a fórmula parecerá muito mais simples:
=VLOOKUP(B2&" "&C2,Orders,4,FALSE)
=ВПР(B2&" "&C2;Orders;4;ЛОЖЬ)
Para que a fórmula funcione, os valores da coluna mais à esquerda da tabela que você está visualizando devem ser combinados exatamente da mesma forma que nos critérios de pesquisa. Na figura acima, combinamos os valores u2bu2 e colocamos um espaço entre eles, da mesma forma que você precisa fazer no primeiro argumento da função (BXNUMX& “” & CXNUMX).
Lembrar! função VPR limitado a 255 caracteres, ele não pode pesquisar um valor com mais de 255 caracteres. Tenha isso em mente e certifique-se de que o comprimento do valor desejado não exceda esse limite.
Concordo que adicionar uma coluna auxiliar não é a solução mais elegante e nem sempre aceitável. Você pode fazer o mesmo sem a coluna auxiliar, mas isso exigiria uma fórmula muito mais complexa com uma combinação de funções ÍNDICE (ÍNDICE) e JOGO (MAIS EXPOSTOS).
Extraímos o 2º, 3º, etc. valores usando VLOOKUP
Você já sabe disso VPR pode retornar apenas um valor correspondente, mais precisamente, o primeiro encontrado. Mas e se esse valor for repetido várias vezes no array visualizado e você quiser extrair o 2º ou o 3º deles? E se todos os valores? O problema parece complicado, mas a solução existe!
Suponha que uma coluna da tabela contenha os nomes dos clientes (Nome do Cliente) e a outra coluna contenha os produtos (Produto) que eles compraram. Vamos tentar encontrar o 2º, 3º e 4º itens adquiridos por um determinado cliente.
A maneira mais fácil é adicionar uma coluna auxiliar antes da coluna nome do cliente e preencha com os nomes dos clientes com o número de repetição de cada nome, por exemplo, John Doe1, John Doe2 Faremos o truque com a numeração usando a função COUNTIF (CONT.SE), visto que os nomes dos clientes estão na coluna B:
=B2&COUNTIF($B$2:B2,B2)
=B2&СЧЁТЕСЛИ($B$2:B2;B2)
Depois disso você pode usar a função normal VPRpara encontrar o pedido necessário. Por exemplo:
- Encontre 2 º item encomendado pelo cliente Dan Brown:
=VLOOKUP("Dan Brown2",$A$2:$C$16,3,FALSE)=ВПР("Dan Brown2";$A$2:$C$16;3;ЛОЖЬ) - Encontre 3 º item encomendado pelo cliente Dan Brown:
=VLOOKUP("Dan Brown3",$A$2:$C$16,3,FALSE)=ВПР("Dan Brown3";$A$2:$C$16;3;ЛОЖЬ)
Na verdade, você pode inserir uma referência de célula como valor de pesquisa em vez de texto, conforme mostrado na figura a seguir:
Se você está apenas procurando 2-e repetição, você pode fazer isso sem a coluna auxiliar criando uma fórmula mais complexa:
=IFERROR(VLOOKUP($F$2,INDIRECT("$B$"&(MATCH($F$2,Table4[Customer Name],0)+2)&":$C16"),2,FALSE),"")
=ЕСЛИОШИБКА(ВПР($F$2;ДВССЫЛ("$B$"&(ПОИСКПОЗ($F$2;Table4[Customer Name];0)+2)&":$C16");2;ИСТИНА);"")
Nesta fórmula:
- $ F $ 2 – uma célula contendo o nome do comprador (permanece inalterado, observe – o link é absoluto);
- $B$ – coluna nome do cliente;
- Table4 – A sua mesa (este local também pode ser um intervalo regular);
- $ C16 – a célula final da sua tabela ou intervalo.
Esta fórmula encontra apenas o segundo valor correspondente. Se precisar extrair as repetições restantes, use a solução anterior.
Se você precisar de uma lista de todas as correspondências – a função VPR isso não é um auxiliar, pois retorna apenas um valor por vez – período. Mas o Excel tem uma função ÍNDICE (INDEX), que pode facilmente lidar com esta tarefa. Como será essa fórmula, você aprenderá no exemplo a seguir.
Recuperar todas as repetições do valor desejado
Como acima mencionado VPR não é possível extrair todos os valores duplicados do intervalo verificado. Para isso, é necessária uma fórmula um pouco mais complexa, composta por diversas funções do Excel, como ÍNDICE (ÍNDICE), SMALL (PEQUENO) e LINHA (LINHA)
Por exemplo, a fórmula abaixo encontra todas as repetições do valor da célula F2 no intervalo B2:B16 e retorna o resultado das mesmas linhas na coluna C.
{=IFERROR(INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3)),"")}
{=ЕСЛИОШИБКА(ИНДЕКС($C$2:$C$16;НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3));"")}
Insira esta fórmula de matriz em várias células adjacentes, como as células F4: F8conforme mostrado na figura abaixo. O número de células deve ser igual ou superior ao número máximo possível de repetições do valor pesquisado. Não se esqueça de clicar Ctrl + Shift + Enterpara inserir a fórmula de matriz corretamente.
Se você estiver interessado em entender como funciona, vamos nos aprofundar um pouco nos detalhes da fórmula:
1 parte:
IF($F$2=B2:B16,ROW(C2:C16)-1,"")
ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"")
$F$2=B2:B16 – compare o valor na célula F2 com cada um dos valores do intervalo B2:B16. Se uma correspondência for encontrada, então a expressão CADEIA(C2:C16)-1 retorna o número da linha correspondente (valor -1 permite que você não inclua a linha do cabeçalho). Se não houver correspondências, a função IF (IF) retorna uma string vazia.
Resultado da função IF (SE) haverá uma matriz horizontal: {1,"",3,"",5,"","","","","","",12,"","",""}
2 parte:
ROW()-3
СТРОКА()-3
Aqui a função LINHA (LINE) atua como um contador adicional. Como a fórmula é copiada nas células F4:F9, subtraímos o número 3 do resultado da função para obter o valor 1 na cela F4 (linha 4, subtraia 3) para obter 2 na cela F5 (linha 5, subtraia 3) e assim por diante.
3 parte:
SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))
НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3))
função SMALL (PEQUENO) retorna não-oh o menor valor na matriz de dados. No nosso caso, qual posição (da menor) retornar é determinada pela função LINHA (LINHA) (ver Parte 2). Então, para uma célula F4 função PEQUENO({matriz},1) Retorna 1 º (menor) elemento da matriz, ou seja, 1. Para célula F5 Retorna 2 º o menor elemento da matriz, ou seja 3, etc.
4 parte:
INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))
ИНДЕКС($C$2:$C$16;НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3))
função ÍNDICE (INDEX) simplesmente retorna o valor de uma célula específica em uma matriz C2:C16. Para célula F4 função ÍNDICE($C$2:$C$16) retornará MaçãsEscolha F5 função ÍNDICE($C$2:$C$16) retornará Doces' e assim por diante.
5 parte:
IFERROR()
ЕСЛИОШИБКА()
Finalmente, colocamos a fórmula dentro da função SE ERRO (IFERROR), porque é improvável que você fique satisfeito com a mensagem de erro #NO (#N/A) se o número de células nas quais a fórmula é copiada for menor que o número de valores duplicados no intervalo que está sendo visualizado.
Pesquisa XNUMXD por linha e coluna conhecidas
Realizar uma pesquisa XNUMXD no Excel envolve pesquisar um valor por um número conhecido de linha e coluna. Em outras palavras, você está extraindo o valor da célula na interseção de uma linha e coluna específicas.
Então, vamos voltar para nossa tabela e escrever uma fórmula com uma função VPR, que encontrará informações sobre o custo dos limões vendidos em março.
Existem várias maneiras de realizar uma pesquisa XNUMXD. Confira as opções e escolha a que mais combina com você.
Funções VLOOKUP e MATCH
Você pode usar várias funções VPR (VLOOKUP) e MAIS EXPOSTOS (MATCH) para encontrar o valor na interseção dos campos Название продукта (string) e Mês (coluna) do array em questão:
=VLOOKUP("Lemons",$A$2:$I$9,MATCH("Mar",$A$1:$I$1,0),FALSE)
=ВПР("Lemons";$A$2:$I$9;ПОИСКПОЗ("Mar";$A$1:$I$1;0);ЛОЖЬ)
A fórmula acima é uma função regular VPR, que procura uma correspondência exata do valor “Lemons” nas células A2 a A9. Mas como você não sabe em qual coluna estão as vendas de março, não será possível definir o número da coluna para o terceiro argumento da função. VPR. Em vez disso, a função é usada MAIS EXPOSTOSpara definir esta coluna.
MATCH("Mar",$A$1:$I$1,0)
ПОИСКПОЗ("Mar";$A$1:$I$1;0)
Traduzido para a linguagem humana, esta fórmula significa:
- Procuramos os personagens “Mar” – argumento lookup_value (valor_procura);
- Olhando nas células de A1 a I1 – argumento lookup_array (procura_matriz);
- Retornando correspondência exata – argumento tipo de partida (tipo de partida).
utilização 0 no terceiro argumento, você diz funções MAIS EXPOSTOS procure o primeiro valor que corresponda exatamente ao valor que você está procurando. Isto é equivalente ao valor FALSE (FALSO) para o quarto argumento VPR.
É assim que você pode criar uma fórmula de pesquisa bidirecional no Excel, também conhecida como pesquisa bidimensional ou pesquisa bidirecional.
Função SOMAPRODUTO
função SUMPRODUCT (SUMPRODUCT) retorna a soma dos produtos das matrizes selecionadas:
=SUMPRODUCT(($A$2:$A$9="Lemons")*($A$1:$I$1="Mar"),$A$2:$I$9)
=СУММПРОИЗВ(($A$2:$A$9="Lemons")*($A$1:$I$1="Mar");$A$2:$I$9)
Funções INDEX e MATCH
No próximo artigo explicarei essas funções em detalhes, então por enquanto você pode apenas copiar esta fórmula:
=INDEX($A$2:$I$9,MATCH("Lemons",$A$2:$A$9,0),MATCH("Mar",$A$1:$I$1,0))
=ИНДЕКС($A$2:$I$9;ПОИСКПОЗ("Lemons";$A$2:$A$9;0);ПОИСКПОЗ("Mar";$A$1:$I$1;0))
Intervalos nomeados e o operador de interseção
Se você não gosta de todas aquelas fórmulas complexas do Excel, talvez goste desta forma visual e memorável:
- Selecione a tabela, abra a guia Fórmulas (Fórmulas) e clique em Criar a partir da seleção (Criar a partir da seleção).
- Marque as caixas Linha superior (na linha acima) e Coluna esquerda (na coluna à esquerda). O Microsoft Excel atribuirá nomes aos intervalos dos valores na linha superior e na coluna esquerda da sua planilha. Agora você pode pesquisar usando esses nomes diretamente, sem criar fórmulas.
- Em qualquer célula vazia, escreva =nome_linha nome_coluna, por exemplo assim:
= Limões março
… ou vice-versa:
=Mar Limões
Lembre-se que os nomes das linhas e colunas devem ser separados por um espaço, que neste caso funciona como o operador de interseção.
Quando você insere um nome, o Microsoft Excel mostra uma dica com uma lista de nomes correspondentes, assim como quando você insere uma fórmula.
- Press Entrar e confira o resultado
Em geral, qualquer que seja o método acima escolhido, o resultado de uma pesquisa bidimensional será o mesmo:
Usando vários VLOOKUPs em uma fórmula
Acontece que a tabela principal e a tabela de consulta não possuem uma única coluna em comum, e isso impede que você use a função usual VPR. Porém, existe outra tabela que não contém as informações que nos interessam, mas possui uma coluna comum com a tabela principal e a tabela de consulta.
Vejamos o exemplo a seguir. Temos uma tabela principal com uma coluna SKU (novo), onde deseja adicionar uma coluna com os preços correspondentes de outra tabela. Além disso, temos 2 tabelas de pesquisa. O primeiro (tabela de consulta 1) contém números atualizados SKU (novo) e nomes de produtos, e o segundo (Tabela de consulta 2) – nomes de produtos e números antigos SKU (antigo).
Para adicionar preços da segunda tabela de consulta à tabela principal, você deve realizar uma ação conhecida como double VPR ou aninhado VPR.
- Escreva uma função VPR, que encontra o nome do produto na tabela Tabela de pesquisa 1utilização SKU, como o valor desejado:
=VLOOKUP(A2,New_SKU,2,FALSE)=ВПР(A2;New_SKU;2;ЛОЖЬ)Aqui Novo_SKU – intervalo nomeado $A:$B na mesa Tabela de pesquisa 1, 2 – esta é a coluna B, que contém os nomes das mercadorias (veja a imagem acima)
- Escreva uma fórmula para inserir preços de uma tabela Tabela de pesquisa 2 com base em nomes de produtos conhecidos. Para fazer isso, cole a fórmula que você criou anteriormente como valor de pesquisa para a nova função VPR:
=VLOOKUP(VLOOKUP(A2,New_SKU,2,FALSE),Price,3,FALSE)=ВПР(ВПР(A2;New_SKU;2;ЛОЖЬ);Price;3;ЛОЖЬ)Aqui Preço – intervalo nomeado $ A: $ C na mesa Tabela de pesquisa 2, 3 é a coluna C contendo preços.
A figura abaixo mostra o resultado retornado pela fórmula que criamos:
Substituição dinâmica de dados de diferentes tabelas usando VLOOKUP e INDIRETO
Primeiro, vamos esclarecer o que queremos dizer com a expressão “Substituição dinâmica de dados de tabelas diferentes” para ter certeza de que nos entendemos corretamente.
Existem situações em que existem várias planilhas com dados do mesmo formato, sendo necessário extrair as informações necessárias de uma determinada planilha, dependendo do valor que está inserido em uma determinada célula. Acho que é mais fácil explicar isso com um exemplo.
Imagine que você possui relatórios de vendas para diversas regiões com os mesmos produtos e no mesmo formato. Você deseja encontrar números de vendas para uma região específica:
Se você tiver apenas dois desses relatórios, poderá usar uma fórmula vergonhosamente simples com funções VPR и IF (SE) para selecionar o relatório desejado para pesquisar:
=VLOOKUP($D$2,IF($D3="FL",FL_Sales,CA_Sales),2,FALSE)
=ВПР($D$2;ЕСЛИ($D3="FL";FL_Sales;CA_Sales);2;ЛОЖЬ)
Onde:
- $D$2 é uma célula contendo o nome do produto. Observe que usamos referências absolutas aqui para evitar alterar o valor de pesquisa ao copiar a fórmula para outras células.
- $ D3 é uma célula com o nome da região. Estamos usando uma referência de coluna absoluta e uma referência de linha relativa porque planejamos copiar a fórmula para outras células na mesma coluna.
- FL_Sales и CA_Vendas – os nomes das tabelas (ou intervalos nomeados) que contêm os relatórios de vendas correspondentes. Você pode, é claro, usar os nomes usuais de planilhas e referências de intervalos de células, por exemplo ‘Folha FL’!$A$3:$B$10, mas os intervalos nomeados são muito mais convenientes.
No entanto, quando existem muitas dessas tabelas, a função IF não é a melhor solução. Em vez disso, você pode usar a função INDIRETO (INDIRETO) para retornar o intervalo de pesquisa desejado.
Como você provavelmente sabe, a função INDIRETO é usado para retornar um link fornecido por uma string de texto, que é exatamente o que precisamos agora. Portanto, sinta-se à vontade para substituir na fórmula acima a expressão pela função IF vincular com a função INDIRETO. Aqui está uma combinação VPR и INDIRETO funciona muito bem com:
=VLOOKUP($D$2,INDIRECT($D3&"_Sales"),2,FALSE)
=ВПР($D$2;ДВССЫЛ($D3&"_Sales");2;ЛОЖЬ)
Onde:
- $D$2 – esta é uma célula com o nome do produto, permanece inalterada devido ao link absoluto.
- $ D3 é a célula que contém a primeira parte do nome da região. Em nosso exemplo, isso FL.
- _Vendas – a parte comum do nome de todos os intervalos ou tabelas nomeados. Quando combinado com o valor na célula D3, forma o nome totalmente qualificado do intervalo necessário. Abaixo estão alguns detalhes para quem é novo na função INDIRETO.
Como funcionam o INDIRETO e o VLOOKUP
Primeiro, deixe-me lembrá-lo da sintaxe da função INDIRETO (INDIRETO):
INDIRECT(ref_text,[a1])
ДВССЫЛ(ссылка_на_текст;[a1])
O primeiro argumento pode ser uma referência de célula (estilo A1 ou R1C1), um nome de intervalo ou uma sequência de texto. O segundo argumento determina qual estilo de link está contido no primeiro argumento:
- A1se o argumento é CÓDIGO VERDADEIRO (VERDADEIRO) ou não especificado;
- R1C1, Se FCOMO E (FALSO).
No nosso caso, o link tem o estilo A1, então você pode deixar de lado o segundo argumento e focar no primeiro.
Então, vamos voltar aos nossos relatórios de vendas. Se você se lembra, cada relatório é uma tabela separada localizada em uma folha separada. Para que a fórmula funcione corretamente, você deve nomear suas tabelas (ou intervalos), e todos os nomes devem ter uma parte comum. Por exemplo, assim: CA_Vendas, FL_Vendas, TX_Vendas e assim por diante. Como você pode ver, “_Sales” está presente em todos os nomes.
função INDIRETO conecta o valor na coluna D e a string de texto “_Sales”, informando assim VPR em qual tabela pesquisar. Se a célula D3 contiver o valor “FL”, a fórmula irá pesquisar na tabela FL_Vendas, se “CA” – na tabela CA_Vendas e assim por diante.
O resultado das funções VPR и INDIRETO será o seguinte:
Se os dados estiverem localizados em livros Excel diferentes, será necessário adicionar o nome do livro antes do intervalo nomeado, por exemplo:
=VLOOKUP($D$2,INDIRECT($D3&"Workbook1!_Sales"),2,FALSE)
=ВПР($D$2;ДВССЫЛ($D3&"Workbook1!_Sales");2;ЛОЖЬ)
Se a função INDIRETO refere-se a outra pasta de trabalho, essa pasta de trabalho deve estar aberta. Se estiver fechado, a função reportará um erro. # Ref! (#SSIL!).