Comando SubTotal Excel
Comando SubTotal Excel
11 de dezembro de 2010
Filtro com Lista de Dados no Excel
Filtro com Lista de Dados no Excel
19 de dezembro de 2010

Objetivo: Realizar a busca dos N valores correspondentes de uma lista de valores utilizando a função Procv para valores com mesmas chaves.

A função PROCV é muito útil para relacionarmos dados de planilhas, porém ele possui uma limitação, só traz a primeira correspondência que ele localiza na lista. Por isso a chave de busca deve ser sempre única. De que forma?

Siga o tutorial:

Primeiro você tem uma lista de valores de NFs com os seus respectivos produtos, e você quer saber quais são todos os produtos de cada nota fiscal. Note que as notas fiscais possuem mais do que um produto, ocorrendo o problema que queremos resolver.

Como falamos no início, o primeiro passo é criarmos uma chave única, para isso você pode usar a função =CONT.SE($C$1:C2;C2) que irá realizar a contagem de quantas vezes a Nota fiscal se repete do início da tabela até o a linha atual, criando desta forma um número sequencial.

Feito isso criamos na coluna A a chave concatenando estes dois campos e formando a chave. Para isso utiliza-se a fórmula: =C2&”-“&B2.

Agora vamos criar uma lista aonde temos os dados que queremos procurar, para isso copie a coluna C e cole em G3 por exemplo.

Em seguida selecione os dados da coluna G e clique na guia Dados->Remover Duplicatas, assim teremos uma lista com as notas fiscais únicas da planilha.

Na célula G2 digite NF e nas células H1 á L1 digite 1,2,3,4,5, sendo que 5 é o nosso limite de procura, imaginando que teríamos apenas 5 produtos no máximo por nota fiscal por exemplo, você terá a planilha conforme abaixo:

Na célula H3 digite a fórmula: =SEERRO(PROCV($G3&”-“&H$2;$A:$D;4;0);””) e arraste para todas as células da planilha, esta fórmula faz uma busca na coluna A que possui a chave dos dados pela chave que é concatenada ($G3&”-“&H$2)  exemplo: 26327-1, 26327-1, etc.

No download você terá a planilha com todas as fórmulas e o exemplo completo.

 

Esse artigo foi útil? Você pode se interessar também por: Como usar o PROCV no Excel

Abraço

Marcos Rieper


Clique aqui e leia mais sobre Excel VBA. https://www.guiadoexcel.com.br/vba/ O Guia do Excel foi criado por Marcos Rieper e oferece artigos, dicas, tutoriais e modelos de planilhas prontas. Aqui você encontra tudo sobre Excel, seja de nível básico, intermediário,  avançado e VBA. O Guia do Excel oferece diversos materiais completamente gratuitos para download. Navegue em nosso site e confira! Conheça também a nossa Loja do Excel https://loja.guiadoexcel.com.br/

36 Comentários

  1. Adriano disse:

    Marcos este é a primeira vez que entro no blog e já dou de cara com a solução do meu problema, parabéns pela blog, já add no favoritos.

  2. Raimundo disse:

    [b]Marcos,[/b] obrigado. Fiz uma longa procura na internet e aqui encontrei essa solução simples e prática para valores repetidos da função PROCV. Terei de adaptar para meu uso pois tenho duas planilhas para conciliar e o único elemento comum é o nome do cliente. De uma tenho de trazer o número do pedido e de outra o número de nota fiscal. Esse exemplo já foi um start para meus neurônios. Parabéns pelo material publicado – muito bom e didático.

  3. Koni disse:

    Adorei essa solução! É muito simples.
    Fiquei me complicando com matriz e SE, mas é muito simples mesmo.

    Obrigada!

  4. Filipa disse:

    Bom dia

    Como consigo que a função PROCV me devolva o segundo ou mais valores encontrados? Se houver dois ou mais valores na primeira coluna da matriz_tabela que correspondem ao valor_proc.

  5. John disse:

    Cara,
    muito obrigado, está formula foi perfeita para que eu está precisando!
    Este site é muito bom parabéns!

    Abraços!

    John

  6. Isaac disse:

    Muito bom…mas queria saber se tem como o PROCV procurar 2 valores e retornar 1. EX
    na coluna A e B tenho doi cód diferentes de um mesmo produto, na coluna A tem o cód antigo e na B tem o cód novo, preciso que o PROCV procure os dois e retorne o valor correspondente. tem como?

  7. VALTER disse:

    caro amigo é fantástico o guiado excel, está de parabéns, mas tenho um problema que já queimei a mufa desde quinta feira passada até esta hora que lhe escrevo não consigo realizar a PROCH em um trabalho que tenho que entregar, neste bimestre não a prova e sim este trabalho, estou lhe enviando por e-mail se puder me ajudar, lhe serei grato de mais.

  8. VALTER disse:

    Ola amigo, este ja havia visto este vídeo, entendi, mas o problema é o seguinte:

    se digito na célula: =proch(b$6;supervisores!1:801;a15;0)

    a tabela supervisores tem vario com o cosigo ( B$6) que seria alfa

    o que esta havendo ele esta me repetindo o mesmo nome nas linhas seguinte e não de ma o nome posterior, conforme eu lhe enviei por e-mail

    desde já lhe agradeço.

  9. Mario disse:

    Obrigado pela ajuda, estava precisando.

  10. Igor disse:

    Olá, tenho varios dados de funcionários: Funcionário A – CPF: X – RG: X – Endereço: X etc… Gostaria de criar uma lista que quando eu clicasse no nome da pessoa, as colunas subsequentes se preenchessem com os respectivos dados do funcionário.

  11. Kairton disse:

    Queria tirar uma duvida…

    Eu tava tentando usar a formula procv para que ela me retornasse com os dados do produtos de outra planilha, mas não deu… Vou exemplificar pra ficar melhor…

    Eu tenho na PLAN1 um formulario onde preencho os dados da moto que foi vendida, como: Marca Modelo, Ano, Cor, Chassi, Renavan, Placa e etc… Ai Pensei, vou tentar fazer com que o Excel retorne os dados pra mim através da Placa. Então o que eu fiz, na PLAN2 cadastrei todas as motos por completo e e fiz a formula do procv na PLAN1, so que qunado eu coloquei a placa ele não me retornou o dados direitinho, então resolvi criar um codigo, ai blz, fez tudo direitinho, so que quando tentei fazer o msm procedimento pra outro codigo, ele não me retorna mais nada, fica dando #N/D, msm os campos e a formulas estando em perfeitas condições…

    Resumindo, tem como eu usar a formula do PROCV pra vários dados?

    Exemplo:

    Cod 1 = Moto X
    Cod 2 = Moto Y
    Cod 3 = Moto O

    E assim por diante, sempre que eu colocar o codigo, ele me retornar os valores correspondentes… Entenderam?

  12. Lucas Sena disse:

    Boa tarde Marcos;

    Estou com uma dúvida no Procv:

    Tem como eu fazer um PROCV buscando apenas na matriz da planilha que filtrei?

    Exemplo:

    construcao de barragem / R$ 10,00 / BH
    terraplenagem / R$ 6,00 / SP
    terraplenagem / R$ 10,00 / RJ

    se filtro terraplenagem, busco o maior valor com SUBTOTAL, faço o PROCV para me informar qual cidade.
    So que aparece BH, pois o PROCV nao busca so nos filtros.

    Pode me ajudar?

    • Marcos Rieper disse:

      Boa tarde Lucas,

      Para fazer isso é necessário realizar uma chave composta, como por exemplo terraplanagem-BH, terraplanagem-SP, então para criar esta chave faça =A1&”-“&B1 e depois utilize esta chave no PROCV.

      Abraço

      Marcos Rieper

  13. Silvio disse:

    Boa tarde Marcos,

    Tenho uma planilha que esta descrita a árvore de composição de nossos produtos.

    Criei um check list com objetivo de apenas digitar o código do produto e quantidade que será produzida e os dados da árvore de composição do produto aparecerem já multiplicados, no entanto assim como nos relatos anteriores tenhos diversos produtos que utilizam da mesma matéria prima e consequentemente tem o mesmo código. Existe alguma maneira de digitar apenas o código do produto e ele buscar apenas os que fazem parte de sua composição?

    Muito Obrigado e parabéns pelo trabalho”

  14. Renaldo Moura disse:

    Marcos Rieper,

    Obrigado!

    Depois de varias buscas e tentando combinação de formula até 05:00hs da manhã consegui encontra uma solução.

    Que Deus abençoe

    Renaldo Moura

  15. Jack disse:

    Não consegui baixar a planilha exemplo, mas segui as instruções e deu certo. Pode ser pq o post é velho e o link expirou, mas muito útil o post

    Estava precisando muito e não sabia como fazer

    Parabéns

  16. Danilo Ferraz disse:

    Boa noite Marcos,

    tenho uma coluna com o percentual de produtividade de alguns funcionários e na coluna ao lado tenho os nomes dos funcionários:

    A B C
    1 90% Joãozinho
    2 85% Zezinho
    3 86% Pedrinho
    4 85% Luizinho
    etc…

    Os valores da coluna B vem de uma outra aba da planilha, porém preciso gerar um pareto destes dados e para isto preciso que eles estejam em ordem decrescente. Seria mto simples alinhar, porém toda vez que houver uma alteração na outra aba o pareto ficará desalinhado (dente de serra). Minha ideia foi na célula E1 aplicar a fórmula =MAIOR($B$1:$C$4;A1) e arrastar pra baixo, desta forma os valores de produtividade ficaram alinhados de forma decrescente e mesmo que a outra aba seja alterada a formula fará com que eles se realinhem de forma decrescente mantendo o pareto sem “dente de serra”, agora preciso aplicar o PROCV na célula D1 para encontrar o funcionário correspondente ao valor da produtividade, mas ocorre que por ter funcionários com o mesmo percentual a fórmula sempre retorna o nome do primeiro funcionário encontrado, no exemplo acima sempre retorna o Zezinho e nunca o Luizinho (ambos com 85%). O método do seu post funciona bem porém para manter os dados nas células onde o gráfico tem como referencia eu não encontrei uma alternativa. Será que tem como?

  17. Danilo disse:

    Boa tarde Marcos,

    infelizmente não é isto que estou precisando, pois a ideia não é filtrar e sim ordenar em ordem decrescente para poder gerar um gráfico. Conforme exemplo acima, a matriz / tabela deveria ficar assim:

    Coluna D ; Coluna E
    Joaozinho ; 90%
    Pedrinho ; 86%
    Zezinho ; 85%
    Luizinho ; 85%

    Desta forma eu geraria um pareto do maior para o menor, mas o que ocorre é que os dados estão desordenados, desta forma eu alinho e gero o gráfico, quando os valores são alterados na outra aba o gráfico fica bagunça então eu tenho que alinhar novamente os dados.

  18. Rogerio disse:

    Parabéns pelo site primeiramente!

    Tenho uma duvida simples de resolver no access, porem no excel não encontro uma formula adequada.

    Possuo duas planilhas dados. Busco um valor existente na primeira planilha em uma relação de dados na segunda planilha. A principio utilizo a formula =procv e encontro o valor desejado. Porem desejo obter uma segunda informação referente a linha deste valor encontrado pelo procv. Ja tentei usar um “procv” com “se”, porem ele nao me traz a informação correta referente a linha desejada.
    Alguma formula adequada?

    Ex:
    Planilha 01
    Coluna A
    001
    012
    015
    086

    Planilha 02
    Coluna A Coluna B
    012 2
    001 1
    086 2
    015 3

    Obrigado pela atenção.
    Rogerio

  19. Obrigado! Excelente Explicação!!!
    Consegui resolver e tirar as dúvidas!!!

    Sucesso!
    Rodrigo

  20. vAGNER disse:

    Valeu!!! Me ajudou muito!!!

  21. Julio Dias disse:

    Ajudou e muito o meu dia hoje.
    Problema de uma semana resolvido em questões de minutos.
    Abraços

  22. João Júnior disse:

    Boa Noite!!!

    Na minha planilha do Excel eu tenho as seguintes informações numa coluna:
    Carlos
    Monica
    Monica
    Melissa
    Luiz
    Pedro
    Melissa

    Gostaria que você solucionasse meu problema fornecendo uma solução em formula e que o resultado desse:
    Carlos
    Monica
    Melissa
    Luiz
    Pedro.

    Abraços.

    • Marcos Rieper disse:

      Boa tarde João,

      Utilize o procedimento Remover Duplicatas do Excel.

      Selecione a coluna em que há os itens duplicados. Clique na guia Dados->Remover duplicatas.

      O Excel irá remover todos os itens duplicados da coluna.

      At.
      Marcos Rieper

  23. ADRIANO JUNIOR disse:

    Boa tarde,
    Estou com um problema e gostaria de receber uma orientação se for possível.
    Tenho uma planilha onde alimento diariamente o abastecimentos dos equipamentos da obra. numa planilha ao lado farei um resumo geral(horas trabalhadas e abastecimento).
    não estou conseguindo extrair o menor valor e o maior valor de horas trabalhas, pois o equipamento aparece várias vezes na tabela.

Deixe uma resposta

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

Esse site utiliza o Akismet para reduzir spam. Aprenda como seus dados de comentários são processados.

Inscreva-se no nosso canal do Youtube!


Junte-se ao nosso canal do Youtube. Começamos em abril de 2016, mas já temos mais de 06:00 h de treinamentos gratuitos e este número irá aumentar. Vídeos novos todos os sábados.